Visual Studio C# project. Export DataTable to Excel formatting of column

  • brian.cook

    Ten Centuries

    Points: 1138

    Hi All,

    I am working on a C# project that pulls data from an SQL database, formats it in a DataGridView, and then export it to an Excel spreadsheet.

    My Query returns what I need, however I am struggling with formatting the resulting value of part of the query into a Percentage format. I can make the column show up as Percentage in the DataGridView, however on export, it does not export with the formatting of the column and goes back to the raw value of the column.

    So, looking for help making the Query so that it reflects Percentage for the resulting field, and if someone knows how to fix the export to excel with that column showing percentage correctly, that would much appreciated.

    Here is the query;
    SELECT
           T1.CODELINE AS Codeline,
           T1.CODELINELIMITS AS CodeLineLimits,
           T1.TYPE AS Type,
           sum(t1.CONTROLFAILCOUNT) as ControlFailCount,
           sum(t1.CONTROLSENTCOUNT) as ControlSentCount,
           sum(t1.INDICATIONCOUNT) as IndicationCount,
    //this is the section I need help with
           1 - (sum(t1.CONTROLFAILCOUNT) / (sum(T1.CONTROLSENTCOUNT)*1.0000)) as SUCCESSRATIO
    //
    FROM
    (SELECT
           tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth.CODELINE,
           tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type,
           tmdsDatabaseStatic.dbo.tblCodelines.CodeLineLimits,
           tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth.CONTROLFAILCOUNT,
           tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth.CONTROLSENTCOUNT,
           tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth.INDICATIONCOUNT      
    FROM
    tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines
    ON tmdsDatabaseStatistics.dbo.tblStatisticsCodeStationMonth.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber

    WHERE month= 10 AND codeline <> 415 AND year = 2017 AND CONTROLSENTCOUNT <> 0)
    AS T1
    GROUP BY
           T1.CODELINE,
           T1.TYPE,
           T1.CODELINELIMITS
    ORDER BY  
    Type, ControlFailCount DESC, Codeline DESC

    And here is the Visual Studio Code behind.

           
    private void BtnSaveReport_Click(object sender, EventArgs e)
            {

                Excel.Application xlApp;
                CultureInfo CurrentCI = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;
                object misValue = Missing.Value;
                xlApp = new Excel.Application
                {
                    DisplayAlerts = false
                };
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = "Control Failures Report";
                for (var r = 0; r < dt.Rows.Count; r++)
                {
                    for (var a = 0; a < dt.Columns.Count; a++)
                    {
                        xlWorkSheet.Cells[r + 2, a + 1] = dt.Rows[r][a].ToString();
                    }
                }
                for (var b = 0; b < dt.Columns.Count; b++)
                {
                    xlWorkSheet.Cells[1, b + 1] = dt.Columns.ColumnName;
                    range = xlWorkSheet.Cells[1, b + 1];
                    range.Interior.ColorIndex = 15;
                    range.Font.Bold = true;
                    //range = (Excel.Range)xlWorkSheet.Cells[1, 7];
                    //range.EntireColumn.NumberFormat = "P2";
                }
                Excel.Range columns = xlWorkSheet.UsedRange.Columns;
                columns.AutoFit();
                xlWorkSheet.Rows[1].Insert();
                Excel.Range newRow = xlWorkSheet.Rows[1];
                Excel.Range newCell = newRow.Cells[1];
                newCell.Value = DateTime.Now.ToString("dd/MM/yyyy");
                //Name and location of the file. By default it will save in Documents\filename
                //This saves the file in Office 12 format
                xlWorkBook.SaveAs("ControlFailuresAuto.xlsx",
                    Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, false, false,
                    Excel.XlSaveAsAccessMode.xlNoChange,
                    Excel.XlSaveConflictResolution.xlUserResolution, true
                    , misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                ReleaseObject(xlWorkSheet);
                ReleaseObject(xlWorkBook);
                ReleaseObject(xlApp);
                MessageBox.Show("Report created, you can find the file Documents\\ControlFailuresAuto.xlsx");
                Cursor.Current = Cursors.Default;
            }

    Thanks!

  • brian.cook

    Ten Centuries

    Points: 1138

    I figured out part of the answer.

    In the Export function, I added the line range[2, 7].EntireColumn.NumberFormat="#.##%;. This now displays the calculated value as Percentage on export. I still have a minor issue where it addes the decimal point after a resulting value of 100%, so I still need to work on that one.


                for (var b = 0; b < dt.Columns.Count; b++)
                {
                    xlWorkSheet.Cells[1, b + 1] = dt.Columns.ColumnName;
                    range = xlWorkSheet.Cells[1, b + 1];
                    range.Interior.ColorIndex = 15;
                    range.Font.Bold = true;
                    //range = (Excel.Range)xlWorkSheet.Cells[1, 7];
                    range[2, 7].EntireColumn.NumberFormat = "#.##%";
                }

  • samcrgh

    Grasshopper

    Points: 23

    Isn't just easier to format the values in Excel as a % once imported?

  • brian.cook

    Ten Centuries

    Points: 1138

    Yes, but I am trying to avoid having to make the user change anything. This will eventually go to upper  management, so taking all the extra steps out is a must.

  • edwardcorner

    Grasshopper

    Points: 13

    Excel.Range procentRange = xlWorksheet.get_Range("A1","A1"); 
    procentRange.NumberFormat = "###,##%";

    More...C# DataGRidView Tutorial

  • evanslandon810

    Newbie

    Points: 5

    there is another approach. check out  https://zetexcel.com/. hope it will help you.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply