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);
            }
        }      
    }