1 NPOI簡介
1.1 NPOI是什麼
NPOI是POI的.NET版本,POI是一套用Java寫成的庫，我們在開發中經常用到匯入匯出表格、文件的情況，NPOI能夠幫助我們在沒有安裝微軟Office的情況下讀寫Office檔案，如xls, doc, ppt等。NPOI採用的是Apache 2.0許可證（poi也是採用這個許可證），這意味著它可以被用於任何商業或非商業專案，我們不用擔心因為使用它而必須開放你自己的原始碼，所以它對於很多從事業務系統開發的公司來說絕對是很不錯的選擇。
1.2 NPOI簡單使用
NPOI的API十分人性化，使用起來非常容易上手。首先建立一個控制檯應用程式，通過nuget獲取NPOI，這裡使用的是最新版本2.4.0，如下：
專案引入NPOI後就可以直接上手使用了，程式碼如下
static void Main(string[] args) { //建立workbook，說白了就是在記憶體中建立一個Excel檔案 IWorkbook workbook = new HSSFWorkbook(); //要新增至少一個sheet,沒有sheet的excel是打不開的 ISheet sheet1 = workbook.CreateSheet("sheet1"); ISheet sheet2 = workbook.CreateSheet("sheet2"); IRow row1 = sheet1.CreateRow(0);//新增第1行,注意行列的索引都是從0開始的 ICell cell1 = row1.CreateCell(0);//給第1行新增第1個單元格 cell1.SetCellValue("hello npoi!");//給單元格賦值 //上邊3個步驟合在一起：sheet1.CreateRow(0).CreateCell(0).SetCellValue("hello npoi"); //獲取第一行第一列的string值 Console.WriteLine(sheet1.GetRow(0).GetCell(0).StringCellValue); //輸出：hello npoi //寫入檔案 using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create)) { workbook.Write(file); } }
執行程式碼，結果如下，同時也在D盤的TestFiles資料夾下建立了test.xls檔案
2 NPOI使用詳解
2.1 單元格資料格式
用過Excel的人都知道，單元格是Excel最有意義的東西，我們做任何操作恐怕都要和單元格打交道。在Excel中我們經常要設定格式，比如說日期格式（2018-1-1）、小數點格式（1.20）、貨幣格式（$2000）、百分比格式（99.99%）等等，這些東西在過去我們恐怕只能在伺服器端生成好，不但增加了伺服器端的程式碼量，還造成了不必要的字串替換操作，如今NPOI將讓伺服器從這種完全沒有必要的操作中解放出來，一切都將由Excel在客戶端處理。
使用NPOI時要注意，所有的格式都是通過 CellStyle.DataFormat 賦給單元格的，而不是直接賦給單元格，設定單元格資料展示的格式有兩種方法。
1.設定excel中內建的格式：
excel內部設定了很多格式，如下圖所示：
如果我們想使用這些內建的格式，可用通過 DataFormat.GetBuildinFormat('格式') 來使用，使用例項如下：
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //兩位小數，內建格式
2.設定自定義格式
有時候內建的格式不能滿足我們的要求，這時候就可以使用 workbook.CreateDataFormat().GetFormat("格式") 自定義格式了，使用語法如下：
cellStyle.DataFormat= workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh時mm分ss秒");//顯示中文日期和時間，自定義格式
下邊的栗子展示了幾種常用的資料格式用法：
static void Main(string[] args) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("new sheet"); //設定日期格式，2018年5月5日格式 ICell cell = sheet.CreateRow(0).CreateCell(0); cell.SetCellValue(new DateTime(2018, 5, 5, 11, 31, 56)); ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh時mm分ss秒"); //cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd hh:mm:ss"); cell.CellStyle = cellStyle; //保留2位小數 ICell cell2 = sheet.CreateRow(1).CreateCell(0); cell2.SetCellValue(12.346666); ICellStyle cellStyle2 = workbook.CreateCellStyle(); cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); cell2.CellStyle = cellStyle2; //貨幣格式 ICell cell3 = sheet.CreateRow(2).CreateCell(0); cell3.SetCellValue(1234.66666); ICellStyle cellStyle3 = workbook.CreateCellStyle(); cellStyle3.DataFormat = workbook.CreateDataFormat().GetFormat("￥#,##0.00");//美元的話格式為 $#,##0.00，其中#，##0表示千分號 cell3.CellStyle = cellStyle3; //百分比 ICell cell4 = sheet.CreateRow(3).CreateCell(0); cell4.SetCellValue(1.236666); ICellStyle cellStyle4 = workbook.CreateCellStyle(); //cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");//保留兩位小數 cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%"); cell4.CellStyle = cellStyle4; //中文大寫數字 ICell cell5 = sheet.CreateRow(4).CreateCell(0); cell5.SetCellValue(111); ICellStyle cellStyle5 = workbook.CreateCellStyle(); cellStyle5.DataFormat = workbook.CreateDataFormat().GetFormat("[DbNum2][$-804]0.00");//不保留小數： [DbNum2][$-804]0 cell5.CellStyle = cellStyle5; //科學計數法 ICell cell6 = sheet.CreateRow(5).CreateCell(0); cell6.SetCellValue(1234.6666); ICellStyle cellStyle6 = workbook.CreateCellStyle(); cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); cell6.CellStyle = cellStyle6; using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create)) { workbook.Write(file); } }
執行程式，生成的excel檔案如下所示：
2.2 單元格合併及設定風格
單元格的合併和風格這部分內容比較多，但是Api十分的簡單，為了看起來清晰，我們分成兩個栗子來演示這部分的功能，程式碼中的註釋很詳細，這裡就直接上程式碼了
1.第一個栗子演示單元格合併，內容停靠，字型風格顏色大小等
static void Main(string[] args) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); //------------------單元格合併 sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 5));//合併單元格,4個引數依次為為startRow,endRow,startCol,endCol ICell cell = sheet.CreateRow(1).CreateCell(1); cell.SetCellValue("合併的區域"); cell.Row.HeightInPoints = 40;//行高 ICellStyle cellstyle = workbook.CreateCellStyle(); //----------------設定單元格常用風格 cellstyle.Alignment = HorizontalAlignment.Left;//水平居左，可選Right,Center cellstyle.VerticalAlignment = VerticalAlignment.Bottom;//垂直居中,可選Top,Center cellstyle.WrapText = true;//自動換行 cellstyle.Indention = 4;//縮排4個位元組 //cellstyle.Rotation = 90;//字型旋轉90度，取值範圍是[-90,90] //---------------字型，字型大小、顏色 IFont font = workbook.CreateFont(); font.FontHeightInPoints = 24;//字號為24，可以用font.FontHeight = 24 * 20;FontHeight的單位是1/20點 font.FontName = "楷體";//字型 font.Boldweight = 700;//加粗 font.Color = HSSFColor.Blue.Index;//字型顏色 cellstyle.SetFont(font); cell.CellStyle = cellstyle; using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create)) { workbook.Write(file); } }
生成的excel檔案如下圖所示：
2.第二個栗子演示單元格的寬高、背景色、邊框風格等
static void Main(string[] args) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); ICell cell = sheet.CreateRow(1).CreateCell(1); cell.SetCellValue("hello"); ICellStyle cellstyle = workbook.CreateCellStyle(); //設定寬度，把第2列設成20位元組寬；引數1指定列的索引；引數2指定寬度，單位是1/256個字元寬度 sheet.SetColumnWidth(1, 20 * 256); //高度設成40，單位是點；也可以用cell2.Row.Height = 40 * 20;Height的單位是1/20點 cell.Row.HeightInPoints = 40; //右邊框設定成點畫線 cellstyle.BorderRight = BorderStyle.MediumDashDot; //右邊框設成粉色 cellstyle.RightBorderColor = HSSFColor.Pink.Index; //前景色填充模式，實心 cellstyle.FillPattern = FillPattern.SolidForeground; //前景色填充顏色，黃色 cellstyle.FillForegroundColor = HSSFColor.Yellow.Index; //背景色 //cellstyle2.FillBackgroundColor = HSSFColor.Green.Index;//背景色設成green cell.CellStyle = cellstyle; using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create)) { workbook.Write(file); } }
執行程式，生成的excel檔案如下：
其實在NPOI中除了操作單元格的內容和風格外，還提供了新增excel檔案摘要，新增批註，畫圖，使用公式等功能，但是這些功能我們用的比較少，所以就不一一展示了，NPOI的公式我自己很少去使用，一般通過程式直接計算結果，個人感覺在程式中計算更加靈活，當excel的結構發生變化時修改起來也更方便些。
3.封裝一個NPOI的helper類
我們先看一下這個helper的使用吧，先看從DataTable匯出到excel
class Program { static void Main(string[] args) { log4net.Config.XmlConfigurator.Configure(); //測試dataTable資料來源 DataTable dataTable = new DataTable(); dataTable.Columns.Add("id", typeof(int)); dataTable.Columns.Add("uname", typeof(string)); dataTable.Columns.Add("sex", typeof(string)); dataTable.Columns.Add("age", typeof(int)); dataTable.Columns.Add("pwd", typeof(string)); dataTable.Columns.Add("email", typeof(string)); dataTable.Columns.Add("address", typeof(string)); Random r = new Random(); for (int i = 0; i < 1000; i++) { DataRow row = dataTable.NewRow(); row["id"] = i; row["uname"] = "hellox" + i.ToString(); row["sex"] = r.Next(2) % 2 == 0 ? "男" : "女"; row["age"] = r.Next(40) + 18; row["pwd"] = "pwd" + r.Next(5000).ToString(); row["email"] = r.Next(100000) + "@qq.com"; row["address"] = $"北京市，西{r.Next(4) + 1}環，xx路{r.Next(100)}號"; dataTable.Rows.Add(row); } //DataTable的列名和excel的列名對應字典，因為excel的列名一般是中文的，DataTable的列名是英文的，字典主要是儲存excel和DataTable列明的對應關係，當然我們也可以把這個對應關係存在配置檔案或者其他地方 Dictionary<string, string> dir = new Dictionary<string, string>(); dir.Add("id", "編號"); dir.Add("uname", "使用者"); dir.Add("sex", "性別"); dir.Add("age", "年齡"); dir.Add("pwd", "密碼"); dir.Add("email", "郵箱"); dir.Add("address", "住址"); //使用helper類匯出DataTable資料到excel表格中,引數依次是 （源資料，excel表名，excel存放位置，列名對應字典，是否清空以前的資料，每個sheet放的資料條數） NPOIHelper.ExportDTtoExcel(dataTable, "考勤資訊表", @"C:\Users\ZDZN\Desktop/Hello11.xlsx", dir, false, 400); } }
執行程式，當一個sheet的數目滿了後會新生產一個sheet1來儲存，效果如下：
接下來測試讀取excel中的資料到datatable中，我們就讀取剛才生成的excel中的資料到DataTable中去：
static void Main(string[] args) { log4net.Config.XmlConfigurator.Configure(); //DataTable的列名和excel的列名對應字典 Dictionary<string, string> dir = new Dictionary<string, string>(); dir.Add("id", "編號"); dir.Add("uname", "使用者"); dir.Add("sex", "性別"); dir.Add("age", "年齡"); dir.Add("pwd", "密碼"); dir.Add("email", "郵箱"); dir.Add("address", "住址"); //讀取資料到DataTable，引數依此是（excel檔案路徑，列名對應字典，列名所在行，sheet索引） DataTable dt = NPOIHelper.ImportExceltoDt(@"C:\Users\ZDZN\Desktop/Hello11.xlsx", dir, 1, 0); //遍歷DataTable--------------------------- foreach (DataColumn item in dt.Columns) { //顯示dataTable的列名 Console.Write(item.ColumnName + new string('-', 10)); } Console.WriteLine(); //遍歷DataTable中的資料 foreach (DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; i++) { Console.Write(row[i].ToString() + new string(' ', 10)); } Console.WriteLine(); } Console.WriteLine("ok"); Console.ReadKey(); }
程式執行如下：
上邊例子中的helper相容了xls和xlsx兩種格式，通過helper只需要一行程式碼就可以快速地進行excel和datatable間的資料轉換，helper附件如下
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using System.Web; using NPOI; using NPOI.HPSF; using NPOI.HSSF; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util; using NPOI.POIFS; using NPOI.SS.Formula.Eval; using NPOI.SS.UserModel; using NPOI.Util; using NPOI.SS; using NPOI.DDF; using System.Linq; using NPOI.SS.Util; using System.Collections; using System.Text.RegularExpressions; using NPOI.XSSF; using NPOI.XSSF.UserModel; using log4net; public class NPOIHelper { private static ILog loger = LogManager.GetLogger("myLogger"); #region 從DataTable匯出到excel檔案中，支援xls和xlsx格式 #region 匯出為xls檔案內部方法 /// <summary> /// 從datatable 中匯出到excel /// </summary> /// <param name="strFileName">excel檔名</param> /// <param name="dtSource">datatabe源資料</param> /// <param name="strHeaderText">表名</param> /// <param name="sheetnum">sheet的編號</param> /// <returns></returns> static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum) { //建立工作簿和sheet IWorkbook workbook = new HSSFWorkbook(); using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read)) { if (writefile.Length > 0 && sheetnum > 0) { workbook = WorkbookFactory.Create(writefile) ; } } ISheet sheet = null; ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表頭，填充列頭，樣式 if (rowIndex == 0) { string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString()); if (workbook.GetSheetIndex(sheetName) >= 0) { workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName)); } sheet = workbook.CreateSheet(sheetName); #region 表頭及樣式 { sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; rowIndex = 1; } #endregion #region 列頭及樣式 if (rowIndex == 1) { IRow headerRow = sheet.CreateRow(1);//第二行設定列名 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //寫入列標題 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //設定列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2); } rowIndex = 2; } #endregion } #endregion #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字串型別 double result; if (isNumeric(drValue, out result)) { //數字字串 double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期型別 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化顯示 break; case "System.Boolean": //布林型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮點型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值處理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //dsi.Company = "江南皮革廠"; //workbook.DocumentSummaryInformation = dsi; //SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); //si.Author = "bigpig"; //填加xls檔案作者資訊 //si.ApplicationName = "NPOI測試程式"; //填加xls檔案建立程式資訊 //si.LastAuthor = "小豬佩奇"; //填加xls檔案最後儲存者資訊 //si.Comments = "說明資訊"; //填加xls檔案作者資訊 //si.Title = "NPOI測試"; //填加xls檔案標題資訊 //si.Subject = "匯出datatable中的資料"; //填加檔案主題資訊 //si.CreateDateTime = DateTime.Now; //workbook.SummaryInformation = si; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } #endregion #region 匯出為xlsx檔案內部方法 /// <summary> /// 從datatable 中匯出到excel /// </summary> /// <param name="dtSource">datatable資料來源</param> /// <param name="strHeaderText">表名</param> /// <param name="fs">檔案流</param> /// <param name="readfs">記憶體流</param> /// <param name="sheetnum">sheet索引</param> static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum) { IWorkbook workbook = new XSSFWorkbook(); if (readfs.Length > 0 && sheetnum > 0) { workbook = WorkbookFactory.Create(readfs); } ISheet sheet = null; ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列寬 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表頭，填充列頭，樣式 if (rowIndex == 0) { #region 表頭及樣式 { string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString()); if (workbook.GetSheetIndex(sheetName) >= 0) { workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName)); } sheet = workbook.CreateSheet(sheetName); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; } #endregion #region 列頭及樣式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //設定列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2); } } #endregion rowIndex = 2; } #endregion #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字串型別 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期型別 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化顯示 break; case "System.Boolean": //布林型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮點型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值處理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } workbook.Write(fs); fs.Close(); } #endregion #region 匯出excel表格 /// <summary> /// DataTable匯出到Excel檔案，xls檔案 /// </summary> /// <param name="dtSource">資料來源</param> /// <param name="strHeaderText">表名</param> /// <param name="strFileName">excel檔名</param> /// <param name="dir">datatable和excel列名對應字典</param> /// <param name="sheetRow">每個sheet存放的行數</param> public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000) { int currentSheetCount = GetSheetNumber(strFileName);//現有的頁數sheetnum if (sheetRow <= 0) { sheetRow = dtSource.Rows.Count; } string[] temp = strFileName.Split('.'); string fileExtens = temp[temp.Length - 1]; int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet數目 if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536) { if (isNew) { currentSheetCount = 0; } for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++) { DataTable pageDataTable = dtSource.Clone(); int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow; for (int j = 0; j < hasRowCount; j++) { pageDataTable.ImportRow(dtSource.Rows[(i- currentSheetCount) * sheetRow + j]); } using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } } else { if (temp[temp.Length - 1] == "xls") strFileName = strFileName + "x"; if (isNew) { currentSheetCount = 0; } for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++) { DataTable pageDataTable = dtSource.Clone(); int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow; for (int j = 0; j < hasRowCount; j++) { pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]); } FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read); MemoryStream readfsm = new MemoryStream(); readfs.CopyTo(readfsm); readfs.Close(); using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i); } readfsm.Close(); } } } #endregion #endregion #region 從excel檔案中將資料匯出到datatable/datatable /// <summary> /// 將制定sheet中的資料匯出到datatable中 /// </summary> /// <param name="sheet">需要匯出的sheet</param> /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param> /// <param name="dir">excel列名和DataTable列名的對應字典</param> /// <returns></returns> static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir) { DataTable table = new DataTable(); IRow headerRow; int cellCount; try { //沒有標頭或者不需要表頭用excel列的序號（1,2,3..）作為DataTable的列名 if (HeaderRowIndex < 0) { headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } //有表頭，使用表頭做為DataTable的列名 else { headerRow = sheet.GetRow(HeaderRowIndex); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { //如果excel某一列列名不存在：以該列的序號作為Datatable的列名，如果DataTable中包含了這個序列為名的列，那麼列名為重複列名+序號 if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { DataColumn column = new DataColumn(Convert.ToString("重複列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } //excel中的某一列列名不為空，但是重複了：對應的Datatable列名為“重複列名+序號” else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { DataColumn column = new DataColumn(Convert.ToString("重複列名" + i)); table.Columns.Add(column); } else //正常情況，列名存在且不重複：用excel中的列名作為datatable中對應的列名 { string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key; DataColumn column = new DataColumn(colName); table.Columns.Add(column); } } } int rowCount = sheet.LastRowNum; for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍歷 { try { IRow row; if (sheet.GetRow(i) == null)//如果excel有空行，則新增缺失的行 { row = sheet.CreateRow(i); } else { row = sheet.GetRow(i); } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍歷 { try { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.String://字串 string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = default(string); } break; case CellType.Numeric://數字 if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//時間戳數字 { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula://公式 switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } catch (Exception exception) { loger.Error(exception.ToString()); } } table.Rows.Add(dataRow); } catch (Exception exception) { loger.Error(exception.ToString()); } } } catch (Exception exception) { loger.Error(exception.ToString()); } return table; } /// <summary> /// 讀取Excel檔案特定名字sheet的內容到DataTable /// </summary> /// <param name="strFileName">excel檔案路徑</param> /// <param name="sheet">需要匯出的sheet</param> /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param> /// <param name="dir">excel列名和DataTable列名的對應字典</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 1) { DataTable table = new DataTable(); using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (file.Length > 0) { IWorkbook wb = WorkbookFactory.Create(file); ISheet isheet = wb.GetSheet(SheetName); table = ImportDt(isheet, HeaderRowIndex, dir); isheet = null; } } return table; } /// <summary> /// 讀取Excel檔案某一索引sheet的內容到DataTable /// </summary> /// <param name="strFileName">excel檔案路徑</param> /// <param name="sheet">需要匯出的sheet序號</param> /// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param> /// <param name="dir">excel列名和DataTable列名的對應字典</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 1, int SheetIndex = 0) { DataTable table = new DataTable(); using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (file.Length > 0) { IWorkbook wb = WorkbookFactory.Create(file); ISheet isheet = wb.GetSheetAt(SheetIndex); table = ImportDt(isheet, HeaderRowIndex, dir); isheet = null; } } return table; } #endregion /// <summary> /// 獲取excel檔案的sheet數目 /// </summary> /// <param name="outputFile"></param> /// <returns></returns> public static int GetSheetNumber(string outputFile) { int number = 0; using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read)) { if (readfile.Length > 0) { IWorkbook wb = WorkbookFactory.Create(readfile); number = wb.NumberOfSheets; } } return number; } /// <summary> /// 判斷內容是否是數字 /// </summary> /// <param name="message"></param> /// <param name="result"></param> /// <returns></returns> public static bool isNumeric(String message, out double result) { Regex rex = new Regex(@"^[-]?\d+[.]?\d*$"); result = -1; if (rex.IsMatch(message)) { result = double.Parse(message); return true; } else return false; } /// <summary> /// 驗證匯入的Excel是否有資料 /// </summary> /// <param name="excelFileStream"></param> /// <returns></returns> public static bool HasData(Stream excelFileStream) { using (excelFileStream) { IWorkbook workBook = new HSSFWorkbook(excelFileStream); if (workBook.NumberOfSheets > 0) { ISheet sheet = workBook.GetSheetAt(0); return sheet.PhysicalNumberOfRows > 0; } } return false; } }