using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

using System.Linq;

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

namespace Trumgu_BI_PF.Util

{

    public static class NpoiHelper

    {

        /// <summary>

        /// dataset导出Excel

        /// </summary>

        /// <param name="dataSet"></param>

        /// <param name="fileFullPath"></param>

        public static void ExportToFile(DataSet dataSet, string fileFullPath)

        {

            var dts = dataSet.Tables.Cast<DataTable>().ToList();

            ExportToFile(dts, fileFullPath);

        }

        /// <summary>

        /// datatable导出Excel

        /// </summary>

        /// <param name="dataTable"></param>

        /// <param name="fileFullPath"></param>

        public static void ExportToFile(DataTable dataTable, string fileFullPath)

        {

            var dts = new List<DataTable> { dataTable };

            ExportToFile(dts, fileFullPath);

        }

        /// <summary>

        /// 多个datatable导出Excel

        /// </summary>

        /// <param name="dataTables"></param>

        /// <param name="fileFullPath"></param>

        public static void ExportToFile(IEnumerable<DataTable> dataTables, string fileFullPath)

        {

            IWorkbook workbook = new XSSFWorkbook();

            var i = ;

            foreach (var dt in dataTables)

            {

                var sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i) : dt.TableName;

                var sheet = workbook.CreateSheet(sheetName);

                var headerRow = sheet.CreateRow();

                for (var j = ; j < dt.Columns.Count; j++)

                {

                    var columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)

                        ? "Column " + j

                        : dt.Columns[j].ColumnName;

                    headerRow.CreateCell(j).SetCellValue(columnName);

                }

                for (var a = ; a < dt.Rows.Count; a++)

                {

                    var dr = dt.Rows[a];

                    var row = sheet.CreateRow(a + );

                    for (var b = ; b < dt.Columns.Count; b++)

                    {

                        row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value

                            ? dr[b].ToString()

                            : string.Empty);

                    }

                }

            }

            using (var fs = File.Create(fileFullPath))

            {

                workbook.Write(fs);

            }

        }

        /// <summary>

        /// excel导入datatable

        /// </summary>

        /// <param name="xlsxFile"></param>

        /// <returns></returns>

        public static List<DataTable> GetDataTablesFrom(string xlsxFile)

        {

            if (!File.Exists(xlsxFile))

                throw new FileNotFoundException("文件不存在");

            var result = new List<DataTable>();

            Stream stream = new MemoryStream(File.ReadAllBytes(xlsxFile));

            IWorkbook workbook = new XSSFWorkbook(stream);

            for (var i = ; i < workbook.NumberOfSheets; i++)

            {

                var dt = new DataTable();

                var sheet = workbook.GetSheetAt(i);

                var headerRow = sheet.GetRow();

                int cellCount = headerRow.LastCellNum;

                for (int j = headerRow.FirstCellNum; j < cellCount; j++)

                {

                    var column = new DataColumn(headerRow.GetCell(j).StringCellValue);

                    dt.Columns.Add(column);

                }

                var rowCount = sheet.LastRowNum;

                for (var a = (sheet.FirstRowNum + ); a < rowCount; a++)

                {

                    var row = sheet.GetRow(a);

                    if (row == null) continue;

                    var dr = dt.NewRow();

                    for (int b = row.FirstCellNum; b < cellCount; b++)

                    {

                        if (row.GetCell(b) == null) continue;

                        dr[b] = row.GetCell(b).ToString();

                    }

                    dt.Rows.Add(dr);

                }

                result.Add(dt);

            }

            stream.Close();

            return result;

        }

    }

}