How to read an excel file in C without using Microsoft Office Interop Excel libraries

Question

I have a  Net-Windows application in C   I need to open an excel and process it  How can I do this without using Microsoft Office Interop Excel libraries

User · Answer

I have used Excel dll library which is    open source lightweight fast compatible with xls and xlsx   The documentation available over here  https   exceldatareader codeplex com   Strongly recommendable

User · Answer

I recently found this library that converts an Excel workbook file into a DataSet  Excel Data Reader

User · Answer

var fileName     C  ExcelFile xlsx   var connectionString    Provider Microsoft ACE OLEDB 12 0 Data Source     fileName     Extended Properties   Excel 12 0 IMEX 1 HDR NO TypeGuessRows 0 ImportMixedTypes Text       using  var conn   new OleDbConnection connectionString         conn Open         var sheets   conn GetOleDbSchemaTable System Data OleDb OleDbSchemaGuid Tables  new object     null  null  null   TABLE          using  var cmd   conn CreateCommand                  cmd CommandText    SELECT   FROM      sheets Rows 0   TABLE NAME   ToString                    var adapter   new OleDbDataAdapter cmd           var ds   new DataSet            adapter Fill ds

User · Answer

I highly recommend CSharpJExcel for reading Excel 97-2003 files  xls  and ExcelPackage for reading Excel 2007 2010 files  Office Open XML format  xlsx    They both work perfectly  They have absolutely no dependency on anything   Sample using CSharpJExcel   Workbook workbook   Workbook getWorkbook new System IO FileInfo fileName    var sheet   workbook getSheet 0       var content   sheet getCell colIndex  rowIndex  getContents        workbook close      Sample using ExcelPackage   using  ExcelPackage xlPackage   new ExcelPackage existingFile          get the first worksheet in the workbook   ExcelWorksheet worksheet   xlPackage Workbook Worksheets 1     int iCol   2      the column to read       output the data in column 2   for  int iRow   1  iRow  lt  6  iRow        Console WriteLine  Cell  0   1   Value  2    iRow  iCol         worksheet Cell iRow  iCol  Value         output the formula in row 6   Console WriteLine  Cell  0   1   Formula  2    6  iCol       worksheet Cell 6  iCol  Formula         the using statement calls Dispose   which closes the package    EDIT     There is another project  ExcelDataReader  that seems to have the ability to handle both formats  It is also easy like the other ones I ve mentioned   There are also other libraries    NPOI  Port of the Apache POI library to  NET  Very powerfull  free  and open source  In addition to Excel  97-2010  it also supports Word and PowerPoint files  ExcelLibrary  It only support Excel 97-2003  xls  files  EPPlus  An extension to ExcelPackage  Easier to use  I guess

User · Answer

I would urge against using OleDB  especially if its going to be run on a server  Its likely to cost you more in the long run - eg we had a SSIS job calling a Stored Procedure with the OleDB reading an excel file in the sptroc and kept crashing the SQL box  I took the OleDB stuff out of the sproc and it stopped crashing the server   A better method I ve found is to do it with Office 2003 and the XML files - in respect of Considerations for server-side Automation of Office  Note  Office 2003 is a minimum requirement for this to fly   Ref for reading from Excel  http   www roelvanlisdonk nl  p 924  please do more research to find other examples   Ref for writing a Excel spreadsheet  http   weblogs asp net jgaylord archive 2008 08 11 use-linq-to-xml-to-generate-excel-documents aspx  public void ReadExcelCellTest                         XDocument document   XDocument Load   C  BDATA Cars xml                XNamespace workbookNameSpace     urn schemas-microsoft-com office spreadsheet                   Get worksheet             var query   from w in document Elements workbookNameSpace    Workbook   Elements workbookNameSpace    Worksheet                           where w Attribute workbookNameSpace    Name   Value Equals  Settings                           select w              List lt XElement gt  foundWoksheets   query ToList lt XElement gt                 if  foundWoksheets Count    lt   0    throw new ApplicationException  Worksheet Settings could not be found                  XElement worksheet   query ToList lt XElement gt    0                   Get the row for  Seat              query   from d in worksheet Elements workbookNameSpace    Table   Elements workbookNameSpace    Row   Elements workbookNameSpace    Cell   Elements workbookNameSpace    Data                       where d Value Equals  Seat                       select d              List lt XElement gt  foundData   query ToList lt XElement gt                 if  foundData Count    lt   0    throw new ApplicationException  Row  Seat  could not be found                  XElement row   query ToList lt XElement gt    0  Parent Parent                  Get value cell of Etl SPIImportLocation ImportPath setting             XElement cell   row Elements   ToList lt XElement gt    1                   Get the value  Leon              string cellValue   cell Elements workbookNameSpace    Data   ToList lt XElement gt    0  Value               Console WriteLine cellValue

User · Answer

Look for GSpread NET  It s also an OpenSource project and it doesn t require Office installed  You can work with Google Spreadsheets by using API from Microsoft Excel  If you want to re-use the old code to get access to Google Spreadsheets  GSpread NET is the best way  You need to add a few row   Set objExcel   CreateObject  GSpreadCOM Application      Name             - User name  any you like    ClientIdAndSecret -  client id client secret  format    ScriptId         - Google Apps script ID app MailLogon Name  ClientIdAndSecret  ScriptId     Further code remain unchanged   http   scand com products gspread index html

User · Answer

If you need to open XLS files rather than XLSX files  http   npoi codeplex com  is a great choice   We ve used it to good effect on our projects

User · Answer

Ive just been searching for a solution and come across  Spreadsheetlight  which looks very promising  Its open source and available as a nuget package

User · Answer

You can try OleDB to read data from excel file  Please try as follow    DataSet ds Data   new DataSet    OleDbConnection oleCon   new OleDbConnection     string strExcelFile     C  Test xlsx   oleCon ConnectionString    Provider Microsoft ACE OLEDB 12 0 Data Source     strExcelFile     Extended Properties   Excel 12 0 IMEX 1 HDR NO TypeGuessRows 0 ImportMixedTypes Text        string SpreadSheetName        OleDbDataAdapter Adapter   new OleDbDataAdapter    OleDbConnection conn   new OleDbConnection sConnectionString    string strQuery  conn Open     int workSheetNumber   0   DataTable ExcelSheets   conn GetOleDbSchemaTable System Data OleDb OleDbSchemaGuid Tables  new object     null  null  null   TABLE       SpreadSheetName   ExcelSheets Rows workSheetNumber   TABLE NAME   ToString     strQuery    select   from      SpreadSheetName         OleDbCommand cmd   new OleDbCommand strQuery  conn   Adapter SelectCommand   cmd  DataSet dsExcel   new DataSet    Adapter Fill dsExcel   conn Close

User · Answer

You could also do what I do and by a commercial control like this one  http   www syncfusion com products reporting-edition xlsio  I have been struging for years before ending with a commercial solution  I first tried the OLEDB approach that is very easy to use in my development environment but can be a knightmare to deploy  Then I tried the open source solutions but most of the are outdated and have bad support   The xlsio controls from syncfusion are just the ones I use and are happy with but others exists  If you can affort it  do not hesitate  it s the best solution  Why  Because it has no dependencies with the system and supports all version of office right away  Among other advantages like  it s really fast   And no  I do not work for synfusion

User · Answer

If you don t wish to use interop  you may want to try out OfficeWriter  Depending on how much processing you really need to do on the file  it might be overkill though  You can request a free trial  There s a fully documented api available at the documentation site   DISCLAIMER  I m one of the engineers who built the latest version

[c#] How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to office-interop