Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!C1:C16").CreatePivotTable TableDestination:="", TableName:="樞紐分析表1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("樞紐分析表1").SmallGrid = False Charts.Add ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A3") ActiveChart.Location Where:=xlLocationAsNewSheet ActiveWorkbook.PublishObjects.Add(xlSourceChart, "D:\Page.htm", "Chart1", "", _ xlHtmlChart, "Book2_12184", "").Publish (True) ChDir "D:\" 小弟不才，接連爬了幾篇文章。發現只有DELPHI的範例。 研究過後發現DELPHI的對VBA的翻譯語法跟BCB有著大大的不同 唉！抱著一本VBA的語法字典卻苦不知把他翻成BCB語法，望大大們慷慨相授 －－－－－－－－－－－－－－－－－－－－－－－－ 我是新手，觀念還不是很清楚。 問了些惹人發笑的問題，請各位大聲的在螢幕後笑出來。沒關係的！ 免得害各位引起嚴重內傷！ －－－－－－－－－－－－－－－－－－－－－－－－

在EXCEL裡的計算式，你可以先在Excel裡開來看，在貼到程式裡。 但有特殊格式，請仔細看以下三段內容： ──────────────── 其中的計算式，你可以到Excel寫完了，再把他COPY到程式裡：    // 設定直的加總公式, -3 & -1 好像是從加總欄位往上數所得來的 Range=Worksheet.OlePropertyGet("Cells",2,6); Range.OlePropertySet("Value","123"); Range=Worksheet.OlePropertyGet("Cells",3,6); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",4,6); Range.OlePropertySet("Value","345"); Range=Worksheet.OlePropertyGet("Cells",5,6); Range.OlePropertySet("FormulaR1C1","=SUM(R[-3]C:R[-1]C)");    // 設定橫的加總公式, -3 & -1 好像是從加總欄位往左數所得來的 Range=Worksheet.OlePropertyGet("Cells",2,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",2,8); Range.OlePropertySet("Value","345"); Range=Worksheet.OlePropertyGet("Cells",2,9); Range.OlePropertySet("FormulaR1C1","=SUM(RC[-3]:RC[-1])");    // 設定直跟橫都有的加總公式, 先往上數,再往左邊數 Range=Worksheet.OlePropertyGet("Cells",3,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",4,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",5,8); Range.OlePropertySet("FormulaR1C1","=SUM(R[-3]C[-2]:R[-1]C[-1])");    ───────────── // 轉EXCEL Variant Excel,Workbooks,Workbook; Variant Worksheets,Worksheet,Range,Cell,FColumns,Item; int i,j;    // 新增一worksheet Excel=CreateOleObject("Excel.Application"); Excel.OlePropertySet("Visible",true); Workbooks=Excel.OlePropertyGet("Workbooks"); Workbook=Workbooks.OleFunction("Add"); Worksheets=Workbook.OlePropertyGet("Worksheets"); Worksheet=Worksheets.OlePropertyGet("Item",1);    // 版面設定 Range=Worksheet.OlePropertyGet("PageSetup"); // 頁首 , 頁尾 Range.OlePropertySet("LeftHeader",""); Range.OlePropertySet("CenterHeader",""); Range.OlePropertySet("RightHeader",""); Range.OlePropertySet("LeftFooter",""); Range.OlePropertySet("CenterFooter","第 &P 頁，共 &N 頁"); Range.OlePropertySet("RightFooter","");    // 設定邊界 , 單位為 point , 一 point = 1 / 72 inches  Range.OlePropertySet("LeftMargin",15); Range.OlePropertySet("RightMargin",15); Range.OlePropertySet("TopMargin",15); Range.OlePropertySet("BottomMargin",15); Range.OlePropertySet("HeaderMargin",15); Range.OlePropertySet("FooterMargin",15);    Range.OlePropertySet("PrintTitleRows","$2:$2"); // 列印標題列 Range.OlePropertySet("PrintTitleColumns","$A:$A"); // 列印標題欄 Range.OlePropertySet("PrintArea","$A$1:$I$15"); // 列印範圍 Range.OlePropertySet("PrintHeadings",false); // 欄名列號 Range.OlePropertySet("PrintGridlines",false); // 列印格線 Range.OlePropertySet("PrintComments",xlPrintNoComments); // 註解"無" Range.OlePropertySet("PrintQuality",300); // 列印品質 Range.OlePropertySet("CenterHorizontally",true); // 水平置中 Range.OlePropertySet("CenterVertically",false); // 垂直置中 Range.OlePropertySet("Orientation",xlLandscape); // 橫向列印 (xlPortrait 縱向列印) Range.OlePropertySet("Draft",false); // 草稿品質 Range.OlePropertySet("PaperSize",xlPaperA4); // 紙張大小 Range.OlePropertySet("FirstPageNumber",xlAutomatic); // 起始頁碼 Range.OlePropertySet("Order",xlDownThenOver); // 循欄列印 (xlOverThenDown 循列列印) Range.OlePropertySet("BlackAndWhite",False); // 儲存格單色列印 Range.OlePropertySet("Zoom",100); // 縮放比例    // 設定欄高 FColumns = Worksheet.OlePropertyGet("Rows",1); FColumns.OlePropertySet("RowHeight",30); // 設定值 Range=Worksheet.OlePropertyGet("Cells",1,1); Range.OlePropertySet("Value","設定值");    // 設定底色 Range=Worksheet.OlePropertyGet("Cells",2,2); Range.OlePropertySet("Value","設定底色"); Cell=Range.OlePropertyGet("Interior"); Cell.OlePropertySet("ColorIndex",6); // 設定圖樣 Range=Worksheet.OlePropertyGet("Cells",4,2); Range.OlePropertySet("Value","設定圖樣"); Cell=Range.OlePropertyGet("Interior"); Cell.OlePropertySet("Pattern",xlGray50);    // 調整欄寬 FColumns = Worksheet.OlePropertyGet("Columns",3); FColumns.OlePropertySet("ColumnWidth",18); // 設定字型&大小 Range=Worksheet.OlePropertyGet("Cells",1,3); Range.OlePropertySet("Value","設定字體大小"); Cell=Range.OlePropertyGet("Font"); Cell.OlePropertySet("Name","細明體"); Cell.OlePropertySet("Size",10); // 設定字體顏色 Range=Worksheet.OlePropertyGet("Cells",3,3); Range.OlePropertySet("Value","設定字體顏色"); Cell=Range.OlePropertyGet("Font"); Cell.OlePropertySet("ColorIndex",3); // 設定粗體字 Range=Worksheet.OlePropertyGet("Cells",5,3); Range.OlePropertySet("Value","設定粗體字"); Cell=Range.OlePropertyGet("Font"); Cell.OlePropertySet("Bold",true); // 設定斜體字 Range=Worksheet.OlePropertyGet("Cells",7,3); Range.OlePropertySet("Value","設定斜體字"); Cell=Range.OlePropertyGet("Font"); Cell.OlePropertySet("Italic",true); // 設定劃底線 Range=Worksheet.OlePropertyGet("Cells",9,3); Range.OlePropertySet("Value","設定劃底線"); Cell=Range.OlePropertyGet("Font"); Cell.OlePropertySet("Underline",xlUnderlineStyleSingle); // 設定向左靠 Range=Worksheet.OlePropertyGet("Cells",11,3); Range.OlePropertySet("Value","設定向左靠"); Range.OlePropertySet("HorizontalAlignment",xlLeft); Range.OlePropertySet("VerticalAlignment",xlCenter); // 設定向中靠 Range=Worksheet.OlePropertyGet("Cells",13,3); Range.OlePropertySet("Value","設定向中靠"); Range.OlePropertySet("HorizontalAlignment",xlCenter); Range.OlePropertySet("VerticalAlignment",xlCenter); // 設定向右靠 Range=Worksheet.OlePropertyGet("Cells",15,3); Range.OlePropertySet("Value","設定向右靠"); Range.OlePropertySet("HorizontalAlignment",xlRight); Range.OlePropertySet("VerticalAlignment",xlCenter);    // 畫框 // XlBordersIndex 常數 // xlDiagonalDown , xlDiagonalUp , xlEdgeBottom , xlEdgeLeft , xlEdgeRight , xlEdgeTop , xlInsideHorizontal , xlInsideVertical // XlLineStyle 常數 // xlContinuous , xlDash , xlDashDot , xlDashDotDot , xlDot , xlDouble , xlSlantDashDot , xlLineStyleNone // XlBorderWeight 常數 // xlHairline , xlThin , xlMedium , xlThick // XlColorIndex 常數 // xlColorIndexAutomatic。 用以指定繪圖物件的自動填滿。 // xlColorIndexNone。用以指定無內部填滿。     Range=Worksheet.OlePropertyGet("Cells",2,4); Range.OlePropertySet("Value","設定框線"); Cell=Range.OlePropertyGet("Borders"); Cell.OlePropertySet("LineStyle",xlContinuous); Cell.OlePropertySet("Weight",xlThin); Cell.OlePropertySet("ColorIndex",xlAutomatic); // 畫左框 Range=Worksheet.OlePropertyGet("Cells",4,4); Range.OlePropertySet("Value","設定左框"); Cell=Range.OlePropertyGet("Borders"); Item=Cell.OlePropertyGet("Item",xlEdgeLeft); Item.OlePropertySet("LineStyle",xlContinuous); Item.OlePropertySet("Weight",xlThin); Item.OlePropertySet("ColorIndex",xlAutomatic);    // 畫右框 Range=Worksheet.OlePropertyGet("Cells",6,4); Range.OlePropertySet("Value","設定右框"); Cell=Range.OlePropertyGet("Borders"); Item=Cell.OlePropertyGet("Item",xlEdgeRight); Item.OlePropertySet("LineStyle",xlContinuous); Item.OlePropertySet("Weight",xlThin); Item.OlePropertySet("ColorIndex",xlAutomatic);    // 畫上框 Range=Worksheet.OlePropertyGet("Cells",8,4); Range.OlePropertySet("Value","設定上框"); Cell=Range.OlePropertyGet("Borders"); Item=Cell.OlePropertyGet("Item",xlEdgeTop); Item.OlePropertySet("LineStyle",xlContinuous); Item.OlePropertySet("Weight",xlThin); Item.OlePropertySet("ColorIndex",xlAutomatic);    // 畫下框 Range=Worksheet.OlePropertyGet("Cells",10,4); Range.OlePropertySet("Value","設定下框"); Cell=Range.OlePropertyGet("Borders"); Item=Cell.OlePropertyGet("Item",xlEdgeBottom); Item.OlePropertySet("LineStyle",xlContinuous); Item.OlePropertySet("Weight",xlThin); Item.OlePropertySet("ColorIndex",xlAutomatic);    // 設定儲存格格式 - 數字 Range=Worksheet.OlePropertyGet("Cells",1,5); Range.OlePropertySet("Value","1234.5"); Range.OlePropertySet("NumberFormatLocal","#,##0.00");    // 設定直的加總公式, -3 & -1 好像是從加總欄位往上數所得來的 Range=Worksheet.OlePropertyGet("Cells",2,6); Range.OlePropertySet("Value","123"); Range=Worksheet.OlePropertyGet("Cells",3,6); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",4,6); Range.OlePropertySet("Value","345"); Range=Worksheet.OlePropertyGet("Cells",5,6); Range.OlePropertySet("FormulaR1C1","=SUM(R[-3]C:R[-1]C)");    // 設定橫的加總公式, -3 & -1 好像是從加總欄位往左數所得來的 Range=Worksheet.OlePropertyGet("Cells",2,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",2,8); Range.OlePropertySet("Value","345"); Range=Worksheet.OlePropertyGet("Cells",2,9); Range.OlePropertySet("FormulaR1C1","=SUM(RC[-3]:RC[-1])");    // 設定直跟橫都有的加總公式, 先往上數,再往左邊數 Range=Worksheet.OlePropertyGet("Cells",3,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",4,7); Range.OlePropertySet("Value","234"); Range=Worksheet.OlePropertyGet("Cells",5,8); Range.OlePropertySet("FormulaR1C1","=SUM(R[-3]C[-2]:R[-1]C[-1])"); ───────────────────     在Form1.h頭文件中定義幾個變量︰    private: Variant Ex,Wb,Sheet,ERange,EBorders;      並在文件頭中包含如下語句︰    #include "Excel_2K_SRVR.h" #include       在Form1.cpp的文件頭中加入    #pragma link "Excel_2K_SRVR"      主要代碼如下︰    void __fastcall TForm1:: SaveButtonClick(TObject *Sender) { try { SaveButton->Enabled = false; ReadButton->Enabled = false;//使兩個按鈕無效    file://取報表文件CardSend.xls的完整目錄名 AnsiString ExcelFileName = GetCurrentDir()+"\\trpt\\table.xls";     if(!FileExists(ExcelFileName)) { Application->MessageBox("報表模板文件不存在,無法打開﹗", "錯誤",MB_ICONSTOP|MB_OK); return; } file://建立Excel的Ole對象Ex try { Ex = Variant::CreateObject("Excel.Application"); } catch(...) { Application->MessageBox("無法啟動Excel","錯誤",MB_ICONSTOP|MB_OK); return; } file://設置Excel為不可見 Ex.OlePropertySet("Visible",false); file://打開指定的Excel報表文件。報表文件中最好設定只有一個Sheet。 Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",ExcelFileName.c_str()); Wb = Ex.OlePropertyGet("ActiveWorkBook"); Sheet = Wb.OlePropertyGet("ActiveSheet");//獲得當前默認的Sheet    file://清空Excel表，這裡是用循環清空到第300行。對于一般的表格已經足夠了。 AnsiString strRowTemp;  AnsiString strRange; int iCols,iRows;//記錄列數和行數    /*從第三行開始，到第300行止。一般第一行是表標題，第二行是副標題或者製表日期。*/ for(iRows=3;iRows<300;iRows ) { file://假設只有6列。 for (iCols = 1;iCols < 7; iCols ) { file://清空行 Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value",""); } file://去掉表格邊框 strRange = "A" IntToStr(iRows) ":F" IntToStr(iRows);//獲取操作範圍 ERange = Sheet.OlePropertyGet("Range",strRange.c_str()); EBorders = ERange.OlePropertyGet("Borders");//獲取邊框對象 EBorders.OlePropertySet("linestyle",xlNone); } AnsiString strPtrDate; file://存放當前日期，作為製表日期 DateSeparator = '-'; ShortDateFormat = "yyyy/m/d";//設置為年/月/日格式 strPtrDate = DateToStr(Date());//取當前日期 AnsiString strYear = strPtrDate.SubString(1,4); strPtrDate = strPtrDate.SubString(6,strPtrDate.Length()-5); AnsiString strMonth = strPtrDate.SubString(1,strPtrDate.Pos("-")-1); AnsiString strDay = strPtrDate.SubString(strPtrDate.Pos("-") 1, strPtrDate.Length()-strPtrDate.Pos("-")); strPtrDate = strYear "年" strMonth "月" strDay "日"; AnsiString strData = "報表標題";//報表標題 file://將報表標題置於第一行第一列。在此之前，應將報表文件的標題格式設定好。 Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value", strData.c_str()); file://將製表日期置於表格第二行的右側。 Sheet.OlePropertyGet("Cells",2,5).OlePropertySet("Value", strPtrDate.c_str()); iRows = 3;//在第三行放置表格的列名 Sheet.OlePropertyGet("Cells",iRows,1).OlePropertySet("Value","列名1"); Sheet.OlePropertyGet("Cells",iRows,2).OlePropertySet("Value","列名2"); Sheet.OlePropertyGet("Cells",iRows,3).OlePropertySet("Value","列名3"); Sheet.OlePropertyGet("Cells",iRows,4).OlePropertySet("Value","列名4"); Sheet.OlePropertyGet("Cells",iRows,5).OlePropertySet("Value","列名5"); Sheet.OlePropertyGet("Cells",iRows,6).OlePropertySet("Value","列名6"); file://畫表格邊框，在A3︰F3之間取範圍 strRange = "A" IntToStr(iRows) ":F" IntToStr(iRows); ERange = Sheet.OlePropertyGet("Range",strRange.c_str()); EBorders = ERange.OlePropertyGet("Borders"); EBorders.OlePropertySet("linestyle",xlContinuous); EBorders.OlePropertySet("weight",xlThin); EBorders.OlePropertySet("colorindex",xlAutomatic); iRows ; file://從數據庫中取數據(略)，假設數據集放入Query1中。 Query1->Open();//打開數據集 file://循環取數 while(!Query1->Eof) { file://循環取字段的數據放到Excel表對應的行列中 for(iCols=1;iCols<7;iCols ) { strRowTemp = Query1->Fields->Fields[iCols-1]->AsString; Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value", strRowTemp.c_str()); } file://畫該行的表格邊框 strRange = "A" IntToStr(iRows) ":F" IntToStr(iRows); ERange = Sheet.OlePropertyGet("Range",strRange.c_str()); EBorders = ERange.OlePropertyGet("Borders"); EBorders.OlePropertySet("linestyle",xlContinuous); EBorders.OlePropertySet("weight",xlThin); EBorders.OlePropertySet("colorindex",xlAutomatic); iRows ; Query1->Next(); }//while結束 Wb.OleProcedure("Save");//保存表格 Wb.OleProcedure("Close");關閉表格 Ex.OleFunction("Quit");退出Excel file://定義目標文件名 AnsiString DestinationFile = GetCurrentDir() "\\report\\table.xls"; file://將剛剛修改的Excel表格文件table.xls拷貝到report目錄下 if(!CopyFile(ExcelFileName.c_str(),DestinationFile.c_str(),false)) { Application->MessageBox("複製文件操作失敗,Excel文件可能正在使用中﹗", "錯誤",MB_ICONSTOP|MB_OK); return; } Application->MessageBox("成功完成報表保存﹗\n可以按\'打開Excel文件\' 按鈕進行報表工作","提示",MB_ICONINFORMATION|MB_OK); SaveButton ->Enabled = true; ReadButton ->Enabled=true; }//try結束 catch(...) { Application->MessageBox("操作Excel表格失敗﹗", "錯誤",MB_ICONSTOP|MB_OK); Wb.OleProcedure("Close"); Ex.OleFunction("Quit"); SaveButton ->Enabled = true; ReadButton ->Enabled=false; } } 至此，完成報表數據的寫入工作。如果要對完成的Excel表進行操作，可以點擊"打開Excel表文件按鈕"(ReadButton),進行修改，保存，打印等操作。ReadButton的單擊事件如下實現︰ void __fastcall TForm1:: ReadButtonClick(TObject *Sender) { try { file://指定report目錄下的報表文件用于用戶操作 AnsiString ExcelFileName = GetCurrentDir(); "\\report\\table.xls"; if(!FileExists(ExcelFileName)) { Application->MessageBox("Excel表文件不存在,無法打開﹗", "錯誤",MB_ICONSTOP|MB_OK); return; } try { Ex = Variant::CreateObject("Excel.Application"); } catch(...) { Application->MessageBox("無法啟動Excel","錯誤",MB_ICONSTOP|MB_OK); return; } file://使Excel可見 Ex.OlePropertySet("Visible",true); file://打開Excel表格文件Table.xls Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",ExcelFileName.c_str()); } catch(...) { Application->MessageBox("操作Excel表格錯誤﹗","錯誤",MB_ICONSTOP|MB_OK); Ex.OleFunction("Quit"); } Terence Chiu

------
Terence Chiu

感謝Terence Chiu 轉貼 denise 大大的這些文章以及姜宏華先生的部分文章 這些我都已拜讀過了。 但是對於VBA轉成BCB時對於物件的方法屬性的分類 至今還弄不清 真得多花時間慢慢 TRY嚕 天啊！給我點時間睡覺吧！ 又要變懸賞文了 －－－－－－－－－－－－－－－－－－－－－－－－ 我是新手，觀念還不是很清楚。 問了些惹人發笑的問題，請各位大聲的在螢幕後笑出來。沒關係的！ 免得害各位引起嚴重內傷！ －－－－－－－－－－－－－－－－－－－－－－－－

想請問一下
Range=Worksheet.OlePropertyGet("Cells",2,4);
Range.OlePropertySet("Value","設定框線");
Cell=Range.OlePropertyGet("Borders");
Cell.OlePropertySet("LineStyle",xlContinuous);
Cell.OlePropertySet("Weight",xlThin);
Cell.OlePropertySet("ColorIndex",xlAutomatic);
這個設定框線的部分是一格一格的設定，那如果我要設定由(1,1)到(10000,10000)的話要怎麼"一起"設定呢



===================引 用 文 章===================
Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!C1:C16").CreatePivotTable TableDestination:="", TableName:="樞紐分析表1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("樞紐分析表1").SmallGrid = False Charts.Add ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A3") ActiveChart.Location Where:=xlLocationAsNewSheet ActiveWorkbook.PublishObjects.Add(xlSourceChart, "D:\Page.htm", "Chart1", "", _ xlHtmlChart, "Book2_12184", "").Publish (True) ChDir "D:\" 小弟不才，接連爬了幾篇文章。發現只有DELPHI的範例。研究過後發現DELPHI的對VBA的翻譯語法跟BCB有著大大的不同唉！抱著一本VBA的語法字典卻苦不知把他翻成BCB語法，望大大們慷慨相授 －－－－－－－－－－－－－－－－－－－－－－－－我是新手，觀念還不是很清楚。問了些惹人發笑的問題，請各位大聲的在螢幕後笑出來。沒關係的！免得害各位引起嚴重內傷！－－－－－－－－－－－－－－－－－－－－－－－－