把图片插入excel表格并按分类生成sheets

privatevoidExcel_Click(objectsender,System.EventArgse)
{

if(this.Customers_Name_List.SelectedValue!=""&&this.VersionName.SelectedValue!="")
{

Objectrefmissing=System.Reflection.Missing.Value;
Excel._Applicationapplication=newExcel.ApplicationClass();
application.Visible=false;

Excel.Workbooksworkbooks=application.Workbooks;
workbooks._Open(Server.MapPath("Excel//Out.xls"),refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing);
application.DisplayAlerts=false;
commoncom=newcommon();
stringnames="";
names=names+"selectdistinctCategory_NamefromProduct";
names=names+"innerjoinCategroyonCategroy.CategoryId=Product.CategroyID";
names=names+"innerjoinQuotationonProduct.ProductID=Quotation.ProductID";
names=names+"innerjoinCustomeronCustomer.CustomerID=Quotation.CustomerID";
names=names+"leftouterjoinWattageonWattage.WattageID=Product.WattageID";
names=names+"leftouterjoinMaxWattageonMaxWattage.MaxWattageID=Product.MaxWattageID";
names=names+"leftouterjoinBrandonBrand.BrandID=Product.BrandID";
names=names+"leftouterjoinVoltageonVoltage.VoltageID=Product.VoltageID";
names=names+"leftouterjoinProduct_ESLonProduct_ESL.Product_ESLID=Product.Product_ESLID";
names=names+"leftouterjoinPlugonPlug.PlugID=Product.PlugID";
names=names+"leftouterjoinCertificateonCertificate.CertificateID=Product.CertificateID";
names=names+"leftouterjoinStandardonStandard.StandardID=Product.StandardID";
names=names+"leftouterjoinLampBaseonLampBase.LampBaseID=Product.LampBaseID";
names=names+"leftouterjoinLifeTimeonLifeTime.LifeTimeID=Product.LifeTimeID";
names=names+"whereCustomer.CustomerID="+Convert.ToInt32(this.Customers_Name_List.SelectedValue)+"andQuotation.Version='"+this.VersionName.SelectedValue+"'";
DataSetdsname=com.GetRecordset(names);
if(dsname.Tables[0].Rows.Count>3)
{
application.Worksheets.Add(Type.Missing,Type.Missing,dsname.Tables[0].Rows.Count-3,Type.Missing);
}

for(inti=0;i<application.Worksheets.Count;i++)
{

Excel.Worksheetworksheet=(Excel.Worksheet)application.Worksheets.get_Item(i+1);

if(i>dsname.Tables[0].Rows.Count-1)
{

worksheet.Delete();

}
else
{
worksheet.Activate();
}
stringselect="";
if(dsname!=null)
{
if(i<dsname.Tables[0].Rows.Count)
{

worksheet.Name=dsname.Tables[0].Rows[i][0].ToString();
//-------------------------
select="selectdistinctCategory_Name,Series_Name,Model_Name,Product_Name,Wattage,MaxWattage,Brand,Voltage,Description,Product_ESL,Plug,certificate,Product_Picture,Standard,LampBase,LifeTime,Quotation.MOQ,Quotation.Remark";
stringitemname=common.single_datafield(this.Customers_Name_List.SelectedValue);
select=select+itemname;
select=select+"fromProduct";
select=select+"innerjoinCategroyonCategroy.CategoryId=Product.CategroyID";
select=select+"innerjoinQuotationonProduct.ProductID=Quotation.ProductID";
select=select+"innerjoinCustomeronCustomer.CustomerID=Quotation.CustomerID";
select=select+"leftouterjoinWattageonWattage.WattageID=Product.WattageID";
select=select+"leftouterjoinMaxWattageonMaxWattage.MaxWattageID=Product.MaxWattageID";
select=select+"leftouterjoinBrandonBrand.BrandID=Product.BrandID";
select=select+"leftouterjoinVoltageonVoltage.VoltageID=Product.VoltageID";
select=select+"leftouterjoinProduct_ESLonProduct_ESL.Product_ESLID=Product.Product_ESLID";
select=select+"leftouterjoinPlugonPlug.PlugID=Product.PlugID";
select=select+"leftouterjoinCertificateonCertificate.CertificateID=Product.CertificateID";
select=select+"leftouterjoinStandardonStandard.StandardID=Product.StandardID";
select=select+"leftouterjoinLampBaseonLampBase.LampBaseID=Product.LampBaseID";
select=select+"leftouterjoinLifeTimeonLifeTime.LifeTimeID=Product.LifeTimeID";
select=select+"whereCustomer.CustomerID="+Convert.ToInt32(this.Customers_Name_List.SelectedValue)+"andQuotation.Version='"+this.VersionName.SelectedValue+"'";
select=select+"andCategory_Name='"+dsname.Tables[0].Rows[i][0].ToString()+"'";
System.Text.StringBuildersb=newSystem.Text.StringBuilder();
//Response.Write(select);
if(i<1)
{
this.itemname();
}
DataSetds=com.GetRecordset(select);
introwIndex=2;
intcolIndex=1;
intcol=0;
foreach(DataGridColumncol1inthis.InfoDataGrid.Columns)
{
col++;
application.Cells[1,col]=col1.HeaderText;
application.get_Range(application.Cells[1,col],application.Cells[1,col]).HorizontalAlignment=XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐

}
foreach(DataRowdatarowinds.Tables[0].Rows)
{
foreach(DataColumndatacolumninds.Tables[0].Columns)
{
if(colIndex==13)
{
//application.Cells[rowIndex,colIndex]="ok";
Excel.Picturespictures=(Excel.Pictures)worksheet.Pictures(Type.Missing);
Excel.Picturepicture=pictures.Insert(@Server.MapPath(datarow[datacolumn].ToString()),Type.Missing);
Excel.Rangerange=worksheet.get_Range("M"+rowIndex.ToString(),Type.Missing);
picture.Left=(double)range.Left;
picture.Top=(double)range.Top;
picture.Width=(double)range.Width;
picture.Height=(double)range.Height;

}
else
{
application.Cells[rowIndex,colIndex]=datarow[datacolumn].ToString();
}

colIndex=colIndex+1;
if(colIndex>this.InfoDataGrid.Columns.Count)
{
colIndex=1;
}
}
rowIndex=rowIndex+1;
}
//-----------------------
if(i<1)
{
//break;
}
}
}

System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet=null;
}

//Response.End();
workbooks.get_Item(1).SaveCopyAs(Server.MapPath("Excel//Out1.xls"));
workbooks.Close();

application.DisplayAlerts=true;
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
workbooks=null;
application=null;
stringpath=Server.MapPath("Excel//Out1.xls");
System.IO.FileInfofile=newSystem.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();
}
}