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 ExcelDOMStripper::RemoveExcelLinks(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::xlLinkTypeExcelLinks));
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))
return;
Excel::NamesPtr spNames = m_spWorkbook->Names;
Excel::NamePtr spName = NULL;
_variant_t vtCellFormula;
CStdString sCellFormula, sFirstAddress;
std::map<CStdString, int> :: iterator mapName;
int nAddressCount = 0;
Excel::RangePtr spUsedRange = spWorksheet->UsedRange;
_variant_t vtWhat(_T("="));
Excel::RangePtr spCellRange = spUsedRange->Find(vtWhat, vtMissing, (long)Excel::xlFormulas, vtMissing, vtMissing, Excel::xlNext);
while (spCellRange != 0)
{
if (0 == nAddressCount)
sFirstAddress = spCellRange->GetAddress(vtMissing, vtMissing, Excel::xlA1);
nAddressCount++;
vtCellFormula = spCellRange->Formula;
sCellFormula = vtCellFormula.bstrVal;
if (_T('=') == sCellFormula[0])
{
if (-1 != sCellFormula.Find(_T('[')))
{
spCellRange->Formula = spCellRange->Value;
if (1 == nAddressCount)
nAddressCount = 0;
m_linksCount++;
}
else if (0 < m_mapNames.size())
{
sCellFormula.Delete(0, 1);
mapName = m_mapNames.find(sCellFormula);
if (mapName != m_mapNames.end())
{
try
{
spName = spNames->Item(sCellFormula.c_str());
}
catch(...)
{
// Should not come here. Because the name is in the map it should also be in the Names collection.
spName = 0;
}
if (spName != 0)
{
CStdString sValue = spName->Value;
if (-1 != sValue.Find(_T('[')))
{
spCellRange->Formula = spCellRange->Value;
mapName->second = 1;
if (1 == nAddressCount)
nAddressCount = 0;
}
}
}
}
}
_variant_t vtCellRange((IDispatch*)spCellRange);
spCellRange = spUsedRange->FindNext(vtCellRange);
if (spCellRange == 0)
break;
if (sFirstAddress == spCellRange->GetAddress(vtMissing, vtMissing, Excel::xlA1))
break;
}
//Links have been updated, now remove them
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::xlLinkTypeExcelLinks), 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);
}
}
}
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");
}
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();
}