Exemplo n.º 1
0
        /// <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));
            }
        }