Received : 2007/09/18 04:03:51
Message : Hi Aspose.Cells team,
I am using the following code to generate an excel file (we have a template .xls file, we get the data depending on the search criteria from ther database in a datasource rs, now I need to fill the data in datasource to the excel work book) I am using the following code
set rs = conn.execute(sql)
'--- Create an instance of the ExcelTemplate object.
Dim License
Set License = CreateObject("Aspose.Cells.License")
License.SetLicense "C:\Program Files\Aspose\Aspose.Cells\License\Aspose.Total.lic"
Dim objSAXLTmplt
Set objSAXLTmplt = Server.CreateObject("ASPOSE.Cells.Workbook")
'--- Set the template's datasource to the Recordset
'--- returned from the database. DataSource("Recordset")
'--- refers to the recordset specified by the template's
'--- data markers (%%=Recordset.ColumnName).
objSAXLTmplt.DataSource("oRsData") = rs
but when I execute the code, I get the following error;
Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'DataSource'
/ExcelWriter/rp_ReportMeetingPlanningStatus.asp, line 287
Please hepl me out.
How can I fill the cells in the excelsheet from a record set or a datasource?
Since you are using smart markers in your template file. So you should create smart markers in the proper format like "&=Recordset.ColumnName" in your template file. And 'DataSource' is not the member of Workbook class. Could you try to change your coding lines i.e.,
Dim objSAXLTmplt Set objSAXLTmplt = Server.CreateObject("ASPOSE.Cells.WorkbookDesigner") .
Well your smart markers in the template file should be in format like &=DataSourceName.FieldName.
Which version Aspose.Cells you have installed on your system. please make sure that Aspose.Cells.dll is properly registered on your machine. Normally the component gets registered when you do install the component using its MSI installer.
JUSt to add the information that I am not using smart markers, i am using data markers (I am not aware if both are different). JUSt in case this information might help in finding the solution.
Also, the dll is also registered as during the inxstallation i didnt get any errors.
I guess there is a problem with the use of syntax.
Can you please tell me wherecan i get the syntax information for ASPOSE.Cells for ASP pages usage.Else please let me know seeinmg the code I am using if there is anything I can change to make it work.
Well, Aspose.Cells component needs smart markers for processing. The smart marker is normally starts with "&=" characters.
I have attached an asp sample project for you. The attached zip file contains four files:
1. Template file "mbook1.xls"........This is a template file in which i input a smart marker "&=$LastName" in Column B in the first worksheet. This smart marker is basically a Multi-valued variable used to store multiple values like array of data. Using this smart marker "&=$LastName", I will store the complete LastName field values in the Employees table of the Northwind database.
2. filldata.asp file..........This is an asp file. I used vbscript code, you may check it thoroughly for reference.
3. Northwind1.mdb access database file.......... This is the source database which is refered in code.
4. output generated file "outbook2.xls".......This is output file generated file. You may check that Column B is filled with LastName field data. There are total 9 records in this field in the Employees table.
Procedure:
1. To run the project, please put filldata.asp and Northwind1.mdb files to c:\InetPub\wwwroot\ folder.
2. put the template file "mbook1.xls" to d:\test\ folder (Either you may create a folder "test" on d:\> or adjust the code accordingly. )
3. open the browser and write the url: `http://localhost/filldata.asp` and enter
Hopefully the project will give you some insight how to use smart markers based on some database tables in asp.
I tried to use the files that you have sent and it works fine. But there is a problem, when the file 'outbook.xls' get generated, it is not able to recognise the format of the file. When I open the file I don't get the data in normal format.
I doesn't recognise .xls extension.
Please let me know what can be the reason for this.
where myTempfile is a variable defined which contains my file name, like myTempfile = abc.xls
But when I run the code it generates the file with file name as 'myTempfile' and file format as unknown. Please let me know how can I refer my file name which is generate dynamically in the above code.
1).when the file ‘outbook.xls’ get generated, it is not able to recognize the format of the file. When I open the file I don’t get the data in normal format.
I doesn’t recognize .xls extension.
What do you mean by that… you mean when you double click the generated file “outbook2.xls”, the file does not open in MS Excel by default and you have to right click on the file and specify Open With option to open the file with the program i.e. MS Excel which will be used to open the file. Well, It should recognize the .xls extension by default. Could you post your file here.
2).
Please change your code to:
Response.AddHeader “content-disposition”,“attachment; filename=myTempfile.xls” …For your info,
Since your code uses some data source and template file for implementation.
What happens when you click the Open button in the Dowload Dialog? Does the output file open fine in MS Excel etc with the related data or you have give specify the target product to open it. What happens when you click Save button to save the file? Does the file get saved properly in the Microsoft Excel Worbook file format or you have to explicitly give "Myfilename.xls" in the Save As dialog box. And tell me what is the end result of all these steps, does the saved file opens fine with related data in MS Excel or other Viewer.
Could you please post your saved excel file here.
And by the way, What hapens when you try my project (without changing any line of code)? It works fine or do you find the problem.
When I open the file, it gives me data in unreadable format.
Also, when I save the file, it gets saved in MS Excel format. I don't have MS Excel installed because I will be using ASPOSE.Cells in place of it.
But after saving the file when I try to open it, it gets opened in wordpad and agian in unreadable format.I am attaching the template file which I am using and the file which gets generatd, I will send it in next attachmnet.
SAme things happen when i am using your code, without changing a single letter.
Well, Aspose.Cells neither needs MS Excel to be installed to implement its functionality nor it uses MS Excel's APIs as it has its own independent sets of APIs to generate spreadsheet reports. Aspose.Cells is a pure spreadsheet generation library used to read/write or generate native excel files with .xls extension normally. The component does not require MS Excel to be installed either on the client side or on the server side to perform its functionality like generating spreadsheet reports with formatted data. But you do require MS Excel or Excel Viewer or any other product to open the generated excel files created by Aspose.Cells library for viewing.
So, Kindly install MS Excel (97-2007) to view the reports generated by the component. I checked your template file "rp_ReportMeetingPlanningStatus_template.xls", opening it into MS Excel, it contains markers in the fomrat like "%%=oRsData.projectname". I think these markers are data markers supported by SoftArtisans's ExcelWriter product. Kindly change those markers to Aspose's Aspose.Cells standards as I suggested you earlier. Aspose.Cells only supports smart markers in the fomrat like "&=DataSourceName.FieldName"......the smart marker starts with "&=" characters.
So, I think this is the root cause of your problem. Kindly do what I suggest you, you may create Aspose.Cells supported smart markers and write your code accordingly to process the smart markers. You may consult my project first and create your own then.
As per the last conversation, my application does not require MS Excel. Infact I need to replace MS Excel by ASPOSE.Cells and hence, I am using ASPOSE. Also, I have re-installed ASPOSE.Cells.
Now, when I try to run only your sample code, then also while generating the file it doesn't open the spreadsheet in a readable format. Infact it is not able to recognize .xls format is what I think.
As per your saying that my file use %%=oRsdata.projectname and that is the root cause. So, I tried running your file but still it doesn't recognize the file in .xls format. So, I installed MS Excel and run your code again. And this time it recognises .xls format file and also opens up in a readable format with the desired data.
After, that I also, changes the formula in my excel file form %%= to &=, but yet again the same result.
Now, I think that the solution that suggested previously is not quite sufficient or happening.
In short, I am right now testing ASPOSE.Cells with your code, which is running fine whne MS Excel is installed and goes down when MS Excel is un-installed. in both the cases ASPOSE.Cells remain installed.
Can you please let me know what could be the root cause for the same. As, there is not problems with the installation with ASPOSE.Cells as it was installed with a success message.
After, that I also, changes the formula in my excel file form %%= to &=, but yet again the same result. Now, I think that the solution that suggested previously is not quite sufficient or happening.
Well, I think you should also adjust your .asp code accordingly (take help from my code based on source template's smart markers ....please use Mutli valued variable i.e. &=$MyField1 which i used in template file.) apart from replacing %%= to &=( you may also check my source template that explains how to create smart markers).
In short, I am right now testing ASPOSE.Cells with your code, which is running fine whne MS Excel is installed and goes down when MS Excel is un-installed. in both the cases ASPOSE.Cells remain installed.
As I said earlier, you do need MS Excel or some other viewer to view .xls file created by Aspose.Cells. If you do not have MS Excel installed, you may save the .xls file but cannot view it. And if you view any .xls file in notepad or wordpad, it won't be readable, actaully it's (.xls file) only formatted for .xls file viewer like MS Excel. And Windows will take it as unknown format until you install MS Excel on the machine.
Amjad, I said that ASPOSE.Cells is not at all recognising .xls files, irrespective of the formulaes used in the file Is this possible??
As per your saying, you think that my application requires MS Excel to read the data in .xls format though the generation of file might be because of aSPOSE.Cells.
This I can justify saying that the sample code you have sent is atleast not dependent on Excel writer and the file generated using this code is also not readable.