导出Excel 并提示下载Web版,纯手工制作
dgForm是.Net1.0中的DataGrid
private void Button1_ServerClick(object sender, System.EventArgs e)
{
string sqlup = "";
//这个for循环,读取要导出的数据行,在DataGrid中哪些行被选中了
for(int i = 0;i < this.dgForm.Items.Count;i++)
{
if(this.dgForm.Items[i].Cells[0].Controls[1].GetType().ToString().Trim() == "System.Web.UI.WebControls.CheckBox")
{
if(((System.Web.UI.WebControls.CheckBox)(this.dgForm.Items[i].Cells[0].Controls[1])).Checked == true)
{
sqlup += this.dgForm.DataKeys[i].ToString() + ",";// this.DataGrid1.Items[i].Cells[6].Text.Trim() + ",";
}
}
}
if(sqlup != "")
{
sqlup = sqlup.Remove(sqlup.Length - 1,1);
//导出
// try
// {
//kill Excel进程
KillExcelProcess();
//报表存放路径
string name=DateTime.Now.Year.ToString()+DateTime.Now.Month.ToString()+DateTime.Now.Day.ToString()+DateTime.Now.Hour.ToString()+DateTime.Now.Minute.ToString()+DateTime.Now.Second.ToString();
string path=Server.MapPath(".")+"/a.xls";//这个a.xls是事先准备好的Excel文件,有列头
if(File.Exists(path))
{
//File.Copy(Server.MapPath(".")+"/b.xlt",Server.MapPath(".")+"/a.xls",true);
File.Delete(path);
}
//创建整机批次报表
Excel.Application excel;
Excel._Workbook workbook;
Excel._Worksheet worksheet;
excel= new Excel.ApplicationClass();// Excel.ApplicationClass();
//打开模板文件,得到WorkBook对象
workbook = excel.Workbooks.Open(Server.MapPath(".")+"/b.xlt",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
//得到WorkSheet对象
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
//开始填写返修通知单
AdoHelper adp = AdoHelper.CreateHelper(AdoHelper.oldassembly,AdoHelper.oldtype);
string sql = "select * from I_最终检验统计 where 编号 in ("+sqlup+")";
IDataReader Dr = adp.ExecuteReader(AdoHelper.sqlconnection,System.Data.CommandType.Text,sql);
int ioc = 4;
int ipp = 1;
while(Dr.Read())
{
// if(ioc == 5)
// {
// worksheet.Cells[3,2]= Dr["送检日期"].ToString();
// worksheet.Cells[3,6]= Dr["送检单号"].ToString();
// }
worksheet.Cells[ioc,1]= ipp.ToString();
worksheet.Cells[ioc,2]= Dr["名称"].ToString() + Dr["型号"].ToString();
worksheet.Cells[ioc,3]= Dr["批次号"].ToString();
worksheet.Cells[ioc,4]= Dr["合同号"].ToString();
worksheet.Cells[ioc,5]= Dr["数量"].ToString();
worksheet.Cells[ioc,6]= Dr["一次提交合格率"].ToString();
worksheet.Cells[ioc,7]= Dr["不合格数"].ToString();
worksheet.Cells[ioc,8]= Dr["故障现象"].ToString();
worksheet.Cells[ioc,9]= Dr["维修记录"].ToString();
worksheet.get_Range(worksheet.Cells[ioc,1],worksheet.Cells[ioc,9]).Borders.LineStyle = 1;
ioc = ioc + 1;
ipp = ipp + 1;
}
Dr.Close();
//
workbook.SaveAs(path,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
workbook.Close(null,null,null);
excel.Workbooks.Close();
excel.Quit();
//kill Excel进程
KillExcelProcess();
//下载报表文件
if(File.Exists(path))
{
FileInfo fi = new FileInfo(path);
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(Path.GetFileName(path),System.Text.Encoding.Default));
Response.AppendHeader("Content-Length",fi.Length.ToString());
Response.ContentType="application/octet-stream";
Response.WriteFile(path);
Response.Flush();
//
File.Delete(path);
//
Response.End();
}
// }
// catch
// {
// }
}
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess()
{
Process[] myProcesses;
myProcesses = Process.GetProcessesByName("Excel");
if(myProcesses.Length!=0)
{
foreach(Process myProcess in myProcesses)
{
myProcess.Kill();
}
}
}
另外直接使用DataGrid的方法导出Excel
using System;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace DataExtender
{
/// <summary>
/// OutExcel 的摘要说明。
/// </summary>
public class OutExcel
{
private string _file;//保存的Excel文件名以及路径
private DataGrid _mydataGrid;
public OutExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void outExcel(Page page)
{
StringWriter sw=new StringWriter();
HtmlTextWriter htw=new HtmlTextWriter(sw);
this.MyDataGrid.RenderControl(htw);
string filestr=this.File;//filestr 保存的Excel文件名以及路径
int pos = filestr.LastIndexOf( "\\");
string file = filestr.Substring(0,pos);
if( !Directory.Exists( file ) )
{
Directory.CreateDirectory(file);
}
StreamWriter sisw = new StreamWriter(filestr);
sisw.Write(sw.ToString());
sisw.Close();
}
public string File
{
get{return this._file;}
set{this._file=value;}
}
public DataGrid MyDataGrid
{
get{return this._mydataGrid;}
set{this._mydataGrid=value;}
}
}
}
附件下载地址:(把.txt去掉)
/Files/qiaojun/OutExcel.cs.txt