環境:vs2015,excel2007,win7 64位
第一步:建立MFC工程,導入Application Range Workbook Worksheet Workbooks Worksheets
//最終要包含的excel服務的頭檔案
#include "CApplication.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorksheet.h"
#include "CWorkbooks.h"
#include "CWorksheets.h"
第二步:建立excel服務初始化、資源關閉、excel儲存的基類
//ExcelFileOp.h
#pragma once
#include "CApplication.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorksheet.h"
#include "CWorkbooks.h"
#include "CWorksheets.h"
#include <string>
#include <utility>
#include <vector>
#include <map>
using namespace std;
// 進價、售價
struct _StrSaleInfo
{
CString strStockPrice;
CString strSalePrice;
};
// 貨物資訊
typedef map<CString, _StrSaleInfo> _st_cargo_info_;
class CMyExcel
{
private:
//标記Excel對象的變量
static CApplication *m_papp; // excel app 指針,多個使用,但隻初始化一次
static int m_siInference; // app 的引用計數,初始化一次+1,隻有為0時才真正初始化,其他時候隻做+1操作。析構自動-1,如果為0則釋放m_papp
CWorkbooks m_books; // books
long m_rowCount; //标記Excel目前寫入的列數
long m_sheetCount; //标記Excel使用了多少标簽頁的變量
long m_totalRow; //标記Excel總列數的變量
long m_totalCol; //标記Excel總行數的變量
char *m_colPst; //一個含有A-Z的數組
pair<long, long> m_cellPosition; //cell的行和列
protected:
COleVariant m_covTrue, m_covFalse, m_coverOptional, m_filePath;
enum _EN_VISIBLE_
{
eUN_VISIBLE = 0, // 可見
eVISIBLE, // 不可見
};
COleVariant covOptional; // 初始化books、打開book的時候需要用到
CApplication * fnGetApp()
{
return m_papp;
}
public:
CMyExcel();
virtual ~CMyExcel();
};
// ExcelFileOp.cpp
#include "stdafx.h"
#include "ExcelFileOp.h"
#include"stdafx.h"
// 初始化靜态成員
CApplication * CMyExcel::m_papp = NULL;
int CMyExcel::m_siInference = 0;
CMyExcel::CMyExcel() :m_rowCount(1), m_sheetCount(1), m_totalCol(256), m_totalRow(65536),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR)
{
// 擷取系統的excel句柄
if (NULL == m_papp)
{
m_papp = new CApplication;
if (!m_papp->CreateDispatch(TEXT("Excel.Application")))
{
AfxMessageBox(_T("Could not start Excel and get Application object !"));
return;
}
}
m_siInference++;
m_covTrue = COleVariant((short)TRUE);
m_covFalse = COleVariant((short)FALSE);
m_coverOptional = COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
m_books = m_papp->get_Workbooks();
m_papp->put_Visible(FALSE); // 初始化excel為不可見
m_papp->put_UserControl(FALSE); // 初始化控件為不可見
}
// 釋放資源
CMyExcel::~CMyExcel()
{
if (0 == --m_siInference)
{
m_books.Close();
m_books.ReleaseDispatch();
m_papp->Quit();
m_papp->ReleaseDispatch();
delete m_papp;
m_papp = NULL;
}
}
第三步:建立實際的讀寫excel的類,繼承于CMyExcel
// ExcelBookReadWrite.h
#pragma once
#include "ExcelFileOp.h"
class CExcelBookReadWrite :
public CMyExcel
{
public:
CExcelBookReadWrite(const CString &csFilePath,const bool bVisible = false);
~CExcelBookReadWrite();
private:
_EN_VISIBLE_ m_eVisible;
CWorkbooks m_books;
CWorkbook m_book;
CWorksheets m_sheets;
//标記excel中目前寫入的标簽頁
CWorksheet m_sheet;
CRange m_range; //标記寫入的範圍
CApplication *m_pAppInstance;
public:
// 擷取excel的行數
virtual long fnGetLineCount();
// 擷取excel的列數
virtual long fnGetCloCount();
// 設定指定cell的值
virtual void fnSetCell(const pair<long, long> &pos, const CString &csNews);
// 擷取指定cell的值
virtual CString fnGetCell(const pair<long, long> &pos);
// 擷取目前cell的坐标(行、列)
virtual pair<long, long> fnGetCellPosition();
// 根據cell文字的長度,設定列的長度。如果pos為-1,-1則根據目前cell的内容來設定。設定長度時,先擷取目前cell的長度,如果需要設定的長度較長則更新
virtual void fnSetLineLength(const pair<long, long> &pos = make_pair(-1, -1));
// 儲存excel
virtual void fnSaveBook();
};
// ExcelBookReadWrite.cpp
#include "stdafx.h"
#include "ExcelBookReadWrite.h"
// 打開指定路徑的excel
CExcelBookReadWrite::CExcelBookReadWrite(const CString &csFilePath, const bool bVisible)
{
m_pAppInstance = fnGetApp();
if (NULL == m_pAppInstance)
{
return;
}
m_books = m_pAppInstance->get_Workbooks();
m_book = m_books.Open(csFilePath, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional);
m_sheets = m_book.get_Sheets();
m_sheet = m_sheets.get_Item(COleVariant((short)1));
m_range = m_sheet.get_UsedRange();
CRange cols = m_range.get_EntireColumn();
cols.AutoFit();
m_eVisible = eVISIBLE;
if (bVisible)
{
m_eVisible = eUN_VISIBLE;
}
m_pAppInstance->put_Visible(bVisible);
m_pAppInstance->put_Visible(TRUE);
}
// 關閉打開的book
CExcelBookReadWrite::~CExcelBookReadWrite()
{
// 如果此處不save,則會造成退出時,有殘留的 excel.exe 程序
m_book.put_Saved(TRUE);
//m_book.Close();
m_book.ReleaseDispatch();
}
// 擷取excel目前sheet的行數(橫向)
long CExcelBookReadWrite::fnGetLineCount()
{
//_Worksheet ws;
//Range range;
//range = ws.GetUsedRange();//獲得Worksheet已使用的範圍
//range = range.GetRows(); //獲得總行數(LPDISPATCH類型)
//long UsedRows = range.GetCount(); //即可獲得已使用的行數了。
//獲得列數也是一樣的方法,把GetRows()改為GetColumns即可。
//CRange range = m_sheet.get_UsedRange();
CRange rows = m_range.get_Rows();
return rows.get_Count();
}
// // 擷取excel目前sheet的列數(縱向)
long CExcelBookReadWrite::fnGetCloCount()
{
//CRange range = m_sheet.get_UsedRange();
CRange cols = m_range.get_Columns();
return cols.get_Count();
}
void CExcelBookReadWrite::fnSetCell(const pair<long, long> &pos,CONST CString &csNews)
{
m_range.AttachDispatch(m_sheet.get_Cells(),TRUE);
m_range.put_Item(_variant_t(pos.first), _variant_t(pos.second), _variant_t(csNews));
}
CString CExcelBookReadWrite::fnGetCell(const pair<long, long> &pos)
{
m_range.AttachDispatch(m_sheet.get_Cells(), TRUE);
return m_range.get_Item(_variant_t(pos.first), _variant_t(pos.second));
}
pair<long, long> CExcelBookReadWrite::fnGetCellPosition()
{
m_range.AttachDispatch(m_sheet.get_Cells(), TRUE);
return make_pair(m_range.get_Column(), m_range.get_Row());
}
void CExcelBookReadWrite::fnSetLineLength(const pair<long, long> &pos)
{
CRange cell = m_range.get_Item(COleVariant(long(pos.first)), COleVariant(long(pos.second))).pdispVal;
CString cs = fnGetCell(pos);
double dwidth = cs.GetLength()*0.7;
// 将 _variant_t 轉換為 long
_variant_t val = cell.get_ColumnWidth();
val.ChangeType(VT_R8);
double dCurrentWidth = val.dblVal;
// 如果目标長度比目前長度長,則改變長度
if (dwidth > dCurrentWidth)
{
cell.put_ColumnWidth(_variant_t(dwidth));
}
}
void CExcelBookReadWrite::fnSaveBook()
{
//m_book.put_Saved(TRUE); //退出excel.exe必須
// 設定自适應
CRange cols = m_range.get_EntireColumn();
cols.AutoFit();
m_book.Save(); // 儲存book
}
第四步:讀寫excel,略
完整代碼可在連結中下載下傳,一個簡單的excel讀寫MFC應用。
代碼下載下傳位址