使用asp.net dateaable 做统计表并且网页上显示的表和导出的Excel 一模一样根据领导看报表需求两个报表和一起（add algorithm）

1.首先使用。net 控件

做两个个表初始属性（

tabBillAdd.Visible = false;
tabBill.Visible = false;

）

2------------------------------------------------------------------------------------------------------------------

接着使用 xml 做魔板

-----------------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8" ?>
<table procname="Biz_Item_GetJyzJSZCount" title="{0}{1}加油站建设汇总表" procnameTZ="Biz_Item_GetJyzJSTZCount">
<tr type="rowtitle">
<td width="5%" rowspan="4" height="10%">单位</td>
<td colspan="10" width="100%" TZ="单站投资（万元/座）">加油站座数（座）</td>

</tr>
<tr type="rowtitle">
<td width="4%" rowspan="3">合计</td>
<td colspan="9">新开工项目</td>

</tr>
<tr type="rowtitle">
<td width="5%" rowspan="2">小计</td>
<td rowspan ="2" width="5%">新增</td>

<td colspan="5" width="5%" left="1">其中：</td>
<td width="5%" rowspan ="2" >扩建</td>
<td width="5%" rowspan ="2">迁建</td>

</tr>
<tr type="rowtitle">

<td width="5%">新建 </td>
<td width="5%">收购 </td>
<td width="5%">控股 </td>
<td width="5%">参股 </td>
<td width="5%">租赁 </td>

</tr>
<tr type="rowdata">
<td keyname="regionname"></td>
<td keyname="Total"></td>
<td keyname="SubTatal" ></td>
<td keyname="NewAdd" ></td>
<td keyname="NewBuild"></td>
<td keyname="Purchase"></td>
<td keyname="Holding"></td>
<td keyname="Participation"></td>
<td keyname="Rent"></td>
<td keyname="Extend"></td>
<td keyname="Relocation"></td>

</tr>
</table>

---------------------------------------------------------------------------------------------------

魔板中文部分是标的title 引文部分是根据存储过程一一对应的列明。循环数据用。

3.---------------------------------------------------------------------------------------------------------------

开始循环数据画出这个表

private void CreateTable()
{
tabBillAdd.Visible = false;
tabBill.Visible = false;
if (String.IsNullOrEmpty(Hi_StatName.Value))
return;
if (JYZJSQuery.strYear != null)
{
lblTitle.Text = String.Format(JYZJSQuery.strYear.ToString() + "年加油站建设座数汇总表");
lblTitle.Visible = false;
}

tabBill.Rows.Clear();

TableRow TR = new TableRow();
TableCell TC = new TableCell();
XmlDocument xmlDoc = new XmlDocument();
System.Web.UI.HtmlControls.HtmlAnchor HA = new System.Web.UI.HtmlControls.HtmlAnchor();

#region 加载XML

string strXmlPath = Server.MapPath("Config/" + Hi_StatName.Value + ".xml");
xmlDoc.Load(strXmlPath);
#endregion

#region 创建表头
XmlNodeList xns = xmlDoc.SelectNodes("/table/tr[@type='rowtitle']");

//添加标题
TR = new TableRow();
TC = new TableCell();
TR.Cells.Add(TC);
TC = new TableCell();
TC.Text = lblTitle.Text.Trim();
TC.ColumnSpan = 10;
TC.HorizontalAlign = HorizontalAlign.Center;
TC.VerticalAlign = VerticalAlign.Top;
TC.Font.Size=20;
TR.Cells.Add(TC);
tabBill.Rows.Add(TR);

TR = new TableRow();
TC = new TableCell();
TR.Cells.Add(TC);
TC = new TableCell();
TC.Height = 30;
TC.ColumnSpan = 10;
TC.HorizontalAlign = HorizontalAlign.Center;
TC.VerticalAlign = VerticalAlign.Top;
TC.Font.Size = 20;
TR.Cells.Add(TC);
tabBill.Rows.Add(TR);

foreach (XmlNode xn in xns)
{

TR = new TableRow();
TR.CssClass = "rowtitle";

foreach (XmlNode xnl in xn.ChildNodes)
{
TC = new TableCell();
TC.CssClass = "celltitle";
TC.Style.Add("text-align", "center");
TC.BorderColor = System.Drawing.Color.LightBlue;
TC.BorderWidth = 1;

if (!Object.Equals(xnl.Attributes["rowspan"], null))
{
TC.RowSpan = Convert.ToInt32(xnl.Attributes["rowspan"].Value);
}

if (!Object.Equals(xnl.Attributes["colspan"], null))
{
TC.ColumnSpan = Convert.ToInt32(xnl.Attributes["colspan"].Value);
}
if (!Object.Equals(xnl.Attributes["width"], null))
{
if (xnl.Attributes["width"].Value.ToLower().Contains("px"))
{
TC.Width = Unit.Pixel(int.Parse(xnl.Attributes["width"].Value.Split('p')[0]));
}
else
{
TC.Width = Unit.Percentage(double.Parse(xnl.Attributes["width"].Value.TrimEnd('%')));
}
}
if (!Object.Equals(xnl.Attributes["left"], null))
{
TC.HorizontalAlign = HorizontalAlign.Left;
TC.Style.Add(HtmlTextWriterStyle.TextAlign, "left");
}

TC.Text = xnl.InnerText.ToString();
TR.Cells.Add(TC);
}

tabBill.Rows.Add(TR);

}

#endregion

#region 查询数据

//procname 名称是从 xml 读的
System.Data.DataTable dt = null;
string strProcName = xmlDoc.SelectSingleNode("/table").Attributes["procname"].InnerText.ToString();

string strTitle = xmlDoc.SelectSingleNode("/table").Attributes["title"].InnerText.ToString();

//这是存储过程 dt 是 datatable 数据结构

dt = StatHelper.GetQueryResultByProc(strProcName, JYZJSQuery.queryParas, Hi_StatName.Value);

if (Object.Equals(dt, null) || dt.Rows.Count == 0)
{
TR = new TableRow();
TC = new TableCell();
TR.CssClass = "rowcontent";
TC.CssClass = "celldata";
TC.Text = "没有符合条件的数据";
TC.ColumnSpan = 11;
TR.Cells.Add(TC);
tabBill.Rows.Add(TR);
return;
}
#endregion

#region 创建行
xns = xmlDoc.SelectNodes("/table/tr[@type='rowdata']/td");
int iRowIndex = tabBill.Rows.Count;
//string strFirstCellText = "";
//int iTemp = 0;
int rowNum = 1;//行号
string preName = "";
for (int i = 0; i < dt.Rows.Count; i++)
{

TR = new TableRow();
if (i % 2 == 0)
TR.CssClass = "rowcontent";
else
TR.CssClass = "rowcontentalt";

//iTemp = 0;

foreach (XmlNode xn in xns)
{
//iTemp++;
TC = new TableCell();
TC.BorderColor = System.Drawing.Color.LightBlue;
TC.BorderWidth = 1;

if (i % 2 == 0)
TC.CssClass = "celldata";
else
TC.CssClass = "celldataalt";

TC.Text = "";

string[] keyNameList = xn.Attributes["keyname"].InnerText.ToString().Split(',');

if (keyNameList.Length > 0)
{
if (Object.Equals(xn.Attributes["DataFormatString"], null))
{
foreach (string temp in keyNameList)
{
if (temp.Equals("Total") || temp.Equals("SubTatal") || temp.Equals("NewAdd"))
{
continue;
}

if (temp != "")
if (temp.Equals("regionname") || temp.Equals("NewBuild") || temp.Equals("Purchase") || temp.Equals("Holding") || temp.Equals("Participation") || temp.Equals("Rent") || temp.Equals("Extend") || temp.Equals("Relocation"))
{
int rowNumCount = 0;
if (dt.Rows[i][temp].ToString() == "") { TC.Text = dt.Rows[i][temp].ToString(); }
else if (dt.Rows[i][temp].ToString().Split('.').Length > 2)
{
TC.Text = dt.Rows[i][temp].ToString().Split('.')[0] + "." + dt.Rows[i][temp].ToString().Split('.')[1];
}

TC.Text = dt.Rows[i][temp].ToString();
}

else
{
TC.Text += dt.Rows[i][temp].ToString();
}

}
}

}

TC.Text = TC.Text == "0" ? "" : TC.Text;
TR.Cells.Add(TC);
}

tabBill.Rows.Add(TR);

}
AddTabSum();
#endregion
}

-------------------------------------------------------------

算合计（也可以在存储过程中算好，但这一步也是必不可缺少的）

private void AddTabSum()
{
int titleRowCount = 0;
foreach (TableRow item in tabBill.Rows)
{
titleRowCount++;
//跳过表头
if (titleRowCount<7)
{
continue;
}
//合计=all

item.Cells[1].Text = (Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToInt32(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text) + Convert.ToInt32(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text) + Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text) + Convert.ToInt32(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text)).ToString();

//小计不加迁建,kuojian
item.Cells[2].Text = (Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToInt32(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text) + Convert.ToInt32(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text) + Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text) + Convert.ToInt32(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text)).ToString();

//新增
item.Cells[3].Text = (Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text)).ToString();

}

//BottomTotal
BottomTotal();

}

-------------------------------------------------------------------------------------------------------------------------

算底部算法

private void BottomTotal()
{
int total = 0, subTotal = 0, newAdd = 0, newBuild = 0,purchase=0,holding=0,participation=0,rent=0,extend=0,Relocation=0;
int totalQN = 0, subTotalQN = 0, newAddQN = 0, newBuildQN = 0, purchaseQN = 0, holdingQN = 0, participationQN = 0, rentQN = 0, extendQN = 0, RelocationQN = 0;
int totalQW = 0, subTotalQW = 0, newAddQW = 0, newBuildQW = 0, purchaseQW = 0, holdingQW = 0, participationQW = 0, rentQW = 0, extendQW = 0, RelocationQW = 0;

TableRow TR = null;
TableCell TC = null;
//区域
List<Biz_QqItemInfo> listQ = bll.GetModelRegionName();

if (listQ == null)
{
return;
}
var queryStrIn = from quIn in listQ
where quIn.RegionType == 1
select quIn.RegionName;

var queryStrOut = from quOut in listQ
where quOut.RegionType == 2
select quOut.RegionName;
//合计区域纵横
for (int i = 0; i <6; i++)
{

TR = new TableRow();
if (i % 2 == 0)
TR.CssClass = "rowcontent";
else
TR.CssClass = "rowcontentalt";
for (int j = 0; j < 11; j++)
{
TC = new TableCell();
TC.BorderColor = System.Drawing.Color.LightBlue;
TC.BorderWidth = 1;
if (i % 2 == 0)
TC.CssClass = "celldata";
else
TC.CssClass = "celldataalt";

if (i == 1 && j == 0)
{
TC.Text = "合计";
}
if (i == 2 & j == 0)
{
TC.Text = "区外";
}
if (i == 3 & j == 0)
{
TC.Text = "区内";
}

if (i == 4 & j == 0)
{
TC.Text = "区内纵横";
}
if (i == 5 & j == 0)
{
TC.Text = "区内交界";
}

TR.Cells.Add(TC);

}
tabBill.Rows.Add(TR);

}
int titleRowCount = 0;
foreach (TableRow item in tabBill.Rows)
{

titleRowCount++;
//跳过表头
if (titleRowCount < 7)
{
continue;
}

//合计
total += Convert.ToInt32(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotal += Convert.ToInt32(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAdd += Convert.ToInt32(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuild += Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchase += Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holding += Convert.ToInt32(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participation += Convert.ToInt32(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rent += Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extend += Convert.ToInt32(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
Relocation += Convert.ToInt32(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);

//区外
if ( queryStrOut.Contains(item.Cells[0].Text.ToString()))
{
//合计
totalQW += Convert.ToInt32(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotalQW += Convert.ToInt32(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAddQW += Convert.ToInt32(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuildQW += Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchaseQW += Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holdingQW += Convert.ToInt32(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participationQW += Convert.ToInt32(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rentQW += Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extendQW += Convert.ToInt32(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
RelocationQW += Convert.ToInt32(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);
}
//区内
if (queryStrIn.Contains(item.Cells[0].Text.ToString()))
{
//合计
totalQN += Convert.ToInt32(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotalQN += Convert.ToInt32(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAddQN += Convert.ToInt32(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuildQN += Convert.ToInt32(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchaseQN += Convert.ToInt32(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holdingQN += Convert.ToInt32(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participationQN += Convert.ToInt32(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rentQN += Convert.ToInt32(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extendQN += Convert.ToInt32(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
RelocationQN += Convert.ToInt32(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);
}
}

titleRowCount = 0;
//赋值
foreach (TableRow item in tabBill.Rows)
{
titleRowCount++;
//跳过表头
if (titleRowCount < 7)
{
continue;
}

if (item.Cells[0].Text.ToString() == "合计")
{

item.Cells[1].Text = total.ToString();
item.Cells[2].Text = subTotal.ToString();
item.Cells[3].Text = newAdd.ToString();
item.Cells[4].Text = newBuild.ToString();
item.Cells[5].Text = purchase.ToString();
item.Cells[6].Text = holding.ToString();
item.Cells[7].Text = participation.ToString();
item.Cells[8].Text = rent.ToString();
item.Cells[9].Text = extend.ToString();
item.Cells[10].Text = Relocation.ToString();

}
if (item.Cells[0].Text.ToString() == "区外")
{

item.Cells[1].Text = totalQW.ToString();
item.Cells[2].Text = subTotalQW.ToString();
item.Cells[3].Text = newAddQW.ToString();
item.Cells[4].Text = newBuildQW.ToString();
item.Cells[5].Text = purchaseQW.ToString();
item.Cells[6].Text = holdingQW.ToString();
item.Cells[7].Text = participationQW.ToString();
item.Cells[8].Text = rentQW.ToString();
item.Cells[9].Text = extendQW.ToString();
item.Cells[10].Text = RelocationQW.ToString();

}
if (item.Cells[0].Text.ToString() == "区内")
{

item.Cells[1].Text = totalQN.ToString();
item.Cells[2].Text = subTotalQN.ToString();
item.Cells[3].Text = newAddQN.ToString();
item.Cells[4].Text = newBuildQN.ToString();
item.Cells[5].Text = purchaseQN.ToString();
item.Cells[6].Text = holdingQN.ToString();
item.Cells[7].Text = participationQN.ToString();
item.Cells[8].Text = rentQN.ToString();
item.Cells[9].Text = extendQN.ToString();
item.Cells[10].Text = RelocationQN.ToString();

}

//第二张表，如果不需合并表此处不需要。
CreateTableAdd();

}

---------------------------------------------------------------------

第二张表也一样

----------------------------------------------------------------

private void CreateTableAdd()
{
string tableName = string.Empty;
if (String.IsNullOrEmpty(Hi_StatName.Value))
return;
if (JYZJSQuery.strYear != null)
{
tableName = String.Format(JYZJSQuery.strYear.ToString() + "年加油站建设汇总表");
lblTitle.Visible = false;
}

tabBillAdd.Rows.Clear();

TableRow TR = new TableRow();
TableCell TC = new TableCell();
XmlDocument xmlDoc = new XmlDocument();
System.Web.UI.HtmlControls.HtmlAnchor HA = new System.Web.UI.HtmlControls.HtmlAnchor();

#region 加载XML

string strXmlPath = Server.MapPath("Config/" + Hi_StatName.Value + ".xml");
xmlDoc.Load(strXmlPath);
#endregion

#region 创建表头
XmlNodeList xns = xmlDoc.SelectNodes("/table/tr[@type='rowtitle']");

//添加标题
TR = new TableRow();
TC = new TableCell();
TR.Cells.Add(TC);
TC = new TableCell();
TC.Text = tableName;
TC.ColumnSpan = 10;
TC.HorizontalAlign = HorizontalAlign.Center;
TC.VerticalAlign = VerticalAlign.Top;
TC.Font.Size = 20;
TR.Cells.Add(TC);
tabBillAdd.Rows.Add(TR);

foreach (XmlNode xn in xns)
{

TR = new TableRow();
TR.CssClass = "rowtitle";

foreach (XmlNode xnl in xn.ChildNodes)
{
TC = new TableCell();
TC.CssClass = "celltitle";
TC.Style.Add("text-align", "center");
TC.BorderColor = System.Drawing.Color.LightBlue;
TC.BorderWidth = 1;

if (!Object.Equals(xnl.Attributes["rowspan"], null))
{
TC.RowSpan = Convert.ToInt32(xnl.Attributes["rowspan"].Value);
}

}

TC.Text = xnl.InnerText.ToString();
if (!Object.Equals(xnl.Attributes["TZ"], null))
{
TC.Text = xnl.Attributes["TZ"].Value;

}
TR.Cells.Add(TC);
}

tabBillAdd.Rows.Add(TR);

}

#endregion

#region 查询数据
System.Data.DataTable dt = null;
string strProcName = xmlDoc.SelectSingleNode("/table").Attributes["procnameTZ"].InnerText.ToString();

string strTitle = xmlDoc.SelectSingleNode("/table").Attributes["title"].InnerText.ToString();

string strQuery= JYZJSQuery.queryParas.FilterString.Replace("Q.REGIONNAMECODE", "RegionCmp");
// where 1=1 and YEAR(CREATEDDATE) = '2017' AND ITEMTYPE = 'JYZ'
strQuery = JYZJSQuery.queryParas.FilterString.Replace("YEAR(CREATEDDATE)", "B.firstDate");

dt = StatHelper.GetGcdbResultByProc(strProcName, strQuery, Hi_StatName.Value);

#region 创建行
xns = xmlDoc.SelectNodes("/table/tr[@type='rowdata']/td");
int iRowIndex = tabBillAdd.Rows.Count;
//string strFirstCellText = "";
//int iTemp = 0;
int rowNum = 1;//行号
string preName = "";
for (int i = 0; i < dt.Rows.Count; i++)
{

TR = new TableRow();
if (i % 2 == 0)
TR.CssClass = "rowcontent";
else
TR.CssClass = "rowcontentalt";

//iTemp = 0;

foreach (XmlNode xn in xns)
{
//iTemp++;
TC = new TableCell();
TC.BorderColor = System.Drawing.Color.LightBlue;
TC.BorderWidth = 1;

if (i % 2 == 0)
TC.CssClass = "celldata";
else
TC.CssClass = "celldataalt";

TC.Text = "";

string[] keyNameList = xn.Attributes["keyname"].InnerText.ToString().Split(',');

if (temp != "")
if (temp.Equals("regionname") || temp.Equals("NewBuild") || temp.Equals("Purchase") || temp.Equals("Holding") || temp.Equals("Participation") || temp.Equals("Rent") || temp.Equals("Extend") || temp.Equals("Relocation"))
{
int rowNumCount = 0;
if (dt.Rows[i][temp].ToString() == "") { TC.Text = dt.Rows[i][temp].ToString(); }
else if (dt.Rows[i][temp].ToString().Split('.').Length > 2)
{
TC.Text = dt.Rows[i][temp].ToString().Split('.')[0] + "." + dt.Rows[i][temp].ToString().Split('.')[1];
}

TC.Text = dt.Rows[i][temp].ToString();
}

else
{
TC.Text += dt.Rows[i][temp].ToString();
}

}
}

}

TC.Text = TC.Text == "0" ? "" : TC.Text;
TR.Cells.Add(TC);
}

tabBillAdd.Rows.Add(TR);

}
AddTabSumAdd();
#endregion
}

private void AddTabSumAdd()
{
int titleRowCount = 0;
foreach (TableRow item in tabBillAdd.Rows)
{
titleRowCount++;
//跳过表头
if (titleRowCount < 7)
{
continue;
}
//合计=all

item.Cells[1].Text = (Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToDecimal(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text) + Convert.ToDecimal(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text) + Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text) + Convert.ToDecimal(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text)).ToString();

//小计不加迁建
item.Cells[2].Text = (Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToDecimal(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text) + Convert.ToDecimal(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text) + Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text) + Convert.ToDecimal(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text)).ToString();

//新增
item.Cells[3].Text = (Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text) + Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text) + Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text)).ToString();

}

//BottomTotal
BottomTotalAdd();

}

//List<string> regionIN = null;
//List<string> regionOut = null;

private void BottomTotalAdd()
{
decimal total = 0, subTotal = 0, newAdd = 0, newBuild = 0, purchase = 0, holding = 0, participation = 0, rent = 0, extend = 0, Relocation = 0;
decimal totalQN = 0, subTotalQN = 0, newAddQN = 0, newBuildQN = 0, purchaseQN = 0, holdingQN = 0, participationQN = 0, rentQN = 0, extendQN = 0, RelocationQN = 0;
decimal totalQW = 0, subTotalQW = 0, newAddQW = 0, newBuildQW = 0, purchaseQW = 0, holdingQW = 0, participationQW = 0, rentQW = 0, extendQW = 0, RelocationQW = 0;

TableRow TR = null;
TableCell TC = null;
//区域
List<Biz_QqItemInfo> listQ = bll.GetModelRegionName();

if (listQ == null)
{
return;
}
var queryStrIn = from quIn in listQ
where quIn.RegionType == 1
select quIn.RegionName;

var queryStrOut = from quOut in listQ
where quOut.RegionType == 2
select quOut.RegionName;
//合计区域纵横
for (int i = 0; i < 6; i++)
{

if (i == 1 && j == 0)
{
TC.Text = "合计";
}
if (i == 2 & j == 0)
{
TC.Text = "区外";
}
if (i == 3 & j == 0)
{
TC.Text = "区内";
}

if (i == 4 & j == 0)
{
TC.Text = "区内纵横";
}
if (i == 5 & j == 0)
{
TC.Text = "区内交界";
}

TR.Cells.Add(TC);

}
tabBillAdd.Rows.Add(TR);

}
int titleRowCount = 0;
foreach (TableRow item in tabBillAdd.Rows)
{

titleRowCount++;
//跳过表头
if (titleRowCount < 7)
{
continue;
}

//合计
total += Convert.ToDecimal(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotal += Convert.ToDecimal(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAdd += Convert.ToDecimal(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuild += Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchase += Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holding += Convert.ToDecimal(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participation += Convert.ToDecimal(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rent += Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extend += Convert.ToDecimal(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
Relocation += Convert.ToDecimal(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);

//区外
if (queryStrOut.Contains(item.Cells[0].Text.ToString()))
{
//合计
totalQW += Convert.ToDecimal(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotalQW += Convert.ToDecimal(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAddQW += Convert.ToDecimal(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuildQW += Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchaseQW += Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holdingQW += Convert.ToDecimal(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participationQW += Convert.ToDecimal(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rentQW += Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extendQW += Convert.ToDecimal(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
RelocationQW += Convert.ToDecimal(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);
}
//区内
if (queryStrIn.Contains(item.Cells[0].Text.ToString()))
{
//合计
totalQN += Convert.ToDecimal(item.Cells[1].Text == "" ? "0" : item.Cells[1].Text);
//小计
subTotalQN += Convert.ToDecimal(item.Cells[2].Text == "" ? "0" : item.Cells[2].Text);
//新增
newAddQN += Convert.ToDecimal(item.Cells[3].Text == "" ? "0" : item.Cells[3].Text);
//新建
newBuildQN += Convert.ToDecimal(item.Cells[4].Text == "" ? "0" : item.Cells[4].Text);
//收购
purchaseQN += Convert.ToDecimal(item.Cells[5].Text == "" ? "0" : item.Cells[5].Text);
//控股
holdingQN += Convert.ToDecimal(item.Cells[6].Text == "" ? "0" : item.Cells[6].Text);
//参股
participationQN += Convert.ToDecimal(item.Cells[7].Text == "" ? "0" : item.Cells[7].Text);
//租赁
rentQN += Convert.ToDecimal(item.Cells[8].Text == "" ? "0" : item.Cells[8].Text);
//扩建
extendQN += Convert.ToDecimal(item.Cells[9].Text == "" ? "0" : item.Cells[9].Text);
//迁建
RelocationQN += Convert.ToDecimal(item.Cells[10].Text == "" ? "0" : item.Cells[10].Text);
}
}

titleRowCount = 0;
//赋值
foreach (TableRow item in tabBillAdd.Rows)
{
titleRowCount++;
//跳过表头
if (titleRowCount < 7)
{
continue;
}

if (item.Cells[0].Text.ToString() == "合计")
{

}
if (item.Cells[0].Text.ToString() == "区外")
{

}
if (item.Cells[0].Text.ToString() == "区内")
{

}

}
ComBindTable();

tabBill.Visible = true;
}

------------------------------------------------------------

比较关键的一歩，合并两个datatable

///合并
private void ComBindTable()
{

int tabBill_Row = 0;
int tabBillAdd_Row = 0;
int jCount = 0;
int iCount = 0;
int leftTable = tabBill.Rows.Count;
int rightTable = tabBillAdd.Rows.Count;

#region 填充左表
//如果左表小

if (rightTable > leftTable)
{
int row = rightTable - leftTable;
//创建空行，空列9
TableRow TR;
TableCell TC;
//合计区域纵横
for (int i = 0; i < row; i++)
{

TR.Cells.Add(TC);

}
tabBill.Rows.Add(TR);

}
}
#endregion

foreach (TableRow item in tabBill.Rows)
{
jCount = iCount;
tabBillAdd_Row = tabBill_Row;

if (tabBill_Row == tabBillAdd_Row)
{
for (int i = jCount; i < rightTable; i++)
{
if (jCount == tabBill_Row)
{
jCount++;

int c = tabBillAdd.Rows[i].Cells.Count;

TableCell[] lis = new TableCell[c];
for (int j = 0; j < c; j++)
{
lis[j] = tabBillAdd.Rows[i].Cells[j];

}
item.Cells.AddRange(lis);

}
else
{
break;
}

}
tabBill_Row++;
tabBillAdd_Row++;
tabBillAdd_Row++;

}
else
{
break;

}
iCount++;
}

}

------------------------------------------------------------------------------------------

输出 Excel

---------------------------------------------------------

protected void btnToExcel_Click(object sender, EventArgs e)
{
CreateTable();
Page.Response.Clear();
Page.Response.Buffer = true;
//Page.Response.Charset="GB2312";　　　　
Page.Response.ContentEncoding = System.Text.Encoding.UTF8;
//下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开　　
//filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc 　　 .xls 　　 .txt 　　.htm　　　
Page.Response.AppendHeader("Content-Disposition", "online;filename=" + HttpUtility.UrlEncode(lblTitle.Text + ".xls", System.Text.Encoding.UTF8));
//System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true); 　　
//Response.ContentType指定文件类型可以为application/ms-excel 　　 application/ms-word 　　 application/ms-txt 　　 application/ms-html 　　或其他浏览器可直接支持文档　　　
Page.Response.ContentType = "application/ms-excel";
Page.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

tabBill.RenderControl(oHtmlTextWriter);
Page.Response.Write(oStringWriter.ToString());
Page.Response.End();

//CreateTable();
//OutputExcel(tabBill, lblTitle.Text);
}

---------------------------------------------------------------

表的样子，这个是之前的（不是太好看）你画出来一定很好看。

posted @ 2017-08-31 09:54 applekingghfhfhbr 阅读(361) 评论(0) 编辑收藏举报

刷新页面返回顶部

applekingghfhfhbr

使用asp.net dateaable 做统计表并且网页上显示的表和导出的Excel 一模一样 根据领导看报表需求两个报表和一起 （add algorithm）

公告

使用asp.net dateaable 做统计表并且网页上显示的表和导出的Excel 一模一样根据领导看报表需求两个报表和一起（add algorithm）