Excel导出
public void CreateKekaoExcel1(DataTable dt, string FileName) { if (dt.Rows.Count > 0) { Response.ClearContent(); Response.BufferOutput = true; Response.Charset = "utf-8"; Response.ContentType = "text/xml"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ""); //if (Result.data == null || Result.data.Count() == 0) //{ throw new Exception("数据为空，不能生成数据表格"); } StringBuilder sb = new StringBuilder(); try { //生成标题行 sb.Append("<Row>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>城市</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>姓名</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>工号</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>考试岗位</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>笔试成绩</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>面试成绩</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>最终成绩</Data></Cell>"); sb.Append("</Row>"); int n = 1; foreach (DataRow dtrow in dt.Rows) { sb.Append("<Row>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["城市"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["姓名"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["工号"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["考试岗位"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["笔试成绩"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["面试成绩"] + "</Data></Cell>"); sb.Append("<Cell ss:StyleID='scen'><Data ss:Type='String'>" + dtrow["最终成绩"] + "</Data></Cell>"); sb.Append("</Row>"); n++; } //生成完毕 //获取文件共通部分 string comon = GetExcelCommonPart(); StringBuilder all = new StringBuilder(); string str1 = all.AppendFormat(comon, sb.ToString()).ToString(); Response.Write(str1); Response.End(); Response.Clear(); // File(System.Text.Encoding.UTF8.GetBytes(str1), "application/ms-excel", "export.xls"); } catch { throw; } } } /// <summary> /// 报表导出文件获取文件共通部分 /// </summary> /// <returns></returns> public static string GetExcelCommonPart() { StringBuilder sb = new StringBuilder(); string FileAuthorName = "考试系统"; // 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。 // 兼容 Excel 2003,Excel 2007, Excel 2010 //生成 sb.Append("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>"); sb.Append(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>"); sb.Append(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>"); sb.Append(@"<Author>" + FileAuthorName + "</Author><LastAuthor>ZJLHC.COM</LastAuthor><Created>" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "</Created><Company>" + FileAuthorName + "</Company><Version>v1.0</Version>"); sb.Append("</DocumentProperties>"); sb.Append(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/> <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>"); //定义标题样式 // Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> // <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> // <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> // <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders> // <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>"); //定义边框样式 sb.Append(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>"); //时间格式 sb.Append(@"<Style ss:ID='s1'><NumberFormat ss:Format='yyyy/mm/dd'/><Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>"); //数值格式 sb.Append(@"<Style ss:ID='s2'><NumberFormat ss:Format='#,##0.00'/><Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>"); //居中 sb.Append(@"<Style ss:ID='scen'><Alignment ss:Horizontal='Center' ss:Vertical='Center'/> <Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> </Borders> <NumberFormat ss:Format='@'/> </Style>"); //居左 sb.Append(@"<Style ss:ID='sleft'><Alignment ss:Horizontal='Left' ss:Vertical='Center'/> <Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> </Borders> <NumberFormat ss:Format='@'/> </Style>"); //居右 sb.Append(@"<Style ss:ID='sright'><Alignment ss:Horizontal='Right' ss:Vertical='Center'/> <Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> </Borders> <NumberFormat ss:Format='@'/> </Style>"); //报表日期格式 sb.Append(@"<Style ss:ID='sdate'><NumberFormat ss:Format='yyyy年mm月dd日'/><Alignment ss:Horizontal='Center' ss:Vertical='Center'/><Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>"); sb.Append("</Styles>"); sb.Append("<Worksheet ss:Name='Sheet1'>"); sb.Append("<Table x:FullColumns='1' x:FullRows='1'>"); sb.Append("{0}");//数据部分 sb.Append("</Table>"); sb.Append("</Worksheet>"); sb.Append("</Workbook>"); return sb.ToString(); }
引入NOPI进行导出(推荐)
学习地址: http://www.cnblogs.com/stone_w/archive/2012/08/02/2620528.html
下载NOPI插件,引入程序集
public void CreateKekaoExcel(DataTable dt, string FileName) { if (dt.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0); row1.CreateCell(0).SetCellValue("城市"); row1.CreateCell(1).SetCellValue("姓名"); row1.CreateCell(2).SetCellValue("工号"); row1.CreateCell(3).SetCellValue("考试岗位"); row1.CreateCell(4).SetCellValue("笔试成绩"); row1.CreateCell(5).SetCellValue("面试成绩"); row1.CreateCell(6).SetCellValue("最终成绩"); for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i+1); rows.CreateCell(0).SetCellValue(dt.Rows[i]["城市"].ToString()); rows.CreateCell(1).SetCellValue(dt.Rows[i]["姓名"].ToString()); rows.CreateCell(2).SetCellValue(dt.Rows[i]["工号"].ToString()); rows.CreateCell(3).SetCellValue(dt.Rows[i]["考试岗位"].ToString()); rows.CreateCell(4).SetCellValue(dt.Rows[i]["笔试成绩"].ToString()); rows.CreateCell(5).SetCellValue(dt.Rows[i]["面试成绩"].ToString()); rows.CreateCell(6).SetCellValue(dt.Rows[i]["最终成绩"].ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", FileName)); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); } }
