Reading columns from Excel, reformat cells

Question

I am currently trying to read in cells from an excel spread sheet, and it seems to reformat cells when I don't want it to. I want it to come through as plan text. I have read a couple of solutions to this problem and I have implemented them, but I am still having the same issue.

The reader turns dates in numbers and numbers into dates.

Example:

Friday, January 29, 2016 comes out to be : 42398

and

40.00 comes out to be : 2/9/1900 12:00:00 AM

code:

string stringconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + files[0] + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
try {
    OleDbConnection conn = new OleDbConnection(stringconn);
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [CUAnswers$]", conn);
    DataTable dt = new DataTable();
    try {
        printdt(dt);

I have tried

IMEX=0;
HDR=NO;
TypeGuessRows=1;

This is how I am printing out the sheet

public void printdt(DataTable dt) {
        int counter1 = 0;
        int counter2 = 0;
        string temp = "";
        foreach (DataRow dataRow in dt.Rows) {
            foreach (var item in dataRow.ItemArray) {
                temp += " ["+counter1+"]["+counter2+"]"+ item +", ";
                counter2++;
            }
            counter1++;
            logger.Debug(temp);
            temp = "";
            counter2 = 0;
        }
    }

Peter Bill · Accepted Answer · 2016-05-19T07:08:02.180

I had a similar problem, except it was using Interop to read the Excel spreadsheet. This worked for me:

var value = (range.Cells[rowCnt, columnCnt] as Range).Value2;
string str = value as string;
DateTime dt;
if (DateTime.TryParse((value ?? "").ToString(), out dt))
{
    // Use the cell value as a datetime
}

Editted to add new ideas

I was going to suggest saving the spreadsheet as comma-separated values. Then Excel converts the cells to text. It is easy to parse a CSV in C#.

That led me to think of how to programmatically do the conversion, which is covered in Convert xls to csv programmatically. Maybe the code in the accepted answer is what you are looking for:

string ExcelFilename = "c:\\ExcelFile.xls";
DataTable worksheets;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + ExcelFilename + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    worksheets = connection.GetSchema("Tables");
    foreach (DataRow row in worksheets.Rows)
    {
        // For Sheets: 0=Table_Catalog,1=Table_Schema,2=Table_Name,3=Table_Type
        // For Columns: 0=Table_Name, 1=Column_Name, 2=Ordinal_Position
        string SheetName = (string)row[2];
        OleDbCommand command = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", connection);
        OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
        oleAdapter.SelectCommand = command;
        DataTable dt = new DataTable();
        oleAdapter.FillSchema(dt, SchemaType.Source);
        oleAdapter.Fill(dt);
        for (int r = 0; r < dt.Rows.Count; r++)
        {
           string type1 = dr[1].GetType().ToString();
           string type2 = dr[2].GetType().ToString();
           string type3 = dr[3].GetType().ToString();
           string type4 = dr[4].GetType().ToString();
           string type5 = dr[5].GetType().ToString();
           string type6 = dr[6].GetType().ToString();
           string type7 = dr[7].GetType().ToString();
        }
    }
}

This doesn't help. I don't want to re-convert it. I want it to come out of the cell in the correct format. or just as plan text. — Sari Rahal, May 17 '16 at 17:02
It seems to me if you want to read a cell as a string, then it needs to be entered as a string and not as a datetime (or whatever). You may need to alter the spreadsheet and convert those cells to text, possibly by prefixing the value with a single quote. (That is probably not the answer you wanted.) — Peter Bill, May 18 '16 at 21:50

Reading columns from Excel, reformat cells

1 Answers1