Extracting Data into Dataset From named columns & Sheet

Aspose.Cells Product Family
1

Hi,

I’d like some advice as to how I can achieve the following:

I would like to user to browse to an excel file using the fileupload control.

The system would then need to look through all the sheets and locate one called data.

This worksheet could have any number of columns. However I need the system to locate 3 columns (Text1, Text2, Text3) named in row number 1 and then copy all of the data from these 3 rows into a dataset.

Here is what I have so far:

// read the workbook data into the system

Workbook workbook = new Workbook();

workbook.Open(FileUpload1.PostedFile.InputStream);

DataTable dataTable = new DataTable();

dataTable = worksheet.Cells.ExportDataTable( //unsure );

dataTable.AcceptChanges();

DataSet ds = new DataSet();

ds.Tables.Add(dataTable);

Any help appreciated.

2

Hi,


I think you may try to create a range based on your desired data range and then use Range.ExportDataTable() method, see a sample code snippet below, you may try to change your code accordingly for your needs.

Sample code:

//…

//Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
//Create a range (A1:C1).
Range range = cells.CreateRange(“A1”, “C1”);

DataTable dt = range.ExportDataTable();

//…Your code goes here.


Thank you.
3

Hi,

Thanks for your reply.

Not sure how I can do this for different columns.

I’ve copied the entire contents of the worksheet, but I’m stuck as to what the best way would be to remove the columns that I do not need?

4

Hi,


The Range Export always takes consecutive rows and columns into account. For your requirements, it seems that you need to first find the columns that you are interested in and copy them next to each other temporary. This can be achieved using the Find/FindString function for searching the respective cells containing the desired column names, similar to:

Cell cell = worksheet.cells.Find(ColumnNameToFind);

This will give you the cell that contains the text of your column. You can get the column index from this using:

cell.Column

property. After the columns are identified, you can temporary copy each of these columns next to each other in the same sheet and export them as a range to a datatable as Amjad has suggested earlier.


5

Hi,

In order to delete any column, you can use the following code.

The following code deletes the column B. I have attached the source and output xlsx file for your reference. Please see the screenshot below.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


string colName = “B”;


int maxRow = worksheet.Cells.MaxDisplayRange.RowCount;


string rngName = colName + “1” + “:” + colName + maxRow;


Range rng = worksheet.Cells.CreateRange(rngName);


worksheet.Cells.DeleteRange(rng.FirstRow, rng.FirstColumn, rng.RowCount, rng.ColumnCount, ShiftType.Up);


workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Screenshot: