你的位置:首页 > ASP.net教程

[ASP.net教程]总结一下工作中遇到的NPOI以及在ASP.NET MVC中的使用


1.前言

相信大家在工作中经常要遇到一些导入导出Execl操作。学习贵在分享,分享使人快乐,园子里的前辈已经有很多好的文章,鄙人也是能力有限,在这里把这些好的文章总结,方便以后再工作中使用。

NPOI:是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。

          NPOI是构建在POI 3.x版本之上的,它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

          它不使用Office COM组件(Microsoft.Office.Interop.XXX.dll),不需要安装Microsoft Office,支持对Office 97-2003的文件格式,功能比较强大。

          能够读写几乎所有的Office 97-2003文件格式,至少能够支持Word, PowerPoint, Excel, Visio的格式。

NPOI官方网站【点击这里】

         1、整个Excel表格叫做工作表:WorkBook(工作薄),包含的叫页(工作表):Sheet;行:Row;单元格Cell。

         2、NPOI是POI的C#版本,NPOI的行和列的index都是从0开始

         3、POI读取Excel有两种格式一个是HSSF,另一个是XSSF。

         HSSF和XSSF的区别如下: 

                HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls 

                XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx

                即:HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

          HSSFWorkbook对应的就是Excel文件  工作簿,

           HSSFSheet对应的就是Excel中sheet 工作表,

          HSSFCell对应的就是Excel的单元格,

          HSSFRow对应的就是Excel的行

.NET调用NPOI组件导入导出Excel的操作类

     此NPOI操作类的优点如下:
       (1)支持web及winform从DataTable导出到Excel; 
       (2)生成速度很快; 
       (3)准确判断数据类型,不会出现身份证转数值等问题; 
       (4)如果单页条数大于65535时会新建工作表; 
       (5)列宽自适应;

2.简单用法

namespace 导入导出{  public partial class Form1 : Form  {    public Form1()    {      InitializeComponent();    }    private void btnInput_Click(object sender, EventArgs e)    {      #region 导入到DataTable      using (FileStream stream = File.OpenRead("huangjinfeng.xls"))      {        IWorkbook workbook = new HSSFWorkbook(stream);        ISheet sheet = workbook.GetSheet("员工捐款信息表");        DataTable table = new DataTable();        IRow headerRow = sheet.GetRow(0);        int cellCount = headerRow.LastCellNum;        int rowCount = sheet.LastRowNum;        for (int i = headerRow.FirstCellNum; i < cellCount; i++)        {          DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);          table.Columns.Add(column);        }        for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)        {          IRow row = sheet.GetRow(i);          DataRow dataRow = table.NewRow();          if (row != null)          {            for (int j = row.FirstCellNum; j < cellCount; j++)            {              if (row.GetCell(j) != null)                dataRow[j] = row.GetCell(j);            }          }          table.Rows.Add(dataRow);        }        this.dataGridView1.DataSource = table;        MessageBox.Show("导入数据成功");      }      #endregion      #region 导入到数据库      //using (FileStream stream = File.OpenRead("huangjinfeng.xls"))      //{      //  string sql="INSERT INTO [dbo].[DonationDetail]([dUserName],[dcID],[dAmount],[dDate],[disdelete],[dCreateTime])";      //  RenderToDb(stream,sql);      //  MessageBox.Show("SQL");      //}      #endregion    }    private void btnOut_Click(object sender, EventArgs e)    {      DonationEntities2 db = new DonationEntities2();      var lists = db.CreateObjectSet<DonationDetail>().Where(c => c.disdelete == 0).ToList();      #region 自由导出      HSSFWorkbook workbook = new HSSFWorkbook();      //2.创建工作表      ISheet sheet = workbook.CreateSheet("员工捐款信息表");      IRow rowHeader = sheet.CreateRow(0);      rowHeader.CreateCell(0, CellType.String).SetCellValue("DId");      rowHeader.CreateCell(1, CellType.String).SetCellValue("员工姓名");      rowHeader.CreateCell(2, CellType.String).SetCellValue("DcId");      rowHeader.CreateCell(3, CellType.String).SetCellValue("捐款金额");      rowHeader.CreateCell(4, CellType.String).SetCellValue("捐款日期");      rowHeader.CreateCell(5, CellType.String).SetCellValue("是否删除");      rowHeader.CreateCell(6, CellType.String).SetCellValue("创建日期");      for (int i = 0; i < lists.Count; i++)      {        IRow row = sheet.CreateRow(i + 1);        //为指定的行添加列        row.CreateCell(0, CellType.String).SetCellValue(lists[i].dId);        row.CreateCell(1, CellType.String).SetCellValue(lists[i].dUserName);        row.CreateCell(2, CellType.String).SetCellValue(lists[i].dcID);        row.CreateCell(3, CellType.String).SetCellValue(lists[i].dAmount.ToString());        row.CreateCell(4, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dDate.ToString()));        row.CreateCell(5, CellType.String).SetCellValue(lists[i].disdelete);        row.CreateCell(6, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dCreateTime.ToString()));      }      //使用文件流做数据的写入      using (FileStream fss = new FileStream("huangjinfeng.xls", FileMode.Create))      {        workbook.Write(fss);      }      MessageBox.Show("导出数据成功");      #endregion    }
    #region 导入到数据库    public static void RenderToDb(Stream excelFileStream, string insertSql)    {      using (excelFileStream)      {        IWorkbook workbook = new HSSFWorkbook(excelFileStream);        ISheet sheet = workbook.GetSheetAt(0);//取第一个工作表        StringBuilder builder = new StringBuilder();        IRow headerRow = sheet.GetRow(0);//第一行为标题行        int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells        int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1        for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)        {          IRow row = sheet.GetRow(i);          if (row != null)          {            builder.Append(insertSql);            builder.Append(" values (");            for (int j = row.FirstCellNum; j < cellCount; j++)            {              builder.AppendFormat("'{0}',", row.GetCell(j)).Replace("'", "''");            }            builder.Length = builder.Length - 1;            builder.Append(");");          }          if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)          {            //每50条记录一次批量插入到数据库            //rowAffected += dbAction(builder.ToString());            SqlHelper.ExecuteNonQuery(builder.ToString());            builder.Length = 0;          }        }      }    }    #endregion

RenderToDb(Stream excelFileStream, string insertSql)
    #region 是否有数据    /// <summary>    /// 是否有数据    /// </summary>    /// <param name="excelFileStream"></param>    /// <returns></returns>    public static bool HasData(Stream excelFileStream)    {      using (excelFileStream)      {        IWorkbook workbook = new HSSFWorkbook(excelFileStream);        if (workbook.NumberOfSheets > 0)        {          ISheet sheet = workbook.GetSheetAt(0);          return sheet.PhysicalNumberOfRows > 0;        }      }      return false;    }     #endregion

HasData(Stream excelFileStream)
  } }

 3.项目中的Execl导入导出

工作中我一般主要是用到的MVC,在这里就说说我们项目中一般的处理过程。先看看我自己写的一个NPOIBase父类。

  public class NPOIBase : ActionResult  {    public IWorkbook _workbook { get; set; }    public ISheet _sheet { get; set; }    public ICellStyle _titleStyle { get; set; }    public ICellStyle _leftStyle { get; set; }    public ICellStyle _centerStyle { get; set; }    public ICellStyle _rightStyle { get; set; }    public ICellStyle _headStyle { get; set; }    public ICellStyle _leftborderStyle { get; set; }    public ICellStyle _rightborderStyle { get; set; }    public ICellStyle _noneRightBorderStyle { get; set; }    public ICellStyle _noneLeftBorderStyle { get; set; }    public ICellStyle _noneLeftAndRightBorderStyle { get; set; }    public ICellStyle _borderStyle { get; set; }
public override void ExecuteResult(ControllerContext context) { }
public void IniNPOI(bool isHeadBorder = false, string sheetName = "") { _workbook = new HSSFWorkbook(); _sheet = string.IsNullOrWhiteSpace(sheetName) ? _workbook.CreateSheet() : _workbook.CreateSheet(sheetName); IniStyle(isHeadBorder); }
public void IniStyle(bool isHeadBorder = false)    {      IFont font12 = _workbook.CreateFont();      font12.FontHeightInPoints = 12;      font12.Boldweight = 700;      _titleStyle = _workbook.CreateCellStyle();      _titleStyle.Alignment = HorizontalAlignment.Center;      _titleStyle.VerticalAlignment = VerticalAlignment.Top;      _titleStyle.SetFont(font12);      _leftStyle = _workbook.CreateCellStyle();      _leftStyle.Alignment = HorizontalAlignment.Left;      _leftStyle.VerticalAlignment = VerticalAlignment.Top;      _centerStyle = _workbook.CreateCellStyle();      _centerStyle.Alignment = HorizontalAlignment.Center;      _centerStyle.VerticalAlignment = VerticalAlignment.Top;      _rightStyle = _workbook.CreateCellStyle();      _rightStyle.Alignment = HorizontalAlignment.Right;      _rightStyle.VerticalAlignment = VerticalAlignment.Top;      _headStyle = _workbook.CreateCellStyle();      _headStyle.Alignment = HorizontalAlignment.Center;      _headStyle.VerticalAlignment = VerticalAlignment.Top;      if (isHeadBorder)      {        _headStyle.BorderBottom = BorderStyle.Thin;        _headStyle.BorderLeft = BorderStyle.Thin;        _headStyle.BorderRight = BorderStyle.Thin;        _headStyle.BorderTop = BorderStyle.Thin;      }      _leftborderStyle = _workbook.CreateCellStyle();      _leftborderStyle.Alignment = HorizontalAlignment.Left;      _leftborderStyle.VerticalAlignment = VerticalAlignment.Top;      _leftborderStyle.BorderBottom = BorderStyle.Thin;      _leftborderStyle.BorderLeft = BorderStyle.Thin;      _leftborderStyle.BorderRight = BorderStyle.Thin;      _leftborderStyle.BorderTop = BorderStyle.Thin;      _rightborderStyle = _workbook.CreateCellStyle();      _rightborderStyle.Alignment = HorizontalAlignment.Right;      _rightborderStyle.VerticalAlignment = VerticalAlignment.Top;      _rightborderStyle.BorderBottom = BorderStyle.Thin;      _rightborderStyle.BorderLeft = BorderStyle.Thin;      _rightborderStyle.BorderRight = BorderStyle.Thin;      _rightborderStyle.BorderTop = BorderStyle.Thin;      _noneRightBorderStyle = _workbook.CreateCellStyle();      _noneRightBorderStyle.Alignment = HorizontalAlignment.Left;      _noneRightBorderStyle.VerticalAlignment = VerticalAlignment.Top;      _noneRightBorderStyle.BorderBottom = BorderStyle.Thin;      _noneRightBorderStyle.BorderLeft = BorderStyle.Thin;      _noneRightBorderStyle.BorderTop = BorderStyle.Thin;      _noneLeftBorderStyle = _workbook.CreateCellStyle();      _noneLeftBorderStyle.Alignment = HorizontalAlignment.Right;      _noneLeftBorderStyle.VerticalAlignment = VerticalAlignment.Top;      _noneLeftBorderStyle.BorderBottom = BorderStyle.Thin;      _noneLeftBorderStyle.BorderRight = BorderStyle.Thin;      _noneLeftBorderStyle.BorderTop = BorderStyle.Thin;      _noneLeftAndRightBorderStyle = _workbook.CreateCellStyle();      _noneLeftAndRightBorderStyle.Alignment = HorizontalAlignment.Center;      _noneLeftAndRightBorderStyle.VerticalAlignment = VerticalAlignment.Top;      _noneLeftAndRightBorderStyle.BorderBottom = BorderStyle.Thin;      _noneLeftAndRightBorderStyle.BorderTop = BorderStyle.Thin;      _borderStyle = _workbook.CreateCellStyle();      _borderStyle.Alignment = HorizontalAlignment.Center;      _borderStyle.VerticalAlignment = VerticalAlignment.Top;      _borderStyle.BorderBottom = BorderStyle.Thin;      _borderStyle.BorderLeft = BorderStyle.Thin;      _borderStyle.BorderRight = BorderStyle.Thin;      _borderStyle.BorderTop = BorderStyle.Thin;      IFont font = _workbook.CreateFont();      font.FontHeightInPoints = 10;      font.Boldweight = 700;      _headStyle.SetFont(font);    }

IniStyle
    public void FillHeadCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null, 
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; if (cellStyle == null) cellStyle = _headStyle; FillCell(row, colIndex, value, cellStyle, mergedCellRangeAddress); _sheet.SetColumnWidth(colIndex, (Encoding.Default.GetBytes(value.Trim()).Length + 4) * 256); }
public void FillCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; ICell titleSum = row.CreateCell(colIndex); titleSum.SetCellValue(value); if (cellStyle != null) titleSum.CellStyle = cellStyle; else if (_centerStyle != null) titleSum.CellStyle = _centerStyle; if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress); }
public void FillCell(IRow row, int colIndex, double value, ICellStyle cellStyle = null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; ICell titleSum = row.CreateCell(colIndex); titleSum.SetCellValue(value); if (cellStyle != null) titleSum.CellStyle = cellStyle; else if (_centerStyle != null) titleSum.CellStyle = _centerStyle; if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress); }
public void ResponseOutPutExcelStream(string fildName) { if (string.IsNullOrWhiteSpace(fildName)) fildName = DateTime.Now.ToString("yyyyMMddHHmmss.xls"); if (fildName.ToLower().IndexOf(".xls") == -1) fildName += ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fildName)); HttpContext.Current.Response.Clear(); MemoryStream file = new MemoryStream(); _workbook.Write(file); file.WriteTo(HttpContext.Current.Response.OutputStream); HttpContext.Current.Response.End(); }
public void SetPrint(bool isLandscape = false, bool isFitToPage = false, double topMargin = 0, double rightMargin = 0,
double bottomMargin = 0.5, double leftMargin = 0, short scale = 100) { _sheet.PrintSetup.Landscape = isLandscape; _sheet.SetMargin(MarginType.TopMargin, topMargin); _sheet.SetMargin(MarginType.RightMargin, rightMargin); _sheet.SetMargin(MarginType.LeftMargin, leftMargin); _sheet.SetMargin(MarginType.BottomMargin, bottomMargin); _sheet.PrintSetup.PaperSize = 9; _sheet.PrintSetup.Scale = scale; _sheet.FitToPage = isFitToPage; if (isFitToPage) { _sheet.PrintSetup.FitWidth = 1; _sheet.PrintSetup.FitHeight = 0; } } }

 这个由于父类是继承的ActionResult,我们用起来就比较方便,在Action中,直接使用就可以了。代码示例如下...

  public class BaseMaterialsExcelResult : NPOIBase  {    string[] __headers = null;    IList<BaseMaterials> __BaseMaterialsList;    public BaseMaterialsExcelResult(IList<BaseMaterials> BaseMaterialsList)    {      __BaseMaterialsList = BaseMaterialsList;      __headers = new string[] {        "序号",        "材料",        "型号",        "推荐供应商",        "出库数量",        "入库数量",        "结存"        };    }
public override void ExecuteResult(ControllerContext context) { if (__BaseMaterialsList == null || __BaseMaterialsList.Count() == 0) return; IniNPOI(); int rowIndex = 0; foreach (var item in __BaseMaterialsList) { #region 新建表,填充列头,样式 int colIndex = 0; if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) _sheet = _workbook.CreateSheet(); IRow headerRow = _sheet.CreateRow(rowIndex); foreach (var head in __headers) FillHeadCell(headerRow, colIndex++, head); rowIndex = 1; } #endregion #region 填充内容 IRow dataRow = _sheet.CreateRow(rowIndex); colIndex = 0; FillCell(dataRow, colIndex++, rowIndex); FillCell(dataRow, colIndex++, item.Name); FillCell(dataRow, colIndex++, item.Type); FillCell(dataRow, colIndex++, item.ProviderName); FillCell(dataRow, colIndex++, item.OutStorageCount.ToString()); FillCell(dataRow, colIndex++, item.StorageCount.ToString()); FillCell(dataRow, colIndex++, item.StockCount.ToString()); #endregion rowIndex++; } _sheet.CreateFreezePane(1, 1, 1, 1); ResponseOutPutExcelStream("BaseMaterials.xls"); } }

 控制器中的代码如下:

  public class HomeController : Controller  {    BluedonStockEntities db = new BluedonStockEntities();    public List<BaseMaterials> GetList()    {      return db.CreateObjectSet<BaseMaterials>().Where(c => true).ToList();    }    public ActionResult Index()    {      var list = GetList();      return View(list);    }    public ActionResult Execl()    {      var list = GetList();       return new BaseMaterialsExcelResult(list);    }  }

 4.问题总结。

  1. 在实例化了一个WorkBook之后,最好添加一个sheet,虽然在最新版的Npoi.net中自动添加了,但是遇到迁移到原来版本就会出现问题。所以我建议还是最少添加一个sheet
  2. 在从单元格取值时要注意单元格的类型,一定要用对应的类型的方法来取单元格中的对应类型的值,如果不确定,那只能是强制转换成为string类型,毕竟string类型是excel中其他类型都可以转换过来的
  3. 在获取sheet中的某一行或者某一行的某一个单元格的时候,还要注意就是一定要确保创建了该行,并且取单元格还要确保创建了单元格,否则会报Null reference not to object 这个我们经常会看到的异常信息。在外层一定要加上try捕获异常
  4. 合并单元格是sheet的工作,因此需要获取对应的sheet,然后调用其AddMergedRegion方法合并单元格,在合并单元格的时候,我们不需要确保该单元格已经存在或创建。
  5. 在为单元格设置样式的过程中,我们会发现所有和样式相关的类的创建都是通过workBook.Create(Font)..这种方式来执行的,我们不可以直接new一个类的实例。
  6. 如果前面的工作都已经做好,需要把内存中的excel表写到硬盘上时,需要调用workBook.write()方法,传入一个文件流进行创建。在这里有可能会出现一个问题,就是你要创建的文件你已经打开了,这时程序就会出现异常,因此我们在调试程序的时候一定要记得打开了excel文件以后要关闭
  7. 最后需要注意的就是文件流,在我们把excel写到硬盘上以后,要显式的调用其close()方法关闭文件流。因为如果不关闭文件流的话,以后就会出现无法重新创建该文件的错误,并且会提示 某文件正由另一进程使用,因此该进程无法访问此文件。

简单用法的源码【点击下载】

MVC版的源码【点击下载】

感谢你的支持,为我点个赞吧!