Formatting a spreadsheet using c# oledbc

Question

I have a spreadsheet and I want to upload it in a ASP.NET-MVC tool using C# to extract the data then put them on an SQL server database.

I created a function who put the data into a DataSet so I can use it after to put the data into the database.

Here is the function :

public DataSet getData(HttpPostedFileBase file, string path)
{
    var fileName = Path.GetFileName(file.FileName);
    oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"");
    DataSet ds = new DataSet();
    OleDbCommand cmd = new OleDbCommand();
    OleDbDataAdapter oleda = new OleDbDataAdapter();

    oledbConn.Open();

    cmd.Connection = oledbConn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * FROM [Worksheet0$]";

    oleda = new OleDbDataAdapter(cmd);
    oleda.Fill(ds);
    oledbConn.Close();

    return ds;
}

Everything is working but when I do a foreach on the dataset and retrieve the data there is a formatting problem.

The values in my spreadsheet are formatted as Numbers, so for example 1.25 turns into 1.3. The cell is showing 1.3 but when I click on it the value is 1.25.

When I check on my dataset the values in it are the one formatted (not the real values), I have for example the 1.3 instead the 1.25.

When I change the columns format before uploading, everything works all right ! But I am looking for an automatic process to do that.

Maybe because of the format of the column that value belongs? — Soner Gönül
– Soner Gönül, Commented Aug 21, 2015 at 8:11
@SonerGönül It works when I change the column format before uploading, but I have a lot of spreadsheets and I have to do that automatically. any Idea ? — ben
– ben, Commented Aug 21, 2015 at 8:16
@aBennouna it can be done through regional setting in control panel,you need to change the number format (control panel->region and language ->additional settings->numbers->no. of decimals) I have no idea how to do it through the code though. — Kryptonian
– Kryptonian, Commented Aug 21, 2015 at 8:38

Nadeem Khoury · Accepted Answer · 2015-08-21 09:08:19Z

1

I think you should try to change this library. I recommend you to use ExcelDataReader 2.1.2.3 and here is the NuGet for it: https://www.nuget.org/packages/ExcelDataReader/ I used this library, it is very fast, and lightweight library. and here is my code:

public List<Checklist> Provide()
{
  List<Checklist> checklists = new List<Checklist>();
  using (var reader = ExcelReaderFactory.CreateOpenXmlReader(m_Stream))
   {
                while (reader.Read())
                {
                    Checklist checklist = new Checklist();

                     checklist.Description = reader.GetString(1);
                     checklist.View = reader.GetString(2);
                     checklist.Organ = reader.GetString(3);
                     checklists.Add(checklist);
                 }
                return checklists; 
    }
}

answered Aug 21, 2015 at 9:08

Nadeem Khoury

9378 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ben Over a year ago

Thank you, do you have any idea of putting the result on a dataset, I tried to use this solution but the result returns null exceldatareader.codeplex.com

Nadeem Khoury Over a year ago

yes, you can use DataSet result = reader.AsDataSet(); (DataSet - The result of each spreadsheet will be created in the result.Tables)

ben · Accepted Answer · 2015-08-21 09:54:11Z

0

Thanks to @Nadeem Khouri

I used the ExcelDataReaderLibrary

Here is the working code :

public DataSet getData(string path)
{
    FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
    DataSet result = excelReader.AsDataSet();
    return result;
}

answered Aug 21, 2015 at 9:54

ben

4281 gold badge6 silver badges15 bronze badges

Collectives™ on Stack Overflow

Formatting a spreadsheet using c# oledbc

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related