private Excel.Application CreateNewExcel(string url)
{
object missing = System.Reflection.Missing.Value;
Excel.Application myExcel = new Excel.Application();
myExcel = new Excel.ApplicationClass();
Excel._Workbook xBk; //工作薄
Excel._Worksheet xSt; //工作Sheet
xBk = myExcel.Workbooks.Add(true);
DataSet dsTop = this.LinkDB(this.comboBoxDB.Text," MF_BOM A LEFT JOIN PRDT B ON A.PRD_NO=B.PRD_NO "
+ " WHERE A.PRD_NO='" + this.treeView1.TopNode.Text + "'"," A.NAME,A.PRD_NO,ISNULL(A.PF_NO,0) AS PF_NO,B.SPC ");
DataSet dsTop2 = this.LinkDB(this.comboBoxDB.Text, " MF_BOM A LEFT JOIN PRDT B ON A.PRD_NO=B.PRD_NO "
+ " WHERE A.PRD_NO='" + this.treeView1.TopNode.Text + "'", " ISNULL(A.PF_NO,0) AS PF_NO ");
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xSt.Name = "Products_Relation";
//以下几句为本文重点,第一句为合并第一行中从1到10的单元格;接下来几句为怎样设置标题栏的格式,当然还可以定义颜色
//等其他操作,类似
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 10]).MergeCells = true;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, 1]).Font.Bold = true;
myExcel.Cells[1, 1] = "货品BOM关系";
myExcel.Cells[2, 1] = "父编号";
myExcel.Cells[2, 2] = "父版本号";
myExcel.Cells[2, 3] = "子编号";
myExcel.Cells[2, 4] = "子版本号";
myExcel.Cells[2, 5] = "用量";
myExcel.Cells[2, 6] = "损耗率";
myExcel.Cells[2, 7] = "关系";
myExcel.Cells[2, 8] = "基数";
myExcel.Cells[2, 9] = "材质级别";
myExcel.Cells[2, 10] = "备注";
ArrayList ALL = new ArrayList();
ArrayList all = GetAllNodes(this.treeView1.TopNode, ALL);
int j = 2;
foreach (TreeNode tn in all)
{
if (tn == this.treeView1.TopNode) continue;
DataSet ds = FindFatherVerDS(tn.Parent.Text);
if(tn.Parent!=this.treeView1.TopNode)
{
myExcel.Cells[++j, 1] = tn.Parent.Text;
myExcel.Cells[j, 2] = ds.Tables[0].Rows[0]["PF_NO"].ToString();
}
else
{
myExcel.Cells[++j, 1] = tn.Parent.Text;
myExcel.Cells[j, 2] = dsTop2.Tables[0].Rows[0]["PF_NO"].ToString();
}
DataSet ds2 = FindFatherVerDS(tn.Text);
string BOMNO = tn.Parent.Text + "->";
DataSet dsQTY = this.LinkDB(this.comboBoxDB.Text, " TF_BOM where PRD_NO='"+ tn.Text +"' AND BOM_NO='"+ BOMNO +"'", " QTY ");
myExcel.Cells[j, 3] = tn.Text;
myExcel.Cells[j, 4] = ds2.Tables[0].Rows[0]["PF_NO"].ToString();
myExcel.Cells[j, 5] = dsQTY.Tables[0].Rows[0]["QTY"].ToString();
}
myExcel.Sheets.Add(missing, missing, 1, Excel.XlSheetType.xlWorksheet);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xSt.Name = "Products_List";
myExcel.Cells[1, 1] = "类型";
myExcel.Cells[1, 2] = "名称";
myExcel.Cells[1, 3] = "编号";
myExcel.Cells[1, 4] = "版本号";
myExcel.Cells[1, 5] = "图号";
myExcel.Cells[1, 6] = "图幅";
myExcel.Cells[1, 7] = "张数";
myExcel.Cells[1, 8] = "规格";
myExcel.Cells[1, 9] = "材质";
myExcel.Cells[1, 10] = "颜色";
myExcel.Cells[1, 11] = "表面处理";
myExcel.Cells[1, 12] = "加工设备";
myExcel.Cells[1, 13] = "重量";
myExcel.Cells[1, 14] = "成本";
myExcel.Cells[1, 15] = "生效日期";
myExcel.Cells[1, 16] = "状态";
myExcel.Cells[1, 17] = "报废";
myExcel.Cells[1, 18] = "版本说明";
myExcel.Cells[1, 19] = "备注";
myExcel.Cells[2,1]=(int)this.treeView1.TopNode.Tag;
myExcel.Cells[2,2] = dsTop.Tables[0].Rows[0]["NAME"].ToString();
myExcel.Cells[2,3] = dsTop.Tables[0].Rows[0]["PRD_NO"].ToString();
myExcel.Cells[2,4] = dsTop.Tables[0].Rows[0]["PF_NO"].ToString();
myExcel.Cells[2,8] = dsTop.Tables[0].Rows[0]["SPC"].ToString();
ArrayList AL = new ArrayList();
ArrayList al = GetAllNodes(this.treeView1.TopNode,AL);
int i = 2;
foreach (TreeNode tn in al)
{
if (tn == this.treeView1.TopNode) continue;
DataSet ds = ResultDS(tn.Text);
myExcel.Cells[++i, 1] = (int)tn.Tag;
myExcel.Cells[i, 2] = ds.Tables[0].Rows[0]["NAME"].ToString();
myExcel.Cells[i, 3] = ds.Tables[0].Rows[0]["PRD_NO"].ToString();
myExcel.Cells[i, 4] = ds.Tables[0].Rows[0]["PF_NO"].ToString();
myExcel.Cells[i, 8] = ds.Tables[0].Rows[0]["SPC"].ToString();
}
myExcel.Visible = true;
xBk.SaveAs(url, missing, missing,
missing, false, false, Excel.XlSaveAsAccessMode.xlShared,
missing, missing, missing, missing, true);
myExcel.Quit();
return myExcel;
}