使用Aspose.Cell控件实现Excel高难度报表的生成(二)

长平狐 发布于 2012/08/22 15:50
阅读 967
收藏 1

继续在上篇《使用Aspose.Cell控件实现Excel高难度报表的生成(一)》随笔基础上,研究探讨基于模板的Aspose.cell报表实现,其中提到了下面两种报表的界面,如下所示:

 或者这样的报表格式

  


首先来分析第一种报表,这个其实还是比较固定的二维表,我们只要绑定相关的信息即可,设计模板如下所示:

 
实际生成的报表如下所示:

 

实现的代码其实不复杂,如下所示:

         private DataTable GetTable(string sql)

        {
            Database db 
=  DatabaseFactory.CreateDatabase();
            DbCommand command 
=  db.GetSqlStringCommand(sql);
            
return  db.ExecuteDataSet(command).Tables[ 0 ];
        }
        
private   void  btnAllMonthReport_Click( object  sender, EventArgs e)
        {
            
string  sql  =   @" Select [LastCount] as LC, [LastMoney] as LM, [CurrentInCount] as CIC, [CurrentInMoney] as CIM, 
                           [CurrentOutCount] as COC, [CurrentOutMoney] as COM, [CurrentCount] as CC, [CurrentMoney] as CM, 
                           YearMonth,ItemName 
                           from TB_ReportMonthCheckOut 
" ;
            DataTable dtBigType 
=  GetTable(sql  +   "  where ReportType =3 " );
            dtBigType.TableName 
=   " BigType " ;
            
if  (dtBigType.Rows.Count  ==   0 )
                
return ;

            DataTable dtItemType 
=  GetTable(sql  +   "  where ReportType =3 " );
            dtItemType.TableName 
=   " ItemType " ;

            WorkbookDesigner designer 
=   new  WorkbookDesigner();
            
string  path  =  System.IO.Path.Combine(Application.StartupPath,  " Report2-1.xls " );
            designer.Open(path);
            designer.SetDataSource(dtBigType);
            designer.SetDataSource(dtItemType);
            designer.SetDataSource(
" YearMonth " , dtBigType.Rows[ 0 ][ " YearMonth " ].ToString());
            designer.Process();

            
// Save the excel file
             string  fileToSave  =  FileDialogHelper.SaveExcel();
            
if  (File.Exists(fileToSave))
            {
                File.Delete(fileToSave);
            }
            designer.Save(fileToSave, FileFormatType.Excel2003);
            Process.Start(fileToSave);
        }

 
通过绑定两个不同的DataTable对象,然后引用他的属性即可,行会自动增加以适应实际的数据,并且对象变量&=$YearMonth也正常显示了,注意一点就是,所有使用变量的地方,都必须在一个独立的Excel单元格中,否则不能解析出来。另外上图的红色圆圈里面表示,汇总的函数,会自动根据行列的增加,自动调整引用,这真是我们需要的。 

 

 出库单的实现也差不多,实现代码如下所示:

             string  TakeOutBill  =  Path.Combine(Application.StartupPath,  " TakeOutBill.xls " );
            WorkbookDesigner designer 
=   new  WorkbookDesigner();
            designer.Open(TakeOutBill);
            designer.SetDataSource(
" TakeOutDate " , DateTime.Now.ToString( " yyyy-MM-dd " ));
            designer.SetDataSource(
" WareHouse " this .txtWareHouse.Text);
            designer.SetDataSource(
" Manager " this .txtCreator.Text);
            designer.SetDataSource(
" CostCenter " this .txtCostCenter.Text);
            designer.SetDataSource(
" Dept " this .txtDept.Text);

            
string  columns  =   " Start|int,ItemNo,ItemName,Specification,Unit,Price|decimal,Count|int " ;
            DataTable dt 
=  DataTableHelper.CreateTable(columns);
            dt.TableName 
=   " Detail " ;
            DataRow row 
=   null ;
            
for  ( int  i  =   0 ; i  <   this .lvwDetail.Items.Count; i ++ )
            {
                PurchaseDetailInfo info 
=   this .lvwDetail.Items[i].Tag  as  PurchaseDetailInfo;
                
if  (info  !=   null )
                {
                    row 
=  dt.NewRow();
                    row[
" Start " =  (i  +   1 );
                    row[
" ItemNo " =  info.ItemNo;
                    row[
" ItemName " =  info.ItemName;
                    row[
" Specification " =  info.Specification;
                    row[
" Unit " =  info.Unit;
                    row[
" Price " =  info.Price;
                    row[
" Count " =  Math.Abs(info.Quantity);
                    dt.Rows.Add(row);
                }
            }
            designer.SetDataSource(dt);
            designer.Process();
            
string  fileToSave  =  FileDialogHelper.SaveExcel();
            
if  (File.Exists(fileToSave))
            {
                File.Delete(fileToSave);
            }
            designer.Save(fileToSave, FileFormatType.Excel2003);
            Process.Start(fileToSave); 


以上报表,其实实现思路基本都差不多,相对来时,还是比较容易的,接下来设计一个比较困难的报表,需要结合Aspose.Cell一些对象来动态创建行列,并设置单元格的变量,然后填入相应的对象构造报表,另外还需要注意单元格格式的变化,如下所示的这种报表

 

这个报表初看没有太多特别的地方,难点就是他的第一行列也是变化的,因此不能通过普通的方式构建二维表,然后绑定数据源的方式,要先加载模板文件,然后操作Excel对象,把第一行的各列头部补齐,然后给下一行各单元格填入对象公式,如&=BigType.DeptName 和&=BigType.TotalMoney等内容,实现的代码如下所示:

             string  sql  =   @" Select [YearMonth], [DeptName], [ItemType], [TotalMoney]
                           from TB_ReportDeptCost 
" ;
            DataTable dt 
=  GetTable(sql);
            
if  (dt.Rows.Count  ==   0 )
                
return ;

            List
< string >  itemTypeList  =   new  List < string > ();
            List
< string >  partList  =   new  List < string > ();
            
foreach  (DataRow row  in  dt.Rows)
            {
                
string  itemType  =  row[ " ItemType " ].ToString();
                
if  ( ! itemTypeList.Contains(itemType))
                {
                    itemTypeList.Add(itemType);
                }

                
string  part  =  row[ " DeptName " ].ToString();
                
if  ( ! partList.Contains(part))
                {
                    partList.Add(part);
                }
            }

            
string  columnString  =   " DeptName " ;
            
for  ( int  i  =   0 ; i  <  itemTypeList.Count; i ++ )
            {
                columnString 
+=   string .Format( " ,TotalMoney{0}|decimal " , i);
            }
            DataTable dtBigType 
=  DataTableHelper.CreateTable(columnString);
            dtBigType.TableName 
=   " BigType " ;
            
foreach  ( string  part  in  partList)
            {
                DataRow row 
=  dtBigType.NewRow();
                row[
" DeptName " =  part;
                
for  ( int  i  =   0 ; i  <  itemTypeList.Count; i ++ )
                {
                    
string  itemType  =  itemTypeList[i];
                    DataRow[] rowSelect 
=  dt.Select( string .Format( " DeptName='{0}' AND ItemType='{1}' " , part, itemType));
                    
if  (rowSelect.Length  >   0 )
                    {
                        row[
" TotalMoney "   +  i.ToString()]  =  rowSelect[ 0 ][ " TotalMoney " ];
                    }
                }
                dtBigType.Rows.Add(row);
            }

            WorkbookDesigner designer 
=   new  WorkbookDesigner();
            
string  path  =  System.IO.Path.Combine(Application.StartupPath,  " Report1.xls " );
            designer.Open(path);

            Aspose.Cells.Worksheet w 
=  designer.Workbook.Worksheets[ 0 ];
            
// 先设置标题项目:如大修件,日常备件等
             int  rowIndex  =   2 ; // 第三行为标题
            Aspose.Cells.Style style  =  w.Cells[rowIndex  +   1 1 ].Style; // 继承数字栏目的样式
            style.Number  =   4 ; // 对应格式是#,##0.00
            Aspose.Cells.Style boldStyle  =  w.Cells[rowIndex,  0 ].Style; // 继承开始栏目的样式
             for  ( int  i  =   0 ; i  <  itemTypeList.Count; i ++ )
            {
                w.Cells[rowIndex, i 
+   1 ].PutValue(itemTypeList[i]);
                w.Cells[rowIndex, i 
+   1 ].Style  =  boldStyle;
                w.Cells[rowIndex 
+   1 , i  +   1 ].PutValue( " &=BigType.TotalMoney "   +  i.ToString());
                w.Cells[rowIndex 
+   1 , i  +   1 ].Style  =  style;
            }

            
// 添加合计行
            w.Cells[rowIndex, itemTypeList.Count  +   1 ].PutValue( " 合计 " );
            w.Cells[rowIndex, itemTypeList.Count 
+   1 ].Style  =  boldStyle;
            w.Cells[rowIndex 
+   1 , itemTypeList.Count  +   1 ].PutValue( string .Format( " &=&=SUM(B{{r}}:{0}{{r}}) " , GetChar(itemTypeList.Count  +   1 )));
            w.Cells[rowIndex 
+   1 , itemTypeList.Count  +   1 ].Style  =  style;

            designer.SetDataSource(dtBigType);
            designer.SetDataSource(
" YearMonth " , dt.Rows[ 0 ][ " YearMonth " ].ToString());
            designer.Process();

            
// Save the excel file
             string  fileToSave  =  FileDialogHelper.SaveExcel();
            
if  (File.Exists(fileToSave))
            {
                File.Delete(fileToSave);
            }
            designer.Save(fileToSave, FileFormatType.Excel2003);
            Process.Start(fileToSave);  

 


原文链接:http://www.cnblogs.com/wuhuacong/archive/2011/02/23/1962347.html
加载中
返回顶部
顶部