CODEWITHSUNDEEP
c#excelasp.net-mvcoledbconnection
ben
ben

Reputation: 428

Formatting a spreadsheet using c# oledbc

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.

Upvotes: 0

Views: 147

Answers (2)

ben
ben

Reputation: 428

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;
}

Upvotes: 0

Nadeem Khoury
Nadeem Khoury

Reputation: 937

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; 
    }
}

Upvotes: 1

Related Questions