Select certain columns with excel query C #

Question

Select certain columns with excel query C #

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have the following query to select an excel document,

Select * From [Sheet1$]

But this excel has many columns, and I just need columns A, C, E, G, Z, AA.

I tried to do the following, but it did not work

Select * From [Sheet1$A1:A,C1:C,E1:E,G1:G,Z1:Z,AA1:A]

If you could help me. Thanks.

c#

asked by oscar ramirez 07.04.2017 в 04:04

source

2 answers

0

I show you the libraries of link there is a lot of documentation, maybe I could find something.

answered by 07.04.2017 в 20:01

Doubt with PHP sessions Insert 4 letters at the beginning of the vector

score 1 · Accepted Answer

We have two situations:

1. Columns have no name.

In this case we have to specify it in the property HDR HDR = NO

if (Path.GetExtension(pathArchivo).ToUpper() == ".XLSX")
{
         cb.Provider = "Microsoft.ACE.OLEDB.12.0";
         cb.Add("Extended Properties", "Excel 12.0 Xml;HDR=NO;IMEX=1;");
}

And we refer to the columns as F1, F2, Fn, etc. according to what we need, for example, if we only need columns A (F1) and C (F3):

OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
cb.DataSource = pathArchivo;

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

OleDbConnection conexionOleDb = new OleDbConnection(cb.ConnectionString);
conexionOleDb.Open();
OleDbCommand cmd = conexionOleDb.CreateCommand();



cmd.CommandText = "SELECT F1,"                               
                   + "F3 "
                   + "FROM [Sheet1$]";
OleDbDataReader dr = cmd.ExecuteReader();

2. The columns do have a name

We place the property HDR = YES and specify the name of our columns, for example, column A is called NAME and column C is called LAST NAME:

OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
    cb.DataSource = pathArchivo;

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

    OleDbConnection conexionOleDb = new OleDbConnection(cb.ConnectionString);
    conexionOleDb.Open();
    OleDbCommand cmd = conexionOleDb.CreateCommand();



    cmd.CommandText = "SELECT NOMBRE,"                               
                       + "APELLIDO "
                       + "FROM [Sheet1$]";
    OleDbDataReader dr = cmd.ExecuteReader();