Table轻松实现报表 转载
Table轻松实现报表
最近做了十几个报表模块，对Table控件颇有心得，与大家分享一下
首先看看为什么要使用Table控件
看一下这个报表，各位可能要笑了，这个用DataGrid做就可以了
但问题是数据并不是直接从数据库取出来的，要经过程序的多重循环统计，这个表在数据库中没有原形，所以要使用DataTable 就要重新构造表结构，很烦人地: P
而使用Table 就容易的多了，构造表用两层循环，外层循环生成TableRow，内层循环再循环统计出数据然后把数据生成TableCell添加入TableRow
下面再看看
这个用于分类统计，怎么样，你的DataGrid是否能如此自由的输出
动态生成列
怎么样，是否你的程序也需要这种更容易控制的输出呢
优势与劣势
注意(MSDN) 通过编程方式对表行或单元格进行的添加或修改，不会在向服务器执行发送操作后仍继续保留。表行和单元格是其自身的控件，而不是 Table 控件的属性。对表行或单元格进行的更改必须在向服务器进行各次发送之后重建。如果需要进行大量修改，则使用 DataList 或 DataGrid 控件而不是 Table 控件。
不保存ViewState，性能没话说，缺点是无法交互。各取所需吧
下面看看Table 的用法
Table本身没有方法为自己添加行，添加行的操作是这样的
TableRow trow = new TableRow();//创建TableRow对象
Table.Rows.Add(trow);//将对象添加到Table的Rows 集合
只添加行是没有意义的，下面给行添加单元格
TableCell tcell = new TableCell();//创建
tcell.Text = "测试单元格";
trow.Cells.Add(tcell);//添加入Cells集合
这就是所有操作。。。
下面是一点心得
对于列比较多的表但是生成新TableCell就够受了，用如下函数
public static TableCell newCell(string Text)
{
TableCell cell=new TableCell();
if(Text!="0"&&Text!="0.00") //为了过滤0，满屏幕的0没准会把你的客户吓跑
cell.Text=Text;
else
cell.Text="";
return cell;
}
public static TableCell newCell(string Text,string ToolTip) //重载函数，对于比较长的表给单元格添加ToolTip不会让你的客户手足无措
{
TableCell cell=new TableCell();
if(Text!="0"&&Text!="0.00")
cell.Text=Text;
else
cell.Text="";
cell.ToolTip=ToolTip;
return cell;
}
public static TableCell newCell(string Text,string ToolTip,System.Web.UI.WebControls.HorizontalAlign align) //还没找到更好的控制整列水对齐方式的方法: (
{
TableCell cell=new TableCell();
if(Text!=""&&Text!="0.00")
{
cell.Text=Text;
cell.HorizontalAlign=align;
}
else
cell.Text="";
cell.ToolTip=ToolTip;
return cell;
}
private void BtnRefresh_Click(object sender, System.EventArgs e)
{
DateTime FromDate; DateTime ToDate; if(ChkDateFrom.Checked) FromDate=new DateTime(Int32.Parse(DdlYear.SelectedValue),Int32.Parse(DdlMonth.SelectedValue),Int32.Parse(DdlDay.SelectedValue)); else FromDate=new DateTime(0); if(ChkDateTo.Checked) ToDate=new DateTime(Int32.Parse(DdlYear1.SelectedValue),Int32.Parse(DdlMonth1.SelectedValue),Int32.Parse(DdlDay1.SelectedValue)); else ToDate=DateTime.Now; string strContract1; string strContract2=""; if(ChkAll.Checked)
{
strContract1="select ContractNum,ContractName,UserName,PersonName,AddDay From Contract order by ContractNum";
} else { strContract1="select ContractNum,ContractName,UserName,PersonName,AddDay From Contract where ContractNum='"+TxtContractNum.Text+"' order by ContractNum"; strContract2=" and ContractNum='"+TxtContractNum.Text+"'"; } string strcmd="select ContractNum,ClassID,GoodsID,ExportDate as [Date],TenancyValue as [Value] "; strcmd=strcmd+" from TenancyExportDetail"; strcmd=strcmd+" where ExportDate <= #"+ToDate.ToShortDateString()+"# and Effect=0 and ClassID is not null and GoodsID is not null"+strContract2; strcmd=strcmd+" union"; strcmd=strcmd+" select ContractNum,ClassID,GoodsID,ImportDate as [Date],(-ReturnValue) as [Value] "; strcmd=strcmd+" from TenancyImportDetail"; strcmd=strcmd+" where ImportDate <= #"+ToDate.ToShortDateString()+"# and (ReturnValueType=0 or ReturnValueType=3) and Effect=0 and ClassID is not null and GoodsID is not null"+strContract2; strcmd=strcmd+" ORDER BY ClassID, GoodsID, [Date],[Value] DESC"; DataTable dtTenancy=user.execsearch(strcmd); DataTable dtTenancyExport=user.execsearch("select ContractNum,ClassID,GoodsID,TenancyValue,ExportDate from TenancyExportDetail where Effect=0 and ExportDate <= #"+ToDate.ToShortDateString()+"# and ExportDate > #"+FromDate.ToShortDateString()+"#"+strContract2); DataTable dtTenancyImport=user.execsearch("select ContractNum,ClassID,GoodsID,ReturnValueType,ReturnValue,PayForType,PayForPrice,ImportDate from TenancyImportDetail where Effect=0 and ReturnValue<>0 and ImportDate <= #"+ToDate.ToShortDateString()+"# and ImportDate >= #"+FromDate.ToShortDateString()+"#"+strContract2); DataTable dtGoods=user.execsearch("select ClassID,GoodsID,GoodsName,CTYPE,UNIT,Worth,InOldRate from GoodsDetail order by ClassID,GoodsID"); DataTable dtContract=user.execsearch(strContract1); DataTable dtContractPrice=user.execsearch("select ContractNum,ClassID,GoodsID,Price,Worth from ContractPrice"); DataTable dtPayDetail=user.execsearch("select ContractNum,Pay,PayType from PayDetail where PayDate > #"+FromDate.ToShortDateString()+"# and PayDate <= #"+ToDate.ToShortDateString()+"#"); foreach(DataRow r in dtContract.Rows) { //首行标示符 bool firstline=true; //输出变量 int TotalReturnValue=0; int TotalTenancyValue=0; int TotalScrapValue=0; int TotalShatterValue=0; int TotalLostValue=0; int TotalInuse=0; float TotalTenancyCost=0; float TotalScrap=0; float TotalShatter=0; float TotalLost=0; float TotalRepairPay=0; float OtherCost=0; float Yingshou=0; float Payed=0; int AddDay=Int32.Parse(r["AddDay"].ToString()); DataTable dtContractGoods=user.execsearch("select Distinct (ClassID+'&'+GoodsID) from TenancyImportDetail where ClassID is not null and GoodsID is not null and Effect=0 and ContractNum='"+r["ContractNum"].ToString()+"' and ImportDate <= #"+ToDate.ToShortDateString()+"# union select Distinct (ClassID+'&'+GoodsID) from TenancyExportDetail where ClassID is not null and GoodsID is not null and Effect=0 and ExportDate <= #"+ToDate.ToShortDateString()+"# and ContractNum='"+r["ContractNum"].ToString()+"'"); TableRow tRow1=new TableRow(); tRow1.Cells.Add(newCell(r["ContractNum"].ToString())); tRow1.Cells.Add(newCell(r["ContractName"].ToString())); tRow1.Cells.Add(newCell(r["UserName"].ToString())); tRow1.Cells[0].RowSpan=dtContractGoods.Rows.Count+1; tRow1.Cells[1].RowSpan=dtContractGoods.Rows.Count+1; tRow1.Cells[2].RowSpan=dtContractGoods.Rows.Count+1; Table1.Rows.Add(tRow1); foreach(DataRow rT in dtContractGoods.Rows) { string ClassID=((string)(rT[0].ToString().Split('&').GetValue(0))); string GoodsID=((string)(rT[0].ToString().Split('&').GetValue(1))); string GoodsName=((DataRow)(dtGoods.Select("ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'").GetValue(0)))["GoodsName"].ToString()+"["+((DataRow)(dtGoods.Select("ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'").GetValue(0)))["CTYPE"].ToString()+"]"; float Worth=0; float Price=0; float TenancyCost=0; DataRow[] WorthRow=dtContractPrice.Select("ContractNum='"+r["ContractNum"].ToString()+"' and ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'"); if(WorthRow.Length>0) { Worth=float.Parse(((DataRow)(WorthRow.GetValue(0)))["Worth"].ToString()); Price=float.Parse(((DataRow)(WorthRow.GetValue(0)))["Price"].ToString()); } else { Worth=float.Parse(((DataRow)(dtGoods.Select("ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'").GetValue(0)))["Worth"].ToString()); Price=float.Parse(((DataRow)(dtGoods.Select("ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'").GetValue(0)))["InOldRate"].ToString()); } TableRow tRow2; if(firstline) { tRow2=tRow1; firstline=false; } else { tRow2=new TableRow(); Table1.Rows.Add(tRow2); } int GoodTenancyValue=0; foreach(DataRow r2 in dtTenancyExport.Select("ContractNum='"+r["ContractNum"].ToString()+"' and ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'")) { GoodTenancyValue+=Int32.Parse(r2["TenancyValue"].ToString()); } TotalTenancyValue+=GoodTenancyValue; //租费统计 int Inuse=0; foreach(DataRow r2 in dtTenancy.Select("ContractNum='"+r["ContractNum"].ToString()+"' and ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'")) { DateTime TenancyDate; int TValue=Int32.Parse(r2["Value"].ToString()); if(Inuse<=0&&TValue<0) continue; if(TValue+Inuse<=0) TValue=-Inuse; if((DateTime)r2["Date"]<FromDate) TenancyDate=FromDate; else TenancyDate=DateTime.Parse(((DateTime)(r2["Date"])).ToShortDateString()); int Days=((TimeSpan)(ToDate-TenancyDate)).Days; if(TValue>0) Days+=1+AddDay; Inuse+=TValue; TenancyCost+=Price*Days*TValue;// LoopCount++; } TotalInuse+=Inuse; //赔偿 int GoodScrapValue=0; int GoodShatterValue=0; int GoodReturnValue=0; int GoodLostValue=0; float ScrapCost=0; float RepairCost=0; float ShatterCost=0; float LostCost=0; foreach(DataRow r2 in dtTenancyImport.Select("ContractNum='"+r["ContractNum"].ToString()+"' and ClassID='"+ClassID+"' and GoodsID='"+GoodsID+"'")) { switch (r2["ReturnValueType"].ToString()) { case "1": if(r2["PayForType"].ToString()=="3") ScrapCost+=Int32.Parse(r2["ReturnValue"].ToString())*Worth*float.Parse(r2["PayForPrice"].ToString())/100; else RepairCost+=Int32.Parse(r2["ReturnValue"].ToString())*float.Parse(r2["PayForPrice"].ToString()); GoodScrapValue+=Int32.Parse(r2["ReturnValue"].ToString()); break; case "2": ShatterCost+=Int32.Parse(r2["ReturnValue"].ToString())*Worth*float.Parse(r2["PayForPrice"].ToString())/100; GoodShatterValue+=Int32.Parse(r2["ReturnValue"].ToString()); break; case "3": LostCost+=Int32.Parse(r2["ReturnValue"].ToString())*Worth*float.Parse(r2["PayForPrice"].ToString())/100; GoodLostValue+=Int32.Parse(r2["ReturnValue"].ToString()); break; } } TotalScrapValue+=GoodScrapValue; TotalShatterValue+=GoodShatterValue; TotalLostValue+=GoodLostValue; TotalScrap+=ScrapCost; TotalShatter+=ShatterCost; TotalRepairPay+=RepairCost; TotalLost+=LostCost; TotalTenancyCost+=TenancyCost; tRow2.Cells.Add(newCell(GoodsName)); tRow2.Cells.Add(newCell(GoodTenancyValue.ToString(),"出租量")); tRow2.Cells.Add(newCell(GoodReturnValue.ToString(),"返还量")); tRow2.Cells.Add(newCell(GoodScrapValue.ToString(),"损坏量")); tRow2.Cells.Add(newCell(GoodShatterValue.ToString(),"报废量")); tRow2.Cells.Add(newCell(GoodLostValue.ToString(),"丢失量")); tRow2.Cells.Add(newCell(Inuse.ToString(),"在用量")); tRow2.Cells.Add(newCell(TenancyCost.ToString("F2"),"租费")); tRow2.Cells.Add(newCell(ScrapCost.ToString("F2"),"损坏赔偿")); tRow2.Cells.Add(newCell(ShatterCost.ToString("F2"),"报废赔偿")); tRow2.Cells.Add(newCell(LostCost.ToString("F2"),"丢失赔偿")); tRow2.Cells.Add(newCell(RepairCost.ToString("F2"),"修理费用")); } //其他费用及已交付费用 foreach(DataRow r2 in dtPayDetail.Select("ContractNum='"+r["ContractNum"].ToString()+"'")) { if(r2["PayType"].ToString()=="0") OtherCost+=float.Parse(r2["Pay"].ToString()); else Payed+=float.Parse(r2["Pay"].ToString()); } if(!firstline) { tRow1=new TableRow(); Table1.Rows.Add(tRow1); } tRow1.BackColor=Color.Silver; Yingshou=TotalTenancyCost+TotalScrap+TotalShatter+TotalLost+TotalRepairPay+OtherCost; tRow1.Cells.Add(newCell("合计")); tRow1.Cells.Add(newCell(TotalTenancyValue.ToString(),"合计出租量")); tRow1.Cells.Add(newCell(TotalReturnValue.ToString(),"合计返还量")); tRow1.Cells.Add(newCell(TotalScrapValue.ToString(),"合计损坏量")); tRow1.Cells.Add(newCell(TotalShatterValue.ToString(),"合计报废量")); tRow1.Cells.Add(newCell(TotalLostValue.ToString(),"合计丢失量")); tRow1.Cells.Add(newCell(TotalInuse.ToString(),"合计在用量")); tRow1.Cells.Add(newCell(TotalTenancyCost.ToString("F2"),"租费合计")); tRow1.Cells.Add(newCell(TotalScrap.ToString("F2"),"损坏赔偿合计")); tRow1.Cells.Add(newCell(TotalShatter.ToString("F2"),"报废赔偿合计")); tRow1.Cells.Add(newCell(TotalLost.ToString("F2"),"丢失赔偿合计")); tRow1.Cells.Add(newCell(TotalRepairPay.ToString("F2"),"修理费合计")); tRow1.Cells.Add(newCell(OtherCost.ToString("F2"),"其他费用合计")); tRow1.Cells.Add(newCell(Yingshou.ToString("F2"),"应收合计")); tRow1.Cells.Add(newCell(Payed.ToString("F2"),"实收合计")); tRow1.Cells.Add(newCell((Yingshou-Payed).ToString("F2"),"合同欠费")); tRow1.Cells.Add(newCell(r["PersonName"].ToString(),"业务员")); } dtTenancy.Dispose(); dtTenancyExport.Dispose(); dtTenancyImport.Dispose(); dtGoods.Dispose(); dtContract.Dispose(); dtContractPrice.Dispose();so,over
