read excel correctly in C #

2

I have a problem that I have not been able to solve, it turns out that I am uploading an excel from a mvc application I capture it and I read it to a datatable until everything is perfect but it turns out that the excel is in Percentage format (%) and the cells are in the same format when I capture it with the datatable I take the value in percentage and I read it as string example in the excel cell appears 12%, the value of the cell when one sees it is "12.46997%" and when I capture in c # read me only 12% not the rest of the decimals it brings.

Some example photos.

The value of excel has decimals.

here how I capture my code c #

I do not know how to receive it, I do not know if there is any way when I read the excel from the code convert to numeric format.

here's my code:

        [HttpPost]
    public ActionResult ProrrataRetiroCarga(HttpPostedFileBase fileProrrataRetiro)

    {
        string filename = Guid.NewGuid() + Path.GetExtension(fileProrrataRetiro.FileName);
        string filepath = "/excelFolder/" + filename;
        fileProrrataRetiro.SaveAs(Path.Combine(Server.MapPath("/excelFolder/"), filename));
        InsertExceldata(filepath, filename);
        ViewData["Success"] = "Archivo Cargado Exitosamente.";
        return View("~/Views/ProrrataRetiro/ProrrataRetiro.aspx");
    }
    private void ExcelConn(string filepath)
    {
        string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Macro; HDR=YES;""", filepath);
        Econ = new OleDbConnection(constr);
    }
    private void InsertExceldata(string fileepath, string filename)
    {
        string fullpath = Server.MapPath("/excelFolder/") + filename;
        ExcelConn(fullpath);
        string query = string.Format("Select * from[PRORRATA_RETIROS$]");

        OleDbCommand Ecom = new OleDbCommand(query, Econ);
        Econ.Open();
        DataSet ds = new DataSet();
        OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
        Econ.Close();
        oda.Fill(ds);
        ds.Tables[0].AcceptChanges();
        DataTable dt = ds.Tables[0];
        dt.Rows[0].Delete();
        dt.Rows[1].Delete();
        dt.Rows[2].Delete();
        dt.Rows[3].Delete();
        dt.Rows[4].Delete();
        dt.Rows[5].Delete();
        dt.Rows[6].Delete();
        dt.AcceptChanges();

        if (dt.Rows.Count > 0)
        {
            DataTable dtpaso = new DataTable();
            DataColumn dcCol = new DataColumn();
            dcCol.ColumnName = "nomEmpresa";
            dtpaso.Columns.Add(dcCol);
            dcCol = new DataColumn();
            dcCol.ColumnName = "Hora";
            dtpaso.Columns.Add(dcCol);
            dcCol = new DataColumn();
            dcCol.ColumnName = "Porc";
            dtpaso.Columns.Add(dcCol);



            for (int fila = 0; fila < dt.Rows.Count; fila++)
            {
                if (dt.Rows[fila][0].ToString() != "")
                {
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        if (dt.Rows[fila][col].ToString().Trim() != "")
                        {
                            DataRow drPaso = dtpaso.NewRow();
                            drPaso["nomEmpresa"] = dt.Rows[0][col + 1].ToString();
                            drPaso["Hora"] = dt.Rows[fila + 1][0];
                            drPaso["Porc"] = (((dt.Rows[fila + 1][col + 1]).ToString()) != "#REF!") ? dt.Rows[fila + 1][col+1].ToString() : "0";
                         //   var numero = dt.Rows[fila + 1][col+1];                             
                            dtpaso.Rows.Add(drPaso);
                            dtpaso.AcceptChanges();
                        }
                        else
                        {
                            col = 0;
                            break;
                        }
                    }
                }
                else
                    break;
            }
        }
    
asked by Joel Baez 05.07.2018 в 18:39
source

1 answer

0

Joel, the problem is that you are taking the data as a string, you must transform to decimal. Change the line:

drPaso["Porc"] = (((dt.Rows[fila + 1][col + 1]).ToString()) != "#REF!") ? 
dt.Rows[fila + 1][col+1].ToString() : "0";

For this:

If(dt.Rows[fila + 1][col + 1].ToString() != "#REF!")
{
  string dato = Rows[fila + 1][col + 1].ToString().Replace("%","");
  drPaso["Porc"] = decimal.Parse(dato).ToString();
}
Else
  drPaso["Porc"] = "0";
    
answered by 07.07.2018 в 11:49