{"id":222,"date":"2009-02-11T21:26:00","date_gmt":"2009-02-11T01:26:00","guid":{"rendered":""},"modified":"2013-11-18T21:10:01","modified_gmt":"2013-11-18T13:10:01","slug":"delphi%e4%b8%ad%e7%9a%84excel%e7%bc%96%e7%a8%8b","status":"publish","type":"post","link":"https:\/\/kyle.ai\/blog\/222.html","title":{"rendered":"Delphi\u4e2d\u7684EXCEL\u7f16\u7a0b"},"content":{"rendered":"

\u9996\u5148\u521b\u5efa Excel \u5bf9\u8c61\uff0c\u4f7f\u7528ComObj:
\nvar ExcelID: Variant;
\nExcelID := CreateOleObject( ‘Excel.Application’ );
\n1) \u663e\u793a\u5f53\u524d\u7a97\u53e3\uff1a
\nExcelID.Visible := True;
\n2) \u66f4\u6539 Excel \u6807\u9898\u680f\uff1a
\nExcelID.Caption := ‘\u5e94\u7528\u7a0b\u5e8f\u8c03\u7528 Microsoft Excel’;
\n3) \u6dfb\u52a0\u65b0\u5de5\u4f5c\u7c3f\uff1a
\nExcelID.WorkBooks.Add;
\n4) \u6253\u5f00\u5df2\u5b58\u5728\u7684\u5de5\u4f5c\u7c3f\uff1a
\nExcelID.WorkBooks.Open( ‘C:\\Excel\\Demo.xls’ );
\n5) \u8bbe\u7f6e\u7b2c2\u4e2a\u5de5\u4f5c\u8868\u4e3a\u6d3b\u52a8\u5de5\u4f5c\u8868\uff1a
\nExcelID.WorkSheets[2].Activate;
\n\u6216
\nExcelID.WorksSheets[ ‘Sheet2’ ].Activate;
\n6) \u7ed9\u5355\u5143\u683c\u8d4b\u503c\uff1a
\nExcelID.Cells[1,4].Value := ‘\u7b2c\u4e00\u884c\u7b2c\u56db\u5217’;
\n7) \u8bbe\u7f6e\u6307\u5b9a\u5217\u7684\u5bbd\u5ea6\uff08\u5355\u4f4d\uff1a\u5b57\u7b26\u4e2a\u6570\uff09\uff0c\u4ee5\u7b2c\u4e00\u5217\u4e3a\u4f8b\uff1a
\nExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;
\n8) \u8bbe\u7f6e\u6307\u5b9a\u884c\u7684\u9ad8\u5ea6\uff08\u5355\u4f4d\uff1a\u78c5\uff09\uff081\u78c5\uff1d0.035\u5398\u7c73\uff09\uff0c\u4ee5\u7b2c\u4e8c\u884c\u4e3a\u4f8b\uff1a
\nExcelID.ActiveSheet.Rows[2].RowHeight := 1\/0.035; \/\/ 1\u5398\u7c73
\n9) \u5728\u7b2c8\u884c\u4e4b\u524d\u63d2\u5165\u5206\u9875\u7b26\uff1a
\nExcelID.WorkSheets[1].Rows[8].PageBreak := 1;
\n10) \u5728\u7b2c8\u5217\u4e4b\u524d\u5220\u9664\u5206\u9875\u7b26\uff1a
\nExcelID.ActiveSheet.Columns[4].PageBreak := 0;
\n11) \u6307\u5b9a\u8fb9\u6846\u7ebf\u5bbd\u5ea6\uff1a
\nExcelID.ActiveSheet.Range[ ‘B3:D4’ ].Borders[2].Weight := 3;
\n1-\u5de6 2-\u53f3 3-\u9876 4-\u5e95 5-\u659c( \\ ) 6-\u659c( \/ )
\n12) \u6e05\u9664\u7b2c\u4e00\u884c\u7b2c\u56db\u5217\u5355\u5143\u683c\u516c\u5f0f\uff1a
\nExcelID.ActiveSheet.Cells[1,4].ClearContents;
\n13) \u8bbe\u7f6e\u7b2c\u4e00\u884c\u5b57\u4f53\u5c5e\u6027\uff1a
\nExcelID.ActiveSheet.Rows[1].Font.Name := ‘\u96b6\u4e66’;
\nExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;
\nExcelID.ActiveSheet.Rows[1].Font.Bold := True;
\nExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;
\n14) \u8fdb\u884c\u9875\u9762\u8bbe\u7f6e\uff1a
\na.\u9875\u7709\uff1a
\nExcelID.ActiveSheet.PageSetup.CenterHeader := ‘\u62a5\u8868\u6f14\u793a’;
\nb.\u9875\u811a\uff1a
\nExcelID.ActiveSheet.PageSetup.CenterFooter := ‘\u7b2c&P\u9875’;
\nc.\u9875\u7709\u5230\u9876\u7aef\u8fb9\u8ddd2cm\uff1a
\nExcelID.ActiveSheet.PageSetup.HeaderMargin := 2\/0.035;
\nd.\u9875\u811a\u5230\u5e95\u7aef\u8fb9\u8ddd3cm\uff1a
\nExcelID.ActiveSheet.PageSetup.HeaderMargin := 3\/0.035;
\ne.\u9876\u8fb9\u8ddd2cm\uff1a
\nExcelID.ActiveSheet.PageSetup.TopMargin := 2\/0.035;
\nf.\u5e95\u8fb9\u8ddd2cm\uff1a
\nExcelID.ActiveSheet.PageSetup.BottomMargin := 2\/0.035;
\ng.\u5de6\u8fb9\u8ddd2cm\uff1a
\nExcelID.ActiveSheet.PageSetup.LeftMargin := 2\/0.035;
\nh.\u53f3\u8fb9\u8ddd2cm\uff1a
\nExcelID.ActiveSheet.PageSetup.RightMargin := 2\/0.035;
\ni.\u9875\u9762\u6c34\u5e73\u5c45\u4e2d\uff1a
\nExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2\/0.035;
\nj.\u9875\u9762\u5782\u76f4\u5c45\u4e2d\uff1a
\nExcelID.ActiveSheet.PageSetup.CenterVertically := 2\/0.035;
\nk.\u6253\u5370\u5355\u5143\u683c\u7f51\u7ebf\uff1a
\nExcelID.ActiveSheet.PageSetup.PrintGridLines := True;
\n15) \u62f7\u8d1d\u64cd\u4f5c\uff1a
\na.\u62f7\u8d1d\u6574\u4e2a\u5de5\u4f5c\u8868\uff1a
\nExcelID.ActiveSheet.Used.Range.Copy;
\nb.\u62f7\u8d1d\u6307\u5b9a\u533a\u57df\uff1a
\nExcelID.ActiveSheet.Range[ ‘A1:E2’ ].Copy;
\nc.\u4eceA1\u4f4d\u7f6e\u5f00\u59cb\u7c98\u8d34\uff1a
\nExcelID.ActiveSheet.Range.[ ‘A1’ ].PasteSpecial;
\nd.\u4ece\u6587\u4ef6\u5c3e\u90e8\u5f00\u59cb\u7c98\u8d34\uff1a
\nExcelID.ActiveSheet.Range.PasteSpecial;
\n16) \u63d2\u5165\u4e00\u884c\u6216\u4e00\u5217\uff1a
\na. ExcelID.ActiveSheet.Rows[2].Insert;
\nb. ExcelID.ActiveSheet.Columns[1].Insert;
\n17) \u5220\u9664\u4e00\u884c\u6216\u4e00\u5217\uff1a
\na. ExcelID.ActiveSheet.Rows[2].Delete;
\nb. ExcelID.ActiveSheet.Columns[1].Delete;
\n18) \u6253\u5370\u9884\u89c8\u5de5\u4f5c\u8868\uff1a
\nExcelID.ActiveSheet.PrintPreview;
\n19) \u6253\u5370\u8f93\u51fa\u5de5\u4f5c\u8868\uff1a
\nExcelID.ActiveSheet.PrintOut;
\n20) \u5de5\u4f5c\u8868\u4fdd\u5b58\uff1a
\nif not ExcelID.ActiveWorkBook.Saved then
\nExcelID.ActiveSheet.PrintPreview;
\n21) \u5de5\u4f5c\u8868\u53e6\u5b58\u4e3a\uff1a
\nExcelID.SaveAs( ‘C:\\Excel\\Demo1.xls’ );
\n22) \u653e\u5f03\u5b58\u76d8\uff1a
\nExcelID.ActiveWorkBook.Saved := True;
\n23) \u5173\u95ed\u5de5\u4f5c\u7c3f\uff1a
\nExcelID.WorkBooks.Close;
\n24) \u9000\u51fa Excel\uff1a
\nExcelID.Quit;<\/p>\n

ADO\u6253\u5f00EXCEL<\/p>\n

\r\nadoz:tadoquery;\r\nadozc:tadoconnection;\r\n\r\nadoz:=tadoquery.Create(nil);\r\nadozc:=tadoconnection.Create(nil);\r\nadozc.loginprompt:=false; \/\/\u5bc6\u7801\u5bf9\u8bdd\u6846\r\nadozc.connected:=false;\r\nadozc.ConnectionString:=\u2019Provider=Microsoft.Jet.OLEDB.4.0;\u2019\r\n+\u2019Data Source=\u2019\r\n+exc \/\/(\u8def\u5f84)\r\n+\u2019;Extended Properties="excel 8.0;\u2019\r\n+\u2019hdr=yes;imex=1";\u2019\r\n+\u2019;Persist Security Info=False\u2019;\r\nadozc.Connected:=true;\r\nadoz.Connection :=adozc;\r\nadoz.SQL.Clear ;\r\nadoz.SQL.Add(\u2018select * from [Sheet1$]\u2018);\r\nadoz.Open;\r\n\r\n\u3001\u3001\u3001\u3001\u3001\u3001\r\n\r\nadoz.free;\r\nadozc.free;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u9996\u5148\u521b\u5efa Excel \u5bf9\u8c61\uff0c\u4f7f\u7528ComObj: var ExcelID: Variant; ExcelID : […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-222","post","type-post","status-publish","format-standard","hentry","category-code_related"],"_links":{"self":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/comments?post=222"}],"version-history":[{"count":1,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":4796,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/222\/revisions\/4796"}],"wp:attachment":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/media?parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/categories?post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/tags?post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}