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();
}
}
