using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace VueWebCoreApi.Tools
{
    public class NPOIHelper
    {
        /// <summary>
        /// NPOI简单Demo，快速入门代码
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strFileName"></param>
        /// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
        public static void ExportEasy(DataTable dtSource, string strFileName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            //填充表头
            IRow dataRow = sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }


            //填充内容
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }


            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
            //workbook.Dispose();
        }




        public static System.Data.DataTable GetExcelDatatable(string fileUrl)
        {

            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";


            DataSet myDataSet = new DataSet();

            //连接串  
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　  
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            //包含excel中表名的字符串数组  
            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }
            OleDbDataAdapter myCommand = null;
            DataTable dt = new DataTable();
            //从指定的表明查询数据,可先把所有表明列出来供用户选择  
            string strExcel = "select*from[" + strTableNames[0] + "]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            //myCommand.Fill(dt);  
            myCommand.Fill(myDataSet, "ExcelInfo");
            conn.Close();
            DataTable ExcelTable = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
            return ExcelTable;
        }


        /// <summary>
        /// 多个sheet导入
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <returns></returns>
        public static List<DataTable> GetExcelDatatableList(string fileUrl)
        {
            List<DataTable> list = new List<DataTable>();
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";


            DataSet myDataSet = new DataSet();

            //连接串  
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　  
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            //包含excel中表名的字符串数组  
            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }
            for (int i = 0; i < strTableNames.Length; i++)
            {
                OleDbDataAdapter myCommand = null;
                DataTable dt = new DataTable();
                //从指定的表明查询数据,可先把所有表明列出来供用户选择  
                string strExcel = "select*from[" + strTableNames[i].Trim() + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                //myCommand.Fill(dt);  
                myCommand.Fill(myDataSet, "" + i + "");
                conn.Close();
                DataTable ExcelTable = myDataSet.Tables[i].DefaultView.ToTable();
                list.Add(ExcelTable);
            }

            return list;
        }

        /// <summary>
        /// 多个sheet导入
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <returns></returns>
        public static List<DataTable> GetExcelDatatableListName(string fileUrl)
        {
            List<DataTable> list = new List<DataTable>();
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";


            DataSet myDataSet = new DataSet();

            //连接串  
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　  
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            for (int m = 0; m < dtSheetName.Rows.Count; m++)
            {
                string TABLE_NAME = dtSheetName.Rows[m]["TABLE_NAME"].ToString();
                string FilterDatabase = "$FilterDatabase";
                if (TABLE_NAME.Contains(FilterDatabase))
                {
                    dtSheetName.Rows.Remove(dtSheetName.Rows[m]);
                }
            }

            //包含excel中表名的字符串数组  
            string[] strTableNames = new string[dtSheetName.Rows.Count];
            for (int k = 0; k < dtSheetName.Rows.Count; k++)
            {
                strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
            }
            for (int i = 0; i < strTableNames.Length; i++)
            {
                OleDbDataAdapter myCommand = null;
                DataTable dt = new DataTable();

                //从指定的表明查询数据,可先把所有表明列出来供用户选择  
                string strExcel = "select*from[" + strTableNames[i].Trim() + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                //myCommand.Fill(dt);  
                myCommand.Fill(myDataSet, "" + i + "");
                conn.Close();
                DataTable ExcelTable = myDataSet.Tables[i].DefaultView.ToTable();
                ExcelTable.TableName = strTableNames[i].Trim();
                list.Add(ExcelTable);
            }

            return list;
        }
    }
}