Hi,
I have a code that generates a report using excel objects, I want to prepare the same report by using aspose objects instead of excel objects. Is it possible that I could simply replace the excel objects by aspose objects and the report would work fine or is there anything else as well that needs to be taken care off ???
please provide your valuable suggestions.
Hi there,
By Excel objects you probably mean VSTO (Office Automation). I am afraid, Aspose.Cells for .NET API has different structure, Namespaces, Classes and functions, therefore it is not possible to simply replace the Excel objects with Aspose.Cells objects and yet attain similar results. I would suggest you to go through the below linked topics for migration from VSTO to Aspose.Cells for .NET API.
http://www.aspose.com/docs/display/cellsnet/Migration+from+Microsoft+Office+Automation+to+Aspose
In case you face any difficulties, please provide us your sample template along with your VSTO source code, and we can try to achieve the same results with Aspose.Cells for .NET.
Hi,
Thanks for the quick response. I will try to do it as per your suggestion. Will let you know in case of any queries.
Thanks again.
Hi Varun,
Thank you for writing back.
I am afraid, I cannot simply convert your provided source code to Aspose.Cells equivalent, that is due to the database dependencies in your source code as you are currently referencing field names to import data from SQL Server to Workbook object. Although I can assist you to achieve the same. First of all, I would suggest you to go through the Programmer’s Guide in order to get acquainted with Aspose.Cells API. This exercise will also help you understand the features provided and limitations of the product.
Please note, Aspose.Cells API does not provide means to get connected with a database, instead you have to implement you own custom logic (for instance using ADO.NET) to pull the data from a database such as SQL Server. Once you have the data in a DataTable/DataSet, you can import it in your worksheet with ease. Rest of the procedures used in your provided source code are also easy to implement such as,
Please note, your provided source code exhibits the usage of pop-up alerts for some actions in VBA code. I am afraid, this cannot be achieved using Aspose.Cells API.
Thank you so much Babar for the valuable inputs. Will get back to you for some more suggestions. :-)
Thanks again.
Hi Babar,
To tell you more about this code, this is used to generate a report in a client software. Now, we have been suggested that if aspose could not establish SQL connection then we can leave that part of code as it is....which means that the software in which this code is being executed would take care of this and we only need to replace the excel objects with aspose objects.In this code a SQL connection is being established quite a lot of times, do you think its feasable that we leave the connection part as it is and replace the excel objects by aspose objects in the rest of the code. If yes, can you please provide an example in the same code, just to give an idea about how to do it.
Thanks in advance.
Hi Varun,
Thank you for writing back.
Could you please be specific for what portion of your requirement do you need the code snippet? Apart from making a connection to SQL database, you are performing many other operation, majorly to format the data on spreadsheet. I have already shared the documentation links for almost everything that you are doing in your provided source code sample. Also, I have already narrated that simply replacing the Excel object with Aspose objects will not help at all because both products have different architecture.
It would be of great help in providing further assistance with your project that you first segregate the operations involved to create a similar report using Aspose.Cells API. Based on individual modules, we can provide you the code snippets for your reference. If possible, please also execute your provided code snippet with your SQL database to create a final report, by looking at it we will get a precise knowledge of your requirements.
Hi Babar,
Thanks for your patience and cooperation.
let me try to explain this, actually in the above code we are preparing a report using excel objects. we want to prepare the same report using aspose objects instead of excel objects.Now, as you explained that aspose would not be able to establish sql connections so, as an alternative method we thought that is it possibble that we leave the code as it is ( the part wherever sql connection is being established) and try to replace the excel objects with aspose objects for the rest of the code. Now, for this case you told us that the two products have different architecture so we cannot just simply replace the objects. So, is there a way we can replace the excel objects with aspose objects, without effecting the normal code functionality?
Please find attached the code for your reference. I have highlighted (in between the star lines) the part used for establishing sql connection (just for an example), prior to that the code uses the excel objects and also after this code snippet. So, can we replace these with aspose objects? if yes, how. if no, why ?
thanks a lot for your assistance.
Regards,
Varun Kapoor
Hi Varun,
Thank you for writing back, and sorry for a bit delayed response.
You are missing a main point here, establishing the connection to SQL database is one part of your requirement, other is to fetch the required data for the report. Please note, Aspose.Cells API cannot fetch data but it can import data to worksheet cells. In your provided source code snippet, you are establishing the connection once but for report data, you are fetching the required fields as per need through out the code, mostly by using inline SQL statements and also by calling stored procedures. I must say that your current approach involves frequent database access and database fields are strictly bound to the Excel objects therefore it is impossible for me to provide you the migrated code that can produce similar report using Aspose.Cells API.
If you wish to keep the data fetching routines as it is then it will not be a problem but you have to alter your logic, that is; instead of binding database fields to your Excel objects directly, you have to first store the data in any container such as DataTable and then import the data to the worksheet by using any of the import methods that suits you best. Once you have the data in spreadsheet you can perform rest of the operations to display the report as per your requirement.
I would suggest you again to check the technical articles posted here for your kind reference. I believe all the operations involved to format the report have already been shared with you. In case you wish us to migrate your existing application then please check the details for Consulting Services offered by Aspose.
Hi Babar,
Thanks a lot for your assistance.
I have tried to start with "hello world" but somehow I guess I am not going correct with the syntax. I am writing this code in a software "socrates" and following is the code snippet.
@Dim strQty:S,tmpCostNum:N,tmpPriceNum:N,tmpPartNum:S,tmpCustPartNum:S,tmpLT:S
@dim j:N,tmpBrand:S
@dim tmpPC:N,tmpPercentMargin:N, tmpTotalPriceNum:N,tmpExtendedPriceNum:N,tmpExtendedCostNum:N
@dim tmpTotalPercentMargin:N, tmpTotalPC:N, tmpTotalCostNum:N,tmpQtyNum:N,tmpLPM:N,tmpItemNumber:S
@Dim tmpNLLoss:N, tmpLLoss:N, tmpCOL:N, tmpTOC:N, tmpList:N, tmpBTCStd:N, tmpBTCOpt:N, tmpStdPkgQty:N,tmpZPR0:N
@Dim locDate:S, locTime:S, locTDStamp:S, tmpLPMSource:S
@Assign tmpCostNum = '1'
@debug tmpCostNum
@Dim intResultsFound:N, intIndex:N, strSQL:S, i:N, loc_UserList:S
@Dim strPropID:S, strResp:S, strCreate:S, strDue:S, strInqID:S
@Dim strDateGiven:S, strSoldTo:S, strEndUser:S, strStatus:S, sDueTime:S, sPriority:S, sTmp:S
@Rem Dim oExcel:O, oWorkbook:O, oSheet:O
@Date locDate
@Time locTime
@Assign locTDStamp = 'Margin Rpt created ' & locTime & ', ' & locDate
@Assign tmpPriceNum = '2'
@debug tmpPriceNum
@If QuoteSearchLocation = 'Local'
@Assign ics_WildCard = '%'
@Else
@Assign ics_WildCard = '%25'
@Endif
@Rem Create a License object
@Assign license As License = New License()
@Rem Set the license of Aspose.Cells to avoid the evaluation
@Rem limitations
@Assign license.SetLicense("Aspose.Cells.lic")
@Rem Instantiate a Workbook object that represents Excel file.
@Assign wb As Workbook = New Workbook()
@Rem Note when you create a new workbook, a default worksheet
@Rem "Sheet1" is added (by default) to the workbook.
@Rem Access the first worksheet "Sheet1" in the boo
@Assign sheet As Worksheet = wb.Worksheets(0)
@Rem Access the "A1" cell in the sheet.
@Assign cell As Cell = sheet.Cells("A7")
@Rem Input the "Hello World!" text into the "A1" cell
@ Assign cell.PutValue("Hello World!")
@Rem Save the Excel file.
@Assign wb.Save("d:\MyBook.xls", SaveFormat.Excel97To2003)
Can you please have a look and guide me through this. "@Rem" are the commented lines of code.
Regards,
Varun Kapoor
Hi Varun,
Thank you for writing back.
I am afraid, I’m not aware of the software “socrates” nor its syntax. If you can provide the link to developer website and its documentation, I can look into it to provide further guidance in this regard.
Regarding the recently provided code snippet, are you able to get the output file after calling the Workbook.Save method?