以下是我一段导处excel的代码,导出来数据都是对的,但是更本不是excel只是回缀名是xls因为我把导出的excel在导入的时候会报外部表不是预期的格式这个错误,而我试过,我把导出的excel打开后另存为xls格式在导入就不会报错,或者我自己建一个xls导进去也不会报错,请大家帮帮忙改动以下代码,怎么样改才能导出来的是真正的excel表。
protected void Button2_Click(object sender, EventArgs e)
{
ds = new DataSet();
SqlConnection con = new SqlConnection("Data Source=10.17.8.7;Initial Catalog=CRMOPSC;User ID=sa;Password=password");
SqlDataAdapter da = new SqlDataAdapter("Select Id,LotNo,Enterprise,CardName,CardCode,NameCode,Demand,DeliveryDate,Mark,Height,Width,Length,Weight,Summation,R,Increase_Decrease,Memo,TWFH,Single_Storage,Sum_Storage,Grade,S16,S12,S9,S5,S4,S2 From Alan", con);
da.Fill(ds,"Alan");
DataTable dt = ds.Tables["Alan"]; dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
dt.Columns["CardName"].ColumnName = "客户";
dt.Columns["CardCode"].ColumnName = "单号";
dt.Columns["NameCode"].ColumnName = "客户编号";
dt.Columns["Demand"].ColumnName = "需求";
dt.Columns["DeliveryDate"].ColumnName = "交期";
dt.Columns["Mark"].ColumnName = "料";
dt.Columns["Height"].ColumnName = "厚";
dt.Columns["Width"].ColumnName = "宽";
dt.Columns["Length"].ColumnName = "长";
dt.Columns["Weight"].ColumnName = "重";
dt.Columns["Summation"].ColumnName = "总";
dt.Columns["R"].ColumnName = "R";
dt.Columns["Increase_Decrease"].ColumnName = "增减";
dt.Columns["Memo"].ColumnName = "备注";
dt.Columns["TWFH"].ColumnName = "T双喷";
dt.Columns["Single_Storage"].ColumnName = "单存";
dt.Columns["Sum_Storage"].ColumnName = "总存";
dt.Columns["Grade"].ColumnName = "查特代";
dt.Columns["S16"].ColumnName = "16支装";
dt.Columns["S12"].ColumnName = "12支装";
dt.Columns["S9"].ColumnName = "9支装";
dt.Columns["S5"].ColumnName = "5支装";
dt.Columns["S4"].ColumnName = "4支装";
dt.Columns["S2"].ColumnName = "2支装"; StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel"; HtmlTextWriter htw = new HtmlTextWriter(sw); dv.RenderControl(htw); Response.Write(sw.ToString());
Response.End(); }
protected void Button2_Click(object sender, EventArgs e)
{
ds = new DataSet();
SqlConnection con = new SqlConnection("Data Source=10.17.8.7;Initial Catalog=CRMOPSC;User ID=sa;Password=password");
SqlDataAdapter da = new SqlDataAdapter("Select Id,LotNo,Enterprise,CardName,CardCode,NameCode,Demand,DeliveryDate,Mark,Height,Width,Length,Weight,Summation,R,Increase_Decrease,Memo,TWFH,Single_Storage,Sum_Storage,Grade,S16,S12,S9,S5,S4,S2 From Alan", con);
da.Fill(ds,"Alan");
DataTable dt = ds.Tables["Alan"]; dt.Columns["Id"].ColumnName = "ID";
dt.Columns["LotNo"].ColumnName = "订单批号";
dt.Columns["Enterprise"].ColumnName = "业";
dt.Columns["CardName"].ColumnName = "客户";
dt.Columns["CardCode"].ColumnName = "单号";
dt.Columns["NameCode"].ColumnName = "客户编号";
dt.Columns["Demand"].ColumnName = "需求";
dt.Columns["DeliveryDate"].ColumnName = "交期";
dt.Columns["Mark"].ColumnName = "料";
dt.Columns["Height"].ColumnName = "厚";
dt.Columns["Width"].ColumnName = "宽";
dt.Columns["Length"].ColumnName = "长";
dt.Columns["Weight"].ColumnName = "重";
dt.Columns["Summation"].ColumnName = "总";
dt.Columns["R"].ColumnName = "R";
dt.Columns["Increase_Decrease"].ColumnName = "增减";
dt.Columns["Memo"].ColumnName = "备注";
dt.Columns["TWFH"].ColumnName = "T双喷";
dt.Columns["Single_Storage"].ColumnName = "单存";
dt.Columns["Sum_Storage"].ColumnName = "总存";
dt.Columns["Grade"].ColumnName = "查特代";
dt.Columns["S16"].ColumnName = "16支装";
dt.Columns["S12"].ColumnName = "12支装";
dt.Columns["S9"].ColumnName = "9支装";
dt.Columns["S5"].ColumnName = "5支装";
dt.Columns["S4"].ColumnName = "4支装";
dt.Columns["S2"].ColumnName = "2支装"; StringWriter sw = new StringWriter();
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
dv.AllowPaging = false; Response.ClearContent();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel"; HtmlTextWriter htw = new HtmlTextWriter(sw); dv.RenderControl(htw); Response.Write(sw.ToString());
Response.End(); }
{
this.DataGrid1.AllowPaging = false;
string sql="select * from car_usestatus where state='已入库' order by out_time DESC";
DatabaseConnect c=new DatabaseConnect();
DataSet ds=c.getBinding(sql,"t"); Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-Excel";
Response.Charset = "";
this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
DataGrid1.DataSource = ds.Tables["t"].DefaultView; //返回DataSet或DataTable
DataGrid1.DataBind(); ClearControls(DataGrid1); DataGrid1.RenderControl(oHtmlTextWriter); Response.ContentEncoding = System.Text.Encoding.UTF8; Response.Write(oStringWriter.ToString()); Response.End();
this.DataGrid1.AllowPaging = true;
}
private void ClearControls(Control control)
{
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
ClearControls(control.Controls[i]);
} if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}
xmlns:w="urn:schemas.microsoft.com:office/word"
xmlns=urn:w3-org-ns:HTML>
<xml>
<o:DocumentProperties>
<o:Created>1997-11-18T00:10:21Z</o:Created>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:ReadOnlyRecommended/>
</o:OfficeDocumentSettings>
<w:WordDocument>
<w:CurrentView>Normal</w:CurrentView>
</w:WordDocument>
</xml>
如果不能用我上面的那种方法导的话.那应该怎么样写导出的代码?
看看源代码,照那个格式修改,一般加上
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">就差不多了