ASP.NET DataTable合并

最新推荐文章于 2017-06-18 14:34:57 发布

twobrushes

最新推荐文章于 2017-06-18 14:34:57 发布

阅读量263

点赞数

分类专栏： ASP.NET 文章标签： asp.net

本文链接：https://blog.csdn.net/twobrushes/article/details/84550625

版权

ASP.NET 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

public partial class ReportIndex1 : System.Web.UI.Page
{
protected string strNf = "2012";
private DataTable dt = new DataTable();
// private DataTable dt_SC_SUM;
private DataTable dt_SC;
private DataTable dt_SC_XF;
private DataTable dt_SC_ZX;
// private DataTable dt_JY_SUM;
private DataTable dt_JY;
private DataTable dt_JY_XF;
private DataTable dt_JY_ZX;
private DataTable dtDq = new DataTable();
private int _S1 = 0;
private int _S2 = 0;
private int _S3 = 0;
private int _J1 = 0;
private int _J2 = 0;
private int _J3 = 0;
BLL.DIC_DQXX b_dqxx = new BLL.DIC_DQXX();
private bool Exist=false;
private int dqlenght = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
BindDate();
}

protected void btnCx_Click(object sender, EventArgs e)
{

}
/// <summary>
/// 绑定数据
/// </summary>
private void BindDate()
{
if (txtNf.Text == "")
{
txtNf.Text = strNf = DateTime.Now.Year.ToString();
}
else
{
strNf = txtNf.Text;
}
//获取地区名称
string sql_dq = "select id from view_dqxx_scxkz_scxkz group by id";
dtDq = DbHelperOra.QueryTable(sql_dq);
for (int i = 1; i < 7; i++)
{
dtDq.Columns.Add(i.ToString(),typeof(string));
}
//获取林木持证企业数量
string sql_Sc = "select id,count(*) from view_dqxx_scxkz_scxkz where SCXKZ_FBRQ<=add_months(to_date('" + strNf + "','yyyy'),12) and SCXKZ_ISTJ=0";
sql_Sc += " group by id ";
dt_SC = DbHelperOra.QueryTable(sql_Sc);
//获取林木新发
string sql_ScXf = "select id,count(*) from view_dqxx_scxkz_scxkz where SCXKZ_FBRQ >=to_date('" + strNf + "','yyyy')";
sql_ScXf += " and SCXKZ_FBRQ<add_months(to_date('" + strNf + "','YYYY'),12) and SCXKZ_ISTJ=0";
sql_ScXf += " group by id";
dt_SC_XF = DbHelperOra.QueryTable(sql_ScXf);
//获取林木注销
string sql_ScZx = "select id,count(*) from view_dqxx_scxkz_scxkz where SCXKZ_FBRQ >=to_date('" + strNf + "','yyyy')";
sql_ScZx += " and SCXKZ_FBRQ<add_months(to_date('" + strNf + "','YYYY'),12) and SCXKZ_ISTJ=1";
sql_ScZx += " group by id";
dt_SC_ZX = DbHelperOra.QueryTable(sql_ScZx);
//获取经营持证企业数量
string sql_JY = "select id,count(*) from view_dqxx_scxkz_scxkz where JYXKZ_FZRQ<=add_months(to_date('" + strNf + "','yyyy'),12) and JYXKZ_ISTJ=0";
sql_JY += " group by id";
dt_JY = DbHelperOra.QueryTable(sql_JY);
//获取林木经营新发
string sql_JyXf = "select id,count(*) from view_dqxx_scxkz_scxkz where JYXKZ_FZRQ >=to_date('" + strNf + "','yyyy')";
sql_JyXf += " and JYXKZ_FZRQ<add_months(to_date('" + strNf + "','YYYY'),12) and JYXKZ_ISTJ=0";
sql_JyXf += " group by id";
dt_JY_XF = DbHelperOra.QueryTable(sql_JyXf);
//获取林木经营注销
string sql_JyZx = "select id,count(*) from view_dqxx_scxkz_scxkz where JYXKZ_FZRQ >=to_date('" + strNf + "','yyyy')";
sql_JyZx += " and JYXKZ_FZRQ<add_months(to_date('" + strNf + "','YYYY'),12) and JYXKZ_ISTJ=1";
sql_JyZx += " group by id";
dt_JY_ZX = DbHelperOra.QueryTable(sql_JyZx);

JoinDataTableSum(dtDq, dt_SC, dt_SC_XF, dt_SC_ZX, dt_JY, dt_JY_XF, dt_JY_ZX,"id");

dt.Columns.AddRange(new DataColumn[] { new DataColumn("DQXX_ID"), new DataColumn("S1"), new DataColumn("S2"), new DataColumn("S3"), new DataColumn("J1"), new DataColumn("J2"), new DataColumn("J3") });
for (int i = 0; i < dtDq.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
if (dtDq.Rows[i][0].ToString() == "1")
{
dr[0] = "贵阳市生态委";
}
else if (dtDq.Rows[i][0].ToString() == "-15")//-15是为了能够在int列中存储string值
{
dr[0] = "合计";
}
else
{
try
{
dr[0] = b_dqxx.GetModel(int.Parse(dtDq.Rows[i][0].ToString())).NAME;
}
catch
{
throw new Exception("地区名称转换失败");
}
}
for (int j = 1; j < 7; j++)
{
dr[j] = dtDq.Rows[i][j].ToString();
}
dt.Rows.Add(dr);
}
rptMain.DataSource = dt;
rptMain.DataBind();

}
# region 合并DataTable
/// <summary>
/// 把所有DataTable合并
/// </summary>
private void JoinDataTableSum(DataTable dtDq,DataTable dtSc,DataTable dtScXf,DataTable dtScZx,DataTable dtJy,DataTable dtJyXf,DataTable dtJyZx,string ColumnName)
{
if (!dtDq.Columns.Contains(ColumnName))
{
throw new Exception("目标DataTable中无" + ColumnName + "列");
}

try
{
dqlenght = dtDq.Rows.Count;
JoinDataTable(dtDq, dtSc, dqlenght, 1, ColumnName, ref _S1);
JoinDataTable(dtDq, dtScXf, dqlenght, 2, ColumnName, ref _S2);
JoinDataTable(dtDq, dtScZx, dqlenght, 3, ColumnName, ref _S3);
JoinDataTable(dtDq, dtJy, dqlenght, 4, ColumnName, ref _J1);
JoinDataTable(dtDq, dtJyXf, dqlenght, 5, ColumnName, ref _J2);
JoinDataTable(dtDq, dtJyZx, dqlenght, 6, ColumnName, ref _J3);
DataRow dr = dtDq.NewRow();
dr[0] = "-15";//这里是为了把它转换为合计
dr[1] = _S1.ToString();
dr[2] = _S2.ToString();
dr[3] = _S3.ToString();
dr[4] = _J1.ToString();
dr[5] = _J2.ToString();
dr[6] = _J3.ToString();
dtDq.Rows.Add(dr);
}
catch (Exception e)
{
throw new Exception("执行DataTableJoin失败。/n" + e.Message);
}
}
/// <summary>
/// 两个DataTable合并
/// </summary>
private void JoinDataTable(DataTable dtSrc,DataTable dt_det,int lenght,int ColumnIndex,string ColumnName,ref int Sum)
{
Exist = false;
for (int i = 0; i < lenght;i++ )
{
foreach (DataRow joinRow in dt_det.Rows)
{
if (joinRow[ColumnName].ToString() == dtSrc.Rows[i][ColumnName].ToString())
{
dtSrc.Rows[i][ColumnIndex] = joinRow[1];
Exist = true;
Sum += int.Parse(dtSrc.Rows[i][ColumnIndex].ToString());
}
}
if (!Exist)
{
//dtSrc.Rows[i][ColumnIndex] = "0";
}
}
}
# endregion
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExpot_Click(object sender, EventArgs e)
{
ExportManage.LegerExport1 _lem = new ExportManage.LegerExport1("经营许可证统计表.xlt", System.DateTime.Now.Year.ToString());
_lem.Export(dt);
}
/// <summary>
/// 通过地区ID转换为名称
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
protected string GetDQName(string id)
{
//if (id == "1")
//{
// id = "贵阳市生态委";
//}
//else if (id =="-15")//-15是为了能够在int列中存储string值
//{
// id = "合计";
//}
//else
//{
// id = b_dqxx.GetModel(int.Parse(id)).NAME;
//}
return id;
}
}