C#之用NPOI类库操作Excel

C#之⽤NPOI类库操作Excel
1、需引⽤以下命名空间：
using erModel;
using erModel;
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("/") || row["SUrl"].ToString().Contains("/"))
{
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;
}
View Code。