MVC3 Excel 导出
在MVC3的页面上，我开始是用js导出的Excel，
<script type="text/javascript">
function exportTableToExcel(table_id) {
alert("1");
var o_AXO = new ActiveXObject("Excel.Application");
alert("2");
var o_WB;
var o_Sheet;
try {
o_WB = o_AXO.Workbooks.Add();
//激活当前sheet
o_Sheet = o_WB.ActiveSheet;
} catch (err) {
alert("同时请确认已经安装好Excel软件");
return false;
}
var o_table = document.getElementById(table_id);
var row_len = o_table.rows.length;
for (i = 0; i < row_len; i++) {
var cell_len = o_table.rows[i].cells.length;
for (j = 0; j < cell_len; j++) {
var cellText = o_table.rows[i].cells[j].innerText;
o_Sheet.Cells(i + 1, j + 1).value = cellText;
}
}
o_AXO.Visible = true;
}
</script>但是换了台机子就不行了， var o_AXO = new ActiveXObject("Excel.Application");这句话不能执行了，以前是可以的，页面有一个table表，但是分页的就不行了，只能导出当前页面，请问JS能处理分页的导出吗，还有一种做法是后台导出，谁能给个成功的例子，要是MVC3的环境下，谢谢！！
[解决办法]
/// <summary> /// 导出Grid的数据(全部)到Excel /// 字段全部为BoundField类型时可用 /// 要是字段为TemplateField模板型时就取不到数据 /// </summary> /// <param name="grid">grid的ID</param> /// <param name="dt">数据源</param> /// <param name="excelFileName">要导出Excel的文件名</param> public static void OutputExcel(GridView grid, DataTable dt, string excelFileName) { Page page = (Page)HttpContext.Current.Handler; page.Response.Clear(); string fileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(excelFileName)); page.Response.AddHeader("Content-Disposition", "attachment:filename=" + fileName + ".xls"); page.Response.ContentType = "application/vnd.ms-excel"; page.Response.Charset = "utf-8"; StringBuilder s = new StringBuilder(); s.Append("<HTML><HEAD><TITLE>" + fileName + "</TITLE><META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></head><body>"); int count = grid.Columns.Count; s.Append("<table border=1>"); s.AppendLine("<tr>"); for (int i = 0; i < count; i++) { if (grid.Columns[i].GetType() == typeof(BoundField)) s.Append("<td>" + grid.Columns[i].HeaderText + "</td>"); //s.Append("<td>" + grid.Columns[i].HeaderText + "</td>"); } s.Append("</tr>"); foreach (DataRow dr in dt.Rows) { s.AppendLine("<tr>"); for (int n = 0; n < count; n++) { if (grid.Columns[n].Visible && grid.Columns[n].GetType() == typeof(BoundField)) s.Append("<td>" + dr[((BoundField)grid.Columns[n]).DataField].ToString() + "</td>"); } s.AppendLine("</tr>"); } s.Append("</table>"); s.Append("</body></html>"); page.Response.BinaryWrite(System.Text.Encoding.GetEncoding("utf-8").GetBytes(s.ToString())); page.Response.End(); }
[解决办法]
/// <summary> /// 导出Excel /// </summary> /// <param name="obj"></param> public void ExportData(GridView obj) { try { string style = ""; if (obj.Rows.Count > 0) { style = @"<style> .text { mso-number-format:\@; } </script> "; } else { style = "no data."; } HttpContext.Current.Response.ClearContent(); DateTime dt = DateTime.Now; string filename = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString(); HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=ExportData" + filename + ".xls"); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); obj.RenderControl(htw); HttpContext.Current.Response.Write(style); HttpContext.Current.Response.Write(sw.ToString()); HttpContext.Current.Response.End(); } catch { } }
[解决办法]
using System;using System.Diagnostics;//using Excel;namespace DotNet.Utilities{ /// <summary> /// 操作EXCEL导出数据报表的类 /// </summary> public class DataToExcel { public DataToExcel() { } #region 操作EXCEL的一个类(需要Excel.dll支持) private int titleColorindex = 15; /// <summary> /// 标题背景色 /// </summary> public int TitleColorIndex { set { titleColorindex = value; } get { return titleColorindex; } } private DateTime beforeTime; //Excel启动之前时间 private DateTime afterTime; //Excel启动之后时间 #region 创建一个Excel示例 /// <summary> /// 创建一个Excel示例 /// </summary> public void CreateExcel() { //Excel.Application excel = new Excel.Application(); //excel.Application.Workbooks.Add(true); //excel.Cells[1, 1] = "第1行第1列"; //excel.Cells[1, 2] = "第1行第2列"; //excel.Cells[2, 1] = "第2行第1列"; //excel.Cells[2, 2] = "第2行第2列"; //excel.Cells[3, 1] = "第3行第1列"; //excel.Cells[3, 2] = "第3行第2列"; ////保存 //excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); ////打开显示 //excel.Visible = true; //// excel.Quit(); //// excel=null; //// GC.Collect();//垃圾回收 } #endregion #region 将DataTable的数据导出显示为报表 /// <summary> /// 将DataTable的数据导出显示为报表 /// </summary> /// <param name="dt">要导出的数据</param> /// <param name="strTitle">导出报表的标题</param> /// <param name="FilePath">保存文件的路径</param> /// <returns></returns> //public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath) //{ // beforeTime = DateTime.Now; // Excel.Application excel; // Excel._Workbook xBk; // Excel._Worksheet xSt; // int rowIndex = 4; // int colIndex = 1; // excel = new Excel.ApplicationClass(); // xBk = excel.Workbooks.Add(true); // xSt = (Excel._Worksheet)xBk.ActiveSheet; // //取得列标题 // foreach (DataColumn col in dt.Columns) // { // colIndex++; // excel.Cells[4, colIndex] = col.ColumnName; // //设置标题格式为居中对齐 // xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true; // xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; // xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select(); // xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种 // } // //取得表格中的数据 // foreach (DataRow row in dt.Rows) // { // rowIndex++; // colIndex = 1; // foreach (DataColumn col in dt.Columns) // { // colIndex++; // if (col.DataType == System.Type.GetType("System.DateTime")) // { // excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); // xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 // } // else // if (col.DataType == System.Type.GetType("System.String")) // { // excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); // xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 // } // else // { // excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); // } // } // } // //加载一个合计行 // int rowSum = rowIndex + 1; // int colSum = 2; // excel.Cells[rowSum, 2] = "合计"; // xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // //设置选中的部分的颜色 // xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select(); // //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种 // //取得整个报表的标题 // excel.Cells[2, 2] = strTitle; // //设置整个报表的标题格式 // xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true; // xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22; // //设置报表表格为最适应宽度 // xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select(); // xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit(); // //设置整个报表的标题为跨列居中 // xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select(); // xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; // //绘制边框 // xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1; // xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗 // xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗 // xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗 // xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 // afterTime = DateTime.Now; // //显示效果 // //excel.Visible=true; // //excel.Sheets[0] = "sss"; // ClearFile(FilePath); // string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; // excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); // //wkbNew.SaveAs strBookName; // //excel.Save(strExcelFileName); // #region 结束Excel进程 // //需要对Excel的DCOM对象进行配置:dcomcnfg // //excel.Quit(); // //excel=null; // xBk.Close(null, null, null); // excel.Workbooks.Close(); // excel.Quit(); // //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程 // // if(rng != null) // // { // // System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); // // rng = null; // // } // // if(tb != null) // // { // // System.Runtime.InteropServices.Marshal.ReleaseComObject(tb); // // tb = null; // // } // if (xSt != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); // xSt = null; // } // if (xBk != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); // xBk = null; // } // if (excel != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); // excel = null; // } // GC.Collect();//垃圾回收 // #endregion // return filename; //} #endregion