asp.net生成Excel并导出下载五种实现方法(aspnet内置对象有哪些)学到了吗

随心笔谈1年前发布 编辑
154 0

private void ExcelBut_Click(object sender, System.EventArgs e)

{

DataSet ds=new DataSet();

string ExcelFileName=””;

DataRow dr=[——列名信息——-];

try

{

ExcelFileName=Path.Combine(Request.PhysicalApplicationPath + “/ExcelFile”, “MyExcel.xls”);

//获取Excel需要的数据

ds=[——获得数据——-];

if (ds.Tables.Count==0)

{

Response.Write(““);

return;

}

int sheetNum=ds.Tables.Count / 2;

StreamWriter writer=new StreamWriter(ExcelFileName, false);

//Styles标签前面的信息相当于’头信息’,不需要改变

writer.WriteLine(”
“);

writer.WriteLine(”
“);

writer.WriteLine(“

writer.WriteLine(“xmlns:o=”urn:schemas-microsoft-com:office:office””);

writer.WriteLine(“xmlns:x=”urn:schemas-microsoft-com:office:excel””);

writer.WriteLine(“xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet””);

writer.WriteLine(“xmlns:html=”http://www.w3.org/TR/REC-html40″>”);

writer.WriteLine(““);

writer.WriteLine(“Automated Report Generator Example“);

writer.WriteLine(string.Format(“{0}T{1}Z“, DateTime.Now.ToString(“yyyy-mm-dd”), DateTime.Now.ToString(“HH:MM:SS”)));

writer.WriteLine(” 51aspx.com“);

writer.WriteLine(” 11.6408“);

writer.WriteLine(“”);

writer.WriteLine(““);

writer.WriteLine(“6195“);

writer.WriteLine(” 18495“);

writer.WriteLine(” 525“);

writer.WriteLine(” 4260“);

writer.WriteLine(” “);

writer.WriteLine(” True“);

writer.WriteLine(” False“);

writer.WriteLine(“”);

//通过 ss:ID 和 ss:Name相当于html中style的类

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(”

writer.WriteLine(” ss:Color=’#FF0000’/> “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(”

writer.WriteLine(” ss:Color=’#FF0000’/> “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(”

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

writer.WriteLine(” “);

//Sheet操作

writer.WriteLine(““);

writer.WriteLine(”

“);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

for (int num=sheetNum – 1; num >=0; num=num – 1)

{

// 考課表間有3行的空行

if (num !=sheetNum – 1)

{

writer.WriteLine(““);

writer.WriteLine(“”);

writer.WriteLine(““);

writer.WriteLine(“”);

writer.WriteLine(““);

writer.WriteLine(“”);

}

// 列名

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg002”] + ““);

writer.WriteLine(“” + dr[“Msg003”] + ““);

writer.WriteLine(“” + dr[“Msg004”] + ““);

writer.WriteLine(“”);

// 详细数据

for (int i=0; i < ds.Tables[2 * num].Rows.Count; i++)

{

writer.WriteLine(““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel1Name”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel2Code”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel2Name”].ToString() + ““);

writer.WriteLine(“”);

}

}

writer.WriteLine(” “);

writer.WriteLine(” “);

// 其他Sheet操作

for (int num=sheetNum – 1; num >=0; num–)

{

writer.WriteLine(““);

writer.WriteLine(”

“);

writer.WriteLine(” “);

writer.WriteLine(“ カキキイロテンスウツ” + dr[“Msg031”] + “
“);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(“”);

writer.WriteLine(” “);

writer.WriteLine(“ カキキイロテンスウツ” + dr[“Msg032”] + “
“);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(“”);

// 列名

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg001”] + ““);

writer.WriteLine(“” + dr[“Msg002”] + ““);

writer.WriteLine(“” + dr[“Msg003”] + ““);

writer.WriteLine(“”);

int SheetN=ds.Tables[2 * num].Rows.Count;

//详细数据

for (int i=0; i < SheetN; i++)

{

writer.WriteLine(““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel1Code”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel1Name”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num].Rows[i][“EvalLevel2Code”].ToString() + ““);

writer.WriteLine(“”);

writer.WriteLine(“”);

}

writer.WriteLine(““);

writer.WriteLine(“”);

writer.WriteLine(““);

writer.WriteLine(“”);

//列名

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg011”] + ““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg012”] + ““);

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg013”] + ““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg014”] + ““);

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg015”] + ““);

writer.WriteLine(““);

writer.WriteLine(“”);

writer.WriteLine(““);

writer.WriteLine(“” + dr[“Msg016”] + ““);

writer.WriteLine(“” + dr[“Msg017”] + ““);

writer.WriteLine(“” + dr[“Msg018”] + ““);

writer.WriteLine(“” + dr[“Msg019”] + ““);

writer.WriteLine(“”);

int DateLen=ds.Tables[2 * num + 1].Rows.Count;

string EmployeeManagement=””;

for (int i=0; i < DateLen; i++)

{

writer.WriteLine(““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EmployeeManagementID”].ToString() + ““);

if (!EmployeeManagement.Equals(ds.Tables[2 * num + 1].Rows[i][“EmployeeManagementID”].ToString()))

{

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EmployeeCode”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EmployeeName”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“BefEmployeeName2”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EmployeeName2”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EvalLevel1Code”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EvalLevel1Name”].ToString() + ““);

EmployeeManagement=ds.Tables[2 * num + 1].Rows[i][“EmployeeManagementID”].ToString();

}

else

{

writer.WriteLine(“”);

writer.WriteLine(“”);

writer.WriteLine(“”);

writer.WriteLine(“”);

writer.WriteLine(“”);

writer.WriteLine(“”);

}

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EvalLevel2Code”].ToString() + ““);

writer.WriteLine(“” + ds.Tables[2 * num + 1].Rows[i][“EvalLevel2Name”].ToString() + ““);

writer.WriteLine(“”);

}

writer.WriteLine(” “);

writer.WriteLine(” “);

}

writer.WriteLine(“”);

writer.Close();

FileDownload(ExcelFileName);

}

catch (System.Exception ex)

{

}

finally

{

Response.End();

}

}

public void FileDownload(FullFileName)

{

FileInfo DownloadFile=new FileInfo(FullFileName);

Response.Clear();

Response.ClearHeaders();

Response.Buffer=true;

Response.AppendHeader(“Content-Disposition”, “attachment;filename=” + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(MyFile”.xls”)));

Response.ContentType=”application/ms-excel;charset=UTF-8″;//DownloadFile.FullNameoctet-stream

Response.ContentEncoding=System.Text.Encoding.GetEncoding(“shift-jis”);

Response.AppendHeader(“Content-Length”, DownloadFile.Length.ToString());

Response.WriteFile(FullFileName);

Response.Flush();

if (File.Exists(FullFileName))

{

File.Delete(FullFileName);

}

}

© 版权声明

相关文章