Results 1 to 4 of 4

Thread: How to read Excel (xls file) From C#

  1. #1
    Join Date
    Oct 2010
    Posts
    76

    How to read Excel (xls file) From C#

    I am running Windows 7 on my Core i3 machine. Basically I have bought this machine just for hard core programing. I have got stuck while reading the excel files from the C#. Actually once I will read the DataBase it would be better for me to parse the same on C#. I am having Test.xls file on the local "C:\" drive. Is there anybody who have done this ever in life. Please share your experience with me so that I can at least learn something from it.

  2. #2
    Join Date
    May 2009
    Posts
    529

    Re: How to read Excel (xls file) From C#

    I think you need to create an object reference in your project for an Excel File that you want to access it from the C#. You can add Excel library object in the COM tab as adding reference dialog. I suggest you to use the following code in your menu click event method which will help you accordingly.

    Code:
      this.openFileDialog1.Book1 = "*.xls";
      if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
       {
          Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
             openFileDialog1.Book1, 0, true, 5,
              "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
              0, true); 
         Excel.Sheets sheet1 = theWorkbook.Worksheets;
         Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
         for (int i = 1; i <= 10; i++)
         {
         Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
         System.Array myvalues = (System.Array)range.Cells.Value;
         string[] strArray = ConvertToStringArray(myvalues);
         }
    }

  3. #3
    Join Date
    May 2009
    Posts
    539

    Re: How to read Excel (xls file) From C#

    Follow these steps to create a Visual C++ 6.0 that automates simple Excel:
    1. Start Visual C++ 6.0 and create a new Win32 console application called XlCpp. Choose an application "Hello, World!" And click Finish.
    2. Open the file generated XlCpp.cpp and add the following code before the main function ():

    Code:
    # Include <ole2.h> / / OLE2 Definitions
    
     / / AutoWrap () - Automation helper function ...
         HRESULT AutoWrap (int Autotype, VARIANT * pvResult, IDispatch * pDisp, LPOLESTR ptName, int carga ...) {
         / / Begin variable-argument list ...
         va_marklist;
         va_begin (marker, carga);
    
         if (! pDisp) {
             MessageBox (NULL, "NULL IDispatch Passed to AutoWrap ()", "Error", 0x10010);
             _exit (0);
         }
    
         / / Variables used ...
         DISPPARAMS disparam = {NULL, NULL, 0, 0};
         DISPID = dispidNamed DISPID_PROPERTYPUT;
         DISPID dpID;
         HRESULT hresult;
         char buffer [200];
         char Namesize [200];
    
        
         / / Convert down to ANSI
         WideCharToMultiByte (CP_ACP, 0, ptName, -1, Namesize, 256, NULL, NULL);
        
         / / Get DISPID for name Passed ...
         hresult = pDisp-> GetIDsOfNames (IID_NULL, & ptName, 1, LOCALE_USER_DEFAULT, & dpID);
         if (FAILED (hresult)) {
             sprintf (buffer, "IDispatch:: GetIDsOfNames (\"% s \ ") failed w / err 0x% 08lx", Namesize, hresult);
             MessageBox (NULL, buffer, "AutoWrap ()", 0x10010);
             _exit (0);
             return hresult;
         }
        
         / / Allocate memory for arguments ...
         VARIANT * parga = new VARIANT [carga +1];
         / / Extract arguments ...
         for (int i = 0; i <carga; i + +) {
             parga [i] = va_arg (marker, VARIANT);
         }
        
         / / Build DISPPARAMS
         disparam.cArgs = carga;
         disparam.rgvarg = parga;
        
         / / Handle special-case for property-puts!
         if (autotype & DISPATCH_PROPERTYPUT) {
             disparam.cNamedArgs = 1;
             disparam.rgdpIDNamedArgs dpIDNamed = &;
         }
        
         / / Make the call!
         hresult = pDisp-> Invoke (dpID, IID_NULL, LOCALE_SYSTEM_DEFAULT, Autotype, & disparam, pvResult, NULL, NULL);
         if (FAILED (hresult)) {
             sprintf (buffer, "IDispatch:: Invoke (\"% s \ "=% 08lx) failed w / err 0x% 08lx", Namesize, dpID, hresult);
             MessageBox (NULL, buffer, "AutoWrap ()", 0x10010);
             _exit (0);
             return hresult;
         }
         / / End variable-argument section ...
         va_end (marker);
        
         delete [] parga;
        
         return hresult;
     }
    3. In the function main (), replace the line printf () with the following code:

    Code:
     / / Initialize COM for this thread ...
        CoInitialize (NULL);
    
        / / Get CLSID for Our server ...
        CLSID clsid;
        HRESULT hresult = CLSIDFromProgID (L "Excel.Application", & clsid);
    
        if (FAILED (hresult)) {
    
           :: MessageBox (NULL, "CLSIDFromProgID () failed", "Error", 0x10010);
           return -1;
        }
    
        / / Start server and get IDispatch ...
        IDispatch * pXlApp;
        hresult = CoCreateInstance (clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **) & pXlApp);
        if (FAILED (hresult)) {
           :: MessageBox (NULL, "Excel not registered Properly", "Error", 0x10010);
           return -2;
        }
    
        / / Make it visible (ie app.visible = 1)
        {
    
           VARIANT x;
           x.vt = VT_I4;
           x.lVal = 1;
           AutoWrap (DISPATCH_PROPERTYPUT, NULL, pXlApp, L "Visible", 1, x);
        }
    
        / / Get Workbooks collection
        IDispatch * pXlBooks;
        {
           VARIANT result;
           VariantInit (& result);
           AutoWrap (DISPATCH_PROPERTYGET, & result, pXlApp, L "Workbooks", 0);
           pXlBooks = result.pdispVal;
        }
    
        / / Call Workbooks.Add () to get a new workbook ...
        IDispatch * pXlBook;
        {
           VARIANT result;
           VariantInit (& result);
           AutoWrap (DISPATCH_PROPERTYGET, & result, pXlBooks, L "Add", 0);
           pXlBook = result.pdispVal;
        }
    
        / / Create a SAFEARRAY of variants 15x15 ...
        VARIANT arr;
        arr.vt = VT_ARRAY | VT_VARIANT;
        {
           SAFEARRAYBOUND sab [2];
           sab [0]. lLbound = 1; sab [0]. cElements = 15;
           sab [1]. lLbound = 1; sab [1]. cElements = 15;
           arr.parray SafeArrayCreate = (VT_VARIANT, 2, sab);
        }
    
        / / Fill safearray With Some gains ...
        for (int i = 1; i <= 15; i + +) {
           for (int j = 1 j <= 15 j + +) {
              / / Create entry value for (i, j)
              VARIANT tmp;
              tmp.vt = VT_I4;
              tmp.lVal = i * j;
              / / Add to safearray ...
              long indices [] = {i, j};
              SafeArrayPutElement (arr.parray, indices, (void *) & tmp);
           }
        }
    
        / / Get object ActiveSheet
        IDispatch * pXlSheet;
        {
           VARIANT result;
           VariantInit (& result);
           AutoWrap (DISPATCH_PROPERTYGET, & result, pXlApp, L "ActiveSheet", 0);
           pXlSheet = result.pdispVal;
        }
    
        / / Get Range object for the Range A1: O15 ...
        IDispatch * pXlRange;
        {
           VARIANT paramet;
           paramet.vt = VT_BSTR;
           paramet.bstrVal =:: SysAllocString (L "A1: O15");
    
           VARIANT result;
           VariantInit (& result);
           AutoWrap (DISPATCH_PROPERTYGET, & result, pXlSheet, L "Range", 1, paramet);
           VariantClear (& paramet);
    
           pXlRange = result.pdispVal;
        }
    
        / / Set range safearray With Our ...
        AutoWrap (DISPATCH_PROPERTYPUT, NULL, pXlRange, L "Value", 1, arr);
    
        / / Wait for user ...
        :: MessageBox (NULL, "All done." "Notice", 0x10000);
    
        / / Set. Saved property of Workbook to TRUE So We arent Prompted
        / / To save When We Tell Excel to quit ...
        {
           VARIANT x;
           x.vt = VT_I4;
           x.lVal = 1;
           AutoWrap (DISPATCH_PROPERTYPUT, NULL, pXlBook, L "Saved", 1, x);
        }
    
        / / Tell Excel to quit (ie App.Quit)
        AutoWrap (DISPATCH_METHOD, NULL, pXlApp, L "Quit", 0);
    
        / / Release references ...
        pXlRange-> Release ();
        pXlSheet-> Release ();
        pXlBook-> Release ();
        pXlBooks-> Release ();
        pXlApp-> Release ();
        VariantClear (& arr);
    
        / / Uninitialize COM for this thread ...
        CoUninitialize ();
    4. Compile and run.

    AutoWrap function () simplifies most of the low-level details involved in the direct use of IDispatch. Feel free to use in your own implementations. However, if you pass multiple parameters, you must do so in reverse order.
    Code:
    For example:    
      VARIANT paramet [3];
         paramet [0]. vt = VT_I4; paramet [0]. lVal = 1;
         paramet [1]. vt = VT_I4; paramet [1]. lVal = 2;
         paramet [2]. vt = VT_I4; paramet [2]. lVal = 3;
         AutoWrap (DISPATCH_METHOD, NULL, pDisp, The "call", 3, paramet [2], paramet [1], paramet [0]);

  4. #4
    Join Date
    Nov 2008
    Posts
    1,259

    Re: How to read Excel (xls file) From C#

    This little routine can be called from VB6 Excel or another Office application.
    Paste it in a general module (eg Module1)
    In VBA>> Insert>> Module and paste in the window ...
    In VB6>> Project>> Add Module, and paste in the window ...

    Code:
      Sun EX As New Application    
     Public Book As Workbook    
     Public Sheet As Worksheet 
    
    'Do not forget to add the reference .. 
      'Microsoft Excel X, X object librairy 
    
      Sub AjouterExcel ()    
         Set EX = CreateObject ("Excel.Application")    
         EX.Visible = True    
         Set Book = EX.Workbooks.Add    
         'All functions are available with Application BooK    
         Set Sheet = Book.Sheets (1)    
         Example:    
         Feuille.Name = "The Leaf"    
         With Leaf    
             . [A1] = "This is the cell A1"    
             . [A2] = "This is the cell A2"    
             . Columns ("A: A"). ColumnWidth = 23.14    
         End With    
         'All functions are available in excel sheet.    
     End Sub

Similar Threads

  1. How to read Text File in Excel via VBA
    By Rish!On in forum MS Office Support
    Replies: 7
    Last Post: 02-02-2012, 04:51 PM
  2. How to stop Open file as Read only in Microsoft Excel
    By RaaginiO in forum MS Office Support
    Replies: 2
    Last Post: 24-01-2012, 05:22 PM
  3. How to read excel, pdf, word file on Nokia 5233
    By Gaganasindhu in forum Portable Devices
    Replies: 5
    Last Post: 09-09-2010, 10:42 AM
  4. Excel is unable to read file
    By Logan.M in forum Software Development
    Replies: 4
    Last Post: 08-04-2009, 10:29 AM
  5. How to read and modify excel file using PHP script
    By JamesB in forum Software Development
    Replies: 3
    Last Post: 17-03-2009, 11:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,644,924.98475 seconds with 18 queries