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;//設置TitletitleRow.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;//設置TitledescRow.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;} View Code

?

?

　　　　

總結

以上是生活随笔為你收集整理的C# 之 用NPOI类库操作Excel的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。