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

private void Excel_Click(object sender, System.EventArgs e)
  { 
   
   if(this.Customers_Name_List.SelectedValue!=""&&this.VersionName.SelectedValue!="")
   {
    
    Object refmissing = System.Reflection.Missing.Value;
    Excel._Application application=new Excel.ApplicationClass();
    application.Visible=false;
    
    Excel.Workbooks workbooks=application.Workbooks;
    workbooks._Open(Server.MapPath("Excel//Out.xls"), refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing, refmissing);
    application.DisplayAlerts=false;
    common com=new common();
    string names="";
    names=names+"select distinct Category_Name  from Product";
    names=names+" inner join Categroy on Categroy.CategoryId=Product.CategroyID";
    names=names+" inner join Quotation on Product.ProductID=Quotation.ProductID";
    names=names+" inner join Customer on Customer.CustomerID=Quotation.CustomerID";
    names=names+" left outer join Wattage on Wattage.WattageID=Product.WattageID";
    names=names+" left outer  join MaxWattage on MaxWattage.MaxWattageID=Product.MaxWattageID";
    names=names+" left outer  join Brand on Brand.BrandID=Product.BrandID";
    names=names+" left outer  join Voltage on Voltage.VoltageID=Product.VoltageID";
    names=names+" left outer  join Product_ESL on Product_ESL.Product_ESLID=Product.Product_ESLID";
    names=names+" left outer  join Plug on Plug.PlugID=Product.PlugID";
    names=names+" left outer  join Certificate on Certificate.CertificateID=Product.CertificateID";
    names=names+" left outer  join Standard on Standard.StandardID=Product.StandardID";
    names=names+" left outer  join LampBase on LampBase.LampBaseID=Product.LampBaseID";
    names=names+" left outer  join LifeTime on LifeTime.LifeTimeID=Product.LifeTimeID";
    names=names+" where Customer.CustomerID="+Convert.ToInt32(this.Customers_Name_List.SelectedValue)+" and Quotation.Version='"+this.VersionName.SelectedValue+"'";
    DataSet dsname=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(int i=0;i<application.Worksheets.Count;i++)
    {
    
     Excel.Worksheet worksheet=(Excel.Worksheet)application.Worksheets.get_Item(i+1);
     
     if(i>dsname.Tables[0].Rows.Count-1)
     {
     
      worksheet.Delete();

     }
     else
     {
      worksheet.Activate();
     }
     string select="";
     if(dsname!=null)
     {
      if(i<dsname.Tables[0].Rows.Count)
      {
      
       worksheet.Name=dsname.Tables[0].Rows[i][0].ToString();
       //-------------------------
       select="select distinct Category_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";
       string  itemname=common.single_datafield(this.Customers_Name_List.SelectedValue);
       select=select+itemname;
       select=select+" from Product";
       select=select+" inner join Categroy on Categroy.CategoryId=Product.CategroyID";
       select=select+" inner join Quotation on Product.ProductID=Quotation.ProductID";
       select=select+" inner join Customer on Customer.CustomerID=Quotation.CustomerID";
       select=select+" left outer join Wattage on Wattage.WattageID=Product.WattageID";
       select=select+" left outer  join MaxWattage on MaxWattage.MaxWattageID=Product.MaxWattageID";
       select=select+" left outer  join Brand on Brand.BrandID=Product.BrandID";
       select=select+" left outer  join Voltage on Voltage.VoltageID=Product.VoltageID";
       select=select+" left outer  join Product_ESL on Product_ESL.Product_ESLID=Product.Product_ESLID";
       select=select+" left outer  join Plug on Plug.PlugID=Product.PlugID";
       select=select+" left outer  join Certificate on Certificate.CertificateID=Product.CertificateID";
       select=select+" left outer  join Standard on Standard.StandardID=Product.StandardID";
       select=select+" left outer  join LampBase on LampBase.LampBaseID=Product.LampBaseID";
       select=select+" left outer  join LifeTime on LifeTime.LifeTimeID=Product.LifeTimeID";
       select=select+" where Customer.CustomerID="+Convert.ToInt32(this.Customers_Name_List.SelectedValue)+" and Quotation.Version='"+this.VersionName.SelectedValue+"'";
       select=select+" and Category_Name='"+dsname.Tables[0].Rows[i][0].ToString()+"'";
       System.Text.StringBuilder sb=new System.Text.StringBuilder();
       //Response.Write(select);
       if(i<1)
       {
        this.itemname();
       }
       DataSet ds=com.GetRecordset(select);
       int rowIndex=2; 
       int colIndex=1;
       int col=0;
       foreach(DataGridColumn col1 in this.InfoDataGrid.Columns) 
       { 
        col++; 
        application.Cells[1,col] = col1.HeaderText; 
        application.get_Range(application.Cells[1,col],application.Cells[1,col]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 
        
       } 
       foreach(DataRow datarow in ds.Tables[0].Rows)
       {
        foreach(DataColumn datacolumn in ds.Tables[0].Columns)
        {
         if(colIndex==13)
         {
          //application.Cells[rowIndex,colIndex] ="ok";
          Excel.Pictures pictures=(Excel.Pictures)worksheet.Pictures(Type.Missing);
          Excel.Picture picture=pictures.Insert(@Server.MapPath(datarow[datacolumn].ToString()),Type.Missing);
          Excel.Range range=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;
    string path=Server.MapPath("Excel//Out1.xls");
    System.IO.FileInfo file = new System.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(); 
   }
  }

 

转载于:https://www.cnblogs.com/leonardleonard/archive/2007/03/21/1928197.html