Import (Insert/Update) spreadsheet data to db

Aspose.Cells Product Family
1

A spreadsheet is ftp-ed to a network shared folder (requires authentication).

1. I would like to read data records as is from this spreadsheet and insert them into a SQL server database.

2. Also I would like to read data from spreadsheet and update matching records into another table.

Could you please suggest the best way to acheive this and how to do this in .NET using Aspose.

Thanks

2
Hi,

Thanks for considering Aspose.

1). Well, you my try to use Cells.ExportDataTable() or Cells.ExportDataTableAsString() method to export the worksheet's data to fill a datatable. Later you may insert/update you SQL Server database table(s) using ADO.NET components.

E.g.,

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Create a new workbook for opening the template file
//From which I fill up a data table
Workbook wb = new Workbook();
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("d:\\test\\MyFile.xls",FileMode.Open);
//Opening the Excel file through the file stream
wb.Open(fstream);
//Extract data from the worksheet and fill a datatable.
DataTable dt = wb.Worksheets[0].Cells.ExportDataTable(0,0,wb.Worksheets[0].Cells.MaxDataRow,wb.Worksheets[0].Cells.MaxDataColumn);

.

.

.

2) I think you may read the spreadsheet using Workbook.Open() method and use Cell.StringValue, Cell.IntValue, Cell.Value etc. properties to read/get data in the cells and update your table accordingly.

Thank you.

3

I am using the ExportDataTable method and would like to read all the data in a given sheet. How do I get the values of total rows or columns (having data) to pass as parameters to the above method.

Thanks

4

Hi,

I have already mentioned in the code of my previous post.

You may use the following properties for your need:

1. Cells.MaxDataRow --> to get the index of the last row in a sheet which contains data.

2. Cells.MaxDataColumn ---> to get the index of the maximum column in a sheet which contains data.

//Extract data from the whole worksheet and fill a datatable.
DataTable dt = wb.Worksheets[0].Cells.ExportDataTable(0,0,wb.Worksheets[0].Cells.MaxDataRow+1,wb.Worksheets[0].Cells.MaxDataColumn+1);

Thank you.

5

Sorry..I haven't noticed the parameter details in your earlier reply. Thanks a lot.

How do I open an excel file located at a remote network share folder having authetication (has userid/password) ?

6

Hi,

We will get back to you soon.

Thank you.

7

Hi

Could you please provide me with a solution ASAP. I am working on a close deadline.

Thanks in advance.

8

How do I open an excel file located at a remote network share folder having authetication (has userid/password) ?

Aspose.Cells doesn't directly support this feature.

You can try to load a remote file into a MemoryStream and use Aspose.Cells to load the stream.

9

Hi,

Please find the sample code, May the sample test code helps you for your need.

System.IO.MemoryStream objImage;
System.Net.WebClient objwebClient;
string sURL = "http://www.workforceexplorer.com/admin/uploadedPublications/1083_NEW0102bench6203.xls";
try
{
objwebClient = new System.Net.WebClient();
objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(objImage);
Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
sheet.Cells["A20"].PutValue("Testing!");
wb.Save("d:\\test\\ntout_workbook.xls");

}
catch (Exception ex )
{
Console.WriteLine(ex.Message);

}
Thank you.
10

Mine is a windows console application and has no web access.