Read / write Excel files with ADO.NET ~ DPLW

I recently raised the need to load an Excel file in a database through a web application. At first I thought about using dtsx package and started to investigate how you connect to SSIS these files. This is done through MDAC (Microsoft Data Access Framework) avoiding the need to have Microsoft Office installed on the server.Therefore, to read an Excel file can be done programmatically via OLE DB connection. Then I explain how:

Connecting to the file:

In the following code to create the connection string depending on the version through OleDbConnectionStringBuilder class:

rutaExcel string = "c: \ ArchivoExcel.xls";

/ / Create the connection string to excel file
Cb = new OleDbConnectionStringBuilder OleDbConnectionStringBuilder ();
cb.DataSource = rutaExcel;

if (Path. GetExtension (rutaExcel). ToUpper () == ". XLS")
{
    cb.Provider = "Microsoft.Jet.OLEDB.4.0";
    cb.Add ("Extended Properties", "Excel 8.0; HDR = YES; IMEX = 0;");
}
else if (Path. GetExtension (rutaExcel). ToUpper () == ". XLSX")
{
    cb.Provider = "Microsoft.ACE.OLEDB.12.0";
    cb.Add ("Extended Properties", "Excel 12.0 Xml; HDR = YES; IMEX = 0;");
}

Provider for. Xls is in the MDAC, but from the 2007 version, the provider must use the 2007 Office System Driver: Data Connectivity Components . In the extended properties of the connection string is set to the version of the file and the following parameters:

HDR: Indicates whether the first row is the name of the fields.
IMEX: This parameter indicates how to treat the types of data and can take the following parameters:

0: Export Mode. If you want to keep data types this is the mode to use.
1: Import mode. With the driver's default settings, evaluate the first 8 characters and depending on these sets as text or number.
2: Link mode. Refresh the file data

For this example we will use IMEX = 0 mode to work with typed data.

Data readout:

Once the connection string we just need to create the read data command:

DataTable dt = new DataTable ("Data");
using (OleDbConnection conn = new OleDbConnection (cb.ConnectionString))
{
    / / Open the connection
    conn.Open ();

    using (OleDbCommand cmd = conn.CreateCommand ())
    {
        cmd.CommandType = CommandType. Text;
        cmd.CommandText = "SELECT * FROM [Data $]";

        / / Save the data in the DataTable
        OleDbDataAdapter da = new OleDbDataAdapter (cmd);
        da.Fill (dt);
    }


    / / Close the connection
    conn.Close ();
}

This command will read all rows in the sheet "Data". As in the connection string parameter use the HDR = true, the first row must match the names of the columns. For the names of the tables use the following notation:

Sheet Name: The name of a sheet followed by the $ character. Example [Sheet1 $].
Range Name: You can use a range of data with a defined name.
Rank: Sheet Name + address range. Example: [Sheet1 $ A1: D100].

Creating Sheets

To create a new sheet using the CREATE TABLE command:

using (OleDbConnection conn = new OleDbConnection (cb.ConnectionString))
{
    / / Open the connection
    conn.Open ();

    / / Create the tab
    using (OleDbCommand cmd = conn.CreateCommand ())
    {
        cmd.CommandType = CommandType. Text;
        cmd.CommandText = @ "CREATE TABLE [Cars]
                            (
                                IdCoche INTEGER,
Brand TEXT
Model TEXT
                                DATETIME FxCompra
                            ) ";
        cmd.ExecuteNonQuery ();
    }

    / / Close the connection
    conn.Close ();
}

This example will create a sheet called "Cars" whose first row corresponds to the name of the columns. In the following example to insert data into the new sheet.

Inserting data

To insert data using the following command:

using (OleDbConnection conn = new OleDbConnection (cb.ConnectionString))
{
    / / Open the connection
    conn.Open ();

    / / Create the tab
    using (OleDbCommand cmd = conn.CreateCommand ())
    {
        cmd.CommandType = CommandType. Text;
        cmd.CommandText = @ "INSERT INTO [Cars $]
                            (
                                IdCoche,
                                Brand,
                                Model
                                FxCompra
                            ) VALUES
                            (
                                @ IdCoche,
                                @ Mark,
                                @ Model
                                @ FxCompra,
                            ) ";

        cmd.Parameters.AddWithValue ("@ IdCoche", 1);
        cmd.Parameters.AddWithValue ("@ Mark", "Ferrari");
        cmd.Parameters.AddWithValue ("@ Model", "599 GTB");
        cmd.Parameters.AddWithValue ("@ FxCompra" DateTime. Now);

        cmd.ExecuteNonQuery ();
    }

    / / Close the connection
    conn.Close ();
}

Conclusion

There are other solutions to import Excel files, but all require Microsoft Office installed on the server, or use a third-party component, which ultimately rely on this technique. As for export, whether there are other alternatives, such as can be seen in that other article .

The following links explain in detail this solution, which has been used since the beginning of ADO:

DPLW

Oct 30, 2011