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

Myth丶恋晨 2021-07-04 11:33 319阅读 0赞

#### 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;
                }
            }
        }
    }