首页 > 代码库 > asp.net使用MVC4框架基于NPOI做导出数据到Excel表
asp.net使用MVC4框架基于NPOI做导出数据到Excel表
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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。