extern "C" BOOL LoadExcelFile(const TCHAR* filename)
{
// Load the Excel application in the background.
Excel::_ApplicationPtr pApplication;
if ( FAILED( pApplication.CreateInstance( _T("Excel.Application") ) ) )
{
Errorf( _T("Failed to initialize Excel::_Application!") );
return FALSE;
}
_variant_t varOption( (long) DISP_E_PARAMNOTFOUND, VT_ERROR );
_ftprintf(stdout, _T("%s\n"), filename);
if (!pApplication || !pApplication->Workbooks) {
Errorf(_T("Workbooks is empty\n"));
return FALSE;
}
Excel::_WorkbookPtr pBook = pApplication->Workbooks->Open(filename);//, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption );
if ( pBook == NULL )
{
Errorf( _T("Failed to open Excel file!") );
return FALSE;
}
pApplication->PutVisible(0, FALSE);
Excel::_WorksheetPtr pSheet = pBook->Sheets->Item[1];
if ( pSheet == NULL )
{
Errorf( _T("Failed to get first Worksheet!") );
return FALSE;
}
Excel::RangePtr pRange = pSheet->Cells;
for (int i = 24; i < 29; ++i) {
for (int j = 3; j < 27; ++j) {
pRange->Item[i][j] = j / 10 + i / 100.f;
}
}
#if 0
fprintf(stdout, "2\n");
// Load the column headers.
Excel::RangePtr pRange = pSheet->GetRange( _bstr_t( _T("A1") ), _bstr_t( _T("Z1" ) ) );
if ( pRange == NULL )
{
Errorf( _T("Failed to get header cell range( A1:Z1 )!") );
return FALSE;
}
int iColumns = 0;
for ( int iColumn = 1; iColumn < 26; ++iColumn )
{
_variant_t vItem = pRange->Item[ 1 ][ iColumn ];
_bstr_t bstrText( vItem );
if ( bstrText.length( ) == 0 )
break;
//m_list.AddColumn( bstrText, iColumns++ );
//_ftprintf(stdout, _T("%s\n"), bstrText);
Errorf(bstrText);
}
// Load the rows (up to the first blank one).
pRange = pSheet->GetRange( _bstr_t( _T("A2") ), _bstr_t( _T("Z16384" ) ) );
for ( int iRow = 1; ; ++iRow )
{
int iColumn;
for ( iColumn = 1; iColumn <= iColumns; ++iColumn )
{
_variant_t vItem = pRange->Item[ iRow ][ iColumn ];
_bstr_t bstrText( vItem );
if ( bstrText.length( ) == 0 )
break;
if ( iColumn == 1 )
;//m_list.AddItem( iRow - 1, 0, bstrText );
else
;//m_list.SetItemText( iRow - 1, iColumn - 1, bstrText );
}
if ( iColumn == 1 )
break;
}
pRange->Item[ 1 ][ 1 ] = 1234;
#endif
// Make it all look pretty.
//for ( int iColumn = 1; iColumn <= iColumns; ++iColumn )
//m_list.SetColumnWidth( iColumn, LVSCW_AUTOSIZE_USEHEADER );
// Switch off alert prompting to save as
pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );
// Save the values in book.xml
pSheet->SaveAs(_T("d:\\tesstbook.xls"));
pApplication->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );
// Don't save any inadvertant changes to the .xls file.
pBook->Close( VARIANT_FALSE );
// And switch back on again...
// Need to quit, otherwise Excel remains active and locks the .xls file.
pApplication->Quit( );
return TRUE;
}
DWORD WINAPI AutomateExcelByImport(LPVOID lpParam)
{
// 在当前线程上初始化COM类库并通过调用CoInitializeEx或CoInitialize方法确认
// 并发模型为单线程单元(STA)。
// [-或者-] CoInitialize(NULL);
// [-或者-] CoCreateInstance(NULL);
CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);
try
{
/////////////////////////////////////////////////////////////////////
// 使用#import指令和智能指针创建Excel.Application COM对象
// 选择1) 使用智能指针构造函数创建对象。
// _ApplicationPtr 为原接口名称, _Application加上"Ptr"后缀。
// Excel::_ApplicationPtr spXlApp(
// __uuidof(Excel::Application) // 组件的CLSID
// );
// [-或者-]
// 选择2) 使用智能指针函数CreateInstance创建对象
Excel::_ApplicationPtr spXlApp;
HRESULT hr = spXlApp.CreateInstance(__uuidof(Excel::Application));
if (FAILED(hr))
{
wprintf(L"CreateInstance failed w/err 0x%08lx\n", hr);
return 1;
}
_putws(L"Excel.Application is started");
/////////////////////////////////////////////////////////////////////
// 令Excel不可见(如： Application.Visible = 0)
spXlApp->Visible[0] = VARIANT_FALSE;
/////////////////////////////////////////////////////////////////////
// 创建一个新的工作簿 (如： Application.Workbooks.Add)
Excel::WorkbooksPtr spXlBooks = spXlApp->Workbooks;
Excel::_WorkbookPtr spXlBook = spXlBooks->Add();
_putws(L"A new workbook is created");
/////////////////////////////////////////////////////////////////////
// 获取处于活动状态的工作表并为其设置名称
Excel::_WorksheetPtr spXlSheet = spXlBook->ActiveSheet;
spXlSheet->Name = _bstr_t(L"Report");
_putws(L"The active worksheet is renamed as Report");
/////////////////////////////////////////////////////////////////////
// 填充数据到工作表单元
_putws(L"Filling data into the worksheet ...");
// 为用户姓名构建一个5*2的安全数组
VARIANT saNames;
saNames.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = 5;
sab[1].lLbound = 1; sab[1].cElements = 2;
saNames.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
SafeArrayPutName(saNames.parray, 1, L"John", L"Smith");
SafeArrayPutName(saNames.parray, 2, L"Tom", L"Brown");
SafeArrayPutName(saNames.parray, 3, L"Sue", L"Thomas");
SafeArrayPutName(saNames.parray, 4, L"Jane", L"Jones");
SafeArrayPutName(saNames.parray, 5, L"Adam", L"Johnson");
}
// 将数组值填充到A2:B6区域(姓与名)
// 获取区域A2:B6的Range对象
param.vt = VT_BSTR;
param.bstrVal = SysAllocString(L"A2:B6");
Excel::RangePtr spXlRange = spXlSheet->Range[param];
spXlRange->Value2 = saNames;
// 清除数组
VariantClear(&saNames);
/////////////////////////////////////////////////////////////////////
// 将工作簿保存为xlsx文件并关闭
_putws(L"Save and close the workbook");
// 确定文件名
// 获取当前exe的目录
wchar_t szFileName[MAX_PATH];
if (!GetModuleDirectory(szFileName, ARRAYSIZE(szFileName)))
{
_putws(L"GetModuleDirectory failed");
return 1;
}
// 将"Sample1.xlsx" 合并到目录
wcsncat_s(szFileName, ARRAYSIZE(szFileName), L"Sample1.xlsx", 12);
// 将空终止字符串转换为BSTR
variant_t vtFileName(szFileName);
spXlBook->SaveAs(vtFileName, Excel::xlOpenXMLWorkbook, vtMissing,
vtMissing, vtMissing, vtMissing, Excel::xlNoChange);
spXlBook->Close();
/////////////////////////////////////////////////////////////////////
// 退出Excel应用程序(如：Application.Quit)
_putws(L"Quit the Excel application");
spXlApp->Quit();
/////////////////////////////////////////////////////////////////////
// 释放COM对象
//
// 为智能指针释放不必要的引用
// ...
// spXlApp.Release();
// ...
}
catch (_com_error &err)
{
wprintf(L"Excel throws the error: %s\n", err.ErrorMessage());
wprintf(L"Description: %s\n", (LPCWSTR) err.Description());
}
// 为该线程撤销COM
CoUninitialize();
return 0;
}