weixin_33725722 2017-06-12 11:03 采纳率: 0%
浏览 319

使用Ajax导出Excel

I have a this code for excel export from database using MVC.

This code working on Index view but if the code in different ActionResult with parameters ( ajax post data ) not working. I can get values using ajax, I can create DataTable from database, datatable can write to excel but not downloading.

Using ClosedXML for excel export.

What can I do for it ?

Thanks for your help.

 string constr =ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt, "Customers");

                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
    }
}
  • 写回答

2条回答 默认 最新

  • weixin_33737774 2017-06-13 02:43
    关注

    Hi you can return a stream inorder to download the file in response using the stream

    Here is the example:

      DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Country",typeof(string)) });
                dt.Rows.Add(1, "C Sharp corner", "United States");
                dt.Rows.Add(2, "Suraj", "India");
                dt.Rows.Add(3, "Test User", "France");
                dt.Rows.Add(4, "Developer", "Russia");
                //Exporting to Excel
                string folderPath = "C:\\Excel\\";
                if (!Directory.Exists(folderPath))
                {
                    Directory.CreateDirectory(folderPath);
                }
                //Codes for the Closed XML
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt, "Customers");
    
                    //wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
                    string myName = Server.UrlEncode("Test" + "_" + DateTime.Now.ToShortDateString() +          ".xlsx");
                    MemoryStream stream = GetStream(wb);// The method is defined below
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment; filename=" + myName);
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.BinaryWrite(stream.ToArray());
                    Response.End();
                }
    

    GetStream method:

    public MemoryStream GetStream(XLWorkbook excelWorkbook)
            {
                MemoryStream fs = new MemoryStream();
                excelWorkbook.SaveAs(fs);
                fs.Position = 0;
                return fs;
            }
    

    Source: http://surajpassion.in/create-an-excel-file-using-closedxml/

    Hope it will solve your problem.

    Thanks

    Karthik

    评论

报告相同问题?

悬赏问题

  • ¥20 树莓派5做人脸情感识别与反馈系统
  • ¥15 selenium 控制 chrome-for-testing 在 Linux 环境下报错 SessionNotCreatedException
  • ¥15 使用pyodbc操作SQL数据库
  • ¥15 MATLAB实现下列
  • ¥30 mininet可视化打不开.mn文件
  • ¥50 C# 全屏打开Edge浏览器
  • ¥80 WEBPACK性能优化
  • ¥30 python拟合回归分析
  • ¥500 metaswitch 6010
  • ¥15 关于#分类#的问题:不用人工智能的算法