后台列表导出Excel主要是将GridView控件、table控件或者DataTable中的数据导出到Excel,因此根据不同的数据源,可以由多种方式,主要是利用table的html代码文本流的形式输出或者COM组件生成Excel后以文件流的形式输出。
1、GridView导出Excel,利用GridView的RenderControl方法将GridView转换成html代码然后利用流的方式发送到浏览器,原理是将内容以xml格式输入到excel文件中,excel可以解析这类文件,其中需要重载VerifyRenderingInServerForm方法。其中开头一段head和body标签不写,直接将rendercontrol后的html代码输送到浏览器也可以。同时还可以改变table中tr和td的样式用以改变生成的Excel文件中单元格的样式。
public void ExcelExport()
{
StringWriter sw = new StringWriter();
sw.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine("<head><meta http-equiv=Content-Type content=\"text/html; charset=UTF-8\">");
sw.WriteLine("<!--[if gte mso 9]>");
sw.WriteLine("<xml>");
sw.WriteLine(" <x:ExcelWorkbook>");
sw.WriteLine(" <x:ExcelWorksheets>");
sw.WriteLine(" <x:ExcelWorksheet>");
sw.WriteLine(" <x:Name>sheetName</x:Name>");
sw.WriteLine(" <x:WorksheetOptions>");
sw.WriteLine(" <x:Print>");
sw.WriteLine(" <x:ValidPrinterInfo />");
sw.WriteLine(" </x:Print>");
sw.WriteLine(" </x:WorksheetOptions>");
sw.WriteLine(" </x:ExcelWorksheet>");
sw.WriteLine(" </x:ExcelWorksheets>");
sw.WriteLine("</x:ExcelWorkbook>");
sw.WriteLine("</xml>");
sw.WriteLine("<![endif]-->");
sw.WriteLine("<style type=\"text/css\"> <!-- .txt {mso-number-format:\"\\@\"; } --> </style> ");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.GridView1.RenderControl(oHtmlTextWriter);
string strHtmlTable = oHtmlTextWriter.InnerWriter.ToString();
System.Text.RegularExpressions.Regex reTable = new System.Text.RegularExpressions.Regex(@"<table[^>]*>");
strHtmlTable = reTable.Replace(strHtmlTable, "<table>");
strHtmlTable = strHtmlTable.Replace("<th>", "<th style=\" font-size:16px;border:0.5pt solid windowtext;font-weight:bold;height:30px; \" >");
strHtmlTable = strHtmlTable.Replace("<th scope=\"col\">", "<th colspan=\"col\" style=\" font-size:16px;border:0.5pt solid windowtext;font-weight:bold;height:30px; \" >");
strHtmlTable = strHtmlTable.Replace("<td>", "<td style=\" font-size:15px;border:0.5pt solid;height:25px; \" >");
sw.WriteLine(strHtmlTable);
sw.WriteLine("</body>");
sw.WriteLine("</html>");
sw.Close();
this.EnableViewState = false;
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("企业耗能统计(" + this.txtStartTime.Text + ").xls", System.Text.Encoding.UTF8).ToString());
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.Write(sw);
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
如果前台不用显示数据,只是导出数据,也可以直接使用后台临时生产的GridView或者DataGrid,然后使用RenderControl生成html内容。
string excelName = "基本信息";
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(excelName + ".xls", System.Text.Encoding.UTF8).ToString());
curContext.Response.ContentType = "application/excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dsExport;
dgExport.AllowPaging = false;
dgExport.DataBind();
dgExport.RenderControl(hw);
// curContext.Response.Write(tw.ToString());
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>" + tw.ToString());
curContext.Response.End();
2、table导出,自行构造合适的table类html代码,然后以流的形式发送到浏览器,原理与GridView导出一样,将内容以xml格式输入到excel文件中,excel可以解析这类文件。
public void ExcelExport()
{
StringWriter sw = new StringWriter();
sw.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine("<head><meta http-equiv=Content-Type content=\"text/html; charset=UTF-8\">");
sw.WriteLine("<!--[if gte mso 9]>");
sw.WriteLine("<xml>");
sw.WriteLine(" <x:ExcelWorkbook>");
sw.WriteLine(" <x:ExcelWorksheets>");
sw.WriteLine(" <x:ExcelWorksheet>");
sw.WriteLine(" <x:Name>sheetName</x:Name>");
sw.WriteLine(" <x:WorksheetOptions>");
sw.WriteLine(" <x:Print>");
sw.WriteLine(" <x:ValidPrinterInfo />");
sw.WriteLine(" </x:Print>");
sw.WriteLine(" </x:WorksheetOptions>");
sw.WriteLine(" </x:ExcelWorksheet>");
sw.WriteLine(" </x:ExcelWorksheets>");
sw.WriteLine("</x:ExcelWorkbook>");
sw.WriteLine("</xml>");
sw.WriteLine("<![endif]-->");
sw.WriteLine("<style type=\"text/css\"> <!-- .txt {mso-number-format:\"\\@\"; } --> </style> ");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
string strHtmlTable = this.dataDiv.InnerHtml;//tableneiro
System.Text.RegularExpressions.Regex reTable = new System.Text.RegularExpressions.Regex(@"<table[^>]*>");
strHtmlTable = reTable.Replace(strHtmlTable, "<table>");
strHtmlTable = strHtmlTable.Replace("<th>", "<th style=\" font-size:16px;border:0.5pt solid windowtext;font-weight:bold;height:30px; \" >");
strHtmlTable = strHtmlTable.Replace("<th colspan='2'>", "<th colspan='2' style=\" font-size:16px;border:0.5pt solid windowtext;font-weight:bold;height:30px; \" >");
strHtmlTable = strHtmlTable.Replace("<th rowspan='2'>", "<th rowspan='2' style=\" font-size:16px;border:0.5pt solid windowtext;font-weight:bold;height:30px; \" >");
strHtmlTable = strHtmlTable.Replace("<td>", "<td style=\" font-size:15px;border:0.5pt solid;height:25px; \" >");
sw.WriteLine(strHtmlTable);
sw.WriteLine("</body>");
sw.WriteLine("</html>");
sw.Close();
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
this.EnableViewState = false;
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("能耗综合成本报表-" + this.txtStartTime.Text+ ".xls", System.Text.Encoding.UTF8).ToString());
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.Write(sw);
Response.End();
}
3、DataTable的Excel输出,同理可以使用以上方法将DataTable转换成table的html代码,然后利用流输出到浏览器,也可以使用微软提供的COM组件,需要引用COM里面的 Microsoft.office.Interop.Excel 14.0 和 程序集里的Microsoft Excel 12.0 Object Library 2个DLL文件,然后使用里面提供的方法,直接操作Excel,可以对单元格赋值,也可以进行合并单元格等操作。
protected void ExportExcel(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
try
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
catch
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);
worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);
xlApp.Visible = true;
try
{
Directory.CreateDirectory(strFilePath);
workBook.SaveAs(strFilePath + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(strSaveFileName)), Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
workBook.Close(null, null, null);
FileInfo fileInfo = new FileInfo(strFilePath + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(strSaveFileName)));
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strSaveFileName, System.Text.Encoding.UTF8));
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
fileInfo.Delete();
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID,暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
4、前台js导出表格数据到Excel,IE利用ActiveX控件,其他浏览器可以利用table的innerHtml内容以Excel方式保存并打开的方法。
参见 https://blog.csdn.net/why15732625998/article/details/67637835 中的第五种方法。
IE:
try {
//创建AX对象excel
var oXL = new ActiveXObject("Excel.Application");
}
catch (e) {
alert("要打印该表,您必须安装Excel电子表格软件,同时浏览器须启用\"ActiveX 控件\",您的浏览器须允许执行控件。请点击【帮助】了解浏览器设置方法!");
return false;
}
//获取workbook对象
var oWB = oXL.Workbooks.Add();
var oSheet, rows, columns;
oSheet = oWB.Worksheets.Add();
oSheet.name = “文档”;
rows = 100;
columns = 100;
if (columns == null) continue;
for (var i = 0; i < columns.length; i++) {
oSheet.Cells(1, i + 1).value = i;
oSheet.Columns(i + 1).NumberFormatLocal = "@";
}
for (var i = 0; i < rows.length; i++) {
for (var j = 0; j < columns.length; j++) {
oSheet.Cells(i + 2, j + 1).value = i;
}
} oSheet.Columns.AutoFit; //自动适应大小 //设置excel可见属性 oXL.Visible = true; fname = oXL.Application.GetSaveAsFilename("连接情况查看.xls", "Excel Spreadsheets (*.xls), *.xls"); if (fname != false) {//防止点击对话框取消按钮出现异常 oWB.SaveAs(fname); } oWB.Close(savechanges = false); oXL.Quit(); oXL = null;
其它浏览器:
function() {
var uri = 'data:application/vnd.ms-excel;base64,',
template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) },
format = function(s, c) {
return s.replace(/{(\w+)}/g,
function(m, p) { return c[p]; }) }
return function(table, name) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))
}
}