浅谈C/C++ 开发Excel插件之操作excel

想在加载项中访问并操作Excel,需要在stdafx.h中加入如下语句，注意实际路径和本机安装Office的路径相关


#import "C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "D:\\Program Files (x86)\\Microsoft Office\\Office12\\EXCEL.EXE" rename( "DialogBox", "ExcelDialogBox" ) rename( "RGB", "ExcelRGB" ) rename( "CopyFile", "ExcelCopyFile" ) rename( "ReplaceText", "ExcelReplaceText" ) exclude( "IFont", "IPicture" ) no_dual_interfaces

using namespace Excel;

这样我们就获取到了整个excel表的对象，可对其进行任何操作（可将这个对象进行适当的封装）。这里介绍几个最基本的操作：

1.添加sheet，这里我实现了2个基本函数，可实现基本操作，在指定位置添加并根据根据名称改变名称等，详情请参考附件。

void ExcelMgr::AddSheet(_bstr_t name, PositionType type)
{
Excel::_WorkbookPtr pWorkbook = m_pExcel->ActiveWorkbook;
Excel::_WorksheetPtr pActiveSheet = m_pExcel->ActiveSheet;
Excel::_WorksheetPtr pWorksheet;
VARIANT varSheet;
varSheet.vt = VT_DISPATCH;
varSheet.pdispVal = pActiveSheet;
switch (type)
{
case ExcelMgr::eActivePageFront:
pWorksheet = pWorkbook->Worksheets->Add(varSheet, vtMissing, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eActviePageEnd:
pWorksheet = pWorkbook->Worksheets->Add(vtMissing, varSheet, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eStart:
varSheet.pdispVal = m_pExcel->Worksheets->GetItem(1);
pWorksheet = pWorkbook->Worksheets->Add(varSheet, vtMissing, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eEnd:
varSheet.pdispVal = m_pExcel->Worksheets->GetItem(m_pExcel->Worksheets->GetCount());
pWorksheet = pWorkbook->Worksheets->Add(vtMissing, varSheet, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
default:
break;
}
pWorksheet->PutName(SheetName(name));
m_nameUseCounts[name] = m_nameUseCounts[name] + 1;
}


void ExcelMgr::AddSheet(_bstr_t curSheet, _bstr_t name, PositionType type)
{
Excel::_WorkbookPtr pWorkbook = m_pExcel->ActiveWorkbook;
Excel::WorksheetsPtr pWorkSheets = pWorkbook->Worksheets;


VARIANT varStartSheet;
varStartSheet.vt = VT_DISPATCH;
varStartSheet.pdispVal = m_pExcel->ActiveSheet;


Excel::_WorksheetPtr pWorksheet;


for (int sheetCount = 1; sheetCount <= pWorkSheets->GetCount(); ++sheetCount)
{
Excel::_WorksheetPtr workSheet = pWorkSheets->GetItem(sheetCount);
if (workSheet->GetName() == curSheet)
{
varStartSheet.pdispVal = workSheet;
break;
}
}


switch (type)
{
case ExcelMgr::eActivePageFront:
pWorksheet = pWorkbook->Worksheets->Add(varStartSheet, vtMissing, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eActviePageEnd:
pWorksheet = pWorkbook->Worksheets->Add(vtMissing, varStartSheet, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eStart:
varStartSheet.pdispVal = m_pExcel->Worksheets->GetItem(1);
pWorksheet = pWorkbook->Worksheets->Add(varStartSheet, vtMissing, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
case ExcelMgr::eEnd:
varStartSheet.pdispVal = m_pExcel->Worksheets->GetItem(m_pExcel->Worksheets->GetCount());
pWorksheet = pWorkbook->Worksheets->Add(vtMissing, varStartSheet, _variant_t((short)1), XlSheetType::xlWorksheet);
break;
default:
break;
}


pWorksheet->PutName(SheetName(name));
m_nameUseCounts[name] = m_nameUseCounts[name] + 1;
}

2. 添加book

void ExcelMgr::AddWork(_bstr_t name)
{
Excel::_WorkbookPtr pWorkbook = m_pExcel->Workbooks->Add(xlWBATWorksheet);
pWorkbook->PutTitle(name);
}

3. 添加chart

void ExcelMgr::AddChart(_bstr_t name)
{
Excel::_ChartPtr pChart = m_pExcel->Charts->Add();
pChart->PutName(name);
}

4.插入数据

void ExcelMgr::Print()
{
mpSheet = GetSheetByName();
if(nullptr == mpSheet)
{
mpSheet = CreateNewSheet();
}
mpRange = mpSheet->Cells;
mpRange->Clear();
mpRange->PutHorizontalAlignment(_variant_t(-4152));
mpRange->PutNumberFormatLocal(_variant_t("@"));
size_t row = 1;
size_t column = 1;
for(size_t index = 0; index < mpData->GetParser()->DataSize(); ++index)
{


if(IParam::HorizontalType == mpData->GetParser()->GetParam()->GetDataShowType())
{
mpRange->PutItem(row,column,_variant_t(mpData->GetParser()->GetData(index).c_str()));
}
else
{
mpRange->PutItem(column,row,_variant_t(mpData->GetParser()->GetData(index).c_str()));
}
if(column < 1 + mpData->GetParser()->GetParamSize())
{
column++;
}
else
{
row++;
column = 1;
}
}
}