NPOI作为开源免费的组件，功能强大，可用来读写Excel(兼容xls和xlsx两种版本)、Word、PPT文件。可是要让我们记住所有的操作，这便有点困难了，至此，总结一些在开发中常用的针对Excel的简单。
本文地址：
一、NPOI的安装
下载NPOI或是通过Nuget包加入进来，然后在代码中引用如下命名空间，然后开始读写Excel文件。
using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using NPOI.HSSF.UserModel;using NPOI.SS.Util;
二、NPOI写入Excel文件
在NPOI中，使用HSSFWorkbook类类来处理xls结尾的Excel文件(版本在2003及以前)，XSSFWorkbook类来处理xlsx结尾的Excel文件(版本在2007及以后)，都继承自接口IWorkbook，我们可以使用IWorkbook来统一处理两种不同格式的Excel文件。
直接参考相关代码即可，对于合并单元格的跨行跨列操作，无需将被跨掉的行生成新行，合并单元格的信息是单独保存的。设置单元格样式时，请创建一个新的样式对象，不创建将使用默认的样式对象。
1 ///2 /// Datable导出到Excel 3 /// 4 ///5 public static void DataTableToExcel() 6 { 7 //一些已有数据信息 8 bool fileSaved = false; 9 SaveFileDialog saveDialog = new SaveFileDialog 10 { 11 DefaultExt = "xls", 12 Filter = "Excel文件|*.xls", 13 FileName = DateTime.Now.ToString("yyyyMMdd") + "-" + enterpriseTable.Rows[0]["名称"].ToString() + "委托书" 14 }; 15 saveDialog.ShowDialog(); 16 string saveFileName = saveDialog.FileName; 17 if (saveFileName.IndexOf(":") < 0) return; //被点了取消 18 if (saveFileName != "") 19 { 20 try 21 { 22 IWorkbook workbook; 23 string fileExt = System.IO.Path.GetExtension(saveFileName).ToLower(); 24 if (fileExt == ".xlsx") 25 { 26 workbook = new XSSFWorkbook(); 27 } 28 else if (fileExt == ".xls") 29 { 30 workbook = new HSSFWorkbook(); 31 } 32 else 33 { 34 return; 35 } 36 37 ISheet sheet = workbook.CreateSheet("Sheet1"); 38 39 sheet.AddMergedRegion(new CellRangeAddress(0, 3, 0, 9));//合并单元格 40 IRow row = sheet.CreateRow(0);//创建首行 41 ICell cell = row.CreateCell(0);//行中创建第一列 42 cell.SetCellValue("标题"); 43 ICellStyle style = workbook.CreateCellStyle();//设置样式，创建新的style实例，脱离统一样式 44 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置单元格的样式：水平对齐居中 45 style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; 46 47 IFont font = workbook.CreateFont();//新建一个字体样式对象 48 font.Boldweight = short.MaxValue; //设置字体加粗样式 49 style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中 50 cell.CellStyle = style; //将新的样式赋给单元格 51 52 sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 4)); 53 sheet.AddMergedRegion(new CellRangeAddress(4, 4, 6, 9)); 54 row = sheet.CreateRow(4); 55 cell = row.CreateCell(0); 56 cell.SetCellValue("编号:"); 57 cell = row.CreateCell(1); 58 cell.SetCellValue(planCode); 59 cell = row.CreateCell(5); 60 cell.SetCellValue("日期："); 61 cell = row.CreateCell(6); 62 cell.SetCellValue(taskPlantable.Rows[0]["编制日期"].ToString()); 63 64 sheet.AddMergedRegion(new CellRangeAddress(5, 5, 1, 4)); 65 sheet.AddMergedRegion(new CellRangeAddress(5, 5, 6, 9)); 66 row = sheet.CreateRow(5); 67 cell = row.CreateCell(0); 68 cell.SetCellValue("单位:"); 69 cell = row.CreateCell(1); 70 cell.SetCellValue(enterpriseTable.Rows[0]["名称"].ToString()); 71 cell = row.CreateCell(5); 72 cell.SetCellValue("联系人："); 73 cell = row.CreateCell(6); 74 cell.SetCellValue(enterpriseTable.Rows[0]["联系人"].ToString()); 75 76 sheet.AddMergedRegion(new CellRangeAddress(6, 6, 1, 4)); 77 sheet.AddMergedRegion(new CellRangeAddress(6, 6, 6, 9)); 78 row = sheet.CreateRow(6); 79 cell = row.CreateCell(0); 80 cell.SetCellValue("传真:"); 81 cell = row.CreateCell(1); 82 cell.SetCellValue(enterpriseTable.Rows[0]["传真"].ToString()); 83 cell = row.CreateCell(5); 84 cell.SetCellValue("联系电话："); 85 cell = row.CreateCell(6); 86 cell.SetCellValue(enterpriseTable.Rows[0]["电话"].ToString()); 87 88 sheet.AddMergedRegion(new CellRangeAddress(7, 7, 1, 9)); 89 row = sheet.CreateRow(7); 90 cell = row.CreateCell(0); 91 cell.SetCellValue("详细地址:"); 92 cell = row.CreateCell(1); 93 cell.SetCellValue(enterpriseTable.Rows[0]["详细地址"].ToString()); 94 95 int index = 7; 96 //数据 97 for (int i = 0; i < taskProjectTable.Rows.Count; i++) 98 { 99 index++;100 sheet.AddMergedRegion(new CellRangeAddress(index, index, 1, 4));101 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));102 row = sheet.CreateRow(index);103 cell = row.CreateCell(0);104 cell.SetCellValue("名称:");105 cell = row.CreateCell(1);106 cell.SetCellValue(taskProjectTable.Rows[i]["名称"].ToString());107 cell = row.CreateCell(5);108 cell.SetCellValue("类型:");109 cell = row.CreateCell(6);110 cell.SetCellValue(taskProjectTable.Rows[i]["项目类型"].ToString());111 112 index++;113 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 2));114 sheet.AddMergedRegion(new CellRangeAddress(index, index, 3, 5));115 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));116 row = sheet.CreateRow(index);117 cell = row.CreateCell(0);118 cell.SetCellValue("项目");119 cell = row.CreateCell(3);120 cell.SetCellValue("方法");121 cell = row.CreateCell(6);122 cell.SetCellValue("仪器");123 124 //获取数据信息125 DataTable taskDataTable = mysql.GetTableFromSQL(selstr.ToString());126 selstr.Clear();127 for (int j = 0; j < taskDataTable.Rows.Count; j++)128 {129 index++;130 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 2));131 sheet.AddMergedRegion(new CellRangeAddress(index, index, 3, 5));132 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));133 row = sheet.CreateRow(index);134 cell = row.CreateCell(0);135 cell.SetCellValue(taskDataTable.Rows[j]["名称"].ToString());136 cell = row.CreateCell(3);137 cell.SetCellValue(taskDataTable.Rows[j]["方法"].ToString());138 cell = row.CreateCell(6);139 cell.SetCellValue(taskDataTable.Rows[j]["仪器型号"].ToString());140 }141 }142 143 //转为字节数组 144 MemoryStream stream = new MemoryStream();145 workbook.Write(stream);146 var buf = stream.ToArray();147 148 //保存为Excel文件 149 using (FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create, FileAccess.Write))150 {151 fs.Write(buf, 0, buf.Length);152 fs.Flush();153 }154 fileSaved = true;155 }156 catch (Exception ex)157 {158 fileSaved = false;159 MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);160 }161 }162 GC.Collect();//强行销毁163 164 if (fileSaved && File.Exists(saveFileName))165 {166 MessageBox.Show("导出成功！", "通知");167 Process.Start(saveFileName);168 }169 else170 {171 MessageBox.Show("导出失败！", "通知");172 }173 }
三、NPOI读取Excel文件
打开指定Excel文件并读取文件中的内容，加入到DataTable中，或是加入到其它的数据载体中。
1 ///2 /// Excel导入成DataTble 3 /// 4 /// 导入路径(包含文件名与扩展名) 5 ///6 public static DataTable ExcelToTable(string file) 7 { 8 DataTable dt = new DataTable(); 9 IWorkbook workbook;10 string fileExt = Path.GetExtension(file).ToLower();11 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))12 {13 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }14 if (workbook == null) { return null; }15 ISheet sheet = workbook.GetSheetAt(0);16 17 //表头 18 IRow header = sheet.GetRow(sheet.FirstRowNum);19 List columns = new List ();20 for (int i = 0; i < header.LastCellNum; i++)21 {22 object obj = GetValueType(header.GetCell(i));23 if (obj == null || obj.ToString() == string.Empty)24 {25 dt.Columns.Add(new DataColumn("Columns" + i.ToString()));26 }27 else28 dt.Columns.Add(new DataColumn(obj.ToString()));29 columns.Add(i);30 }31 //数据 32 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)33 {34 DataRow dr = dt.NewRow();35 bool hasValue = false;36 foreach (int j in columns)37 {38 dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));39 if (dr[j] != null && dr[j].ToString() != string.Empty)40 {41 hasValue = true;42 }43 }44 if (hasValue)45 {46 dt.Rows.Add(dr);47 }48 }49 }50 return dt;51 }
四、对单元格数据类型的操作
获取目标单元格的数据类型及数据值。
1 ///2 /// 获取单元格类型 3 /// 4 /// 目标单元格 5 ///6 private static object GetValueType(ICell cell) 7 { 8 if (cell == null) 9 return null;10 switch (cell.CellType)11 {12 case CellType.Blank: 13 return null;14 case CellType.Boolean: 15 return cell.BooleanCellValue;16 case CellType.Numeric: 17 return cell.NumericCellValue;18 case CellType.String: 19 return cell.StringCellValue;20 case CellType.Error: 21 return cell.ErrorCellValue;22 case CellType.Formula: 23 default:24 return "=" + cell.CellFormula;25 }26 }
将数据设置到目标单元格中，并设置为指定数据格式。
1 ///2 /// 设置单元格数据类型 3 /// 4 /// 目标单元格 5 /// 数据值 6 ///7 public static void SetCellValue(ICell cell, object obj) 8 { 9 if (obj.GetType() == typeof(int))10 {11 cell.SetCellValue((int)obj);12 }13 else if (obj.GetType() == typeof(double))14 {15 cell.SetCellValue((double)obj);16 }17 else if (obj.GetType() == typeof(IRichTextString))18 {19 cell.SetCellValue((IRichTextString)obj);20 }21 else if (obj.GetType() == typeof(string))22 {23 cell.SetCellValue(obj.ToString());24 }25 else if (obj.GetType() == typeof(DateTime))26 {27 cell.SetCellValue((DateTime)obj);28 }29 else if (obj.GetType() == typeof(bool))30 {31 cell.SetCellValue((bool)obj);32 }33 else34 {35 cell.SetCellValue(obj.ToString());36 }37 }
本文地址：
2018-09-11,望技术有成后能回来看见自己的脚步