JAVA APACHE-POI tables

Question

Have this below class, which gonna be part of a loop, which will be adding many tables in the excel sheet.

My problem is, i cant figure out, how to make the table which is formatted as formatAsTable style to calculate the value in the totals row. I put this 2 lines of code:

cttable.setTotalsRowShown(true);
cttable.setTotalsRowCount(1);

Puts up the filters, but the formula is not there.I know i have string values, will change those later, but those arent the problem.Full code below, if anyone knows how to, please elaborate cause i dont know how.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Random;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

public class formatAsTable2
{
    public static void main(String[] args)
        throws FileNotFoundException, IOException, InvalidFormatException
    {
        String[][] multi = new String[][]{
            { "Advisor", "nonContactedUTD", "appointmentsUTD", "totalWorkLoad", "notResponsiveCalls", "successfulCalls", "totalCalls", "totalIncomingEmails", "totalCommunication", "emailsSent","offerLetters","appFees","deposits" },
            { "Sharon Brown", "42", "44", "86", "62", "27", "89", "21", "220", "131" , "0", "6", "2", "0" },
            { "Elena Soteriou", "40", "44", "NULL", "62", "27", "89", "21", "230", "131" , "0", "7", "2", "0" },
            { "Helen Christou","45", "44", "86", "62", "27", "89", "21", "210", "131" , "0", "8", "2", "0" },
            { "Maria Georgiou", "48", "44", "86", "NULL", "27", "89", "21", "240", "131" , "0", "45", "2", "0" }
    };
    //(indexes start from 0) areaStart should be added to arenaRow
    int rowStart = 1;   //From which row the table to start +1
    int columnStart = 1; // From which column the table to start first column value 0

    int len = multi.length;
    int wid = multi[0].length;

    int areaRow = len + rowStart; // how many rows the table has
    int areaColumn = wid + columnStart - 1; //how many columns the table has
                                            /* Start with Creating a workbook and worksheet object */

    InputStream inp = new FileInputStream("Excel_Format_As_Table.xlsx");

    XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(inp);
    XSSFSheet sheet = (XSSFSheet)wb.getSheetAt(0);

    /* Create an object of type XSSFTable */
    XSSFTable my_table = sheet.createTable();


    XSSFCellStyle my_style_1 = wb.createCellStyle();
    XSSFCellStyle my_style_0 = wb.createCellStyle();
    my_style_1.setAlignment(HorizontalAlignment.CENTER);
    my_style_1.setVerticalAlignment(VerticalAlignment.CENTER);

    XSSFFont my_font = wb.createFont();
    my_font.setBold(true);
    my_style_1.setFont(my_font);
    my_style_0.setFont(my_font);

    /* get CTTable object*/
    CTTable cttable = my_table.getCTTable();
    cttable.setTotalsRowShown(true);
    cttable.setTotalsRowCount(1);

    /* Let us define the required Style for the table */
    CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
    table_style.setName("TableStyleMedium27");

    /* Set Table Style Options */
    table_style.setShowColumnStripes(false); //showColumnStripes=0
    table_style.setShowRowStripes(true); //showRowStripes=1


                                         /* Define the data range including headers */
    AreaReference my_data_range = new AreaReference(new CellReference(rowStart, columnStart), new CellReference(areaRow, areaColumn));

    String randomCode = generateRandomChars();
    String wantedDisplayName = "MT_" + randomCode;
    String wantedName = "WN_" + randomCode;
    long id = 4L;



    java.util.List<XSSFTable> all_tables = sheet.getTables();

    for (XSSFTable a_table : all_tables) {

        if (wantedDisplayName.equals(a_table.getDisplayName())) wantedDisplayName += "_1";
        if (wantedName.equals(a_table.getName())) wantedName += "_1";
        if (a_table.getCTTable().getId() > id) id = a_table.getCTTable().getId();

        // System.out.println(wantedDisplayName);
        // System.out.println(wantedName);
        // System.out.println(id);
    }
    id++;
    cttable.setRef(my_data_range.formatAsString());
    cttable.setDisplayName(wantedDisplayName);      /* this is the display name of the table */
    cttable.setName(wantedName);    /* This maps to "displayName" attribute in <table>, OOXML */
    cttable.setId(id); //id attribute against table as long value
    cttable.addNewAutoFilter();

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(areaColumn); //define number of columns

                                  /* Define Header Information for the Table */
    for (int i = columnStart; i <= areaColumn; i++)
    {
        CTTableColumn column = columns.addNewTableColumn();

        column.setName("Column" + i);
        column.setId(i + 1);
    }
    int x = -1;
    int y = -1;

    /* Add remaining Table Data */

    XSSFRow rowDate = sheet.createRow(rowStart - 1);
    XSSFCell dateCell = rowDate.createCell(0);
    dateCell.setCellValue("Date");
    dateCell.setCellStyle(my_style_0);

    for (int i = rowStart; i<areaRow; i++) //we have to populate 4 rows
    {
        ++x;
        y = -1;
        /* Create a Row */
        XSSFRow row = sheet.createRow(i);
        for (int j = columnStart; j <= areaColumn; j++) //Three columns in each row
        {
            ++y;

            XSSFCell localXSSFCell = row.createCell(j);
            if (i == rowStart)
            {
                localXSSFCell.setCellValue(multi[x][y]);
            }
            else
            {
                localXSSFCell.setCellValue(multi[x][y]);
            }

        }
        XSSFRow rowx = sheet.createRow(i + 1);
        XSSFCell cellx = rowx.createCell(1);
        cellx.setCellValue("Total");
        //cellx = rowx.createCell(2);
        //cellx.setCellFormula("SUM(C3:C6)");



        cttable.setTotalsRowShown(true);
        cttable.setTotalsRowCount(1);

    }
    XSSFRow rowDateValue = sheet.getRow(rowStart);
    XSSFCell dateCellValue = rowDateValue.createCell(0);
    dateCellValue.setCellValue("24/02/2017");
    dateCellValue.setCellStyle(my_style_1);

    sheet.addMergedRegion(new CellRangeAddress(
        rowStart, // mention first row here
        multi.length + rowStart, //mention last row here, it is 4 as we are doing a row wise merging
        0, //mention first column of merging
        0  //mention last column to include in merge
    ));

    System.out.println("X" + x);
    System.out.println("y" + y);

    my_table.updateReferences();

    inp.close();
    /* Write output as File */
    FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
    wb.write(fileOut);
    fileOut.close();

    }
        public static String generateRandomChars() {

        String candidateChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
        int length = 17;

        StringBuilder sb = new StringBuilder();
        Random random = new Random();
        for (int i = 0; i < length; i++) {
            sb.append(candidateChars.charAt(random.nextInt(candidateChars
                .length())));
        }

        return sb.toString();
    }
}

Axel Richter · Accepted Answer · 2017-02-28 04:21:09Z

2

Main problem is, that you must set TotalsRowFunction for CTTableColumn as well as the cell formula into the totals row cells. If not set, then you can select the totals row function in Excel by clicking in a totals row cell and select the function from drop-down.

But before we can set the cell formulas into the totals row cells, we must fix multiple other issues with your code.

All code examples are changings or supplements to your provided code:

We should have table display name and table name equal. This is because the display name is used in Excel but apache poi's formula evaluator uses the name.

...
    id++;
    cttable.setRef(my_data_range.formatAsString());
    cttable.setDisplayName(wantedDisplayName);      /* this is the display name of the table */
    cttable.setName(wantedDisplayName);    /* This maps to "displayName" attribute in <table>, OOXML */
    cttable.setId(id); //id attribute against table as long value
    cttable.addNewAutoFilter();
...

We should name the columns not simply all "Column..." but name them using their correct names. This is because the apache poi formula evaluator needs the correct names for evaluating the structured reference formulas.

...
    /* Define Header Information for the Table */
    for (int i = columnStart; i <= areaColumn; i++)
    {
        CTTableColumn column = columns.addNewTableColumn();
        column.setName(multi[0][i-1]);
        column.setId(i + 1);
        column.setTotalsRowFunction(org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);
    }
...

Here the TotalsRowFunction is already set to SUM.

For using org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction this example needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in the FAQ-N10025.

Now we can set the cell formulas into the totals row cells as structured reference formulas.

...
        XSSFRow rowx = sheet.createRow(i + 1);
        XSSFCell cellx = rowx.createCell(1);
        cellx.setCellValue("Total");
        for (int j = columnStart+1 ; j <= areaColumn; j++) 
        {
            cellx = rowx.createCell(j);
            cellx.setCellFormula("SUBTOTAL(109," + wantedDisplayName + "[" + multi[0][j-1] + "])");
        }
...

Here SUBTOTAL(109,... is set for SUM.

edited Feb 28, 2017 at 4:21

answered Feb 27, 2017 at 16:19

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

George Georgiou Over a year ago

I tried to use your approach, although i finished this part of the code, by manually calculating the values from the array and adding just the value in the place of the Totals row. The problem : the path in my classpath NetBeans cant find the org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction for some reason. i have these 2 libraries with their all the jars.ooxml-schemas-3.16 beta2 and also tried the 3.7. None of them find it ,i can see the class in the Libraries as STTotalsRowFuntion$Enum.class.I would like to do it your way,but

Axel Richter Over a year ago

This example needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in the FAQ-N10025.

George Georgiou Over a year ago

I will add this jar and if it works i will try your code and replay back.

Collectives™ on Stack Overflow

JAVA APACHE-POI tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest