[VB] Spreadsheet from DataTable

Question

In my app, I'm downloading a spreadsheet from FTP, moving the data read from the spreadsheet to a DataTable, and, depending on certain conditions, emailing a new spreadsheet (one that contains certain rows from the 1st spreadsheet).

My problem is creating the spreadsheet that will be mailed. I can't seem to work out how to add the row from the DataTable (originally from the spreadsheet that was downloaded) into the spreadsheet that's going to be mailed.

DS.Tables(0).Rows.Add(ObjSheet.Range(workTable.Rows(i)) - 1)

Is what I've got at the moment, but it's not working. workTable is the DataTable where the rows from the spreadsheet were copied to. DS is the DataSet where I'm putting the rows from the specific selection. I'm pretty sure I can simplify the flow of data by eliminating either the DataTable or the DataSet.

To eliminate any confusion, I'm using VB2005 - Wish I could use 2008 for this, LINQ simplifies so much...

Answer 1

One of the answers may be to not program with office - if you can use the Office 2007 file formats (and given the existence of the Office Compatability pack there's no fundamental reason why you shouldn't although there may be issues within a specific environment) then you can use .NET code, System.IO.Packaging and the Open XML Format SDK 1.0 - that's .NET 3.0 which is just libraries and therefore useable from VS2005.

More pragmatically, don't be so keen (at least in the first instance) to do away with the intermediate steps - something I need to remind myself at regular intervals.

If you start with the following:

1) Load the data from the source 2) Process the data from the source 3) Write the result out to the destination

Then you'll be able to demonstrate at steps 1 and 2 that you have what you want, and step 3 becomes more manageable in that you're just trying to move known data from app to file.

Hmm, that's more "thoughts" than "answer" - but I hope it helps.

Answer 2

SpreadsheetGear for .NET can help you do this.

See the live ASP.NET (C# & VB) "Excel to Datagrid" samples here. There are more samples - some of which might prove useful - here.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

YOu can also do it with SmartXLS.

There is a sample which demonstrate how to export DataTable to spreadsheet files.