Export BO to Excel


Author
Message
Juan Carlos Pazos
Juan Carlos Pazos
StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)
Group: Forum Members
Posts: 144, Visits: 227
Hi

I use many times information from SF forums and many times when I had some question, someone help me a lot; so I think in contribute a routine that can you use in your applications. Hope someone benefit from this small sample.

The sample shows how to use the information in a ListView and export that information to MS Excel, I use the StrataFrame Sample database taking information from Orders and Order_Items to make a simple list, I fill that in the form and click the button it will check if it's installed Excel, if it's installed will lunch Excel, create header, subheaders and column headers with some format, then it will pass all the data of the BO filling one by one the rows in Excel, also it groups the information (I did this for the order number but you can make it to any thing you need) also add a row for subtotal for every group and at the end a grand total for all. Also sas a small formating the Excel sheet routine.

You have this:

And you will get this:

I hope you find usefull; and if someone has a way to Import from Excel to a BO it will be great because thats the next thing I need to doWink

Regards

Smile Everything is possible, just keep trying...

Attachments
ExportExcel.zip (259 views, 163.00 KB)
Edhy Rijo
E
StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)StrataFrame VIP (2.7K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Hi Juan Carlos,

Thanks for the contribution, I ran your sample and it worked as advertised. Sorry I can not help you out in your need to import from Excel to a BO.

Edhy Rijo

Greg McGuffey
Greg McGuffey
Strategic Support Team Member (2.2K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
Thanks for the contribution Juan Carlos!



On my list is the need to import from excel to a BO. The method I've been thinking about using is have Excel export the sheet to a CSV file, then use an ODBC driver, via a data source to access that data (via a BO of course). I'm going to investigate how you did the export, because that might be the first step of getting the data out of a BO into Excel, which the users could then change and I'd upload it.



I'm not planning on use OLE automation because its slow, and because my users are using everything from Excel 2000 to 2007.
Teddy Jensen
Teddy Jensen
StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)StrataFrame Novice (56 reputation)
Group: StrataFrame Users
Posts: 52, Visits: 8K
Maybe http://filehelpers.com/ could solve your problems.

I haven't used it yet, but i think it also have a MS Excel storage to import/export from.

/Teddy

Juan Carlos Pazos
Juan Carlos Pazos
StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)
Group: Forum Members
Posts: 144, Visits: 227
Hi Teddy

Looks promising, I will review this and if something can be done, I will do my best to add a sample for import.

Regards

Smile Everything is possible, just keep trying...

Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Thanks for the contribution, Juan!
Paul Chase
Paul Chase
StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)
Group: Forum Members
Posts: 414, Visits: 2.8K
Hi here is a quick rundown on importing excel to a business object use oledb jet provider,

To Import Excel Data to a strongly typed Business Object you first have to create a business object and map to the data source just like any other datasource. Here is a quicky on how to do it.

1) Create and set up your business object project to use oledb and use the connection string as follows. You can then map and build your strong typed business object. You can set whether or not your sheet has header etc in the connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\pathtoyourfile.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';

2) Add an Access Data Source item using the same connection string

DataLayer.DataSources.Add(New MicroFour.StrataFrame.Data.AccessDataSourceItem("ExcelImport", loConnectionString))

3) Fill your business object

Dim LcSheetName as String = "Sheet1"

Bo.FillDataTable(String.Format("Select * from [{0}]", LcSheetName))

Just like any other data source that we generate a business object for and using strong typing we need to know in advance abouth the fields datatypes etc.However sometimes you might want to import an excel sheet that you don't know about at design time.So you can simply use regular ADO and oledb to import to a datatable and then do with it as you want. Here is a really basic example.

Private Function ImportExcel(ByVal ExcelFilename As String, ByVal Header As Boolean) As DataTable

Dim HasHeader As String

'Set Header Value

If Header Then

HasHeader = "YES"

Else

HasHeader = "NO"

End If

Dim loConnectionString As String = String.Format( _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'", _

ExcelFilename,HasHeader)

Dim loconn As New OleDbConnection()

Dim locmd As New OleDbCommand()

Dim lodbschema As DataTable

Dim lcSheetName As String

Dim loDataAdapter As OleDbDataAdapter

Dim LoDataTable As New DataTable

'Set Connection String

loconn.ConnectionString = loConnectionString

'Open Connection

loconn.Open()

locmd.Connection = loconn

'Get Schema table and determine if there is more than one sheet

lodbschema = loconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

If lodbschema.Rows.Count > 1 Then

'call picklist of available sheets

lcSheetName = GetSheetNames(lodbschema)

Else

LcSheetName = lodbschema.Rows(0).Item("Table_Name")

End If

'Create Data Adapter

loDataAdapter = New OleDb.OleDbDataAdapter(String.Format("Select * from [{0}]", lcSheetName), loconn)

'Fill Table

loDataAdapter.Fill(LoDataTable)

'Close the Connection

loconn.Close()

'Dispose

loconn.Dispose()

locmd.Dispose()

lodbschema.Dispose()

loDataAdapter.Dispose()

'Return the Table

Return LoDataTable

Hope that helps and it all makes sense as I was kinda in a hurry and pulling snippets from my application.

Paul

Greg McGuffey
Greg McGuffey
Strategic Support Team Member (2.2K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
Awesome example Paul!
Juan Carlos Pazos
Juan Carlos Pazos
StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)StrataFrame User (146 reputation)
Group: Forum Members
Posts: 144, Visits: 227
Paul

Thanks for the idea, I want to try and add a sample of this (or if you have a sample). I have a problem with this:

GetSheetNames

I assume you create a function, can you tell me how this must be?

Regards

Smile Everything is possible, just keep trying...

Paul Chase
Paul Chase
StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)StrataFrame User (450 reputation)
Group: Forum Members
Posts: 414, Visits: 2.8K
Hi Juan,

That function would call a form with a drop down that is bound to the table_name property of the schema table. Actually in the production code that I am using I toggle the visibility of a combobox on and off on the import form. I was trying to simply pull out the pieces that were relevant to give a simple example. If I have a little spare time today I will see if I can make a sample.

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search