C# 之用NPOI类库操作Excel

2022-11-05 11:48:13

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 = ; //行高

ICell cell = row.CreateCell(); //创建第一列

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();

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();

                    //设置行高

                    titleRow.HeightInPoints = ;

                    //设置Title

                    titleRow.CreateCell().SetCellValue(strHeaderText);

                    //设置样式

                    titleRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Title);

                    //合并单元格

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    //设置边框

                    sheet.SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(, table.Rows.Count + , , ), CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

                    //创建描述行

                    IRow descRow = sheet.CreateRow();

                    //设置行高

                    descRow.HeightInPoints = ;

                    //设置Title

                    descRow.CreateCell().SetCellValue(strDescText);

                    //设置样式

                    descRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Desc);

                    //合并单元格

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    sheet.SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(, , , ), CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

                    IRow headerRow = sheet.CreateRow();

                    //设置行高

                    headerRow.HeightInPoints = ;

                    headerRow.CreateCell().SetCellValue("序号");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("日期");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("时间");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("事件");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("媒体");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("研判");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().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().SetCellValue("责任单位");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("落实部门");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("处置");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("话题");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow.CreateCell().SetCellValue("地址");

                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

                    headerRow.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    IRow headerRow2 = sheet.CreateRow();

                    headerRow2.HeightInPoints = ;

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("标题");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("摘要");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("名称");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("风险等级");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("调查落实");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("恢复引导");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("类别");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell().SetCellValue("关键词一");

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    headerRow2.CreateCell();

                    headerRow2.GetCell().CellStyle = CellStyle(workbook, CellStyleEnum.Head2);

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    sheet.SetColumnWidth(,  * );

                    // 行号

                    int rowIndex = ;

                    foreach (DataRow row in table.Rows)

                    {

                        IRow dataRow = sheet.CreateRow(rowIndex);

                        //dataRow.HeightInPoints = 70;//行高

                        int[] arrLenght = new int[];

                        arrLenght[] = row["SContent"].ToString().Length;

                        arrLenght[] = row["STitle"].ToString().Length;

                        arrLenght[] = row["SUrl"].ToString().Length;

                        if (arrLenght[] > arrLenght[])

                        {

                            if (arrLenght[] > arrLenght[])

                            {

                                //arrLenght[0] 最大

                                dataRow.HeightInPoints = arrLenght[] + ;

                            }

                            else

                            {

                                //arrLenght[2] 最大

                                dataRow.HeightInPoints = arrLenght[] + ;

                            }

                        }

                        else if (arrLenght[] > arrLenght[])

                        {

                            //arrLenght[1] 最大

                            dataRow.HeightInPoints = arrLenght[] + ;

                        }

                        else

                        {

                            //arrLenght[2] 最大

                            dataRow.HeightInPoints = arrLenght[] + ;

                        }

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(rowIndex - );

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(Convert.ToDateTime(row["SPostTime"]).ToString("MM.dd"));

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(Convert.ToDateTime(row["SPostTime"]).ToString("HH:mm"));

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(row["STitle"].ToString());

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SContent"].ToString());

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SMedia"].ToString());

                        if (row["SRank"].ToString() == "")

                        {

                            dataRow.CreateCell(, CellType.STRING).SetCellValue("");

                        }

                        else

                        {

                            dataRow.CreateCell(, CellType.STRING).SetCellValue(_SGSentimentBLL.RankTitle(Convert.ToInt32(row["SRank"])));

                        }

                        if (!String.IsNullOrEmpty(row["SZone"].ToString()))

                        {

                            dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SZone"].ToString().Substring(, ) + "公司");

                        }

                        else

                        {

                            dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SZone"].ToString());

                        }

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SAdvanceDeptName"].ToString());

                        dataRow.CreateCell(, CellType.STRING).SetCellValue("");

                        dataRow.CreateCell(, CellType.STRING).SetCellValue("");

                        dataRow.CreateCell(, CellType.STRING).SetCellValue(row["TypeName"].ToString());

                        dataRow.CreateCell(, 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 > )

                            {

                                dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SUrl"].ToString().Substring());

                            }

                            else

                            {

                                dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SUrl"].ToString());

                            }

                        }

                        else

                        {

                            dataRow.CreateCell(, CellType.STRING).SetCellValue(row["SUrl"].ToString());

                        }

                        ICellStyle cellStyle = CellStyle(workbook, CellStyleEnum.Content2);

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        dataRow.GetCell().CellStyle = cellStyle;

                        rowIndex++;

                    }

                    workbook.Write(ms);

                    ms.Flush();

                    ms.Position = ;

                }

            }

        }

        return ms;

    }

码农公寓

相关文章