导出Excel 并提示下载Web版

最新推荐文章于 2024-08-26 20:29:10 发布

我是一只猫一只猫

最新推荐文章于 2024-08-26 20:29:10 发布

阅读量1.1k

点赞数

文章标签： excel web ioc datagrid string path

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;}
  }
}
}