I’m struggling to find an elegant solution on how to get the text from the excel file. For Aspose.Pdf, this is pretty straightforward, but for Aspose.Cells it seems it is very cumbersome. Would you please provide an example how I could read the text of the file?
However, I can’t be creating additional temp files - in this case, is my chance going through all the cells in all worksheets in the workbook and adding them to something like a string builder? If so, is there an example for this please?
I believe, Amjad’s suggested solution is more elegant and simple. Using that approach, you can write all contents of a worksheet to a single CSV or Text file while specifying the desired delimiter. Moreover, the data will be organized in rows. However, if you still wish to iterate all cells and write data to an instance of StringBuilder, you may try the following approach.
Please note, code shared here is for demonstration purposes so you should amend it according to your application requirements.
Java
StringBuilder stringBuilder = new StringBuilder(); Workbook book = new Workbook(dir + “book1.xlsx”); Worksheet sheet = book.getWorksheets().get(0); //retrieve first worksheet from collection Cells cells = sheet.getCells(); Range displayRange = cells.getMaxDisplayRange(); for(int row = displayRange.getFirstRow(); row<displayRange.getRowCount(); row++) { for(int col = displayRange.getFirstColumn(); col<displayRange.getColumnCount(); col++) { stringBuilder.append(displayRange.get(row, col).getStringValue()); stringBuilder.append(" "); } } System.out.println(stringBuilder.toString());
Please note, if the requirement is to process all cells in a Worksheet or a range, it is recommended to use Enumerator (Iterator for Java) instead of accessing individual cells in a for loop. This is because the APIs such as MaxRow, MaxDataRow, MaxColumn, MaxDataColumn & MaxDisplayRange require extra time to gather the corresponding statistics. Secondly, commonly not all cells in a range are instantiated. In such situations to check every cell in the matrix is not so efficient. Moreover, accessing a cell in a loop as Cells[row, column] (Cells.get(row, column) for Java) will cause all cell objects in a range to be instantiated, which may eventually cause OutOfMemoryException.
Please check the following updated code segment that achieves the same results more efficiently as compared to the previously shared code snippet.
Well, I think you may try to export data (in Excel spreadsheet) to fill an array (see the document on how to export data to an array via Aspose.Cells for Java APIs: Import and Export Data|Documentation) and then pass this Object[][] directly to JTable constructor.
Both Aspose.Cells for Java and Apache POI are different APIs and diverse with different architectures so you cannot parse one’s object into other directly.
Well, Cells.exportArray() method would export an object array for single worksheet cells only, so you have to write your own code and got to loop through each worksheet to export the arrays. Moreover, you have to export the array based on the complete dataset in the worksheet, you may update the third and fourth parameters in the method accordingly.
I want to write the data in excel file after comparing two different excel files I have stored the data in ArrayList and the data is also writing in third file but the proble here arises that they are not setting in rows and column from starting…If I tried to increment the col then data is stored diagonally otherwise in one col only…how can I put the data in all rows and col like excel file dynamically.
Well, Aspose.Cells only provides feasibility to either retrieve the data cell by cell in iterator from the sheet or export to fill an array/array list. To import data into worksheets, the component also provides some useful methods to import data from array, array list of resultset, etc., see the document with examples for your reference:
The methods may place data horizontally or vertically in the cells.
I am afraid, to insert specific data or record (from array/array list) at your desired place in the worksheet cells, you got to browse through the array/array list elements by yourselves and place it one by one accordingly for your custom needs, there is no automatic way to cope with it.
@Amjad_Sahi Hello, I would like to ask. Is there any documentation if I get the excel file from user by using httprequest post to an API, how I could get the directory of the path file that will be used for below code:
These forums are specific to Aspose APIs only. Well, your query is out of context and you should browse internet to accomplish the task by yourself as you have to write your own code to send a file as http request post. For example, see the following threads (external) for your reference: