Read Excel all columns as string in C#
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;
}
Related videos on Youtube
Comments
-
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 over 10 yearsIf ypu are using Interop, you don't need a connection string, google
excel Interop c# example
for more info -
Daniel Möller over 10 yearsIs 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 over 10 years@JMK,@Daniel. Thank you for advice. I will try with excel interop
-
-
coder over 10 yearsThank you, but id didn't resolve my problem. I will try as suggested by commentator to use excel interop.
-
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 about 10 yearsI think the FirstCell.Offset in the while loop is missing the .Value