asp.net 把table中数据导入excel

Question

如题：

Answer 1

    /*<summary> 
   * 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
   * </summary>
   * <param name="dt">要导出数据的DataTable</param>
   * <param name="head">题头数据</param>
   * <param name="absFileName">文件的绝对路径</param>
   * <returns></returns>
   */
    public static void ExportToExcel(System.Data.DataTable dt, System.Data.DataTable head, string absFileName)
    {
        //设置多少行为一个Sheet
        int RowsToDivideSheet = 65535;
        //计算Sheet数
        int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
        GC.Collect();
        Application excel = null;
        _Workbook xBk = null;
        _Worksheet xSt = null;
        try
        {
            excel = new ApplicationClass();
            xBk = excel.Workbooks.Add(true);            //循环中要使用的变量
            int dvRowStart;
            int dvRowEnd;
            //对全部Sheet进行操作
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
            {
                //计算起始行
                dvRowStart = sheetIndex * RowsToDivideSheet;
                dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
                if (dvRowEnd > dt.Rows.Count - 1)
                {
                    dvRowEnd = dt.Rows.Count - 1;
                }                //创建一个Sheet
                if (null == xSt)
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                }
                else
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
                }
                //设置SheetName
                xSt.Name = "Excel";
                if (sheetCount > 1)
                {
                    xSt.Name += ((int)(sheetIndex + 1)).ToString();
                }                //题头导出
                int rowCount = head.Rows.Count;
                int colCount = head.Columns.Count;
                object[,] dataArray = new object[colCount, rowCount];
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        dataArray[j, i] = head.Rows[i][j];
                    }
                }
                xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
                xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
                xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
                xSt.Columns.AutoFit();                //数据导出
                System.Data.DataTable SheetTable = new System.Data.DataTable();
                foreach (DataColumn dc in dt.Columns)
                {
                    DataColumn newdc = new DataColumn();
                    newdc.ColumnName = dc.ColumnName;
                    newdc.DataType = dc.DataType;
                    SheetTable.Columns.Add(newdc);
                }                for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
                {
                    SheetTable.ImportRow(dt.Rows[drvIndex]);
                }
                //保存数据
                rowCount = SheetTable.Rows.Count;
                colCount = SheetTable.Columns.Count;
                object[,] dataArray1 = new object[rowCount, colCount];
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        dataArray1[i, j] = SheetTable.Rows[i][j];
                    }
                }
                xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
                xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
                xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;              
                xSt.Columns.AutoFit();            }
            //删除Sheet1
            excel.DisplayAlerts = false; //注意一定要加上这句
            ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
            excel.DisplayAlerts = true;//注意一定要加上这句            object objOpt = System.Reflection.Missing.Value;
            excel.Visible = false;            xBk.SaveCopyAs(absFileName);
            xBk.Close(false, null, null);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            xBk = null;
            excel = null;
            xSt = null;
        }
        catch (Exception e)
        {
            xBk.Close(false, null, null);
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            xBk = null;
            excel = null;
            xSt = null;
        }
    }

Answer 2

我想你是方法能用，但是我要的是table不是datatable，如果是datatable的话
public void ExportToExcel(DataTable   dt)//这里传进参数DataTable
    {
        
        string fileName = "表" + DateTime.Now.ToShortDateString();//文件的名称
        this.Response.Clear();
        this.Response.Charset = "gb2312 ";
        this.Response.ContentEncoding = System.Text.Encoding.Default;
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.AddHeader("Pragma", "public");
        this.Response.AddHeader("Cache-Control", "max-age=0");
        this.Response.AppendHeader("content-disposition", "attachment; filename=" + HttpContext.Current.Server.UrlPathEncode(fileName) + ".xls");
        string colHeaders = "";
        string ls_item = "";
        System.IO.StringWriter stringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlTextWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
        this.EnableViewState = false;        if (dt != null)
        {
            // header 
            for (int i = 0; i < dt.Columns.Count - 1; i++)
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\t ";
            }
            colHeaders += dt.Columns[dt.Columns.Count - 1].Caption.ToString() + "\n ";            Response.Write(colHeaders);            // table body
            foreach (DataRow dr in dt.Rows)
            {
                // ID
                ls_item += dr[0].ToString() + "\t ";
                // digitalID
                ls_item += dr[1].ToString() + "\t ";
                // loginname
                ls_item += dr[2].ToString() + "\t ";
                // createdate
                ls_item += dr[3].ToString() + "\t ";
                // loginnam
                ls_item += dr[4].ToString() + "\t ";
                // corname
                ls_item += dr[5].ToString() + "\n";                Response.Write(ls_item);                ls_item = "";            }
            this.Response.End();
        }
    }
这个方法也行！不过还是谢谢你!

Answer 3

这个问题我是搞定了  可是问题又出项了  我的table里面用的是
 <table style="border: black 1px solid;" cellpadding="0" cellspacing="0">
                    <asp:Literal ID="litSum" runat="server"></asp:Literal>
  </table>
当时 如果我把table加了unat="server" id="table1"  就出项问题错误 1 与“System.Web.UI.HtmlControls.HtmlTableRowCollection.Add(System.Web.UI.HtmlControls.HtmlTableRow)”最匹配的重载方法具有一些无效参数 D:\htgl\Htlist\Summary.aspx 64
错误 2 参数“1”: 无法从“System.Web.UI.WebControls.Literal”转换为“System.Web.UI.HtmlControls.HtmlTableRow” D:\htgl\Htlist\Summary.aspx 64
搞了很久没搞定