报表替换Excel显示
报表还是不够灵活,不够好用。特别是当项目内容过多,显示的数值过长时候都会用##替代,而打印不出来。
而且定宽,定高,不方便调整。我们开发的辛苦,客户用的也不舒服。
双方都不爽,最后弄了个Excel,算是解决了这个问题。
先要弄个Excel模板文件,然后把数据内容逐个填充到单元格即可,客户爱拖爱调,那就是他的事了。
续上篇,调用接收参数报表类。
public
class
ReportRun extends ObjectRun
{
int g_Year;
PurchYearCollectTable g_PurchYCTable;
Array g_Arr;
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
SysExcelCells cells;
SysExcelCell cell;
SysExcelRange columns;
SysExcelRange column;
COM range,row;
FileName fileName;
int rowNum;
real pricesCount;
private void excelHead()
{
container m_Title;
;
cell = cells.item( 1 , 5 );
m_Title = this .read(cell);
cell.value(strfmt(conpeek (m_Title, 1 ),g_Year));
}
private void excelTable()
{
PurchYearCollect m_PurchYearC;
int i,arrLeng,rowN,rowR;
;
rowN = 4 ;
arrLeng = g_Arr.lastIndex() + 1 ;
for (i = 1 ;i < arrLeng;i ++ )
{
m_PurchYearC = g_Arr.value(i);
if (rowN > 5 )
{
range = sheet.range(strfmt( " A%1:N%1 " ,rowN - 1 )).comObject();
row = range.EntireRow();
row.copy();
row.insert();
}
cell = cells.item(rowN, 1 );
cell.value(m_PurchYearC.getItemName());
cell = cells.item(rowN, 2 );
cell.value(m_PurchYearC.getPrice( 1 ));
cell = cells.item(rowN, 3 );
cell.value(m_PurchYearC.getPrice( 2 ));
cell = cells.item(rowN, 4 );
cell.value(m_PurchYearC.getPrice( 3 ));
cell = cells.item(rowN, 5 );
cell.value(m_PurchYearC.getPrice( 4 ));
cell = cells.item(rowN, 6 );
cell.value(m_PurchYearC.getPrice( 5 ));
cell = cells.item(rowN, 7 );
cell.value(m_PurchYearC.getPrice( 6 ));
cell = cells.item(rowN, 8 );
cell.value(m_PurchYearC.getPrice( 7 ));
cell = cells.item(rowN, 9 );
cell.value(m_PurchYearC.getPrice( 8 ));
cell = cells.item(rowN, 10 );
cell.value(m_PurchYearC.getPrice( 9 ));
cell = cells.item(rowN, 11 );
cell.value(m_PurchYearC.getPrice( 10 ));
cell = cells.item(rowN, 12 );
cell.value(m_PurchYearC.getPrice( 11 ));
cell = cells.item(rowN, 13 );
cell.value(m_PurchYearC.getPrice( 12 ));
rowN ++ ;
}
rowN += 3 ;
rowR = rowN + 1 ;
for (i = 1 ;i < arrLeng;i ++ )
{
m_PurchYearC = g_Arr.value(i);
if (rowN > rowR)
{
range = sheet.range(strfmt( " A%1:M%1 " ,rowN - 1 )).comObject();
row = range.EntireRow();
row.copy();
row.insert();
}
cell = cells.item(rowN, 1 );
cell.value(m_PurchYearC.getItemName());
cell = cells.item(rowN, 2 );
cell.value(m_PurchYearC.getRate( 1 ));
cell = cells.item(rowN, 3 );
cell.value(m_PurchYearC.getRate( 2 ));
cell = cells.item(rowN, 4 );
cell.value(m_PurchYearC.getRate( 3 ));
cell = cells.item(rowN, 5 );
cell.value(m_PurchYearC.getRate( 4 ));
cell = cells.item(rowN, 6 );
cell.value(m_PurchYearC.getRate( 5 ));
cell = cells.item(rowN, 7 );
cell.value(m_PurchYearC.getRate( 6 ));
cell = cells.item(rowN, 8 );
cell.value(m_PurchYearC.getRate( 7 ));
cell = cells.item(rowN, 9 );
cell.value(m_PurchYearC.getRate( 8 ));
cell = cells.item(rowN, 10 );
cell.value(m_PurchYearC.getRate( 9 ));
cell = cells.item(rowN, 11 );
cell.value(m_PurchYearC.getRate( 10 ));
cell = cells.item(rowN, 12 );
cell.value(m_PurchYearC.getRate( 11 ));
cell = cells.item(rowN, 13 );
cell.value(m_PurchYearC.getRate( 12 ));
rowN ++ ;
}
excel.visible( true );
}
Container read(SysExcelCell sysExcelCell)
{
container line;
int intvalue;
real realvalue;
;
switch (sysExcelCell.value().variantType())
{
case COMVariantType::VT_EMPTY:
line += 0 ;
break ;
case COMVariantType::VT_I1:
line += sysExcelCell.value(). char ();
break ;
case COMVariantType::VT_I2:
line += sysExcelCell.value(). short ();
break ;
case COMVariantType::VT_I4:
intValue = sysExcelCell.value(). int ();
if (intValue == 0 )
{
intValue = sysExcelCell.value(). long ();
}
line += intValue;
break ;
case COMVariantType::VT_UI1:
line += sysExcelCell.value(). byte ();
break ;
case COMVariantType::VT_UI2:
line += sysExcelCell.value().uShort();
break ;
case COMVariantType::VT_UI4:
intValue = sysExceLCell.value().uInt();
if (intValue == 0 )
{
intValue = sysExcelCell.value().uLong();
}
line += intValue;
break ;
case COMVariantType::VT_R4 :
realValue = sysExcelCell.value(). float ();
line += realValue;
break ;
case COMVariantType::VT_R8 :
realValue = sysExcelCell.value(). double ();
line += realValue;
break ;
case COMVariantType::VT_DECIMAL :
realValue = sysExcelCell.value(). decimal ();
line += realValue;
break ;
case COMVariantType::VT_BSTR :
line += SysExcelCell.value().bstr();
break ;
default :
throw error(strfmt( " @SYS26908 " , sysExcelCell.value().variantType()));
}
return line;
}
private void excelImport()
{
FilenameOpen m_file;
VendParameters m_parameter;
;
select firstonly m_parameter;
m_file = m_parameter.PurchYearPrintModel;
excel = SysExcelApplication::construct();
books = excel.workbooks();
if ( ! books.open(m_file))
return ;
if ( ! books.count())
{
info( " no content! " );
return ;
}
book = books.item( 1 );
sheets = book.worksheets();
sheet = sheets.itemFromNum( 1 );
cells = sheet.cells();
}
private void PriceRedution()
{
PurchLineView m_PurchLineView;
utcDateTime m_BeginTime,m_EndTime,m_BeginYear;
Date m_BeginDate,m_EndDate;
int i,r;
real downPrice,total,lastPrice;
str m_ItemName;
MainTypeTable m_MainType;
PurchYearCollect m_PurchYear;
PurchLine m_PurchLine,t_PurchLine;
real tmpPrice,tmpQty;
;
g_Arr = new Array(Types::Class);
r = 1 ;
m_BeginYear = DateTimeUtil::newDateTime(mkdate( 1 , 1 ,g_Year), 0 );
while select m_MainType
{
m_PurchYear = new PurchYearCollect();
m_PurchYear.setItemName(m_MainType.Name);
m_BeginDate = mkdate( 1 , 1 ,g_Year);
m_EndDate = mkdate( 31 , 1 ,g_Year);
for (i = 1 ;i < 13 ;i ++ )
{
m_BeginTime = DateTimeUtil::newDateTime(m_BeginDate, 0 );
m_EndTime = DateTimeUtil::newDateTime(m_EndDate, 3600 * 24 - 1 );
total = 0 ;
downPrice = 0 ;
// while select sum(PurchQty) from m_PurchLineView
// group by m_PurchLineView.MainTypeId
// where m_PurchLineView.createdDateTime1 > m_BeginTime && m_PurchLineView.createdDateTime1 < m_EndTime
// && m_PurchLineView.MainTypeId == m_MainType.MainTypeId
// {
while select m_PurchLineView
where m_PurchLineView.createdDateTime > m_BeginTime && m_PurchLineView.createdDateTime < m_EndTime
&& m_PurchLineView.MainTypeId == m_MainType.MainTypeId
{
lastPrice = 0 ;
while select firstonly t_PurchLine order by t_PurchLine.createdDateTime desc where t_PurchLine.ItemId == m_PurchLineView.ItemId
&& t_PurchLine.createdDateTime < m_BeginYear
{
lastPrice = t_PurchLine.PurchPrice;
}
if ( ! t_PurchLine)
{
while select firstonly t_PurchLine order by t_PurchLine.createdDateTime asc
where t_PurchLine.ItemId == m_PurchLineView.ItemId
{
lastPrice = t_PurchLine.PurchPrice;
}
}
tmpPrice = m_PurchLineView.PurchPrice;
tmpQty = m_PurchLineView.PurchQty;
downPrice += (tmpPrice - lastPrice) * tmpQty;
total += tmpPrice * tmpQty;
}
// }
m_BeginDate = nextmth(m_BeginDate);
m_EndDate = endmth(nextmth(m_EndDate));
m_PurchYear.setPrice(i,downPrice);
m_PurchYear.setTotal(i,total);
}
g_Arr.value(r,m_PurchYear);
++ r;
}
}
public boolean fetch()
{
;
this .excelImport();
this .excelHead();
this .excelTable();
throw "" ; // 出处抛出空异常,报表最后就不显示
return false ;
}
public void init()
{
PurYearClass m_PurYear;
;
super();
m_PurYear = element.args().caller();
g_Year = m_PurYear.getYear();
Title.text(strfmt( " @SYS1303 " ,g_Year));
this .PriceRedution(); // 技术月降价汇总的方法
}
}
{
int g_Year;
PurchYearCollectTable g_PurchYCTable;
Array g_Arr;
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
SysExcelCells cells;
SysExcelCell cell;
SysExcelRange columns;
SysExcelRange column;
COM range,row;
FileName fileName;
int rowNum;
real pricesCount;
private void excelHead()
{
container m_Title;
;
cell = cells.item( 1 , 5 );
m_Title = this .read(cell);
cell.value(strfmt(conpeek (m_Title, 1 ),g_Year));
}
private void excelTable()
{
PurchYearCollect m_PurchYearC;
int i,arrLeng,rowN,rowR;
;
rowN = 4 ;
arrLeng = g_Arr.lastIndex() + 1 ;
for (i = 1 ;i < arrLeng;i ++ )
{
m_PurchYearC = g_Arr.value(i);
if (rowN > 5 )
{
range = sheet.range(strfmt( " A%1:N%1 " ,rowN - 1 )).comObject();
row = range.EntireRow();
row.copy();
row.insert();
}
cell = cells.item(rowN, 1 );
cell.value(m_PurchYearC.getItemName());
cell = cells.item(rowN, 2 );
cell.value(m_PurchYearC.getPrice( 1 ));
cell = cells.item(rowN, 3 );
cell.value(m_PurchYearC.getPrice( 2 ));
cell = cells.item(rowN, 4 );
cell.value(m_PurchYearC.getPrice( 3 ));
cell = cells.item(rowN, 5 );
cell.value(m_PurchYearC.getPrice( 4 ));
cell = cells.item(rowN, 6 );
cell.value(m_PurchYearC.getPrice( 5 ));
cell = cells.item(rowN, 7 );
cell.value(m_PurchYearC.getPrice( 6 ));
cell = cells.item(rowN, 8 );
cell.value(m_PurchYearC.getPrice( 7 ));
cell = cells.item(rowN, 9 );
cell.value(m_PurchYearC.getPrice( 8 ));
cell = cells.item(rowN, 10 );
cell.value(m_PurchYearC.getPrice( 9 ));
cell = cells.item(rowN, 11 );
cell.value(m_PurchYearC.getPrice( 10 ));
cell = cells.item(rowN, 12 );
cell.value(m_PurchYearC.getPrice( 11 ));
cell = cells.item(rowN, 13 );
cell.value(m_PurchYearC.getPrice( 12 ));
rowN ++ ;
}
rowN += 3 ;
rowR = rowN + 1 ;
for (i = 1 ;i < arrLeng;i ++ )
{
m_PurchYearC = g_Arr.value(i);
if (rowN > rowR)
{
range = sheet.range(strfmt( " A%1:M%1 " ,rowN - 1 )).comObject();
row = range.EntireRow();
row.copy();
row.insert();
}
cell = cells.item(rowN, 1 );
cell.value(m_PurchYearC.getItemName());
cell = cells.item(rowN, 2 );
cell.value(m_PurchYearC.getRate( 1 ));
cell = cells.item(rowN, 3 );
cell.value(m_PurchYearC.getRate( 2 ));
cell = cells.item(rowN, 4 );
cell.value(m_PurchYearC.getRate( 3 ));
cell = cells.item(rowN, 5 );
cell.value(m_PurchYearC.getRate( 4 ));
cell = cells.item(rowN, 6 );
cell.value(m_PurchYearC.getRate( 5 ));
cell = cells.item(rowN, 7 );
cell.value(m_PurchYearC.getRate( 6 ));
cell = cells.item(rowN, 8 );
cell.value(m_PurchYearC.getRate( 7 ));
cell = cells.item(rowN, 9 );
cell.value(m_PurchYearC.getRate( 8 ));
cell = cells.item(rowN, 10 );
cell.value(m_PurchYearC.getRate( 9 ));
cell = cells.item(rowN, 11 );
cell.value(m_PurchYearC.getRate( 10 ));
cell = cells.item(rowN, 12 );
cell.value(m_PurchYearC.getRate( 11 ));
cell = cells.item(rowN, 13 );
cell.value(m_PurchYearC.getRate( 12 ));
rowN ++ ;
}
excel.visible( true );
}
Container read(SysExcelCell sysExcelCell)
{
container line;
int intvalue;
real realvalue;
;
switch (sysExcelCell.value().variantType())
{
case COMVariantType::VT_EMPTY:
line += 0 ;
break ;
case COMVariantType::VT_I1:
line += sysExcelCell.value(). char ();
break ;
case COMVariantType::VT_I2:
line += sysExcelCell.value(). short ();
break ;
case COMVariantType::VT_I4:
intValue = sysExcelCell.value(). int ();
if (intValue == 0 )
{
intValue = sysExcelCell.value(). long ();
}
line += intValue;
break ;
case COMVariantType::VT_UI1:
line += sysExcelCell.value(). byte ();
break ;
case COMVariantType::VT_UI2:
line += sysExcelCell.value().uShort();
break ;
case COMVariantType::VT_UI4:
intValue = sysExceLCell.value().uInt();
if (intValue == 0 )
{
intValue = sysExcelCell.value().uLong();
}
line += intValue;
break ;
case COMVariantType::VT_R4 :
realValue = sysExcelCell.value(). float ();
line += realValue;
break ;
case COMVariantType::VT_R8 :
realValue = sysExcelCell.value(). double ();
line += realValue;
break ;
case COMVariantType::VT_DECIMAL :
realValue = sysExcelCell.value(). decimal ();
line += realValue;
break ;
case COMVariantType::VT_BSTR :
line += SysExcelCell.value().bstr();
break ;
default :
throw error(strfmt( " @SYS26908 " , sysExcelCell.value().variantType()));
}
return line;
}
private void excelImport()
{
FilenameOpen m_file;
VendParameters m_parameter;
;
select firstonly m_parameter;
m_file = m_parameter.PurchYearPrintModel;
excel = SysExcelApplication::construct();
books = excel.workbooks();
if ( ! books.open(m_file))
return ;
if ( ! books.count())
{
info( " no content! " );
return ;
}
book = books.item( 1 );
sheets = book.worksheets();
sheet = sheets.itemFromNum( 1 );
cells = sheet.cells();
}
private void PriceRedution()
{
PurchLineView m_PurchLineView;
utcDateTime m_BeginTime,m_EndTime,m_BeginYear;
Date m_BeginDate,m_EndDate;
int i,r;
real downPrice,total,lastPrice;
str m_ItemName;
MainTypeTable m_MainType;
PurchYearCollect m_PurchYear;
PurchLine m_PurchLine,t_PurchLine;
real tmpPrice,tmpQty;
;
g_Arr = new Array(Types::Class);
r = 1 ;
m_BeginYear = DateTimeUtil::newDateTime(mkdate( 1 , 1 ,g_Year), 0 );
while select m_MainType
{
m_PurchYear = new PurchYearCollect();
m_PurchYear.setItemName(m_MainType.Name);
m_BeginDate = mkdate( 1 , 1 ,g_Year);
m_EndDate = mkdate( 31 , 1 ,g_Year);
for (i = 1 ;i < 13 ;i ++ )
{
m_BeginTime = DateTimeUtil::newDateTime(m_BeginDate, 0 );
m_EndTime = DateTimeUtil::newDateTime(m_EndDate, 3600 * 24 - 1 );
total = 0 ;
downPrice = 0 ;
// while select sum(PurchQty) from m_PurchLineView
// group by m_PurchLineView.MainTypeId
// where m_PurchLineView.createdDateTime1 > m_BeginTime && m_PurchLineView.createdDateTime1 < m_EndTime
// && m_PurchLineView.MainTypeId == m_MainType.MainTypeId
// {
while select m_PurchLineView
where m_PurchLineView.createdDateTime > m_BeginTime && m_PurchLineView.createdDateTime < m_EndTime
&& m_PurchLineView.MainTypeId == m_MainType.MainTypeId
{
lastPrice = 0 ;
while select firstonly t_PurchLine order by t_PurchLine.createdDateTime desc where t_PurchLine.ItemId == m_PurchLineView.ItemId
&& t_PurchLine.createdDateTime < m_BeginYear
{
lastPrice = t_PurchLine.PurchPrice;
}
if ( ! t_PurchLine)
{
while select firstonly t_PurchLine order by t_PurchLine.createdDateTime asc
where t_PurchLine.ItemId == m_PurchLineView.ItemId
{
lastPrice = t_PurchLine.PurchPrice;
}
}
tmpPrice = m_PurchLineView.PurchPrice;
tmpQty = m_PurchLineView.PurchQty;
downPrice += (tmpPrice - lastPrice) * tmpQty;
total += tmpPrice * tmpQty;
}
// }
m_BeginDate = nextmth(m_BeginDate);
m_EndDate = endmth(nextmth(m_EndDate));
m_PurchYear.setPrice(i,downPrice);
m_PurchYear.setTotal(i,total);
}
g_Arr.value(r,m_PurchYear);
++ r;
}
}
public boolean fetch()
{
;
this .excelImport();
this .excelHead();
this .excelTable();
throw "" ; // 出处抛出空异常,报表最后就不显示
return false ;
}
public void init()
{
PurYearClass m_PurYear;
;
super();
m_PurYear = element.args().caller();
g_Year = m_PurYear.getYear();
Title.text(strfmt( " @SYS1303 " ,g_Year));
this .PriceRedution(); // 技术月降价汇总的方法
}
}