Read Excel all columns as string in C#

13,093

Solution 1

Take a look at this on codeproject. As stated in the comments, if you're using Interop you don't need a connection string. You can simply open a workbook, get an array of the items (an object array) and call ToString() on each item to get its string representation. Something like this should do:

ApplicationClass app = new ApplicationClass();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;

Workbook book = app.Workbooks.Open(@"path\Book1.xls", 
    Missing.Value, Missing.Value, Missing.Value, 
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
    Missing.Value, Missing.Value, Missing.Value);

Worksheet sheet = (Worksheet)book.Worksheets[1];
Range range = sheet.get_Range(...);

string execPath = Path.GetDirectoryName(
    Assembly.GetExecutingAssembly().CodeBase);

object[,] values = (object[,])range.Value2;

for (int i = 1; i <= values.GetLength(0); i++)
{
    for (int j = 1; j <= values.GetLength(1); j++)
    {
        string s = values[i, j].ToString();
    }
}

Solution 2

What I usually do is that:

        Range FirstCell = YourWorkSheet.Range["A1"];  //Use the Header of the column you want instead of "A1", or even a name you give to the cell in the worksheet.

        List<string> ColumnValues = new List<string>();

        int i = 1;
        object CellValue = FirstCell.Offset[i, 0].Value;
        while (CellValue != null)
        {
            ColumnValues.Add(CellValue.ToString());
            i++;
            CellValue = FirstCell.Offset[i,0].Value;
        }
Share:
13,093

Related videos on Youtube

coder
Author by

coder

My motto is "Code First, Design Later"

Updated on June 04, 2022

Comments

  • coder
    coder over 1 year

    I am trying to read Excel columns as string in C#. I wrote following code:

     string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\\excelFile.xls;"
                                    + "Extended Properties=\"Excel 8.0;IMEX=1\"";
    

    The problem is that in one column I have mixed data type like numbers,strings. I already searched for solutions, but none was helpful for me. The link bellow didn't help me( https://stackoverflow.com/questions/11200472/read-excel-columns-as-text I found that Excel decide which type will be column according to top 10 columns.How can I fix this issue?I am using Microsoft.Office.Interop.Excel.

    • JMK
      JMK over 10 years
      If ypu are using Interop, you don't need a connection string, google excel Interop c# example for more info
    • Daniel Möller
      Daniel Möller over 10 years
      Is there a code where you get the data from the table??? I work with excel interop a lot, but never used connectionString. Always use objects, Worksheets, Ranges, to extract data. If you use that kind of code somewhere, I can help.
    • coder
      coder over 10 years
      @JMK,@Daniel. Thank you for advice. I will try with excel interop
  • coder
    coder over 10 years
    Thank you, but id didn't resolve my problem. I will try as suggested by commentator to use excel interop.
  • coder
    coder over 10 years
    @SpaceghostAli.Thank you. After a few modification I finnaly managed it. I will post my code in order of other user may find it useful.
  • aoakenfo
    aoakenfo about 10 years
    I think the FirstCell.Offset in the while loop is missing the .Value