I'm using the following code to display an excel file in a data grid, the problem is, large numbers are being displayed as exponential form i.e. 1236548965132160 is displayed as 1.23654896513216E+15 in the grid,
string xlsxString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
OleDbConnection excelOleDBConnection = new OleDbConnection(xlsxString);
excelOleDBConnection.Open();
OleDbDataAdapter dataAdapterForExcelFile = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelOleDBConnection);
DataTable dataTableForTheAdapter = new DataTable();
dataAdapterForExcelFile.Fill(dataTableForTheAdapter);
this.dataGridView1.DataSource = dataTableForTheAdapter.DefaultView;
I need to remove this exponential form from the code, and not the excel, please suggest.
Two options:
DataTable dataTableForTheAdapter = new DataTable();
dataAdapterForExcelFile.FillSchema(dataTableForTheAdapter, SchemaType.Source);
for(int i = 0; i < dataTableForTheAdapter.Columns.Count; i++)
if (dataTableForTheAdapter.Columns[i].DataType == typeof(double))
dataTableForTheAdapter.Columns[i].DataType = typeof(decimal);
dataAdapterForExcelFile.Fill(dataTableForTheAdapter);
or:
this.dataGridView1.DataSource = dataTableForTheAdapter.DefaultView;
for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
if (this.dataGridView1.Columns[i].ValueType == typeof(double))
this.dataGridView1.Columns[i].DefaultCellStyle.Format = "N";
}