/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Excel(bool CloseWindow)
{
try
{
BLL.CostBudgetDynamic dyn = m_dyn;
if (dyn == null)
{
dyn = GenerateDynamic();
}
TExcel excel = new TExcel(Response, Request, Server, Session);
try
{
excel.StartRow = 10;
excel.ColumnHeadRow = 9;
//取1级费用项
DataView dv;
if (dyn.NeedApport) //有分摊时，多了一级总计
{
dv = new DataView(dyn.tb, "Deep=3", "SortID", DataViewRowState.CurrentRows);
}
else
{
dv = new DataView(dyn.tb, "Deep=2", "SortID", DataViewRowState.CurrentRows);
}
excel.DataSource = dv;
//新建工作簿
excel.TemplateFileName = "项目费用表.xls";
// excel.TemplateSheetName = "汇总";
excel.TemplateSheetName = "";
excel.AddWorkbook();
//新建若干“分项”页
for (int k = 2; k <= dv.Count; k++)
{
((Excel.Worksheet)excel.Book.Worksheets[2]).Copy(TExcel.m_Opt, excel.Book.Worksheets[2]);
}
//转到汇总页
excel.Sheet = (Excel.Worksheet)excel.Book.Worksheets[1];
//表头信息
excel.SetCellValue(1, 1, BLL.ProjectRule.GetProjectName(dyn.ProjectCode));
//报表日期
if (this.txtCostBudgetBackupCode.Value != "") //备份
{
excel.SetCellValue(1, 3, dyn.entityBackup.GetDateTime("BackupDate", "yyyy-MM-dd HH:mm:ss"));
}
else
{
excel.SetCellValue(1, 3, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
excel.SetCellValue(1, 5, dyn.GetTargetCheckDate());
excel.SetCellValue(1, 6, dyn.entitySet.GetDecimal("HouseCount"));
excel.SetCellValue(1, 7, dyn.entitySet.GetDecimal("BuildingArea"));
excel.SetCellValue(1, 8, dyn.entitySet.GetDecimal("HouseArea"));
excel.DataSource = dv;
excel.DataToSheet();
//按第2级费用项分组
excel.StartRow = 11;
excel.ColumnHeadRow = 9;
/*
* //2级费用项临时表头
* DataTable tbLevel2 = dyn.entitySet.CurrentTable.Copy();
* BLL.ConvertRule.DataTableAddColumn(dyn.tb, tbLevel2);
*/
//3级费用项临时表（包括合同明细）
DataTable tbDtl = dyn.tb.Clone();
tbDtl.Columns.Add("ReportDtlSno", typeof(int));
DataColumn[] pk = new DataColumn[1];
pk[0] = tbDtl.Columns["ReportDtlSno"];
tbDtl.PrimaryKey = pk;
DataRow drNew;
int i = 0;
foreach (DataRowView drv in dv)
{
i++;
// BLL.ConvertRule.DataRowCopy(drv.Row, tbLevel2.Rows[0], dyn.tb, tbLevel2);
string ParentCostCode = BLL.ConvertRule.ToString(drv["CostCode"]);
string ParentCostName = BLL.ConvertRule.ToString(drv["CostName"]);
//取第2级费用项的子项
DataView dv2 = new DataView(dyn.tb, "ParentCode='" + ParentCostCode + "'", "SortID", DataViewRowState.CurrentRows);
//复制到临时表
tbDtl.Rows.Clear();
int ReportDtlSno = 0;
foreach (DataRowView drv2 in dv2)
{
DataRow dr2 = drv2.Row;
string CostCode = BLL.ConvertRule.ToString(dr2["CostCode"]);
//3级费用项
drNew = tbDtl.NewRow();
BLL.ConvertRule.DataRowCopy(dr2, drNew, dyn.tb, tbDtl);
ReportDtlSno++;
drNew["ReportDtlSno"] = ReportDtlSno;
tbDtl.Rows.Add(drNew);
//3级费用项的合同明细
DataRow[] drsContract = dyn.tbContract.Select("CostCode = '" + CostCode + "'", "ContractID, ContractName", DataViewRowState.CurrentRows);
AddContract(tbDtl, drNew, drsContract, ref ReportDtlSno);
//3级费用项的预留金额
DataRow[] drsNoContract = dyn.tbNoContract.Select("CostCode = '" + CostCode + "'");
AddNoContract(tbDtl, drNew, drsContract, drsNoContract, ref ReportDtlSno);
}
//合计行
drNew = tbDtl.NewRow();
BLL.ConvertRule.DataRowCopy(drv.Row, drNew, dyn.tb, tbDtl);
ReportDtlSno++;
drNew["ReportDtlSno"] = ReportDtlSno;
drNew["CostName"] = "合计";
tbDtl.Rows.Add(drNew);
/*
* //第2个2级费用项开始，每次都要新建工作页
* if (i > 1)
* {
* excel.TemplateSheetName = "分项";
* excel.AddWorksheet(i, true);
* }
*/
//转到分项页
excel.Sheet = (Excel.Worksheet)excel.Book.Worksheets[i + 1];
//一个2级费用项打印一页
excel.Sheet.Name = ParentCostName.Replace("/", "");
excel.DataToSheetSingle(drv.Row);
//2级费用项表头信息
// excel.SetCellValue(1, 1, drv["CostName"]);
DataView dvDtl = new DataView(tbDtl, "", "ReportDtlSno", DataViewRowState.CurrentRows);
excel.DataSource = dvDtl;
excel.DataToSheet();
/*
* //合同明细只有一条时，隐藏合同明细
* int colIsHide = 15;
* for(int k=0;k<dv2.Count;k++)
* {
* int row = excel.StartRow + k;
* string IsHide = TExcel.GetCellValue(excel.Sheet, row, colIsHide);
* if (IsHide == "1")
* {
* ((Excel.Range)excel.Sheet.Cells[row, 1]).EntireRow.Hidden = true;
* }
* }
*
* //隐藏列IsHide
* ((Excel.Range)excel.Sheet.Cells[1, colIsHide]).EntireColumn.Hidden = true;
*/
}
//缺省选中第1页
((Excel.Worksheet)excel.Book.Worksheets[1]).Select(TExcel.m_Opt);
//保存
excel.SaveWorkbook();
excel.ShowClient();
}
finally
{
excel.Dispose();
}
if (CloseWindow)
{
Response.Write(Rms.Web.JavaScript.WinClose(true));
}
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "导出Excel出错：" + ex.Message));
}
}
/// <summary>
/// 绑定动态费用明细
/// </summary>
private void BindDataGrid(BLL.CostBudgetDynamic dyn, bool IsScreenToTable)
{
try
{
DataTable tbDtl = dyn.tbHtml;
//已批预算的审核日期
this.lblTargetCheckDate.Text = dyn.GetTargetCheckDate();
string VerID = dyn.GetTargetVerID();
this.txtHasTargetHis.Value = (BLL.ConvertRule.ToDecimal(VerID) > 0)?"1":"";
if (VerID != "")
{
VerID = "版本" + VerID;
}
//版本号链接
this.spanTargetVerID.InnerText = "";
this.hrefTargetVerID.InnerText = "";
if (this.txtHasTargetHis.Value == "1") //有历史版本
{
this.hrefTargetVerID.InnerText = VerID;
}
else
{
this.spanTargetVerID.InnerText = VerID;
}
//历史目标费用
string TargetHisHead1 = "";
string TargetHisHead2 = "";
dyn.GenerateTargetHisHead(ref TargetHisHead1, ref TargetHisHead2);
ViewState["TargetHisHead1"] = TargetHisHead1;
ViewState["TargetHisHead2"] = TargetHisHead2;
ViewState["HasTargetChange"] = dyn.HasTargetChange;
if (dyn.HasTargetChange)
{
this.spanListTitleTargetMoneyDesc.InnerText = dyn.TargetChangeDesc;
this.spanListTitleTargetMoney.Style["display"] = "";
}
else
{
this.spanListTitleTargetMoneyDesc.InnerText = "";
this.spanListTitleTargetMoney.Style["display"] = "none";
}
if (IsScreenToTable)
{
//屏幕数据保存到临时表
tbDtl = ScreenToTable(false);
}
else
{
//折叠全部费用项
BLL.CostBudgetPageRule.CollapseAll(tbDtl);
if (dyn.NeedApport) //有分摊时，多了一级总计
{
BLL.CostBudgetPageRule.ExpandDeep(tbDtl, 3);
}
else
{
BLL.CostBudgetPageRule.ExpandDeep(tbDtl, 2);
}
}
this.dgList.DataSource = tbDtl;
this.dgList.DataBind();
/*
* string[] arrField = {"ItemMoney", "OldItemMoney", "TotalPayoutMoney", "RemainItemMoney"};
* decimal[] arrSum = BLL.MathRule.SumColumn(tb, arrField);
* this.dgList.Columns[2].FooterText = arrSum[0].ToString("N");
* this.dgList.Columns[3].FooterText = arrSum[1].ToString("N");
* this.dgList.Columns[6].FooterText = arrSum[2].ToString("N");
* this.dgList.Columns[7].FooterText = arrSum[3].ToString("N");
*
* this.dgList.DataSource = tb;
* this.dgList.DataBind();
*/
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "绑定动态费用明细出错：" + ex.Message));
}
}
/// <summary>
/// 绑定动态费用明细
/// </summary>
private void BindDataGrid(BLL.CostBudgetDynamic dyn, bool IsScreenToTable)
{
try
{
DataTable tbDtl = dyn.tbHtml;
//已批预算的审核日期
this.lblTargetCheckDate.Text = dyn.GetTargetCheckDate();
string VerID = dyn.GetTargetVerID();
this.txtHasTargetHis.Value = (BLL.ConvertRule.ToDecimal(VerID) > 0) ? "1" : "";
if (VerID != "")
{
VerID = "版本" + VerID;
}
//版本号链接
this.spanTargetVerID.InnerText = "";
this.hrefTargetVerID.InnerText = "";
if (this.txtHasTargetHis.Value == "1") //有历史版本
{
this.hrefTargetVerID.InnerText = VerID;
}
else
{
this.spanTargetVerID.InnerText = VerID;
}
/*
* //历史目标费用
* string TargetHisHead1 = "";
* string TargetHisHead2 = "";
* dyn.GenerateTargetHisHead(ref TargetHisHead1, ref TargetHisHead2);
*
* ViewState["TargetHisHead1"] = TargetHisHead1;
* ViewState["TargetHisHead2"] = TargetHisHead2;
*
* ViewState["HasTargetChange"] = dyn.HasTargetChange;
* if (dyn.HasTargetChange)
* {
* this.spanListTitleTargetMoneyDesc.InnerText = dyn.TargetChangeDesc;
* this.spanListTitleTargetMoney.Style["display"] = "";
* }
* else
* {
* this.spanListTitleTargetMoneyDesc.InnerText = "";
* this.spanListTitleTargetMoney.Style["display"] = "none";
* }
*
* if (IsScreenToTable)
* {
* //屏幕数据保存到临时表
* tbDtl = ScreenToTable(false);
* }
* else
* {
* //折叠全部费用项
* BLL.CostBudgetPageRule.CollapseAll(tbDtl);
*
* if (dyn.NeedApport) //有分摊时，多了一级总计
* {
* BLL.CostBudgetPageRule.ExpandDeep(tbDtl, 3);
* }
* else
* {
* BLL.CostBudgetPageRule.ExpandDeep(tbDtl, 2);
* }
* }
*/
}
catch (Exception ex)
{
ApplicationLog.WriteLog(this.ToString(), ex, "");
Response.Write(Rms.Web.JavaScript.Alert(true, "绑定动态费用明细出错：" + ex.Message));
}
}