[Solved] Decimal point changing to comma while importing Excel in ASP.Net MVC

sureshMGR
 
on Mar 23, 2021 02:07 AM
1659 Views

Hi friends, 

When i try to upload excel with decimal values it will reading values and showing in html table fine, its working fine in local source.

After moving inside server decimal values are changing as comma.

for ex: 10.61 is showing as 10,61.

How to fix this issue?

public ActionResult ReadExcel()
{
    HttpPostedFileBase filecont = Request.Files[0];
    DataTable dt = new DataTable();
    string conString = string.Empty;
    string filePath = string.Empty;
    string path = Server.MapPath("~/Excelfiles/");
    filePath = path + Path.GetFileName(filecont.FileName);
    string extension = Path.GetExtension(filecont.FileName);
    filecont.SaveAs(filePath);
    conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", filePath);          
    OleDbConnection conn = new OleDbConnection(conString);
    conn.Open();
    DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    foreach (DataRow dr in Sheets.Rows)
    {
        string sht = dr[2].ToString().Replace("'", "");
        OleDbCommand dataCommand = new OleDbCommand("select * from [" + sht + "]", conn);
        OleDbDataAdapter da = new OleDbDataAdapter(dataCommand);              
        da.Fill(dt);
    }
    conn.Close();
    if (System.IO.File.Exists(filePath))
        System.IO.File.Delete(filePath);          
    return Json(new
    {
        datatab = DataTableToJson(dt)
    },
       JsonRequestBehavior.AllowGet);
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 23, 2021 08:16 AM
on Jan 04, 2022 09:56 AM

Hi sureshMGR,

You need to loop through the DataTable rows and column value and Replace the comma with dot.

Check with below code.

public ActionResult ReadExcel()
{
    HttpPostedFileBase filecont = Request.Files[0];
    DataTable dt = new DataTable();
    string conString = string.Empty;
    string filePath = string.Empty;
    string path = Server.MapPath("~/Excelfiles/");
    filePath = path + Path.GetFileName(filecont.FileName);
    string extension = Path.GetExtension(filecont.FileName);
    filecont.SaveAs(filePath);
    conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", filePath);
    OleDbConnection conn = new OleDbConnection(conString);
    conn.Open();
    DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    foreach (DataRow dr in Sheets.Rows)
    {
        string sht = dr[2].ToString().Replace("'", "");
        OleDbCommand dataCommand = new OleDbCommand("select * from [" + sht + "]", conn);
        OleDbDataAdapter da = new OleDbDataAdapter(dataCommand);
        da.Fill(dt);
    }
    conn.Close();

    foreach (DataRow row in dt.Rows)
    {
        foreach (DataColumn column in dt.Columns)
        {
            decimal value = 0;
            if (decimal.TryParse(row[column.ColumnName].ToString(), out value))
            {
                row[column.ColumnName] = row[column.ColumnName].ToString().Replace(",", ".");
            }
        }
    }

    if (System.IO.File.Exists(filePath))
        System.IO.File.Delete(filePath);
    return Json(new
    {
        datatab = DataTableToJson(dt)
    },
        JsonRequestBehavior.AllowGet);
}