[分享] Excel function with MFC

[Zephyr750 (紅蓮西風750)]

上網收集的excel相關的操作方法
把它包成一個簡單使用的class

跟大家分享

WindowsXP
Office2003
測試無誤
//--------------------------------------
//excelfun.h
#ifndef __XLEF_H__
#define __XLEF_H__

//#include <comdef.h>
#include "excel.h"
#include "stdafx.h"

//Set BorderAround
#define BA_DOT          1
#define BA_NORMAL       2
#define BA_COARSE       3
#define BA_THICK        4

//Set Horizontal Alignment
#define HA_CENTER       3

//Set Horizontal Alignment
#define HA_CENTER       3

//define參數尚未補完

class xlsFile
{
    COleVariant VOptional, VTRUE, VFALSE;
        //VOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR)
        //VFalse((short)FALSE)
        //VTrue((short)TRUE)
    _Application objApp;
    Workbooks objBooks;
    _Workbook objBook;
    Sheets objSheets;
    _Worksheet objSheet,objSheetT;
    Range range,col,row;//,oCell;//,range2,range3;
    Interior cell;
    Font font;
    COleException e;

    LPDISPATCH lpDisp;
    ChartObjects chartobjects;
    ChartObject chartobject;
    _Chart xlsChart;
    VARIANT var;

    char buf[200];  //暫存的字串
    char buf1[200];
    char buf2[200];
//      int i,j;

public:
        xlsFile();
        ~xlsFile();
        //xlsFile& //開了檔案之後可以繼續選擇Sheet和命名
        xlsFile& New();
        xlsFile& Open();
        xlsFile& Open(const char*);
        void SaveAs(const char*);
        void Save();
        //void Quit(CString FileName);
        void Quit();

        void SelectSheet(short, const char*);  //選擇Sheet，並指定名字
        void CopySheet(const char*);    //複製Sheet插在指定Sheet後面
        void CopySheet(short );                 //複製Sheet插在指定Sheet後面
        void SetVisible(bool);//設定視窗為看得見，並把控制權交給使用者
        //-----------------------------------------------------
        //xlsFile& 選了格子之後可以繼續下「讀」「寫」的成員函數
        //選一格
        xlsFile& SelectCell(const char* );
        xlsFile& SelectCell(const char* , int );
        xlsFile& SelectCell(char,int);
        xlsFile& SelectCell(char,char,int);
        //選一個範圍
        xlsFile& SelectCell(const char* , const char* );
        xlsFile& SelectCell(const char* , int ,const char* , int );
        xlsFile& SelectCell(char,int,char,int);
        xlsFile& SelectCell(char,char,int,char,char,int);
        //---------------------------------------------
        //設定資料進儲存格（存成字串）
        //一般版
        void SetCell(int);
        void SetCell(double);
        void SetCell(long);
        void SetCell(const char* );
        void SetCell(CString );
        //自訂細部格式版
        void SetCell(const char*, int);
        void SetCell(const char*, double);
        void SetCell(const char*, long);
        //---------------------------------------
        xlsFile& SetCellHeight(float); //設定列高
        xlsFile& SetCellWidth(float);  //設定欄寬
        void AutoFitHight();    //自動調整列高
        void AutoFitWidth();    //自動調整欄寬
        //--------------------------------------------
        xlsFile& SetMergeCells();//合併儲存格
        xlsFile& SetCellBorder(int Boarder, long color = RGB(0,0,0));//設定框線粗細和顏色
        xlsFile& SetCellColor(short);//設定底色

        xlsFile& SetFont(const char* fontType = "新細明體");//設定字型（預設新細明體）
        xlsFile& SetFontSize(short fontSize = 12);//設定字體大小（預設12pt）
        xlsFile& SetFontColor(short colorIndex = 1);//字型顏色（預設黑色）
        xlsFile& SetFontBold(bool isBold = false); //粗體

        xlsFile& SetHorztlAlgmet(short );       //水平對齊
        xlsFile& SetVrticlAlgmet(short );       //垂直對齊
        //--------------------------------------------
        CString GetCell();

};
//---------------------------------------
//excelfun.cpp

#include "stdafx.h"
#include "excelfun.h"
#include <comdef.h>

xlsFile::xlsFile():
VOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR), VFALSE((short)FALSE), VTRUE((short)TRUE)
{
        ZeroMemory(buf,sizeof(buf));
        ZeroMemory(buf1,sizeof(buf1));
        ZeroMemory(buf2,sizeof(buf2));
        //Step 1.叫Excel應用程式
        if(!objApp.CreateDispatch("Excel.Application",&e))
        {
                CString str;
                str.Format("Excel CreateDispatch() failed w/err 0x%08lx", e.m_sc);
                AfxMessageBox(str, MB_SETFOREGROUND);
        }
};

xlsFile::~xlsFile()
{
        //objApp.SetUserControl(TRUE);  //移至visualable
        range.ReleaseDispatch();
        //chartobject.ReleaseDispatch();
        //chartobjects.ReleaseDispatch();
        objSheet.ReleaseDispatch();
        objSheets.ReleaseDispatch();
        objBook.ReleaseDispatch();
        objBooks.ReleaseDispatch();
        objApp.ReleaseDispatch();
}

//Open()
xlsFile& xlsFile::New()
{
        return Open();
}

xlsFile& xlsFile::Open()
{
        objBooks = objApp.GetWorkbooks();
    objBook = objBooks.Add(VOptional);  //開新檔案
        objSheets = objBook.GetWorksheets();
        return *this;
}

xlsFile& xlsFile::Open(const char* path)
{
        objBooks = objApp.GetWorkbooks();
    objBook.AttachDispatch(objBooks.Add(_variant_t(path))); //開啟一個已存在的檔案
        objSheets = objBook.GetWorksheets();
        return *this;
}

void xlsFile::SaveAs(const char* strTableName)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf, "%s", strTableName);
        objBook.SaveAs(
                COleVariant(buf),
                VOptional, VOptional,
                VOptional, VOptional,
                VOptional, 1,
                VOptional, VFALSE,
                VOptional, VOptional, VOptional);
        //objBook.Close (VOptional,COleVariant(buf),VOptional);
}

void xlsFile::Save()
{
        objBook.Save();
}

/*
void xlsFile::Quit(CString FileName)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%s",FileName);
        objBook.Close(VFalse,COleVariant(buf), VOptional); //關閉不跳出視窗問問題
        objBooks.Close();
        objApp.Quit();
}
*/
void xlsFile::Quit()
{
        objBook.Close(VFALSE,VOptional, VOptional);
        objBooks.Close();
        objApp.Quit();
}

//SelectSheet()
void xlsFile::SelectSheet(short SheetNumber,const char* SheetName)
{
        objSheet = objSheets.GetItem(COleVariant(SheetNumber)); //從sheet1開始
    objSheet.SetName(SheetName); //設定sheet名稱
}

void xlsFile::CopySheet(const char* SheetName)
{
        objSheet.AttachDispatch(objSheets.GetItem(_variant_t(SheetName)),true);
        objSheet.Copy(vtMissing,_variant_t(objSheet));
}

void xlsFile::CopySheet(short SheetNumber)
{
        objSheet.AttachDispatch(objSheets.GetItem(COleVariant(SheetNumber)));
        objSheet.Copy(vtMissing,_variant_t(objSheet));
}

//SelectCell()
xlsFile& xlsFile::SelectCell(const char* x)
{
        range=objSheet.GetRange(COleVariant(x),COleVariant(x));
        return *this;
}

xlsFile& xlsFile::SelectCell(const char* x1,const char* x2)
{
        range=objSheet.GetRange(COleVariant(x1),COleVariant(x2));
        return *this;
}

xlsFile& xlsFile::SelectCell(const char* x, int y)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%s%d",x,y);
        range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
        return *this;
}

xlsFile& xlsFile::SelectCell(const char* x1, int y1, const char* x2, int y2)
{
        ZeroMemory(buf1,sizeof(buf1));
        ZeroMemory(buf2,sizeof(buf2));
        sprintf(buf1,"%s%d",x1,y1);
        sprintf(buf2,"%s%d",x2,y2);
        range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
        return *this;
}
//選一個格子

xlsFile& xlsFile::SelectCell(char x, int y)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%c%d",x,y);
        range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
        return *this;
}

xlsFile& xlsFile::SelectCell(char x1,char x2,int y)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%c%c%d",x1,x2,y);
        range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
        return *this;
}

//選範圍
xlsFile& xlsFile::SelectCell(char x1, int y1, char x2, int y2)
{
        ZeroMemory(buf1,sizeof(buf1));
        ZeroMemory(buf2,sizeof(buf2));
        sprintf(buf1,"%c%d",x1,y2);
        sprintf(buf2,"%c%d",x1,y2);
        range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
        return *this;
}

xlsFile& xlsFile::SelectCell(char x11, char x21, int y1, char x12, char x22, int y2)
{
        ZeroMemory(buf1,sizeof(buf1));
        ZeroMemory(buf2,sizeof(buf2));
        sprintf(buf1,"%c%c%d",x11,x21,y1);
        sprintf(buf2,"%c%c%d",x12,x22,y2);
        range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
        return *this;
}

//合併儲存格
xlsFile& xlsFile::SetMergeCells()
{
        //先選取一個範圍的儲存格
    range.SetMergeCells(_variant_t(true));
        return *this;
}

//SetCell()
void xlsFile::SetCell(int Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%d",Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(long Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%d",Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(double Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%f",Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(const char* Data)
{
        ZeroMemory(buf,sizeof(buf));
        strcpy(buf,Data);
        //sprintf(buf,"%s",Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(CString Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,"%s",Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(const char* Format, int Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,Format,Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(const char* Format, double Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,Format,Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}

void xlsFile::SetCell(const char* Format, long Data)
{
        ZeroMemory(buf,sizeof(buf));
        sprintf(buf,Format,Data);
        range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));

}

//SetVisible()
void xlsFile::SetVisible(bool a)
{
        objApp.SetVisible(a);    //顯示Excel檔
        objApp.SetUserControl(a);//使用者控制後，就不可以自動關閉
}
//GetCell()
CString xlsFile::GetCell()
{
//      VARIANT cellvalue;
//      cellvalue = ;
        //cellvalue = range.GetText();
        return (char*)_bstr_t(range.GetItem(_variant_t((long)1), _variant_t((long)1)));
}
//-------------------------------------------------
//設定框線、框線顏色
xlsFile& xlsFile::SetCellBorder(int Boarder, long color)
{
//      Boarder = (long)1;
//      color = (long)RGB(0,0,0);
        range.BorderAround(_variant_t((long)1),Boarder,1,_variant_t(color));//細框線
        return *this;
}
//設定底色
xlsFile& xlsFile::SetCellColor(short colorIndex)
{
        cell = range.GetInterior();                   //取得選取範圍，設定儲存格的記憶體位址
    cell.SetColorIndex(_variant_t(colorIndex));   //設定底色（查表）
        //cell.SetColor(_variant_t(colorIndex));
        return *this;
}

//-------------------------------------------------
//設定字型
xlsFile& xlsFile::SetFont(const char* fontType)
{
        font = range.GetFont();
    font.SetName(_variant_t(fontType));//原本是韓文字型
        return *this;
}

xlsFile& xlsFile::SetFontSize(short fontSize)
{
        font = range.GetFont();
    font.SetSize(_variant_t(fontSize));//字型大小pt
        return *this;
}

xlsFile& xlsFile::SetFontColor(short colorIndex)
{
        font = range.GetFont();
        font.SetColorIndex(_variant_t(colorIndex)); //字色(預設黑色)
        return *this;
}

xlsFile& xlsFile::SetFontBold(bool isBold)
{
        font = range.GetFont();
        if (isBold)             font.SetBold(VTRUE);
        else                    font.SetBold(VFALSE);
        //font.SetBold(_variant_t(isBold)); //粗體
        return *this;
}

//-------------------------------------------------
//對齊方式
//水平對齊
xlsFile& xlsFile::SetHorztlAlgmet(short position)
{
        range.SetHorizontalAlignment(COleVariant(position));
        return *this;
}

//垂直對齊
xlsFile& xlsFile::SetVrticlAlgmet(short position)
{
        range.SetVerticalAlignment(COleVariant(position));
        return *this;
}

//--------------------------
//設定欄寬列高

void xlsFile::AutoFitWidth()
{
        col = range.GetEntireColumn(); //選取某個範圍的一整排
        col.AutoFit(); //自動調整一整排的欄寬
}

void xlsFile::AutoFitHight()
{
        row = range.GetEntireRow(); //選取某個範圍的一整排
        row.AutoFit(); //自動調整一整排的列高
}

xlsFile& xlsFile::SetCellHeight(float height)
{
        range.SetRowHeight(_variant_t(height));
        return *this;
}

xlsFile& xlsFile::SetCellWidth(float height)
{
        range.SetColumnWidth(_variant_t(height));
        return *this;
}

//---------------------------------------

--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 60.248.125.78
→ angleevil:可惜沒有gnu的QQ
推 yauhh:你可以整理成專案放到open source網站上,分享比較方便
※ 編輯: Zephyr750       來自: 219.80.139.93        (07/08 21:18)
推 VictorTom:有分享有推XD