摘要：
=null）｛cellstyle.FillForegroundColor=FillForegroundColor.GetIndex（）；｝如果（fillBackgroundColor！
1、需引用以下命名空间：
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HPSF; using NPOI.HSSF.Util;
2、接下来在内存中生成一个Excel文件，代码如下：
HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet("Sheet1");
3、然后在新创建的sheet里面，创建我们的行和列，代码如下：
IRow row = sheet.CreateRow(index); //index代表多少行 row.HeightInPoints = 35; //行高 ICell cell = row.CreateCell(0); //创建第一列 cell.SetCellValue("设置单元格的值"); //设置单元格的值
4、设置单元格的样式已经字体大小，边框，以及合并单元格
(1).创建单元格字体的样式及大小
/// <summary> /// 获取字体样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="fontname">字体名</param> /// <param name="fontcolor">字体颜色</param> /// <param name="fontsize">字体大小</param> /// <returns></returns> public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize) { IFont font1 = hssfworkbook.CreateFont(); if (string.IsNullOrEmpty(fontfamily)) { font1.FontName = fontfamily; } if (fontcolor != null) { font1.Color = fontcolor.GetIndex(); } font1.IsItalic = true; font1.FontHeightInPoints = (short)fontsize; return font1; }
(2).设置单元格内显示数据的格式
ICell cell = row.CreateCell(1); ICellStyle cellStyleNum = Excel.GetICellStyle(book); IDataFormat formatNum = book.CreateDataFormat(); cellStyleNum.DataFormat = formatNum.GetFormat("0.00E+00");//设置单元格的格式为科学计数法cell.CellStyle = cellStyleNum;
(3).创建单元格的边框，背景颜色，以及对齐方式
/// <summary> /// 获取单元格样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPatternType fillPattern,
HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.FillPattern = fillPattern; cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (fillForegroundColor != null) { cellstyle.FillForegroundColor = fillForegroundColor.GetIndex(); } if (fillBackgroundColor != null) { cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex(); } if (font != null) { cellstyle.SetFont(font); } //有边框 cellstyle.BorderBottom = CellBorderType.THIN; cellstyle.BorderLeft = CellBorderType.THIN; cellstyle.BorderRight = CellBorderType.THIN; cellstyle.BorderTop = CellBorderType.THIN; return cellstyle; }
(4).合并单元格
/// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); }
5、将Excel文件输出
FileStream stream = File.OpenWrite(@"F:/test.xls"); ; book.Write(stream); stream.Close();
6、完整示例：
public MemoryStream RenderToExcelZBNew(DataTable table, string strHeaderText, string strDescText) { MemoryStream ms = new MemoryStream(); using (table) { using (IWorkbook workbook = new HSSFWorkbook()) { using (HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet()) { //创建标题行 IRow titleRow = sheet.CreateRow(0); //设置行高 titleRow.HeightInPoints = 45; //设置Title titleRow.CreateCell(0).SetCellValue(strHeaderText); //设置样式 titleRow.GetCell(0).CellStyle = CellStyle(workbook, CellStyleEnum.Title); //合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 13)); //设置边框 sheet.SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, table.Rows.Count + 3, 0, 13), CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); //创建描述行 IRow descRow = sheet.CreateRow(1); //设置行高 descRow.HeightInPoints = 50; //设置Title descRow.CreateCell(0).SetCellValue(strDescText); //设置样式 descRow.GetCell(0).CellStyle = CellStyle(workbook, CellStyleEnum.Desc); //合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 13)); sheet.SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 13), CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); IRow headerRow = sheet.CreateRow(2); //设置行高 headerRow.HeightInPoints = 23; headerRow.CreateCell(0).SetCellValue("序号"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 0, 0)); headerRow.GetCell(0).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(1).SetCellValue("日期"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 1, 1)); headerRow.GetCell(1).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(2).SetCellValue("时间"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 2, 2)); headerRow.GetCell(2).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(3).SetCellValue("事件"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 3, 4)); headerRow.GetCell(3).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(5).SetCellValue("媒体"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 5, 5)); headerRow.GetCell(5).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(6).SetCellValue("研判"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 6, 6)); headerRow.GetCell(6).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); //headerRow.CreateCell(7).SetCellValue("风险等级"); //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 7, 7)); //headerRow.GetCell(7).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(7).SetCellValue("责任单位"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 7, 7)); headerRow.GetCell(7).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(8).SetCellValue("落实部门"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 8, 8)); headerRow.GetCell(8).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(9).SetCellValue("处置"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 9, 10)); headerRow.GetCell(9).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(11).SetCellValue("话题"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 11, 12)); headerRow.GetCell(11).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow.CreateCell(13).SetCellValue("地址"); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 13, 13)); headerRow.GetCell(13).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); IRow headerRow2 = sheet.CreateRow(3); headerRow2.HeightInPoints = 25; headerRow2.CreateCell(0); headerRow2.GetCell(0).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(1); headerRow2.GetCell(1).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(2); headerRow2.GetCell(2).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(7); headerRow2.GetCell(7).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(8); headerRow2.GetCell(8).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(9); headerRow2.GetCell(9).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(3).SetCellValue("标题"); headerRow2.GetCell(3).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(4).SetCellValue("摘要"); headerRow2.GetCell(4).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(5).SetCellValue("名称"); headerRow2.GetCell(5).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(6).SetCellValue("风险等级"); headerRow2.GetCell(6).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(9).SetCellValue("调查落实"); headerRow2.GetCell(9).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(10).SetCellValue("恢复引导"); headerRow2.GetCell(10).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(11).SetCellValue("类别"); headerRow2.GetCell(11).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(12).SetCellValue("关键词一"); headerRow2.GetCell(12).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); headerRow2.CreateCell(13); headerRow2.GetCell(13).CellStyle = CellStyle(workbook, CellStyleEnum.Head2); sheet.SetColumnWidth(0, 4 * 256); sheet.SetColumnWidth(1, 6 * 256); sheet.SetColumnWidth(2, 6 * 256); sheet.SetColumnWidth(3, 25 * 256); sheet.SetColumnWidth(4, 25 * 256); sheet.SetColumnWidth(5, 5 * 256); sheet.SetColumnWidth(6, 5 * 256); sheet.SetColumnWidth(7, 5 * 256); sheet.SetColumnWidth(8, 5 * 256); sheet.SetColumnWidth(9, 18 * 256); sheet.SetColumnWidth(10, 18 * 256); sheet.SetColumnWidth(11, 6 * 256); sheet.SetColumnWidth(12, 6 * 256); sheet.SetColumnWidth(13, 18 * 256); // 行号 int rowIndex = 4; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); //dataRow.HeightInPoints = 70;//行高 int[] arrLenght = new int[3]; arrLenght[0] = row["SContent"].ToString().Length; arrLenght[1] = row["STitle"].ToString().Length; arrLenght[2] = row["SUrl"].ToString().Length; if (arrLenght[0] > arrLenght[1]) { if (arrLenght[0] > arrLenght[2]) { //arrLenght[0] 最大 dataRow.HeightInPoints = arrLenght[0] + 15; } else { //arrLenght[2] 最大 dataRow.HeightInPoints = arrLenght[2] + 10; } } else if (arrLenght[1] > arrLenght[2]) { //arrLenght[1] 最大 dataRow.HeightInPoints = arrLenght[1] + 15; } else { //arrLenght[2] 最大 dataRow.HeightInPoints = arrLenght[2] + 10; } dataRow.CreateCell(0, CellType.STRING).SetCellValue(rowIndex - 3); dataRow.CreateCell(1, CellType.STRING).SetCellValue(Convert.ToDateTime(row["SPostTime"]).ToString("MM.dd")); dataRow.CreateCell(2, CellType.STRING).SetCellValue(Convert.ToDateTime(row["SPostTime"]).ToString("HH:mm")); dataRow.CreateCell(3, CellType.STRING).SetCellValue(row["STitle"].ToString()); dataRow.CreateCell(4, CellType.STRING).SetCellValue(row["SContent"].ToString()); dataRow.CreateCell(5, CellType.STRING).SetCellValue(row["SMedia"].ToString()); if (row["SRank"].ToString() == "0") { dataRow.CreateCell(6, CellType.STRING).SetCellValue(""); } else { dataRow.CreateCell(6, CellType.STRING).SetCellValue(_SGSentimentBLL.RankTitle(Convert.ToInt32(row["SRank"]))); } if (!String.IsNullOrEmpty(row["SZone"].ToString())) { dataRow.CreateCell(7, CellType.STRING).SetCellValue(row["SZone"].ToString().Substring(0, 2) + "公司"); } else { dataRow.CreateCell(7, CellType.STRING).SetCellValue(row["SZone"].ToString()); } dataRow.CreateCell(8, CellType.STRING).SetCellValue(row["SAdvanceDeptName"].ToString()); dataRow.CreateCell(9, CellType.STRING).SetCellValue(""); dataRow.CreateCell(10, CellType.STRING).SetCellValue(""); dataRow.CreateCell(11, CellType.STRING).SetCellValue(row["TypeName"].ToString()); dataRow.CreateCell(12, CellType.STRING).SetCellValue(row["IssueName"].ToString()); if (row["SUrl"].ToString().Contains("http://t.qq.com/") || row["SUrl"].ToString().Contains("http://weibo.com/")) { if (row["SUrl"].ToString().Length > 50) { dataRow.CreateCell(13, CellType.STRING).SetCellValue(row["SUrl"].ToString().Substring(50)); } else { dataRow.CreateCell(13, CellType.STRING).SetCellValue(row["SUrl"].ToString()); } } else { dataRow.CreateCell(13, CellType.STRING).SetCellValue(row["SUrl"].ToString()); } ICellStyle cellStyle = CellStyle(workbook, CellStyleEnum.Content2); dataRow.GetCell(0).CellStyle = cellStyle; dataRow.GetCell(1).CellStyle = cellStyle; dataRow.GetCell(2).CellStyle = cellStyle; dataRow.GetCell(3).CellStyle = cellStyle; dataRow.GetCell(4).CellStyle = cellStyle; dataRow.GetCell(5).CellStyle = cellStyle; dataRow.GetCell(6).CellStyle = cellStyle; dataRow.GetCell(7).CellStyle = cellStyle; dataRow.GetCell(8).CellStyle = cellStyle; dataRow.GetCell(9).CellStyle = cellStyle; dataRow.GetCell(10).CellStyle = cellStyle; dataRow.GetCell(11).CellStyle = cellStyle; dataRow.GetCell(12).CellStyle = cellStyle; dataRow.GetCell(13).CellStyle = cellStyle; rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; } } } return ms; }