Problems reading all lines of an Excel with OLEDB and C#

Asked

Viewed 535 times

1

I am developing a program to import spreadsheets Excel with language C#, using the component OLEDB, when importing a spreadsheet with 100547 lines the program can only read 54046.

Follow the source code:

public class ReadExcel
{
    public string ConnectionExcel(ExcelUpload excelUpload)
    {
        //connection String for xls file format.
        if (excelUpload.fileExtension == ".xls")
        {
            excelUpload.excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelUpload.fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        //connection String for xlsx file format.
        else if (excelUpload.fileExtension == ".xlsx")
        {
            excelUpload.excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelUpload.fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }
        else
        {
            excelUpload.excelConnectionString = "";
        }
        return excelUpload.excelConnectionString;
    }

    public DataTable readArqExcel(string excelConnectionString, DataSet ds)
    {
        //Create Connection to Excel work book and add oledb namespace
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        excelConnection.Open();
        DataTable dt = new DataTable();

        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
        {
            return null;
        }

        //Numero de planilhas contidas no excel
        String[] excelSheets = new String[dt.Rows.Count];
        int count = 0;

        //excel data saves in temp file here.
        foreach (DataRow row in dt.Rows)
        {
            excelSheets[count] = row["TABLE_NAME"].ToString();
            count++;
        }

        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
        string query = string.Format("Select * from [{0}]", excelSheets[0]);
        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
        {
            dataAdapter.Fill(ds);
        }

        excelConnection.Close();

        return ds.Tables[0];
    }
}

I tested the IIS 8 (REMOTE SERVER) and the IIS Express (Visual Studio local server), I noticed that on the server IIS Express the code works perfectly, but , no IIS 8 the code ends up reading the file in half.

It’s some kind of web server configuration?

c# excel iis iis-express oledb

  • give a look pq excel has line limit per page

  • Good morning Eduardo, so in excel this normal, has all the right information. I’m thinking it would be in IIS but I haven’t found any memory settings or anything like that yet. Pq on IIS express works it imports the 100547 lines, agr on IIS inside the server it cuts by half.

1 answer

0


I was able to solve the problem through the Excel connection string as below, by changing the parameter IMEX =2 TO 

if (excelUpload.fileExtension == ".xls")
        {
            excelUpload.excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelUpload.fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        }
        //connection String for xlsx file format.
        else if (excelUpload.fileExtension == ".xlsx")
        {
            excelUpload.excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelUpload.fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
        }

Browser other questions tagged c# excel iis iis-express oledb

You are not signed in. Login or sign up in order to post.