求一导出excel方法

Question

我已经在后面创建了一个excel但我不想保存成物理文件，然后再下载。
希望可以通过类似于RenderControl()的方法实现输出到页面下载。
我excel内的数据不在本页面，而且列很多，所以放弃了用控件的方法。
生成的文件是固定的，每个人下载时都是去数据库抓数据，如果生成了物理文件那么下载完了还要再删除掉。并且同时操作容易出问题。（worksheet.SaveAs(Server.MapPath("../../Export/ItemMasterData.csv"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);）
所以想用输出流的方式来做。求解。
代码如下：
Excel.Application excelApp = new Excel.Application();
            Excel.Workbooks workbooks = excelApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(true);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);            for (int i = 0; i < dtExport.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dtExport.Columns[i].ColumnName;
            }            int rowIndex = 2;
            int cellIndex = 1;
            foreach (DataRow var in dtExport.Rows)
            {
                foreach (DataColumn vars in dtExport.Columns)
                {
                    worksheet.Cells[rowIndex, cellIndex] = var[vars].ToString();
                    cellIndex++;
                }
                rowIndex++;
                cellIndex = 1;
            }            worksheet.SaveAs(Server.MapPath("../../Export/ItemMasterData.csv"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            
            workbook.Close(Type.Missing, Type.Missing, Type.Missing);
            workbooks.Close();
            excelApp.Quit();
            workbook = null;
            workbooks = null;
            excelApp = null;主意看上面的描述。

Answer 1

使用Aspose.Cells.dll
        
public static string CreateExcel(string filename, DataTable dt)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            try
            {
                book.Styles.Add();
                Style style = book.Styles[0];
                //style.VerticalAlignment = TextAlignmentType.Center;
                //style.HorizontalAlignment = TextAlignmentType.Center;
                //style.Font.Color = Color.Green;
                style.Font.IsBold = true;
                //style.ShrinkToFit = true;
                style.ForegroundColor = Color.Gray;
                //style.Font. = 
                //style.BackgroundColor = Color.Gray;
                style.Pattern = BackgroundType.Solid;
                //style.Borders.DiagonalStyle                StyleFlag styleFlag = new StyleFlag();
                styleFlag.All = true;                Row row = sheet.Cells.Rows[0];
                row.ApplyStyle(style, styleFlag);                sheet.Cells.ImportDataTable(dt, true, 0, 0);
                sheet.Name = "raw data";
                book.Save(filename);
            }
            catch (Exception ex)
            {
                filename = "";
                throw (ex);
            }
            finally
            {
                sheet = null;
                book = null;
            }
            return filename;
        }

Answer 2

如何导入数据至Excel文件 C#.net 

Answer 3

    private void DataTableToExcel(DataTable dt)
    {
        if (dt != null)
        {
            GridView dvExport = new GridView();
            dvExport.AllowPaging = false;
            dvExport.DataSource = dt;
            dvExport.DataBind();            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "UTF-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=CESReport.xls"); //.xls的文件名可修改
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel"; //设置输出文件类型为excel文件。   
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            dvExport.RenderControl(oHtmlTextWriter);
            Response.Output.Write(oStringWriter.ToString());
            Response.Flush();
        }
    }

Answer 4

Sys.WebForms.PageRequestManagerParseerrorException:The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(),response filters,HttpModules,or server trace is enabled.
Details: Error parsing near '<div> <table cells'.