MFC中使用excel2007读写excel表格
环境：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" 1234567
第二步：建立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"// 打开指定路径的excelCExcelBookReadWrite::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);
}// 关闭打开的bookCExcelBookReadWrite::~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(); // 保存
第四步：读写excel，略
完整代码可在链接中下载，一个简单的excel读写MFC应用。
赞 (0)