/// <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));
}
}