Export data from worksheet to datatable

Aspose.Cells Product Family
1

Hi,When i am trying to export data from a worksheet to dataTable(C#),I got all of the validations shown as header which i wanted to exclude them.

How do i clean these validations?Please find the attached excel file is for you reference.

appreciated if you could provide some helps.

Thanks,

This message was posted using Page2Forum (attachment) from Exporting Data from Worksheets - Aspose.Cells for .NET

2

Hi,

To clear all the validations in the worksheet, you may use Validations.Clear() method.

See the sample code:
Aspose.Cells.Validations validations = workbook.Worksheets[0].Validations;
validations.Clear();


Thank you.

3

Hi Thanks for your prompt response.

I have tried what you suggested above but i still got all of validation items list as headers in the DataTable.The attached screen shot is what got.

I have list the codes what i used below for you reference,and would you please double check it for me as i urgent need it to be resolved.

Workbook wbGetUpload = new Workbook();
wbGetUpload.Open(MapPath(strFilePath));

Worksheet wsLabForm = wbGetUpload.Worksheets[0];
Validations va = wsLabForm.Validations;
va.Clear();

//Delete blanks
wsLabForm.Cells.DeleteBlankColumns();
wsLabForm.Cells.DeleteBlankRows();
System.Data.DataTable dtGetExcelData = wsLabForm.Cells.ExportDataTableAsString(0, 0, wsLabForm.Cells.MaxRow + 1, wsLabForm.Cells.MaxColumn + 1);

//Bind the excel’s data into GridView
this.gvLoadedRecords.DataSource = dtGetExcelData;
this.gvLoadedRecords.DataBind();


Thanks a lot!!

4

Hi,

I am not sure about your requirement. Do you want to delete the second row, if this is the case, you may simply remove that row by using Cells.DeleteRow() method.
e.g

Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//To Delete the second row.
cells.DeleteRow(i);



Thank you.

5

Hi,
Sorry i did not explain what i needed in detail.

Please see the below screen shot shows the dropdown list(it also calls validations in excel) in the excel file i attached you the first time,i just want to get the selected item as value but i got all of the list item shown as header in datable when i used ExportDataTable function,
Please refer to the screen shots i attached below.

6

Hi,

We will get back to you soon.

Thank you.

7

Hi,

Well, the Cells.ExportDataTableAsString just exports the values of the cells to the data table and not the data validation settings. Please create a sample project with all the details and post it here to show/reproduce the issue you have mentioned. Also, create a file with your desired formatted data in MS Excel manually, it will help us to find the issue to figure it out soon.

Thank you