Read Excel all columns as string in C#

Question

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.

If ypu are using Interop, you don't need a connection string, google `excel Interop c# example` for more info — JMK, Mar 27 '13 at 20:24
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. — Daniel Möller, Mar 27 '13 at 20:26
@JMK,@Daniel. Thank you for advice. I will try with excel interop — coder, Mar 27 '13 at 20:31

Daniel Möller · Answer 1 · 2013-10-18T17:06:10.753

1

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;
        }

edited Oct 18 '13 at 17:06

answered Mar 27 '13 at 20:38

Daniel Möller

84,878
18
192
214

I think the FirstCell.Offset in the while loop is missing the .Value – aoakenfo Oct 16 '13 at 00:11

HasaniH · Accepted Answer · 2013-03-27T20:49:16.490

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();
    }
}

@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. — coder, Mar 27 '13 at 21:01

score 0 · Answer 3 · answered Mar 27 '13 at 20:17

Hmm. Don't know if it helps, but I had the same issue. Now it works for me with the following connection string:

<add name="Excel2010File"
     connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=&quot;Excel 12.0;READONLY=TRUE;IMEX=1&quot;"
     providerName="Microsoft.ACE.OLEDB.12.0" />

You can find the libraries for my provider in the web (sorry, don't have a link anymore) if you don't have it. And you can set it to 12.0 even with a lower versioned excel file.

Thank you, but id didn't resolve my problem. I will try as suggested by commentator to use excel interop. — coder, Mar 27 '13 at 20:31

Read Excel all columns as string in C#

3 Answers3