MFC用在Excel（語法+註解篇）

初始化

objApp.CreateDispatch("Excel.Application",&e) //產生一個Excel應用程式
objApp.SetVisible(TRUE); //繪出Excel應用程式視窗
objBooks = objApp.GetWorkbooks(); //取得應用程式的活頁簿記憶體位址
objBook = objBooks.Add(VOptional); //開新檔案
objBook.AttachDispatch(objBooks.Add(_variant_t("C:\\test.xls"))); //開啟舊檔（也可以叫Dlg出來用）
objSheets = objBook.GetWorksheets(); //取得Books的Sheet記憶體位址
objSheet = objSheets.GetItem(COleVariant((short)1)); //開啟sheet1（就是點一下Sheet1翻到這一頁）
objSheet.SetName("My Sheet"); //設定sheet1的名稱稱

設定內容

range = objSheet.GetRange(COleVariant("A1"),COleVariant("A1")); //選取Sheet的範圍（使用Excel座標）
range.SetColumnWidth(_variant_t((float)1.2)); //設定欄寬（像素值）
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("My Auto-Excel Demo")); //填入內容
range.BorderAround(_variant_t((long)1),3,1,_variant_t((long)#000000)); //外框
range.SetHorizontalAlignment(COleVariant((short)3)); //置中
range.SetMergeCells(_variant_t(true)); //合併儲存格
range.SetColumnWidth(_variant_t((float)30.38)); //設定欄寬
range.SetRowHeight(_variant_t((float)54.00)); //設定欄高
col = range.GetEntireColumn(); //選取某個範圍的一整排
col.AutoFit(); //自動調整一整排的欄寬
（列同理）

設定字型

font = range.GetFont(); //取得選取範圍，設定字型的記憶體位址
font.SetName(_variant_t("Arial")); //設定字型
font.SetSize(_variant_t((short)12)); //設定字體大小pt
font.SetBold(_variant_t((short)TRUE)); //粗體
font.SetColorIndex(_variant_t((short)5)); //字色（查表，應該還有更好的方法）

設定儲存格

cell = range.GetInterior(); //取得選取範圍，設定儲存格的記憶體位址
cell.SetColorIndex(_variant_t((short)5)); //設定底色（查表）

其它

char buf[200]; //暫存的字串
ZeroMemory(buf,sizeof(buf)); //清空記憶體
sprintf(buf,"A%c33",'A'+i-11); //用變數定義儲存格座標
strcpy(buf,"總計"); //定義欲輸入的字串

圖表

//畫圖
lpDisp = objSheet.ChartObjects(VOptional);
chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer for ChartObjects to the chartobjects object.
chartobject = chartobjects.Add(20, 140, 350, 250); //defines the rectangle,
// adds a new chart at that rectangle and assigns its object reference to a ChartObject variable named chartobject
xlsChart.AttachDispatch(chartobject.GetChart()); // GetChart() returns LPDISPATCH, and this attaches it to your chart object.
lpDisp = objSheet.GetRange(COleVariant("B3"), COleVariant("F6")); // The range containing the data to be charted.
range.AttachDispatch(lpDisp);
var.vt = VT_DISPATCH;
// .vt is the usable member of the tagVARIANT Struct. Its value is a union of options.
var.pdispVal = lpDisp; // Assign IDispatch pointer of the Source range to var.
xlsChart.ChartWizard(
var, // Source.
COleVariant((short)11), // Gallery: 3d Column.
VOptional, // Format, use default.
COleVariant((short)1), // PlotBy: xlRows.
COleVariant((short)1), // CategoryLabels.
_variant_t("Open,Working,Verify,Returned,Closed,Canceled"), 
COleVariant((short)1), // SeriesLabels.
COleVariant((short)TRUE), // HasLegend.
VOptional, //COleVariant("Use by Month"), // Title.
VOptional, //COleVariant("Month"), // CategoryTitle.
VOptional, //COleVariant("Usage in Thousands"), // ValueTitles.
VOptional // ExtraTitle.
);
xlsChart.SetChartType((long)55); //設定圖表類型（查表：圖表種類篇）
//調刻度
/*_Axis ax = xlsChart.Axes(_variant_t((short)2),_variant_t((short)1)); //2是縱軸, 1不清楚
sc = ax.GetMaximumScale();
if(sc < 5.0)
{
chart.SetAutoScaling(FALSE);
ax.SetMajorUnitIsAuto(FALSE);
ax.SetMajorUnit(1);
ax.SetMinorUnitIsAuto(FALSE);
ax.SetMinorUnit(1);
//ax.SetMinorUnitScale(1);
ax.SetMaximumScale((long)(sc+1)); //最大刻度
}*/

結尾

objApp.SetUserControl(TRUE);
range.ReleaseDispatch();chartobject.ReleaseDispatch();
chartobjects.ReleaseDispatch();objSheet.ReleaseDispatch();
objSheets.ReleaseDispatch();objBook.ReleaseDispatch();
objBooks.ReleaseDispatch();
objApp.ReleaseDispatch();

參考資料：

流浪小築 http://www.intra.idv.tw/