CMWDiscovery::CMWDiscovery(IDispatch* pDispatch) : m_hwnd(0), m_pReportManager(NULL), m_pDocDispatch(pDispatch), m_bHasPassword(false) { Excel::_WorkbookPtr spWorkBook; spWorkBook = m_pDocDispatch; if(spWorkBook) { m_officeType = OfficeApplicationType::Excel; m_name = PathFindFileNameW(spWorkBook->FullName); m_bHasPassword = spWorkBook->GetHasPassword() == VARIANT_TRUE; return; } PowerPoint::_PresentationPtr spPresentation = pDispatch; if(spPresentation) { m_officeType = OfficeApplicationType::PowerPoint; PowerPoint::_ApplicationPtr pPptApp = spPresentation->Application; // hopefully PowerPoint 14 will give a new way to get the App HWND? #pragma warning (push) #pragma warning (disable : 4312) m_hwnd = reinterpret_cast<HWND>(pPptApp->GetHWND()); #pragma warning (pop) return; } Word::_DocumentPtr spDocument; spDocument = m_pDocDispatch; if(spDocument) { m_officeType = OfficeApplicationType::Word; } }
void ExcelControllerImpl08::DoSaveDocument(IDispatchPtr spDocument) { if (spDocument == 0) throw Workshare::Com::ComException(_T("The excel document instance was null"), E_INVALIDARG); Excel::_WorkbookPtr pExcelDocument = spDocument; pExcelDocument->Save(); }
IDispatchPtr ExcelControllerImpl08::DoOpenDocument(const CStdString& sDocumentPath, bool bReadOnly) { if(sDocumentPath.IsEmpty()) throw Workshare::ArgumentException(_T("sDocumentPath"), _T("An empty filename is not allowed.")); if(!::PathFileExists(sDocumentPath)) throw Workshare::System::IO::FileNotFoundException(sDocumentPath, _T("Expected an existing excel workbook filename to open.")); //TODO: (EW/WS) Refer to CloseDocument for a description of a latent defect which may be caused by the following line. SetScreenUpdating(false); Excel::_WorkbookPtr spDocument = FindOpenDocument(sDocumentPath); if(spDocument) { if (!bReadOnly) { if (spDocument->ReadOnly) throw Workshare::Com::ComException(_T("Failed to open a READ-ONLY document for write access."), E_FAIL); if (!spDocument->Saved) spDocument->Save(); } } else { Excel::_ApplicationPtr spApplication = GetApplication(); spApplication->EnableEvents = VARIANT_FALSE; //The events are on by default, so no need to store the original value LONG_PTR nNewDocumentCount = spApplication->Workbooks->Count + 1; try { _variant_t vtReadOnly = bReadOnly; _variant_t vtUpdateLinks(false); spDocument = spApplication->Workbooks->Open(sDocumentPath.c_str(), vtUpdateLinks, vtReadOnly); WaitForWorkbookOpened(sDocumentPath); } catch (const Workshare::Com::ComException&) { if(nNewDocumentCount != spApplication->Workbooks->Count) throw; _variant_t vtCount(static_cast<long>(nNewDocumentCount)); spDocument = spApplication->Workbooks->GetItem(&vtCount); } } DisableUnwantedOptions(); spDocument->put_Saved(LocaleHelper::GetLocaleIDForInstalledExcel(spDocument), VARIANT_TRUE); return spDocument; }
void ExcelControllerImpl08::DoSaveDocumentAs(IDispatchPtr spDocument, const CStdString& sNewFileName, MetawallBackEnd::wsFileFormat eFormat) { if (spDocument == 0) throw Workshare::Com::ComException(_T("The word document instance was null"), E_INVALIDARG); if (MetawallBackEnd::WSEXCELDOCUMENT != eFormat) throw Workshare::Com::ComException(_T("The format specified for SaveDocumentAs was invalid."), E_INVALIDARG); Excel::_WorkbookPtr pExcelDocument = spDocument; _variant_t vtFileName(sNewFileName.c_str()); _variant_t vtFormat((long)(Excel::xlWorkbookNormal)); _variant_t vtFalse(VARIANT_FALSE); _variant_t vtEmpty(_T("")); HRESULT hr = pExcelDocument->SaveAs(&vtFileName, &vtFormat, &vtEmpty, &vtEmpty, &vtFalse, &vtFalse, Excel::xlNoChange); if (FAILED(hr)) throw Workshare::Com::ComException(_T("Failed to save the workbook with the following file name:") + sNewFileName, hr); }
void PublishExcelToPDFWithWorkBook(Excel::_WorkbookPtr spWorkbook, const CStdString& sOutputFile, int from, int to) { LOG_WS_FUNCTION_SCOPE(); CStdString restrictionLevel = c_sNoRestriction; CStdString classification = c_sNoClassification; try { _variant_t vSaved = spWorkbook->Saved; restrictionLevel = GetRestrictionLevel(spWorkbook); classification = GetClassification(spWorkbook); // TODO: Switch off Excel's attempt to match the printer's paper size // by temporarily setting spWorkbook->Application->MapPaperSize = VARIANT_FALSE. // But that option isn't available in the Excel 2000 API that we're using. bool bIsNotExcel97 = (atof(spWorkbook->Application->Version) > 8.0); if( !PDFConverterController::UseExcelPrintArea() ) { for (int iIndex = 1; iIndex <= spWorkbook->Worksheets->Count; iIndex++) { Excel::_WorksheetPtr spWorksheet = spWorkbook->Worksheets->Item[ iIndex ]; spWorksheet->PageSetup->PrintArea = _T(""); } } PDFConverterController pdfConverter; pdfConverter.InitialisePDFConverterUseFileName(sOutputFile, VARIANT_TRUE); _variant_t vtPrintToFile(false); _variant_t vtActivePrinter(c_sPDFDriverName); _variant_t vtOutputFile(sOutputFile); _variant_t vtFrom(vtMissing); _variant_t vtTo(vtMissing); if (from != 0 || to != 0) { vtFrom = from; vtTo = to; } if(bIsNotExcel97) { //60.18: This msg loop is to prevent the 'known' issue with Workshare events from crashing. DoEvents(); spWorkbook->PrintOut(&vtFrom, &vtTo, &vtMissing, &vtMissing, &vtActivePrinter, &vtPrintToFile, &vtMissing, &vtOutputFile); } else { if (::PathFileExists(sOutputFile) && !::DeleteFile(sOutputFile)) { CStdString sMsg; sMsg.Format(_T("Failed to overwrite file %s"), sOutputFile.c_str()); throw Workshare::System::IO::IOException(sMsg); } DismissPrintDialog(sOutputFile); _variant_t vtResult; IDispatchPtr spDispDocument = spWorkbook; HRESULT hr = CCOMDispatchHelper::AutoWrap(DISPATCH_METHOD, &vtResult, spDispDocument, L"PrintOut", 7, vtMissing, vtPrintToFile, vtActivePrinter, vtMissing, vtMissing, vtMissing, vtMissing); if (FAILED(hr)) throw Workshare::Com::ComException(_T("Failed to invoke PrintOut method"), hr, spDispDocument); } pdfConverter.UnInitialisePDFConverter(); // Setting the following property fixes an issue in the Office - iManage integration, // in which iManage appears to query the Saved flag on Close and pops up a dialog, // even though we have specified to Close with no Save: LCID lCid = LocaleHelper::GetLocaleIDForInstalledExcel(spWorkbook); spWorkbook->put_Saved(lCid, vSaved); if(c_sNoRestriction != restrictionLevel) SetPdfRestrictionLevel(sOutputFile, restrictionLevel); if (c_sNoClassification != classification) SetPdfClassification(sOutputFile, classification); } catch(const Workshare::Exception&) { throw; } catch(...) { unexpected(); } }
void ProduceAandNTab(Excel::_WorkbookPtr &pXLBook, const std::vector<std::wstring> &vTabs) { // Get the active Worksheet and set its name. Excel::_WorksheetPtr pXLSheet = pXLBook->ActiveSheet; _ASSERT(pXLSheet); if(nullptr == pXLSheet) { return; } pXLSheet->Name = _bstr_t(L"a & n"); // // Create four columns of data: Name, Pc, Br, and ISP // Construct a safearray of the data LONG lIndex = 2; // Note, 1 based not 0 - will contain # of rows... VARIANT saData; saData.vt = VT_ARRAY | VT_VARIANT; { SAFEARRAYBOUND sab[2]; sab[0].lLbound = 1; sab[0].cElements = vTabs.size() + 2; sab[1].lLbound = 1; sab[1].cElements = 4; saData.parray = SafeArrayCreate(VT_VARIANT, 2, sab); if(saData.parray == nullptr) { MessageBoxW(HWND_TOP, L"Unable to create safearray for passing data to Excel.", L"Memory error...", MB_TOPMOST | MB_ICONERROR); return; } // // Clean-up safe array when done... auto cleanArray = std::experimental::make_scope_exit([&saData]() -> void { VariantClear(&saData); }); // // Labels: SafeArrayPutString(*saData.parray, 1, 1, L"Motor"); SafeArrayPutString(*saData.parray, 1, 2, L"Pressure"); SafeArrayPutString(*saData.parray, 1, 3, L"Burn Rate"); SafeArrayPutString(*saData.parray, 1, 4, L"ISP"); for(auto entry : vTabs) { std::wstring wstrLink(L"="); wstrLink += entry; wstrLink += L"!$L$"; const std::wstring wstrPc(wstrLink + L"47"); const std::wstring wstrBr(wstrLink + L"56"); const std::wstring wstrISP(wstrLink + L"55"); SafeArrayPutString(*saData.parray, lIndex, 1, entry); // A Name SafeArrayPutString(*saData.parray, lIndex, 2, wstrPc); // B Pc SafeArrayPutString(*saData.parray, lIndex, 3, wstrBr); // C Br SafeArrayPutString(*saData.parray, lIndex, 4, wstrISP); // D Isp ++lIndex; } // // Insert the data into A1-Dx std::wstring wstrDataRange(L"A1:D"); wstrDataRange += std::to_wstring(lIndex); Excel::RangePtr pXLRange = pXLSheet->Range[_variant_t(wstrDataRange.c_str())]; pXLRange->Value2 = saData; } // Create a chart of the data std::wstring wstrChartRange(L"='a & n'!$B$1:$C$"); wstrChartRange += std::to_wstring(lIndex - 1); _variant_t varChartRangeString(wstrChartRange.c_str()); // // Create the range... Excel::RangePtr pChartRange = pXLSheet->Range[varChartRangeString]; _ASSERT(pChartRange); if(nullptr == pChartRange) { return; } pChartRange->Select(); // // Create the chart -- odd, but this the method for getting it embedded on // the same page as the data... auto pShapes = pXLSheet->GetShapes(); _ASSERT(pShapes); if(nullptr == pShapes) { return; } // auto pChartShape = pShapes->AddChart2(240, Excel::xlXYScatterLines); auto pChartShape = pShapes->AddChart(Excel::xlXYScatterLines); _ASSERT(pChartShape); if(nullptr == pChartShape) { return; } pChartShape->Select(); // // With the newly created Chart Shape we can access it's chart with Workbook::GetActiveChart() // And set the source data to the range created previously... auto pChart = pXLBook->GetActiveChart(); _ASSERT(pChart); if(nullptr == pChart) { return; } pChart->PutHasTitle(LOCALE_USER_DEFAULT, VARIANT_TRUE); pChart->SetSourceData(pChartRange); // // Set the Chart Title auto pChartTitle = pChart->ChartTitle; _ASSERT(pChartTitle); if(nullptr != pChartTitle) { pChartTitle->Select(); pChartTitle->Text = _bstr_t(L"a & n"); } // Add Trendline: Excel::SeriesPtr pFSC1 = pChart->SeriesCollection(variant_t(1)); _ASSERT(pFSC1); if(nullptr == pFSC1) { return; } pFSC1->Select(); Excel::TrendlinesPtr pTrendLines = pFSC1->Trendlines(); _ASSERT(pTrendLines); if(nullptr == pTrendLines) { return; } pTrendLines->Add(Excel::xlPower); Excel::TrendlinePtr pTrendLine = pTrendLines->Item(_variant_t(1)); _ASSERT(pTrendLine); if(nullptr == pTrendLine) { return; } pTrendLine->Select(); pTrendLine->Type = Excel::xlPower; pTrendLine->Forward = 200; pTrendLine->Backward = 200; pTrendLine->DisplayEquation = VARIANT_TRUE; pTrendLine->DisplayRSquared = VARIANT_TRUE; // Enlarge Text Excel::DataLabelPtr pDataLabel = pTrendLine->DataLabel; _ASSERT(pDataLabel); if(nullptr == pDataLabel) { return; } pDataLabel->Select(); Excel::FontPtr pFont = pDataLabel->Font; _ASSERT(pFont); if(nullptr == pFont) { return; } pFont->Size = 16; // Move to top right... pDataLabel->Top = 40; pDataLabel->Left = 40; // // Move chart into place and give a good size: Excel::ChartAreaPtr pChartArea = pChart->GetChartArea(0); _ASSERT(pChartArea); if(nullptr == pChartArea) { return; } pChartArea->PutLeft(240.0f); pChartArea->PutTop(0.0f); // // Now give an Average ISP: PlaceStringInCell(pXLSheet, L'H', 17, L"Average Delivered ISP:"); std::wstring wstrAvgISP(L"=AVERAGE(D2:D"); wstrAvgISP += std::to_wstring(lIndex - 1); wstrAvgISP += L")"; PlaceStringInCell(pXLSheet, L'K', 17, wstrAvgISP); // // Place a & n numbers on the sheet: // a: =EXP(INDEX(LINEST(LN(C2:C4),LN(B2:B4),,)*1,2)) // n: =INDEX(LINEST(LN(C2:C4), LN(B2:B4),,),1) PlaceStringInCell(pXLSheet, L'H', 18, L"Burn Rate Coefficient (a):"); PlaceStringInCell(pXLSheet, L'H', 19, L"Burn Rate Exponent (n):"); std::wstring wstrCoefficient(L"=EXP(INDEX(LINEST(LN(C2:C"); wstrCoefficient += std::to_wstring(vTabs.size() + 1); wstrCoefficient += L"), LN(B2:B"; wstrCoefficient += std::to_wstring(vTabs.size() + 1); wstrCoefficient += L"), , ) * 1, 2))"; PlaceStringInCell(pXLSheet, L'K', 18, wstrCoefficient); std::wstring wstrExponent(L"=INDEX(LINEST(LN(C2:C"); wstrExponent += std::to_wstring(vTabs.size() + 1); wstrExponent += L"), LN(B2:B"; wstrExponent += std::to_wstring(vTabs.size() + 1); wstrExponent += L"), , ), 1)"; PlaceStringInCell(pXLSheet, L'K', 19, wstrExponent); // // Draw attention to the results: Excel::RangePtr pResultsRange = pXLSheet->Range[_bstr_t(L"H17:K19")]; _ASSERT(pResultsRange); if(nullptr == pResultsRange) { return; } pResultsRange->Style = _bstr_t(L"Output"); }
bool ProduceExcelWorkbook(Excel::_WorkbookPtr &pXLBook, const std::wstring &wstrFilename, const std::string &strPropellant, bool bMetric, const boost::property_tree::ptree &tree, const std::vector<std::tuple<double, double, double, double, double> > &vReadings, std::map<std::wstring, std::wstring> &mapProperties) { if(vReadings.empty()) { return false; } // Get the active Worksheet and set its name. Excel::_WorksheetPtr pXLSheet = pXLBook->ActiveSheet; _ASSERT(pXLSheet); if(nullptr == pXLSheet) { return false; } // // Get Max Pressure to calculate the 10% line double dPmaxtime = 0.0f; double dPmax = GetPmax(dPmaxtime, vReadings); double dpTenPercent = dPmax * 0.10f; // // Need to know the start and stop burn time based on 10% of Pmax: double dBurnStartTime = 0.0f; double dBurnEndTime = 0.0f; LONG lIndexBurnStart = 0; LONG lIndexBurnEnd = 0; LONG lExcelIndex = 1; bool bHaveEndTime = false; for(auto entry : vReadings) { double dPressure = std::get<ePressure>(entry); double dTime = std::get<eTime>(entry); ++lExcelIndex; if(dTime <= dPmaxtime) { if(dPressure < dpTenPercent) { continue; } if(0 == lIndexBurnStart) { dBurnStartTime = dTime; lIndexBurnStart = lExcelIndex; } } else { // Watching the curve downward... lIndexBurnEnd = lExcelIndex; if(dPressure < dpTenPercent) { break; } dBurnEndTime = dTime; } } double dBurnTime = dBurnEndTime - dBurnStartTime; // // Convert name to a wide string and assign it to the sheet std::wstring wstrTabName(wstrFilename); SanitizeStringName(wstrTabName); // // Remove any math operators from the name: _bstr_t bstrTabName(SysAllocString(wstrTabName.c_str())); pXLSheet->Name = bstrTabName; std::wstring_convert<std::codecvt_utf8_utf16<wchar_t>> converter; // std::wstring wstrPropellantName(converter.from_bytes(strPropellant.c_str())); // // Create seven columns of data: Time, Thrust (LBS), Thrust (N), and Pressure (PSI), ... // Construct a safearray of the data LONG lIndex = 2; // Note, 1 based not 0 - will contain # of rows... VARIANT saData; VARIANT saDataReferences; saData.vt = VT_ARRAY | VT_VARIANT; saDataReferences.vt = VT_ARRAY | VT_VARIANT; { SAFEARRAYBOUND sab[2]; sab[0].lLbound = 1; sab[0].cElements = vReadings.size() + 2; sab[1].lLbound = 1; sab[1].cElements = 7; saData.parray = SafeArrayCreate(VT_VARIANT, 2, sab); if(saData.parray == nullptr) { MessageBoxW(HWND_TOP, L"Unable to create safearray for passing data to Excel.", L"Memory error...", MB_TOPMOST | MB_ICONERROR); return false; } // // Clean-up safe array when done... auto cleanArray = std::experimental::make_scope_exit([&saData]() -> void { VariantClear(&saData); }); sab[0].lLbound = 1; sab[0].cElements = vReadings.size() + 2; sab[1].lLbound = 1; sab[1].cElements = 5; saDataReferences.parray = SafeArrayCreate(VT_VARIANT, 2, sab); if(saDataReferences.parray == nullptr) { MessageBoxW(HWND_TOP, L"Unable to create safearray for passing data to Excel.", L"Memory error...", MB_TOPMOST | MB_ICONERROR); return false; } // // Clean-up safe array when done... auto cleanRefArray = std::experimental::make_scope_exit([&saDataReferences]() -> void { VariantClear(&saDataReferences); }); // // Labels: SafeArrayPutString(*saData.parray, 1, 1, L"Time"); SafeArrayPutString(*saData.parray, 1, 2, L"Thrust\n(lbs.)"); SafeArrayPutString(*saData.parray, 1, 3, L"Thrust\n(N)"); SafeArrayPutString(*saData.parray, 1, 4, L"Pressure\n(PSI)"); SafeArrayPutString(*saData.parray, 1, 5, L"Threshold"); SafeArrayPutString(*saData.parray, 1, 6, L"Time\nDelta"); SafeArrayPutString(*saData.parray, 1, 7, L"Thrust Element\n(N)"); // // Labels for reference data SafeArrayPutString(*saDataReferences.parray, 1, 1, L"Time Delta"); // AA SafeArrayPutString(*saDataReferences.parray, 1, 2, L"Thrust\n(lbs.)"); // AB SafeArrayPutString(*saDataReferences.parray, 1, 3, L"Thrust\n(N)"); // AC SafeArrayPutString(*saDataReferences.parray, 1, 4, L"Pressure\n(PSI)"); // AD SafeArrayPutString(*saDataReferences.parray, 1, 5, L"Thrust Element\n(N)"); // AE LONG lIndexBurnStart = lIndex; LONG lIndexBurnEnd = lIndex; for(auto data : vReadings) { double dTime = std::get<eTime>(data); double dThrust = std::get<eThrust>(data); double dPressure = std::get<ePressure>(data); double dThrustLBS = bMetric ? dThrust / g_dNewtonsPerPound : dThrust; double dThrustN = bMetric ? dThrust : dThrust * g_dNewtonsPerPound; double dTimeDelta = std::get<eTimeDelta>(data); double dThrustElementN = std::get<eThrustElementN>(data); SafeArrayPutDouble(*saData.parray, lIndex, 1, dTime); // A SafeArrayPutDouble(*saData.parray, lIndex, 2, dThrustLBS); // B SafeArrayPutDouble(*saData.parray, lIndex, 3, dThrustN); // C SafeArrayPutDouble(*saData.parray, lIndex, 4, dPressure); // D std::wstring wstrPmaxCalcCell(L"=IF($D$"); wstrPmaxCalcCell += std::to_wstring(lIndex); wstrPmaxCalcCell += L"<$Q$35,0,$Q$35"; SafeArrayPutString(*saData.parray, lIndex, 5, wstrPmaxCalcCell);// E (pMax threshold) SafeArrayPutDouble(*saData.parray, lIndex, 6, dTimeDelta); // F SafeArrayPutDouble(*saData.parray, lIndex, 7, dThrustElementN); // G // // Generate a set of data (to be hidden) which is blank when the real value is below the threshold auto fnGenThresholdIFStatement = ([lIndex](std::wstring &wstrStatement, wchar_t wcCol) { wstrStatement = L"=IF($E$"; wstrStatement += std::to_wstring(lIndex); wstrStatement += L",$"; wstrStatement += wcCol; wstrStatement += L"$"; wstrStatement += std::to_wstring(lIndex); wstrStatement += L",\"\")"; }); // // Now the references to included data =IF($E$n,$F$n,"") std::wstring wstrRef; fnGenThresholdIFStatement(wstrRef, L'F'); SafeArrayPutString(*saDataReferences.parray, lIndex, 1, wstrRef);// AA (Time Delta) fnGenThresholdIFStatement(wstrRef, L'B'); SafeArrayPutString(*saDataReferences.parray, lIndex, 2, wstrRef);// AB (Thrust LBS.) fnGenThresholdIFStatement(wstrRef, L'C'); SafeArrayPutString(*saDataReferences.parray, lIndex, 3, wstrRef);// AC (Thrust N.) fnGenThresholdIFStatement(wstrRef, L'D'); SafeArrayPutString(*saDataReferences.parray, lIndex, 4, wstrRef);// AD (Pressure) fnGenThresholdIFStatement(wstrRef, L'G'); SafeArrayPutString(*saDataReferences.parray, lIndex, 5, wstrRef);// AE (Thrust Element) ++lIndex; } // // Insert the data into A1-Gx std::wstring wstrDataRange(L"A1:G"); wstrDataRange += std::to_wstring(lIndex); Excel::RangePtr pXLRange = pXLSheet->Range[_variant_t(wstrDataRange.c_str())]; _ASSERT(pXLRange); if(nullptr == pXLRange) { return false; } pXLRange->Value2 = saData; // // Insert the Reference Data in AA - EE std::wstring wstrDataRangeRef(L"AA1:AE"); wstrDataRangeRef += std::to_wstring(lIndex); Excel::RangePtr pXLRangeRef = pXLSheet->Range[_variant_t(wstrDataRangeRef.c_str())]; _ASSERT(pXLRangeRef); if(nullptr == pXLRangeRef) { return false; } pXLRangeRef->Value2 = saDataReferences; } // // Create a chart showing pressure and thrust curves // With a secondary axis for thrust so that curves are near in scale // e.g. "ASBLUE3_16!$A$1:$B$457,ASBLUE3_16!$D$1:$D$457" std::wstring wstrChartRange(wstrTabName + L"!$A$1:$B$"); wstrChartRange += std::to_wstring(lIndex); wstrChartRange += L","; wstrChartRange += wstrTabName; wstrChartRange += L"!$D$1:$D$"; wstrChartRange += std::to_wstring(lIndex); _variant_t varChartRangeString(wstrChartRange.c_str()); // // Create the range... Excel::RangePtr pChartRange = pXLSheet->Range[varChartRangeString]; _ASSERT(pChartRange); if(nullptr == pChartRange) { return false; } // // Create the chart -- odd, but this the method for getting it embedded on // the same page as the data... auto pShapes = pXLSheet->GetShapes(); _ASSERT(pShapes); if(nullptr == pShapes) { return false; } // auto pChartShape = pShapes->AddChart2(240, Excel::xlXYScatter); <- Requires Excel 2013 auto pChartShape = pShapes->AddChart(Excel::xlXYScatter); _ASSERT(pChartShape); if(nullptr == pChartShape) { return false; } pChartShape->Select(); // // With the newly created Chart Shape we can access it's chart with Workbook::GetActiveChart() // And set the source data to the range created previously... auto pChart = pXLBook->GetActiveChart(); _ASSERT(pChart); if(nullptr == pChart) { return false; } pChart->SetSourceData(pChartRange); // // Set the Chart Title pChart->PutHasTitle(LOCALE_USER_DEFAULT, VARIANT_TRUE); auto pChartTitle = pChart->ChartTitle; _ASSERT(pChartTitle); if(nullptr != pChartTitle) { pChartTitle->Select(); pChartTitle->Text = _bstr_t(L"Pressure and Thrust"); } // // Setup as a custom chart with stacked lines and a secondary axis Excel::SeriesPtr pFSC1 = pChart->SeriesCollection(variant_t(1)); Excel::SeriesPtr pFSC2 = pChart->SeriesCollection(variant_t(2)); _ASSERT(pFSC1); _ASSERT(pFSC2); if(nullptr == pFSC1 || nullptr == pFSC2) { return false; } pFSC1->ChartType = Excel::xlLineStacked; pFSC1->AxisGroup = Excel::xlSecondary; pFSC2->ChartType = Excel::xlLineStacked; pFSC2->AxisGroup = Excel::xlPrimary; // // Make the axis labels appear (left for PSI, bottom for time, and right for thrust) pChart->SetElement(Office::msoElementPrimaryCategoryAxisTitleAdjacentToAxis); pChart->SetElement(Office::msoElementPrimaryValueAxisTitleRotated); pChart->SetElement(Office::msoElementSecondaryValueAxisTitleAdjacentToAxis); // // Set the bottom (Primary - Category) axis title to "time": Excel::AxisPtr pPrimaryAxes = pChart->Axes(Excel::xlCategory, Excel::xlPrimary); _ASSERT(pPrimaryAxes); if(nullptr == pPrimaryAxes) { return false; } Excel::AxisTitlePtr pPrimaryAxisTitle = pPrimaryAxes->AxisTitle; _ASSERT(pPrimaryAxisTitle); if(nullptr == pPrimaryAxisTitle) { return false; } pPrimaryAxisTitle->Select(); _bstr_t bstrTime(L"Time"); pPrimaryAxisTitle->Text = bstrTime; // // Set the left (Primary - Value) axis title to "PSI": _bstr_t bstrPSI(L"PSI"); Excel::AxisPtr pPrimaryVAxes = pChart->Axes(Excel::xlValue, Excel::xlPrimary); _ASSERT(pPrimaryVAxes); if(nullptr == pPrimaryVAxes) { return false; } Excel::AxisTitlePtr pPrimaryVAxisTitle = pPrimaryVAxes->AxisTitle; _ASSERT(pPrimaryVAxisTitle); if(nullptr == pPrimaryVAxisTitle) { return false; } pPrimaryVAxisTitle->Select(); pPrimaryVAxisTitle->Text = bstrPSI; // // Set the right (Secondary - Value) axis title to "Pounds of Thrust" Excel::AxisPtr pSecondaryAxes = pChart->Axes(Excel::xlValue, Excel::xlSecondary); _ASSERT(pSecondaryAxes); if(nullptr == pSecondaryAxes) { return false; } Excel::AxisTitlePtr pSecondaryAxisTitle = pSecondaryAxes->AxisTitle; _ASSERT(pSecondaryAxisTitle); if(nullptr == pSecondaryAxisTitle) { return false; } pSecondaryAxisTitle->Text = _bstr_t(L"Pounds of Thrust"); // // Move chart into place and give a good size: Excel::ChartAreaPtr pChartArea = pChart->GetChartArea(0); _ASSERT(pChartArea); if(nullptr == pChartArea) { return false; } pChartArea->PutLeft(365.0f); pChartArea->PutTop(52.0f); pChartArea->PutHeight(300.0f); pChartArea->PutWidth(500.0f); // // Set Gridlines pChart->SetElement(Office::msoElementPrimaryCategoryGridLinesNone); pChart->SetElement(Office::msoElementPrimaryValueGridLinesNone); pChart->SetElement(Office::msoElementPrimaryValueGridLinesMajor); pChart->SetElement(Office::msoElementPrimaryValueGridLinesNone); pChart->SetElement(Office::msoElementPrimaryValueGridLinesMinor); // // Add the 10% Pmax line: Excel::SeriesCollectionPtr pSeriesCol = pChart->SeriesCollection(); _ASSERT(pSeriesCol); if(nullptr == pSeriesCol) { return false; } Excel::SeriesPtr pPmaxSeries = pSeriesCol->NewSeries(); _ASSERT(pPmaxSeries); if(nullptr == pPmaxSeries) { return false; } pPmaxSeries->Name = _bstr_t(L"Action Time"); std::wstring wstrPmaxValue(L"="); wstrPmaxValue += wstrTabName; std::wstring wstrPmaxXValue(wstrPmaxValue); wstrPmaxValue += L"!$E$2:$E$"; wstrPmaxXValue += L"!$A$2:$A$"; wstrPmaxValue += std::to_wstring(lIndex); wstrPmaxXValue += std::to_wstring(lIndex); pPmaxSeries->Values = _bstr_t(wstrPmaxValue.c_str()); pPmaxSeries->XValues = _bstr_t(wstrPmaxXValue.c_str()); pPmaxSeries->ChartType = Excel::xlLine; pPmaxSeries->AxisGroup = Excel::xlPrimary; std::wstring wstrPressureRange(L"(D2:D"); wstrPressureRange += std::to_wstring(lIndex); wstrPressureRange += L")"; // // Display other file data... const std::string strGrains(tree.get("Document.MotorData.Grains", "0")); const std::string strCaseDiameter(tree.get("Document.MotorData.CaseDiameter", "0 mm")); const std::string strNozzleThroat(tree.get("Document.MotorData.NozzleThroatDiameter", "0")); std::string strInfo(strGrains + " grain " + strCaseDiameter + " " + strPropellant + " with " + strNozzleThroat + "\" throat 'nozzle'"); std::wstring wstrInfo(converter.from_bytes(strInfo.c_str())); std::wstring wstrInfoRange(L"K1"); Excel::RangePtr pInfoRange = pXLSheet->Range[_variant_t(wstrInfoRange.c_str())]; _ASSERT(pInfoRange); if(nullptr == pInfoRange) { return false; } pInfoRange->Value2 = _bstr_t(wstrInfo.c_str()); // // Add calculated data: ??? 5% line and other calculated data... // <doc name, display name, display location row letter, display location cell number, metric conversion> std::vector<std::tuple<std::string, std::wstring, wchar_t, int, bool> > vFileItemDisplay; vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.MaxThrust", L"Maximum Thrust:", L'K', 25, true)); vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.AvgThrust", L"Average Thrust:", L'K', 26, true)); vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.MaxPressure", L"Maximum Pressure:", L'K', 28, false)); vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.AvgPressure", L"Average Pressure:", L'K', 29, false)); vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.BurnTime", L"Burn Time:", L'K', 31, false)); vFileItemDisplay.push_back(std::make_tuple("Document.MotorData.Impuse", L"Impulse:", L'K', 32, false)); // NOTE: Spelling mistake in the TC Logger data! PlaceStringInCell(pXLSheet, L'L', 24, L"TC Logger File Data:"); for(auto entry : vFileItemDisplay) { std::wstring wstrDisplay(std::get<1>(entry)); double dData = tree.get(std::get<0>(entry).c_str(), 0.0f); bool bConversion = std::get<4>(entry); const wchar_t wcColumn = std::get<2>(entry); const wchar_t wcDataColumn = wcColumn + 2; int row = std::get<3>(entry); PlaceStringInCell(pXLSheet, wcColumn, row, wstrDisplay); if(bConversion) { double dThrustLBS = bMetric ? dData / g_dNewtonsPerPound : dData; double dThrustN = bMetric ? dData : dData * g_dNewtonsPerPound; PlaceStringInCell(pXLSheet, wcDataColumn, row, std::to_wstring(dThrustLBS)); PlaceStringInCell(pXLSheet, wcDataColumn + 1, row, L"LBS."); PlaceStringInCell(pXLSheet, wcDataColumn + 2, row, std::to_wstring(dThrustN)); PlaceStringInCell(pXLSheet, wcDataColumn + 3, row, L"N"); } else { PlaceStringInCell(pXLSheet, wcDataColumn, row, std::to_wstring(dData)); } } PlaceStringInCell(pXLSheet, L'I', 34, L"VARIABLES:"); // // Percent of Pmax used for calculations PlaceStringInCell(pXLSheet, L'I', 35, L"% Pmax for Burn threshold:"); PlaceStringInCell(pXLSheet, L'L', 35, mapProperties[L"PmaxThreshold"]); // 10% by default gives Action Time according to Sutton PlaceStringInCell(pXLSheet, L'M', 35, L"Pmax:"); std::wstring wstrPmaxCalc(L"=MAX"); wstrPmaxCalc += wstrPressureRange; PlaceStringInCell(pXLSheet, L'N', 35, wstrPmaxCalc); PlaceStringInCell(pXLSheet, L'O', 35, L"Pmax Threshold:"); PlaceStringInCell(pXLSheet, L'Q', 35, L"=($N$35*($L$35/100))"); // Q35 // // Grain Weight (g): [178] PlaceStringInCell(pXLSheet, L'I', 36, L"Grain Weight (g):"); PlaceStringInCell(pXLSheet, L'L', 36, mapProperties[L"GrainWeight"]); // Liner Weight g/in: [ 1.9197342 ] or [ 3.030928 ] PlaceStringInCell(pXLSheet, L'I', 37, L"Casting Tube Weight (g/in.):"); PlaceStringInCell(pXLSheet, L'L', 37, mapProperties[L"CastingTubeWeight"]); // PlaceStringInCell(pXLSheet, L'M', 37, L"White 54mm casting tube: 1.9197342 g/in. Tru-Core Waxy 54mm: 3.030928 g/in"); // Grain Length: [ 3.0625 ] PlaceStringInCell(pXLSheet, L'I', 38, L"Grain Length (in.):"); PlaceStringInCell(pXLSheet, L'L', 38, mapProperties[L"GrainLength"]); // Grain Diameter (in.): [ 1.75 ] PlaceStringInCell(pXLSheet, L'I', 39, L"Grain Diameter (in.):"); PlaceStringInCell(pXLSheet, L'L', 39, mapProperties[L"GrainDiameter"]); // Grain Core (in.): [ 0.625 ] PlaceStringInCell(pXLSheet, L'I', 40, L"Grain Core (in.):"); PlaceStringInCell(pXLSheet, L'L', 40, mapProperties[L"GrainCore"]); // // Our calculations (using Excel formulas so input can be changed)... PlaceStringInCell(pXLSheet, L'I', 42, L"Calculations based on Pmax Threshold:"); // Density: PlaceStringInCell(pXLSheet, L'I', 43, L"Density:"); PlaceStringInCell(pXLSheet, L'L', 43, L"=((L36-(L38*L37))/453.59237)/(((((L39/2)^2)-(L40/2)^2))*PI()*L38)"); // Web PlaceStringInCell(pXLSheet, L'I', 44, L"Web:"); PlaceStringInCell(pXLSheet, L'L', 44, L"=(L39-L40)/2"); // Burn Time (s) PlaceStringInCell(pXLSheet, L'I', 45, L"Burn Time (s):"); std::wstring wstrBurnTime(L"=SUM(AA1:AA"); wstrBurnTime += std::to_wstring(lIndex); PlaceStringInCell(pXLSheet, L'L', 45, wstrBurnTime); PlaceStringInCell(pXLSheet, L'M', 45, std::wstring(L"10% = " + std::to_wstring(dBurnTime))); // Max Pressure PlaceStringInCell(pXLSheet, L'I', 46, L"Max Pressure (PSI):"); std::wstring wstrBtPressureRange(L"(AD1:AD"); wstrBtPressureRange += std::to_wstring(lIndex); wstrBtPressureRange += L")"; std::wstring wstrMaxPressure(L"=MAX"); wstrMaxPressure += wstrBtPressureRange; PlaceStringInCell(pXLSheet, L'L', 46, wstrMaxPressure); // Average Pressure PlaceStringInCell(pXLSheet, L'I', 47, L"Average Pressure (PSI):"); std::wstring wstrAvgPressure(L"=AVERAGE"); wstrAvgPressure += wstrBtPressureRange; PlaceStringInCell(pXLSheet, L'L', 47, wstrAvgPressure); std::wstring wstrThrustRange(L"(AB1:AB"); std::wstring wstrThrustRangeN(L"(AC1:AC"); std::wstring wstrThrustElementRangeN(L"(AE1:AE"); wstrThrustRange += std::to_wstring(lIndex); wstrThrustRangeN += std::to_wstring(lIndex); wstrThrustElementRangeN += std::to_wstring(lIndex); wstrThrustRange += L")"; wstrThrustRangeN += L")"; wstrThrustElementRangeN += L")"; // Max Thrust (LBS) PlaceStringInCell(pXLSheet, L'I', 48, L"Max Thrust (LBS):"); std::wstring wstrMaxThrust(L"=MAX"); wstrMaxThrust += wstrThrustRange; PlaceStringInCell(pXLSheet, L'L', 48, wstrMaxThrust); // Max Thrust (N) PlaceStringInCell(pXLSheet, L'I', 49, L"Max Thrust (N):"); std::wstring wstrMaxThrustN(L"=MAX"); wstrMaxThrustN += wstrThrustRangeN; PlaceStringInCell(pXLSheet, L'L', 49, wstrMaxThrustN); // Average Thrust (LBS) PlaceStringInCell(pXLSheet, L'I', 50, L"Average Thrust (LBS):"); std::wstring wstrAvgThrust(L"=AVERAGE"); wstrAvgThrust += wstrThrustRange; PlaceStringInCell(pXLSheet, L'L', 50, wstrAvgThrust); // Average Thrust (N) PlaceStringInCell(pXLSheet, L'I', 51, L"Average Thrust (N):"); std::wstring wstrAvgThrustN(L"=AVERAGE"); wstrAvgThrustN += wstrThrustRangeN; PlaceStringInCell(pXLSheet, L'L', 51, wstrAvgThrustN); // Total Thrust: PlaceStringInCell(pXLSheet, L'I', 52, L"Total Thrust (N):"); std::wstring wstrTotalThrustN(L"=SUM"); wstrTotalThrustN += wstrThrustElementRangeN; PlaceStringInCell(pXLSheet, L'L', 52, wstrTotalThrustN); // Grain Weight minus Liner: PlaceStringInCell(pXLSheet, L'I', 53, L"Propellant Weight (g):"); PlaceStringInCell(pXLSheet, L'L', 53, L"=(L36-(L38*L37))"); // ISP: PlaceStringInCell(pXLSheet, L'I', 55, L"ISP:"); PlaceStringInCell(pXLSheet, L'L', 55, L"=L52/(9.8*(L53/1000))"); // Burn Rate: PlaceStringInCell(pXLSheet, L'I', 56, L"Burn Rate:"); PlaceStringInCell(pXLSheet, L'L', 56, L"=(L44/L45)"); // // Make the INPUT area GREEN: Excel::RangePtr pVariableRange = pXLSheet->Range[_variant_t(L"I35:L40")]; _ASSERT(pVariableRange); if(nullptr == pVariableRange) { return false; } Excel::InteriorPtr pIntVarRange = pVariableRange->Interior; _ASSERT(pIntVarRange); if(nullptr == pIntVarRange) { return false; } pIntVarRange->Pattern = xlSolid; pIntVarRange->PatternColorIndex = Excel::xlAutomatic; pIntVarRange->Color = 5287936; pIntVarRange->TintAndShade = 0; pIntVarRange->PatternTintAndShade = 0; // // Output Area Excel::RangePtr pCalcRange = pXLSheet->Range[_variant_t(L"I43:L56")]; _ASSERT(pCalcRange); if(nullptr == pCalcRange) { return false; } Excel::InteriorPtr pIntCalcRange = pCalcRange->Interior; _ASSERT(pIntCalcRange); if(nullptr == pIntCalcRange) { return false; } pIntCalcRange->PatternColorIndex = Excel::xlAutomatic; pIntCalcRange->Color = 7373816; pIntCalcRange->TintAndShade = 0; pIntCalcRange->PatternTintAndShade = 0; return true; }
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; }