读书人
栏目▼

MVC3 Excel 导出,该怎么处理

发布时间： 2013-01-23 10:44:50 作者： rapoo

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

读书人网 >C#

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net