void ExcelDOMStripper::RemoveOLELinks(IDispatchPtr spDispatch)
{
if (spDispatch == 0)
throw Workshare::ArgumentNullException(_T("spDispatch"), _T("The Worksheet that we want to remove the excel links from is invalid."));
Excel::_WorksheetPtr spWorksheet = spDispatch;
if (spWorksheet == 0)
throw Workshare::Com::ComException(_T("The dispatch instance passed does not implement the Excel::_Worksheet interface"), E_NOINTERFACE);
try
{
_variant_t vtLinkSources(m_spWorkbook->LinkSources((long)Excel::xlLinkTypeOLELinks));
if (VT_EMPTY == vtLinkSources.vt)
return;
SAFEARRAY *psa = V_ARRAY(&vtLinkSources);
LONG iLBound, iUBound;
::SafeArrayGetLBound(psa, 1, &iLBound);
::SafeArrayGetUBound(psa, 1, &iUBound);
if (0 < (iUBound - iLBound + 1))
{
CComSafeArray <VARIANT> safeArray(psa);
for (ULONG i = 1; i <= safeArray.GetCount(); i++)
{
_variant_t name(safeArray.GetAt(i));
VARIANT result;
VariantInit(&result);
HRESULT hr = DispatchWrapper(DISPATCH_METHOD, &result, m_spWorkbook, L"BreakLink", 2, _variant_t(Excel::xlLinkTypeOLELinks), name);
if (DISP_E_UNKNOWNNAME != hr)
{
if (0 != result.boolVal)
throw Workshare::Com::ComException(_T("Failed to break the link on the Excel document"), E_FAIL);
}
}
}
Excel::OLEObjectsPtr spOLEObjects = spWorksheet->OLEObjects();
long lObjectCount = spOLEObjects->Count;
for (long lObjectIndex = lObjectCount; 0 < lObjectIndex; lObjectIndex--)
{
Excel::_OLEObjectPtr spOLEObject = spOLEObjects->Item(lObjectIndex);
_variant_t vtOLEType = spOLEObject->OLEType;
if (Excel::xlOLELink == vtOLEType.intVal || Excel::xlOLEEmbed == vtOLEType.intVal)
{
if(m_spWorkbook->MultiUserEditing)
throw Workshare::Com::ComException(_T("An embedded OLE link has been detected, but cannot be cleaned when Workbook Sharing (Track Changes) is enabled. To clean the link, click No and then resend while selecting to accept and switch off Track Changes."), E_FAIL);
Excel::RangePtr spRangePtr = spOLEObject->TopLeftCell;
/*spOLEObject->Copy();
spWorksheet->Paste(_variant_t(spRangePtr), vtMissing);
spOLEObject->Delete();*/
spOLEObject->Copy();
//spOLEObject->Activate();
spOLEObject->Delete();
m_linksCount++;
spRangePtr->Select();
spWorksheet->PasteSpecial(_T("Picture (PNG)"), VARIANT_FALSE, VARIANT_FALSE);
} else
{
}
}
Excel::ShapesPtr spShapes = spWorksheet->Shapes;
long lShapeCount = spShapes->Count;
for (long lShapeIndex = lShapeCount; 0 < lShapeIndex; lShapeIndex--)
{
Excel::ShapePtr spShape = spShapes->Item(lShapeIndex);
_variant_t vtShapeType = spShape->Type;
if (msoffice::msoLinkedOLEObject == vtShapeType.intVal)
{
spShape->Copy();
spShape->TopLeftCell->Activate();
spShape->Delete();
m_linksCount++;
spWorksheet->PasteSpecial(_T("Picture (PNG)"), VARIANT_FALSE, VARIANT_FALSE);
}
}
}
catch(Workshare::Exception const&)
{
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");
}