Read excel file

Question

Hello to all. Here is an example found in the internet. Who knows how to read only the first column of an excel file? Those. I need to separately read all the columns of the excel file and add them to the arrays. Type analogue with a text file ( *.txt ), here is the code with .txt .

 string section = sr.ReadLine(); if (section.ToLower() == "firstcolumn") { string value = sr.ReadLine(); qnnr = value.Split(' '); }

Below is a reading from an excel file.

 static void Main(string[] args) { // Имя файла string filename = @"C:\data.xls"; // Строка подключения string ConnectionString = String.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=No\";Data Source={0}", filename); // Открываем соединение DataSet ds = new DataSet("EXCEL"); OleDbConnection cn = new OleDbConnection(ConnectionString); cn.Open(); // Получаем списко листов в файле DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); // Показать список листов в файле //for (int i = 0; i < schemaTable.Rows.Count; i++) //{ // // Имена листов // Console.WriteLine(schemaTable.Rows[i].ItemArray[2]); // // Дата модификации // Console.WriteLine(schemaTable.Rows[i].ItemArray[7]); //} // Береме название первого листа string sheet1 = (string)schemaTable.Rows[0].ItemArray[2]; // Выбираем все данные с листа string select = String.Format("SELECT * FROM [{0}]", sheet1); OleDbDataAdapter ad = new OleDbDataAdapter(select, cn); ad.Fill(ds); DataTable tb = ds.Tables[0]; }

Possible duplicate question: c # How easy it is to work with / open / change / save Excel / Сsv files

Answer 1 · 2012-11-11T08:34:33

After all the manipulations in DataTable.Columns you will have a description of the columns. If ONLY columns are needed, then SELECT TOP 0 * FROM sheet1 can be made. If the data at this stage is not needed at all, then I would do this:

  OleDbConnection cn = new OleDbConnection(ConnectionString); cn.Open(); // Получаем списко листов в файле DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); // Береме название первого листа string sheet1 = (string)schemaTable.Rows[0].ItemArray[2]; // Выбираем все данные с листа string select = String.Format("SELECT * FROM [{0}]", sheet1); OleDbCommand oleDB = new OleDbCommand(select, cn); OleDbDataReader reader = oleDB.ExecuteReader(); for (int i = 0; i < reader.FieldCount; i++) { string name = reader.GetName(i); // Имя столбца Type type = reader.GetFieldType(i); // Тип столбца }

In general, I think there are more "humane" ways through the schemes.

UPDATE

Immediately did not pay attention to the connection string. To specify from where to count column headings, use the HDR parameter of the connection string. Set it to TRUE and get as column names what is written in the first row. More details about the connection string can be found here .

if you write SELECT TOP 2 * from sheet1 -> get the first two lines.
Type like this, as the second example there site-do.ru/db/sql4.php

Read excel file

1 answer 1

More articles: