#include "stdafx.h"

#include "ExcelProcess.h"

#include "..\Header\CommonHeader.h"

//为避免在代码中多处对ExcelProcess进行new，将ExcelProcess设计为单例模式

//通过getInstance和destroyInstance获取和销毁对象

ExcelProcess* ExcelProcess::excel = nullptr;

ExcelProcess::ExcelProcess()

{

lpDisp = NULL;

excelVer = 0;

//init();

}

//获取对象

ExcelProcess* ExcelProcess::getInstance()

{

if(excel == NULL)

{

excel = new ExcelProcess();

}

return excel;

}

//销毁对象

void ExcelProcess::destroyInstance()

{

if(excel != NULL)

{

delete excel;

excel = NULL;

}

}

ExcelProcess::~ExcelProcess()

{

try

{

sheet.ReleaseDispatch();

sheets.ReleaseDispatch();

book.ReleaseDispatch();

books.ReleaseDispatch();

ExcelApp.ReleaseDispatch();

ExcelApp.Quit();

//退出伪装的app

if(!ExcelApp_fake.get_ActiveSheet())

{

books_fake.ReleaseDispatch();

ExcelApp_fake.ReleaseDispatch();

ExcelApp_fake.Quit();

}

}

catch (COleDispatchException*)

{

AfxMessageBox(Notice_get_by_id(IDS_POW_OFF_EXCEL_FAIL));

//AfxMessageBox(_T("关闭Excel服务出错。"));

}

}

/************************************************************************/

/* 初始化 */

/************************************************************************/

BOOL ExcelProcess::init()

{

CString strOfficeVer[5] = {_T("office 2003"),_T("office 2007"),_T("office 2010"),_T("office 2013"), _T("office 2016")};

BOOL result = FALSE;

for(int i = 4; i >= 0; i--)

{

if(!createServer(strOfficeVer[i]))

continue;

else

{

result = TRUE;

}

}

if(excelVer == 0)

{

result = FALSE;

}

return result;

}

//获取office版本

int ExcelProcess::getOfficeVer()

{

return excelVer;

}

/************************************************************************/

/* 启动伪Excel服务

/* 因为有些office版本(如2007)的机制是一个EXE进程对应多个Excel文件

/* 如果不启动伪Excel服务，当用户打开其他excel文件时，会对原来的excel文件产生干扰

/* 该伪Excel服务就是提供给用户操作其他excel文件(可能发生)

/************************************************************************/

BOOL ExcelProcess::createFakeServer()

{

CString strOfficeVer[5] = {_T("office 2003"),_T("office 2007"),_T("office 2010"),_T("office 2013"), _T("office 2016")};

for(int i = 4; i >= 0; i--)

{

CString officeVer = strOfficeVer[i];

//去除前后空格

officeVer.Trim();

//获取版本号字符

CString verNum = officeVer.Right(4);

int ver = _ttoi(verNum);

switch(ver)

{

case 2003:

if(judgeExcelVer(11))

{

if(ExcelApp_fake.CreateDispatch(_T("Excel.Application.11"), NULL))

{

ExcelApp_fake.put_DisplayAlerts(FALSE);

books_fake.AttachDispatch(ExcelApp_fake.get_Workbooks());

return TRUE;

}

else

{

return FALSE;

}

}

break;

case 2007:

if(judgeExcelVer(12))

{

if(ExcelApp_fake.CreateDispatch(_T("Excel.Application.12"), NULL))

{

ExcelApp_fake.put_DisplayAlerts(FALSE);

books_fake.AttachDispatch(ExcelApp_fake.get_Workbooks());

return TRUE;

}

else

{

return FALSE;

}

}

break;

case 2010:

if(judgeExcelVer(14))

{

if(ExcelApp_fake.CreateDispatch(_T("Excel.Application.14"), NULL))

{

ExcelApp_fake.put_DisplayAlerts(FALSE);

books_fake.AttachDispatch(ExcelApp_fake.get_Workbooks());

return TRUE;

}

else

{

return FALSE;

}

}

break;

case 2013:

if( judgeExcelVer(15))

{

if(ExcelApp_fake.CreateDispatch(_T("Excel.Application.15"), NULL))

{

ExcelApp_fake.put_DisplayAlerts(FALSE);

books_fake.AttachDispatch(ExcelApp_fake.get_Workbooks());

return TRUE;

}

else

{

return FALSE;

}

}

break;

case 2016:

if( judgeExcelVer(16))

{

if(ExcelApp_fake.CreateDispatch(_T("Excel.Application.16"), NULL))

{

ExcelApp_fake.put_DisplayAlerts(FALSE);

books_fake.AttachDispatch(ExcelApp_fake.get_Workbooks());

return TRUE;

}

else

{

return FALSE;

}

}

break;

}

}

return FALSE;

}

/************************************************************************/

/* 判断Excel版本号 */

/************************************************************************/

BOOL ExcelProcess::judgeExcelVer(int Ver)

{

HKEY hkey;

int ret;

CString str;

LONG len;

str.Format(_T("Excel.Application.%d"),Ver);

str += _T("\\CLSID");

ret = RegCreateKey(HKEY_CLASSES_ROOT, str, &hkey);

if(ret == ERROR_SUCCESS)

{

RegQueryValue(HKEY_CLASSES_ROOT, str, NULL, &len);

//如果注册表中 HKEY_CLASSES_ROOT\Excel.Application.x\CPLSID中的值为空，则读取到'\0'，长度为2

return len == 2 ? FALSE : TRUE;

}

else

{

return FALSE;

}

}

/************************************************************************/

/* 启动Excel服务，传入的字符串参数格式为 office **** */

/************************************************************************/

BOOL ExcelProcess::createServer( CString officeVer )

{

//去除前后空格

officeVer.Trim();

//获取版本号字符

CString verNum = officeVer.Right(4);

int ver = _ttoi(verNum);

switch(ver)

{

case 2003:

if(judgeExcelVer(11))

{

if(ExcelApp.CreateDispatch(_T("Excel.Application.11"), NULL))

{

excelVer = 2003;

}

else

{

return FALSE;

}

}

else

{

return FALSE;

}

break;

case 2007:

if(judgeExcelVer(12))

{

if(ExcelApp.CreateDispatch(_T("Excel.Application.12"), NULL))

{

excelVer = 2007;

}

else

{

return FALSE;

}

}

else

{

return FALSE;

}

break;

case 2010:

if(judgeExcelVer(14))

{

if(ExcelApp.CreateDispatch(_T("Excel.Application.14"), NULL))

{

excelVer = 2010;

}

else

{

return FALSE;

}

}

else

{

return FALSE;

}

break;

case 2013:

if( judgeExcelVer(15))

{

if(ExcelApp.CreateDispatch(_T("Excel.Application.15"), NULL))

{

excelVer = 2013;

}

else

{

return FALSE;

}

}

else

{

return FALSE;

}

break;

case 2016:

if( judgeExcelVer(16))

{

if(ExcelApp.CreateDispatch(_T("Excel.Application.16"), NULL))

{

excelVer = 2016;

}

else

{

return FALSE;

}

}

else

{

return FALSE;

}

break;

}

return TRUE;

}

BOOL ExcelProcess::setExcelExportSheets( CString filePath )

{

// ExcelApp.put_Visible(TRUE);

// ExcelApp.put_UserControl(FALSE);

//获取工作薄集合

books.AttachDispatch(ExcelApp.get_Workbooks());

//打开一个工作簿

try

{

lpDisp = books.Open(filePath,

vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,

vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,

vtMissing, vtMissing, vtMissing, vtMissing);

book.AttachDispatch(lpDisp);

}

catch(...)

{

//新建

lpDisp = books.Add(vtMissing);

book.AttachDispatch(lpDisp);

saveExcelAs(filePath);

}

sheets.AttachDispatch(book.get_Sheets());

return TRUE;

}

//显示excel文件，在配置批处理导出结束后使用

void ExcelProcess::setView()

{

ExcelApp.put_Visible(TRUE);

ExcelApp.put_UserControl(FALSE);

}

BOOL ExcelProcess::openExcelFile( CString filePath )

{

return setExcelImportSheets(filePath);

}

BOOL ExcelProcess::setExcelImportSheets( CString filePath )

{

if (filePath.GetLength() == 0)

return FALSE;

// ExcelApp.put_Visible(TRUE);

// ExcelApp.put_UserControl(FALSE);

//获取工作薄集合

books.AttachDispatch(ExcelApp.get_Workbooks());

//打开一个工作簿

try

{

lpDisp = books.Open(filePath,

vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,

vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,

vtMissing, vtMissing, vtMissing, vtMissing);

book.AttachDispatch(lpDisp);

}

catch(...)

{

return FALSE;

}

//获取工作表集合

sheets.AttachDispatch(book.get_Worksheets());

// //得到当前活跃sheet

// //如果有单元格正处于编辑状态中，此操作不能返回，会一直等待

// lpDisp=book.get_ActiveSheet();

// sheet.AttachDispatch(lpDisp);

return TRUE;

}

//获取一个指定名称的sheet，如果不存则返回false，存在则选中该sheet

BOOL ExcelProcess::getSheet( CString sheetName )

{

sheets.AttachDispatch(book.get_Sheets());

try

{

lpDisp = sheets.get_Item(_variant_t(sheetName));

sheet.AttachDispatch(lpDisp);

}

catch(...)

{

// lpDisp = sheets.Add(vtMissing, vtMissing, _variant_t(1), vtMissing);

// sheet.AttachDispatch(lpDisp);

// sheet.put_Name(sheetName);

return FALSE;

}

return TRUE;

}

//创建sheet

void ExcelProcess::createSheet(CString sheetName)

{

lpDisp = sheets.Add(vtMissing, vtMissing, _variant_t(1), vtMissing);

sheet.AttachDispatch(lpDisp);

sheet.put_Name(sheetName);

}

//获取存在的sheet(不指定名称)

BOOL ExcelProcess::getActiveSheet()

{

//sheets.AttachDispatch(book.get_Sheets());

try

{

lpDisp = book.get_ActiveSheet();

sheet.AttachDispatch(lpDisp);

}

catch(...)

{

return FALSE;

}

return TRUE;

}

//删除sheet

BOOL ExcelProcess::deleteSheet( CString sheetName )

{

try

{

//删除系统默认产生的三个sheet

sheet.AttachDispatch(sheets.get_Item(_variant_t(sheetName)));

sheet.Delete();

}

catch(...)

{

//TODO

return FALSE;

}

return TRUE;

}

/************************************************************************/

/* 设置单元格数据有效性为数字 */

/************************************************************************/

BOOL ExcelProcess::setCellsTypeToNum( CString cellBegin, CString cellEnd, int min, int max, int defValue )

{

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateWholeNumber, _variant_t(XlDVAlertStyle::xlValidAlertStop),

_variant_t(XlFormatConditionOperator::xlBetween), _variant_t(min), _variant_t(max));

range.put_Value2(_variant_t(defValue));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsTypeToNum( CString cellBegin, CString cellEnd, int min, int max )

{

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateWholeNumber, _variant_t(XlDVAlertStyle::xlValidAlertStop),

_variant_t(XlFormatConditionOperator::xlBetween), _variant_t(min), _variant_t(max));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsTypeToNum( CString cellBegin, CString cellEnd, double min, double max )

{

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateDecimal, _variant_t(XlDVAlertStyle::xlValidAlertStop),

_variant_t(XlFormatConditionOperator::xlBetween), _variant_t(min), _variant_t(max));

setCellsAlignLeft(range);

return TRUE;

}

/************************************************************************/

/* 设置单元格数据有效性为字符串数组 */

/************************************************************************/

BOOL ExcelProcess::setCellsToStringList( CString cellBegin, CString cellEnd, const std::vector<CString>& strList, unsigned int defValueIndex /*= 0*/ )

{

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

CString strDef = strList.at(defValueIndex);

CString str;

for(unsigned int i = 0; i < strList.size(); i++)

{

str += strList.at(i);

str += ',';

}

range.put_Value2(_variant_t(strDef));

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween), _variant_t(str), _variant_t(NULL));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsToStringList( CString cellBegin, CString cellEnd,const std::vector<CString>& strList )

{

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

CString str;

for(unsigned int i = 0; i < strList.size(); i++)

{

str += strList.at(i);

str += ',';

}

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween), _variant_t(str), _variant_t(NULL));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsToStringList(CString cellBegin, CString cellEnd, CString sheetName, CString valueCellBegin, CString valueCellEnd)

{

valueCellBegin.Insert(0, _T("$"));

valueCellEnd.Insert(0, _T("$"));

int index = 0;

for(index = 0; index < valueCellBegin.GetLength(); index++)

{

if(valueCellBegin.GetAt(index) >= _T('0') && valueCellBegin.GetAt(index) <= _T('9'))

break;

}

valueCellBegin.Insert(index, _T("$"));

index = 0;

for(index = 0; index < valueCellEnd.GetLength(); index++)

{

if(valueCellEnd.GetAt(index) >= _T('0') && valueCellEnd.GetAt(index) <= _T('9'))

break;

}

valueCellEnd.Insert(index, _T("$"));

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

CString str;

str.Format(_T("=%s!%s:%s"), sheetName, valueCellBegin, valueCellEnd);

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween), _variant_t(str), _variant_t(NULL));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsToStringList(CString cellBegin, CString cellEnd, const CString* strList, unsigned int len, unsigned int defValueIndex)

{

if(strList == nullptr || defValueIndex >= len)

return FALSE;

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

CString str;

CString strDef = strList[defValueIndex];

for(unsigned int i = 0; i < len; i++)

{

str += strList[i];

str += ',';

}

range.put_Value2(_variant_t(strDef));

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween), _variant_t(str), _variant_t(NULL));

setCellsAlignLeft(range);

return TRUE;

}

//重载

BOOL ExcelProcess::setCellsToStringList(CString cellBegin, CString cellEnd, const CString* strList, unsigned int len)

{

if(strList == nullptr)

return FALSE;

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

CString str;

for(unsigned int i = 0; i < len; i++)

{

str += strList[i];

str += ',';

}

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween), _variant_t(str), _variant_t(NULL));

setCellsAlignLeft(range);

return TRUE;

}

//根据Excel公式设置单元格的数据有效性

void ExcelProcess::setCellsToFormula(CString cellBegin, CString cellEnd, CString formulaName)

{

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

LPDISPATCH result = range.get_Validation();

validation.AttachDispatch(result);

validation.Delete();

validation.Add(XlDVType::xlValidateList, _variant_t(XlDVAlertStyle::xlValidAlertStop), _variant_t(XlFormatConditionOperator::xlBetween),

_variant_t(formulaName), vtMissing);

setCellsAlignLeft(range);

}

/************************************************************************/

/* 设置单元格颜色，颜色索引见 Excel颜色对照表.doc */

/************************************************************************/

BOOL ExcelProcess::setCellsColor(CString cellBegin, CString cellEnd, int colorIndex)

{

lpDisp = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

range.AttachDispatch(lpDisp);

LPDISPATCH result = range.get_Interior();

interior.AttachDispatch(result);

interior.put_ColorIndex(_variant_t(colorIndex));

return TRUE;

}

/************************************************************************/

/* 设置一片单元格的值

/* cellBegin 起点坐标

/* 范围的长宽由nums决定

/************************************************************************/

BOOL ExcelProcess::setCellsValue( CString cellBegin, vector<vector<CString>>& nums )

{

COleSafeArray safeArr;

//第一、二行为标题，从第三行开始去获取列数

//DWORD numElements[] = {nums.size(), nums.size() > 1 ? nums.at(nums.size() - 1).size() : nums.at(0).size()};

DWORD numElements[] = {nums.size(), nums.at(0).size()};

//创建二维数组

safeArr.Create(VT_BSTR , 2, numElements);

CString cellEnd = getEndCell(cellBegin, nums.size(), nums.at(0).size());

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

//遍历

for(ULONG i = lFirstLBound; i < nums.size(); i++)

{

index[0] = i;

for(ULONG j = lSecondLBound; j < nums.at(i).size(); j++)

{

index[1] = j;

//CString lElement = *((CString*)nums + cols*i + j);

//CString lElement = nums[i][j];

CString lElement = nums.at(i).at(j);

BSTR strTmp = lElement.AllocSysString();

safeArr.PutElement(index, strTmp);

SysFreeString(strTmp);

}

}

range.put_Value2((VARIANT) safeArr);

setCellsAlignLeft(range);

return TRUE;

}

//重载，主要用于设置序号

BOOL ExcelProcess::setCellsValue( CString cellBegin, vector<int>& nums )

{

COleSafeArray safeArr;

DWORD numElements[] = {nums.size(), 1};

//创建二维数组

safeArr.Create(VT_I4 , 2, numElements);

//只有一列数据

CString cellEnd = getEndCell(cellBegin, nums.size(), 1);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

//遍历

for(ULONG i = lFirstLBound; i < nums.size(); i++)

{

index[0] = i;

index[1] = lSecondLBound;

int val = nums.at(i);

safeArr.PutElement(index, &val);

}

range.put_Value2((VARIANT) safeArr);

setCellsAlignLeft(range);

setCellsBold(cellBegin, cellEnd, TRUE);

return TRUE;

}

//重载 暂时不用

BOOL ExcelProcess::setCellsValue( CString cellBegin, long** nums, int rows, int cols )

{

COleSafeArray safeArr;

DWORD numElements[] = {rows, cols};

//创建二维数组

safeArr.Create(VT_I4 , 2, numElements);

CString cellEnd = getEndCell(cellBegin, rows, cols);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

//遍历

for(long i = lFirstLBound; i <= lFirstUBound; i++)

{

index[0] = i;

for(long j = lSecondLBound; j <= lSecondUBound; j++)

{

index[1] = j;

long lElement = *((long*)nums + cols*i + j);

safeArr.PutElement(index, &lElement);

}

}

/*VARIANT varWrite = (VARIANT) safeArr;*/

range.put_Value2((VARIANT) safeArr);

setCellsAlignLeft(range);

return TRUE;

}

//重载 暂时不用

BOOL ExcelProcess::setCellsValue( CString cellBegin, CString** nums, int rows, int cols )

{

COleSafeArray safeArr;

DWORD numElements[] = {rows, cols};

//创建二维数组

safeArr.Create(VT_BSTR , 2, numElements);

CString cellEnd = getEndCell(cellBegin, rows, cols);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

//遍历

for(long i = lFirstLBound; i <= lFirstUBound; i++)

{

index[0] = i;

for(long j = lSecondLBound; j <= lSecondUBound; j++)

{

index[1] = j;

//CString lElement = *((CString*)nums + cols*i + j);

CString lElement = nums[i][j];

BSTR strTmp = lElement.AllocSysString();

safeArr.PutElement(index, strTmp);

SysFreeString(strTmp);

}

}

range.put_Value2((VARIANT) safeArr);

setCellsAlignLeft(range);

return TRUE;

}

//读取一列数据

//cellBegin 起点坐标

//count 数量

void ExcelProcess::getColValue(vector<CString>& outData, CString cellBegin, int count)

{

CString cellEnd = getEndCell(cellBegin, count, 1);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

VARIANT data = range.get_Value2();

COleSafeArray safeArr(data);

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

ASSERT(lSecondLBound == 1);

ASSERT(lSecondUBound == 1);

VARIANT value;

for(long i = lFirstLBound; i <= lFirstUBound; i++)

{

index[0] = i;

index[1] = 1;

safeArr.GetElement(index, &value);

CString valueStr;

switch (value.vt)

{

case VT_BOOL:

{

BOOL t = value.boolVal;

valueStr = t ? _T("TRUE") : _T("FALSE");

break;

}

case VT_BSTR:

{

valueStr = value.bstrVal;

break;

}

case VT_I4:

{

int t = value.intVal;

valueStr.Format(_T("%d"), t);

break;

}

case VT_R8:

{

double t = value.dblVal;

valueStr.Format(_T("%lf"), t);

break;

}

default:

break;

}

outData.push_back(valueStr);

}

}

//设置一列数据

BOOL ExcelProcess::setColValue( CString cellBegin, const vector<CString>& nums )

{

COleSafeArray safeArr;

DWORD numElements[] = {nums.size(), 1};

//创建二维数组

safeArr.Create(VT_BSTR , 2, numElements);

CString cellEnd = getEndCell(cellBegin, nums.size(), 1);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

long index[2] = {0, 0};

long lFirstLBound = 0;

long lFirstUBound = 0;

long lSecondLBound = 0;

long lSecondUBound = 0;

//获取行的下限

safeArr.GetLBound(1, &lFirstLBound);

//获取行的上限

safeArr.GetUBound(1, &lFirstUBound);

//获取列的下限

safeArr.GetLBound(2, &lSecondLBound);

//获取列的上限

safeArr.GetUBound(2, &lSecondUBound);

//遍历

for(ULONG i = lFirstLBound; i < nums.size(); i++)

{

index[0] = i;

index[1] = 0;

CString lElement = nums.at(i);

BSTR strTmp = lElement.AllocSysString();

safeArr.PutElement(index, strTmp);

SysFreeString(strTmp);

}

range.put_Value2((VARIANT) safeArr);

//setCellsAlignLeft(range);

return TRUE;

}

//最多支持开头是两个字母

//传入起点坐标，行数和列数，返回结束点坐标

//如传入 A1,2,1 返回 A2

//如传入 A1,1,2 返回 B2

CString ExcelProcess::getEndCell( CString cellBegin, int rows, int cols )

{

BOOL hasTwoChar = FALSE;

TCHAR beginChar = cellBegin[0];

if(!((beginChar >= 'A' && beginChar <= 'Z')||(beginChar >= 'a' && beginChar <= 'z')))

{

//AfxMessageBox(_T("输入起始点数据错误"));

return CString("");

}

beginChar = cellBegin[1];

{

if(beginChar >= '0' && beginChar <= '9')

beginChar = cellBegin[0];

else

{

beginChar = cellBegin[1];

hasTwoChar = TRUE;

if(!((beginChar >= 'A' && beginChar <= 'Z')||(beginChar >= 'a' && beginChar <= 'z')))

{

//AfxMessageBox(_T("输入起始点数据错误"));

return CString("");

}

}

}

if(cols <= 0)

{

//AfxMessageBox(_T("输入的列数需要大于0"));

return CString("");

}

CString numStr;

if(!hasTwoChar)

numStr = cellBegin.Mid(1, cellBegin.GetLength() - 1);

else

numStr = cellBegin.Mid(2, cellBegin.GetLength() - 1);

int beginRow = _ttol(numStr);

int endRow = beginRow + rows - 1;

TCHAR endChar = beginChar + cols - 1;

if(beginChar + cols - 1 > _T('Z'))

{

if(!hasTwoChar)

{

int index = (beginChar + cols - 1)% _T('Z');

CString a;

a.Format(_T("%c%c"),_T('A'), (_T('A') + index - 1));

CString tmp1;

tmp1.Format(_T("%s%d"), a, endRow);

return tmp1;

}

else

{

TCHAR tmpchar = cellBegin[0] + 1;

int index = (beginChar + cols - 1)% _T('Z');

CString a;

a.Format(_T("%c%c"), tmpchar, (_T('A') + index - 1));

CString tmp1;

tmp1.Format(_T("%s%d"), a, endRow);

return tmp1;

}

}

CString tmp;

if(!hasTwoChar)

tmp.Format(_T("%c%d"), endChar, endRow);

else

tmp.Format(_T("%c%c%d"), cellBegin[0], endChar, endRow);

return tmp;

}

//获取一片范围的值

//cellBegin 起点坐标

//rows 行数

//cols 列数

void ExcelProcess::getValue( vector<vector<CString>>& outData, CString cellBegin, int rows, int cols)

{

CString cellEnd = getEndCell(cellBegin, rows, cols);

range = sheet.get_Range(_variant_t(cellBegin), _variant_t(cellEnd));

VARIANT data = range.get_Value2();

if(rows == 1 && cols == 1)

{

vector<CString> tmp;

tmp.push_back(data.bstrVal);

outData.push_back(tmp);

return;

}

COleSafeArray safeArr(data);

// COleSafeArray safeArr;

// DWORD numElements[] = {rows, cols};

// safeArr.Create(VT_BSTR , 2, numElements);