SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
brian.cook
brian.cook
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 123
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[b].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
brian.cook
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 123
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[b].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 = "#.##%";
}

Sam Langley
Sam Langley
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 2
Isn't just easier to format the values in Excel as a % once imported?
brian.cook
brian.cook
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 123
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search