把图片插入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();     }   }

本文由来源 21aspnet，由 javajgs_com 整理编辑，其版权均为 21aspnet 所有，文章内容系作者个人观点，不代表 Java架构师必看 对观点赞同或支持。如需转载，请注明文章来源。