Reading Excel files from C

Question

Is there a free or open source library to read Excel files   xls  directly from a C  program    It does not need to be too fancy  just to select a worksheet and read the data as strings  So far  I ve been using Export to Unicode text function of Excel  and parsing the resulting  tab-delimited  file  but I d like to eliminate the manual step

User · Answer

Excel Data Reader is the way to go   It  s Open Source  at http   exceldatareader codeplex com  and actively developed   We been using it for reading Tabular  and sometimes not so tabular  worksheets for a couple of years now  In a financial application    Works like a charm to  read unit test data from human-readable sheets   Just avoid the feature of trying to return DateTime s  as  for Excel  DateTime s are just double numbers

User · Answer

ExcelMapper is an open source tool  http   code google com p excelmapper   that can be used to read Excel worksheets as Strongly Typed Objects  It supports both xls and xlsx formats

User · Answer

Not free  but with the latest Office there s a very nice automation  Net API   there has been an API for a long while but was nasty COM  You can do everything you want   need in code all while the Office app remains a hidden background process

User · Answer

If it s just tabular data  I would recommend file data helpers by Marcos Melli which can be downloaded here

User · Answer

While you did specifically ask for  xls  implying the older file formats  for the OpenXML formats  e g  xlsx  I highly recommend the OpenXML SDK  http   msdn microsoft com en-us library bb448854 aspx

User · Answer

Here s some code I wrote in C  using  NET 1 1 a few years ago  Not sure if this would be exactly what you need  and may not be my best code       using System  using System Data  using System Data OleDb   namespace ExportExcelToAccess            lt summary gt          Summary description for ExcelHelper           lt  summary gt      public sealed class ExcelHelper               private const string CONNECTION STRING    Provider Microsoft Jet OLEDB 4 0 Data Source  lt FILENAME gt  Extended Properties   Excel 8 0 HDR Yes                public static DataTable GetDataTableFromExcelFile string fullFileName  ref string sheetName                        OleDbConnection objConnection   new OleDbConnection                objConnection   new OleDbConnection CONNECTION STRING Replace   lt FILENAME gt    fullFileName                DataSet dsImport   new DataSet                 try                               objConnection Open                     DataTable dtSchema   objConnection GetOleDbSchemaTable OleDbSchemaGuid Tables  null                    if   null    dtSchema       dtSchema Rows Count  lt   0                                             raise exception if needed                                    if   null    sheetName   amp  amp   0    sheetName Length                                         if   CheckIfSheetNameExists sheetName  dtSchema                                                    raise exception if needed                                                         else                                         Reading the first sheet name from the Excel file                      sheetName   dtSchema Rows 0   TABLE NAME   ToString                                       new OleDbDataAdapter  SELECT   FROM      sheetName        objConnection   Fill dsImport                             catch  Exception                                  raise exception if needed                           finally                                  Clean up                  if objConnection    null                                        objConnection Close                        objConnection Dispose                                                  return dsImport Tables 0                region Commented code for importing data from CSV file                              string strConnectionString    Provider Microsoft Jet OLEDB 4 0     Data Source     System IO Path GetDirectoryName fullFileName         Extended Properties   Text HDR YES FMT Delimited                                                System Data OleDb OleDbConnection conText   new System Data OleDb OleDbConnection strConnectionString                               new System Data OleDb OleDbDataAdapter  SELECT   FROM     System IO Path GetFileName fullFileName  Replace            conText  Fill dsImport                               return dsImport Tables 0                 endregion                         lt summary gt              This method checks if the user entered sheetName exists in the Schema Table              lt  summary gt               lt param name  sheetName  gt Sheet name to be verified lt  param gt               lt param name  dtSchema  gt schema table  lt  param gt          private static bool CheckIfSheetNameExists string sheetName  DataTable dtSchema                        foreach DataRow dataRow in dtSchema Rows                                if  sheetName    dataRow  TABLE NAME   ToString                                           return true                                                 return false

User · Answer

The solution that we used  needed to    Allow Reading Writing of Excel produced files Be Fast in performance  not like using COMs  Be MS Office Independent  needed to be usable without clients having MS Office installed  Be Free or Open Source  but actively developed    There are several choices  but we found NPoi   NET port of Java s long existing Poi open source project  to be the best  http   npoi codeplex com   It also allows working with  doc and  ppt file formats

User · Answer

I want to show a simple method to read xls xlsx file with  NET  I hope that the following will be helpful for you     private DataTable ReadExcelToTable string path                 Connection String       string connstring    Provider Microsoft ACE OLEDB 12 0 Data Source     path     Extended Properties  Excel 8 0 HDR NO IMEX 1              the same name         string connstring   Provider Microsoft JET OLEDB 4 0 Data Source     path       Extended Properties  Excel 8 0 HDR NO IMEX 1            using OleDbConnection conn   new OleDbConnection connstring                  conn Open              Get All Sheets Name         DataTable sheetsName   conn GetOleDbSchemaTable OleDbSchemaGuid Tables new object   null null null  Table                  Get the First Sheet Name         string firstSheetName   sheetsName Rows 0  2  ToString                Query String          string sql   string Format  SELECT   FROM   0    firstSheetName            OleDbDataAdapter ada  new OleDbDataAdapter sql connstring           DataSet set   new DataSet            ada Fill set           return set Tables 0               Code is from article  http   www c-sharpcorner com uploadfile d2dcfc read-excel-file-with-net   You can get more details from it

User · Answer

This is what I used for Excel 2003   Dictionary lt string  string gt  props   new Dictionary lt string  string gt     props  Provider      Microsoft Jet OLEDB 4 0   props  Data Source     repFile  props  Extended Properties      Excel 8 0    StringBuilder sb   new StringBuilder    foreach  KeyValuePair lt string  string gt  prop in props        sb Append prop Key       sb Append           sb Append prop Value       sb Append         string properties   sb ToString     using  OleDbConnection conn   new OleDbConnection properties         conn Open        DataSet ds   new DataSet        string columns   String Join      columnNames ToArray         using  OleDbDataAdapter da   new OleDbDataAdapter           SELECT     columns     FROM      worksheet         conn                 DataTable dt   new DataTable tableName           da Fill dt           ds Tables Add dt

User · Answer

you could write an excel spreadsheet that loads a given excel spreadsheet and saves it as csv  rather than doing it manually    then you could automate that from c    and once its in csv  the c  program can grok that    also  if someone asks you to program in excel  it s best to pretend you don t know how    edit  ah yes  rob and ryan are both right

User · Answer

Just did a quick demo project that required managing some excel files  The  NET component  from GemBox software was adequate for my needs  It has a free version with a few limitations   http   www gemboxsoftware com GBSpreadsheet htm

User · Answer

The ADO NET approach is quick and easy  but it has a few quirks which you should be aware of  especially regarding how DataTypes are handled   This excellent article will help you avoid some common pitfalls  http   blog lab49 com archives 196

User · Answer

How about Excel Data Reader    http   exceldatareader codeplex com   I ve used in it anger  in a production environment  to pull large amounts of data from a variety of Excel files into SQL Server Compact  It works very well and it s rather robust

User · Answer

I did a lot of reading from Excel files in C  a while ago  and we used two approaches    The COM API  where you access Excel s objects directly and manipulate them through methods and properties The ODBC driver that allows to use Excel like a database    The latter approach was much faster  reading a big table with 20 columns and 200 lines would take 30 seconds via COM  and half a second via ODBC  So I would recommend the database approach if all you need is the data   Cheers   Carl

User · Answer

var fileName   string Format   0   fileNameHere   Directory GetCurrentDirectory     var connectionString   string Format  Provider Microsoft Jet OLEDB 4 0  data source  0   Extended Properties Excel 8 0    fileName    var adapter   new OleDbDataAdapter  SELECT   FROM  workSheetNameHere     connectionString   var ds   new DataSet     adapter Fill ds   anyNameHere     DataTable data   ds Tables  anyNameHere      This is what I usually use   It is a little different because I usually stick a AsEnumerable   at the edit of the tables    var data   ds Tables  anyNameHere   AsEnumerable      as this lets me use LINQ to search and build structs from the fields   var query   data Where x   gt  x Field lt string gt   phoneNumber      string Empty  Select x   gt                  new MyContact                                               firstName  x Field lt string gt   First Name                            lastName   x Field lt string gt   Last Name                            phoneNumber  x Field lt string gt   Phone Number

User · Answer

Late to the party  but I m a fan of LinqToExcel

User · Answer

If you have multiple tables in the same worksheet you can give each table an object name and read the table using the OleDb method as shown here  http   vbktech wordpress com 2011 05 10 c-net-reading-and-writing-to-multiple-tables-in-the-same-microsoft-excel-worksheet

User · Answer

We use ClosedXML in rather large systems    Free Easy to install Straight forward coding Very responsive support Developer team is extremly open to new suggestions  Often new features and bug fixes are implemented within the same week

User · Answer

Excel Package is an open-source  GPL  component for reading writing Excel 2007 files  I used it on a small project  and the API is straightforward  Works with XLSX only  Excel 200 amp    not with XLS   The source code also seems well-organized and easy to get around  if you need to expand functionality or fix minor issues as I did    At first  I tried the ADO Net  Excel connection string  approach  but it was fraught with nasty hacks -- for instance if second row contains a number  it will return ints for all fields in the column below and quietly drop any data that doesn t fit

User · Answer

I know that people have been making an Excel  extension  for this purpose  You more or less make a button in Excel that says  Export to Program X   and then export and send off the data in a format the program can read   http   msdn microsoft com en-us library ms186213 aspx should be a good place to start   Good luck

User · Answer

SpreadsheetGear for  NET is an Excel compatible spreadsheet component for  NET  You can see what our customers say about performance on the right hand side of our product page  You can try it yourself with the free  fully-functional evaluation

User · Answer

Forgive me if I am off-base here  but isn t this what the Office PIA s are for

User · Answer

SpreadsheetGear is awesome  Yes it s an expense  but compared to twiddling with these other solutions  it s worth the cost  It is fast  reliable  very comprehensive  and I have to say after using this product in my fulltime software job for over a year and a half  their customer support is fantastic

User · Answer

You can try using this open source solution that makes dealing with Excel a lot more cleaner   http   excelwrapperdotnet codeplex com

User · Answer

I just used ExcelLibrary to load an  xls spreadsheet into a DataSet  Worked great for me

User · Answer

Lately  partly to get better at LINQ     I ve been using Excel s automation API to save the file as XML Spreadsheet and then get process that file using LINQ to XML

User · Answer

The  NET component Excel Reader  NET may satisfy your requirement  It s good enought for reading XLSX and XLS files  So try it from      http   www devtriogroup com ExcelReader

User · Answer

I recommend the FileHelpers Library which is a free and easy to use  NET library to import export data from EXCEL  fixed length or delimited records in files  strings or streams   More   The Excel Data Link Documentation Section http   filehelpers sourceforge net example exceldatalink html

User · Answer

SmartXLS is another excel spreadsheet component which support most features of excel Charts formulas engines  and can read write the excel2007 openxml format

User · Answer

If it is just simple data contained in the Excel file you can read the data via ADO NET  See the connection strings listed here   http   www connectionstrings com  carrier excel2007 or  http   www connectionstrings com  carrier excel  -Ryan  Update  then you can just read the worksheet via something like select   from  Sheet1

User · Answer

Koogra is an open-source component written in C  that reads and writes Excel files

User · Answer

Take io Spreadsheet will do this work for you  and at no charge  Just take a look at this

[c#] Reading Excel files from C#

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to ms-office