package org.keyboardplaying.xtt.xlsx;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTimeConstants;
import org.joda.time.LocalDate;
import org.junit.Test;

/**
 * Prototype
 *
 * @author Cyrille Chopelet (https://keyboardplaying.org)
 */
public class XlsxBuilderPrototype {

    private static final int NB_HEADER_ROWS = 2;

    private static final int COLUMN_MONTH = 20;
    private static final int COLUMN_DAY = 21;

    @Test
    @SuppressWarnings("javadoc")
    public void generateWorkbook() {
        try (InputStream in = getClass().getResourceAsStream("tracker-dev.xlsx");
                XSSFWorkbook wb = new XSSFWorkbook(in)) {
            final int dayRow = NB_HEADER_ROWS;
            final int monthRow = dayRow + 1;
            final int startRow = monthRow + 1;

            XSSFSheet sheet = wb.getSheet("Timesheet");
            int currentYear = 2016;
            LocalDate dt = new LocalDate(currentYear, DateTimeConstants.JANUARY, 1);

            int r = startRow;
            final CellCopyPolicy policy = new CellCopyPolicy.Builder().build();
            while (dt.year().get() == currentYear) {
                /* Create rows */
                r = createMonth(sheet, dt, dayRow, monthRow, r, policy);
                dt = dt.plusMonths(1);
            }

            File out = new File("tracker-test.xlsx");
            wb.write(new FileOutputStream(out));
            wb.close();

            System.out.println(out.getAbsolutePath());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private int createMonth(XSSFSheet sheet, LocalDate dt, final int dayRow, final int monthRow, int row,
            final CellCopyPolicy policy) {
        int r = row;

        final int nbDays = dt.dayOfMonth().getMaximumValue();
        final int month = dt.monthOfYear().get();

        /* Insert days for the month */
        for (int day = 1; day <= nbDays; day++) {
            XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
            inserted.getCell(COLUMN_MONTH).setCellValue(month);
            inserted.getCell(COLUMN_DAY).setCellValue(day);
            r++;
        }

        /* Insert month summary */
        XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
        inserted.getCell(COLUMN_MONTH).setCellValue(month);
        r++;

        /* Group month rows */
        int groupRow = r - 2;
        sheet.groupRow(row, groupRow);
        sheet.setRowGroupCollapsed(groupRow, true);

        return r;
    }

    private XSSFRow copyRow(XSSFSheet sheet, int srcRow, int destRow, final CellCopyPolicy policy) {
        sheet.copyRows(srcRow, srcRow + 1, destRow, policy);
        return sheet.getRow(destRow);
    }
}

@@ -113,16 +113,67 @@
 tStream;
+%0Aimport java.util.ArrayList;%0Aimport java.util.List;
 %0A%0Aimport
@@ -217,16 +217,119 @@
 Policy;%0A
+import org.apache.poi.ss.usermodel.ConditionalFormatting;%0Aimport org.apache.poi.ss.util.CellReference;%0A
 import o
@@ -414,16 +414,85 @@
 FSheet;%0A
+import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;%0A
 import o
@@ -843,18 +843,58 @@
 MONTH = 
-20
+CellReference.convertColStringToIndex(%22U%22)
 ;%0A    pr
@@ -933,10 +933,246 @@
 Y = 
-21
+CellReference.convertColStringToIndex(%22V%22);%0A    private static final int COLUMN_TECHNICAL_FIRST = CellReference.convertColStringToIndex(%22U%22);%0A    private static final int COLUMN_TECHNICAL_LAST = CellReference.convertColStringToIndex(%22AB%22)
 ;%0A%0A 
@@ -1585,16 +1585,525 @@
 sheet%22);
+%0A%0A            /* Save and remove conditional formatting */%0A            List%3CConditionalFormatting%3E formattingRules = new ArrayList%3C%3E();%0A            XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();%0A            for (int i = formatting.getNumConditionalFormattings() - 1; i %3E= 0; i--) %7B%0A                formattingRules.add(formatting.getConditionalFormattingAt(i));%0A                formatting.removeConditionalFormatting(i);%0A            %7D%0A%0A            /* Prepare tracker rows */
 %0A       
@@ -2216,17 +2216,16 @@
 RY, 1);%0A
-%0A
         
@@ -2532,32 +2532,214 @@
 %0A            %7D%0A%0A
+            /* TODO Remove two first rows */%0A%0A            /* TODO Apply conditional formatting */%0A%0A            /* TODO Hide technical columns */%0A%0A            /* Write output file */%0A
             File

Prepare the CF by saving it before editing the sheet