Exemple #1
0
        public void MakeProjectReport(Window winref, DataSet ds)
        {
            try
            {
                using (var xl = new ExcelPackage())
                {
                    DataTable dt = ds.Tables[Constants.SingleProjectReport];

                    var ws            = xl.Workbook.Worksheets.Add(dt.TableName.ToString());
                    int dtcolumncount = dt.Columns.Count;
                    int dtrowcount    = dt.Rows.Count;

                    for (int i = 0; i < dtrowcount; i++)
                    {
                        for (int j = 0; j < dtcolumncount; j++)
                        {
                            ws.Cells[i + 2, j + 1].Value = dt.Rows[i][j];
                        }
                    }

                    int firstrow     = 1;
                    int firstdatarow = firstrow + 1;
                    int lastdatarow  = dt.Rows.Count + 1;

                    FormatColumns(dt, ref ws, 1, new Collection <int>());

                    //make activities table
                    DataTable dtactivities = ds.Tables[Constants.SingleProjectReportActivities];
                    if (dtactivities.Columns.Count > 0 && dtactivities.Rows.Count > 0)
                    {
                        int dtactcolumncount  = dtactivities.Columns.Count;
                        int captionrow        = 4;
                        int startrow          = captionrow + 1;
                        int colourcolumnindex = 0;

                        for (int i = 0; i < dtactivities.Columns.Count; i++)
                        {
                            if (dtactivities.Columns[i].Caption == "StatusColour")
                            {
                                colourcolumnindex = i;
                            }
                            else
                            {
                                ws.Cells[startrow, i + 1].Value = dtactivities.Columns[i].Caption;
                                ws.Cells[startrow, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                            }
                        }

                        for (int i = 0; i < dtactivities.Rows.Count; i++)
                        {
                            for (int j = 0; j < dtactivities.Columns.Count; j++)
                            {
                                if (j != colourcolumnindex)
                                {
                                    ws.Cells[i + startrow, j + 1].Value = dtactivities.Rows[i][j] ?? string.Empty;
                                }
                            }
                        }

                        //Format top row
                        var actrangeheader = ws.Cells[captionrow, 1, captionrow, dtactcolumncount - 1].Style;
                        actrangeheader.Font.Bold        = true;
                        actrangeheader.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        actrangeheader.Fill.BackgroundColor.SetColor(Color.AliceBlue);
                        actrangeheader.Font.Color.SetColor(Color.Black);
                        actrangeheader.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws.Row(captionrow).Height        = 30;

                        FormatSecondTable(dtactivities, ref ws, startrow - 1, colourcolumnindex);

                        //Format status column
                        int statuscolumn = 2;
                        for (int p = startrow; p < startrow + dtactivities.Rows.Count; p++)
                        {
                            var cell = ws.Cells[p, statuscolumn].Value;
                            if (!string.IsNullOrEmpty(cell.ToString()))
                            {
                                ws.Cells[p, statuscolumn].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                ws.Cells[p, statuscolumn].Style.Fill.BackgroundColor.SetColor(GetColourFromString(dtactivities.Rows[p - startrow][colourcolumnindex].ToString()));
                            }
                            //Format status month rows
                            ws.Cells[p, 1].Style.Numberformat.Format = "MMM-yyy";
                            ws.Cells[p, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        }
                        ws.Column(statuscolumn).AutoFit();
                        ws.Column(statuscolumn + 1).Width = 40;
                        ws.Cells[captionrow, 1, captionrow + dtactivities.Rows.Count, dtactcolumncount - 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
                    }
                    string projectid = " ID " + dt.Rows[0][0].ToString();
                    try
                    {
                        string filename = SaveAs(winref, dt.TableName.ToString() + projectid);
                        if (!string.IsNullOrEmpty(filename))
                        {
                            xl.SaveAs(new System.IO.FileInfo(filename));
                            Process.Start(filename);
                        }
                    }
                    catch (Exception e)
                    {
                        IMessageBoxService msg = new MessageBoxService();
                        msg.ShowMessage("The file is already open.\nPlease close or select a different file name\n" + e.Message, "File already open", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Exclamation);
                        msg = null;
                    }
                }
            }
            catch
            {
                IMessageBoxService msg = new MessageBoxService();
                msg.ShowMessage("An unexpected error has occurred in MakeProjectReport", "Error", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Error);
                msg = null;
            }
        }
Exemple #2
0
        public void MakeCustomReport(Window winref, DataSet ds, CustomReportModel reportdetails)
        {
            try
            {
                using (var xl = new ExcelPackage())
                {
                    ExcelWorksheet ws;
                    ws = xl.Workbook.Worksheets.Add(reportdetails.Name);

                    int firstrow      = 1;
                    int firstdatarow  = firstrow + 1;
                    int lastdatarow   = firstrow;
                    int dtcolumncount = 0;
                    int dtrowcount    = 0;
                    int tblctr        = 0;
                    foreach (DataTable dt in ds.Tables)
                    {
                        if (!reportdetails.CombineTables)
                        {
                            if (tblctr == 0)
                            {
                                ws.Name = dt.TableName;
                            }
                            else
                            {
                                ws = xl.Workbook.Worksheets.Add(dt.TableName);
                            }
                        }

                        tblctr++;
                        dtcolumncount = dt.Columns.Count;
                        dtrowcount    = dt.Rows.Count;

                        for (int i = 0; i < dtrowcount; i++)
                        {
                            for (int j = 0; j < dtcolumncount; j++)
                            {
                                ws.Cells[firstrow + i + 1, j + 1].Value = dt.Rows[i][j];
                            }
                        }

                        FormatCustomTable(dt, ref ws, firstrow);

                        if (reportdetails.CombineTables)
                        {
                            firstrow     = dtrowcount + firstrow + 2; //2 rows between tables
                            firstdatarow = firstrow + 1;
                        }
                    }

                    try
                    {
                        string filename = SaveAs(winref, reportdetails.Name);
                        if (!string.IsNullOrEmpty(filename))
                        {
                            xl.SaveAs(new System.IO.FileInfo(filename));
                            Process.Start(filename);
                        }
                    }
                    catch (Exception e)
                    {
                        IMessageBoxService msg = new MessageBoxService();
                        msg.ShowMessage("The file is already open.\nPlease close or select a different file name\n" + e.Message, "File already open", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Exclamation);
                        msg = null;
                    }
                }
            }
            catch
            {
                IMessageBoxService msg = new MessageBoxService();
                msg.ShowMessage("An unexpected error has occurred in MakeCustomReport", "Error", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Error);
                msg = null;
            }
        }
Exemple #3
0
        public void MakeGenericReport(Window winref, DataTable dt)
        {
            try
            {
                using (var xl = new ExcelPackage())
                {
                    var ws            = xl.Workbook.Worksheets.Add(dt.TableName.ToString());
                    int dtcolumncount = dt.Columns.Count;
                    int dtrowcount    = dt.Rows.Count;

                    int firstrow     = 1;
                    int firstdatarow = firstrow + 1;
                    int lastdatarow  = dtrowcount + 1;

                    int statuscolumn              = -1;
                    int colourcolumn              = -1;
                    int projecttypecolourcol      = -1;
                    Collection <int> excludedcols = new Collection <int>();

                    for (int col = 0; col < dtcolumncount; col++)
                    {
                        if (dt.Columns[col].ColumnName == "StatusColour")
                        {
                            colourcolumn = col;
                            excludedcols.Add(col);
                        }
                        else
                        if (dt.Columns[col].ColumnName == "ProjectTypeColour")
                        {
                            projecttypecolourcol = col;
                            excludedcols.Add(col);
                        }
                        else
                        {
                            if (dt.Columns[col].ColumnName == "SalesFunnelStage" || dt.Columns[col].ColumnName == "Status")
                            {
                                statuscolumn = col + 1;
                                if (colourcolumn != -1)
                                {
                                    statuscolumn--;
                                }
                                if (projecttypecolourcol != -1)
                                {
                                    statuscolumn--;
                                }
                            }
                        }
                    }

                    FormatColumns(dt, ref ws, 1, excludedcols);

                    int colctr = 1;
                    for (int i = 0; i < dtrowcount; i++)
                    {
                        colctr = 1;
                        for (int j = 0; j < dtcolumncount; j++)
                        {
                            if (!excludedcols.Contains(j))
                            {
                                if (projecttypecolourcol != -1)
                                {
                                    var cell = ws.Cells[i + 2, colctr].Style.Fill;
                                    cell.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                    cell.BackgroundColor.SetColor(GetColourFromString(dt.Rows[i][projecttypecolourcol].ToString()));
                                    ws.Cells[i + 2, colctr].Value = dt.Rows[i][j];
                                }
                                else
                                {
                                    ws.Cells[i + 2, colctr].Value = dt.Rows[i][j];
                                }
                                colctr++;
                            }
                        }
                    }

                    if (statuscolumn > -1 && colourcolumn > -1)
                    {
                        for (int p = firstdatarow; p <= lastdatarow; p++)
                        {
                            var cell = ws.Cells[p, statuscolumn].Style.Fill;
                            cell.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            cell.BackgroundColor.SetColor(GetColourFromString(dt.Rows[p - firstdatarow][colourcolumn].ToString()));
                        }
                    }

                    for (int g = 1; g < colctr; g++)
                    {
                        ws.Column(g).AutoFit();
                    }

                    try
                    {
                        string filename = SaveAs(winref, dt.TableName.ToString());
                        if (!string.IsNullOrEmpty(filename))
                        {
                            xl.SaveAs(new System.IO.FileInfo(filename));
                            Process.Start(filename);
                        }
                    }
                    catch (Exception e)
                    {
                        IMessageBoxService msg = new MessageBoxService();
                        msg.ShowMessage("The file is already open.\nPlease close or select a different file name\n" + e.Message, "File already open", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Exclamation);
                        msg = null;
                    }
                }
            }
            catch
            {
                IMessageBoxService msg = new MessageBoxService();
                msg.ShowMessage("An unexpected error has occurred in MakeGenericReport", "Error", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Error);
                msg = null;
            }
        }
Exemple #4
0
        public void MakeSalesPipelineReport(Window winref, DataTable dt, DataTable projectcountdata)
        {
            try
            {
                using (var xl = new ExcelPackage())
                {
                    var ws            = xl.Workbook.Worksheets.Add(dt.TableName.ToString());
                    int dtcolumncount = dt.Columns.Count;

                    DateTime newdate;

                    int summarydatacolumncount  = dt.Columns.Count;
                    int summarydatarowcount     = dt.Rows.Count;
                    int firstrowsummarydata     = 2;
                    int firstdatarowsummarydata = firstrowsummarydata + 1;
                    int lastdatarowsummarydata  = summarydatarowcount + firstrowsummarydata;

                    int colourcolumn = 0;
                    ws.Cells[1, 1].Value = dt.TableName;// "Projects Value";
                    //format first row
                    for (int i = 0; i < summarydatacolumncount; i++)
                    {
                        if (DateTime.TryParse(dt.Columns[i].ColumnName, out newdate))
                        {
                            ws.Cells[firstrowsummarydata, i + 1].Value = newdate.ToString("MMM yyyy");
                            ws.Column(i + 1).Width = 10;
                        }
                        else
                        {
                            ws.Cells[firstrowsummarydata, i + 1].Value = dt.Columns[i].ColumnName;
                        }
                        ws.Cells[firstrowsummarydata, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        if (dt.Columns[i].ColumnName == "StatusColour")
                        {
                            colourcolumn = i + 1;
                        }
                    }
                    //load data
                    for (int i = 0; i < summarydatarowcount; i++)
                    {
                        for (int j = 0; j < summarydatacolumncount; j++)
                        {
                            ws.Cells[i + firstdatarowsummarydata, j + 1].Value = dt.Rows[i][j];
                        }
                    }

                    //colour rows according to status
                    for (int p = firstdatarowsummarydata; p <= lastdatarowsummarydata; p++)
                    {
                        if (colourcolumn > 0)
                        {
                            var result   = ws.Cells["A" + p.ToString()].Value;
                            var newrange = ws.Cells[p, 1, p, summarydatacolumncount];
                            newrange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            newrange.Style.Fill.BackgroundColor.SetColor(GetColourFromString(ws.Cells[p, colourcolumn].Value.ToString()));
                        }
                    }

                    //Format top row
                    var rangeheader = ws.Cells[firstrowsummarydata, 1, firstrowsummarydata, summarydatacolumncount].Style;
                    rangeheader.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rangeheader.Fill.BackgroundColor.SetColor(Color.AliceBlue);
                    rangeheader.Font.Color.SetColor(Color.Black);
                    rangeheader.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    rangeheader.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    ws.Row(firstrowsummarydata).Height = 30;

                    //Format grid
                    var gridrowrange = ws.Cells[firstrowsummarydata, 1, lastdatarowsummarydata, summarydatacolumncount].Style.Border;
                    gridrowrange.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    gridrowrange.Right.Color.SetColor(System.Drawing.Color.LightGray);
                    gridrowrange.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    gridrowrange.Top.Color.SetColor(System.Drawing.Color.LightGray);

                    //Format numerical data cells
                    for (int i = 2; i < summarydatacolumncount + 1; i++)
                    {
                        var currencyrange = ws.Cells[firstdatarowsummarydata, i, lastdatarowsummarydata, i];
                        currencyrange.Style.Numberformat.Format = "$ #,##0";

                        var datasalesrange = ws.Cells[firstdatarowsummarydata, i, lastdatarowsummarydata, i];
                        datasalesrange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;

                        ws.Column(i).AutoFit();
                    }

                    //Format Status column
                    var statusrange = ws.Cells[firstrowsummarydata, 1, lastdatarowsummarydata, 1];
                    statusrange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                    ws.Column(1).AutoFit();

                    //==========================================================================================================
                    //ADD PROJECTCOUNT TABLE
                    int firstrowprojectcount     = 16;
                    int firstdatarowprojectcount = firstrowprojectcount + 1;
                    int lastdatarowprojectcount  = firstrowprojectcount + projectcountdata.Rows.Count;
                    int columncountprojectcount  = projectcountdata.Columns.Count;

                    ws.Cells["A" + (firstrowprojectcount - 1).ToString()].Value = projectcountdata.TableName;// "Number of Projects";

                    for (int i = 0; i < columncountprojectcount; i++)
                    {
                        if (DateTime.TryParse(projectcountdata.Columns[i].ColumnName, out newdate))
                        {
                            ws.Cells[firstrowprojectcount, i + 1].Value = newdate.ToString("MMM yyyy");
                        }
                        else
                        {
                            ws.Cells[firstrowprojectcount, i + 1].Value = projectcountdata.Columns[i].ColumnName;
                        }

                        ws.Cells[firstrowprojectcount, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                    //load data
                    for (int i = 0; i < projectcountdata.Rows.Count; i++)
                    {
                        for (int j = 0; j < columncountprojectcount; j++)
                        {
                            ws.Cells[i + firstdatarowprojectcount, j + 1].Value = projectcountdata.Rows[i][j];
                        }
                    }

                    //colour rows according to status
                    for (int p = firstdatarowprojectcount; p <= lastdatarowprojectcount; p++)
                    {
                        if (colourcolumn > 0)
                        {
                            var result   = ws.Cells["A" + p.ToString()].Value;
                            var newrange = ws.Cells[p, 1, p, columncountprojectcount];
                            newrange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            newrange.Style.Fill.BackgroundColor.SetColor(GetColourFromString(ws.Cells[p, colourcolumn].Value.ToString()));
                        }
                    }
                    //Format top row
                    rangeheader = ws.Cells[firstrowprojectcount, 1, firstrowprojectcount, columncountprojectcount].Style;
                    rangeheader.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    rangeheader.Fill.BackgroundColor.SetColor(Color.AliceBlue);
                    rangeheader.Font.Color.SetColor(Color.Black);
                    rangeheader.VerticalAlignment       = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws.Row(firstrowprojectcount).Height = 30;

                    //Format grid
                    var projcountrange = ws.Cells[firstrowprojectcount, 1, lastdatarowprojectcount, columncountprojectcount].Style.Border;
                    projcountrange.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    projcountrange.Right.Color.SetColor(Color.LightGray);
                    projcountrange.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    projcountrange.Top.Color.SetColor(Color.LightGray);

                    //Format Status column
                    var projectcountstatusrange = ws.Cells[firstrowprojectcount, 1, lastdatarowprojectcount, 1];
                    projectcountstatusrange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                    //remove colour column
                    if (colourcolumn > 0)
                    {
                        ws.DeleteColumn(colourcolumn);
                    }

                    try
                    {
                        string filename = SaveAs(winref, Constants.SalesPipelineReportName);
                        if (!string.IsNullOrEmpty(filename))
                        {
                            xl.SaveAs(new System.IO.FileInfo(filename));
                            Process.Start(filename);
                        }
                    }
                    catch (Exception e)
                    {
                        IMessageBoxService msg = new MessageBoxService();
                        msg.ShowMessage("The file is already open.\nPlease close or select a different file name\n" + e.Message, "File already open", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Exclamation);
                        msg = null;
                    }
                }
            }
            catch
            {
                IMessageBoxService msg = new MessageBoxService();
                msg.ShowMessage("An unexpected error has occurred in MakeSalesPipelineReport", "Error", GenericMessageBoxButton.OK, GenericMessageBoxIcon.Error);
                msg = null;
            }
        }