**.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!";
}