C#使用NPOI导出Excel文件
一、NPOI简介
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
NPOI官方教程地址：http://tonyqus.sinaapp.com
googlecode：http://code.google.com/p/npoi/
codeplex：http://npoi.codeplex.com/
二、导出Excel帮助类
1 /*** 2 * Title：" " 项目 3 * 主题：用NPOI导出Excel文件 4 * Description： 5 * 功能：XXX 6 * Date：2019 7 * Version：0.1版本 8 * Author：Coffee 9 * Modify Recoder： 10 */ 11 12 using System; 13 using System.Data; 14 using System.IO; 15 using System.Text; 16 using NPOI.HPSF; 17 using NPOI.HSSF.UserModel; 18 using NPOI.SS.UserModel; 19 20 namespace kernal 21 { 22 public class NPOIHelper 23 { 24 #region 文件属性信息 25 static string companyName = "测试公司"; //公司名称 26 static string subject = "主题名称"; //主题名称 27 static string author = "测试作者"; //作者名称 28 29 #endregion 30 31 32 /// <summary> 33 /// Excel文件设置与数据处理 34 /// </summary> 35 /// <param name="table">数据表</param> 36 /// <param name="headerText">头部文本</param> 37 /// <param name="sheetName">表的名称</param> 38 /// <param name="columnName">数据列名称</param> 39 /// <param name="columnTitle">表标题</param> 40 /// <returns></returns> 41 public static HSSFWorkbook ExcelSettingsAndHandleData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle) 42 { 43 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); 44 ISheet sheet = hssfworkbook.CreateSheet(sheetName); 45 46 #region 设置文件属性信息 47 48 //创建一个文档摘要信息实体。 49 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 50 dsi.Company = companyName; //公司名称 51 hssfworkbook.DocumentSummaryInformation = dsi; 52 53 //创建一个摘要信息实体。 54 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 55 si.Subject = subject; 56 si.Author = author; 57 si.Title = headerText; 58 si.Subject = headerText; 59 si.CreateDateTime = DateTime.Now; 60 hssfworkbook.SummaryInformation = si; 61 62 #endregion 63 64 ICellStyle dateStyle = hssfworkbook.CreateCellStyle(); 65 IDataFormat format = hssfworkbook.CreateDataFormat(); 66 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); 67 68 #region 取得列宽 69 70 int[] colWidth = new int[columnName.Length]; 71 for (int i = 0; i < columnName.Length; i++) 72 { 73 colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length; 74 } 75 for (int i = 0; i < table.Rows.Count; i++) 76 { 77 for (int j = 0; j < columnName.Length; j++) 78 { 79 int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length; 80 if (intTemp > colWidth[j]) 81 { 82 colWidth[j] = intTemp; 83 } 84 } 85 } 86 87 #endregion 88 89 int rowIndex = 0; 90 foreach (DataRow row in table.Rows) 91 { 92 #region 新建表，填充表头，填充列头，样式 93 if (rowIndex == 65535 || rowIndex == 0) 94 { 95 if (rowIndex != 0) 96 { 97 sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString()); 98 } 99 100 #region 表头及样式 101 //if (!string.IsNullOrEmpty(headerText)) 102 { 103 IRow headerRow = sheet.CreateRow(0); 104 headerRow.HeightInPoints = 25; 105 headerRow.CreateCell(0).SetCellValue(headerText); 106 107 ICellStyle headStyle = hssfworkbook.CreateCellStyle(); 108 headStyle.Alignment = HorizontalAlignment.Center; 109 IFont font = hssfworkbook.CreateFont(); 110 font.FontHeightInPoints = 20; 111 font.Boldweight = 700; 112 headStyle.SetFont(font); 113 114 headerRow.GetCell(0).CellStyle = headStyle; 115 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1)); 116 117 118 } 119 #endregion 120 121 #region 列头及样式 122 { 123 IRow headerRow; 124 125 headerRow = sheet.CreateRow(1); 126 127 ICellStyle headStyle = hssfworkbook.CreateCellStyle(); 128 headStyle.Alignment = HorizontalAlignment.Center; 129 IFont font = hssfworkbook.CreateFont(); 130 font.FontHeightInPoints = 10; 131 font.Boldweight = 700; 132 headStyle.SetFont(font); 133 134 for (int i = 0; i < columnName.Length; i++) 135 { 136 headerRow.CreateCell(i).SetCellValue(columnTitle[i]); 137 headerRow.GetCell(i).CellStyle = headStyle; 138 //设置列宽 139 if ((colWidth[i] + 1) * 256 > 30000) 140 { 141 sheet.SetColumnWidth(i, 10000); 142 } 143 else 144 { 145 sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256); 146 } 147 } 148 149 } 150 #endregion 151 152 rowIndex = 2; 153 154 155 } 156 #endregion 157 158 #region 填充数据 159 160 IRow dataRow = sheet.CreateRow(rowIndex); 161 for (int i = 0; i < columnName.Length; i++) 162 { 163 ICell newCell = dataRow.CreateCell(i); 164 165 string drValue = row[columnName[i]].ToString(); 166 167 switch (table.Columns[columnName[i]].DataType.ToString()) 168 { 169 case "System.String"://字符串类型 170 if (drValue.ToUpper() == "TRUE") 171 newCell.SetCellValue("是"); 172 else if (drValue.ToUpper() == "FALSE") 173 newCell.SetCellValue("否"); 174 newCell.SetCellValue(drValue); 175 break; 176 case "System.DateTime"://日期类型 177 DateTime dateV; 178 DateTime.TryParse(drValue, out dateV); 179 newCell.SetCellValue(dateV); 180 181 newCell.CellStyle = dateStyle;//格式化显示 182 break; 183 case "System.Boolean"://布尔型 184 bool boolV = false; 185 bool.TryParse(drValue, out boolV); 186 if (boolV) 187 newCell.SetCellValue("是"); 188 else 189 newCell.SetCellValue("否"); 190 break; 191 case "System.Int16"://整型 192 case "System.Int32": 193 case "System.Int64": 194 case "System.Byte": 195 int intV = 0; 196 int.TryParse(drValue, out intV); 197 newCell.SetCellValue(intV); 198 break; 199 case "System.Decimal"://浮点型 200 case "System.Double": 201 double doubV = 0; 202 double.TryParse(drValue, out doubV); 203 newCell.SetCellValue(doubV); 204 break; 205 case "System.DBNull"://空值处理 206 newCell.SetCellValue(""); 207 break; 208 default: 209 newCell.SetCellValue(""); 210 break; 211 } 212 213 } 214 215 #endregion 216 217 rowIndex++; 218 } 219 220 return hssfworkbook; 221 } 222 223 224 /// <summary> 225 /// 将数据写入Excel文件 226 /// </summary> 227 /// <param name="hssfworkbook">工作簿</param> 228 /// <param name="exportPath">文件导出路径</param> 229 private static void WirteToFile(HSSFWorkbook hssfworkbook,string exportPath) 230 { 231 FileStream file = null; 232 try 233 { 234 file = new FileStream(exportPath + ".xls", FileMode.Create); 235 hssfworkbook.Write(file); 236 } 237 catch (Exception e) 238 { 239 throw e; 240 } 241 finally 242 { 243 file.Dispose(); 244 hssfworkbook.Close(); 245 } 246 247 } 248 249 /// <summary> 250 /// 导出Excel文件 251 /// </summary> 252 /// <param name="table">数据表</param> 253 /// <param name="headerText">头部文本。</param> 254 /// <param name="sheetName">工作表名称</param> 255 /// <param name="columnName">数据列对应字段名称。</param> 256 /// <param name="columnTitle">数据列对应字段的中文名称</param> 257 /// <param name="fileName">文件名称</param> 258 /// <param name="exportFilePath">导出的文件路径</param> 259 public static void ExportExcel(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle, string fileName,string exportFilePath) 260 { 261 //Excel文件设置与数据处理 262 HSSFWorkbook hssfworkbook = ExcelSettingsAndHandleData(table, headerText, sheetName, columnName, columnTitle); 263 //将数据写入Excel文件 264 WirteToFile(hssfworkbook, exportFilePath+fileName); 265 } 266 267 }//Class_end 268 }
三、导出Excel帮助类的使用方法
1 /*** 2 * Title：" " 项目 3 * 主题：XXX 4 * Description： 5 * 功能：XXX 6 * Date：2019 7 * Version：0.1版本 8 * Author：Coffee 9 * Modify Recoder： 10 */ 11 12 using Control; 13 using Global; 14 using kernal; 15 using NPOI.SS.Formula.Functions; 16 using System.Collections; 17 using System.Collections.Generic; 18 using System.Data; 19 using UnityEngine; 20 21 namespace TestExport 22 { 23 public class Test_ExportExcel : MonoBehaviour 24 { 25 void Start() 26 { 27 28 } 29 30 private void Update() 31 { 32 if (Input.GetKeyDown(KeyCode.O)) 33 { 34 //导出Excle文件 35 ExportExcel(); 36 } 37 } 38 39 //导出Excle文件 40 private void ExportExcel() 41 { 42 string path = @"C:\Users\CoffeeMilk\Desktop\新建文件夹\测试\"; 43 ExportData(path); 44 } 45 46 //人员信息 47 private DataTable PeopleInfo() 48 { 49 DataTable dtAllPeopleInfoData = new DataTable(); 50 51 dtAllPeopleInfoData.Columns.Add("Number"); //数据序号 52 dtAllPeopleInfoData.Columns.Add("ID"); //用户编号 53 dtAllPeopleInfoData.Columns.Add("UserName"); //用户名称 54 dtAllPeopleInfoData.Columns.Add("TelNumber"); //联系电话 55 dtAllPeopleInfoData.Columns.Add("UserDealer"); //公司名称 56 dtAllPeopleInfoData.Columns.Add("DealerAddress"); //公司地址 57 dtAllPeopleInfoData.Columns.Add("RegisterTime"); //注册时间 58 59 DataRow row = null; 60 int peopleTotalNumbers = 0; //当前导出人员信息的总数 61 List<Ctrl_PeopleInfoData> _PeopleInfoDatas = new List<Ctrl_PeopleInfoData>();//当前需要导出的人员信息集合 62 peopleTotalNumbers = Ctrl_CommonPeopleInfoOperation.GetInstance().QueryPeopleInfoTotalNumbers();//人员总数 63 _PeopleInfoDatas = Ctrl_CommonPeopleInfoOperation.GetInstance().QueryAllPeopleInfoOfIndexAndDisplyNumbers(0, peopleTotalNumbers); 64 65 66 67 for (int i = 0; i < peopleTotalNumbers; i++) 68 { 69 row = dtAllPeopleInfoData.NewRow(); 70 row["Number"] = i + 1; 71 row["ID"] = _PeopleInfoDatas[i].ID; 72 row["UserName"] = _PeopleInfoDatas[i].UserName; 73 row["TelNumber"] = _PeopleInfoDatas[i].TelNumber; 74 row["UserDealer"] = _PeopleInfoDatas[i].UserDealer; 75 row["DealerAddress"] = _PeopleInfoDatas[i].DealerAddress; 76 row["RegisterTime"] = _PeopleInfoDatas[i].RegisterTime; 77 dtAllPeopleInfoData.Rows.Add(row); 78 } 79 80 return dtAllPeopleInfoData; 81 82 83 } 84 85 86 87 //维修计划 88 private DataTable RepairPlan() 89 { 90 DataTable dtAllInfoData = new DataTable(); 91 dtAllInfoData.Columns.Add("Number"); //数据序号 92 dtAllInfoData.Columns.Add("RepairPartName"); //维修部件 93 dtAllInfoData.Columns.Add("RepairTime"); //维修时间 94 dtAllInfoData.Columns.Add("RepairName"); //维修人员 95 dtAllInfoData.Columns.Add("IsRepair"); //是否维修 96 dtAllInfoData.Columns.Add("IsPass"); //是否通过 97 dtAllInfoData.Columns.Add("RepairContent"); //维修内容 98 99 //dtAllInfoData.Columns.Add("AuditorName"); //审核人员 100 101 DataRow row = null; 102 int TotalNumbers = 0; //当前导出维修计划信息的总数 103 List<Ctrl_View_RepairPlan_Data> _View_RepairPlan_Datas = new List<Ctrl_View_RepairPlan_Data>();//当前需要导出的维修计划信息集合 104 105 TotalNumbers = Ctrl_CommonView_RepairPlanOperation.GetInstance().QueryRepairPlanTotalNumbers();//维修计划总数 106 _View_RepairPlan_Datas = Ctrl_CommonView_RepairPlanOperation.GetInstance().QueryAllRepairPlanOfIndex(0, TotalNumbers); 107 108 for (int i = 0; i < TotalNumbers; i++) 109 { 110 row = dtAllInfoData.NewRow(); 111 row["Number"] = i + 1; 112 row["RepairPartName"] = _View_RepairPlan_Datas[i].RepairPartName; 113 row["RepairTime"] = _View_RepairPlan_Datas[i].RepairTime; 114 row["RepairName"] = _View_RepairPlan_Datas[i].RepairName; 115 row["IsRepair"] = _View_RepairPlan_Datas[i].IsRepair; 116 row["IsPass"] = _View_RepairPlan_Datas[i].IsPass; 117 row["RepairContent"] = _View_RepairPlan_Datas[i].RepairContent; 118 119 //row["AuditorName"] = _View_RepairPlan_Datas[i].AuditorName; 120 dtAllInfoData.Rows.Add(row); 121 } 122 return dtAllInfoData; 123 } 124 125 private void ExportData(string filepath) 126 { 127 128 //DataTable table = PeopleInfo(); 129 DataTable table = RepairPlan(); 130 131 132 //string[] strFields = { "Number", "ID", "UserName", "TelNumber", "UserDealer", "DealerAddress", "RegisterTime"}; 133 //string[] strFieldsName = {"序号", "编号", "用户名称", "联系电话", "使用厂商", "使用商地址", "注册时间"}; 134 //NPOIHelper.Write(table, "人员信息表", "人员信息表", strFields, strFieldsName, "人员信息表", filepath); 135 136 string[] strFields = { "Number", "RepairPartName", "RepairTime", "RepairName", "IsRepair", "IsPass", "RepairContent" }; 137 string[] strFieldsName = { "序号", "维修部件名称", "维修时间", "维修人员", "是否维修", "是否通过", "维修内容" }; 138 NPOIHelper.ExportExcel(table, "维修计划表", "维修维修计划表", strFields, strFieldsName, "维修计划表", filepath); 139 140 Debug.Log("输出完成"); 141 } 142 } 143 }
四、导出效果图如下
NPOI导出Excel表功能实现(多个工作簿)：http://www.cnblogs.com/zhengjuzhuan/archive/2010/02/01/1661103.html
在 Server 端存取 Excel 檔案的利器：NPOI Library：http://msdn.microsoft.com/zh-tw/ee818993.aspx
ASP.NET使用NPOI类库导出Excel：http://www.cnblogs.com/niunan/archive/2010/03/30/1700706.html
