Example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setFillBackgroundColor

List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFillBackgroundColor

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCellStyle
  6. setFillBackgroundColor

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setFillBackgroundColor.

Prototype

@Override
public void setFillBackgroundColor(short bg)

Source Link

Document

Set the background fill color represented as a indexed color value.

Usage

From source file:achmad.rifai.admin.ui.Saver.java

private void title(int i, XSSFRow r1, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r1.createCell(i);
    c.setCellType(CellType.STRING);// w w  w. ja  v a  2s.c om
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.YELLOW));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void konten(int i, XSSFRow r, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r.createCell(i);
    c.setCellType(CellType.STRING);//from  w  w w.j a v a2 s  .  c o  m
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.WHITE));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setAlignment(HorizontalAlignment.JUSTIFY);
    cs.setVerticalAlignment(VerticalAlignment.TOP);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void konten1(int i, XSSFRow r, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r.createCell(i);
    c.setCellType(CellType.STRING);/*from  ww w.jav  a2  s.  co  m*/
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.WHITE));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void title2(int i, XSSFRow r1, String s, XSSFSheet sh) {
    sh.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 1, i, i));
    org.apache.poi.xssf.usermodel.XSSFCell c = r1.createCell(i);
    c.setCellType(CellType.STRING);/*  w ww.ja  va 2  s  .  c  o  m*/
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.YELLOW));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean createSpreadsheetTS(String pathSheet, String nameSheet, TestPlanTSBean testPlan)
        throws Exception {

    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from w w  w.  j  a va2  s  . c  o  m*/
    String sheetTI = pathSheet + "\\" + nameSheet;
    nameSheet = nameSheet.replace("xlsx", "xlsm");
    ;
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File("C:\\FastPlan\\sheets\\TS_NEW.xlsm");
    logger.info("Realizando cpia da planilha");
    boolean existInList = false;

    List<TesteCaseTSBean> testCasesAutomatizados = new ArrayList<TesteCaseTSBean>();

    //run macro
    String cmd = "C:\\FastPlan\\runMacro.vbs";
    Runtime.getRuntime().exec("cmd /c" + cmd);

    Thread.sleep(2000);

    copySheet(sourceStheet, destinationSheet);
    logger.info("Planilha copiada");
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);

    XSSFSheet sheetTS = workbook.getSheetAt(0);
    XSSFCellStyle estilo = workbook.createCellStyle();

    //        workbook.setSheetName(workbook.getSheetIndex(sheetTS), FunctiosDates.dateToString(FunctiosDates.getDateActual(), "yyyy-MM-dd-HH-mm-ss"));
    int linha = 2;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell release = row.getCell(1);
    Cell prj = row.getCell(2);
    Cell fase = row.getCell(3);
    Cell testPhase = row.getCell(4);
    Cell testScriptName = row.getCell(5);
    Cell testScriptDescription = row.getCell(6);
    Cell stepNo = row.getCell(7);
    Cell stepDescription = row.getCell(8);
    Cell expectedResults = row.getCell(9);
    Cell product = row.getCell(10);
    Cell dataPlanejada = row.getCell(11);
    Cell qtdSteps = row.getCell(12);
    Cell complexidade = row.getCell(13);
    Cell automatizado = row.getCell(14);

    logger.info("Inserindo dados do plano");
    descriptionPlan.setCellValue(testPlan.getName());
    release.setCellValue(testPlan.getRelease());

    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);

        logger.info("Inserindo dados dos TCs");
        System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - " + testPlan.getSti()
                + " - " + "row:" + linha);
        prj.setCellValue(testPlan.getSti());
        fase.setCellValue(testPlan.getCrFase());
        testPhase.setCellValue(testPlan.getTestPhase());
        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        qtdSteps.setCellValue(testPlan.getTestCase().get(i).getListStep().size());
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        //set colors 
        if (i % 2 == 0) {

            System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - entrou");
            //                setColorCells(new Cell[]{descriptionPlan, release, prj, fase, testPhase, testScriptName, testScriptDescription, stepNo,
            //                    stepDescription, expectedResults, product, dataPlanejada, qtdSteps, complexidade}, workbook);

            XSSFCellStyle styleColor = (XSSFCellStyle) product.getCellStyle();
            styleColor.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);
            product.setCellStyle(styleColor);

        }

        for (int j = 0; j < testPlan.getTestCase().get(i).getListStep().size(); j++) {

            row = sheetTS.getRow(linha);
            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);
            //                
            //                stepNo.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getNomeStep());
            logger.info("Inserindo dados dos Steps");
            stepNo.setCellValue(j + 1);
            stepDescription.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getDescStep());
            expectedResults.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getResultadoStep());

            linha = linha + 1;

            row = sheetTS.getRow(linha);

            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);

        }

        linha = linha + 1;
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);
        logger.info("Dados inseridos na planilha");

        if (testPlan.getTestCase().get(i).isAutomatizado()) {

            for (int j = 0; j < testCasesAutomatizados.size(); j++) {
                if (testPlan.getTestCase().get(i).equals(testCasesAutomatizados.get(j).getTestScriptName())) {
                    existInList = true;
                }
            }
            if (!existInList) {
                testCasesAutomatizados.add(testPlan.getTestCase().get(i));
                existInList = true;
            }
        }

    }

    ExtraiPlanilha extraiPlanilha = new ExtraiPlanilha();
    extraiPlanilha.exportTStoTI(testCasesAutomatizados, sheetTI);

    logger.info("Preparando para salvar planilha");
    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    logger.info("Fim mtodo - new FileOutputStream(destinationSheet) ");
    logger.info("Tentando gravar na planilha.");
    workbook.write(fileOut);
    logger.info("Fim mtodo - workbook.write(fileOut)");
    fileOut.close();
    fileSheet.close();
    sucess = true;
    logger.info("Planilha gerada.");
    return sucess;

}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public void setColorCells(Cell[] cells, XSSFWorkbook workbook) {
    XSSFCellStyle styleColor = workbook.createCellStyle();
    styleColor.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);

    for (int i = 0; i < cells.length; i++) {
        styleColor = (XSSFCellStyle) cells[i].getCellStyle();
        styleColor.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        cells[i].setCellStyle(styleColor);
    }/*from  w  w w . ja  v a 2 s.  com*/

}

From source file:com.svi.main.logic.ExtractAndPrint.java

private void writeLogFile(List<Nodes> nodesHolder) {
    DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm");
    Date date = new Date();
    Sheet mainSheet;/*  w w w .  ja v a  2 s.c  o  m*/
    Sheet dataSheet;
    Sheet elementSheet;
    Row dataSheetRow;
    Row elementSheetRow;
    InputStream fis;
    XSSFWorkbook workbook;
    File outputPath = new File(logPath + "\\Logs");
    File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name
    int dataSheetRowCount = 1;
    int elementSheetRowCount = 1;
    int totalElementException = 0;

    try {
        if (!Files.exists(outputPath.toPath())) {
            Files.createDirectories(outputPath.toPath());
        }
        fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx");
        workbook = new XSSFWorkbook(fis);

        //Style for exception sheet
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //Get data Sheet
        mainSheet = workbook.getSheetAt(0);
        writeProjectDetails(mainSheet);
        dataSheet = workbook.getSheetAt(4);
        dataSheetRow = dataSheet.createRow(0);

        elementSheet = workbook.getSheetAt(1);
        elementSheetRow = elementSheet.createRow(0);
        //Write excel headers
        writeDataSheetHeaders(dataSheetRow);
        writeElementSheetHeaders(elementSheetRow);

        //Set progress bar values
        progress = new AtomicInteger(0);
        total = new AtomicInteger(nodesHolder.size());
        mf.setJprogressValues(total, progress);
        // Sort the nodes per Node ID
        Collections.sort(nodesHolder, new Comparator<Nodes>() {
            public int compare(Nodes o1, Nodes o2) {
                return o1.getNodeId().compareTo(o2.getNodeId());
            }
        });
        //Write Data Sheet
        for (Nodes node : nodesHolder) {
            mf.loader();
            dataSheetRow = dataSheet.createRow(dataSheetRowCount++);
            writeDataSheet(node, dataSheetRow);
        }
        for (Nodes node : nodesHolder) {
            for (Elements e : node) {
                if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING"))
                        && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) {
                    totalElementException++;
                }
            }
        }
        progress = new AtomicInteger(0);
        total = new AtomicInteger(totalElementException);
        mf.setJprogressValues(total, progress);
        //Write exception sheet
        for (Nodes node : nodesHolder) {
            for (Elements e : node) {
                if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING"))
                        && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) {
                    elementSheetRow = elementSheet.createRow(elementSheetRowCount++);
                    writeElementSheet(e, elementSheetRow);
                    mf.elementLoader();
                }
            }
        }
        XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook);
        try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
            workbook.write(outputStream);//Write the Excel File
            outputStream.close();
        }
        workbook.close();
        fis.close();
        mf.generateMessage();
    } catch (Exception ex) {
        Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }// www  . j a va2  s  .  com
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:DSC.AccountantReport.java

private static void createExcelReport() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    clients.sort(new Comparator<Client>() {
        @Override/*from w w  w .  java 2  s.c o  m*/
        public int compare(Client o1, Client o2) {
            return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName());
        }
    });

    int lastIndex = 0;
    for (int letter = 0; letter < 26; letter++) {
        XSSFSheet sheet = workbook.createSheet("AccountReport " + (char) (65 + letter));
        Map<String, Object[]> data = new TreeMap<>();
        data.put("1", new Object[] { "Doorstep Chef Accountant Sheet", "", "", "", "", "", "",
                "Week: " + DriverReport.returnWeekInt(), "", "" });
        data.put("2", new Object[] { "", "", "", "", "", "", "", "", "", "" });
        data.put("3", new Object[] { "Customer", "Contact", "Fam", "4Day", "Mthly", "EFT", "Cash", "Date Paid",
                "Stay", "Comments" });

        int reduction = 0;
        for (int i = 0; i < clients.size(); i++) {
            Client client = clients.get(i);
            if (client.getSurname().toUpperCase().charAt(0) == (char) (65 + letter)) {
                data.put((i + 4 - reduction) + "",
                        new Object[] { client.getName() + " " + client.getSurname(),
                                client.getContactNumber().substring(0, 3) + " "
                                        + client.getContactNumber().substring(3, 6) + " "
                                        + client.getContactNumber().substring(6, 10),
                                client.getAdditionalInfo(), "", "", "", "", "", "", "" });
            } else {
                reduction++;
            }
        }

        Set<String> keySet = data.keySet();
        int totalSize = 34900;
        int longestCustomer = 0;

        for (int key = 1; key < keySet.size() + 1; key++) {
            Row row = sheet.createRow(key - 1);
            Object[] arr = data.get(key + "");
            for (int i = 0; i < arr.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue((String) arr[i]);

                if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) {
                    longestCustomer = ((String) arr[i]).length();
                }
                XSSFCellStyle borderStyle = workbook.createCellStyle();

                if (!((key + "").equals("1") || (key + "").equals("2"))) {
                    borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                    borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                    if ((key + "").equals("3")) {
                        borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                        borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS);
                        borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                        XSSFFont font = workbook.createFont();
                        font.setColor(IndexedColors.WHITE.getIndex());
                        font.setBold(true);
                        borderStyle.setFont(font);
                    } else {
                        if (i != 0) {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        if (i != 9) {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        }

                        if ((Integer.parseInt((key + ""))) != keySet.size()) {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
                        } else {
                            borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        }
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);

                    }
                } else {
                    if (i == 7) {
                        borderStyle.setAlignment(HorizontalAlignment.RIGHT);
                    }
                    XSSFFont font = workbook.createFont();
                    font.setFontName("Calibri");
                    font.setFontHeightInPoints((short) 13);
                    font.setBold(true);
                    borderStyle.setFont(font);
                }

                cell.setCellStyle(borderStyle);

            }
            if (key == 1) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));
            }

        }
        sheet.setColumnWidth(0, (longestCustomer + 1) * 240);
        sheet.setColumnWidth(1, 11 * 240);
        sheet.setColumnWidth(2, 5 * 240);
        sheet.setColumnWidth(3, 5 * 240);
        sheet.setColumnWidth(4, 5 * 240);
        sheet.setColumnWidth(5, 5 * 240);
        sheet.setColumnWidth(6, 5 * 240);
        sheet.setColumnWidth(7, 10 * 240);
        sheet.setColumnWidth(8, 5 * 240);
        for (int i = 0; i < 9; i++) {
            totalSize -= sheet.getColumnWidth(i);
        }
        sheet.setColumnWidth(9, totalSize);

        Row rowDate = sheet.createRow(keySet.size() + 1);
        Cell cell = rowDate.createCell(0);
        SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss");

        cell.setCellValue(sf.format(Calendar.getInstance().getTime()));
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 9));

    }

    try {
        workbook.write(excelOut);
        excelOut.close();
        System.out.println("Done - Accountant");
        if (!(DSC_Main.generateAllReports)) {
            accountLoadObj.setVisible(false);
            accountLoadObj.dispose();
            JOptionPane.showMessageDialog(null, "AccountReports Succesfully Generated", "Success",
                    JOptionPane.INFORMATION_MESSAGE);
        } else {
            DSC_Main.reportsDone++;
            if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) {
                DSC_Main.reportsDone();
            }
        }

    } catch (IOException io) {
        accountLoadObj.setVisible(false);
        accountLoadObj.dispose();
        JOptionPane.showMessageDialog(null, "An error occured\nCould not create AccountReport", "Error",
                JOptionPane.ERROR_MESSAGE);
        System.err.println("Error - Could not create new AccountReport: ");
        io.printStackTrace();
    }
}

From source file:DSC.ChefReport.java

public static void processChefReport() throws IOException {

    boolean firstFamEntry = true;
    String list[] = { "Standard", "Low Carb", "Kiddies" };
    int excelNumber = 0;
    int sheetNumber = 0;

    for (mealCounter = 0; mealCounter < 3; mealCounter++) {
        workbook = new XSSFWorkbook();
        sheetNumber = 0;//from ww w.  j  av a  2s .c  om
        for (String currRoute : allRoutes) {

            Map<String, Object[]> data = new TreeMap<>();
            data.put(0 + "", new String[] { "Doorstep Chef - Chef Report " + DriverReport.returnWeekInt(), "",
                    "", "Meal Type : " + list[mealCounter] + " " + " " + "Route: " + sheetNumber });
            data.put(1 + "", new String[] { "", "", "", "", "", "", "" });
            data.put(2 + "", new String[] { "Family Size", "Quantity", "Allergies", "Exclusions" });
            int counter = 3;
            XSSFSheet sheet = workbook.createSheet("ChefReports Route - " + sheetNumber);
            int rowNum = 0;
            int cellNum = 0;
            String familysize = "";

            ArrayList<Meal> mealList = new ArrayList<>();
            boolean hasValue = false;

            for (Order order : orders) {
                for (Meal meal : order.getMeals()) {
                    if (meal.getMealType().equals(list[mealCounter]) && order.getRoute().equals(currRoute)) {
                        mealList.add(meal);
                        hasValue = true;
                    }
                }
            }

            mealList.sort(new Comparator<Meal>() {
                @Override
                public int compare(Meal o1, Meal o2) {
                    if (o1.getQuantity() < o2.getQuantity()) {
                        return -1;
                    } else if (o1.getQuantity() > o2.getQuantity()) {
                        return 1;
                    } else {
                        return 0;
                    }
                }
            });

            if (hasValue) {
                int currQuantity = 0;
                int bulk = 0;
                boolean firstIterate = true;

                for (Meal meal : mealList) {

                    if (meal.getQuantity() != currQuantity) {

                        if (!firstIterate && bulk != 0) {
                            data.put(counter + "",
                                    new String[] { familysize + " Normal *", bulk + "", "", "" });
                            bulk = 0;
                            counter++;
                        }
                        firstIterate = false;

                        switch (meal.getQuantity()) {
                        case 1:
                            familysize = "Single";
                            break;
                        case 2:
                            familysize = "Couple";
                            break;
                        case 3:
                            familysize = "Three";
                            break;
                        case 4:
                            familysize = "Four";
                            break;
                        case 5:
                            familysize = "Five";
                            break;
                        case 6:
                            familysize = "Six";
                            break;
                        default:
                            familysize = "Extra";
                        }
                        currQuantity = meal.getQuantity();
                    }

                    if (meal.getAllergies().equals("-") && meal.getExclusions().equals("-")) {
                        bulk++;
                    } else {
                        data.put(counter + "", new String[] { familysize + " Meal", meal.getQuantity() + "",
                                meal.getAllergies(), meal.getExclusions() });
                        counter++;
                    }

                }
            }

            Set<String> keySet = data.keySet();
            Object[] keys = data.keySet().toArray();
            Arrays.sort(keys);
            ArrayList<Object> keyList = new ArrayList();
            int longestCustomer = 5;
            int totalWidth = 50000;
            boolean isBulk = false;

            for (Object key : keys) {
                keyList.add(data.get(key));
            }

            for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) {
                Row row = sheet.createRow(rowNum);
                Object[] arr = data.get(keyIterate + "");

                for (int i = 0; i < arr.length; i++) {
                    XSSFFont font = workbook.createFont();
                    Cell cell = row.createCell(i);
                    cell.setCellValue((String) arr[i]);
                    XSSFCellStyle borderStyle = workbook.createCellStyle();

                    if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) {

                        font.setFontName("Calibri");
                        font.setFontHeightInPoints((short) 13);
                        font.setBold(true);
                        borderStyle.setFont(font);
                        borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                        borderStyle.setAlignment(HorizontalAlignment.LEFT);

                    } else {
                        borderStyle.setBorderBottom(BorderStyle.THIN);
                        borderStyle.setBorderTop(BorderStyle.THIN);
                        borderStyle.setBorderLeft(BorderStyle.THIN);
                        borderStyle.setBorderRight(BorderStyle.THIN);
                        if ((arr[0] + "").contains("*")) {
                            isBulk = true;
                            borderStyle.setBorderBottom(BorderStyle.MEDIUM);
                        }
                    }
                    if ((keyIterate + "").equals("2")) {
                        borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
                        borderStyle.setAlignment(HorizontalAlignment.CENTER);
                        borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS);
                        borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                        XSSFFont font2 = workbook.createFont();
                        font2.setColor(IndexedColors.WHITE.getIndex());
                        borderStyle.setFont(font2);

                    }
                    cell.setCellStyle(borderStyle);

                }

                rowNum++;
                cellNum++;

            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

            for (int i = 0; i < 5; i++) {
                switch (i) {
                case 1:
                    sheet.setColumnWidth(i, 3000);
                    break;
                case 2:
                    sheet.setColumnWidth(i, 8000);
                    break;
                default:
                    sheet.setColumnWidth(i, 4000);
                    break;
                }

                if (i == 3) {
                    sheet.setColumnWidth(i, 8000);
                }

            }

            Row rowDate = sheet.createRow(keySet.size() + 1);
            Cell cell = rowDate.createCell(0);
            SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss");

            cell.setCellValue(sf.format(Calendar.getInstance().getTime()));
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 3));

            sheetNumber++;

            creatSheet(list[mealCounter], workbook);

            excelNumber++;
            if (excelNumber == allRoutes.size()) {
                if (!(DSC_Main.generateAllReports)) {
                    chefLoadingObj.setVisible(false);
                    chefLoadingObj.dispose();
                    JOptionPane.showMessageDialog(null, "Chef Reports Successfully Generated.");
                }
            }
        }
    }
}