C# exporting to Excel format invalid

Question

I am trying to export datagrid's data to an excel sheet in C#. Using below code I managed to export it successfully

private void ExportToExcelAndCsv()
        {
            dataGrid.SelectAllCells();
            dataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
            ApplicationCommands.Copy.Execute(null, dataGrid);
            String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
            String result = (string)Clipboard.GetData(DataFormats.Text);
            dataGrid.UnselectAllCells();
            System.IO.StreamWriter file1 = new System.IO.StreamWriter(@"C:\Users\Fisniku\Desktop\"+tipiL+".xls");
            file1.WriteLine(result.Replace(',', ' '));
            file1.Close();
        }

My issue is that I have a column in String datatype in SQLServer containing data in format of fractions such as:

1/2
2/2
1/3
9/4

When data is exported in excel, that column becomes a date, and shows data like a date 01-Jan in Custom format.

After trying to modify the column in excel to text format, it looses value and becomes invalid.

How can I modify the code so it will preserve the same format as in datagrid?

naxxo · Answer 1 · 2019-08-29T18:13:12.293

0

You may need to format the cell as text. Take a look at this question: Format an Excel column (or cell) as Text in C#?

edited Aug 29 '19 at 18:13

answered Aug 29 '19 at 18:05

naxxo

15
6

This answer will be useless if the link ever goes dead. You should clip the pertinent text from it and put it in your answer itself. – Tab Alleman Aug 29 '19 at 18:06
Thanks Tab, I changed the answer to a better one – naxxo Aug 29 '19 at 18:13

score 0 · Accepted Answer · answered Aug 30 '19 at 20:38

I am assuming that this data in the grid originates from a SQL query or table based on the tags on your question. If that's the case, and if either the data is read-only or the data is in a datatable that is bound to the grid, then an easy way of accomplishing this is to use EPPlus instead of interop.

EPPlus has a single command to export a C# datatable (System.Data.DataTable) into a spreadsheet:

ws.Cells["A1"].LoadFromDataTable(dt, true);

And, per your original problem, it respects datatypes meaning it won't clobber leading zeroes on text fields that have all numbers, convert text that looks like a date into a date, etc.

If you're not using a datatable, you can always convert your content to a datatable (although there is a lot of overhead with this approach).

If you are married to interop, then just be sure you format the appropriate columns as text BEFORE you do the paste:

sheet.Range["A:A"].NumberFormat = "@";

C# exporting to Excel format invalid

2 Answers2