//得到行的总数
int IllusionExcelFile::GetRowCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Rows(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
//得到列的总数
int CExcelFile::GetColumnCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Columns(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
//得到行的总数
long Illusion_ExcelFile::row_count()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Rows(), true);
long count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
//VT_R8
int IllusionExcelFile::GetCellInt(long irow, long icolumn)
{
int num;
COleVariant vresult;
if (already_preload_ == FALSE)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item(COleVariant((long)irow), COleVariant((long)icolumn)).pdispVal, true);
vresult = range.get_Value2();
range.ReleaseDispatch();
}
else
{
long read_address[2];
VARIANT val;
read_address[0] = irow;
read_address[1] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vresult = val;
}
//
num = static_cast<int>(vresult.dblVal);
return num;
}
double IllusionExcelFile::GetCellDouble(long irow, long icolumn)
{
double rtn_value = 0;
COleVariant vresult;
//字符串
if (already_preload_ == FALSE)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item(COleVariant((long)irow), COleVariant((long)icolumn)).pdispVal, true);
vresult = range.get_Value2();
range.ReleaseDispatch();
}
//如果数据依据预先加载了
else
{
long read_address[2];
VARIANT val;
read_address[0] = irow;
read_address[1] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vresult = val;
}
if (vresult.vt == VT_R8)
{
rtn_value = vresult.dblVal;
}
return rtn_value;
}
void IllusionExcelFile::FreezePanes(const TCHAR* cell){
CRange range = excel_work_sheet_.get_Range(COleVariant(cell), COleVariant(cell));
range.Select();
CWindow0 mwin = excel_application_.get_ActiveWindow();
mwin.put_FreezePanes(1);
range.ReleaseDispatch();
}
CString Illusion_ExcelFile::get_cell_cstring(long iline, long icolumn)
{
COleVariant vResult ;
CString str;
//字符串
if (already_preload_ == FALSE)
{
CRange range;
_variant_t v_row(iline);
_variant_t v_column(icolumn);
range.AttachDispatch(excel_current_range_.get_Item(v_row, v_column).pdispVal, true);
vResult = range.get_Value2();
range.ReleaseDispatch();
}
//如果数据依据预先加载了
else
{
long read_address[2];
VARIANT val;
read_address[0] = iline;
read_address[1] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vResult = val;
}
if (vResult.vt == VT_BSTR)
{
str = vResult.bstrVal;
}
//整数
else if (vResult.vt == VT_INT)
{
str.Format(_T("%d"), vResult.pintVal);
}
//8字节的数字
else if (vResult.vt == VT_R8)
{
str.Format(_T("%.6f"), vResult.dblVal);
}
//时间格式
else if (vResult.vt == VT_DATE)
{
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
CTime tm(st);
str = tm.Format(_T("%Y-%m-%d"));
}
//单元格空的
else if (vResult.vt == VT_EMPTY)
{
str = "";
}
return str;
}
///检查一个CELL是否是时间
BOOL IllusionExcelFile::IsCellTime(long iRow, long iColumn)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
COleVariant vResult = range.get_Value2();
if (vResult.vt == VT_DATE)
{
return TRUE;
}
range.ReleaseDispatch();
return FALSE;
}
//检查一个CELL是否是数值
BOOL IllusionExcelFile::IsCellIntOrDouble(long irow, long icolumn)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item(COleVariant((long)irow), COleVariant((long)icolumn)).pdispVal, true);
COleVariant vResult = range.get_Value2();
//好像一般都是VT_R8
if (vResult.vt == VT_INT || vResult.vt == VT_R8)
{
return TRUE;
}
range.ReleaseDispatch();
return FALSE;
}
void CLogAnalyzerView::OnGeneratereportExcel()
{
#ifdef EXCEL_REPORT
try
{
if (m_logger->GetFileName().empty())
return;
CApplication app;
CWorkbook exlBook;
CWorkbooks exlBooks;
CWorksheet exlSheet;
CWorksheets exlSheets;
CChart chart;
CRange range;
CChartObjects chartobjects;
LPDISPATCH lpDisp;
COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
// Start Excel and get the Application object.
if(!app.CreateDispatch("Excel.Application"))
{
ERROR_MESSAGEBOX("Couldn't start Excel and get an application 0bject");
return;
}
app.put_Visible(TRUE);
// Get Workbooks collection.
lpDisp = app.get_Workbooks();
ASSERT(lpDisp);
// Attach the IDispatch pointer
exlBooks.AttachDispatch(lpDisp);
exlBook = exlBooks.Add(covOptional);
// Get sheets.
lpDisp = exlBook.get_Sheets();
ASSERT(lpDisp);
exlSheets.AttachDispatch(lpDisp);
lpDisp = exlSheets.get_Item( COleVariant((short)(1)) );
ASSERT(lpDisp);
// Attach the lpDisp pointer to a Worksheet object.
exlSheet.AttachDispatch(lpDisp);
const std::map<tstring, Poco::SharedPtr<CComponent>>& componentMap = m_logger->GetComponents();
char charPos[32];
int nSize = 0;
if (componentMap.size() > 0)
{
std::map<tstring, Poco::SharedPtr<CComponent>>::const_iterator ite = componentMap.begin();
int nIndex = 3;
while (ite != componentMap.end())
{
if (ite->second->GetTraceLogs().size() > 0)
{
sprintf(charPos, "A%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp); // Attach the IDispatch pointer
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(ite->second->GetComponentName().c_str()));
range.put_Value2(COleVariant(ite->second->GetComponentName().c_str()));
int nTrace = 0, nNotification = 0, nError = 0, nInformation = 0, nDebug = 0, nCritical = 0, nWarning = 0, nFatal = 0;
if (nSize == 0)
{
nTrace = 10;
nNotification = 10;
nError = 5, nInformation = 50, nDebug = 2, nCritical = 2, nWarning = 9, nFatal = 1;
}
else if (nSize == 1)
{
nTrace = 10;
nNotification = 25;
nError = 4, nInformation = 36, nDebug = 5, nCritical = 0, nWarning = 20, nFatal = 1;
}
else if (nSize == 2)
{
nTrace = 0;
nNotification = 5;
nError = 4, nInformation = 100, nDebug = 5, nCritical = 5, nWarning = 15, nFatal = 0;
}
else if (nSize == 3)
{
nTrace = 0;
nNotification = 5;
nError = 4, nInformation = 82, nDebug = 36, nCritical = 1, nWarning = 50, nFatal = 0;
}
//ite->second->getLogTypeCount(nTrace, nNotification, nError, nInformation, nDebug, nCritical, nWarning, nFatal);
//Trace
sprintf(charPos, "B%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nTrace);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Debug
sprintf(charPos, "C%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nDebug);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Information
sprintf(charPos, "D%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nInformation);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Warning
sprintf(charPos, "E%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nWarning);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Error
sprintf(charPos, "F%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nError);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Critical
sprintf(charPos, "G%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nCritical);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Notification
sprintf(charPos, "H%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nNotification);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
//Fatal
sprintf(charPos, "I%d", nIndex);
lpDisp = exlSheet.get_Range(COleVariant(charPos), COleVariant(charPos));
range.AttachDispatch(lpDisp);
sprintf(charPos, "%d", nFatal);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant(charPos));
range.put_Value2(COleVariant(charPos));
++nIndex;
++nSize;
}
++ite;
}
}
lpDisp = exlSheet.get_Range(COleVariant("B2"), COleVariant("B2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("TRACE"));
range.put_Value2(COleVariant("TRACE"));
lpDisp = exlSheet.get_Range(COleVariant("C2"), COleVariant("C2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("DEBUG"));
range.put_Value2(COleVariant("DEBUG"));
lpDisp = exlSheet.get_Range(COleVariant("D2"), COleVariant("D2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("INFORMATION"));
range.put_Value2(COleVariant("INFORMATION"));
lpDisp = exlSheet.get_Range(COleVariant("E2"), COleVariant("E2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("WARNING"));
range.put_Value2(COleVariant("WARNING"));
lpDisp = exlSheet.get_Range(COleVariant("F2"), COleVariant("F2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("ERROR"));
range.put_Value2(COleVariant("ERROR"));
lpDisp = exlSheet.get_Range(COleVariant("G2"), COleVariant("G2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("CRITICAL"));
range.put_Value2(COleVariant("CRITICAL"));
lpDisp = exlSheet.get_Range(COleVariant("H2"), COleVariant("H2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("NOTIFICATION"));
range.put_Value2(COleVariant("NOTIFICATION"));
lpDisp = exlSheet.get_Range(COleVariant("I2"), COleVariant("I2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.put_Value(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR), COleVariant("FATAL"));
range.put_Value2(COleVariant("FATAL"));
// The cells are populated. To start the chart,
// declare some long variables and site the chart.
long left, top, width, height;
left = 200;
top = 200;
width = 450;
height = 350;
lpDisp = exlSheet.ChartObjects(covOptional);
ASSERT(lpDisp);
chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
// for ChartObjects to the chartobjects
// object.
CChartObject chartobject = chartobjects.Add(left, top, width, height);
//defines the rectangle,
// adds a new chart at that rectangle and
// assigns its object reference to a
// ChartObject variable named chartobject
chart.AttachDispatch(chartobject.get_Chart());
sprintf(charPos, "I%d", nSize + 2);
lpDisp = exlSheet.get_Range(COleVariant("A2"), COleVariant(charPos));
// The range containing the data to be charted.
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
VARIANT var; // ChartWizard needs a Variant for the Source range.
var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
var.pdispVal = lpDisp; // Assign IDispatch pointer
chart.ChartWizard(var, // Source.
COleVariant((short)52), // Gallery: stacked column.
covOptional, // Format, use default.
COleVariant((short)1), // PlotBy: xlRows.
COleVariant((short)1), // CategoryLabels.
COleVariant((short)1), // SeriesLabels.
COleVariant((short)TRUE), // HasLegend.
COleVariant("Trace Report"), // Title.
COleVariant("Components"), // CategoryTitle.
COleVariant("Trace Count"), // ValueTitles.
covOptional // ExtraTitle.
);
range.ReleaseDispatch();
exlSheet.ReleaseDispatch();
exlSheets.ReleaseDispatch();
exlBook.ReleaseDispatch();
exlBooks.ReleaseDispatch();
chart.ReleaseDispatch();
chartobject.ReleaseDispatch();
app.ReleaseDispatch();
}
catch(COleException *e)
{
char buf[1024];
sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
}
catch(COleDispatchException *e)
{
char buf[1024];
sprintf(buf,
"COleDispatchException. SCODE: %08lx, Description: \"%s\".", (long)e->m_wCode,
(LPSTR)e->m_strDescription.GetBuffer(1024));
::MessageBox(NULL, buf, "COleDispatchException", MB_SETFOREGROUND | MB_OK);
}
catch(...)
{
::MessageBox(NULL, "General Exception caught.", "Catch-All", MB_SETFOREGROUND | MB_OK);
}
#endif
}
