轉EXCEL

// 轉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])");