Difficulty with Designer worksheet

jonathanm5 · February 9, 2008, 2:52pm

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

Laurence · February 9, 2008, 11:14pm

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

jonathanm5 · February 11, 2008, 2:17pm

Thank you - it works great. A fantastic product

hofika3 · May 18, 2023, 7:40am

To collect data from an MS-Access database and use it in a designer spreadsheet, you can make the following modifications to your code:

Replace the data reader with a dataset: Instead of using a data reader, you can fill a dataset with the data from your database query. Modify your code as follows:

vbCopy code

Dim dataSet As New DataSet()
Dim adapter As New OleDbDataAdapter(myCommand)
adapter.Fill(dataSet)

Import data to the worksheet: To import data from the dataset to the worksheet, you can use the ImportDataTable method instead of ImportFromDataReader. Modify your code as follows:

vbCopy code

Dim dataTable As DataTable = dataSet.Tables(0)
Worksheet.Cells.ImportDataTable(dataTable, True, 0, 0, True)

Update the path of the designer spreadsheet: The error you’re encountering suggests that the path to the designer spreadsheet is incorrect. Make sure you provide the correct canopies wall path to the designer spreadsheet on your server. You can provide the full physical path instead of the relative path:

vbCopy code

designer.Open("C:\path\to\monthlyrecon.xls")

Alternatively, you can use the Server.MapPath method to get the physical path based on a virtual path:

vbCopy code

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

Make sure the path is correct and the file exists at that location.

Here’s the modified code with the changes mentioned above:

vbCopy 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 dataSet As New DataSet()

    'Instantiating a WorkbookDesigner object
    Dim designer As WorkbookDesigner = New WorkbookDesigner()

    'Open a designer spreadsheet containing smart markers
    designer.Open(Server.MapPath("~/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"))

    'Filling the dataset with data from the database
    Dim adapter As New OleDbDataAdapter(myCommand)
    adapter.Fill(dataSet)

    'Importing the contents of the dataset to the worksheet
    Dim dataTable As DataTable = dataSet.Tables(0)
    Worksheet.Cells.ImportDataTable(dataTable, True, 0, 0, True)

    'Saving the Excel file
    designer.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, Response)
End Sub

Make

amjad.sahi · May 18, 2023, 8:15am

@hofika3,

Is there anything we can do to assist you regarding Aspose.Cells APIs, kindly provide us details and samples? Please feel free to write us if you find any issue or want technical assistance when using Aspose.Cells APIs.