export datagridview to excel

Question

I have a datagridview which I want to export that to excel. It works perfectly but it has a problem in excel. I have some columns which named like 7/8 7/9 .... in sql it shows them like [7/8] [7/9] .... but when I export that to excel those columns names will show with the month name like : 8-jul 9-jul but I don't want them to show like that.

I want to show exactly like columns names in sql.

What should I do?

Any help will be appreciated

Nikola Davidovic Nikola Davidovic · Accepted Answer · 2012-11-18T19:24:42

If you are using Microsoft.Office.Interop.Excel, you can set numberformat property of the Cell to @ (which is for text) and it will hold the appropriate value, try this:

oSheet.Cells[rowCount, columnCount].numberformat = "@";
oSheet.Cells[rowCount, columnCount] = "7/9";

EDIT According to comment, you should use it like this:

for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) 
{ 
       for (int j = 0; j < dataGridView1.Columns.Count; j++) 
       {
             if(j+1 == 5 && i!=1) //value of the cell where you put values like 7/8 and it's not the column name cell
                   worksheet.Cells[i + 2, j + 1].numberformat = "@";
             worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); 
       } 
}

EDIT2 Since it is a column name you can use this:

for (int j = 0; j < dataGridView1.Columns.Count; j++) 
{
    worksheet.Cells[1, j + 1].numberformat = "@"; 
    worksheet.Cells[1, j + 1].dataGridView1.Columns[i].Name
}

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(); 
       } 
}

export datagridview to excel

4 Answers