Make a Query to an Excel File in C #?

Question

Make a Query to an Excel File in C #?

Navigation

#1 by (1 votes)

0

I have the following code, when making the query it does not bring me any record.

public DataSet ExcelToDS(string bdconn, string ruta, DateTime fecin, DateTime fecfin)
{
    string excelconn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + ruta + ";Extended Properties = \"Excel 8.0;HDR = YES;\"";

        OleDbConnection conn = default(OleDbConnection);
        conn = new OleDbConnection(excelconn);
        conn.Open();

        OleDbCommand query = default(OleDbCommand);
        query = new OleDbCommand("Select * From [Embarque Mty$] Where Fecha Between " + fecin.ToString("dd/MM/yyyy") + " And " + fecfin.ToString("dd/MM/yyyy") + "", conn);

        OleDbDataAdapter adp = new OleDbDataAdapter();
        adp.SelectCommand = query;

        DataSet DS = new DataSet();

        adp.Fill(DS);

c#

asked by Eduardol Rivas 11.10.2017 в 19:31

source

1 answer

Show Data using keyup (Does not work) how to make yii2 generate two buttons in a modal on the footer side?

score 1 · Answer 1

uses the nuget library exceldatareader

// Auto-detect format, supports:
    //  - Binary Excel files (2.0-2003 format; *.xls)
    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) {

    using (var reader = ExcelReaderFactory.CreateReader(stream)) {

        // Choose one of either 1 or 2:

        // 1. Use the reader methods
        do {
            while (reader.Read()) {
                // reader.GetDouble(0);
            }
        } while (reader.NextResult());

        // 2. Use the AsDataSet extension method
        var result = reader.AsDataSet();

        // The result of each spreadsheet is in result.Tables
    }

}