C# NPOI操作Excel（下）

根据自己项目需求编写，仅供参考

个人建议：使用Excel模板进行导出操作。尽量避免自己生成Excel（既繁琐又容易出BUG）。大多情况下导出Excel都是固定格式，使用模板导出会方便很多。

public class NpoiExcelHelper
    {
        /// <summary>
        /// 读取excel转为DataTable
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">指定sheet</param>
        /// <param name="isColumnName">第一行是否为列名</param>
        /// <param name="startRow">从第几行开始</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isColumnName, int startRow = 0)
        {
            IWorkbook workBook = null;
            ISheet sheet = null;
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                //低于2007版本
                if (Path.GetExtension(fileName) == ".xls")
                {
                    workBook = new HSSFWorkbook(fs);
                }
                //2007及以上版本
                else if (Path.GetExtension(fileName) == ".xlsx")
                {
                    workBook = new XSSFWorkbook(fs);
                }
            }
            //判断是否指定sheet上传
            if (sheetName != null)
            {
                //获取指定sheet
                sheet = workBook.GetSheet(sheetName);
                if (sheet == null)
                {
                    //获取不到时取第一个sheet
                    sheet = workBook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workBook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //sheet中第一行
                IRow firstRow = sheet.GetRow(0);
                if (firstRow == null)
                {
                    throw new Exception("首行无数据");
                }

                //遍历第一行的单元格
                for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
                {
                    //得到列名
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        //得到列名的值,若列名不是字符则不能使用StringCellValue，最好使用ToString()
                        string cellValue = cell.ToString();
                        if (cellValue != null)
                        {
                            try
                            {
                                //判断第一行是否是列名
                                if (isColumnName)
                                {
                                    //将列放入datatable中
                                    DataColumn column = new DataColumn(cellValue);
                                    dt.Columns.Add(column);
                                }
                                else
                                {
                                    //将空列放入datatable中
                                    DataColumn column = new DataColumn();
                                    dt.Columns.Add(column);
                                }
                            }
                            catch
                            {
                                throw new Exception("列名有误！");
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                }
                //遍历所有行
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    //得到i行
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    //datatable新增行
                    DataRow dr = dt.NewRow();
                    //遍历i行的单元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    try
                    {
                        //将行放入datatable中
                        dt.Rows.Add(dr);
                    }
                    catch
                    {
                        throw new Exception("第" + i + "行有误！");
                    }
                }
            }
            return dt;
        }
        /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="fileName">导出文件的路径</param>
        /// <param name="templetName">导出模板路径</param>
        /// <param name="dt">DataTable</param>
        /// <param name="titleName">文件标题</param>
        /// <param name="sheetName">文件sheet名称</param>
        public static void DataTableToExcel(string fileName,string templetName, DataTable dt, string titleName,string sheetName)
        {
            FileStream fs1 = new FileStream(templetName, FileMode.Open, FileAccess.Read);
            IWorkbook workBook = new HSSFWorkbook(fs1);
            ISheet sheet = workBook.GetSheet(sheetName);
            
            //第一行
            IRow row0 = sheet.GetRow(0);
            ICell cellTitle = row0.GetCell(0);
            cellTitle.SetCellValue(titleName);
            //第二行
            IRow row1 = sheet.GetRow(1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell cell = row1.GetCell(j);
                cell.SetCellValue(dt.Columns[j].ColumnName);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow rowi = sheet.CreateRow(i + 2);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    //创建单元格
                    ICell cell = rowi.CreateCell(j);
                    //给单元格赋值
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                    cell.CellStyle.BorderBottom = BorderStyle.Thin;
                    cell.CellStyle.BorderLeft = BorderStyle.Thin;
                }
            }
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workBook.Write(fs);
            }
        }      
    }