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()+"'";
