总结一下工作中遇到的NPOI已经在ASP.NET MVC中的使用

最新推荐文章于 2023-12-13 15:47:23 发布

心在忍耐

最新推荐文章于 2023-12-13 15:47:23 发布

阅读量1.7k

点赞数

分类专栏： MVC ASP.NET

ASP.NET 同时被 2 个专栏收录

70 篇文章 0 订阅

订阅专栏

MVC

49 篇文章 1 订阅

订阅专栏

原文 http://www.cnblogs.com/fenglingyi/p/4750323.html

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.问题总结。

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

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

MVC版的源码【点击下载】

心在忍耐

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
总结一下工作中遇到的NPOI已经在ASP.NET MVC中的使用

原文 http://www.cnblogs.com/fenglingyi/p/4750323.html1.前言相信大家在工作中经常要遇到一些导入导出Execl操作。学习贵在分享，分享使人快乐，园子里的前辈已经有很多好的文章，鄙人也是能力有限，在这里把这些好的文章总结，方便以后再工作中使用。NPOI ：是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel
复制链接

扫一扫