NPOI2.1.1生成Excel文件（C#）

NPOI2.1.1生成Excel文件（C#）

几经周折，总结如下：

首先，下载NPOI2.1.1。

之后解压，VS项目中右击引用，然后选中所有dll文件引入到项目中即可。

再之后创建xls文件，这样做：

public void create() { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("Sheet1"); hssfworkbook.CreateSheet("Sheet2"); hssfworkbook.CreateSheet("Sheet3"); //Title IRow row = sheet.CreateRow(0); //创建行对象 ICell cell = row.CreateCell(0); //创建单元格对象 cell.SetCellValue("ddddd"); //设置单元格内容 // 设置单元格字体 ICellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeight = 20 * 20; style.SetFont(font); cell.CellStyle = style; // 合并第0列到第4列的内容 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4)); string filePath = @"C:\Users\Administrator\Desktop\Hello.xls"; FileStream file = new FileStream(filePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); }

运行程序即可看到桌面上有了Hello.xls文件。

---

NPOI导Excel参考

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.Util; using System.IO; using System.Collections.Generic; using System; using System.Data; namespace Project202 { class DataOut { /// <summary> /// /// </summary> public DataOut() { } /// <summary> /// /// </summary> /// <param name="dt"></param> /// <param name="headLine"></param> /// <returns></returns> public int Save(DataTable dt, string[] headLine) { FileStream fileExcel; HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet Sheet1; 设计文件名形如20170630-1，不存在时建立20170630-1文件，若文件无法打开则存为20170630-2文件，以此类推 int largestNum = 0; string fileName; if(dt.TableName =="Data") fileName = DateTime.Now.ToString("导出数据(yyyyMMdd-HH时mm分ss秒)");//文件名的前六位设为当前日期 else fileName = DateTime.Now.ToString("导出日志(yyyyMMdd-HH时mm分ss秒)"); if (Directory.Exists(Setting.savePath) == false) Directory.CreateDirectory(Setting.savePath);//建立路径 else { DirectoryInfo di = new DirectoryInfo(Setting.savePath); foreach (FileInfo file in di.GetFiles("*.xls"))//获取所有后缀为.xls的文件 { if (file.Name.StartsWith(fileName))//检索名以当天日期开头的文件 { int tempNum; try { tempNum = int.Parse(file.Name.Substring(7, (int)file.Name.Length - 11));//获取现存文件中日期20170630-1中-后的序号 largestNum = tempNum > largestNum ? tempNum : largestNum;//在largestNum中记录序号的最大值 } catch { largestNum = 1; } } } } fileName = Setting.savePath + fileName; if (++largestNum > 1) fileName += "-" + (largestNum - 1).ToString() + ".xls"; else fileName += ".xls"; if (workBook.GetSheet("数据") == null) { Sheet1 = (HSSFSheet)workBook.CreateSheet("数据"); } else Sheet1 = (HSSFSheet)workBook.GetSheet("数据"); Sheet1.CreateRow(Sheet1.LastRowNum); Sheet1.SetColumnWidth(0, 24 * 256); Sheet1.SetColumnWidth(1, 13 * 256); Sheet1.SetColumnWidth(2, 13 * 256); HSSFRow sheetRow = (HSSFRow)Sheet1.GetRow(Sheet1.LastRowNum); HSSFCell[] sheetCell = new HSSFCell[dt.Columns.Count]; if (Sheet1.LastRowNum == 0) { HSSFCellStyle headStyle = workBook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont headFont = workBook.CreateFont() as HSSFFont; headFont.FontHeightInPoints = 10; headFont.Boldweight = 700; headStyle.SetFont(headFont); HSSFCell[] headCell = new HSSFCell[headLine.Length]; for (int j = 0; j < headLine.Length; j++) { headCell[j] = (HSSFCell)sheetRow.CreateCell(j); headCell[j].SetCellValue(headLine[j]); headCell[j].CellStyle = headStyle; } Sheet1.CreateRow(Sheet1.LastRowNum + 1); sheetRow = (HSSFRow)Sheet1.GetRow(Sheet1.LastRowNum); } ICellStyle cellStyle = workBook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; //IFont font = workBook.CreateFont(); //font.FontHeightInPoints = 10; //cellStyle.SetFont(font); ICellStyle cellStyleText = workBook.CreateCellStyle(); cellStyleText.Alignment = HorizontalAlignment.Center; //cellStyleText.SetFont(font); cellStyleText.DataFormat = HSSFDataFormat.GetBuiltinFormat("text"); HSSFCellStyle rowStyle = workBook.CreateCellStyle() as HSSFCellStyle; Sheet1.SetDefaultColumnStyle(3, cellStyle); Sheet1.SetDefaultColumnStyle(0, cellStyleText); Sheet1.SetDefaultColumnStyle(1, cellStyleText); Sheet1.SetDefaultColumnStyle(2, cellStyleText); ICellStyle tempStyle = workBook.CreateCellStyle(); tempStyle.CloneStyleFrom(cellStyleText); //tempStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //tempStyle.FillPattern = FillPattern.SolidForeground; IFont tempFont = workBook.CreateFont(); tempFont.Color = HSSFColor.Red.Index; tempStyle.SetFont(tempFont); for (int i = 0;i<dt.Rows.Count;++i) { for (int j = 0; j < headLine.Length; j++) { sheetCell[j] = (HSSFCell)sheetRow.CreateCell(j); string strValue = dt.Rows[i][j].ToString(); switch (dt.Columns[j].DataType.ToString()) { case "System.Int16": //整型 case "System.Int32": case "System.Int64": int intV = 0; int.TryParse(strValue, out intV); sheetCell[j].SetCellValue(intV); break; default: sheetCell[j].SetCellValue(strValue); if (strValue == "True") sheetCell[j].CellStyle = tempStyle; break; } } if (Sheet1.LastRowNum < 65535) Sheet1.CreateRow(Sheet1.LastRowNum + 1); sheetRow = (HSSFRow)Sheet1.GetRow(Sheet1.LastRowNum); } lock (this) { fileExcel = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite); workBook.Write(fileExcel); fileExcel.Close(); return Sheet1.LastRowNum-1; } } } }