使用C＃导出到ASP.Net中的Excel [英] Export to Excel in ASP.Net with C#

问题描述

在C＃中，我通过SQL SP将Excel导入表中。然后我更新该表。之后我将该表导出到Excel中（导出到Excel期间我得到如下错误）

In C# I import Excel into Table through SQL SP.Then I update that table.After that I Export that Table into Excel(During Export to Excel I got Error as below)

HttpException was unhandled by user code

System.Web.HttpException was unhandled by user code
  Message="DataGrid with id '' could not automatically generate any columns from the selected data source."

我的编码

My coding

 protected void Button1_Click(object sender, EventArgs e)
 {
     connectionstring();
     SqlConnection con = new SqlConnection(str);
     con.Open();
     SqlCommand cmd = new SqlCommand("SP_Excel", con);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add("@ExcelName", SqlDbType.VarChar).Value = FileUpload1.FileName.ToString();
     cmd.ExecuteNonQuery();
     con.Close();
     Response.Write("<script>alert(''Table Imported Successfully'');</script>");
     Button1.Visible = false;
     Button2.Visible = true;

     SqlDataAdapter da = new SqlDataAdapter(cmd);
     DataTable dt = new DataTable();
     da.Fill(dt);
     GridView1.DataSource = dt;
     GridView1.DataBind();
     ViewState["dtList"] = dt;


protected void Button2_Click(object sender, EventArgs e)
 {
     DataTable dt1 = (DataTable)ViewState["dtList"];
     if (dt1 == null)
     {
         throw new Exception("No Records to Export");
     }
     string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xlsb";
     FileInfo FI = new FileInfo(Path);
     StringWriter stringWriter = new StringWriter();
     HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
     DataGrid DataGrd = new DataGrid();
     DataGrd.DataSource = dt1;
     DataGrd.DataBind();
     DataGrd.RenderControl(htmlWrite);
     string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
     if (!Directory.Exists(directory))
     {
         Directory.CreateDirectory(directory);
     }
     System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
     stringWriter.ToString().Normalize();
     vw.Write(stringWriter.ToString());
     vw.Flush();
     vw.Close();
     WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
 }
 public static void WriteAttachment(string FileName, string FileType, string content)
 {
     HttpResponse Response = System.Web.HttpContext.Current.Response;
     Response.ClearHeaders();
     Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
     Response.ContentType = FileType;
     Response.Write(content);
     Response.End();
 }

推荐答案

而不是

Response.AppendHeader（Content-处理，附件; filename =+ FileName;

尝试

Response.AddHeader（Content-Disposition，string.Format（attachment; filename = { 0}+ FileName;

instead of
Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
try
Response.AddHeader("Content-Disposition", string.Format("attachment; filename = {0}" + FileName);

您好，



您必须将MIME类型更改为

application / vnd.ms-excel.sheet.binary.macroEnabled.12 用于生成 .xlsb 个文件。< br $> b $ b

请参考什么是Microsoft Office MIME类型？ [< a href =http://filext.com/faq/office_mime_types.phptarget =_ blanktitle =New Window> ^ ]用于各种类型的有效MIME类型。



所以，更改你的代码如下。

Hi,

you have to change the MIME type as
application/vnd.ms-excel.sheet.binary.macroEnabled.12 for generating .xlsb files.

refer What are the Microsoft Office MIME Types?[^] for valid MIME types for various types.

So, change your code as below.

WriteAttachment(FI.Name, "application/vnd.ms-excel.sheet.binary.macroEnabled.12", stringWriter.ToString());

希望它有所帮助。

hope it helps.

看起来像您的数据网格无法遍历数据源中的列（excel，来自viewstate）这并不奇怪。您的网格怎么能知道您的woorksheet中有哪些列？在Excel中，您可以将标题放在第5行，如果需要，可以在C列开始列。并且每张表中可以有多个表。 Excel的结构与数据库的结构不同。



您几乎可以这样做，但是在导出时必须从数据库中读取数据，不是来自你的观点（这是优秀的）



一点点提示。这个开源项目非常棒。它将您的数据导出为ex​​cel并且它非常易于使用：

http：// closedxml。 codeplex.com/ [ ^ ]

Looks like your datagrid is not able to iterate through columns from your datasource (excel, from viewstate) That is not that strange. How on earth can your grid know what columns you have in your woorksheet? In Excel you can put your heading in row number 5 and start your columns at column C if you want. And you can have multiple "table" in each sheet. Excel is not structured the same way as a database.

You could do this almost as you do, but you will have to read the data from your database when you export, not from your viewstate (which is excel)

A little tip. This open source project is superb. It''l export your data as excel and it''s very easy to use:
http://closedxml.codeplex.com/[^]

这篇关于使用C＃导出到ASP.Net中的Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！