How to read data of an Excel file using C

Question

How to read an Excel file using C    I open an Excel file for reading and copy it to clipboard to search email format  but I don t know how to do it   FileInfo finfo  Excel ApplicationClass ExcelObj   new Excel ApplicationClass    ExcelObj Visible   false   Excel Workbook theWorkbook  Excel Worksheet worksheet   if  listView1 Items Count  gt  0        foreach  ListViewItem s in listView1 Items                finfo   new FileInfo s Text           if  finfo Extension      xls     finfo Extension      xlsx     finfo Extension      xlt     finfo Extension      xlsm     finfo Extension      csv                         theWorkbook   ExcelObj Workbooks Open s Text  0  true  5          true  Excel XlPlatform xlWindows    t   false  false  0  true  false  false                for  int count   1  count  lt   theWorkbook Sheets Count  count                                  worksheet    Excel Worksheet theWorkbook Worksheets get Item count                   worksheet Activate                    worksheet Visible   false                  worksheet UsedRange Cells Select

User · Answer

OK   One of the more difficult concepts to grasp about Excel VSTO programming is that you don t refer to cells like an array  Worksheet 0  0  won t give you cell A1  it will error out on you   Even when you type into A1 when Excel is open  you are actually entering data into Range A1   Therefore you refer to cells as Named Ranges   Here s an example   Excel Worksheet sheet   workbook Sheets  Sheet1   as Excel Worksheet   Excel Range range   sheet get Range  A1   Missing Value    You can now literally type   range Text    this will give you the text the user sees range Value2    this will give you the actual value stored by Excel  without rounding    If you want to do something like this   Excel Range range   sheet get Range  A1 A5   Missing Value   if  range1    null       foreach  Excel Range r in range1                  string user   r Text          string value   r Value2           There might be a better way  but this has worked for me   The reason you need to use Value2 and not Value is because the Value property is a parametrized and C  doesn t support them yet   As for the cleanup code  i will post that when i get to work tomorrow  i don t have the code with me  but it s very boilerplate   You just close and release the objects in the reverse order you created them   You can t use a Using   block because the Excel Application or Excel Workbook doesn t implement IDisposable  and if you don t clean-up  you will be left with a hanging Excel objects in memory   Note    If you don t set the Visibility property Excel doesn t display  which can be disconcerting to your users  but if you want to just rip the data out  that is probably good enough You could OleDb  that will work too    I hope that gets you started  let me know if you need further clarification   I ll post a complete   here is a complete sample   using System  using System IO  using System Reflection  using NUnit Framework  using ExcelTools   Ms Office  using Excel   Microsoft Office Interop Excel   namespace Tests        TestFixture      public class ExcelSingle                Test          public void ProcessWorkbook                         string file     C  Users Chris Desktop TestSheet xls               Console WriteLine file                Excel Application excel   null              Excel Workbook wkb   null               try                               excel   new Excel Application                     wkb   ExcelTools OfficeUtil OpenBook excel  file                    Excel Worksheet sheet   wkb Sheets  Data   as Excel Worksheet                   Excel Range range   null                   if  sheet    null                      range   sheet get Range  A1   Missing Value                    string A1   String Empty                   if  range    null                       A1   range Text ToString                     Console WriteLine  A1 value   0    A1                              catch Exception ex                                  if you need to handle stuff                 Console WriteLine ex Message                             finally                               if  wkb    null                      ExcelTools OfficeUtil ReleaseRCM wkb                    if  excel    null                      ExcelTools OfficeUtil ReleaseRCM excel                                     I ll post the functions from ExcelTools tomorrow  I don t have that code with me either   Edit  As promised  here are the Functions from ExcelTools you might need   public static Excel Workbook OpenBook Excel Application excelInstance  string fileName  bool readOnly  bool editable          bool updateLinks            Excel Workbook book   excelInstance Workbooks Open              fileName  updateLinks  readOnly              Type Missing  Type Missing  Type Missing  Type Missing  Type Missing              Type Missing  editable  Type Missing  Type Missing  Type Missing              Type Missing  Type Missing           return book         public static void ReleaseRCM object o            try               System Runtime InteropServices Marshal ReleaseComObject o             catch             finally               o   null                    To be frank  this stuff is much easier if you use VB NET   It s in C  because I didn t write it   VB NET does option parameters well  C  does not  hence the Type Missing   Once you typed Type Missing twice in a row  you run screaming from the room   As for you question  you can try to following   http   msdn microsoft com en-us library microsoft office interop excel range find VS 80  aspx  I will post an example when I get back from my meeting    cheers  Edit   Here is an example  range   sheet Cells Find  Value to Find                                                    Type Missing                                                   Type Missing                                                   Type Missing                                                   Type Missing                                                   Excel XlSearchDirection xlNext                                                   Type Missing                                                   Type Missing  Type Missing    range Text    give you the value found   Here is another example inspired by this site    range   sheet Cells Find  Value to find   Type Missing  Type Missing Excel XlLookAt xlWhole Excel XlSearchOrder xlByColumns Excel XlSearchDirection xlNext false  false  Type Missing     It helps to understand the parameters   P S   I m one of those weird people who enjoys learning COM automation   All this code steamed from a tool I wrote for work which required me to process over 1000  spreadsheets from the lab each Monday

User · Answer

try                       DataTable sheet1   new DataTable  Excel Sheet                OleDbConnectionStringBuilder csbuilder   new OleDbConnectionStringBuilder                csbuilder Provider    Microsoft ACE OLEDB 12 0               csbuilder DataSource   fileLocation              csbuilder Add  Extended Properties    Excel 12 0 Xml HDR YES                string selectSql     SELECT   FROM  Sheet1                 using  OleDbConnection connection   new OleDbConnection csbuilder ConnectionString               using  OleDbDataAdapter adapter   new OleDbDataAdapter selectSql  connection                                 connection Open                    adapter Fill sheet1                                   catch  Exception e                        Console WriteLine e Message               This worked for me  Please try it and let me know for queries

User · Answer

You can use Microsoft Office Interop Excel assembly to process excel files     Right click on your project and go to Add reference  Add the Microsoft Office Interop Excel assembly      Include using Microsoft Office Interop Excel  to make use of assembly    Here is the sample code            using Microsoft Office Interop Excel         create the Application object we can use in the member functions      Microsoft Office Interop Excel Application  excelApp   new Microsoft Office Interop Excel Application         excelApp Visible   true       string fileName    C   sampleExcelFile xlsx          open the workbook     Workbook workbook    excelApp Workbooks Open fileName          Type Missing  Type Missing  Type Missing  Type Missing          Type Missing  Type Missing  Type Missing  Type Missing          Type Missing  Type Missing  Type Missing  Type Missing          Type Missing  Type Missing          select the first sheet             Worksheet worksheet    Worksheet workbook Worksheets 1          find the used range in worksheet     Range excelRange   worksheet UsedRange         get an object array of all of the cells in the worksheet  their values      object    valueArray    object    excelRange get Value                  XlRangeValueDataType xlRangeValueDefault          access the cells     for  int row   1   row  lt   worksheet UsedRange Rows Count    row                for  int col   1  col  lt   worksheet UsedRange Columns Count    col                          access each cell             Debug Print valueArray row  col  ToString                            clean up stuffs     workbook Close false  Type Missing  Type Missing       Marshal ReleaseComObject workbook         excelApp Quit        Marshal FinalReleaseComObject  excelApp

User · Answer

Here s a 2020 answer - if you don t need to support the older  xls format  so pre 2003  you could use either    LightweightExcelReader to access specfic cells  or cursor through all the data in a spreadsheet    or   ExcelToEnumerable if you want to map spreadsheet data to a list of objects    Pros     Performance - at the time of writing  the the fastest way to read an  xlsx file  https   github com ChrisHodges ExcelToEnumerable wiki Performance   Simplicity - less verbose than OLE DB or OpenXml   Cons    Neither LightweightExcelReader nor ExcelToEnumerable support  xls files    Disclaimer  I am the author of LightweightExcelReader and ExcelToEnumerable

User · Answer

I d recommend you to use Bytescout Spreadsheet   https   bytescout com products developer spreadsheetsdk bytescoutspreadsheetsdk html  I tried it with Monodevelop in Unity3D and it is pretty straight forward  Check this sample code to see how the library works   https   bytescout com products developer spreadsheetsdk read-write-excel html

User · Answer

Why don t you create OleDbConnection  There are a lot of available resources in the Internet  Here is an example  OleDbConnection con   new OleDbConnection  Provider Microsoft Jet OLEDB 4 0 Data Source   filename   Extended Properties Excel 8 0    con Open    try          Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference      DataSet myDataSet   new DataSet         OleDbDataAdapter myCommand   new OleDbDataAdapter   SELECT   FROM    listname        con        myCommand Fill myDataSet        con Close         richTextBox1 AppendText   nDataSet Filled            Travers through each row in the dataset      foreach  DataRow myDataRow in myDataSet Tables 0  Rows                     Stores info in Datarow into an array           Object   cells   myDataRow ItemArray              Traverse through each array and put into object cellContent as type Object             Using Object as for some reason the Dataset reads some blank value which             causes a hissy fit when trying to read  By using object I can convert to             String at a later point            foreach  object cellContent in cells                               Convert object cellContect into String to read whilst replacing Line Breaks with a defined character                string cellText   cellContent ToString                   cellText   cellText Replace   n                          Read the string and put into Array of characters chars                richTextBox1 AppendText   n  cellText                             Thread Sleep 15000     catch  Exception ex         MessageBox Show ex ToString            Thread Sleep 15000     finally        con Close

User · Answer

The recommended way to read Excel files on server side app is Open XML    Sharing few links -  https   msdn microsoft com en-us library office hh298534 aspx  https   msdn microsoft com en-us library office ff478410 aspx  https   msdn microsoft com en-us library office cc823095 aspx

User · Answer

Use OLEDB Connection to communicate with excel files  it gives better result  using System Data OleDb                     string physicalPath    Your Excel file physical path                   OleDbCommand cmd   new OleDbCommand                    OleDbDataAdapter da   new OleDbDataAdapter                    DataSet ds   new DataSet                    String strNewPath   physicalPath                  String connString    Provider Microsoft ACE OLEDB 12 0 Data Source     strNewPath     Extended Properties   Excel 12 0 HDR Yes IMEX 2                     String query    SELECT   FROM  Sheet1        You can use any different queries to get the data from the excel sheet                 OleDbConnection conn   new OleDbConnection connString                   if  conn State    ConnectionState Closed  conn Open                    try                                       cmd   new OleDbCommand query  conn                       da   new OleDbDataAdapter cmd                       da Fill ds                                      catch                                          Exception Msg                                     finally                                       da Dispose                        conn Close                        The Output data will be stored in dataset  using the dataset object you can easily access the datas  Hope this may helpful

User · Answer

Excel File Reader  amp  Writer Without Excel On u r System    Download and add the dll for  NPOI u r project  Using this code to read a excel file           using  FileStream file   new FileStream filePath  FileMode Open  FileAccess Read  FileShare ReadWrite                        XSSFWorkbook XSSFWorkbook   new XSSFWorkbook file                     ISheet objxlWorkSheet   XSSFWorkbook GetSheetAt 0           int intRowCount   1          int intColumnCount   0          for                              IRow Row   objxlWorkSheet GetRow intRowCount               if  Row    null                                ICell Cell   Row GetCell 0                   ICell objCell   objxlWorkSheet GetRow intRowCount  GetCell intColumnCount

User · Answer

Use Open XML   Here is some code to process a spreadsheet with a specific tab or sheet name and dump it to something like CSV    I chose a pipe instead of comma    I wish it was easier to get the value from a cell  but I think this is what we are stuck with   You can see that I reference the MSDN documents where I got most of this code   That is what Microsoft recommends            lt summary gt          Got code from  https   msdn microsoft com en-us library office gg575571 aspx          lt  summary gt       Test      public void WriteOutExcelFile                 var fileName    ExcelFiles  File With Many Tabs xlsx           var sheetName    Submission Form      Existing tab name          using  var document   SpreadsheetDocument Open fileName  isEditable  false                         var workbookPart   document WorkbookPart              var sheet   workbookPart Workbook Descendants lt Sheet gt    FirstOrDefault s   gt  s Name    sheetName               var worksheetPart    WorksheetPart  workbookPart GetPartById sheet Id                var sheetData   worksheetPart Worksheet Elements lt SheetData gt    First                 foreach  var row in sheetData Elements lt Row gt                                   foreach  var cell in row Elements lt Cell gt                                           Console Write       GetCellValue cell  workbookPart                                      Console Write   n                                            lt summary gt          Got code from  https   msdn microsoft com en-us library office hh298534 aspx          lt  summary gt           lt param name  cell  gt  lt  param gt           lt param name  workbookPart  gt  lt  param gt           lt returns gt  lt  returns gt      private string GetCellValue Cell cell  WorkbookPart workbookPart                if  cell    null                        return null                     var value   cell CellFormula    null               cell CellValue InnerText                cell InnerText Trim                If the cell represents an integer number  you are done              For dates  this code returns the serialized value that             represents the date  The code handles strings and             Booleans individually  For shared strings  the code             looks up the corresponding value in the shared string             table  For Booleans  the code converts the value into             the words TRUE or FALSE          if  cell DataType    null                        return value                    switch  cell DataType Value                        case CellValues SharedString                      For shared strings  look up the value in the                    shared strings table                  var stringTable                       workbookPart GetPartsOfType lt SharedStringTablePart gt                             FirstOrDefault                        If the shared string table is missing  something                     is wrong  Return the index that is in                    the cell  Otherwise  look up the correct text in                     the table                  if  stringTable    null                                        value                           stringTable SharedStringTable                              ElementAt int Parse value   InnerText                                    break               case CellValues Boolean                  switch  value                                        case  0                           value    FALSE                           break                      default                          value    TRUE                           break                                    break                    return value

User · Answer

public void excelRead string sheetName                        Excel Application appExl   new Excel Application                Excel Workbook workbook   null              try                               string methodName                         Excel Worksheet NwSheet                  Excel Range ShtRange                     Opening Excel file myData xlsx                  appExl   new Excel Application                      workbook   appExl Workbooks Open sheetName  Missing Value  ReadOnly  false                   NwSheet    Excel Worksheet workbook Sheets get Item 1                   ShtRange   NwSheet UsedRange    gives the used cells in sheet                   int rCnt1   0                  int cCnt1   0                   for  rCnt1   1  rCnt1  lt   ShtRange Rows Count  rCnt1                                          for  cCnt1   1  cCnt1  lt   ShtRange Columns Count  cCnt1                                                  if  Convert ToString NwSheet Cells rCnt1  cCnt1  Value2      Y                                                          methodName   NwSheet Cells rCnt1  cCnt1 - 2  Value2                              Type metdType   this GetType                                MethodInfo mthInfo   metdType GetMethod methodName                                if  Convert ToString NwSheet Cells rCnt1  cCnt1 - 2  Value2      fn AddNum     Convert ToString NwSheet Cells rCnt1  cCnt1 - 2  Value2      fn SubNum                                                                 StaticVariable intParam1   Convert ToInt32 NwSheet Cells rCnt1  cCnt1   3  Value2                                   StaticVariable intParam2   Convert ToInt32 NwSheet Cells rCnt1  cCnt1   4  Value2                                   object   mParam1   new object     StaticVariable intParam1  StaticVariable intParam2                                    object result   mthInfo Invoke this  mParam1                                   StaticVariable intOutParam1   Convert ToInt32 result                                   NwSheet Cells rCnt1  cCnt1   5  Value2   Convert ToString StaticVariable intOutParam1          Convert ToString StaticVariable intOutParam1    String Empty                                                             else                                                               object   mParam   new object                                        mthInfo Invoke this  mParam                                    NwSheet Cells rCnt1  cCnt1   5  Value2   StaticVariable outParam1         StaticVariable outParam1   String Empty                                  NwSheet Cells rCnt1  cCnt1   6  Value2   StaticVariable outParam2         StaticVariable outParam2   String Empty                                                            NwSheet Cells rCnt1  cCnt1   1  Value2   StaticVariable resultOut                              NwSheet Cells rCnt1  cCnt1   2  Value2   StaticVariable resultDescription                                                     else if  Convert ToString NwSheet Cells rCnt1  cCnt1  Value2      N                                                         MessageBox Show  Result is No                                                      else if  Convert ToString NwSheet Cells rCnt1  cCnt1  Value2      EOF                                                         MessageBox Show  End of File                                                                                        workbook Save                    workbook Close true  Missing Value  Missing Value                   appExl Quit                    System Runtime InteropServices Marshal FinalReleaseComObject ShtRange                   System Runtime InteropServices Marshal FinalReleaseComObject NwSheet                   System Runtime InteropServices Marshal FinalReleaseComObject workbook                   System Runtime InteropServices Marshal FinalReleaseComObject appExl                             catch  Exception                                workbook Close true  Missing Value  Missing Value                             finally                               GC Collect                    GC WaitForPendingFinalizers                    System Runtime InteropServices Marshal CleanupUnusedObjectsInCurrentContext                               code for reading excel data in datatable public void testExcel string sheetName                        try                               MessageBox Show sheetName                    foreach Process p in Process GetProcessesByName  EXCEL                                          p Kill                                        string fileName    E   inputSheet                   Excel Application oXL                  Workbook oWB                  Worksheet oSheet                  Range oRng                        creat a Application object                 oXL   new Excel Application                             get   WorkBook  object                 oWB   oXL Workbooks Open sheetName                          get   WorkSheet object                 oSheet    Microsoft Office Interop Excel Worksheet oWB Sheets 1                   System Data DataTable dt   new System Data DataTable                      DataSet ds   new DataSet                      ds Tables Add dt                   DataRow dr                    StringBuilder sb   new StringBuilder                    int jValue   oSheet UsedRange Cells Columns Count                  int iValue   oSheet UsedRange Cells Rows Count                        get data columns                 for  int j   1  j  lt   jValue  j                                          oRng    Microsoft Office Interop Excel Range oSheet Cells 1  j                       string strValue   oRng Text ToString                        dt Columns Add strValue  System Type GetType  System String                                           string colString   sb ToString   Trim                      string   colArray   colString Split                             get data in cell                 for  int i   2  i  lt   iValue  i                                          dr   dt NewRow                        for  int j   1  j  lt   jValue  j                                                  oRng    Microsoft Office Interop Excel Range oSheet Cells i  j                           string strValue   oRng Text ToString                            dr j - 1    strValue                                              dt Rows Add dr                                     if StaticVariable dtExcel    null                                        StaticVariable dtExcel Clear                        StaticVariable dtExcel   dt Copy                                      else                 StaticVariable dtExcel   dt Copy                     oWB Close true  Missing Value  Missing Value                   oXL Quit                    MessageBox Show sheetName                               catch  Exception ex                                MessageBox Show ex Message                             finally                                           code for class initialize  public static void startTesting TestContext context                         Playback Initialize                ReadExcel myClassObj   new ReadExcel                string sheetName                 StreamReader sr   new StreamReader   E  SaveSheetName txt                sheetName   sr ReadLine                sr Close                myClassObj excelRead sheetName               myClassObj testExcel sheetName                code for test initalize public  void runValidatonTest                          DataTable dtFinal   StaticVariable dtExcel Copy                for  int i   0  i  lt  dtFinal Rows Count  i                                  if  TestContext TestName    dtFinal Rows i  2  ToString    amp  amp  dtFinal Rows i  3  ToString       Y   amp  amp  dtFinal Rows i  4  ToString       TRUE                                         MessageBox Show TestContext TestName                       MessageBox Show dtFinal Rows i  2  ToString                         StaticVariable runValidateResult    true                       break                                                StaticVariable dtExcel   dtFinal Copy

User · Answer

First of all  it s important to know what you mean by  open an Excel file for reading and copy it to clipboard      This is very important because there are many ways you could do that depending just on what you intend to do  Let me explain    If you want to read a set of data and copy that in the clipboard and you know the data format  e g  column names   I suggest you use an OleDbConnection to open the file  this way you can treat the xls file content as a Database Table  so you can read data with SQL instruction and treat the data as you want  If you want to do operations on the data with the Excel object model then open it in the way you began  Some time it s possible to treat an xls file as a kind of csv file  there are tools like File Helpers which permit you to treat and open an xls file in a simple way by mapping a structure on an arbitrary object    Another important point is in which Excel version the file is   I have  unfortunately I say  a strong experience working with Office automation in all ways  even if bounded in concepts like Application Automation  Data Management and Plugins  and generally I suggest only as the last resort  to using Excel automation or Office automation to read data  just if there aren t better ways to accomplish that task   Working with automation could be heavy in performance  in terms of resource cost  could involve in other issues related for example to security and more  and last but not at least  working with COM interop it s not so  free    So my suggestion is think and analyze the situation within your needs and then take the better way

User · Answer

Using OlebDB  we can read excel file in C   easily  here is the code while working with Web-Form  where FileUpload1 is file uploading tool     string path   Server MapPath    Uploads       if   Directory Exists path         Directory CreateDirectory path       get file path filePath   path   Path GetFileName FileUpload1 FileName     get file extenstion string extension   Path GetExtension FileUpload1 FileName     save file on  Uploads  folder of project FileUpload1 SaveAs filePath    string conString   string Empty    check file extension switch  extension        case   xls     Excel 97-03          conString    Provider Microsoft Jet OLEDB 4 0 Data Source Excel03ConString Extended Properties  Excel 8 0 HDR YES            break      case   xlsx     Excel 07 and above          conString    Provider Microsoft ACE OLEDB 12 0 Data Source Excel07ConString Extended Properties  Excel 8 0 HDR YES            break       create datatable object DataTable dt   new DataTable    conString   string Format conString  filePath      Use OldDb to read excel using  OleDbConnection connExcel   new OleDbConnection conString         using  OleDbCommand cmdExcel   new OleDbCommand                  using  OleDbDataAdapter odaExcel   new OleDbDataAdapter                          cmdExcel Connection   connExcel                 Get the name of First Sheet              connExcel Open                DataTable dtExcelSchema              dtExcelSchema   connExcel GetOleDbSchemaTable OleDbSchemaGuid Tables  null               string sheetName   dtExcelSchema Rows 0   TABLE NAME   ToString                connExcel Close                   Read Data from First Sheet              connExcel Open                cmdExcel CommandText    SELECT   From      sheetName                    odaExcel SelectCommand   cmdExcel              odaExcel Fill dt               connExcel Close                         bind datatable with GridView GridView1 DataSource   dt  GridView1 DataBind      Source   https   qawithexperts com article asp-net read-excel-file-and-import-data-into-gridview-using-datatabl 209  Console application similar code example https   qawithexperts com article c-sharp read-excel-file-in-c-console-application-example-using-oledb 168  If you need don t want to use OleDB  you can try https   github com ExcelDataReader ExcelDataReader  which seems to have the ability to handle both formats   xls and  xslx

[c#] How to read data of an Excel file using C#?

Examples related to c#

Examples related to excel