excel枢纽分析.doc

制作excel樞紐分析表模板步驟1.設置巨集安全性 .2.使用巨集抓取向導生成樞紐分析表代碼 根據具體情況制作樞紐分析表樞紐分析表 製作完後 停止巨集錄製打開巨集編輯代碼修改數據來源 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Template!R1C1:R1982C24).CreatePivotTable TableDestination:= _ 20091103115540.xlsSheet1!R3C1, TableName:=樞紐分析表1, xlPivotTableVersion10 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Worksheets(Template).Range(A1).CurrentRegion.Address).CreatePivotTable TableDestination:= _ , TableName:=樞紐分析表1, DefaultVersion:= _Template為excel 數據源表名稱找一个空白的表寫入當表被打開時執行事件 程式:Private Sub Worksheet_Activate() Application.DisplayAlerts = FalseThisWorkbook.Worksheets(接單分析表).DeleteThisWorkbook.Worksheets(Sheet1).Delete Call Macro1 調用巨集Macro1 With ThisWorkbook.VBProject.VBComponents .Remove .Parent.VBComponents(Module1) 刪除巨集ActiveWorkbook.Save 保存End With End Subusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.OleDb;using System.IO;namespace WebPortal public partial class business_OrderDetail : System.Web.UI.Page private DateTime beforeTime; private DateTime afterTime; Excel.Application oExcel; Excel.Workbooks oBooks; Excel.Workbook oBook; Excel.Worksheet oSheets; public static DataSet ds = new DataSet(); protected void Page_Load(object sender, EventArgs e) if(!IsPostBack) this.txt_begintime.Text = String.Format(0:yyyy-MM-dd, System.DateTime.Today.Date); this.txt_endtime.Text = String.Format(0:yyyy-MM-dd, System.DateTime.Today.Date); bindfactory(); bindsalse(); bindcustomer(); protected void btn_quty_Click(object sender, EventArgs e) bindgridview(); public void bindgridview() ds.Clear(); string dbconnpath = Server.MapPath(./dbconnstring.xml); Orderbasic order = new Orderbasic(); order.Facttype = Convert.ToInt32(drp_selectfactory.SelectedValue.Trim(); order.Edate = txt_endtime.Text.Trim(); order.Bdate = txt_begintime.Text.Trim(); order.Customerid = drp_customer.SelectedValue.Trim(); order.SubCustid = drp_subcustomer.SelectedValue.Trim(); order.SalesId = drp_saleman.SelectedValue.Trim(); ds = order.GetorderDetail(order, dbconnpath, newerp); if (ds.Tables0.Rows.Count= 0) this.lbmessage.Text = 您所選擇的單位沒有任何資料!; this.lbmessage.Visible = true; else this.lbmessage.Visible = false; public void bindcustomer() drp_customer.Items.Clear(); string dbconnpath = Server.MapPath(./dbconnstring.xml); DataSet dscuid= new DataSet(); sqlserveropt.sqlhelp sqlhelp = new sqlserveropt.sqlhelp(); string sql = select customerid,replace(customerid, ,)+_+shortname from customer(nolock); dscuid = sqlhelp.execsqltext(sql, newerp, dbconnpath); drp_customer.DataSource = dscuid.Tables0.DefaultView; drp_customer.DataValueField = dscuid.Tables0.Columns0.ToString(); drp_customer.DataTextField = dscuid.Tables0.Columns1.ToString(); drp_customer.DataBind(); drp_customer.Items.Insert(0, new ListItem(全部, ); public void bindsubcustomer() drp_subcustomer.Items.Clear(); string dbconnpath = Server.MapPath(./dbconnstring.xml); DataSet dssubcuid = new DataSet(); sqlserveropt.sqlhelp sqlhelp = new sqlserveropt.sqlhelp(); string sql = select subcustid,replace(subcustid, ,)+_+subShortName from cust_subcust(nolock) where customerid=+drp_customer.SelectedValue.Trim()+; dssubcuid = sqlhelp.execsqltext(sql, newerp, dbconnpath); drp_subcustomer.DataSource = dssubcuid.Tables0.DefaultView; drp_subcustomer.DataValueField = dssubcuid.Tables0.Columns0.ToString(); drp_subcustomer.DataTextField = dssubcuid.Tables0.Columns1.ToString(); drp_subcustomer.DataBind(); drp_subcustomer.Items.Insert(0, new ListItem(全部, ); public void bindsalse() drp_saleman.Items.Clear(); string dbconnpath = Server.MapPath(./dbconnstring.xml); DataSet dssaleman = new DataSet(); sqlserveropt.sqlhelp sqlhelp = new sqlserveropt.sqlhelp(); string sql = select empid,replace(empid, , )+_+empname from empbas(nolock) where empid in (select distinct salesid from orderbasic(nolock) and empstatus=0; dssaleman = sqlhelp.execsqltext(sql, newerp, dbconnpath); drp_saleman.DataSource = dssaleman.Tables0.DefaultView; drp_saleman.DataValueField = dssaleman.Tables0.Columns0.ToString(); drp_saleman.DataTextField = dssaleman.Tables0.Columns1.ToString(); drp_saleman.DataBind(); drp_saleman.Items.Insert(0, new ListItem(全部, ); public void bindfactory() drp_selectfactory.Items.Clear(); string dbconnpath = Server.MapPath(./dbconnstring.xml); DataSet dscust = new DataSet(); sqlserveropt.sqlhelp sqlhelp = new sqlserveropt.sqlhelp(); string sql = select lineid,linename from classline(nolock); dscust = sqlhelp.execsqltext(sql, newerp, dbconnpath); drp_selectfactory.DataSource = dscust.Tables0.DefaultView; drp_selectfactory.DataValueField = dscust.Tables0.Columns0.ToString(); drp_selectfactory.DataTextField = dscust.Tables0.Columns1.ToString(); drp_selectfactory.DataBind(); drp_selectfactory.Items.Insert(0, new ListItem(全部, 0); public void ss(string filename) string strExcelConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + filename + ;Extended Properties=EXCEL 8.0; OleDbConnection conn = new OleDbConnection(strExcelConn); conn.Open(); / 廠別=factTypename,業務員=Salename,客戶代碼=CustomerId, 客戶名稱=Name,子客戶名稱=SubShortname,子客戶代碼=SubCustID,EndUser=EndCustomer, /訂單類別=POType,製程種類= SalProdClass, /層別=Numoflayer, 疊構= salStructCtrl,下單日期=PODate,訂單數量=Quantity, /客戶交期=DelDate,出貨年月=DelDate , 廠內交期=ExpStkDate, 可投料日期=PutMtlDate,實際投料日期=FactPdldate,接單面積=Area,接單金額=TotalMoney, / 產品形態=SalProdType, 訂單=Ponum, 訂單序號=SerialNum,全流程委外商=ShortName string createsql = CREATE TABLE Template(廠別 char(24), 業務員 char(20),客戶代碼 char(16),客戶名稱 char(150),子客戶名稱 char(150),子客戶代碼 char(6),EndUser char(20),; createsql += 訂單類別 char(8),製程種類 char(10),層別 char(3),疊構 char(10),下單日期 char(10),訂單數量 float,; createsql += 客戶交期 char(10),出貨年月 char(8),廠內交期 char(10),可投料日期 char(10),實際投料日期 char(10),接單面積 float,接單金額 float,; createsql += 產品形態 char(100),訂單 char(15),訂單序號 char(10),全流程委外商 char(100) ); OleDbCommand comcreatable = new OleDbCommand(createsql, conn); comcreatable.ExecuteNonQuery(); for (int i = 0; i ds.Tables0.Rows.Count;i+) string insertsql = insert into Template(廠別,業務員,客戶代碼,客戶名稱,子客戶名稱,子客戶代碼,EndUser,訂單類別,製程種類,層別,; insertsql += 疊構,下單日期,訂單數量,客戶交期,出貨年月,廠內交期,可投料日期,實際投料日期,接單面積,接單金額,; insertsql += 產品形態,訂單,訂單序號,全流程委外商) values(; insertsql += +ds.Tables0.Rowsi0+,+ds.Tables0.Rowsi1+,; insertsql += +ds.Tables0.Rowsi2+,; insertsql += +ds.Tables0.Rowsi3+,+ds.Tables0.Rowsi4+,; insertsql += +ds.Tables0.Rowsi5+,+ds.Tables0.Rowsi6+,; insertsql += +ds.Tables0.Rowsi7+,; insertsql += +ds.Tables0.Rowsi8+,; insertsql += +ds.Tables0.Rowsi9+,+ds.Tables0.Rowsi10+,; insertsql += +ds.Tables0.Rowsi11+,+ds.Tables0.Rowsi12+,; insertsql += +ds.Tables0.Rowsi13+,; insertsql += +ds.Tables0.Rowsi14+,+ds.Tables0.Rowsi15+,; insertsql += +ds.Tables0.Rowsi16+,; insertsql += +ds.Tables0.Rowsi17+,+ds.Tables0.Rowsi18+,; insertsql += +ds.Tables0.Rowsi19+,; insertsql += +ds.Tables0.Rowsi20+,+ds.Tables0.Rowsi21+,; insertsql += +ds.Tables0.Rowsi22+,; insertsql += +ds.Tables0.Rowsi23+); OleDbCommand com = new OleDbCommand(insertsql, conn); com.ExecuteNonQuery(); conn.Close(); conn.Dispose(); public void copy() /Random rad = new Random(); string id = ; /while (id.Length 8) / / id = id + rad.Next(10).ToString(); / /id = DateTime.Now.ToString().Replace(/, ).Replace(:, ).Replace( , ) + .xls; id = System.DateTime.Now.ToString(yyyyMMddHHmmss) + .xls; string filename = Server.MapPath(Temp) + id; string path = Server.MapPath(Template.xls); File.Copy(path, filename); if (Request.CookiesFilename = null) HttpCookie cookie = new HttpCookie(Filename); cookie.Expires = DateTime.Now.AddDays(1); cookie.Values.Add(name, id); Response.AppendCookie(cookie); else HttpCookie acookie = Request.CookiesFilename; acookie.Values.Remove(name); acookie.Expires = DateTime.Now.AddDays(1); acookie.Values.Add(name, id); public void del(string path, string kzm) /del(,*) int intx; string aa; string str; str = System.IO.Directory.GetFiles(path, kzm); for (intx = 0; intx beforeTime & startTime afterTime) myProcess.Kill(); download(savepath); /釋放對象 public void ReleaseObj() if (oExcel != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); if (oBooks != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); if (oBook != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); if (oSheets != null) System.Run