.Cells Newbie


#1

I am evaluating the aspose.cell dll. I am trying to use the designer spreadsheets/smart markers to fill an excel spreadsheet with values from a database. For some reason I cannot get this to work .. my references to the field names in the database on the spreadsheet return nothing. However, I can get a variable to show up in the spreadsheet. I know my dataset is not empty as I have tested it and it does contain values. Can anyone point me in the right direction?? I am referencing the fields in my spreadsheet with &=ds.DOCUMENT_NUMBER

Dim ds As DataSet = CreateDataSource()
Dim designer As WorkbookDesigner = New WorkbookDesigner()
Dim path As String = MapPath(".")

path = path.Substring(0, path.LastIndexOf("\"))

Dim designerFile As String = path + "\Designer\test.xls"

designer.Open(designerFile)

designer.SetDataSource(ds)

designer.Process()
designer.Save("test.xls", SaveType.OpenInExcel, FileFormatType.Default, Me.Response)

Private Function CreateDataSource() As DataSet

Dim ds As DataSet = New DataSet()

Dim SQLDbConnection1 As SqlConnection = New SqlConnection()
Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf("\"))
SQLDbConnection1.ConnectionString = "Data Source=SYSTEMS35;Initial Catalog=L903_XP_TRANS;User ID=22;PWD=3333;"


Dim SQLDbDataAdapter1 As SqlDataAdapter = New SqlDataAdapter()
Dim SQLDbSelectCommand1 as SqlCommand = New SqlCommand()

SQLDbSelectCommand1.Connection = SQLDbConnection1
SQLDbDataAdapter1.SelectCommand = SQLDbSelectCommand1
SQLDbSelectCommand1.CommandText = "Select * from ub_m where document_number='0000248860'"

SQLDbConnection1.Open()

SQLDbDataAdapter1.Fill(ds)

SQLDbConnection1.Close()
Return ds
End Function


#2

&=ds.DOCUMENT_NUMBER

In above smart maker, ds is the DataTable name and DOCUMENT_NUMBER is field name. And they are case sensitive. So please check if there is a DataTable named as "ds" and if there is a field named as "DOCUMENT_NUMBER".


#3

Do I need to declare this data table name anywhere? I noticed in the demo that there was a line of code like this :

oleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Order Details", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Discount", "Discount"), New System.Data.Common.DataColumnMapping("OrderID", "OrderID"), New System.Data.Common.DataColumnMapping("ProductID", "ProductID"), New System.Data.Common.DataColumnMapping("Quantity", "Quantity"), New System.Data.Common.DataColumnMapping("UnitPrice", "UnitPrice")})})

Do I need to do this?


#4

Ok, i used that table mapping statement and got the document number to show up .. however .. i have over 100 fields/columns in the database I am using. I need to pull from all of these. Do I need to create a column mapping statement for all of them???


Thanks so much for your help


#5

Figured it out .. thanks!