首页 > 代码库 > asp.net使用MVC4框架基于NPOI做导出数据到Excel表

asp.net使用MVC4框架基于NPOI做导出数据到Excel表

2024-07-15 13:33:45   222人阅读

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

使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

下面我们使用NPOI在MVC4框架下制作一个导出的功能。

（1）在DAL数据访问层，定义需要需要导出的数据表，可以根据需要导出的字段，进行SQL语句的组织条件。

 public DataTable GetData()        {            DataTable dt = new DataTable();            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ToString()))            {                string sql = "select [LoginID],[WageID],[Name],[UserLimit],[OnDutyTime],[CarShiFa],[OnDutyDay],[NightOnDuty],[AllNightOnDuty],[CarAllowance],[WorkOvertime],[WeekendNightWork],[WeekendOverNight] from Kaoqinsum where OnDutyTime=datename(yy,getdate()) + ‘-‘ + datename(m,dateadd(m,-1,getdate()))";                conn.Open();                SqlCommand cmd = new SqlCommand(sql, conn);                SqlDataAdapter sda = new SqlDataAdapter(cmd);                sda.Fill(dt);                conn.Close();                return dt;            }        }

　　（2）在BLL业务逻辑层，调用数据访问层中的GetDate();

  public DataTable GetDate()        {            return new SalaryManageDAL.KaoqinsumDAL().GetData();        }

　　（3）在控制器中，我们来书写导出功能的主要代码。

  public ActionResult DaoChu()          {              DataTable dt = new SalaryManageBLL.KaoqinsumBLL().GetDate();              //1、实例化workbook工作簿对象              HSSFWorkbook hssfworkbook = new HSSFWorkbook();              //2、创建文档摘要信息              DocumentSummaryInformation dsf = PropertySetFactory.CreateDocumentSummaryInformation();              dsf.Company = "沈阳工学院";//公司              dsf.Category = "Statistics";//类别              //CustomProperties 自定义属性              SummaryInformation si = PropertySetFactory.CreateSummaryInformation();              si.Author = "院办";//作者              //Comments 评论 CreateDateTime 创建时间 Template模板              si.Keywords = "kaoqin,yuanban";//关键字              si.Subject = "kaoqin";//主题              si.Title = "考勤汇总";//标题              si.RevNumber = "1.0";//版本号              //3、将写好的文档摘要 赋值workbook对象              hssfworkbook.DocumentSummaryInformation = dsf;              hssfworkbook.SummaryInformation = si;              //4、创建Sheet              HSSFSheet Sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");              HSSFSheet Sheet2 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet2");              HSSFSheet Sheet3 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet3");              //5、创建页眉页脚              Sheet1.CreateRow(0).CreateCell(1).SetCellValue(123);              Sheet1.Header.Center = "统计数据";              Sheet1.Header.Left = "logo.png";              Sheet1.Header.Right = "zhguAddress";              Sheet1.Footer.Center = "page";              //6、标题              string yeartime = time();              HSSFCell fcell = (HSSFCell)Sheet1.CreateRow(0).CreateCell(0);//第一行              fcell.SetCellValue("沈阳工学院" + yeartime + "考勤汇总情况表");//文本              //合并单元格              Sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13));//2.0使用 2.0以下为Region              //标题样式              HSSFCellStyle fCellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              HSSFFont ffont = (HSSFFont)hssfworkbook.CreateFont();              ffont.FontHeight = 20 * 20;              ffont.FontName = "宋体";              ffont.Color = HSSFColor.BLUE.index;              fCellStyle.SetFont(ffont);              fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐              fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐              fcell.CellStyle = fCellStyle;              //7、设置单元格格式 创建单元格              /*模拟设定7列*/              HSSFDataFormat dataformat = (HSSFDataFormat)hssfworkbook.CreateDataFormat();//数据格式              HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();//数据字体              font.Color = HSSFColor.BLACK.index; //颜色               font.IsItalic = false;//斜体              font.IsStrikeout = false;//加粗              font.FontName = "宋体";//字体              //必不可少 可以变更在循环输出数据时指定类型 需要调用sqlDbType 较复杂              //Id  int类型              HSSFCell cell1 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(0); //创建单元格              HSSFCellStyle cellStyle1 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();//单元格样式              cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              // CellRangeAddressList ranglist1 = new CellRangeAddressList(0, 65535, 0, 0);//集合限定类型              // DVConstraint constraint1 = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.BETWEEN, "0", "100");//约束              cellStyle1.SetFont(font);              cell1.CellStyle = cellStyle1;              cell1.SetCellValue("");              //Name              HSSFCell cell2 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(1);              HSSFCellStyle cellStyle2 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle2.SetFont(font);              cell2.CellStyle = cellStyle2;              cell2.SetCellValue("");              //phone              HSSFCell cell3 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(2);              HSSFCellStyle cellStyle3 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle3.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle3.SetFont(font);              cell3.CellStyle = cellStyle3;              cell3.SetCellValue("");              //address              HSSFCell cell4 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(3);              HSSFCellStyle cellStyle4 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle4.SetFont(font);              cell4.CellStyle = cellStyle4;              cell4.SetCellValue("");              //Status              HSSFCell cell5 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(4);              HSSFCellStyle cellStyle5 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle5.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle5.SetFont(font);              cell5.CellStyle = cellStyle5;              cell5.SetCellValue("");              //balance              HSSFCell cell6 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(5);              HSSFCellStyle cellStyle6 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cell6.SetCellValue("");              cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle6.SetFont(font);              cell6.CellStyle = cellStyle6;              //CreateDate              HSSFCell cell7 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(6);              HSSFCellStyle cellStyle7 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle7.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle7.SetFont(font);              cell7.CellStyle = cellStyle7;              cell7.SetCellValue("");              HSSFCell cell8 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(7);              HSSFCellStyle cellStyle8 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle8.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle8.SetFont(font);              cell8.CellStyle = cellStyle8;              cell8.SetCellValue("");              HSSFCell cell9 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(8);              HSSFCellStyle cellStyle9 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle9.SetFont(font);              cell9.CellStyle = cellStyle9;              cell9.SetCellValue("");              HSSFCell cell10 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(9);              HSSFCellStyle cellStyle10 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle10.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle10.SetFont(font);              cell10.CellStyle = cellStyle10;              cell10.SetCellValue("");              HSSFCell cell11 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(10);              HSSFCellStyle cellStyle11 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle11.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle11.SetFont(font);              cell11.CellStyle = cellStyle11;              cell11.SetCellValue("");              HSSFCell cell12 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(11);              HSSFCellStyle cellStyle12 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle12.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle12.SetFont(font);              cell12.CellStyle = cellStyle12;              cell12.SetCellValue("");              HSSFCell cell13 = (HSSFCell)Sheet1.CreateRow(1).CreateCell(12);              HSSFCellStyle cellStyle13 = (HSSFCellStyle)hssfworkbook.CreateCellStyle();              cellStyle13.DataFormat = HSSFDataFormat.GetBuiltinFormat("");              cellStyle13.SetFont(font);              cell13.CellStyle = cellStyle13;              cell13.SetCellValue("");              //8、创建单元格 加入数据              HSSFRow r = (HSSFRow)Sheet1.CreateRow(1);//第二行 标题              for (int i = 0; i < dt.Columns.Count; i++)              {                  r.CreateCell(i).SetCellValue(dt.Columns[i].ToString());              }              if (dt.Rows.Count > 0)              {                  for (int i = 0; i < dt.Rows.Count; i++)                  {                      HSSFRow row = (HSSFRow)Sheet1.CreateRow(i + 2);//写入行                      for (int j = 0; j < dt.Columns.Count; j++)//写入列                      {                          if (dt.Columns[j].ColumnName == "balance")                          {                              row.CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");                              row.CreateCell(j).SetCellValue(Convert.ToDouble(dt.Rows[i][j].ToString()));                          }                          else                          {                              row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());                          }                      }                  }              }              //9、求和 SUM函数              HSSFCell cesum = (HSSFCell)Sheet1.CreateRow(Sheet1.LastRowNum + 1).CreateCell(5);//最后一行+1行等于总计行              HSSFCell cebegin = (HSSFCell)Sheet1.GetRow(2).GetCell(5);//开始              HSSFCell ceend = (HSSFCell)Sheet1.GetRow(Sheet1.LastRowNum - 1).GetCell(5);//结束              cesum.SetCellFormula("sum(" + GetA_Z(5) + 3 + ":" + GetA_Z(5) + Sheet1.LastRowNum + ")");              FileStream fs = new FileStream(Server.MapPath("~/ExportFiles/" + yeartime + "考勤信息.xls"), FileMode.Create);              hssfworkbook.Write(fs);              fs.Close();              //Response.Write("导出完成");              return View();                //return ;          }

　　

 public string GetA_Z(double p)          {              string[] str = { "0:A", "1:B", "2:C", "3:D", "4:E", "5:F", "6:G", "7:H", "8:I", "9:J", "10:K", "11:L", "12:M", "13:N", "14:O", "15:P", "16:Q", "17:R", "18:S", "19:T", "20:U", "21:V", "22:W", "23:X", "24:Y", "25:Z" };              for (int i = 0; i < str.Length; i++)              {                  if (p.ToString() == str[i].Split(‘:‘)[0].ToString())                  {                      return str[i].Split(‘:‘)[1].ToString();                  }              }              return "";          }

　　由于实际项目中需要时间的条件限制。定义了一个返回值类型为string的time();

  public String time()          {              string time = "";              DateTime dt = DateTime.Now;              int year = dt.Year;              int month = dt.Month;              if (1 < month && 10 > month)              {                  time = year + "-";                  time += "0";                  time = time + Convert.ToString(month - 1);              }              if (month == 1)              {                  year = dt.Year - 1;                  time = Convert.ToString(year) + "-";                  time += "12";              }              return time;          }

　　(4)在前台UI界面定义一个按钮，来实现点击触发控制器中的DaoChu();

<a href="http://www.mamicode.com/#" id="daochu" class="easyui-linkbutton" data-options="iconCls:‘icon-search‘">导出数据</a>

　　定义id="daochu"所触发的事件。

　　

  $("#daochu").click(function () {                getdaochu = "/Kaoqinsum/DaoChu";
　　　　　　　　　　//提交执行控制器的方法                initDataGrid("#dg", colums, getdaochu);
　　　　　　　　　　//创建个返回值日期，用于导出时对时间的判断，导出对应月份的数据。                var date = new Date();                var year = date.getFullYear();                var month = date.getMonth();                var clock;                if (0 < month < 10) {                    clock = year + "-";                    clock += "0";                    clock += month;                }                if (month == 0) {                    year = date.getFullYear() - 1;                    clock = year + "-";                    clock += "12";                }                if ($("#OnDutyTime").datebox(‘getValue‘) != "") {                    geturl3 = "../ExportFiles/" + $("#OnDutyTime").datebox(‘getValue‘) + "考勤信息.xls"; ;                    window.open(geturl3);                }                if ($("#OnDutyTime").datebox(‘getValue‘) == "") {                    geturl2 = "../ExportFiles/" + clock + "考勤信息.xls";                    window.open(geturl2);                }            })

　　

PS：导出的Excel表下载地址http://pan.baidu.com/s/1ntp2izn   密码：mxmo


联系
我们