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

Question

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

brian.cook

Default port

Points: 1484
More actions
October 18, 2017 at 6:42 am

#151129

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!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

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

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

brian.cook Default port Points: 1484 More actions · Answer 1

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 = "#.##%"; }

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

samcrgh Grasshopper Points: 23 More actions · Answer 2

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

brian.cook Default port Points: 1484 More actions · Answer 3

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.

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

edwardcorner Grasshopper Points: 13 More actions · Answer 4

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

More...C# DataGRidView Tutorial

evanslandon810 Newbie Points: 5 More actions · Answer 5

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