Conversion of Scientific Notation in uploaded Excel to Original Number in Dataset
In Asp.net when Uploading an Excel Sheet when we enter more than 15 digits in the Cell and that column is a Number format or datatype in Excel that Number turns into Scientific Notation or an Exponential Number so when we fetch that data and fill in the dataset that dataset contains same Scientific Notation.How to change that scientific Notation to original Number in our dataset that i will discuss in this Article with code snippets and real time images.
Scenario to have a Scientific Notation in Excel Sheet:
Suppose in my Excel sheet i have a column Number Format Datatype called Mobile Number.
But if you entered more than 15 digits in the cell that number converts in to Scientific Notation or Exponential Form.
say for an Example i have a Number like this 123456789125453000 but it turns in to 1.23456789125453E+017.
Because in Excel the Number Format Datatype only allow 15 Digits when the digits are exceeded 15 then it will change to Exponential form or Scientific Notation.To ignore the Scientific format :
Now our task is to show the Original Number while fetching the data in the dataset. So for that i have two solutionsMethod 1 :
One is looping the dataset (row by row/record by record)and updating each row.
With Method 1 looping the dataset(row by row/Record by Record) is not a good solution.
Because if the excel sheet contains 5 lakh records that will lead to a performance issue for an application.
Hence the solution is not amicable for all the scenarios.Method 2 :
2) without looping the dataset (only Loop Columns).
we know that the Method 1 will not yield a fruitful solution.For achieving this task we won't Compromise the Performance of an Application.why because for an example if the excel sheet contains 5 lakh records updating the Dataset(row by row/record by record) that will lead to a performance issue for an application.
Hence the solution is not amicable for all the scenarios(especially when the records in the Excel are huge).without looping the dataset(Loop Only Columns) :
without looping the dataset(row by row/record by record) we can able to change the Excel data which is scientific notation to Number format.
Open a Connection with Oledb.
DataSet dst =new DataSet();
if (!string.IsNullOrEmpty(strSheet))
{
strQuery = string.Format("SELECT * FROM [" + strSheet + "]");
}
else
{
strQuery = string.Format("SELECT * FROM [Sheet1$]");
}
try
{
if (strFilename.EndsWith("xls"))
{
//strConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & ";Extended Properties=Excel 8.0", Path.GetDirectoryName(strFilename))
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";Extended Properties=Excel 8.0;", Path.GetDirectoryName(strFilename));
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.EXCEL);
}
else if (strFilename.EndsWith("xlsx"))
{
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";Extended Properties=Excel 12.0;", Path.GetDirectoryName(strFilename));
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.EXCEL);
}
else if (strFilename.EndsWith("csv") | strFilename.EndsWith("txt"))
{
//strConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;", Path.GetDirectoryName(strFilename))
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Text;", Path.GetDirectoryName(strFilename));
//strQuery = String.Format("SELECT * FROM {0}", Path.GetFileName(strFilename))
strQuery = "SELECT * FROM [" + Path.GetFileName(strFilename) + "]";
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.NOTEPAD);
}
OleDbConnection objCon = null;
objCon = new OleDbConnection(strConnString);
objCon.Open();
OleDbCommand cmdselect = new OleDbCommand(strQuery, objCon);
OleDbDataAdapter datadapt = new OleDbDataAdapter(cmdselect);
datadapt.Fill(dst);
The above code is for opening the Oledb connection and fill the dataset.
But that dataset contains a Number with Scientific Notation.we need to change the scientific notation to Number.
see the below real time image of a dataset from the dataset visualizer.
The first row contains a Scientific Notation number.
When Uploading the excel sheet there are no fixed or predefined or predetermined columns(unknown of a specific column) in the Excel.
For that i will use below lines of Code
So with the above code i get the columns in my dataset so to formulate/Create a query dynamically i will loop the columns(i.e. Loop each and every Column)
Now loop the Columns of the dataset like the below code.
for (int i = 0; i < dst.Tables[0].Columns.Count; i++)
{
if (dst.Tables[0].Columns[i].ColumnName.ToUpper().Contains("MOBILE"))
{
loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],\"#####\") as " + dst.Tables[0].Columns[i].ColumnName + " ";
}
else
{
//Format([MobileNo], \"#####\")
loopCoulmnns = loopCoulmnns + "," + dst.Tables[0].Columns[i].ColumnName;
}
}
if (loopCoulmnns.StartsWith(","))
{
loopCoulmnns = loopCoulmnns.Remove(0, 1);
}
if (loopCoulmnns.EndsWith(","))
{
int length = loopCoulmnns.Length;
loopCoulmnns = loopCoulmnns.Remove(length - 1, 1);
Now the User may or may not Upload a Excel sheet with a column called Mobile Number
then we need to loop all the columns in the dataset and if the column contains a Mobile Number which is Number format in Excel sheet formating the column like this.See the below code.
loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],\"#####\") as " + dst.Tables[0].Columns[i].ColumnName + " ";
Instead of select * from [sheet1$] we need to do like this.When the below code is applicable if you know that the Excel sheet Definitely contains a Column Called a Mobile Number.
select FirstName,LastName,Email, Format([MobileNo], \"#####\")as Mobile no. from [sheet1$].
Now after doing this we need to again fill the dataset using oledbconnection and oledbadapter now our scientific notation will be ignored and it changes into a Number format.
see the Below out put how it will changes.
Now my dataset turns into Original (like Excel sheet)see the next output as shown below.
and the Numbers which are not in scientific number will remains same.And it will convert only the scientific numbers in to Number.
We can Use OledbSchema to get columns and datatypes for this see the below code..
objCon = new OleDbConnection(strConnString);
objCon.Open();
objCmd = new OleDbCommand(strQuery, objCon);
objDa = new OleDbDataAdapter();
objDa.SelectCommand = objCmd;
objDT = objCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); Full Code :
string strConnString = "";
string strQuery = "";
OleDbConnection objCon = null;
OleDbDataAdapter objDa = null;
OleDbCommand objCmd = null;
DataTable dtexcel =new DataTable();
DataSet newdatasetexcel = new DataSet();
string loopCoulmnns = "";
try
{
//if (!GetExcelConnString(strFileName, strSheet, ref strConnString, ref strQuery, strFilepath))
//{
// return false;
//}
DataSet dst = GetExcelConnString(strFileName, strSheet, ref strConnString, ref strQuery, strFilepath);
for (int i = 0; i < dst.Tables[0].Columns.Count; i++)
{
if (dst.Tables[0].Columns[i].ColumnName.ToUpper().Contains("MOBILE"))
{
loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],\"#####\") as " + dst.Tables[0].Columns[i].ColumnName + " ";
}
else
{
//Format([MobileNo], \"#####\")
loopCoulmnns = loopCoulmnns + "," + dst.Tables[0].Columns[i].ColumnName;
}
}
if (loopCoulmnns.StartsWith(","))
{
loopCoulmnns = loopCoulmnns.Remove(0, 1);
}
if (loopCoulmnns.EndsWith(","))
{
int length = loopCoulmnns.Length;
loopCoulmnns = loopCoulmnns.Remove(length - 1, 1);
}
strQuery = "select " + loopCoulmnns + " from " + "[" + strSheet + "]" + " ";
objCon = new OleDbConnection(strConnString);
objCon.Open();
objCmd = new OleDbCommand(strQuery, objCon);
objDa = new OleDbDataAdapter();
objDa.SelectCommand = objCmd;
dsObj = new DataSet();
dsObj.Locale.NumberFormat = CultureInfo.CurrentCulture.NumberFormat;
if (!string.IsNullOrEmpty(strSheet))
{
objDa.Fill(dsObj, strSheet);
}
else
{
objDa.Fill(dsObj);
}
//newdatasetexcel.Tables.Add(dtexcel);
//for (int i = 0; i < dsObj.Tables[0].Columns.Count; i++)
//{
// DataColumn dcExcel = new DataColumn();
// dcExcel.ColumnName = dsObj.Tables[0].Columns[i].ColumnName;
// dcExcel.DataType = typeof(string);
// newdatasetexcel.Tables[0].Columns.Add(dcExcel);
//}
// foreach (DataRow fSrcRow in dsObj.Tables[0].Rows)
// {
// newdatasetexcel.Tables[0].ImportRow(fSrcRow);
// }
//if (newdatasetexcel.Tables[0].Rows.Count > 0)
//{
// DataRow []dr = newdatasetexcel.Tables[0].Select("MobileNo Like '%E%'");
// if (dr.Length > 0)
// {
// for (int i = 0; i < dr.Length; i++)
// {
// dr[0].ItemArray[i] = "bhushan";
// dr[0].AcceptChanges();
// }
// }
//}
}
Nice Article..
This is very helpful to those who are try to do exporting excel data to sql database with more than 16 digits number format. If it's crosses 16 digits excel sheet convert that into scientific notification again we fetch that excel cell value it's coming like a Scientific notation only. But using the above we can easily format scientific notation format to number format.