We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Difficulty with Designer worksheet

Please tell me how to do collect data from an MS-Access database and use it in a designer spreadsheet.

The code below is as far as I have got. It has at least three problems.

1. does it have to use the dataset - can it not use teh datareader?

2.This line shows an error - how do i convert it to a designer sheet?

"Worksheet.Cells.ImportFromDataReader(dataReader, True, 0, 0, True)"

3. the line "designer.Open("~/reports/monthlyrecon.xls")" produces teh error

Could not find a part of the path 'c:\windows\system32\inetsrv\~\reports\monthlyrecon.xls'.

Thank you for your help

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

'Instantiate an instance of license and set the license file through its path

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Cells.lic")

Dim myCommand As OleDbCommand

Dim dataReader As System.Data.OleDb.OleDbDataReader = Nothing

'Instantiating an WorkbookDesigner object

Dim designer As WorkbookDesigner = New WorkbookDesigner()

'Open a designer spreadsheet containing smart markers

designer.Open("~/reports/monthlyrecon.xls")

'Set the data source for the designer spreadsheet

designer.SetDataSource(DataSet)

'Process the smart markers

designer.Process()

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/fpdb/passliabcapture.mdb"))

connection.Open()

myCommand = New OleDbCommand("SELECT ID, Comment, Surname, RunDate, ActionDay, Description, Transtype, PremiumCollected, Rejecteddebit, Balance, fin_monthly0, fin_mnthtotalbrok, fin_pliab, fin_fun1, fin_ppa, EscapeFileNo FROM [datadump] Where [RunDate] >= @rdate and [RunDate] <= @enddate and [transtype] <> 'C'", connection)

myCommand.Parameters.Add(New OleDbParameter("@rdate", OleDbType.VarChar, 200))

myCommand.Parameters.Add(New OleDbParameter("@enddate", OleDbType.VarChar, 200))

myCommand.Parameters("@rdate").Value = CDate(Request.Form("From"))

myCommand.Parameters("@enddate").Value = CDate(Request.Form("enddate"))

'Importing the contents of the data reader to the worksheet

dataReader = myCommand.ExecuteReader()

Worksheet.Cells.ImportFromDataReader(dataReader, True, 0, 0, True)

'Saving the Excel file

designer.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, Response)

End Sub

1. does it have to use the dataset - can it not use teh datareader?

If you define smart markers in your template file, to process data with smart markers, you should use DataSet object.

If you don't use smart marker, you can use ImportDataReader method to import data from a datareader.

2.This line shows an error - how do i convert it to a designer sheet?

"Worksheet.Cells.ImportFromDataReader(dataReader, True, 0, 0, True)"

You should declar a Worksheet object and retrive it from WorkbookDesigner object.

3. the line "designer.Open("~/reports/monthlyrecon.xls")" produces teh error

Could not find a part of the path 'c:\windows\system32\inetsrv\~\reports\monthlyrecon.xls'.

You application is a web application and your current folder is "c:\windows\system32", A web application cannot access this folder.

I think you can try following code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

'Instantiate an instance of license and set the license file through its path

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Cells.lic")

Dim myCommand As OleDbCommand

Dim dataReader As System.Data.OleDb.OleDbDataReader = Nothing

'Instantiating an WorkbookDesigner object

Dim designer As WorkbookDesigner = New WorkbookDesigner()

'Open a designer spreadsheet containing smart markers

designer.Open(Server.MapPath("~/reports/monthlyrecon.xls")) ' please make sure the file is placed on the correct path

'Set the data source for the designer spreadsheet

designer.SetDataSource(DataSet)

'Process the smart markers

designer.Process()

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/fpdb/passliabcapture.mdb"))

connection.Open()

myCommand = New OleDbCommand("SELECT ID, Comment, Surname, RunDate, ActionDay, Description, Transtype, PremiumCollected, Rejecteddebit, Balance, fin_monthly0, fin_mnthtotalbrok, fin_pliab, fin_fun1, fin_ppa, EscapeFileNo FROM [datadump] Where [RunDate] >= @rdate and [RunDate] <= @enddate and [transtype] <> 'C'", connection)

myCommand.Parameters.Add(New OleDbParameter("@rdate", OleDbType.VarChar, 200))

myCommand.Parameters.Add(New OleDbParameter("@enddate", OleDbType.VarChar, 200))

myCommand.Parameters("@rdate").Value = CDate(Request.Form("From"))

myCommand.Parameters("@enddate").Value = CDate(Request.Form("enddate"))

'Importing the contents of the data reader to the worksheet

dataReader = myCommand.ExecuteReader()

Dim wks = designer.Workbook.Worksheets(1) ' You should use the correct sheet index. This is only for demo purpose.

wks.Cells.ImportFromDataReader(dataReader, True, 0, 0, True)

'Saving the Excel file

designer.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, Response)

End Sub

Thank you - it works great. A fantastic product