首页 > > 网络编程 > Mysql >

总结一下工作中遇到的NPOI以及在ASP.NET MVC中的…

2018-06-22 01:03:35来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

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版的源码【点击下载】

感谢你的支持，为我点个赞吧！

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：C#进制转换

下一篇：安装VS2013的离线MSDN帮助文档

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签