ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace DockHelper
{   
        public class ExcelHelper : IDisposable
        {
            private string fileName = null; //文件名
            private IWorkbook workbook = null;
            private FileStream fs = null;
            private bool disposed;


            public ExcelHelper(string fileName)
            {
                this.fileName = fileName;
                disposed = false;
            }


            /// <summary>
            /// 将DataTable数据导入到excel中
            /// </summary>
            /// <param name="data">要导入的数据</param>
            /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
            /// <param name="sheetName">要导入的excel的sheet的名称</param>
            /// <returns>导入数据行数(包含列名那一行)</returns>
            public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
            {
                int i = 0;
                int j = 0;
                int count = 0;
                ISheet sheet = null;


                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook();
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook();


                try
                {
                    if (workbook != null)
                    {
                        sheet = workbook.CreateSheet(sheetName);
                    }
                    else
                    {
                        return -1;
                    }


                    if (isColumnWritten == true) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }


                    for (i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        ++count;
                    }
                    workbook.Write(fs); //写入到excel
                    return count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return -1;
                }
            }


            /// <summary>
            /// 将excel中的数据导入到DataTable中
            /// </summary>
            /// <param name="sheetName">excel工作薄sheet的名称，如果是null则取出第0个标签</param>
            /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
            /// <returns>返回的DataTable</returns>
            public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int getRow = 0)
            {
                if (isFirstRowColumn)
                {
                    return ExcelToDataTable(sheetName, true, 0, -1, 1, getRow);
                }
                else
                {
                    return ExcelToDataTable(sheetName, false, 0, -1, 0, getRow);
                }
            }


            /// <summary>
            /// 将excel中的数据导入到DataTable中
            /// </summary>
            /// <param name="sheetName">excel工作薄sheet的名称，如果是null则取出第0个标签</param>
            /// <param name="hasColumn">是否包含DataTable的列名，true代表是包含将会去除字段名称</param>
            /// <param name="columnIndex">列名行数，从0开始</param>
            /// <param name="columnTypeIndex">列类型，-1代表没有该内容</param>
            /// <param name="startRow">数值起始行数，从0开始</param>
            /// <param name="getRow">获取行数，0代表获取所有内容</param>
            /// <param name="getRow">获取列数，0代表获取第一行的列数</param>
            /// <returns>返回的DataTable</returns>
            /// 
            public DataTable ExcelToDataTable(string sheetName, bool hasColumn, int columnIndex, int columnTypeIndex, int startRow, int getRow = 0, int setColumnCount = 0)
            {
                ISheet sheet = null;
                DataTable data = new DataTable();
                try
                {
                    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook(fs);


                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(startRow);
                        if (firstRow == null)
                        {
                            //MessageBox.Show("第" + startRow + "行没有发现内容");
                            return null;
                        }
                        int cellCount;
                        if (setColumnCount == 0)
                        {
                            cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                        }
                        else
                        {
                            cellCount = setColumnCount;
                        }


                        if (hasColumn)//如果包含列名
                        {
                            IRow columnRow = sheet.GetRow(columnIndex);
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = columnRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (cellValue != null)
                                    {
                                        DataColumn column = new DataColumn(cellValue);
                                        data.Columns.Add(column);
                                    }
                                }
                            }
                        }
                        else
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                DataColumn column = new DataColumn("Col" + i.ToString());
                                data.Columns.Add(column);
                            }
                        }


                        //根据第二行得到单元格类型
                        if (columnTypeIndex != -1)
                        {
                            IRow TwoRow = sheet.GetRow(columnTypeIndex);
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = TwoRow.GetCell(i);
                                if (cell != null)
                                {
                                    data.Columns[i].DataType = GetCellValue(cell).GetType();
                                }
                            }
                        }


                        int rowCount = sheet.LastRowNum;//最后一列的标号
                        if (getRow != 0 && getRow < rowCount)//用于预览时只部分提取
                        {
                            rowCount = getRow;
                        }


                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　


                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j));
                                }
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                    return data;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("从Excel中读取数据错误，错误：" + ex.Message);
                    Console.WriteLine("Exception: " + ex.ToString());
                    return null;
                }
            }


            public List<string> GetSheetNames()
            {
                try
                {
                    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook(fs);
                    int x = workbook.NumberOfSheets;
                    List<string> sheetNames = new List<string>();
                    for (int i = 0; i < x; i++)
                    {
                        sheetNames.Add(workbook.GetSheetName(i));
                    }
                    return sheetNames;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("从Excel中读取数据错误，错误：" + ex.Message);
                    Console.WriteLine("Exception: " + ex.ToString());
                    return null;
                }
            }


            /// <summary>
            /// 将DataSet导出到Excel中
            /// </summary>
            /// <param name="dataset"></param>
            /// <param name="FileName"></param>
            public static void DataSetToExcel(DataSet dataset, string FileName)
            {
                if (FileName.IndexOf(".xlsx") > 0) // 2007版本
                    DataSetToExcel2007(dataset, FileName);
                else if (FileName.IndexOf(".xls") > 0) // 2003版本
                    DataSetToExcel2003(dataset, FileName);
            }


            /// <summary>
            /// 将DataSet导出到Excel中
            /// </summary>
            /// <param name="dataset"></param>
            /// <param name="FileName"></param>
            public static void DataSetToExcel2003(DataSet dataset, string FileName)
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                for (int i = 0; i < dataset.Tables.Count; i++)
                {
                    System.Data.DataTable datatable = dataset.Tables[i];
                    HSSFSheet sheet = workbook.CreateSheet(datatable.TableName) as HSSFSheet;
                    //填充表头
                    HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
                    foreach (DataColumn column in datatable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    //填充内容
                    int rownum = 0;
                    int sheetnum = 0;
                    for (int j = 0; j < datatable.Rows.Count; j++)
                    {
                        rownum++;
                        if (rownum > 60000)
                        {
                            rownum = 1;
                            sheetnum++;
                            sheet = workbook.CreateSheet(datatable.TableName + sheetnum.ToString()) as HSSFSheet;
                            dataRow = sheet.CreateRow(0) as HSSFRow;
                            foreach (DataColumn column in datatable.Columns)
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            }
                        }
                        dataRow = sheet.CreateRow(rownum) as HSSFRow;
                        for (int k = 0; k < datatable.Columns.Count; k++)
                        {
                            dataRow.CreateCell(k).SetCellValue(datatable.Rows[j][k].ToString());
                        }
                    }
                }
                //保存
                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                        fs.Close();
                    }
                }
            }


            /// <summary>
            /// 将DataSet导出到Excel中
            /// </summary>
            /// <param name="dataset"></param>
            /// <param name="FileName"></param>
            public static void DataSetToExcel2007(DataSet dataset, string FileName)
            {
                XSSFWorkbook workbook = new XSSFWorkbook();
                for (int i = 0; i < dataset.Tables.Count; i++)
                {
                    System.Data.DataTable datatable = dataset.Tables[i];
                    XSSFSheet sheet = workbook.CreateSheet(datatable.TableName) as XSSFSheet;
                    //填充表头
                    XSSFRow dataRow = sheet.CreateRow(0) as XSSFRow;
                    foreach (DataColumn column in datatable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    //填充内容
                    int rownum = 0;
                    int sheetnum = 0;
                    for (int j = 0; j < datatable.Rows.Count; j++)
                    {
                        rownum++;
                        if (rownum > 1048000)
                        {
                            rownum = 1;
                            sheetnum++;
                            sheet = workbook.CreateSheet(datatable.TableName + sheetnum.ToString()) as XSSFSheet;
                            dataRow = sheet.CreateRow(0) as XSSFRow;
                            foreach (DataColumn column in datatable.Columns)
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            }
                        }
                        dataRow = sheet.CreateRow(rownum) as XSSFRow;
                        for (int k = 0; k < datatable.Columns.Count; k++)
                        {
                            dataRow.CreateCell(k).SetCellValue(datatable.Rows[j][k].ToString());
                        }
                    }
                }
                //保存
                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                        fs.Close();
                    }
                }
            }


            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }


            protected virtual void Dispose(bool disposing)
            {
                if (!this.disposed)
                {
                    if (disposing)
                    {
                        if (fs != null)
                            fs.Close();
                    }


                    fs = null;
                    disposed = true;
                }
            }


            public static object GetCellValue(ICell cell)
            {
                object value = null;
                try
                {
                    if (cell.CellType != CellType.Blank)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Numeric:
                                // Date Type的数据CellType是Numeric
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    value = cell.DateCellValue;
                                }
                                else
                                {
                                    // Numeric type
                                    value = cell.NumericCellValue;
                                }
                                break;
                            case CellType.Boolean:
                                // Boolean type
                                value = cell.BooleanCellValue;
                                break;
                            default:
                                // String type
                                value = cell.StringCellValue;
                                break;
                        }
                    }
                }
                catch (Exception)
                {
                    value = "";
                }


                return value;
            }
        }
    }