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 Excel12DOMStripper::RemoveSheetHeadersFooters(bool bRemoveHeaders, bool bRemoveFooters) throw(Workshare::Exception)
{
if(!bRemoveHeaders && !bRemoveFooters)
return;
HRESULT hr = S_OK;
CStdString sErr;
for(long nIndex = 1; nIndex <= m_spWorkbook->Sheets->Count; nIndex++)
{
//long sheetCount = m_spWorkbook->Sheets->Count;
//long wkSheetCount = m_spWorkbook->Worksheets->Count;
//TCHAR msg[255];
//swprintf_s(msg,254,_T("SC: %ld, WSC: %ld, nIndex: %ld"),sheetCount,wkSheetCount,nIndex);
//::MessageBox(0,msg,_T("Hello"),0);
IDispatchPtr spDispSheet = m_spWorkbook->Sheets->Item[nIndex];
_variant_t vtDispSheet;
hr = CCOMDispatchHelper::AutoWrap(DISPATCH_PROPERTYGET, &vtDispSheet, spDispSheet, L"PageSetup", 0);
if(FAILED(hr))
{
sErr.Format(_T("Failed to get PageSetup object from Sheet(%n)"), nIndex);
throw Workshare::Com::ComException(sErr.c_str(), hr);
}
Excel::PageSetupPtr spPageSetup = vtDispSheet.pdispVal;
if(spPageSetup == 0)
{
sErr.Format(_T("Invalid PageSetup object returned for Sheet(%n)"), nIndex);
throw Workshare::Exception(sErr.c_str());
}
CComQIPtr<Excel12::PageSetup> spPageSetup12 = spPageSetup;
if(spPageSetup12 != NULL)
{
if(spPageSetup12->DifferentFirstPageHeaderFooter)
spPageSetup12->DifferentFirstPageHeaderFooter = VARIANT_FALSE;
if(spPageSetup12->OddAndEvenPagesHeaderFooter)
spPageSetup12->OddAndEvenPagesHeaderFooter = VARIANT_FALSE;
}
RemovePageSetupHeadersFooters(spPageSetup, bRemoveHeaders, bRemoveFooters);
Excel::_WorksheetPtr spWorksheet = NULL;
try // CH 16/10/07 in 20007 ->Sheets->Count and ->Worksheets->Count are not
{ // necessarily the same (Macros's and charts are sheets) so catch ex
spWorksheet = m_spWorkbook->Worksheets->Item[nIndex];
}
catch (...)
{
}
if (NULL != spWorksheet)
{
Excel::ChartObjectsPtr spChartObjects(spWorksheet->ChartObjects());
RemoveEmbeddedChartHeadersFooters(spChartObjects, bRemoveHeaders, bRemoveFooters);
}
}
}
void ExcelDOMStripper::RestoreHiddenWorksheets()
{
Excel::SheetsPtr pWorksheets = m_spWorkbook->Worksheets;
const long count = pWorksheets->Count;
for (long index = 1; index <= count; ++index)
{
std::vector<long>::iterator position = std::find(m_hiddenWorksheets.begin(), m_hiddenWorksheets.end(), index);
if (position != m_hiddenWorksheets.end())
{
Excel::_WorksheetPtr spWorksheet = pWorksheets->Item[index];
spWorksheet->PutVisible(LocaleHelper::GetLocaleIDForInstalledExcel(m_spWorkbook), Excel::xlSheetHidden);
}
}
}
void ExcelDOMStripper::UnhideWorksheets()
{
Excel::SheetsPtr pWorksheets = m_spWorkbook->Worksheets;
const long count = pWorksheets->Count;
for (long index = 1; index <= count; ++index)
{
Excel::_WorksheetPtr spWorksheet = pWorksheets->Item[index];
if (!spWorksheet->Visible)
{
m_hiddenWorksheets.push_back(index);
spWorksheet->PutVisible(LocaleHelper::GetLocaleIDForInstalledExcel(m_spWorkbook), Excel::xlSheetVisible);
}
}
}
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;
}
void CDlgReportMounth::OnBnClickedOk()
{
CString sStart, sEnd;
if(m_ComboYear.GetCurSel()<0)
{
AfxMessageBox(_T("Ќе выбран год"));
return;
}
int iYear;
iYear = m_ComboYear.GetItemData(m_ComboYear.GetCurSel());
if((iYear < 2000)||(iYear>2019))
{
AfxMessageBox(_T("ќшибка в программе"));
return;
}
if(m_ComboMounth.GetCurSel()<0)
{
AfxMessageBox(_T("Ќе выбранн мес¤ц"));
return;
}
int iMounth;
iMounth = m_ComboMounth.GetItemData(m_ComboMounth.GetCurSel());
if((iMounth < 1)||(iMounth>12))
{
AfxMessageBox(_T("ќшибка в программе"));
return;
}
CRepScanApp *pApp;
pApp = (CRepScanApp*)AfxGetApp();
if(pApp->dBase == NULL)
{
AfxMessageBox(_T("Ќет доступа к Ѕƒ"));
return ;
}
CString sSelectDate;
if(iMounth < 10)
sSelectDate.Format(_T("%d0%d"),iYear,iMounth);
else
sSelectDate.Format(_T("%d%d"),iYear,iMounth);
try
{
CString sSQL;
CRecordset Query(pApp->dBase);
CDBVariant oValue;
/*sSQL.Format(_T("select name, (Right(left(name,8),4)+Right(left(name,4),2)+left(name,2)) as dat from sysobjects where Right(name,9) = '%s_BQ'"),sSelectDate);
Query.Open(CRecordset::forwardOnly,sSQL,CRecordset::readOnly);
sSQL = _T("");
CString NewDate;
while(!Query.IsEOF())
{
Query.GetFieldValue(_T("name"),oValue);
CString sDate;
sDate = GetValue(&oValue);
sDate = sDate.Left(8);
NewDate = sDate.Right(4);
sDate = sDate.Left(4);
NewDate = NewDate + sDate.Right(2)+sDate.Left(2);
if(sSQL =="")
sSQL = _T("select Employee, count(test.con) as ConDay,id_zone, '")+NewDate+_T("' as dat from (select Employee,id_doc,id_zone, sort,task, 1 as con from [")+GetValue(&oValue)+_T("] group by Employee,id_doc,id_zone,sort,task) as test group by Employee ,id_zone");
else
sSQL = sSQL + _T("\n union select Employee, count(test.con) as ConDay,id_zone, '")+NewDate+_T("' as dat from (select Employee,id_doc,id_zone, sort,task, 1 as con from [")+GetValue(&oValue)+_T("] group by Employee,id_doc,id_zone,sort,task) as test group by Employee ,id_zone");
Query.MoveNext();
}
Query.Close();
if(sSQL.GetLength()<1)
return;
sSQL = _T("select Employee, id_zone,ConDay, dat from (")+sSQL+_T(") as testday order by Employee, dat, id_zone");
*/
/*
Registered Whse. Activity Line
*/
/*sSQL = _T("select [Assigned User ID] as Employee,[Zone Code] as id_zone,count(*) as ConDay from ( SELECT [Source Document No_],[Pick No_],[Assigned User ID],[Item No_],[Zone Code],[From Bin Code] FROM [")+sDatabase;
sSQL = sSQL + _T("$Picking Operation Register] join [")+sDatabase;
sSQL = sSQL + _T("$bin] on [code] = [From Bin Code] where Left(CONVERT ( nchar , [Entry Date], 112),6) = '")+sSelectDate;
sSQL = sSQL + _T("' group by [Source Document No_],[Pick No_],[Assigned User ID],[Item No_],[Zone Code],[From Bin Code] ) as TestTab group by [Assigned User ID],[Zone Code]order by Employee,id_zone");
*/
sSQL = _T("select [Assigned User ID] as Employee ,[Zone Code] as id_zone, count(*) as ConDay from (select [Assigned User ID], hd.[No_],[Line No_],ln.[Zone Code] from [")+sDatabase;
sSQL = sSQL +_T("$Warehouse Zone Employee] join[")+sDatabase;
sSQL = sSQL + _T("$Registered Whse_ Activity Hdr_] as hd on [User ID] =[Assigned User ID] join [")+sDatabase;
sSQL = sSQL + _T("$Registered Whse_ Activity Line] as ln on ln.[No_] = hd.[No_] and [Action Type] = 1 where Left(CONVERT ( nchar , [Registering Date], 112),6) = '")+sSelectDate;
sSQL = sSQL + _T("' and [Type] = 2 group by [Assigned User ID],hd.[No_],[Line No_],ln.[Zone Code]) as Test group by [Assigned User ID],[Zone Code] order by [Assigned User ID],[Zone Code]");
Excel::WorkbooksPtr ExcelBooks;
Excel::_WorkbookPtr ExcelBook;
Excel::_WorksheetPtr ExcelSheet;
Excel::RangePtr range;
appExcel.CreateInstance( _T("Excel.Application"));
VARIANT bTRUE;
bTRUE.vt = 11;
bTRUE.boolVal = TRUE;
appExcel->Visible[0] = FALSE;
ExcelBook= appExcel->Workbooks->Add();
ExcelSheet = ExcelBook->Worksheets->Item[1];
Query.Open(CRecordset::forwardOnly,sSQL,CRecordset::readOnly);
int iRow;
iRow = 1;
CString sName;
CString oldMan;
int iCol;
int iValue;
iCol=2;
CString sDat;
sDat = _T("—борщики");
ExcelSheet->Cells->Item[1,1] = sDat.AllocSysString();
sDat = _T("«она1");
ExcelSheet->Cells->Item[1,2] = sDat.AllocSysString();
sDat = _T("«она2");
ExcelSheet->Cells->Item[1,3] = sDat.AllocSysString();
sDat = _T("«она3");
ExcelSheet->Cells->Item[1,4] = sDat.AllocSysString();
sDat = _T("«она4");
ExcelSheet->Cells->Item[1,5] = sDat.AllocSysString();
sDat = _T("«она5");
ExcelSheet->Cells->Item[1,6] = sDat.AllocSysString();
sDat = _T("«она1нс");
ExcelSheet->Cells->Item[1,7] = sDat.AllocSysString();
sDat = _T("«она2нс");
ExcelSheet->Cells->Item[1,8] = sDat.AllocSysString();
sDat = _T("«она3нс");
ExcelSheet->Cells->Item[1,9] = sDat.AllocSysString();
sDat = _T("«она4нс");
ExcelSheet->Cells->Item[1,10] = sDat.AllocSysString();
sDat = _T("«она5нс");
ExcelSheet->Cells->Item[1,11] = sDat.AllocSysString();
sDat = _T("Ќеизвестна¤ зона");
ExcelSheet->Cells->Item[1,12] = sDat.AllocSysString();
sDat = _T("—умма строк");
ExcelSheet->Cells->Item[1,13] = sDat.AllocSysString();
sDat = _T("USD");
ExcelSheet->Cells->Item[1,14] = sDat.AllocSysString();
Excel::BordersPtr borders;
sDat = _T("—тавки");
ExcelSheet->Cells->Item[1,16] = sDat.AllocSysString();
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[1,16],ExcelSheet->Cells->Item[2,27]);
borders = range->GetBorders();
borders->PutLineStyle(Excel::xlContinuous);
borders->PutWeight(Excel::xlThin);
borders->PutColorIndex(Excel::xlAutomatic);
//—тавки 1 «она 2 «она 3 «она 4 «она 5 «она Ќеизвестна¤ зона
// 0.08775 0.053625 0.053625 0.053625 0
//—тавки «она1нс «она2нс «она3нс «она4нс «она5нс
// 0.08775 0.053625 0.053625 0.053625 0.053625
sDat = _T("1 «она");
ExcelSheet->Cells->Item[1,17] = sDat.AllocSysString();
//sDat = _T("0.08775");
sDat = sReadFromIni(_T("Rate"),_T("Z01"),_T("0"));
ExcelSheet->Cells->Item[2,17] = sDat.AllocSysString();
sDat = _T("2 «она");
ExcelSheet->Cells->Item[1,18] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z02"),_T("0"));
ExcelSheet->Cells->Item[2,18] = sDat.AllocSysString();
sDat = _T("3 «она");
ExcelSheet->Cells->Item[1,19] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z03"),_T("0"));
ExcelSheet->Cells->Item[2,19] = sDat.AllocSysString();
sDat = _T("4 «она");
ExcelSheet->Cells->Item[1,20] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("Z04"),_T("0"));
ExcelSheet->Cells->Item[2,20] = sDat.AllocSysString();
sDat = _T("5 «она");
ExcelSheet->Cells->Item[1,21] = sDat.AllocSysString();
//sDat = _T("0");
sDat = sReadFromIni(_T("Rate"),_T("PAL"),_T("0"));
ExcelSheet->Cells->Item[2,21] = sDat.AllocSysString();
sDat = _T("1нс «она");
ExcelSheet->Cells->Item[1,22] = sDat.AllocSysString();
//sDat = _T("0.08775");
sDat = sReadFromIni(_T("Rate"),_T("MZ01"),_T("0"));
ExcelSheet->Cells->Item[2,22] = sDat.AllocSysString();
sDat = _T("2нс «она");
ExcelSheet->Cells->Item[1,23] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ02"),_T("0"));
ExcelSheet->Cells->Item[2,23] = sDat.AllocSysString();
sDat = _T("3нс «она");
ExcelSheet->Cells->Item[1,24] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ03"),_T("0"));
ExcelSheet->Cells->Item[2,24] = sDat.AllocSysString();
sDat = _T("4нс «она");
ExcelSheet->Cells->Item[1,25] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ04"),_T("0"));
ExcelSheet->Cells->Item[2,25] = sDat.AllocSysString();
sDat = _T("5нс «она");
ExcelSheet->Cells->Item[1,26] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("MZ05"),_T("0"));
ExcelSheet->Cells->Item[2,26] = sDat.AllocSysString();
sDat = _T("Ќеизвестна¤ зона");
ExcelSheet->Cells->Item[1,27] = sDat.AllocSysString();
//sDat = _T("0.053625");
sDat = sReadFromIni(_T("Rate"),_T("UNKNOWN"),_T("0"));
ExcelSheet->Cells->Item[2,27] = sDat.AllocSysString();
COleDateTime cDate,datEnd;
_variant_t oVal;
while(!Query.IsEOF())
{
Query.GetFieldValue(_T("Employee"),oValue);
if(GetValue(&oValue)!=oldMan)
{
iRow++;
sName = GetValue(&oValue);
ExcelSheet->Cells->Item[iRow,1] = sName.AllocSysString();
oldMan = sName;
}
//id_zone
Query.GetFieldValue(_T("id_zone"),oValue);
sName = GetValue(&oValue);
iCol = -1;
if(sName == _T("Z01"))
{
iCol = 2;
}
if(sName == _T("Z02"))
{
iCol = 3;
}
if(sName == _T("Z03"))
{
iCol = 4;
}
if(sName == _T("Z04"))
{
iCol = 5;
}
if(sName == _T("PAL"))
{
iCol = 6;
}
if(sName == _T("MZ01"))
{
iCol = 7;
}
if(sName == _T("MZ02"))
{
iCol = 8;
}
if(sName == _T("MZ03"))
{
iCol = 9;
}
if(sName == _T("MZ04"))
{
iCol = 10;
}
if(sName == _T("MZ05"))
{
iCol = 11;
}
if(iCol < 0)
{
iCol = 12;
}
Query.GetFieldValue(_T("ConDay"),oValue);
iValue = GetValueID(&oValue);
//ExcelSheet->Cells->Item[iRow,iCol] = sName.AllocSysString();
range = ExcelSheet->Cells->Item[iRow,iCol];
oVal = range->GetValue2();
if(oVal.vt != 0)
{
oVal.ChangeType(VT_I8);
iValue = iValue+oVal.lVal;
}
sName.Format(_T("%d"),iValue);
ExcelSheet->Cells->Item[iRow,iCol] = sName.AllocSysString();
range = ExcelSheet->Cells->Item[iRow, 13];
range->PutFormula(_T("=SUM(RC[-11]:RC[-1])"));
sName.Format(_T("=RC[-12]*R[-%d]C17 + RC[-11]*R[-%d]C18 + RC[-10]*R[-%d]C19 + RC[-9]*R[-%d]C20 + RC[-8]*R[-%d]C21 + RC[-7]*R[-%d]C22 + RC[-6]*R[-%d]C23 + RC[-5]*R[-%d]C24 + RC[-4]*R[-%d]C25 + RC[-3]*R[-%d]C26 + RC[-2]*R[-%d]C27"),iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2,iRow-2);
/*
=RC[-6]*R[-2]C12+RC[-5]*R[-2]C13+RC[-4]*R[-2]C14
*/
range = ExcelSheet->Cells->Item[iRow, 14];
range->PutFormula(sName.AllocSysString());
Query.MoveNext();
}
Query.Close();
//=—”ћћ(R[-169]C:R[-1]C)
if(iRow > 1)
{
iRow++;
sName.Format(_T("»того:"));
ExcelSheet->Cells->Item[iRow, 1] = sName.AllocSysString();
sName.Format(_T("=SUM(R[-%d]C:R[-1]C)"),iRow-2);
range = ExcelSheet->Cells->Item[iRow,2];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,3];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,4];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,5];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,6];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,7];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,8];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,9];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,10];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,11];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,12];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,13];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->Cells->Item[iRow,14];
range->PutFormula(sName.AllocSysString());
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[1, 1], ExcelSheet->Cells->Item[iRow, 14]);
//range->PutFormula(sName.AllocSysString());
borders = range->GetBorders();
borders->PutLineStyle(Excel::xlContinuous);
borders->PutWeight(Excel::xlThin);
borders->PutColorIndex(Excel::xlAutomatic);
range = ExcelSheet->GetRange(ExcelSheet->Cells->Item[2, 14], ExcelSheet->Cells->Item[iRow, 14]);
range->Interior->Color = RGB(200, 160, 35);
}
appExcel->Visible[0] = TRUE;
}
catch(CDBException *exsept)
{
AfxMessageBox(exsept->m_strError);
exsept->Delete();
return ;
}
OnOK();
}
