Gridview export to excel - Formatting problems

Question

I am not sure about what is going on here. I use this button event to export data from datagridview to excel and the data get exported, file saved, etc.., so it looks like is working fine to me.

private void button2_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
        app.Visible = true;
        try
        {
            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
            worksheet.Name = "Gioietta Environment Data";
            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
            string fileName = String.Empty;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Excel files |*.xls|All files (*.*)|*.*";
            saveFileDialog1.FilterIndex = 2;
            saveFileDialog1.RestoreDirectory = true;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                fileName = saveFileDialog1.FileName;
                workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            else
                return;               
        }
        catch (System.Exception ex)
        {

        }
        finally
        {
            app.Quit();
            workbook = null;
            app = null;
        }
    }

The problem I am experiencing is on that the excel side. Basically even formatting the exported values as numbers, I cannot use them. I cannot even sum them up! If I re-type the value in the same cell manually than it become usable. Has this something to to with the exporting process?

This is how I load the data to the datagridview:

var time = DateTime.Now.ToString("HH:mm:ss");
        dataGridView1.Rows.Add(new string[] { time, textBox1.Text, textBox2.Text });

enter image description here

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

The problem comes from this line and how DataGridView is populated:

worksheet.Cells[i   2, j   1] = dataGridView1.Rows[i].Cells[j].Value.ToString();

Data are exported as string whatever the type due to .ToString() and they are string anyway.

As DataGridViewCell.Value contains a string, so you have to cast the dgvCell.Value to a numeric value before exporting. For example:

if (j == 1 || j == 2) 
{
    worksheet.Cells(i   2, j   1) = Convert.ToDecimal(dataGridView1.Rows(i).Cells(j).Value)
} 
else 
{
    worksheet.Cells[i   2, j   1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}

If DataGridViewCell.Value contains a numeric value (Decimal, Double, Integer, ...), Just remove .toString() and it should work as expected (Excel will set the appropriate type).

In both cases, if you want to apply a custom format for displaying in Excel, you will loose it during export, and you will have to explictly set it in Excel, using Range.NumberFormat Property. These Q/A can help to achieve this:

edited May 23 '17 at 11:48

Community

1
1

answered Jun 22 '13 at 13:24

Chris

8,527
10
34
51

Thanks but I already tried that and the problem persist.Still the same. – FeliceM Jun 22 '13 at 13:35
@FeliceM Are value from Datagridview in numeric type? (Decimal, double, int, ...). If not you have to cast it from string to numeric type. Ctype(cell.value, Decimal) for example. Note that you will loose the format, and will have to reaply it in excel. – Chris Jun 22 '13 at 13:43
actually are 3 strings. I can convert two of them to int but then become messing because the first one is a string time. – FeliceM Jun 22 '13 at 14:07
@FeliceM If I understand correctly, you have 3 'strings' columns with the first one displays a 'date', and two others display 'int'. In yes just make the cast to Integer for the appropriate column, and maintain '.ToString' for other (if you want to export it as string). It makes sense? – Chris Jun 22 '13 at 14:24
Yes, I have 3 columns, a var HH:mm:ss, a string "temperature and a string "umidita". If I take out .ToString() as suggested by you, I assume the value format to excel stay as per original one but still does not work in excel. If I understand what you are suggesting, I should load the gridview with var, int, int and take out also the ToString when exporting. Is this what you suggest? I have updated my code above to show how I transfer the data to the datagridview. – FeliceM Jun 22 '13 at 14:29
thanks a lot for your support. I have tried your suggestions and a lot more on the code side without success. Right now the solution I am using is on the excel side "text to column" this works but obviously is not the desired solution. I have not accepted your answer because does not give me the solution but for sure you deserved a +. Thanks – FeliceM Jun 23 '13 at 06:59
@FeliceM You are welcome. And I am really curious about what you tried and what exactly does not work. – Chris Jun 23 '13 at 08:02
I tried to convert the strings during exporting to int, decimal, one by one, all together and so on. But did not work. So as I said above at the end I worked out the solution in excel using the command "text to column" which can be done one column at time and the values become usable. The time column does not need to be re-worked, I did this only on temperature and humidity values. Select the column and click on data>>text to column, do not select any delimiter, >>finish, and the numbers become values.It works. – FeliceM Jun 23 '13 at 09:52
@FeliceM From http://en.wikipedia.org/wiki/Decimal_mark I guess the *comma* is also the decimal mark for you. So I agree; at home, the Convert.ToDecimal method as I have suggest will failed if I pass a string value like `"10.00"` and I need to convert before the stringvalue like this `StrValue.Replace(".", System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.NumberDecimalSeparator);` – Chris Jun 23 '13 at 10:18

Gridview export to excel - Formatting problems

1 Answers1

Linked