private void ExportExcle()
{
GC.Collect();
Application excel = new Application();
_Workbook xBk;
_Worksheet xSt;
excel= new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
excel.Cells.Font.Name ="宋体";
excel.Cells.Font.Size ="10";
try
{
xSt.get_Range(excel.Cells[1,1],excel.Cells[1,1]).Interior.ColorIndex = 33;
//第一行写指标
for(int i=0;i<=intColumns-1;i++)
{
excel.Cells[1,i+2] = indName[i];
//颜色
xSt.get_Range(excel.Cells[1,i+2],excel.Cells[1,i+2]).Interior.ColorIndex = 33;
excel.Columns.AutoFit();//自动调整宽度
}
//读出初始时间(每行依次+1)
string strDate = HttpUtility.UrlDecode(Request.Cookies["IsDate"].Value);
string[] strArrUrl = strDate.Split(new char[]{','});
for(int j=2;j<=strArrUrl.Length;j++)
{
excel.Cells[j,1] = strArrUrl[j-2].Split(';');
xSt.get_Range(excel.Cells[j,1],excel.Cells[j,1]).Interior.ColorIndex = 33;
string strdbid = HttpUtility.UrlDecode(Request.Cookies["DBID"].Value);
if(strdbid == "year" || strdbid == "dq")
{
strTemp = strArrUrl[j-2].Substring(0,4) + "0101";
}
else
{
strTemp = strArrUrl[j-2].Substring(0,4) + strArrUrl[j-2].Substring(5,2) +"01";
}
//创建XML文件(内容为txtValue.Text)
System.Xml.XmlDataDocument datadoc = new System.Xml.XmlDataDocument();
datadoc.LoadXml(txtData.Value);
System.Xml.XmlNode node = datadoc.SelectSingleNode("//resultset");
for(int y=0;y<=intColumns-2;y++)
{
//根据时间和此时指示名选择出"数据"和"单位" 依次显示
string s = "result[AREANAME='"+ strArea +"' and INDICATORNAME='"+ indName[y] +"' and DATE='"+ strTemp +"']";
System.Xml.XmlNode node2 = node.SelectSingleNode(s);
if(node2 != null)
{
excel.Cells[j,y+2] = node2.SelectSingleNode("DATA").InnerText + node2.SelectSingleNode("UNIT").InnerText;
}
else
{
excel.Cells[j,y+2] = "-";
}
}
}
}
catch(Exception ex)
{
string strMessage = ex.Message;
return;
}
//显示效果
excel.Caption = strArea + "数据查询导出";
excel.Visible=false;
xBk.SaveCopyAs(Server.MapPath(".")+"//test.xls");
xBk.Close(false, null,null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
//excel.Quit();
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath("Test.xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset="GB2312";
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}