Excel column distribution behavior during loop process, is it possible?

Question

Using Apache POI 3.9, I want to map some values to an Excel template that looks similar to this:

Looks pretty easy, at least for the Seller table in which I can map the values from my bean without problems.

However, the issue I am facing is related to the Products table because I need to set the data from my bean for each column which makes the logic a little bit complex since during the loop process I'll need to look for the next column letter:

e.g. My first product data will be set under B7 then at C7, D7, E7, etc until the loop ends.

(Just to know, for this example template I am just showing the "Name" attribute of the product but each one has around 35 attributes in real life, that's why I am not showing the data in rows since the table won't look so friendly to the user in horizontal view).

So, my question is:

What happen If my products count is more than the total letters of the alphabet, how can I get the right column and cell during the loop process to set my product bean data following Excel column distribution?

With "Excel column distribution" I mean the following:

e.g. In Excel, when going to the column which contains the last letter of the alphabet "Z" then the columns continue showing AA, AB, AC, etc.

Is it possible?

This is what I've tried (using dummy data) and this will work until getting into the letter "Z" column:

Empty Excel template used in this code snippet can be downloaded at: https://www.dropbox.com/s/eo0s54o9vkqhlbl/template.xls

package com.app.test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;


/**
 * Test class for generating Excel sheet
 */
public class ExportTest
{
    public static void main(String[] args) throws IOException
    {
        CellReference ref;

        // Load template
        InputStream fis = ExportTest.class.getResourceAsStream("/template.xls");
        Workbook workbook = new HSSFWorkbook(fis);
        fis.close();

        // Constants
        final String TBL_FIRSTCOLUMN = "B"; // Starting product table column at sheet (in which the first product data will be set)
        final int MAX_PRODUCTS = 25; // Max. products added to the dummy products list (this will set the last product to the "Z" column)
        final int TBL_STARTROW = 7; // Starting product table row number at sheet (in which the first product data will be set)
        final int TBL_ATTR_ROWS = 1; // Number of attribute rows at products table (in this case just "Name")

        // Generate dummy data with seller information
        LinkedHashMap<String, String> cellMap = new LinkedHashMap<String, String>();
        cellMap.put("B2", "1");
        cellMap.put("B3", "Company");
        cellMap.put("B4", "US");

        // Generate dummy data with product information
        List<String> products = new ArrayList<String>();
        for(int i = 0; i < MAX_PRODUCTS; ++i) {
            products.add("Chocolate");
        }

        // Declare style for cells
        CellStyle style = workbook.createCellStyle();
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        // Get template sheet
        Sheet sheet = workbook.getSheetAt(0);

        // Set values to "Seller" table
        for(Map.Entry<String, String> entry : cellMap.entrySet()) {
            ref = new CellReference(entry.getKey());
            sheet.getRow(ref.getRow()).getCell(ref.getCol()).setCellValue(entry.getValue());
        }

        // Set values to "Products" table
        for(int i = 0; i < products.size(); ++i) {

            // Get product name
            String name = products.get(i);
            String num = String.valueOf(i + 1);

            // Get char representation of the letter, this will allow me to get
            // C, D, E...Z columns but then I will get a IllegalArgumentException
            // if my products count exceed the alphabet letters. At this point I'll 
            // need to follow Excel column distribution behavior.
            String nextLetter = String.valueOf((char)(TBL_FIRSTCOLUMN.charAt(0) + i));

            for(int j = 0; j < TBL_ATTR_ROWS; ++j) {
                // Get cell reference of B7 then C7, etc
                ref = new CellReference(nextLetter + (TBL_STARTROW + j));

                // Check if row/cell exists, otherwise it will throw NullPointerException when trying to get each one
                Row row = sheet.getRow(ref.getRow());
                if(row == null) {
                    row = sheet.createRow(ref.getRow());
                }
                Cell cell = row.getCell(ref.getCol());
                if(cell == null) {
                    cell = row.createCell(ref.getCol());
                }

                // Set value and style to cell
                cell.setCellValue(name + num);
                cell.setCellStyle(style);
            }
        }

        // Write workbook to file
        String path = String.format("%s%s%s", System.getProperty("user.home"), System.getProperty("file.separator"), "exported.xls");
        OutputStream out = new FileOutputStream(new File(path));
        workbook.write(out);
        out.close();
    }
}

Then, If the products count exceeds the alphabet letters I will get the following exception:

Exception in thread "main" java.lang.IllegalArgumentException: Invalid column index (-11). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') at org.apache.poi.hssf.usermodel.HSSFCell.checkBounds(HSSFCell.java:939) at org.apache.poi.hssf.usermodel.HSSFCell.(HSSFCell.java:153) at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:148) at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:126) at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:39) at com.app.test.ExportTest.main(ExportTest.java:99)

(To replicate this try using MAX_PRODUCTS = 26, NOT 27 - alphabet count - since we are starting at B column on Excel sheet).

Any help will be appreciated, thanks!

Answer 1

You call a utility method in Apache POI -- CellReference.convertNumToColString to map your column number into an Excel column designation.

Takes in a 0-based base-10 column and returns a ALPHA-26 representation. eg column #3 -> D

Since you're starting with column "B" (1), add 1 to i first.

String nextLetter = String.valueOf(CellReference.convertNumToColString(i + 1));