extern "C" BOOL KTAPI KTExcelStatus() { Excel::_ApplicationPtr app; if ( FAILED(app.CreateInstance( _T("Excel.Application")))) { return FALSE; } app->Quit( ); return TRUE; }
void ExcelControllerImpl08::QuitApplication() { try { Excel::_ApplicationPtr spApplication = GetApplication(); if (spApplication != 0) { LCID lcid = LocaleHelper::GetLocaleIDForInstalledExcel(spApplication); spApplication->DisplayAlerts[lcid] = VARIANT_FALSE; spApplication->Quit(); } } catch(_com_error& ex) { LOG_WS_INFO(ex.ErrorMessage()); } catch(...) { unexpected(); } }
void CreateExcelSpreadsheet(std::map<std::wstring, std::wstring> &mapProperties) { CoInitialize(nullptr); auto aShutdown = std::experimental::make_scope_exit([]() ->void { CoUninitialize(); }); // // Make sure Excel is present... CLSID clsid; HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid); if(FAILED(hr)) { MessageBoxW(HWND_TOP, L"Unable to find Excel.Application's CLSID.", L"Excel unavailable...", MB_TOPMOST | MB_ICONERROR); return; } Excel::_ApplicationPtr pXLApp; hr = pXLApp.CreateInstance(__uuidof(Excel::Application)); if(FAILED(hr)) { MessageBoxW(HWND_TOP, L"Unable to connect with Excel.", L"Excel unavailable...", MB_TOPMOST | MB_ICONERROR); return; } std::vector<std::wstring> vwstrInputFiles; if(false == ObtainInputFiles(vwstrInputFiles)) { return; } // // Sort input files by nozzle diameter SortInputFiles(vwstrInputFiles); // Make Excel invisible. (i.e. Application.Visible = 0) pXLApp->Visible[0] = VARIANT_TRUE; // Create a new Excel Workbook. (i.e. Application.Workbooks.Add) Excel::WorkbooksPtr pXLBooks = pXLApp->Workbooks; _ASSERT(pXLBooks); if(nullptr == pXLBooks) { return; } Excel::_WorkbookPtr pXLBook = pXLBooks->Add(); _ASSERT(pXLBook); if(nullptr == pXLBook) { return; } pXLApp->ActiveWindow->PutWindowState(Excel::xlMaximized); std::vector<std::wstring> vTabnames; bool bFirst = true; for(auto wstrFile : vwstrInputFiles) { std::array<wchar_t, 1024> wzFilename; _wsplitpath_s(wstrFile.c_str(), nullptr, 0, nullptr, 0, &wzFilename[0], wzFilename.size(), nullptr, 0); boost::property_tree::ptree tree; std::filebuf file; if(false == file.open(wstrFile.c_str(), std::ios::in)) { continue; } std::istream inputStream(&file); boost::property_tree::xml_parser::read_xml(inputStream, tree); std::string strPropellantName(tree.get("Document.MotorData.Propellant", "-")); printf("NAME: %hs\n", strPropellantName.c_str()); const std::string strMetric(tree.get("Document.MotorData.Metric", "False")); bool bMetric = (strMetric == "True"); double dSelectedAvgStartTime = tree.get("Document.MotorData.SelectedAvgStartTime", 0.0f); double dSelectedAvgEndTime = tree.get("Document.MotorData.SelectedAvgEndTime", 0.0f); auto motorData = tree.get_child("Document.MotorData"); std::vector<std::tuple<double, double, double, double, double> > vReadings; double dLastTime = dSelectedAvgStartTime; for(auto data : motorData) { if(data.first != "Item") { continue; } double dTime = data.second.get<double>("Time", -1.0f); double dThrust = data.second.get<double>("Thrust", -1.0f); double dPressure = data.second.get<double>("Pressure", -1.0f); // // Skip readings that were "selected" out in the TC Logger program // Useful for example to skip over an ignition spike... if(dSelectedAvgEndTime > 0.0f) { if(dTime < dSelectedAvgStartTime || dTime > dSelectedAvgEndTime) { continue; } } double dTimeDelta = dTime - dLastTime; dLastTime = dTime; double dThrustElementN = bMetric ? (dTimeDelta * dThrust) : (dTimeDelta * (dThrust * g_dNewtonsPerPound)); vReadings.push_back(std::make_tuple(dTime, dThrust, dPressure, dTimeDelta, dThrustElementN)); } if(bFirst) { bFirst = false; } else { // // Create a new worksheet Excel::SheetsPtr pWorksheets = pXLBook->Worksheets; pWorksheets->Add(); // Becomes the active sheet... } // // Generate a workbook with data and a chart marking the 10% line, etc. // If successful and there are at least 3 files we will generate a sheet with a & n and average ISP std::wstring sanitizedFilename(&wzFilename[0]); SanitizeStringName(sanitizedFilename); if(true == ProduceExcelWorkbook(pXLBook, sanitizedFilename, strPropellantName, bMetric, tree, vReadings, mapProperties)) { vTabnames.push_back(sanitizedFilename); } } // // If we have three or more sets of data we can generate an a & n: if(vTabnames.size() > 2) { Excel::SheetsPtr pWorksheets = pXLBook->Worksheets; pWorksheets->Add(); // Becomes the active sheet... ProduceAandNTab(pXLBook, vTabnames); } return; }
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; }
UINT CNav_ExportCustomsDlg::ExportData(LPVOID p) { HRESULT hRes; Excel::_ApplicationPtr appExcel; hRes = appExcel.CreateInstance( _T("Excel.Application")); CNav_ExportCustomsDlg *Dialog; Dialog = (CNav_ExportCustomsDlg*)p; if(Dialog != NULL) { CString sStart, sEnd; Dialog->m_EndDate.GetWindowText(sEnd); Dialog->m_StartDate.GetWindowText(sStart); CString sGTD; Dialog->m_EdGTDNUMBER.GetWindowText(sGTD); COleDateTime datStart,datEnd, cDate; datStart.ParseDateTime(sStart); datEnd.ParseDateTime(sEnd); CString sConnect; CString sServer, sDatabase; sServer = sReadFromIni(_T("DB"),_T("SERVER"),_T("svbyminssq3")); //sWriteToIni(_T("DB"),_T("SERVER"),sServer); sDatabase = sReadFromIni(_T("DB"),_T("DATABASE"),_T("SHATE-M-8")); //sWriteToIni(_T("DB"),_T("DATABASE"),sDatabase); Excel::WorkbooksPtr ExcelBooks; Excel::_WorkbookPtr ExcelBook; Excel::_WorksheetPtr ExcelSheet; Excel::RangePtr range; VARIANT bTRUE; bTRUE.vt = 11; bTRUE.boolVal = TRUE; appExcel->Visible[0] = FALSE; ExcelBook= appExcel->Workbooks->Add(); ExcelSheet = ExcelBook->Worksheets->Item[1]; sConnect.Format(_T("DRIVER=SQL Server;SERVER=%s;UID=;WSID=%s;Trusted_Connection=Yes;DATABASE=%s;LANGUAGE=русский"),sServer,GetWinUserName(),sDatabase); CDatabase* dBase; dBase = NULL; try { dBase = new(CDatabase); dBase->SetQueryTimeout(600); dBase->OpenEx(sConnect,CDatabase::noOdbcDialog); sConnect.Format(_T("EXEC [sp_setapprole] '%s', '%s', 'none', 0, 0"),_T("$ndo$shadow"),_T("FF5EC4E40F67BD4EDF3D04F8B84364DAD0")); dBase->ExecuteSQL(sConnect); } catch(CDBException *exsept) { appExcel->Visible[0] = TRUE; Dialog->m_stState.SetWindowTextW(exsept->m_strError); exsept->Delete(); if(dBase != NULL) { if(dBase->IsOpen()) { dBase->Close(); } delete(dBase); } dBase = NULL; Dialog->m_BtOK.ShowWindow(1); Dialog->SecondThread = NULL; return 0; } CString sSQL; try { CRecordset Query(dBase); int iField; CDBVariant dbValue; Dialog->m_stState.SetWindowTextW(_T("Формирование")); sGTD = sGTD + _T("'"); sSQL = _T("select distinct SIH.[Posting Date],(SIL.[TTN Series]+SIL.[TTN Number]) AS TTN,SIL.[Item No_ 2], SIH.[Bill-to Name] "); sSQL = sSQL + _T(" from [")+sDatabase; sSQL = sSQL + _T("$Sales Invoice Header] as SIH join ["); sSQL = sSQL + sDatabase; sSQL = sSQL + _T("$Sales Invoice Line] as SIL on SIL.[Document No_] = SIH.[No_] and SIL.[No_] is not null and SIL.[No_] <> '' and SIL.[TTN Series] <> '' and SIL.[TTN Number] <> '' join ["); sSQL = sSQL + sDatabase; sSQL = sSQL + _T("$Custom Declaration Relation] as CDR on CDR.[Item No_] = SIL.[No_] and CDR.[Document Type] = 5 and CDR.[CD No_] = '") + sGTD; sSQL = sSQL + _T(" where [Sales Process Type Code] = 'Б/Н_ДОСТАВКА' and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) >= '")+ datStart.Format(_T("%Y%m%d"))+_T("'"); sSQL = sSQL + _T(" and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) <= '")+ datEnd.Format(_T("%Y%m%d"))+_T("'"); //sSQL = sSQL + _T(" and CDR.[CD No_] = '") + sGTD; CString sDat; int iRow; iRow = 1; sDat = _T("Дата учета продажи"); ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString(); sDat = _T("Номер ТТН, ТН"); ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString(); sDat = _T("Код проданного товара (код товара2)"); ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString(); sDat = _T("Клиент"); ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString(); Query.Open(CRecordset::snapshot,sSQL, CRecordset::readOnly); while(!Query.IsEOF()) { iRow++; if((iRow -2) % 100 == 0) { sSQL.Format(_T("Обработанно %d"),iRow-2); Dialog->m_stState.SetWindowTextW(sSQL); } iField = 0; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString(); iField = 1; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString(); iField = 2; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString(); iField = 3; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString(); Query.MoveNext(); } Query.Close(); } catch(CDBException *exsept) { /*appExcel->Visible[0] = TRUE; appExcel = NULL;*/ appExcel->Visible[0] = TRUE; Dialog->m_stState.SetWindowTextW(exsept->m_strError); Dialog->m_EdError.SetWindowTextW(sSQL); exsept->Delete(); if(dBase != NULL) { if(dBase->IsOpen()) { dBase->Close(); } delete(dBase); } Dialog->m_BtOK.ShowWindow(1); Dialog->SecondThread = NULL; dBase = NULL; return 0; } Dialog->m_stState.SetWindowTextW(_T("Выполненно")); Dialog->m_BtOK.ShowWindow(1); } appExcel->Visible[0] = TRUE; Dialog->SecondThread = NULL; return 1; }