本文共 6364 字，大约阅读时间需要 21 分钟。

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://linuxstyle.blog.csdn.net/article/details/1536881 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！