- ©«¤l
- 430
- ¥DÃD
- 65
- ºëµØ
- 0
- ¿n¤À
- 521
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-8-13
|
//Excel½g
mExcel.DisplayAlerts := False; //Disable ´£¥Ü°T®§
mExcel.Visible := True; //Åã¥Ü Excel µe±
mExcel.ActiveWindow.FreezePanes := True; //áµ²µ¡®æ(¤W¤@¦æ¤@©wn¥ý¿ï¦C©Î®æ)
mExcel.ActiveWindow.Zoom := 75; //Åã¥Ü¤ñ²v¬°75%
mExcel.ActiveWindow.Zoom := True; //¨Ì¾Ú¥Ø«e¿ï¾Ü½d³ò¦Û°Ê¨M©wµøµ¡¤j¤p
//Workbook½g
mWorkBook := mExcel.WorkBooks[1]; //±N«ü©wÅܼƳ]©w²Ä¤@Ó¬¡¶Ã¯(¼Æ¦r¥i¥H¥Î¦WºÙ¨ú¥N)
mWorkBook.Name :='¤º®e'; //ÅܧóWorkBook¦WºÙ
mExcel.WorkBooks.Add; //·s¼W¤@Ӫťլ¡¶Ã¯
mExcel.WorkBooks.Open(§¹¾ã¸ô®|); //¶}±ÒExcelÀÉ
mExcel.WorkBooks[mFile].Close; //Ãö³¬ExcelÀÉ
DeleteFile(mPath+mFile); //§R°£ExcelÀÉ
mWorkBook.SaveAs(mPath+mFile,-4143); //Àx¦sExcelÀÉ
//Sheet½g
mSheet := mExcel.WorkBooks[1].WorkSheets[1]; //±N«ü©wÅܼƳ]©w²Ä¤@Ó¤u§@ªí(¼Æ¦r¥i¥H¥Î¦WºÙ¨ú¥N)
mSheet.Name :='¤º®e'; //ÅܧóSheet¦WºÙ
mSheet.Copy[After := mWorkBook.Sheets[mWorkBook.Sheets.Count]]; //±NmSheet½Æ»s¨ìmWorkBook³Ì«á
mSheet.Move[After := mWorkBook.Sheets[mWorkBook.Sheets.Count]]; //±NmSheet·h²¾¨ìmWorkBook³Ì«á
mWorkbook.Sheets.Add[After:=mWorkbook.Sheets[mSheetCount-1]]; //·s¼W¤@Ӫťդu§@ªí
mWorkBook.Sheets[1].Delete; //§R°£«ü©wSheet
mWorkBook.Sheets[1].Activate; //±N«ü©wSheet³]¬°¨Ï¥Î¤¤
//§R°£¦h¾lSheet
if (mWorkBook.Sheets.Count > 1) then
begin
for i:=2 to mWorkBook.Sheets.Count do
mWorkBook.Sheets[2].Delete;
end;
//¿ï¨ú½g
mSheet.Cells.EntireColumn //©Ò¦³Äæ
mSheet.Cells.EntireRow //©Ò¦³¦C
mSheet.Cells //©Ò¦³Àx¦s®æ
mSheet.Columns[1] //²Ä¤@Äæ
mSheet.Rows[1] //²Ä¤@¦C
mSheet.Cells[r,c] //²Är¦C²ÄcÄæ
mSheet.Range[°_,¨´] //°Ï¶¡¿ï¾Ü(°_°W¥i¥H¬OÄæ¡B¦C¡B®æ)
//¶ñȽg
mSheet.Cells[1,1].Value:= '¤º®e'; //Äæ¦ì¶ñÈ
mSheet.Cells[1,1].Formula:= '¤½¦¡'; //Äæ¦ì¶ñ¤J¤½¦¡
mSheet.Cells[1,1].FormulaR1C1:= '¤½¦¡'; //Äæ¦ì¶ñ¤J¤½¦¡
mSheet.Cells[1,1].HasFormula //Àx¦s®æ¬O§_¦³¤½¦¡
//®æ¦¡½g
mSheet.Range[°_,¨´].Merge; //¦X¨ÖÀx¦s®æ
mSheet.Cells.EntireColumn.AutoFit; //³Ì¾AÄæ¼e
mSheet.Cells.EntireRow.AutoFit; //³Ì¾A¦C°ª
mSheet.Columns[1].ColumnWidth := 100; //³]©wÄæ¼e
mSheet.Rows[1].RowHeight := 100; //³]©w¦C°ª
mSheet.Rows[1].HorizontalAlignment := -4108; //¤ô¥¸m¤¤(¾a¥ª:-4131¡F¾a¥k:-4152)
mSheet.Rows[1].VerticalAlignment := -4108; //««ª½¸m¤¤(¾a¥ª:-4131¡F¾a¥k:-4152)
mSheet.Rows[1].WrapText := True; //¦Û°Ê´«¦C
mSheet.Columns[1].Hidden := True; //ÁôÂÃ
mSheet.Columns[1].NumberFormatLocal := '@'; //³]©wÄæ¦ì®æ¦¡[¤å¦r]
mSheet.Columns[1].NumberFormatLocal := '#,##0_ '; //³]©wÄæ¦ì®æ¦¡[¼ÆÈ(¾ã¼Æ¦ì ¤T¦ì¤@ºJ)]
mSheet.Columns[1].NumberFormatLocal := '#,##0_ ;[¬õ¦â]-#,##0 '; //³]©wÄæ¦ì®æ¦¡[¼ÆÈ(¾ã¼Æ¦ì ¤T¦ì¤@ºJ t¼Æ¬õ¦r)]
mSheet.Columns[1].NumberFormatLocal := '#,##0_);[¬õ¦â](#,##0)'; //³]©wÄæ¦ì®æ¦¡[¼ÆÈ(¾ã¼Æ¦ì ¤T¦ì¤@ºJ t¼Æ¬A¸¹¬õ¦r)]
mSheet.Columns[1].NumberFormatLocal := '0.00_ '; //³]©wÄæ¦ì®æ¦¡[¼ÆÈ(¤p¼Æ¨â¦ì)]
mSheet.Columns[1].NumberFormatLocal := '0.0_);[¬õ¦â](0.0)'; //³]©wÄæ¦ì®æ¦¡[¼ÆÈ(¤p¼Æ¤@¦ì t¼Æ¬õ¦r)]
mSheet.Columns[1].NumberFormatLocal := '0.00%'; //³]©wÄæ¦ì®æ¦¡[¦Ê¤À¤ñ(¤p¼Æ¨â¦ì)]
mSheet.Cells[1].Interior.ColorIndex := 38; //³]©w©³¦â¬°ª´ºÀ¦â
mSheet.Cells[1].Interior.ColorIndex := 6; //³]©w©³¦â¬°¶À¦â
mSheet.Cells[1].Interior.ColorIndex := 36; //³]©w©³¦â¬°²L¶À¦â
mSheet.Cells[1].Interior.ColorIndex := 35; //³]©w©³¦â¬°²Lºñ¦â
mSheet.Cells[1,1].Font.Size := 10; //³]©w¦rÅé¤j¤p
mSheet.Cells[1,1].Font.Bold := True; //³]©w²ÊÅé¦r
......
//®Ø½u
mSheet.Cells[1,1].Borders[n].LineStyle := 1;
mSheet.Cells[1,1].Borders[n].Weight := 2;
//n = 5.¥ª¤W¥k¤U±×½u 6.¥ª¤U¥k¤W±×½u 7.¥ªÃä½u 8.¤WÃä½u 9.¤UÃä½u 10.¥kÃä½u 11.««ª½½u 12.¤ô¥½u
//Borders¥i¨Ï¥Î°Ñ¼Æ¡G
// LineStyle = 1 ¹ê½u¡F-4115 µuµê½u¡F4 ªøµuµê½u¡F5 ªøµuµuµê½u¡F-4118 ²Óµê½u¡F-4119 Âù¹ê½u
// Weight = ¥Ñ²Ó¨ì²Ê¡G1 --> 2 --> -4138 --> 4
// ColorIndex = ÃC¦â
//³]©w®æ¦¡¤Æ±ø¥ó
mSheet.Cells[1,1].FormatConditions.Delete; //²M°£®æ¦¡¤Æ±ø¥ó
mSheet.Cells[1,1].FormatConditions.Add[Type:='1', Operator:='1', Formula1:='1', Formula2:='2']; //·s¼W®æ¦¡¤Æ±ø¥ó(³Ì¦h3Ó)
//°Ñ¼Æ»¡©ú
// °Ñ¼Æ ¤¤¤å»¡©ú »¡©ú
//======== ======== =======================================================================
//Type ¨Ó·½«¬ºA 1.Àx¦s®æªºÈ 2.¤½¦¡
//Operator ³W«h 1.¤¶©ó 2.¤£¤¶©ó 3.µ¥©ó 4.¤£µ¥©ó 5.¤j©ó 6.¤p©ó 7.¤j©ó©Îµ¥©ó 8.¤p©ó©Îµ¥©ó
//Formula1 ±ø¥ó°_
//Formula2 ±ø¥ó¨´
mSheet.Cells[1,1].FormatConditions(1).Interior.ColorIndex := 3; //³]©w±ø¥ó¤@¬°©³¦â¬õ¦â
//¥i³]©w¤§®æ¦¡¦³¡GFonts(¦r«¬)¡BBorders(¥~®Ø)¡BInterior(¹Ï¼Ë)
//¸ê®Æ½g
mSheet.Cells.EntireColumn.AutoFilter; //¦Û°Ê¿z¿ï
mExcel.Selection.Subtotal(1,-4157,VarArrayOf([4,5,6,7,8]),True,False,True); //°µ¤pp
//°Ñ¼Æ»¡©ú
// °Ñ¼Æ ¤¤¤å»¡©ú ¹w³]È
//================ ======================== ======
//GroupBy ¤À²Õ¤ppÄæ¦ì 1
//Function ¨Ï¥Î¨ç¼Æ -4157 ¥[Á`
//TotalList ·s¼W¤pp¦ì¸m
//Replace ¨ú¥N¥Ø«e¤pp True
//PageBreaks ¨C²Õ¸ê®Æ¤À¶ False
//SummaryBelowData ºKn¸ê®Æ¸m©ó¤pp¸ê®Æ¤U¤è True
//¥i¨Ï¥Î¨ç¼Æ¡G-4157 ¥[Á`¡F-4106 ¥§¡È¡F-4112 ¶µ¥ØӼơF-4113 ¼Æ¦r¶µ¥Ø¼Æ¡F-4136 ³Ì¤jÈ¡F-4139 ³Ì¤pÈ¡F
mSheet.Outline.ShowLevels(2); //§â¤pp¼h¯Å³]2Åã¥Ü
//¦C¦L½g
mSheet.PageSetup.PrintTitleRows := '$1:$1'; //¦C¦L¼ÐÃD¦C
mSheet.PageSetup.CenterHeader := 'ªíÀY'; //¤¤¶º
mSheet.PageSetup.LeftHeader := '¶¦¸: &P / &N'; //¥ª¶º
mSheet.PageSetup.RightHeader := ''; //¥k¶º
mSheet.PageSetup.CenterFooter := '&¡@&P / &N'; //¤¤¶§À
mSheet.PageSetup.LeftFooter := '¶¦¸: &P / &N'; //¥ª¶§À
mSheet.PageSetup.RightFooter := ''; //¥k¶§À
mSheet.PageSetup.PrintArea := '$B$1:$N$300'; //³]©w¦C¦L½d³ò
mSheet.PageSetup.Orientation := 2; //1.ª½¦L 2.¾î¦L
mSheet.PageSetup.Zoom := 65; //¦C¦L®É¤p¦¨65%
mSheet.PageSetup.Zoom := True; //¨Ï¥Î¶¦¸ÁY©ñ¥\¯à
mSheet.PageSetup.FitToPagesWide := 1; //ÁY©ñ¦¨¤@¶¼e(»Ý°t¦XZoom = True)
mSheet.PageSetup.FitToPagesTall := 1; //ÁY©ñ¦¨¤@¶°ª(»Ý°t¦XZoom = True)
mSheet.PageSetup.PaperSize := 8; //³]©w¯È±i¤j¤p 8:A3¡B9:A4
mSheet.PageSetup.TopMargin := 1/0.035; //³»Ãä¶Z1cm
mSheet.PageSetup.BottomMargin := 1/0.035; //©³Ãä¶Z1cm
mSheet.PageSetup.LeftMargin := 1/0.035; //¥ªÃä¶Z2cm
mSheet.PageSetup.RightMargin := 1/0.035; //¥kÃä¶Z2cm
mSheet.PageSetup.HeaderMargin := 1/0.035; //¶º1cm
mSheet.PageSetup.FooterMargin := 1/0.035; //¶§À1cm
mSheet.PageSetup.CenterHorizontally := True; //¶±¤ô¥©~¤¤
mSheet.PageSetup.CenterVertically := False; //¶±««ª½©~¤¤
From ihao½×¾Â http://www.ihao.org/dz5/,http://www.ihao.org/dz5/viewthread.php?tid=61467 |
|