//---------------------------------------------------------------------------
// 一些會用到的總體變數, 可以定義在 .h 檔內.
Variant Excel,Workbooks,Workbook;
Variant Worksheets,Worksheet, Range, Cell, FColumns, Format, Item;
//---------------------------------------------------------------------------
//建立一個新的Excel 檔.
void __fastcall TForm1::ButtonNewFileClick(TObject *Sender)
{
// 建立一個可見的 Excel 物件
Excel=CreateOleObject("Excel.Application");
Excel.OlePropertySet("Visible", true); // 設為 false 則不可見
bIsCreated=true;
// 產生 workbooks 跟 workbook 物件.
Workbooks=Excel.OlePropertyGet("Workbooks");
Workbook=Workbooks.OleFunction("Add");
// 產生 worksheets 跟 worksheet 物件.
Worksheets=Workbook.OlePropertyGet("Worksheets");
Worksheet=Worksheets.OlePropertyGet("Item", 1);
}
//---------------------------------------------------------------------------
// 開啟一個已存在的 Excel 檔
void __fastcall TForm1::ButtonOpenFileClick(TObject *Sender)
{
// 若先前尚未建立 Excel 物件, 則建立之.
if (!bIsCreated)
{
// 建立一個可見的 Excel 物件
Excel=CreateOleObject("Excel.Application");
Excel.OlePropertySet("Visible", true);
}
// 開啟某個已存在的 workbooks 跟 workbook.
Workbooks=Excel.OlePropertyGet("Workbooks");
Workbook=Workbooks.OleFunction("Open", "d:\\test1.xls");
// 將該Excel 檔的 Worksheets 設給變數 Worksheets.
Worksheets=Workbook.OlePropertyGet("Worksheets");
}
//---------------------------------------------------------------------------
// 將開啟中的Excel 存成檔案.
void __fastcall TForm1::ButtonSaveFileClick(TObject *Sender)
{
// 將目前開啟中的Excel 存成檔案.
Workbook.OleFunction("SaveAs", "d:\\test2.xls");
}
//---------------------------------------------------------------------------
// 關閉 Excel 程式.
void __fastcall TForm1::ButtonCloseFileClick(TObject *Sender)
{
Excel.OleFunction("Quit");
bIsCreated=false;
}
//---------------------------------------------------------------------------
// 新增一個 sheet.
void __fastcall TForm1::ButtonNewSheetClick(TObject *Sender)
{
Worksheet=Worksheets.OleFunction("Add");
}
//---------------------------------------------------------------------------
// 刪除一個 sheet.
void __fastcall TForm1::ButtonDeleteSheetClick(TObject *Sender)
{
// 取得第2 個 worksheet, 並刪除之.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
Worksheet.OlePropertyGet("Delete");
}
//---------------------------------------------------------------------------
// 重新替 sheet 命名.
void __fastcall TForm1::ButtonRenameSheetClick(TObject *Sender)
{
// 最得第1 個 worksheet, 並將之命名為 CrazyStar.
Worksheet=Worksheets.OlePropertyGet("Item", 1);
Worksheet.OlePropertySet("Name", "CrazyStar");
}
//---------------------------------------------------------------------------
// 設定某個sheet 為作用中.
void __fastcall TForm1::ButtonActivateSheetClick(TObject *Sender)
{
// 設定第二個 sheet 為 active sheet.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
Worksheet.OlePropertyGet("Activate");
}
//---------------------------------------------------------------------------
// 設定Excel 檔的版面格式
void __fastcall TForm1::ButtonSetPageClick(TObject *Sender)
{
// 將該Excel 檔的 Worksheets 設給變數 Worksheets.
Worksheets=Workbook.OlePropertyGet("Worksheets");
// 將 Worksheets 的第2 個 worksheet 設給變數 Worksheet.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
// 版面設定
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); // 縮放比例
}
//---------------------------------------------------------------------------
// 設定 cell 的格式
void __fastcall TForm1::ButtonSetGridFormat1Click(TObject *Sender)
{
// 將該Excel 檔的 Worksheets 設給變數 Worksheets.
Worksheets=Workbook.OlePropertyGet("Worksheets");
// 將 Worksheets 的第2 個 worksheet 設給變數 Worksheet.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
// 設定欄高
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);
}
void __fastcall TForm1::ButtonSetGridFormat2Click(TObject *Sender)
{
// 設定劃底線
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);
// 畫框
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);
}
//-------------------------------------------------------------------------------
//---------------------------------------------------------------------------
// 寫入某值到 Excel 的某格中.
void __fastcall TForm1::ButtonSetValueClick(TObject *Sender)
{
// 將該Excel 檔的 Worksheets 設給變數 Worksheets.
Worksheets=Workbook.OlePropertyGet("Worksheets");
// 將 Worksheets 的第2 個 worksheet 設給變數 Worksheet.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
// 取得第 (2,3) 個 cell.
Cell=Worksheet.OlePropertyGet("Cells", 2, 3);
Cell.OlePropertySet("Value", "CrazyStar"); // 設定它的文字內容為 "CrazyStar".
}
//---------------------------------------------------------------------------
// 讀取Excel 某格的值到程式中.
void __fastcall TForm1::ButtonGetValueClick(TObject *Sender)
{
Variant Var;
int A;
float B;
String C;
// 將該Excel 檔的 Worksheets 設給變數 Worksheets.
Worksheets=Workbook.OlePropertyGet("Worksheets");
// 將 Worksheets 的第2 個 worksheet 設給變數 Worksheet.
Worksheet=Worksheets.OlePropertyGet("Item", 2);
// 取得第 (2,3) 個 cell.
Cell=Worksheet.OlePropertyGet("Cells", 2, 3);
// 讀取該 cell 的值到變數 Var 中
Var=Cell.OlePropertyGet("Value");
// 轉換取得的資料.
if (String(Var)!="")
{
A=StrToInt(Var);
B=StrTofloat(Var);
C=String(Var);
}
}
void __fastcall TForm1::ButtonSetGridFormat3Click(...)
{
// 畫上框
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])");
}
留言列表
