コード例 #1
0
    protected void btnReport_Click(object sender, EventArgs e)
    {
        DataTable dt = CamperHoldingDA.GetData(Int32.Parse(ddlCampYear.SelectedItem.Text));

        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        ws.Name = "Campers Holding";

        ws.InsertDataTable(dt, 0, 0, true);

        CellStyle tableHeaderStyle = new CellStyle();

        tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
        tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

        ws.Rows[0].Style    = tableHeaderStyle;
        ws.Columns[0].Width = 20 * 256;
        ws.Columns[1].Width = 20 * 256;
        ws.Columns[2].Width = 30 * 256;
        ws.Columns[4].Width = 17 * 256;
        ws.Columns[5].Width = 42 * 256;
        ws.Columns[6].Width = 40 * 256;
        ws.Columns[7].Width = 16 * 256;
        ws.Columns[8].Width = 16 * 256;

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}CamperHoldingReport.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);

        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #2
0
    protected void btnReport_Click(object sender, EventArgs e)
    {
        DataSet ds;

        // Work sheet #1 - Data grouped by camp
        // the data content section of the report
        if (param.CamperOrg == CamperOrgType.CamperContactInfo)
        {
            ds = CamperApplicationBL.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
        }
        else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
        {
            ds = CamperApplicationBL.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
        }
        else
        {
            ds = CamperApplicationBL.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List, param.TimesReceivedGrant);
        }


        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet2"];

        //We start at first row, because for ExcelLite control, the header row is not included
        int BEGIN_COLUMN_INDEX;

        if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport)
        {
            BEGIN_COLUMN_INDEX = 0;
        }
        else
        {
            BEGIN_COLUMN_INDEX = 1;
        }

        const int CAMP_NAME_MERGED_CELL_NUMBER  = 6;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic seeting
        ws.Columns[0].Width = 20 * 20;

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = System.Drawing.Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;

        var fileName = "report";

        ReportHeader.Value = fileName = GetReportName(param.CamperOrg);
        ws.Name            = "By Camp";
        //
        iRow += 1;

        // Create Report SubHeader
        CellStyle styleSubHeader = new CellStyle();

        styleSubHeader.Font.Size   = 16 * 20;
        styleSubHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Value  = String.Format("Camp Year: {0}  Generated on {1}", param.CampYear, DateTime.Now);
        SubHeader.Style  = styleSubHeader;

        iRow += 2;

        CellStyle cs = new CellStyle();

        //cs.Font.Color = System.Drawing.Color.Red;
        cs.Font.Size   = 18 * 20;
        cs.Font.Weight = ExcelFont.BoldWeight;

        CellStyle csFirst = new CellStyle();

        csFirst.FillPattern.SetSolid(Color.Yellow);
        csFirst.Font.Weight = ExcelFont.BoldWeight;
        csFirst.WrapText    = true;

        ws.Columns[0].Width = 20 * 256;

        CellStyle tableHeaderStyle = new CellStyle();

        tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
        tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

        CellStyle redText = new CellStyle();

        redText.Font.Color = Color.Red;

        foreach (DataTable dt in ds.Tables)
        {
            CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
            cr.Merged = true;

            cr.Value = dt.Rows[0]["Camp Name"].ToString();

            //if (param.CamperOrg == CamperOrgType.CamperContactInfo)
            //{
            //    cr.Value = dt.Rows[0]["Camp Name"].ToString();
            //}
            //else
            //{
            //    cr.Value = param.CampsThatHaveDataDict.ElementAt(ds.Tables.IndexOf(dt)).Value;
            //}

            cr.Style = cs;

            ws.Rows[iRow].Height = 25 * 20;

            iRow += 1;

            ws.Rows[iRow].Height = 25 * 20;

            CellRange crFirst = ws.Cells.GetSubrangeAbsolute(iRow, 0, iRow, 0);

            if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
            {
                crFirst.Value = "Registered at Camp: Enter a Y / N here";
                crFirst.Style = csFirst;
            }

            ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);

            // 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
            if (param.CamperOrg == CamperOrgType.CamperContactInfo)
            {
                for (int i = 0; i <= dt.Rows.Count; i++)
                {
                    if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
                    {
                        ws.Cells[i + iRow, 14].Style = redText;
                        ws.Cells[i + iRow, 15].Style = redText;
                        ws.Cells[i + iRow, 16].Style = redText;
                    }
                }
            }
            else
            {
                for (int i = 0; i <= dt.Rows.Count; i++)
                {
                    //if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
                    //{
                    //    ws.Cells[i + iRow, 14].Style = redText;
                    //    ws.Cells[i + iRow, 15].Style = redText;
                    //}
                }
            }

            // loop through each column and 1.set the width of each colum, 2. set the header style of each column
            for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
            {
                if (param.CamperOrg == CamperOrgType.CamperContactInfo)
                {
                    ws.Cells[iRow, i].Style = tableHeaderStyle;
                    if (i == 2)
                    {
                        ws.Columns[i].Width = 22 * 256;
                    }
                    else if (i == 3)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else if (i == 4)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else if (i == 5)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else if (i == 7)
                    {
                        ws.Columns[i].Width = 23 * 256;
                    }
                    else if (i == 5)
                    {
                        ws.Columns[i].Width = 15 * 256;
                    }
                    else if (i == 11)
                    {
                        ws.Columns[i].Width = 26 * 256;
                    }
                    else if (i == 12)
                    {
                        ws.Columns[i].Width = 15 * 256;
                    }
                    else if (i == 13)
                    {
                        ws.Columns[i].Width = 10 * 256;
                    }
                    else if (i == 14)
                    {
                        ws.Columns[i].Width = 10 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 20 * 256;
                    }
                }
                else
                {
                    ws.Cells[iRow, i].Style = tableHeaderStyle;
                    if (i == 3)
                    {
                        ws.Columns[i].Width = 30 * 256;
                    }
                    else if (i == 4)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else if (i == 5)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else if (i == 7)
                    {
                        ws.Columns[i].Width = 23 * 256;
                    }
                    else if (i == 5)
                    {
                        ws.Columns[i].Width = 15 * 256;
                    }
                    else if (i == 11)
                    {
                        ws.Columns[i].Width = 26 * 256;
                    }
                    else if (i == 12)
                    {
                        ws.Columns[i].Width = 15 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 20 * 256;
                    }
                }
            }

            iRow += dt.Rows.Count + 2;

            DataTable dtTotal;

            if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
            {
                dtTotal = CreateTotalTablePerCamp(dt);
            }
            else if (param.CamperOrg == CamperOrgType.Synagogue)
            {
                dtTotal = CreateTotalTablePerSynag(dt);
            }
            else
            {
                dtTotal = null;
            }

            if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
            {
                ws.InsertDataTable(dtTotal, iRow, BEGIN_COLUMN_INDEX, true);

                for (int i = BEGIN_COLUMN_INDEX; i <= dtTotal.Columns.Count; i++)
                {
                    ws.Cells[iRow, i].Style = tableHeaderStyle;
                }

                iRow += dtTotal.Rows.Count + 3;
            }
        }

        // ************************** second tab ********************************
        // Second alternate sheet that list all rows as table format
        // Camper Detail Report (for Program admin) doesn't need to show the whole list
        if (param.CamperOrg != CamperOrgType.EnrollmentConfirmationFJC && param.CamperOrg != CamperOrgType.EnrollmentConfirmationPartner)
        {
            var ws2 = excel.Worksheets["Sheet1"];

            ws2.Name = fileName;
            DataTable dtAlternate;

            if (param.CamperOrg == CamperOrgType.CamperContactInfo)
            {
                dtAlternate = CamperApplicationDA.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List,
                                                                                    param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
            }
            else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
            {
                dtAlternate = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List,
                                                                               param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
            }
            else
            {
                dtAlternate = CamperApplicationDA.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID,
                                                                            param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List);
            }

            dtAlternate.Columns.RemoveAt(0);     // We don't want the first column, which is CampID

            iRow                = 1;
            ReportHeader        = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
            ReportHeader.Merged = true;
            ReportHeader.Style  = styleReportHeader;
            ReportHeader.Value  = GetReportName(param.CamperOrg);

            ws2.Rows[iRow].Height = 25 * 20;

            iRow += 1;

            ws2.Rows[iRow].Height = 25 * 20;
            // Create Report SubHeader
            SubHeader        = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
            SubHeader.Merged = true;
            SubHeader.Value  = "Camp Year: " + param.CampYear;
            SubHeader.Style  = styleSubHeader;

            iRow += 2;

            ws2.InsertDataTable(dtAlternate, iRow, BEGIN_COLUMN_INDEX, true);

            // 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
            if (param.CamperOrg == CamperOrgType.CamperContactInfo)
            {
                for (int i = 0; i <= dtAlternate.Rows.Count; i++)
                {
                    if (ws2.Cells[i + iRow, 14].Value.ToString() == "No")
                    {
                        ws2.Cells[i + iRow, 14].Style = redText;
                        ws2.Cells[i + iRow, 15].Style = redText;
                        ws2.Cells[i + iRow, 16].Style = redText;
                    }
                }
            }

            ws2.Rows[iRow].Style = tableHeaderStyle;
            if (param.CamperOrg == CamperOrgType.CamperContactInfo)
            {
                ws2.Columns[BEGIN_COLUMN_INDEX].Width      = 16 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width  = 28 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width  = 28 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width  = 14 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width  = 16 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width  = 23 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 26 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 11].Width = 15 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 15].Width = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 16].Width = 25 * 256;
            }
            else
            {
                // Camper Detail Report
                ws2.Columns[BEGIN_COLUMN_INDEX].Width      = 15 * 256; // CampID
                ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width  = 30 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width  = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width  = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width  = 30 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width  = 18 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 6].Width  = 30 * 256;  // Camp Name
                ws2.Columns[BEGIN_COLUMN_INDEX + 9].Width  = 22 * 256;  // Session Datea
                ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 18 * 256;  // Timer
                ws2.Columns[BEGIN_COLUMN_INDEX + 12].Width = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 13].Width = 25 * 256;
                ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
            }
        }
        else
        {
            // since there is no whole list report, we need to delete sheet1
            excel.Worksheets[0].Delete();
        }

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3} {4}.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond, fileName);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #3
0
    private void GenerateExcelReport()
    {
        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        //We start at first row, because for ExcelLite control, the header row is not included
        const int BEGIN_COLUMN_INDEX            = 1;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic setting
        ws.Columns[0].Width = 20 * 20; // make the first column smaller

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = System.Drawing.Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;
        ReportHeader.Value  = "Parent Country of Origin (Online Data Only)";

        ws.Rows[iRow].Height = 25 * 20;

        iRow += 1;

        // Create Report SubHeader - usually it's camp year and report generation time
        CellStyle styleReportSubHeader = new CellStyle();

        styleReportSubHeader.Font.Size   = 16 * 20;
        styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Style  = styleReportSubHeader;
        SubHeader.Value  = string.Format("Camp Year: {0}.  Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());

        iRow += 2;

        CellStyle styleReportSubHeaderNote = new CellStyle();
        //styleReportSubHeader.Font.Size = 16 * 20;
        //styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
        //styleReportSubHeaderNote.

        CellRange SubHeaderNote = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, 11);

        SubHeaderNote.Merged = true;
        //SubHeaderNote.Style = styleReportSubHeaderNote;
        SubHeaderNote.Value = "This report offers two different pieces of data.  “Number Of Campers” who have at least one parent from the country(s) selected and the list of countries with the number of corresponding parents.  Note: the";
        //ws.Rows[iRow].Height = 25 * 20;

        iRow += 1;

        CellRange SubHeaderNote2 = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, 11);

        SubHeaderNote2.Merged = true;
        //SubHeaderNote.Style = styleReportSubHeaderNote;
        SubHeaderNote2.Value = "number of campers will typically be less than the total number of parents in this report.";
        //ws.Rows[iRow].Height = 25 * 20;


        iRow += 2;

        CellStyle styleTableTitle = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Center
        };

        styleTableTitle.Font.Size = 16 * 20;
        styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
        styleTableTitle.Font.Weight = ExcelFont.BoldWeight;

        CellStyle styleTableDataRow = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Center
        };
        CellStyle styleTableDataRowCampName = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Left
        };

        CellStyle styleTableHeaderColumns = new CellStyle();

        styleTableHeaderColumns.Font.Weight         = ExcelFont.BoldWeight;
        styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);

        // Data Content of report
        DataSet[] dsArray = GenerateDataSet();

        //DataSet dsModified = dsArray[0];
        //DataSet ds2 = dsArray[1];

        int    indexDataSet = 0; // serve to see which dataset we are running (either ByProgram or ByCamp
        bool   isRun = false;
        string EntityName = "", ReportName = "";

        foreach (DataSet dsModified in dsArray)
        {
            isRun = false;

            if (chkByProgram.Checked && indexDataSet == 0)
            {
                EntityName = "Program";
                ReportName = "By Program, By Parent Birth Country";
                isRun      = true;
            }

            if (chkByCamp.Checked && indexDataSet == 1)
            {
                EntityName = "CampName";
                ReportName = "By Camp, By Parent Birth Country";
                isRun      = true;
            }

            if (isRun)
            {
                // Get rid of first columns for other tables except first one
                for (int i = 1; i < dsModified.Tables.Count; i++)
                {
                    dsModified.Tables[i].Columns.Remove(EntityName);
                }

                int table_column_count = dsModified.Tables[0].Columns.Count;
                int times = 0, current_starting_column = 0;

                // Table Title
                CellStyle styleSubReportTitle = new CellStyle {
                    HorizontalAlignment = HorizontalAlignmentStyle.Center
                };
                styleSubReportTitle.Font.Size = 16 * 20;
                styleSubReportTitle.FillPattern.SetSolid(Color.Transparent);
                styleSubReportTitle.Font.Weight = ExcelFont.BoldWeight;

                CellRange SubReportTitle = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
                SubReportTitle.Merged = true;
                SubReportTitle.Value  = ReportName;
                SubReportTitle.Style  = styleSubReportTitle;

                iRow += 3;

                foreach (DataTable dt in dsModified.Tables)
                {
                    if (times == 1)
                    {
                        // Get the header location
                        current_starting_column = BEGIN_COLUMN_INDEX + 1 + times * table_column_count;
                    }
                    else
                    {
                        // Get the header location
                        current_starting_column = BEGIN_COLUMN_INDEX + times * table_column_count;
                    }

                    int temp_column = 0;
                    if (times == 0)
                    {
                        temp_column = current_starting_column + 1;
                    }
                    else
                    {
                        temp_column = current_starting_column;
                    }


                    // Table Title
                    CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
                    TableTitle.Merged = true;
                    TableTitle.Value  = dt.TableName;
                    TableTitle.Style  = styleTableTitle;

                    // this creats the real table
                    ws.InsertDataTable(dt, iRow, current_starting_column, true);

                    // loop through each column and set style accordingly
                    for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
                    {
                        ws.Cells[iRow, i].Style = styleTableHeaderColumns;
                        ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;

                        ws.Columns[i].Width = 11 * 256;

                        // first column of first table, e.g. camp naem
                        if (times == 0)
                        {
                            if (i == current_starting_column)
                            {
                                ws.Columns[i].Width = 55 * 256; // camp/program name
                            }
                            else if (i == current_starting_column + 1)
                            {
                                ws.Columns[i].Width = 15 * 256;
                            }
                        }
                        else if (i == current_starting_column)
                        {
                            ws.Columns[i].Width = 15 * 256;
                        }
                    }

                    // Set the data row style
                    for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
                    {
                        ws.Rows[j].Style = styleTableDataRow;
                    }

                    // left justify the camp names
                    if (times == 0)
                    {
                        for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
                        {
                            ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
                        }
                    }

                    times++;
                }

                iRow += dsModified.Tables[0].Rows.Count + 5;
            }
            indexDataSet += 1;
        }



        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}ParentCountryOfOrigin.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #4
0
ファイル: DataExtractBL.cs プロジェクト: wenchenglai/CIPRS
    public static string GenerateExcelReport(DataTable dt)
    {
        string templateFile = HttpContext.Current.Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = HttpContext.Current.Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        // The first row is decoration headers
        ws.Rows[1].Height = 2 * 256;

        CellStyle cs = new CellStyle();

        cs.FillPattern.SetSolid(Color.FromArgb(204, 153, 255));
        cs.Font.Weight         = ExcelFont.BoldWeight;
        cs.WrapText            = true;
        cs.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr = ws.Cells.GetSubrangeAbsolute(0, 0, 0, 10);

        cr.Merged = true;
        cr.Value  = "Program Information";
        cr.Style  = cs;

        CellStyle cs2 = new CellStyle();

        cs2.FillPattern.SetSolid(Color.FromArgb(102, 255, 255));
        cs2.Font.Weight         = ExcelFont.BoldWeight;
        cs2.WrapText            = true;
        cs2.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr2 = ws.Cells.GetSubrangeAbsolute(0, 11, 0, 15);

        cr2.Merged = true;
        cr2.Value  = "Camp Information";
        cr2.Style  = cs2;

        CellStyle cs3 = new CellStyle();

        cs3.FillPattern.SetSolid(Color.FromArgb(255, 153, 204));
        cs3.Font.Weight         = ExcelFont.BoldWeight;
        cs3.WrapText            = true;
        cs3.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr3 = ws.Cells.GetSubrangeAbsolute(0, 16, 0, 23);

        cr3.Merged = true;
        cr3.Value  = "Basic Camper Information";
        cr3.Style  = cs3;

        CellStyle cs4 = new CellStyle();

        cs4.FillPattern.SetSolid(Color.FromArgb(204, 255, 204));
        cs4.Font.Weight         = ExcelFont.BoldWeight;
        cs4.WrapText            = true;
        cs4.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr4 = ws.Cells.GetSubrangeAbsolute(0, 24, 0, 28);

        cr4.Merged = true;
        cr4.Value  = "Camper Contact Information";
        cr4.Style  = cs4;

        CellStyle cs5 = new CellStyle();

        cs5.FillPattern.SetSolid(Color.FromArgb(255, 255, 153));
        cs5.Font.Weight         = ExcelFont.BoldWeight;
        cs5.WrapText            = true;
        cs5.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr5 = ws.Cells.GetSubrangeAbsolute(0, 29, 0, 42);

        cr5.Merged = true;
        cr5.Value  = "Parent Contact Information";
        cr5.Style  = cs5;

        CellStyle cs6 = new CellStyle();

        cs6.FillPattern.SetSolid(Color.FromArgb(150, 150, 150));
        cs6.Font.Weight         = ExcelFont.BoldWeight;
        cs6.WrapText            = true;
        cs6.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr6 = ws.Cells.GetSubrangeAbsolute(0, 43, 0, 45);

        cr6.Merged = true;
        cr6.Value  = "Application Information";
        cr6.Style  = cs6;

        CellStyle cs7 = new CellStyle();

        cs7.FillPattern.SetSolid(Color.FromArgb(255, 204, 153));
        cs7.Font.Weight         = ExcelFont.BoldWeight;
        cs7.WrapText            = true;
        cs7.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr7 = ws.Cells.GetSubrangeAbsolute(0, 46, 0, 52);

        cr7.Merged = true;
        cr7.Value  = "Marketing Source";
        cr7.Style  = cs7;

        CellStyle cs8 = new CellStyle();

        cs8.FillPattern.SetSolid(Color.FromArgb(153, 204, 255));
        cs8.Font.Weight         = ExcelFont.BoldWeight;
        cs8.WrapText            = true;
        cs8.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr8 = ws.Cells.GetSubrangeAbsolute(0, 53, 0, 68);

        cr8.Merged = true;
        cr8.Value  = "Demographic Information";
        cr8.Style  = cs8;

        // this creats the real table
        ws.InsertDataTable(dt, 1, 0, true);

        CellStyle styleTableHeaderColumns = new CellStyle();

        styleTableHeaderColumns.Font.Weight         = ExcelFont.BoldWeight;
        styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        styleTableHeaderColumns.VerticalAlignment   = VerticalAlignmentStyle.Center;
        styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
        styleTableHeaderColumns.WrapText = true;

        ws.Columns[0].Width = 15 * 256;
        ws.Columns[1].Width = 35 * 256;
        ws.Columns[2].Width = 30 * 256;

        ws.Columns[11].Width = 35 * 256; // Camp Name
        ws.Columns[12].Width = 27 * 256;

        ws.Columns[16].Width = 30 * 256; // Last Name
        ws.Columns[17].Width = 30 * 256;

        ws.Rows[1].Style  = styleTableHeaderColumns;
        ws.Rows[1].Height = 3 * 256;

        CellStyle styleContent = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Left, VerticalAlignment = VerticalAlignmentStyle.Center
        };

        for (int i = 2; i < dt.Rows.Count; i++)
        {
            ws.Rows[i].Style = styleContent;
        }

        styleContent.WrapText = true;

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}DataExtract.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        return(strFileParts[strFileParts.Length - 1]);
    }
コード例 #5
0
    private void GenerateExcelReport()
    {
        // Data Content of report
        DateTime   cutoffDate   = GetCutoffDate();
        List <int> yearList     = GetYearList();
        DataSet    dsNormalized = GenerateDataTables(cutoffDate);

        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        //We start at first row, because for ExcelLite control, the header row is not included
        int       BEGIN_COLUMN_INDEX            = 0;
        const int CAMP_NAME_MERGED_CELL_NUMBER  = 6;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic setting

        // first column
        ws.Columns[0].Width = 40 * 250;

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;
        ReportHeader.Value  = "Summary by Program";

        iRow += 1;

        // Create Report SubHeader - usually it's camp year
        CellStyle styleSubHeader = new CellStyle();

        styleSubHeader.Font.Size   = 16 * 20;
        styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
        styleSubHeader.Font.Color  = Color.Red;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Value  = cutoffDate.ToString(" MMM d, yyyy");
        SubHeader.Style  = styleSubHeader;

        iRow += 2;

        CellStyle cs = new CellStyle();

        cs.Font.Size   = 18 * 20;
        cs.Font.Weight = ExcelFont.BoldWeight;

        CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);

        cr.Merged = true;
        cr.Value  = "";
        cr.Style  = cs;

        ws.Rows[iRow].Height = 25 * 20;

        iRow += 3;

        int iColumn       = 0;
        int sameYearCount = 0;

        if (chkAllTimers.Checked)
        {
            sameYearCount += 1;
        }

        if (chk1stTimers.Checked)
        {
            sameYearCount += 1;
        }

        if (chk2ndTimers.Checked)
        {
            sameYearCount += 1;
        }

        if (chk3rdTimers.Checked)
        {
            sameYearCount += 1;
        }

        int  sameYearOffset = 1;
        bool tempFlag       = true;

        foreach (DataTable dt in dsNormalized.Tables)
        {
            CellStyle styleTableHeader = new CellStyle();
            styleTableHeader.Font.Size   = 16 * 16;
            styleTableHeader.Font.Weight = ExcelFont.BoldWeight;

            string originalName = dt.TableName;
            int    year         = ChangeTableNameForExcelReport(dt);

            // only the first table shows the program name, the rest on the same row will have no first column
            int currentColumn = iColumn;
            if (sameYearOffset != 1)
            {
                dt.Columns.Remove("Name");
            }
            else
            {
                currentColumn = iColumn + 1;

                // This is special code for if select only current year, then the tables layout are vertical, and just need to show year at once
                if (yearList.Count > 0 || tempFlag)
                {
                    tempFlag = false;
                    CellRange TableHeaderYear = ws.Cells.GetSubrangeAbsolute(iRow - 2, currentColumn, iRow - 2, currentColumn);
                    TableHeaderYear.Value       = year.ToString();
                    styleTableHeader.Font.Color = Color.Black;
                    TableHeaderYear.Style       = styleTableHeader;
                }
            }

            styleTableHeader.Font.Color = Color.Red;
            CellRange TableHeader = ws.Cells.GetSubrangeAbsolute(iRow - 1, currentColumn, iRow - 1, currentColumn);
            TableHeader.Style = styleTableHeader;

            TableHeader.Value = dt.TableName;
            dt.TableName      = originalName;



            // this creats the real table
            ws.InsertDataTable(dt, iRow, iColumn, true);

            CellStyle tableHeaderStyle = new CellStyle();
            tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
            tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

            CellStyle tableHeaderStyle2 = new CellStyle();
            tableHeaderStyle2.Font.Weight = ExcelFont.BoldWeight;
            tableHeaderStyle2.FillPattern.SetSolid(Color.Orange);

            //Bold Style
            CellStyle boldStyle = new CellStyle();
            boldStyle.Font.Weight = ExcelFont.BoldWeight;

            // adjust each column's width and header style
            for (int i = iColumn; i <= (dt.Columns.Count + iColumn - 1); i++)
            {
                // make sure total row is bold
                ws.Cells[iRow + dt.Rows.Count, i].Style = boldStyle;

                // make column wide enough to see the column header
                if (iColumn == 0)
                {
                    // first table has program name, so has different i offset
                    if (i == 0 + iColumn)
                    {
                        ws.Columns[i].Width = 35 * 256;
                    }
                    else if (i == 1 + iColumn)
                    {
                        ws.Columns[i].Width = 10 * 256;
                    }
                    else if (i == 2 + iColumn)
                    {
                        ws.Columns[i].Width = 30 * 256;
                    }
                    else if (i == 4 + iColumn)
                    {
                        ws.Columns[i].Width = 22 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 17 * 256;
                    }

                    if (i == iColumn + 1)
                    {
                        // but Seth wants total column to be color of pink
                        ws.Cells[iRow, i].Style = tableHeaderStyle2;
                    }
                    else
                    {
                        // make sure header row has bgcolor and is bold
                        ws.Cells[iRow, i].Style = tableHeaderStyle;
                    }
                }
                else
                {
                    if (i == iColumn)
                    {
                        ws.Columns[i].Width = 10 * 256;
                    }
                    else if (i == 1 + iColumn)
                    {
                        ws.Columns[i].Width = 30 * 256;
                    }
                    else if (i == 3 + iColumn)
                    {
                        ws.Columns[i].Width = 22 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 17 * 256;
                    }

                    if (iColumn == i)
                    {
                        // but Seth wants total column to be color of pink
                        ws.Cells[iRow, i].Style = tableHeaderStyle2;
                    }
                    else
                    {
                        // make sure header row has bgcolor and is bold
                        ws.Cells[iRow, i].Style = tableHeaderStyle;
                    }
                }
            }

            // Make the same year tables on the same row
            if (sameYearOffset < sameYearCount && yearList.Count > 0)
            {
                iColumn        += dt.Columns.Count + 1;
                sameYearOffset += 1;
            }
            else
            {
                iRow          += dt.Rows.Count + 4;
                iColumn        = 0;
                sameYearOffset = 1;
            }
        }

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}CamperCountByProgramAndStatus.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset     = "";

        if (newFile.Length == 0)
        {
            Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #6
0
 public GemBoxExcelLiteManager()
 {
     ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
 }
コード例 #7
0
        private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
        {
            //if the month is the aggStart use the blank template. Otherwise, use last months
            string xlsBaseFileName;

            if (aggStartDate == repDate)
            {
                xlsBaseFileName = "NNIN Data Blank.xls";
            }
            else
            {
                xlsBaseFileName = string.Format("AS{0:yyyyMM}_Report{1:yyyyMM}_{2}.xls", aggStartDate, repDate.AddMonths(-1), litCurrentThreshold.Text);
            }

            string xlsBaseFilePath = XlsFilePath + xlsBaseFileName;
            string xlsReportPath   = XlsFilePath + xlsReportName;

            DateTime period = repDate;
            DateTime sDate  = period;
            DateTime eDate  = sDate.AddMonths(1);

            DataSet ds = NNINDA.GetTablesWithMinimumMinutes(period, (int)ClientPrivilege.LabUser, makeAggData, Convert.ToDouble(litCurrentThreshold.Text) / 60);

            ds.Tables[0].TableName = "TechnicalInterest_Hours";
            ds.Tables[1].TableName = "OrgType_Hours";
            ds.Tables[2].TableName = "TechnicalInterest_Users";
            ds.Tables[3].TableName = "OrgType_Users";
            ds.Tables[4].TableName = "TechnicalInterest_Fees";
            ds.Tables[5].TableName = "OrgType_Fees";

            DataColumn[] pk;

            pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
            ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;

            pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
            ds.Tables["OrgType_Fees"].PrimaryKey = pk;

            //get raw cumulative user data
            DataTable dtCumUser = NNINDA.GetCumulativeUserAggregateData(aggStartDate, repDate);

            DataTable dtReport = new DataTable();

            dtReport.Columns.Add("BaseRow", typeof(int));
            dtReport.Columns.Add("TotalRow", typeof(int));
            dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum,
            dtReport.Columns.Add("ByType", typeof(string));  //orgtype, techid, none
            dtReport.Columns.Add("DateOnly", typeof(bool));  //orgtype, techid, none

            AddReportRow(dtReport, 10, 0, string.Empty, string.Empty, true);
            AddReportRow(dtReport, 24, 43, "Hours", "TechnicalInterest", false);
            AddReportRow(dtReport, 11, 43, "Hours", "OrgType", false);
            AddReportRow(dtReport, 40, 0, string.Empty, string.Empty, true);
            AddReportRow(dtReport, 69, 88, "Users", "TechnicalInterest", false);
            AddReportRow(dtReport, 55, 88, "Users", "OrgType", false);
            AddReportRow(dtReport, 85, 0, string.Empty, string.Empty, true);
            AddReportRow(dtReport, 161, 180, "Fees", "TechnicalInterest", false);
            AddReportRow(dtReport, 146, 180, "Fees", "OrgType", false);
            AddReportRow(dtReport, 177, 0, string.Empty, string.Empty, true);
            AddReportRow(dtReport, 116, 136, "CumUser", "TechnicalInterest", false);
            AddReportRow(dtReport, 101, 136, "CumUser", "OrgType", false);
            AddReportRow(dtReport, 133, 0, string.Empty, string.Empty, true);
            AddReportRow(dtReport, 226, 0, "CumUser", "DemGender", false);
            AddReportRow(dtReport, 232, 0, "CumUser", "DemEthnic", false);
            AddReportRow(dtReport, 238, 0, "CumUser", "DemRace", false);
            AddReportRow(dtReport, 247, 0, "CumUser", "DemDisability", false);

            ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
            ExcelFile SpreadSheet = new ExcelFile();

            SpreadSheet.LoadXls(xlsBaseFilePath);
            ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];

            DataRow[] fdr;
            int       lastVal, useCol = 0, useRow;
            double    hardTotals; //for recording internal time without formula

            foreach (DataRow drReport in dtReport.Rows)
            {
                //write dates
                for (int j = 0; j < 12; j++)
                {
                    ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
                    if (aggStartDate.AddMonths(j) == repDate)
                    {
                        useCol = j + 1;
                    }
                }

                if (!Convert.ToBoolean(drReport["DateOnly"]))
                {
                    hardTotals = 0;
                    if (drReport["RepType"].ToString() == "CumUser")
                    {
                        fdr = dtCumUser.Select(string.Format("TableName = '{0}'", drReport["ByType"]));
                        for (int j = 0; j < fdr.Length; j++)
                        {
                            useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
                            if (aggStartDate == repDate)
                            {
                                ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
                            }
                            else
                            {
                                lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
                                for (int i = 2; i < useCol; i++)
                                {
                                    if (!string.IsNullOrEmpty(ws.Cells[useRow, i].Formula))
                                    {
                                        string form = ws.Cells[useRow, i].Formula;
                                        lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
                                    }
                                }
                                string formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
                                ws.Cells[useRow, useCol].Formula = formula;
                            }
                            hardTotals += Convert.ToDouble(fdr[j]["Count"]);
                        }
                        if (Convert.ToInt32(drReport["TotalRow"]) != 0)
                        {
                            ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
                        }
                    }
                    else
                    {
                        foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
                        {
                            useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(drReport["ByType"].ToString() + "ID");
                            double val = Convert.ToDouble(dr[drReport["RepType"].ToString()]); //must use Convert.ToDouble here because dr[...] may be different types depending on the rep type (e.g. double or int)
                            ws.Cells[useRow, useCol].Value = val;
                            hardTotals += val;
                        }
                        ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
                    }
                }
            }

            SpreadSheet.SaveXls(xlsReportPath);
            SpreadSheet = null;
            GC.Collect();

            return(xlsReportPath);
        }
コード例 #8
0
    private void GenerateExcelReport()
    {
        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        //We start at first row, because for ExcelLite control, the header row is not included
        const int BEGIN_COLUMN_INDEX            = 1;
        const int CAMP_NAME_MERGED_CELL_NUMBER  = 6;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic setting
        ws.Columns[0].Width = 20 * 20;

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = System.Drawing.Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;
        ReportHeader.Value  = "Number of Campers By Camp and Status (Online Data Only)";

        //
        iRow += 1;

        // Create Report SubHeader - usually it's camp year
        CellStyle styleSubHeader = new CellStyle();

        styleSubHeader.Font.Size   = 16 * 20;
        styleSubHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Value  = "Camp Year: " + ddlCampYear.SelectedItem.Text;
        SubHeader.Style  = styleSubHeader;

        iRow += 2;

        // Data Content of report
        DataTable dt = GenerateDataTable();

        CellStyle cs = new CellStyle();

        cs.Font.Size   = 18 * 20;
        cs.Font.Weight = ExcelFont.BoldWeight;


        CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);

        cr.Merged = true;
        cr.Value  = "";
        cr.Style  = cs;

        ws.Rows[iRow].Height = 25 * 20;

        iRow += 1;

        ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);

        CellStyle tableHeaderStyle = new CellStyle();

        tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
        tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

        for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
        {
            ws.Cells[iRow, i].Style = tableHeaderStyle;
            ws.Cells[iRow + dt.Rows.Count, i].Style = tableHeaderStyle;
            if (i == 1)
            {
                ws.Columns[i].Width = 30 * 256;
            }
            else
            {
                ws.Columns[i].Width = 20 * 256;
            }
        }

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Millisecond.ToString() + "CamperCountByCampAndStatus.xls";
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #9
0
    private void GenerateExcelReport()
    {
        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        //We start at first row, because for ExcelLite control, the header row is not included
        int       BEGIN_COLUMN_INDEX            = 1;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic setting
        ws.Columns[0].Width = 20 * 20; // make the first column smaller

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = System.Drawing.Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;
        ReportHeader.Value  = "Camper By State By Camp";

        ws.Rows[iRow].Height = 25 * 20;

        iRow += 1;

        // Create Report SubHeader - usually it's camp year and report generation time
        CellStyle styleReportSubHeader = new CellStyle();

        styleReportSubHeader.Font.Size   = 16 * 20;
        styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Style  = styleReportSubHeader;
        SubHeader.Value  = string.Format("Camp Year: {0}.  Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());

        iRow += 4;

        CellStyle styleTableTitle = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Center
        };

        styleTableTitle.Font.Size = 16 * 20;
        styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
        styleTableTitle.Font.Weight = ExcelFont.BoldWeight;

        CellStyle styleTableDataRow = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Center
        };
        CellStyle styleTableDataRowCampName = new CellStyle {
            HorizontalAlignment = HorizontalAlignmentStyle.Left
        };

        CellStyle styleTableHeaderColumns = new CellStyle();

        styleTableHeaderColumns.Font.Weight         = ExcelFont.BoldWeight;
        styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);

        // Data Content of report
        DataSet dsModified = GenerateDataSet();

        for (int i = 1; i < dsModified.Tables.Count; i++)
        {
            dsModified.Tables[i].Columns.Remove("CampName");
        }

        int current_starting_column = 0, times = 0;
        int last_table_column_count = 0, last_column_index = 0;

        foreach (DataTable dt in dsModified.Tables)
        {
            // Get the header location
            if (times == 0)
            {
                current_starting_column = BEGIN_COLUMN_INDEX;
            }
            else
            {
                current_starting_column = last_column_index + last_table_column_count;
            }

            // we need to know the last table column count to calculate the current starting column index
            last_table_column_count = dt.Columns.Count;
            last_column_index       = current_starting_column + 1;

            int temp_column = 0;
            if (times == 0)
            {
                temp_column = current_starting_column + 1;
            }
            else
            {
                temp_column = current_starting_column;
            }

            CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
            TableTitle.Value  = dt.TableName;
            TableTitle.Merged = true;
            TableTitle.Style  = styleTableTitle;

            // this creats the real table
            ws.InsertDataTable(dt, iRow, current_starting_column, true);

            for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
            {
                ws.Cells[iRow, i].Style = styleTableHeaderColumns;
                ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;

                ws.Columns[i].Width = 8 * 256;

                // first column of first table, e.g. camp naem
                if (times == 0)
                {
                    if (i == current_starting_column)
                    {
                        ws.Columns[i].Width = 55 * 256; // camp/program name
                    }
                }
            }

            // Set the data row style
            for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
            {
                ws.Rows[j].Style = styleTableDataRow;
            }

            // left justify the camp names
            if (times == 0)
            {
                for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
                {
                    ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
                }
            }

            times++;
        }

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}CamperReportByState.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #10
0
    private void GenerateExcelReport()
    {
        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        //We start at first row, because for ExcelLite control, the header row is not included
        int       BEGIN_COLUMN_INDEX            = 1;
        const int CAMP_NAME_MERGED_CELL_NUMBER  = 6;
        const int REPORT_HEADER_CELL_NUMBER     = 6;
        const int REPORT_SUB_HEADER_CELL_NUMBER = 4;

        int iRow = 1;

        // Global artistic setting
        ws.Columns[0].Width = 20 * 20;

        // Create Report Header
        CellStyle styleReportHeader = new CellStyle();

        styleReportHeader.Font.Color  = System.Drawing.Color.Blue;
        styleReportHeader.Font.Size   = 22 * 20;
        styleReportHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);

        ReportHeader.Merged = true;
        ReportHeader.Style  = styleReportHeader;
        ReportHeader.Value  = "Number of Campers By Program, Status and Summer";

        iRow += 1;

        // Create Report SubHeader - usually it's camp year
        CellStyle styleSubHeader = new CellStyle();

        styleSubHeader.Font.Size   = 16 * 20;
        styleSubHeader.Font.Weight = ExcelFont.BoldWeight;

        CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);

        SubHeader.Merged = true;
        SubHeader.Value  = string.Format("Camp Year: {0}.  Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
        SubHeader.Style  = styleSubHeader;

        iRow += 2;

        // 2010-11-26 no longer needed
        //AddSubTotalRowsTable(dsNormalized);

        CellStyle cs = new CellStyle();

        cs.Font.Size   = 18 * 20;
        cs.Font.Weight = ExcelFont.BoldWeight;

        CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);

        cr.Merged = true;
        cr.Value  = "";
        cr.Style  = cs;

        ws.Rows[iRow].Height = 25 * 20;

        iRow += 2;

        // Data Content of report
        DataSet ds           = GenerateDataTable();
        DataSet dsNormalized = NormalizeTables(ds);

        for (int i = 1; i < ds.Tables.Count; i++)
        {
            dsNormalized.Tables[i].Columns.Remove("Name");
        }

        int times = 0;
        int table_column_count = ds.Tables[0].Columns.Count;
        int current_column     = 0;

        foreach (DataTable dt in dsNormalized.Tables)
        {
            if (times == 1)
            {
                BEGIN_COLUMN_INDEX += 1;
            }
            // Get the header location
            current_column = BEGIN_COLUMN_INDEX + times * table_column_count;

            CellRange TableHeader = ws.Cells.GetSubrangeAbsolute(iRow - 1, current_column, iRow - 1, current_column);
            TableHeader.Value = dt.TableName;
            TableHeader.Style = styleSubHeader;

            // this creats the real table
            ws.InsertDataTable(dt, iRow, current_column, true);

            CellStyle tableHeaderStyle = new CellStyle();
            tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
            tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

            for (int i = current_column; i <= (dt.Columns.Count + current_column - 1); i++)
            {
                ws.Cells[iRow, i].Style = tableHeaderStyle;
                ws.Cells[iRow + dt.Rows.Count, i].Style = tableHeaderStyle;
                if (i == current_column)
                {
                    // first column of every table
                    if (times >= 1)
                    {
                        ws.Columns[i].Width = 20 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 35 * 256;
                    }
                }
                else
                {
                    ws.Columns[i].Width = 20 * 256; // non-first column
                }
            }

            //if (times == 1)
            //    BEGIN_COLUMN_INDEX -= 1;

            times++;
        }

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}CamperCountByProgramAndStatus.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #11
0
    private void GenerateExcelReport(DataTable dtAllCamps, IList <int> campIdList)
    {
        var templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReportPrintingFormatted.xls");
        var workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        var excel = new ExcelFile();

        excel.LoadXls(templateFile);

        var summaryTable = new DataTable();

        summaryTable.Columns.Add("CampName", typeof(string));
        summaryTable.Columns.Add("1st/2nd/3rd Time", typeof(int));
        summaryTable.Columns.Add("Day School", typeof(int));
        summaryTable.Columns.Add("Total Dollars", typeof(double));


        foreach (var campId in campIdList)
        {
            IEnumerable <DataRow> enumByCamp = from camperApp in dtAllCamps.AsEnumerable()
                                               where camperApp.Field <int>("CampID") == campId
                                               select camperApp;

            if (enumByCamp.Any())
            {
                var sum     = enumByCamp.Sum(x => x.Field <double>("GrantAmount"));
                var countDS = (from camperApp in enumByCamp
                               where camperApp.Field <string>("Day School") == "Y"
                               select camperApp).Count();
                var countAll = enumByCamp.Count();

                DataTable dt = enumByCamp.CopyToDataTable();

                var campName = dt.Rows[0]["CampName"].ToString();

                var dr = summaryTable.NewRow();
                dr["CampName"]         = campName;
                dr["1st/2nd/3rd Time"] = countAll;
                dr["Day School"]       = countDS;
                dr["Total Dollars"]    = sum;
                summaryTable.Rows.Add(dr);

                if (campName.Contains(":"))
                {
                    campName = campName.Replace(":", "-");
                }

                //We start at first row, because for ExcelLite control, the header row is not included
                const int BEGIN_COLUMN_INDEX            = 0;
                const int REPORT_HEADER_CELL_NUMBER     = 5;
                const int REPORT_SUB_HEADER_CELL_NUMBER = 5;
                const int CAMP_NAME_MERGED_CELL_NUMBER  = 5;

                int iRow = 1;

                var ws = excel.Worksheets.AddCopy(campName, excel.Worksheets["Sheet1"]);

                // Global artistic setting
                ws.Columns[0].Width = 20 * 20; // make the first column smaller

                // Create Report Header
                var styleReportHeader = new CellStyle
                {
                    Font = { Color = Color.Blue, Size = 22 * 20, Weight = ExcelFont.BoldWeight }
                };

                CellRange reportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
                reportHeader.Merged = true;
                reportHeader.Style  = styleReportHeader;
                reportHeader.Value  = "One Happy Camper Payment Report";

                ws.Rows[iRow].Height = 25 * 20;

                iRow += 1;

                // Create Report SubHeader - usually it's camp year and report generation time
                var styleReportSubHeader = new CellStyle {
                    Font = { Size = 16 * 20, Weight = ExcelFont.BoldWeight }
                };

                CellRange subHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                   REPORT_SUB_HEADER_CELL_NUMBER);
                subHeader.Merged = true;
                subHeader.Style  = styleReportSubHeader;
                subHeader.Value  = string.Format("Camp Year: {0}  Generated on {1} {2}", ddlCampYear.SelectedItem.Text,
                                                 DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());

                // Create Federation Name row
                iRow += 2;

                var cs = new CellStyle {
                    Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight }
                };
                CellRange fedNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                      CAMP_NAME_MERGED_CELL_NUMBER);
                fedNameRange.Merged = true;
                fedNameRange.Style  = cs;
                fedNameRange.Value  = ddlFed.SelectedItem.Text;

                // admin name
                iRow += 1;
                //var cs = new CellStyle { Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight } };
                CellRange adminNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                        CAMP_NAME_MERGED_CELL_NUMBER);
                adminNameRange.Merged = true;
                //fedNameRange.Style = cs;
                adminNameRange.Value = String.Format("Report Generated by: {0} {1}", Session["FirstName"],
                                                     Session["LastName"]);

                // report type name
                iRow += 2;
                var reportTypeStyle = new CellStyle {
                    Font = { Size = 10 * 20, Weight = ExcelFont.BoldWeight }
                };
                var reportTypeText = "";
                if (isFinal)
                {
                    reportTypeStyle.FillPattern.SetSolid(Color.ForestGreen);
                    reportTypeStyle.Font.Color = Color.White;
                    reportTypeText             = "FINAL REPORT.  ALL RECORDS HAVE BEEN UPDATED TO PAYMENT REQUESTED.";
                }
                else
                {
                    reportTypeStyle.FillPattern.SetSolid(Color.Yellow);
                    reportTypeStyle.Font.Color = Color.Red;
                    reportTypeText             = "IMPORTANT:  This is a Preliminary Report.  Once data confirmed, run a FINAL report.";
                }

                CellRange reportTypeNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
                reportTypeNameRange.Merged = true;
                reportTypeNameRange.Style  = reportTypeStyle;
                reportTypeNameRange.Value  = reportTypeText;

                iRow += 1;

                CellRange campNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
                campNameRange.Merged = true;
                campNameRange.Style  = cs;
                campNameRange.Value  = campName;

                iRow += 1;

                // this creats the real table
                dt.Columns.Remove("CampID");
                dt.Columns.Remove("CampName");
                dt.Columns.Remove("StatusID");
                ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);

                // decorate the header of content table
                // loop through each column and 1.set the width of each colum, 2. set the header style of each column
                var tableHeaderStyle = new CellStyle {
                    Font = { Weight = ExcelFont.BoldWeight }
                };
                tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);

                for (int i = BEGIN_COLUMN_INDEX; i < dt.Columns.Count; i++)
                {
                    ws.Cells[iRow, i].Style = tableHeaderStyle;

                    if (i == 3)
                    {
                        ws.Columns[i].Width = 30 * 256;
                    }
                    else if (i == 4)
                    {
                        ws.Columns[i].Width = 8 * 256;
                    }
                    else if (i == 5)
                    {
                        ws.Columns[i].Width = 28 * 256;
                    }
                    else if (i == 7)
                    {
                        ws.Columns[i].Width = 16 * 256;
                    }
                    else
                    {
                        ws.Columns[i].Width = 18 * 256;
                    }
                }

                var totalGrantAmount = dt.AsEnumerable().Sum(x => x.Field <double>("GrantAmount"));

                var totalCamperCount = dt.Rows.Count;

                iRow += dt.Rows.Count + 2;
                var boldTextStyle = new CellStyle {
                    Font = { Weight = ExcelFont.BoldWeight }
                };
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Value = "Total Dollars";
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Style = boldTextStyle;
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Value = totalGrantAmount;
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Style = boldTextStyle;

                iRow += 1;
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Value = "# of campers";
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 7].Style = boldTextStyle;
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Value = totalCamperCount;
                ws.Cells[iRow, BEGIN_COLUMN_INDEX + 8].Style = boldTextStyle;
            }
        }

        if (dtAllCamps.Rows.Count > 0)
        {
            // ******** Summary worksheet ***************
            IEnumerable <DataRow> enumSummary = from rows in summaryTable.AsEnumerable()
                                                select rows;

            var sumTimer = enumSummary.Sum(x => x.Field <int>("1st/2nd/3rd Time"));
            var sumDS    = enumSummary.Sum(x => x.Field <int>("Day School"));
            var sumTotal = enumSummary.Sum(x => x.Field <double>("Total Dollars"));

            var dr = summaryTable.NewRow();
            dr["CampName"]         = "TOTAL";
            dr["1st/2nd/3rd Time"] = sumTimer;
            dr["Day School"]       = sumDS;
            dr["Total Dollars"]    = sumTotal;
            summaryTable.Rows.Add(dr);

            const int BEGIN_COLUMN_INDEX            = 0;
            const int REPORT_HEADER_CELL_NUMBER     = 5;
            const int REPORT_SUB_HEADER_CELL_NUMBER = 5;
            const int CAMP_NAME_MERGED_CELL_NUMBER  = 5;

            int iRow = 1;

            var ws = excel.Worksheets.AddCopy("Summary", excel.Worksheets["Sheet1"]);

            // Global artistic setting
            ws.Columns[0].Width = 20 * 20; // make the first column smaller

            // Create Report Header
            var styleReportHeader = new CellStyle
            {
                Font = { Color = Color.Blue, Size = 22 * 20, Weight = ExcelFont.BoldWeight }
            };

            CellRange reportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                  REPORT_HEADER_CELL_NUMBER);
            reportHeader.Merged = true;
            reportHeader.Style  = styleReportHeader;
            reportHeader.Value  = "One Happy Camper Payment Report";

            ws.Rows[iRow].Height = 25 * 20;

            iRow += 1;

            // Create Report SubHeader - usually it's camp year and report generation time
            var styleReportSubHeader = new CellStyle {
                Font = { Size = 16 * 20, Weight = ExcelFont.BoldWeight }
            };

            CellRange subHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                               REPORT_SUB_HEADER_CELL_NUMBER);
            subHeader.Merged = true;
            subHeader.Style  = styleReportSubHeader;
            subHeader.Value  = string.Format("Camp Year: {0}  Generated on {1} {2}", ddlCampYear.SelectedItem.Text,
                                             DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());

            // Create Federation Name row
            iRow += 2;

            var cs = new CellStyle {
                Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight }
            };
            CellRange fedNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                  CAMP_NAME_MERGED_CELL_NUMBER);
            fedNameRange.Merged = true;
            fedNameRange.Style  = cs;
            fedNameRange.Value  = ddlFed.SelectedItem.Text;

            // admin name
            iRow += 1;
            //var cs = new CellStyle { Font = { Size = 18 * 20, Weight = ExcelFont.BoldWeight } };
            CellRange adminNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow,
                                                                    CAMP_NAME_MERGED_CELL_NUMBER);
            adminNameRange.Merged = true;
            //fedNameRange.Style = cs;
            adminNameRange.Value = String.Format("Report Generated by: {0} {1}", Session["FirstName"],
                                                 Session["LastName"]);

            // report type name
            iRow += 2;
            var reportTypeStyle = new CellStyle {
                Font = { Size = 10 * 20, Weight = ExcelFont.BoldWeight }
            };
            var reportTypeText = "";
            if (isFinal)
            {
                reportTypeStyle.FillPattern.SetSolid(Color.ForestGreen);
                reportTypeStyle.Font.Color = Color.White;
                reportTypeText             = "FINAL REPORT.  ALL RECORDS HAVE BEEN UPDATED TO PAYMENT REQUESTED.";
            }
            else
            {
                reportTypeStyle.FillPattern.SetSolid(Color.Yellow);
                reportTypeStyle.Font.Color = Color.Red;
                reportTypeText             = "IMPORTANT:  This is a Preliminary Report.  Once data confirmed, run a FINAL report.";
            }

            CellRange reportTypeNameRange = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
            reportTypeNameRange.Merged = true;
            reportTypeNameRange.Style  = reportTypeStyle;
            reportTypeNameRange.Value  = reportTypeText;

            iRow += 1;

            // this creats the real table
            ws.InsertDataTable(summaryTable, iRow, BEGIN_COLUMN_INDEX, true);

            // decorate the header of content table
            // loop through each column and 1.set the width of each colum, 2. set the header style of each column
            var tableHeaderStyle = new CellStyle {
                Font = { Weight = ExcelFont.BoldWeight }
            };
            tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
            var boldTextStyle = new CellStyle {
                Font = { Weight = ExcelFont.BoldWeight }
            };

            for (int i = BEGIN_COLUMN_INDEX; i < summaryTable.Columns.Count; i++)
            {
                ws.Cells[iRow, i].Style = tableHeaderStyle;
                ws.Cells[iRow + summaryTable.Rows.Count, i].Style = boldTextStyle;

                if (i == BEGIN_COLUMN_INDEX)
                {
                    ws.Columns[i].Width = 30 * 256;
                }
                else if (i == BEGIN_COLUMN_INDEX + 3)
                {
                    ws.Columns[i].Width = 20 * 256;
                }
                else
                {
                    ws.Columns[i].Width = 18 * 256;
                }
            }

            // ******** End of Summary worksheet ********

            excel.Worksheets[0].Delete();
            excel.Worksheets[0].Delete();
            excel.Worksheets[0].Delete();
            excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
        }


        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}-Payment.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset     = "";

        if (newFile.Length == 0)
        {
            Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #12
0
ファイル: DataExtract.aspx.cs プロジェクト: wenchenglai/CIPRS
    private void GenerateExcelReport()
    {
        string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
        string workFileDir  = Server.MapPath(@"~/Docs");

        // Make a excel report
        ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
        ExcelFile excel = new ExcelFile();

        excel.LoadXls(templateFile);

        ExcelWorksheet ws = excel.Worksheets["Sheet1"];

        // Data Content of report
        DataTable dt = GenerateDataSet();

        // The first row is decoration headers
        ws.Rows[1].Height = 2 * 256;
        int columnPointer = 9;

        CellStyle cs = new CellStyle();

        cs.FillPattern.SetSolid(Color.FromArgb(204, 153, 255));
        cs.Font.Weight         = ExcelFont.BoldWeight;
        cs.WrapText            = true;
        cs.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr = ws.Cells.GetSubrangeAbsolute(0, 0, 0, columnPointer);

        cr.Merged = true;
        cr.Value  = "Program Information";
        cr.Style  = cs;

        CellStyle cs2 = new CellStyle();

        cs2.FillPattern.SetSolid(Color.FromArgb(102, 255, 255));
        cs2.Font.Weight         = ExcelFont.BoldWeight;
        cs2.WrapText            = true;
        cs2.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr2 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 5);

        cr2.Merged = true;
        cr2.Value  = "Camp Information";
        cr2.Style  = cs2;

        CellStyle cs3 = new CellStyle();

        cs3.FillPattern.SetSolid(Color.FromArgb(255, 153, 204));
        cs3.Font.Weight         = ExcelFont.BoldWeight;
        cs3.WrapText            = true;
        cs3.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr3 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 8);

        cr3.Merged = true;
        cr3.Value  = "Basic Camper Information";
        cr3.Style  = cs3;

        CellStyle cs4 = new CellStyle();

        cs4.FillPattern.SetSolid(Color.FromArgb(204, 255, 204));
        cs4.Font.Weight         = ExcelFont.BoldWeight;
        cs4.WrapText            = true;
        cs4.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr4 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 5);

        cr4.Merged = true;
        cr4.Value  = "Camper Contact Information";
        cr4.Style  = cs4;

        CellStyle cs5 = new CellStyle();

        cs5.FillPattern.SetSolid(Color.FromArgb(255, 255, 153));
        cs5.Font.Weight         = ExcelFont.BoldWeight;
        cs5.WrapText            = true;
        cs5.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr5 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 14);

        cr5.Merged = true;
        cr5.Value  = "Parent Contact Information";
        cr5.Style  = cs5;

        CellStyle cs6 = new CellStyle();

        cs6.FillPattern.SetSolid(Color.FromArgb(150, 150, 150));
        cs6.Font.Weight         = ExcelFont.BoldWeight;
        cs6.WrapText            = true;
        cs6.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr6 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 3);

        cr6.Merged = true;
        cr6.Value  = "Application Information";
        cr6.Style  = cs6;

        CellStyle cs7 = new CellStyle();

        cs7.FillPattern.SetSolid(Color.FromArgb(255, 204, 153));
        cs7.Font.Weight         = ExcelFont.BoldWeight;
        cs7.WrapText            = true;
        cs7.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr7 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 8);

        cr7.Merged = true;
        cr7.Value  = "Marketing Source";
        cr7.Style  = cs7;

        CellStyle cs8 = new CellStyle();

        cs8.FillPattern.SetSolid(Color.FromArgb(153, 204, 255));
        cs8.Font.Weight         = ExcelFont.BoldWeight;
        cs8.WrapText            = true;
        cs8.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr8 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 16);

        cr8.Merged = true;
        cr8.Value  = "Demographic Information";
        cr8.Style  = cs8;

        // Toronto questions
        CellStyle cs9 = new CellStyle();

        cs9.FillPattern.SetSolid(Color.FromArgb(233, 19, 210));
        cs9.Font.Weight         = ExcelFont.BoldWeight;
        cs9.WrapText            = true;
        cs9.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr9 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 2, 0, columnPointer += 7);

        cr9.Merged = true;
        cr9.Value  = "FJC ONLY";
        cr9.Style  = cs9;

        // Grandfather questions
        var cs10 = new CellStyle();

        cs10.FillPattern.SetSolid(Color.FromArgb(133, 19, 110));
        cs10.Font.Weight         = ExcelFont.BoldWeight;
        cs10.WrapText            = true;
        cs10.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr10 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 4);

        cr10.Merged = true;
        cr10.Value  = "Grandfather Policy";
        cr10.Style  = cs10;

        // Chicago Siblings
        var cs11 = new CellStyle();

        cs11.FillPattern.SetSolid(Color.FromArgb(133, 119, 110));
        cs11.Font.Weight         = ExcelFont.BoldWeight;
        cs11.WrapText            = true;
        cs11.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        CellRange cr11 = ws.Cells.GetSubrangeAbsolute(0, columnPointer + 1, 0, columnPointer += 2);

        cr11.Merged = true;
        cr11.Value  = "Chicago Siblings";
        cr11.Style  = cs11;

        // this creats the real table
        ws.InsertDataTable(dt, 1, 0, true);

        CellStyle styleTableHeaderColumns = new CellStyle();

        styleTableHeaderColumns.Font.Weight         = ExcelFont.BoldWeight;
        styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        styleTableHeaderColumns.VerticalAlignment   = VerticalAlignmentStyle.Center;
        styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
        styleTableHeaderColumns.WrapText = true;

        ws.Columns[0].Width = 15 * 256;
        ws.Columns[1].Width = 35 * 256;
        ws.Columns[2].Width = 30 * 256;

        ws.Columns[11].Width = 35 * 256;         // Camp Name
        ws.Columns[12].Width = 27 * 256;

        ws.Columns[16].Width = 30 * 256;         // Last Name
        ws.Columns[17].Width = 30 * 256;

        ws.Rows[1].Style  = styleTableHeaderColumns;
        ws.Rows[1].Height = 3 * 256;

        CellStyle styleContent = new CellStyle();

        styleContent.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        styleContent.VerticalAlignment   = VerticalAlignmentStyle.Center;

        for (int i = 2; i < dt.Rows.Count; i++)
        {
            ws.Rows[i].Style = styleContent;
        }

        styleContent.WrapText = true;

        excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];

        // Save to a file on the local file system
        string filename = String.Format("\\{0}{1}{2}{3}DataExtract.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
        string newFile  = workFileDir + filename;

        excel.SaveXls(newFile);


        string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);

        //Display excel spreadsheet
        this.Response.Clear();
        this.Response.Buffer = true;
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.Charset     = "";

        if (newFile.Length == 0)
        {
            this.Response.Write("Error encountered - no spreadsheet to display");
        }
        else
        {
            this.Response.WriteFile(newFile);
        }

        Response.End();
    }
コード例 #13
0
        private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
        {
            //if the month is the aggStart use the blank template. Otherwise, use last months
            string xlsBaseFileName;

            if (aggStartDate == repDate)
            {
                xlsBaseFileName = "NNIN Data Blank.xls";
            }
            else
            {
                xlsBaseFileName = "AS" + aggStartDate.ToString("yyyyMM") + "_Report" + repDate.AddMonths(-1).ToString("yyyyMM") + ".xls";
            }

            string xlsBaseFilePath = xlsFilePath + xlsBaseFileName;
            string xlsReportPath   = xlsFilePath + xlsReportName;

            DateTime period  = repDate;
            DateTime sDate   = period;
            DateTime eDate   = sDate.AddMonths(1);
            Compile  compile = new Compile();

            //this gets the aggregated costs for rooms and tools
            DataSet   dsCostTables = NNINDA.GetCostTables(period);
            DataTable dtRoomCost   = dsCostTables.Tables[0];
            DataTable dtToolCost   = dsCostTables.Tables[1];

            //get Client info
            DataSet ds = DataCommand()
                         .Param("Action", "GetAllTables")
                         .Param("Period", period)
                         .Param("Privs", (int)ClientPrivilege.LabUser)
                         .Param("MakeCumUser", makeAggData)
                         .FillDataSet("dbo.NNIN_Select");

            ds.Tables[0].TableName = "ClientTechInt";
            ds.Tables[1].TableName = "AcctOrgType";
            ds.Tables[2].TableName = "TechnicalInterest_Hours";
            ds.Tables[3].TableName = "OrgType_Hours";
            ds.Tables[4].TableName = "TechnicalInterest_Users";
            ds.Tables[5].TableName = "OrgType_Users";
            ds.Tables[6].TableName = "TechnicalInterest_Fees";
            ds.Tables[7].TableName = "OrgType_Fees";

            DataColumn[] pk;

            pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
            ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;

            pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
            ds.Tables["OrgType_Fees"].PrimaryKey = pk;

            //calc fees by tech interest and orgtype
            DataRow drTI, drOT;

            DataRow[] fdr;
            foreach (DataTable dt in dsCostTables.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    fdr = ds.Tables["ClientTechInt"].Select(string.Format("ClientID = {0}", dr["ClientID"]));
                    if (fdr.Length > 0)
                    {
                        drTI = ds.Tables["TechnicalInterest_Fees"].Rows.Find(fdr[0]["TechnicalInterestID"]);

                        drTI["Fees"] = drTI.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
                        fdr          = ds.Tables["AcctOrgType"].Select(string.Format("AccountID = {0}", dr["AccountID"]));
                        if (fdr.Length > 0)
                        {
                            drOT         = ds.Tables["OrgType_Fees"].Rows.Find(fdr[0]["OrgTypeID"]);
                            drOT["Fees"] = drOT.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
                        }
                    }
                }
            }

            //get raw cumulative user data
            DataCommand()
            .Param("Action", "Aggregate")
            .Param("sDate", aggStartDate)
            .Param("eDate", repDate)
            .FillDataSet(ds, "dbo.CumUser_Select", "CumUser");

            DataTable dtReport = new DataTable();

            dtReport.Columns.Add("BaseRow", typeof(int));
            dtReport.Columns.Add("TotalRow", typeof(int));
            dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum
            dtReport.Columns.Add("ByType", typeof(string));  //orgtype, techid, none
            dtReport.Columns.Add("DateOnly", typeof(bool));  //orgtype, techid, none

            AddReportRow(dtReport, new object[] { 10, 0, string.Empty, string.Empty, true });
            AddReportRow(dtReport, new object[] { 24, 43, "Hours", "TechnicalInterest", false });
            AddReportRow(dtReport, new object[] { 11, 43, "Hours", "OrgType", false });
            AddReportRow(dtReport, new object[] { 40, 0, string.Empty, string.Empty, true });
            AddReportRow(dtReport, new object[] { 69, 88, "Users", "TechnicalInterest", false });
            AddReportRow(dtReport, new object[] { 55, 88, "Users", "OrgType", false });
            AddReportRow(dtReport, new object[] { 85, 0, string.Empty, string.Empty, true });
            AddReportRow(dtReport, new object[] { 161, 180, "Fees", "TechnicalInterest", false });
            AddReportRow(dtReport, new object[] { 146, 180, "Fees", "OrgType", false });
            AddReportRow(dtReport, new object[] { 177, 0, string.Empty, string.Empty, true });
            AddReportRow(dtReport, new object[] { 116, 136, "CumUser", "TechnicalInterest", false });
            AddReportRow(dtReport, new object[] { 101, 136, "CumUser", "OrgType", false });
            AddReportRow(dtReport, new object[] { 133, 0, string.Empty, string.Empty, true });
            AddReportRow(dtReport, new object[] { 226, 0, "CumUser", "DemGender", false });
            AddReportRow(dtReport, new object[] { 232, 0, "CumUser", "DemEthnic", false });
            AddReportRow(dtReport, new object[] { 238, 0, "CumUser", "DemRace", false });
            AddReportRow(dtReport, new object[] { 247, 0, "CumUser", "DemDisability", false });

            ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
            ExcelFile SpreadSheet = new ExcelFile();

            SpreadSheet.LoadXls(xlsBaseFilePath);
            ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];

            int    lastVal, useCol = 0, useRow;
            double hardTotals; //for recording internal time without formula

            foreach (DataRow drReport in dtReport.Rows)
            {
                //write dates
                for (int j = 0; j < 12; j++)
                {
                    ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
                    if (aggStartDate.AddMonths(j) == repDate)
                    {
                        useCol = j + 1;
                    }
                }

                if (!Convert.ToBoolean(drReport["DateOnly"]))
                {
                    hardTotals = 0;
                    if (drReport["RepType"].ToString() == "CumUser")
                    {
                        fdr = ds.Tables["CumUser"].Select(string.Format("TableName = '{0}'", drReport["ByType"]));
                        for (int j = 0; j < fdr.Length; j++)
                        {
                            useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
                            if (aggStartDate == repDate)
                            {
                                ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
                            }
                            else
                            {
                                lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
                                for (int i = 2; i < useCol; i++)
                                {
                                    string form = ws.Cells[useRow, i].Formula;
                                    if (!string.IsNullOrEmpty(form))
                                    {
                                        lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
                                    }
                                }
                                ws.Cells[useRow, useCol].Formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
                            }

                            hardTotals += fdr[j].Field <double>("Count");
                        }

                        if (Convert.ToInt32(drReport["TotalRow"]) != 0)
                        {
                            ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
                        }
                    }
                    else
                    {
                        foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
                        {
                            useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(string.Format("{0}ID", drReport["ByType"]));
                            ws.Cells[useRow, useCol].Value = dr[drReport["RepType"].ToString()];
                            hardTotals += Convert.ToDouble(dr[drReport["RepType"].ToString()]);
                        }

                        ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
                    }
                }
            }

            SpreadSheet.SaveXls(xlsReportPath);
            SpreadSheet = null;
            System.GC.Collect();

            return(xlsReportPath);
        }