asp.net中杀掉excel

最新推荐文章于 2025-12-01 22:23:21 发布

转载最新推荐文章于 2025-12-01 22:23:21 发布 · 652 阅读

文章标签：

#asp.net #dataset #exception #sql #excel #string

编程专栏收录该内容

101 篇文章

订阅专栏

本文介绍了一个使用C#结合SQL Server及Microsoft Excel Interop组件来动态生成Excel文件的方法。该方法能够根据数据库中的数据自动生成带有多个工作表的Excel文件，并填充相应的数据。

**.aspx.cs中这么调用项目中的类方法
class.export_template_excel(PadZerosOnString(this.TextBox1.Text.ToString()),Server.MapPath("./file/"));
GC.Collect();

类中改方法的实现注意红色部分
try
   {

    SqlConnection con=new SqlConnection(connect);
    SqlCommand com=new SqlCommand("GetPklist_SAP",con);
    com.CommandType=CommandType.StoredProcedure;
    com.Parameters.Add("@po",System.Data.SqlDbType.VarChar,20);
    com.Parameters["@po"].Value=orderno;
    con.Open();
    com.ExecuteNonQuery();
    com.Dispose();
    con.Close();

    Microsoft.Office.Interop.Excel.Application app=new Microsoft.Office.Interop.Excel.ApplicationClass();
    app.Visible=true;
    Microsoft.Office.Interop.Excel.Workbook workbook=app.Workbooks.Open("excel模板文件",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
    Microsoft.Office.Interop.Excel.Worksheet worksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);

    DataSet ds=new DataSet();
    string sql="select distinct substring(SortCode,1,3) SortCode from T_PODetail where Ordernumber='"+orderno+"'";

    SqlDataAdapter sda=new SqlDataAdapter(sql,con);
    con.Open();
    sda.Fill(ds);

    if(ds.Tables[0].Rows.Count>0)
    {
     for(int i=1;i<ds.Tables[0].Rows.Count;i++) //决定了有几个worksheet
     {
      ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Copy(Missing.Value,workbook.Worksheets.get_Item(1));

     }
    }

    DataSet ds1=new DataSet();
    string sql1="select * from T_POMaster where Ordernumber='"+orderno+"'";
    SqlConnection con1=new SqlConnection(connect);
    SqlDataAdapter sda1=new SqlDataAdapter(sql1,con1);
    con1.Open();
    sda1.Fill(ds1);
    if(ds1.Tables[0].Rows.Count==0)
    {
     return "";
    }

    for(int i=0;i<ds.Tables[0].Rows.Count;i++)
    {
     if(ds.Tables[0].Rows[i]["SortCode"].ToString()!="")
     {
      Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(i+1);
      sheet.Name = ds.Tables[0].Rows[i]["SortCode"].ToString();
      sheet.Cells[3,3]=ds1.Tables[0].Rows[0]["Ordernumber"].ToString();
      sheet.Cells[3,6]=ds1.Tables[0].Rows[0]["Material"].ToString();
      sheet.Cells[3,7]=ds1.Tables[0].Rows[0]["GeneralName"].ToString();
      sheet.Cells[3,12]=ds1.Tables[0].Rows[0]["ScheduledStart"].ToString();
      sheet.Cells[3,16]=ds1.Tables[0].Rows[0]["TotalOrderQty"].ToString();
      sheet.Cells[4,16]=ds.Tables[0].Rows[i]["SortCode"].ToString();

      DataSet ds2=new DataSet();
      string sql2="select * from T_POPrint where Ordernumber='"+orderno+"' and substring(SortCode,1,3)='"+ds.Tables[0].Rows[i]["SortCode"].ToString()+"'";
      SqlConnection con2=new SqlConnection(connect);
      SqlDataAdapter sda2=new SqlDataAdapter(sql2,con2);
      con2.Open();
      sda2.Fill(ds2);
      if(ds2.Tables[0].Rows.Count==0)
      {
       return "";
      }
      else
      {
       for(int k=0;k<ds2.Tables[0].Rows.Count;k++)
       {

        sheet.Cells[6+k,1]=k+1;
        sheet.Cells[6+k,2]=ds2.Tables[0].Rows[k]["Material"].ToString();
        sheet.Cells[6+k,3]=ds2.Tables[0].Rows[k]["TRQty"].ToString();
        sheet.Cells[6+k,4]=ds2.Tables[0].Rows[k]["PO_Loc1"].ToString();
        sheet.Cells[6+k,5]=ds2.Tables[0].Rows[k]["PO_QtyOh1"].ToString();
        sheet.Cells[6+k,6]=ds2.Tables[0].Rows[k]["PO_Lot1"].ToString();
        sheet.Cells[6+k,8]=ds2.Tables[0].Rows[k]["PO_Loc2"].ToString();
        sheet.Cells[6+k,9]=ds2.Tables[0].Rows[k]["PO_QtyOh2"].ToString();
        sheet.Cells[6+k,10]=ds2.Tables[0].Rows[k]["PO_Lot2"].ToString();
        sheet.Cells[6+k,12]=ds2.Tables[0].Rows[k]["PO_Loc3"].ToString();
        sheet.Cells[6+k,13]=ds2.Tables[0].Rows[k]["PO_QtyOh3"].ToString();
        sheet.Cells[6+k,14]=ds2.Tables[0].Rows[k]["PO_Lot3"].ToString();

       }
      }
      con2.Close();
      con2.Dispose();
      sda2.Dispose();
      ds2.Dispose();
     }
    }


   try
   {
    File.Delete(path+orderno+".xls");
   }
   catch(Exception)
   {

   }

    workbook.SaveAs(path+orderno+".xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
    workbook.Close(null,null,null);
    app.Workbooks.Close();
    app.Application.Quit();
    app.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
    worksheet=null;
    workbook=null;
    app=null;
    con1.Close();
    con1.Dispose();
    ds1.Clear();
    ds1.Dispose();
    sda1.Dispose();

    con.Close();
    con.Dispose();
    ds.Clear();
    ds.Dispose();
    sda.Dispose();

    System.Web.HttpContext.Current.Response.Write("<script>window.open('file/"+orderno+".xls"+"');</script>");
    return "export sucessfully";
   }
   catch(Exception)
   {
    return "Error!";
   }