【NPOI】导出Excel文件

来源：互联网发布：淘宝商家入驻条件编辑：程序博客网时间：2024/05/20 05:55

最近有点懒，也有点忙，大半个月时间没写博客了。

历经十五天，第二个实训项目终于写完了，也终于有时间来记录项目中遇到的一些问题。

将web中信息导出至Excel中早就不陌生了，大一做一个比赛项目《考勤管理系统》时，就操作过将数据导出成Excel文件和将Excel文件导入进数据库，当时比较“稚嫩”，啥也不懂，采用office的api进行导出操作，然后提交后，发现在没有安装office的电脑上就用不了，它必须依赖于office的插件才能运行。这次吸取的上次的教训，百度又找到了一个方法，用NPOI进行excel操作。

别人说：这个NPOI操作Excel，应该是最好的方案了，没有之一，使用NPOI能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

一、下载NPOI

先去官网：http://npoi.codeplex.com/。

目前用的版本是：NPOI 2.2.1,发现不同版本在使用上有好大的区别，网上看其他人写的也存在一些差异，有些属性的大小写不同，方法不同等，不过还是换汤不换药的。

二、引用文件

解压文件后可以选择需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用

我选的是4.0的，将下面四个文件引用进去

三、导出Excel方法

        /// <summary>        /// 导出excel        /// </summary>        /// <returns></returns>        public ActionResult ExcelINTest()        {            HSSFWorkbook book = new HSSFWorkbook();            HSSFSheet sheet = book.CreateSheet("Sheet1") as HSSFSheet;            //设置列宽            sheet.SetColumnWidth(1, 10 * 256);            sheet.SetColumnWidth(2, 10 * 256);            sheet.SetColumnWidth(3, 10 * 256);            sheet.SetColumnWidth(4, 15 * 256);            sheet.SetColumnWidth(5, 25 * 256);            sheet.SetColumnWidth(6, 10 * 256);            #region 表头部分            //合并单元格            HSSFRow dataRow = sheet.CreateRow(1) as HSSFRow;            dataRow = sheet.CreateRow(1) as HSSFRow;            CellRangeAddress region = new CellRangeAddress(0, 0, 1, 6);            //CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。            sheet.AddMergedRegion(region);            IRow hrow = sheet.CreateRow(0);            hrow.Height = 20 * 25;            ICell icellltop0 = hrow.CreateCell(1);            //表头样式            IFont font12 = book.CreateFont();            font12.FontHeightInPoints = 14;            font12.FontName = "微软雅黑";            font12.Boldweight = short.MaxValue;            font12.Color = Black.Index;            ICellStyle cellStyle = book.CreateCellStyle();            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;            cellStyle.BorderBottom = BorderStyle.Thin;            cellStyle.BorderLeft = BorderStyle.Thin;            cellStyle.BorderRight = BorderStyle.Thin;            cellStyle.BorderTop = BorderStyle.Thin;            for (int i = region.FirstRow; i <= region.LastRow; i++)            {                IRow row = HSSFCellUtil.GetRow(i, sheet);                for (int j = region.FirstColumn; j <= region.LastColumn; j++)                {                    ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);                    singleCell.CellStyle = cellStyle;                }            }            cellStyle.SetFont(font12);            icellltop0.CellStyle = cellStyle;            icellltop0.SetCellValue("此处程序自动生成");            #endregion            #region 标题部分            ICellStyle TitleStyle = book.CreateCellStyle();            TitleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;            TitleStyle.FillPattern = FillPattern.SolidForeground;            TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;            TitleStyle.BorderBottom = BorderStyle.Thin;            TitleStyle.BorderLeft = BorderStyle.Thin;            TitleStyle.BorderRight = BorderStyle.Thin;            TitleStyle.BorderTop = BorderStyle.Thin;            IRow TitleRow = sheet.CreateRow(3);            ICell Titlecell1 = TitleRow.CreateCell(1);            Titlecell1.CellStyle = TitleStyle;            Titlecell1.SetCellValue("会员卡号");            ICell Titlecell2 = TitleRow.CreateCell(2);            Titlecell2.CellStyle = TitleStyle;            Titlecell2.SetCellValue("姓名");            ICell Titlecell3 = TitleRow.CreateCell(3);            Titlecell3.CellStyle = TitleStyle;            Titlecell3.SetCellValue("性别");            ICell Titlecell4 = TitleRow.CreateCell(4);            Titlecell4.CellStyle = TitleStyle;            Titlecell4.SetCellValue("电话");            ICell Titlecell5 = TitleRow.CreateCell(5);            Titlecell5.CellStyle = TitleStyle;            Titlecell5.SetCellValue("登记时间");            ICell Titlecell6 = TitleRow.CreateCell(6);            Titlecell6.CellStyle = TitleStyle;            Titlecell6.SetCellValue("会员等级");            #endregion            int lenght = 0, man = 0, woman = 0;            #region sqltoExcel            using (Models.MPMS_DBDataContext db = new Models.MPMS_DBDataContext())            {                MPMS.Models.Users user = Session["userInfo"] as MPMS.Models.Users;                int S_ID = (int)user.S_ID;                var data = db.MemCards.Where(p => p.S_ID == S_ID).ToList();                lenght = data.Count();                for (int i = 0; i < lenght; i++)                {                    IRow row = sheet.CreateRow(i + 4);                    row.CreateCell(1).SetCellValue(data[i].MC_CardID);                    row.CreateCell(2).SetCellValue(data[i].MC_Name);                    string sex = data[i].MC_Sex == 0 ? "未知" : data[i].MC_Sex == 1 ? "男" : "女";                    if (data[i].MC_Sex == 1)                        man++;                    if (data[i].MC_Sex == 2)                        woman++;                    row.CreateCell(3).SetCellValue(sex);                    row.CreateCell(4).SetCellValue(data[i].MC_Mobile);                    string date = DateTime.Parse(data[i].MC_CreateTime.ToString()).ToString();                    row.CreateCell(5).SetCellValue(date);                    var jj = db.CardLevels.FirstOrDefault(p => p.CL_ID == data[i].CL_ID);                    row.CreateCell(6).SetCellValue(jj.CL_LevelName);                }            }            #endregion            #region 统计部分            ICellStyle SumStyle = book.CreateCellStyle();            SumStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;            SumStyle.FillPattern = FillPattern.SolidForeground;            SumStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;            SumStyle.BorderBottom = BorderStyle.Thin;            SumStyle.BorderLeft = BorderStyle.Thin;            SumStyle.BorderRight = BorderStyle.Thin;            SumStyle.BorderTop = BorderStyle.Thin;            IRow SumRow = sheet.CreateRow(1);            ICell cell1 = SumRow.CreateCell(1);            cell1.SetCellValue("总人数");            cell1.CellStyle = SumStyle;            ICell cell2 = SumRow.CreateCell(2);            cell2.SetCellValue(lenght);            cell2.CellStyle = SumStyle;            ICell cell3 = SumRow.CreateCell(3);            cell3.SetCellValue("男生人数");            cell3.CellStyle = SumStyle;            ICell cell4 = SumRow.CreateCell(4);            cell4.SetCellValue(man);            cell4.CellStyle = SumStyle;            ICell cell5 = SumRow.CreateCell(5);            cell5.SetCellValue("女生人数");            cell5.CellStyle = SumStyle;            ICell cell6 = SumRow.CreateCell(6);            cell6.SetCellValue(woman);            cell6.CellStyle = SumStyle;            #endregion            #region 边框设置            ICellStyle borStyle = book.CreateCellStyle();            borStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;            borStyle.BorderBottom = BorderStyle.Thin;            borStyle.BorderLeft = BorderStyle.Thin;            borStyle.BorderRight = BorderStyle.Thin;            borStyle.BorderTop = BorderStyle.Thin;            for (int i = 2; i <= lenght + 3; i++)            {                if (i == 3) continue;                IRow row = HSSFCellUtil.GetRow(i, sheet);                for (int j = 1; j <= 6; j++)                {                    ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);                    singleCell.CellStyle = borStyle;                }            }            #endregion            // 写入到客户端              System.IO.MemoryStream ms = new System.IO.MemoryStream();            book.Write(ms);            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));            Response.BinaryWrite(ms.ToArray());            book = null;            ms.Close();            ms.Dispose();            return Content("OK");        }

阅读全文

1 0