Export spreadsheet into database

Aspose.Cells Product Family
1

I have a excel workbook template with 4 worksheets. All sheets have the same structure and data from these 4 sheets goes into the same table. There are 40 columns and about 30 rows of data in each sheet. Each row in the spreadsheet is stored as one record in the table. Every cell value has to validated before inserting into the database. These validations could be checking for 1. Empty values, 2. If the value is in a given range of values, 3. check if the value exists in the database, 4. value could be used to query the database for other info etc.

Could you please let me know what functions in the Cells API should be used to perform this export.

How do I attach validations for each cell value or how do i implement validations while exporting the spreadsheet data.

I have a lot of named ranges defined in the spreadsheet template which refer to a single cell or a set of rows and columns (as a table). How do I traverse through these ranges. What is the equivalent to Excel's HLookup and VLookup functions?

Appreciate if you could provide some code samples or pointers to the documentation for each of the above tasks.

2

Hi,

Aspose.Cells can export data to fill a data table for your need. You may use Cells.ExportDataTable() method to export data to fill the datatable, see the document for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/exporting-data-from-worksheets.html You can also use Range.ExportDataTable() method to fill data to the datatable.

For validation upon datatable, you should use your own code to implement it, Aspose.Cells does not do that, it simply export the simple data to fill a new datatable or a predefined/existing datatable, so you should use your own .NET code to check each value in the field accordingly before saving to the database.

Moreover, while living in XLS format in MS Excel, you can surely use Data Validations but when you export to data to data table, no validation attributes are exported, this is same as MS Excel.

Thanks for your understanding!