Excel Processing in Java using Aspose.Cells

In this blog we will see the introduction to a wonderful library (Aspose.Cells) that allows us to perform every operation with excel that we do manually. This library is available in Java and .Net. Here, we will be using the Java version of this library, although concept remains same in .Net, its just the syntax that changes. The content below will help users to choose between the open source Apache POI and Aspose.Cells (which comes in both free and paid versions).

Before jumping to Aspose.Cells lets see a concept called Named Ranges in excel which we will use further in our code.

What are Named Ranges?

In simple terms, Named Ranges represent a group of cells with one name, where a group can be as small as a single cell and as big as a complete worksheet in excel.

As shown in below pic, we have created a named range “test_named_range” that represents a group of cells from D4 to F7. This range can be used to tell the program a place in the excel sheet where it needs to perform some operation.

What is Aspose.Cells?

Aspose.Cells for Java is a class library that enables your applications to perform a great range of spreadsheet processing tasks. Aspose.Cells for Java supports the popular spreadsheet (XLS, XLSX, XLSM, XLSB, XLTX, XLTM, CSV, SpreadsheetML, ODS) file formats your business use every day. It also allows exporting Excel files to PDF, XPS, HTML, MHTML, Plain Text and popular image formats including TIFF, JPG, PNG, BMP and SVG.

A detailed guide can be found here.

Lets learn this library in Action

Suppose we have an excel file that contains a table as shown in the pic below and this table cells are also represented by some named range, whose name we do not know, then how can we perform some operation to increase the number of rows in this table?

Similarly, there can be multiple excel files available with thousands of named ranges and we want to perform this operation only on our table present in those excel files.

For this to happen let us consider that all the named ranges in any excel file is the id of the question present in our database. So a mapping is available in our DB to tell us that which id is a table and which id is a normal question like “Company Name”.

In this excel we have a table that represents the list of 11 employees of a company along with their details. We some how discovered that we need the data to be increased to any value (say 15) at run-time. All we have is the InputStream of that file and we know that the table is represented by some named range. Now let us see some code that does the same operation.

Workbook is the object that represents the excel file.

Workbook workbook = new Workbook(inputStreamOfFile);
// inputStreamOfFile is the input stream we have to pass inside this object to get a excel workbook object in return

A fresh Workbook can also be created (multiple types of constructors are available):

Workbook workbook = new Workbook();

WorksheetCollection represents the collection of all the worksheet inside that workbook.

WorksheetCollection worksheets = workbook.getWorksheets();

Individual worksheet is represented by Worksheet object.

Worksheet worksheet = worksheets.get(1);

Lets first see how we can get an array that contains all the named ranges inside a workbook.

Range[] namedRanges = worksheets.getNamedRanges();

Range Object represents that range provided by a Named Range.

Range range = worksheets.getRangeByName("XYZ_named_Range");

We can get the name of the range by:

String name = namedRanges[0].getName();

We can get all the cells in a worksheet by:

Cells cells = worksheet.getCells();

Now lets increase the number of rows in the sheet.

Here we have passed a range object that represents the named range and the total number of rows that we want. We are using a serialNumberCounter to set the serial number correctly starting from second row, that’s why its value is 2

private Range increaseNoOfRows(Range tableRange, int totalRows) {
    
    Worksheet worksheet = tableRange.getWorksheet();
    Cells cells = worksheet.getCells();

    int serialNumberCounter = 2;
    int secondRowNumber = tableRange.getFirstRow() + 1;
    worksheet.getCells().insertRows(secondRowNumber, totalRows - tableRange.getRowCount());
    Row firstRow = worksheet.getCells()
            .getRows()
            .getRowByIndex(tableRange.getFirstRow());

    while (serialNumberCounter < totalRows + 1) {
        Row row = worksheet.getCells()
                .getRows()
                .getRowByIndex(secondRowNumber);
        Iterator<Cell> currentRowIterator = row.iterator();
        Iterator<Cell> firstRowIterator = firstRow.iterator();
        while (currentRowIterator.hasNext()
                && firstRowIterator.hasNext()
                && serialNumberCounter < totalRows) {
            Cell cell = worksheet.getCells()
                    .checkCell(secondRowNumber
                            , tableRange.getFirstColumn() - 1);
            cell.setValue(serialNumberCounter);
            serialNumberCounter++;
            secondRowNumber++;
        }

        Range newRange = cells.createRange(tableRange.getFirstRow()
                , tableRange.getFirstColumn(), totalRows
                , tableRange.getColumnCount());
        newRange.setName(tableRange.getName());

        return newRange;
    }
}

We are setting the name of the new range back to the original name of range to override the existing range with new scope.

So our code has increased the table rows to 15 from 11.

Auto Fit Excel Rows and hide extra rows

Now lets see how can we auto fit rows so that the data inside cells can be visible nicely instead of getting overlapped. Its the same that we do when we format cell data in excel. Along with this we will also see how to hide existing hidden rows in excel even if data is there (using a boolean variable isIgnored and setting its value to true) and also hide useless infinite rows available in excel.

public void formatWorkbook(Workbook workbook, boolean isIgnored) {
    WorksheetCollection worksheets = workbook.getWorksheets();
    AutoFitterOptions options = new AutoFitterOptions();
    options.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE);
    options.setIgnoreHidden(isIgnored);
    Iterator<Worksheet> worksheetIterator = worksheets.iterator();
    while (worksheetIterator.hasNext()) {
        try {
            Worksheet worksheet = worksheetIterator.next();
            worksheet.autoFitRows(options);
        } catch (Exception e) {
            log.error("Error in Formatting rows");
            throw new CoiDataException("Error in Formatting rows");
        }
    }
    workbook.calculateFormula();
}

Get Cells Iterator of a Range

Using Aspose.Cells we get an iterator that helps us iterate the cells that are a part of a particular named range.

public Iterator<Cell> getIterator(String nameOfRange, NameCollection namedRangesCollection) {
    Name name = namedRangesCollection.get(nameOfRange);
    Range range = name.getRange();
    return range.iterator();
}

Enter Some Data in Excel using named range

Once we have access to the iterator, we can also enter values in the cells that are a part of a particular named range.

Iterator<Cell> i = getIterator(range, namedRangesCollection);
int counter = 1;
if (i.hasNext()) {
    Cell cell = i.next();
    cell.setValue(counter);
}

Two Ways to Set Value in a Cell

There are two ways we can enter value in a cell.

1- cell.setValue();

2- cell.putValue();

setValue() will always set the value as a string while putValue() will set the value of the excel cell to a type (boolean, date etc.) you will pass in its parameters.

Convert Excel to PDF (Or Any Other Format) using Aspose.Cells

We can also convert the excel and get a PDF InputStreamResource in return. Aspose.Cells has simplified it a lot by providing us an enum class FileFormatType.

A list of possible convertible formats can be found here.

We can use the below function to get InputStreamResouce of any format type and generate a file from it.

public InputStreamResource streamResource(Workbook workbook
                                    , FileFormatType fileFormatType) {
    
  try (ByteArrayOutputStream outputStream = new 
                                          ByteArrayOutputStream()) {
       workbook.save(outputStream, fileFormatType);
       ByteArrayInputStream byteInputStream = new 
                   ByteArrayInputStream(outputStream.toByteArray());
        
       InputStreamResource inputStreamSource = new 
                               InputStreamResource(byteInputStream);
        
       byteInputStream.close();
       return inputStreamSource;
    } catch (Exception e) {
        throw new RuntimeException("Exception occurred in when 
                                   converting workbook to stream ");
    }
}

How to hide worksheets

We can simply hide a worksheet by using the below code

workbook.getWorksheets().get(0).setVisible(false);

Conclusion

Aspose.Cells has a lot to offer to its users. Whatever operation we can think of doing in excel, we can do the same using Aspose.Cells. This blog has covered a simpler introduction for developers who are planning to use this library as a part of their projects so that they can simplify their work. Do let us know in the comments whether you like this library and what are your views about it.

Have a nice Day!