Select Query in Excel Sheet

Aspose.Cells Product Family
1

Hi,



I’m working in Asp.Net Platform.

Can you please guide me to run Select Query in the aspose excel sheets instead of exporting data by each cell.I’m using Oralce DataAdapter to store the select query resultset.

2

Hi,


We don’t support Select Query to select worksheet cells. Well, you don’t need to export / get data cell by cell, you may export worksheet data to fill an array or even a DataTable using Cells.ExportArray method or Cells.ExportDataTable method. See the relevant topic for your help:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/exporting-data-from-worksheets.html

Thank you.

3

As you said i have stored the excel rows and columns in a dataset.Now i want to run a select a Query in dataset but it stores all the excel rows into dataset rows .

I want first excel rows should be the header of the dataset. Is it possible ?

Here is my code .

FileStream fstream = new FileStream(@“C:\AsposeTest.xlsx”, FileMode.Open);
Workbook workbook = new Workbook(fstream);
Worksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 100, 30);
DataSet ds =new DataSet();
ds.Tables.Add(dataTable.Select(" [EmpID] <> null").CopyToDataTable());
fstream.Close();

4

Hi,


I think you may try other overloads of the ExportDataTable() method and change your line, i.e.:

DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 100, 30);

to:

DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 100, 30, true);

The last parameter is a Boolean i.e. exportColumnName --> put true for it. The first row will be taken as Column Names in the DataTable.

Thank you.
5

thank you so much

Now it is working fine.

Is it possible to get excel sheet name collections ?


6

Hi,


Well, you need to use some loop to loop through the worksheets to get the name of the sheets. See a simple code, e.g

Sample code:

Workbook wb = new Workbook(@“E:\test2\Book1.xls”);
foreach (Worksheet ws in wb.Worksheets)
{
MessageBox.Show(ws.Name);
//You code goes here.
}