CODEWITHSUNDEEP
c#
Syed Salman Raza Zaidi
Syed Salman Raza Zaidi

Reputation: 2192

Excel File Upload Changing Data Type

I have to upload excel files, I have excel files with a proper format, I have a column which contains large numbers,so in excel it is displaying the numbers with Exponent, For eg I have this value 8.99681E+18(8996812351321100000)

I am making DataTable by reading it on runtime,After making Datatable,this value got changed with (8996812351321100288). This is how I am doing

var conn = new OleDbConnection();
 if (fileExtension == ".xls")
      //4.0 Connection String
 if (fileExtension == ".xlsx")
                //12.0 Connection String

           conn.Open();
            DataTable dtSheets = conn.GetSchema("Tables");
            string firstSheet = dtSheets.Rows[0]["TABLE_NAME"].ToString();
            using (var comm = new OleDbCommand())
            {
                comm.CommandText = "select * from [" + firstSheet + "]";
                comm.Connection = conn;                
                using (var da = new OleDbDataAdapter())
                {
                    da.SelectCommand = comm;
                    da.Fill(dt);
                    if (dt.Columns.Contains("SIMNo"))
                    {
                        DataTable dtCloned = dt.Clone();
                        dtCloned.Columns["SIMNo"].DataType = typeof(Int64);
                        foreach (DataRow row in dt.Rows)
                        {
                            dtCloned.ImportRow(row);
                        }
                        return dtCloned;
                    }
                    return dt;
                }
            }

Is there a way that I can change the data type of SIMNo at runtime before creaating DataTable?and how can I do that?

Upvotes: 0

Views: 172

Answers (2)

HungDL
HungDL

Reputation: 513

I think, BigInt should be a help.

http://www.carljohansen.co.uk/bigint/.

Upvotes: 1

Tim Rogers
Tim Rogers

Reputation: 21713

Casting 8996812351321100000.0 to Int64 gives a precision rounding error.

(Int64)8996812351321100000.0 // == 8996812351321100288

Instead, use the decimal datatype as it is designed to represent decimal numbers without rounding issues. Or, as it's a number that will not have any arithmetic applied to it, just use a string.

Upvotes: 1

Related Questions