none
C# - Reading XLSX in C# - Unable to read data? RRS feed

  • Question

  • Good evening,

    I am developing a WPF that has a routine to read an XLSX file (using VS 2017). The WPF form has a Button to activate OpenDialog and read the XLSX file and a ListBox where the contents of the XLSX will be displayed. There is only one worksheet in the XLSX file. Things seem to be going ok, up to getting used cell ranges, etc., then when it comes to reading specific cells the program crashes, namely when I tried reading contents of cell A2, showing error that states  'Cannot convert type 'double' to 'string''. Further investigation revealed that the cell content comes up as null. Knowing that the cell in question contains a value of 1000, I wonder why it is being read as null (if it is being read at all.)

    For coding, here is what I have added in the using area:
    using System.Runtime.InteropServices;
    using Excel=Microsoft.Office.Interop.Excel;


    Here is the code I am using to read the XLSX (when user clicks on button):

    private void GetEXCELFile_Click(object sender, RoutedEventArgs e)
            {
                Microsoft.Win32.OpenFileDialog openFileDlg = new Microsoft.Win32.OpenFileDialog();
                openFileDlg.Filter = "Excel Worksheets|*.xls;*.xlsx";
                openFileDlg.Title = "Open Excel Structure Inventory";
                Nullable<bool> result = openFileDlg.ShowDialog();
                if ( result == true)
                {
                    Excel.Application ExlApp;
                    Excel.Workbook ExlBook;
                    Excel.Worksheet ExlSheet;
                    Excel.Range ExlRange;
    
                    int RowCount, ColumnCount, PipeCount;
                    int nRow, nCol, nPipes;
                    string PointNo, Northing, Easting, Elevation, Desc, Comment;
    
                    ExlApp = new Excel.Application();
                    ExlApp.Visible = true;
                    ExlBook = ExlApp.Workbooks.Open(openFileDlg.FileName);
                    ExlSheet = (Excel.Worksheet)ExlBook.Sheets[1]; 
                    
                    ExlRange = ExlSheet.UsedRange;
                    nRow = ExlRange.Rows.Count;
                    nCol = ExlRange.Columns.Count;
    
                    double StrNorth, StrEast, StrElev;
                    string StrCode, StrComment;
    
                    //Begin processing ****************************************
    
                    for (RowCount = 2; RowCount <= nRow; RowCount++)
                    {
                        PointNo = (string)(ExlRange.Cells[RowCount, 1] as Excel.Range).Value2;   //read Point #
                        Northing = (string)(ExlRange.Cells[RowCount, 2] as Excel.Range).Value2;  //Read Northing
                        StrNorth = double.Parse(Northing);                                       //convert to double to perform future opps
                        Easting = (string)(ExlRange.Cells[RowCount, 3] as Excel.Range).Value2;   //Read Easting
                        StrEast = double.Parse(Easting);                                         //convert to double to perform future opps                 
                        Elevation = (string)(ExlRange.Cells[RowCount, 4] as Excel.Range).Value2; //Read Elevation
                        StrElev = double.Parse(Elevation);                                       //convert to double to perform future opps
                        Desc = (string)(ExlRange.Cells[RowCount, 5] as Excel.Range).Value2;      //Read description
                        StrCode = Desc;
                        Comment = (string)(ExlRange.Cells[RowCount, 6] as Excel.Range).Value2;   //Read Comment
                        StrComment = Comment;
    
                        EXCELList.Items.Add( PointNo + ": N=" + Northing + " E=" + Easting + " Z=" + Elevation +
                                                  " Code" + Desc);                                //Add to ListBox
    
                    }
    
                    //End processing  ************************************************
    
                    ExlBook.Close(false, null, null);
                    ExlApp.Quit();
    
                    Marshal.ReleaseComObject(ExlSheet);
                    Marshal.ReleaseComObject(ExlBook);
                    Marshal.ReleaseComObject(ExlApp);
                }
                DIPTabs.SelectedIndex = 2;
            }

    I have Google'ed and Bing'ed and the solutions I have found tell me my code should work.

    So, if any of you have any inkling as to why I am failing at this XLSX-reading business, your helping me will be greatly appreciated.

    Thank you,

    Ric.

    Sunday, September 16, 2018 1:35 AM

Answers

  • Hi ASE_RRenteria,

    >>when you tried reading contents of cell A2, showing error that states  'Cannot convert type 'double' to 'string''.

    The Text property can be use to retrieve the text contents from a Cell.

    So, you could try to use the code as follows: 

     for (RowCount = 2; RowCount <= nRow; RowCount++)
                    {
                        PointNo = (ExlRange.Cells[RowCount, 1] as Excel.Range).Text.ToString();   //read Point #
                        Northing = (ExlRange.Cells[RowCount, 2] as Excel.Range).Text.ToString();  //Read Northing
                        StrNorth = double.Parse(Northing);                                       //convert to double to perform future opps
                        Easting = (string)(ExlRange.Cells[RowCount, 3] as Excel.Range).Text.ToString();   //Read Easting
                        StrEast = double.Parse(Easting);                                         //convert to double to perform future opps                 
                        Elevation = (string)(ExlRange.Cells[RowCount, 4] as Excel.Range).Text.ToString(); //Read Elevation
                        StrElev = double.Parse(Elevation);                                       //convert to double to perform future opps
                        Desc = (string)(ExlRange.Cells[RowCount, 5] as Excel.Range).Text.ToString();      //Read description
                        StrCode = Desc;
                        Comment = (string)(ExlRange.Cells[RowCount, 6] as Excel.Range).Text.ToString();   //Read Comment
                        StrComment = Comment;
    
                        EXCELList.Items.Add(PointNo + ": N=" + Northing + " E=" + Easting + " Z=" + Elevation +
                                                  " Code" + Desc);                                //Add to ListBox
    
                    }

    Hopefully it helps you.

    Best Regards, 

    Lina


    MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Wouter Defour Monday, September 17, 2018 9:41 AM
    • Marked as answer by ASE_RRenteria Monday, September 17, 2018 9:05 PM
    Monday, September 17, 2018 2:56 AM