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
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();
}
}
it's simple just use a space in [7/8] like [ 7/8]
Use MS ReportViewer to generate excel reports. Or use Aspose library. Or use Devexpress AspxGridView + AspxGridViewExporter
Please see this project: https://exportdata.codeplex.com/, it can export large datatable of more than 4,000 rows to excel through datagridview and also allows you to set the excel data property, such as format, style etc.besides. In it, you can set the excel data format to be the one you need.you can give it a try.
