Example usage for org.apache.poi.ss.usermodel Sheet createRow

List of usage examples for org.apache.poi.ss.usermodel Sheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet createRow.

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.griffinslogistics.excel.BDLGenerator.java

/**
 *
 * @param sheet/*from w ww.j  ava  2s . co  m*/
 * @param leftStyle
 * @param middleStyle
 * @param rightStyle
 * @param footerStyle
 * @return index of the last table row created
 */
private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle,
        CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) {
    Integer index = 18;
    Integer currentBookTitleIndex = 1;
    Set<Integer> rowsToSum = new HashSet<Integer>();
    Set<Integer> totalsToSum = new HashSet<Integer>();
    String cellMergeString;
    String cellFormula;

    try {
        int currentBookNumber = bookBoxModels.get(0).getBookNumber();

        for (int i = 0; i < bookBoxModels.size(); i++) {
            index++;

            BookBoxModel currentModel = bookBoxModels.get(i);

            Row row = sheet.createRow(index);

            if (currentBookNumber != currentModel.getBookNumber()) {
                for (int j = 2; j <= 4; j++) {
                    row.createCell(j).setCellStyle(footerStyle);
                }

                addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum);
                index++;
                currentBookTitleIndex++;

                row = sheet.createRow(index);
            }

            for (int j = 2; j <= 4; j++) {
                row.createCell(j).setCellStyle(middleStyle);
            }

            //Book Title Row
            Cell titleCell = row.createCell(1);

            cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1);

            sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));
            titleCell.setCellStyle(leftStyle);
            titleCell.setCellValue(currentModel.getTitle());

            Cell quantityCell = row.createCell(5);
            quantityCell.setCellStyle(middleStyle);
            quantityCell.setCellValue(Double.parseDouble(currentModel.getBooksCount().toString()));

            Cell boxCountCell = row.createCell(6);
            boxCountCell.setCellStyle(middleStyle);
            boxCountCell.setCellValue(Double.parseDouble(currentModel.getBoxesCount().toString()));

            Cell totalQuantityCell = row.createCell(7);
            totalQuantityCell.setCellStyle(rightStyle);
            cellFormula = String.format("F%s*G%s", index + 1, index + 1);
            totalQuantityCell.setCellFormula(cellFormula);

            Cell palettesCountCell = row.createCell(8);
            palettesCountCell.setCellStyle(rightStyle);

            // excel is not 0-based!
            rowsToSum.add(index + 1);

            currentBookNumber = currentModel.getBookNumber();
        }

        index++;
        Row row = sheet.createRow(index);

        for (int i = 2; i <= 4; i++) {
            row.createCell(i).setCellStyle(footerStyle);
        }

        addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum);

        //Total row
        index++;
        Row footerTotalRow = sheet.createRow(index);
        Cell footerTotalRowTitleCell = footerTotalRow.createCell(1);

        for (int i = 2; i <= 4; i++) {
            footerTotalRow.createCell(i).setCellStyle(footerStyle);
        }

        cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString));

        footerTotalRowTitleCell.setCellStyle(footerStyle);
        footerTotalRowTitleCell.setCellValue("Total");

        Cell footerTotalRowQuantityCell = footerTotalRow.createCell(5);
        footerTotalRowQuantityCell.setCellStyle(footerStyle);

        Cell footerTotalRowBoxCountCell = footerTotalRow.createCell(6);
        footerTotalRowBoxCountCell.setCellStyle(footerStyle);

        Cell footerTotalRowTotalQuantityCell = footerTotalRow.createCell(7);
        footerTotalRowTotalQuantityCell.setCellStyle(footerStyle);

        Cell footerTotalRowPalettesCountCell = footerTotalRow.createCell(8);
        footerTotalRowPalettesCountCell.setCellStyle(footerStyle);

        //build cell formulas
        StringBuilder totalBoxesCountformulaBuilder = new StringBuilder();
        StringBuilder totalBooksCountformulaBuilder = new StringBuilder();
        StringBuilder totalPaletsCountformulaBuilder = new StringBuilder();
        // Example: SUM(H22;H25;H28;H31;H34)
        totalBoxesCountformulaBuilder.append("SUM(");
        totalBooksCountformulaBuilder.append("SUM(");
        totalPaletsCountformulaBuilder.append("SUM(");

        for (Integer integer : totalsToSum) {
            totalBoxesCountformulaBuilder.append("G").append(integer).append(",");
            totalBooksCountformulaBuilder.append("H").append(integer).append(",");
            totalPaletsCountformulaBuilder.append("I").append(integer).append(",");
        }

        totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1);
        totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1);
        totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1);

        totalBoxesCountformulaBuilder.append(")");
        totalBooksCountformulaBuilder.append(")");
        totalPaletsCountformulaBuilder.append(")");

        footerTotalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString());
        footerTotalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString());
        footerTotalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString());

    } catch (Exception e) {
        e.printStackTrace();
    }
    return index;
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber,
        String deliveryAddress, String client) {
    String mergeString;//from  w ww  .jav  a2 s.c o m

    index += 2;
    Row transportNumberRow = sheet.createRow(index);
    Cell transportNumberCell = transportNumberRow.createCell(1);

    transportNumberCell.setCellValue("Num Tpt: " + packageNumber);
    transportNumberCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 2;
    Row addressLabelRow = sheet.createRow(index);
    Cell addressLabelCell = addressLabelRow.createCell(1);
    addressLabelCell.setCellValue("A livrer chez: ");
    addressLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row addressRow = sheet.createRow(index);
    Cell addressCell = addressRow.createCell(1);
    addressCell.setCellValue(deliveryAddress);
    addressCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 3;
    Row clientLabelRow = sheet.createRow(index);
    Cell clientLabelCell = clientLabelRow.createCell(1);
    clientLabelCell.setCellValue("Pour le compte des Editions: ");
    clientLabelCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 1;
    Row clientRow = sheet.createRow(index);
    Cell clientCell = clientRow.createCell(1);
    clientCell.setCellValue(client);
    clientCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 4;
    Row dateRow = sheet.createRow(index);
    Cell dateCell = dateRow.createCell(1);
    dateCell.setCellValue("Date: ................");
    dateCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    index += 5;
    Row signatureRow = sheet.createRow(index);
    Cell signatureCell = signatureRow.createCell(1);
    signatureCell.setCellValue("Signature et tampon: ...................");
    signatureCell.setCellStyle(footerStyle);
    mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
}

From source file:com.griffinslogistics.excel.BookLabelGenerator.java

public static void generateLabel(OutputStream outputStream, BookLabelModel bookLabelModel) {

    try {/*from  w  w  w  .  j ava 2 s  . c om*/
        XSSFWorkbook workbook = new XSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        String title = bookLabelModel.getTitle().replace("/", "-");
        bookLabelModel.setTitle(title);
        Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle());

        for (int i = 0; i < 20; i++) {
            Row row = sheet.createRow(i);
            if (i != 0 && i != 10) {
                row.setHeightInPoints(25);
            } else {
                row.setHeightInPoints(12);
            }
        }

        //column widths
        sheet.setColumnWidth(0, 5000);
        sheet.setColumnWidth(1, 10000);

        sheet.setColumnWidth(3, 5000);
        sheet.setColumnWidth(4, 10000);

        generateHeaders(sheet, styles);
        generateAddress(sheet, styles, bookLabelModel);
        generateClient(sheet, styles, bookLabelModel);
        generateTransportation(sheet, styles, bookLabelModel);
        generateTitle(sheet, styles, bookLabelModel);
        generateCountPerBox(sheet, styles, bookLabelModel);
        generateCountPerAddress(sheet, styles, bookLabelModel);

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(4);

        workbook.write(outputStream);
    } catch (IOException ex) {
        Logger.getLogger(BookLabelGenerator.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Return the session report as an Excel file
 * /*w w w  . java  2s.  co  m*/
 * @param sessionData
 * @return
 */
private ByteArrayOutputStream getSessionReportAsExcel(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData) throws AgileBaseException, IOException, SQLException {
    BaseReportInfo report = sessionData.getReport();
    if (report == null) {
        throw new ObjectNotFoundException("No report found in the session");
    }
    // create Excel spreadsheet
    Workbook workbook = new SXSSFWorkbook();
    // the pane 2 report
    String reportName = report.getReportName();
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    reportName = reportName.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    Sheet reportSheet;
    try {
        reportSheet = workbook.createSheet(reportName);
    } catch (IllegalArgumentException iaex) {
        reportSheet = workbook.createSheet(reportName + " " + report.getInternalReportName());
    }
    int rowNum = 0;
    // header
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Row row = reportSheet.createRow(rowNum);
    int columnNum = 0;
    Set<ReportFieldInfo> reportFields = report.getReportFields();
    for (ReportFieldInfo reportField : reportFields) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(reportField.getFieldName());
        cell.setCellStyle(boldCellStyle);
        BaseField field = reportField.getBaseField();
        if (field.equals(field.getTableContainingField().getPrimaryKey())) {
            reportSheet.setColumnHidden(columnNum, true);
        }
        columnNum++;
    }
    // data
    rowNum++;
    DataManagementInfo dataManagement = this.databaseDefn.getDataManagement();
    List<DataRowInfo> reportDataRows = dataManagement.getReportDataRows(company, report,
            sessionData.getReportFilterValues(), false, sessionData.getReportSorts(), -1, QuickFilterType.AND,
            false);
    String fieldValue = "";
    boolean defaultReport = (report.equals(report.getParentTable().getDefaultReport()));
    for (DataRowInfo dataRow : reportDataRows) {
        Map<BaseField, DataRowFieldInfo> dataRowFieldMap = dataRow.getDataRowFields();
        row = reportSheet.createRow(rowNum);
        columnNum = 0;
        for (ReportFieldInfo reportField : reportFields) {
            BaseField field = reportField.getBaseField();
            if (field instanceof TextField) {
                fieldValue = dataRowFieldMap.get(field).getKeyValue();
            } else {
                fieldValue = dataRowFieldMap.get(field).getDisplayValue();
            }
            if (!fieldValue.equals("")) {
                Cell cell;
                DatabaseFieldType dbFieldType = field.getDbType();
                if ((defaultReport) && (field instanceof RelationField)) {
                    dbFieldType = ((RelationField) field).getDisplayField().getDbType();
                }
                switch (dbFieldType) {
                case FLOAT:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Double.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                    }
                    break;
                case INTEGER:
                case SERIAL:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Integer.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        logger.debug(nfex.toString() + ": value " + fieldValue.replace(",", ""));
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                        logger.debug("Successfully set string instead");
                    }
                    break;
                case VARCHAR:
                default:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(Helpers.unencodeHtml(fieldValue));
                    break;
                }
            }
            columnNum++;
        }
        rowNum++;
    }
    // Export info worksheet
    addReportMetaDataWorksheet(company, user, sessionData, report, workbook);
    // one worksheet for each of the report summaries
    for (ChartInfo savedChart : report.getSavedCharts()) {
        this.addSummaryWorksheet(company, sessionData, savedChart, workbook);
    }
    // the default summary
    ChartInfo reportSummary = report.getChart();
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    if ((groupings.size() > 0) || (aggregateFunctions.size() > 0)) {
        this.addSummaryWorksheet(company, sessionData, reportSummary, workbook);
    }
    // write to output
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    workbook.write(outputStream);
    return outputStream;
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a sheet with export information to the workbook
 *///from   ww w .ja  v a 2 s. com
private static void addReportMetaDataWorksheet(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData, BaseReportInfo report, Workbook workbook) {
    String title = "Export information";
    Sheet infoSheet;
    try {
        infoSheet = workbook.createSheet(title);
    } catch (IllegalArgumentException iaex) {
        // Just in case there happens to be a report called 'Export
        // information'.
        // The sheet name must be unique
        infoSheet = workbook.createSheet(title + " " + report.getInternalReportName());
    }
    Row row = infoSheet.createRow(0);
    Cell cell = row.createCell(1);
    cell.setCellValue("Export from www.agilebase.co.uk");
    row = infoSheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue("Company");
    cell = row.createCell(1);
    cell.setCellValue(company.getCompanyName());
    row = infoSheet.createRow(3);
    cell = row.createCell(0);
    cell.setCellValue("Module");
    cell = row.createCell(1);
    ModuleInfo module = report.getModule();
    if (module != null) {
        cell.setCellValue(report.getModule().getModuleName());
    } else {
        cell.setCellValue("");
    }
    row = infoSheet.createRow(4);
    cell = row.createCell(0);
    cell.setCellValue("Report");
    cell = row.createCell(1);
    cell.setCellValue(report.getReportName());
    row = infoSheet.createRow(5);
    cell = row.createCell(0);
    cell.setCellValue("Exported by");
    cell = row.createCell(1);
    cell.setCellValue(user.getForename() + " " + user.getSurname());
    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    Date date = new Date();
    String now = dateFormat.format(date);
    row = infoSheet.createRow(6);
    cell = row.createCell(0);
    cell.setCellValue("Export time");
    cell = row.createCell(1);
    cell.setCellValue(now);
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a worksheet to the report for the specified workbook
 *//*from   www.  j a v a 2s  . co  m*/
private void addSummaryWorksheet(CompanyInfo company, SessionDataInfo sessionData, ChartInfo reportSummary,
        Workbook workbook) throws SQLException, CantDoThatException {
    ChartDataInfo reportSummaryData = this.databaseDefn.getDataManagement().getChartData(company, reportSummary,
            sessionData.getReportFilterValues(), false);
    if (reportSummaryData == null) {
        return;
    }
    int rowNum;
    Row row;
    Cell cell;
    int columnNum;
    String fieldValue;
    Sheet summarySheet;
    String summaryTitle = reportSummary.getTitle();
    if (summaryTitle == null) {
        summaryTitle = "Summary";
    } else if (summaryTitle.equals("")) {
        summaryTitle = "Summary";
    }
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    summaryTitle = summaryTitle.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    try {
        summarySheet = workbook.createSheet(summaryTitle);
    } catch (IllegalArgumentException iaex) {
        // sheet name must be unique
        summarySheet = workbook.createSheet(summaryTitle + " " + reportSummary.getId());
    }
    // header
    rowNum = 0;
    row = summarySheet.createRow(rowNum);
    columnNum = 0;
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    for (ChartGroupingInfo grouping : groupings) {
        BaseField groupingBaseField = grouping.getGroupingReportField().getBaseField();
        if (groupingBaseField instanceof RelationField) {
            fieldValue = groupingBaseField.getTableContainingField() + ": "
                    + ((RelationField) groupingBaseField).getDisplayField();
        } else {
            fieldValue = groupingBaseField.getFieldName();
        }
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
        fieldValue = aggregateFunction.toString();
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    List<ChartDataRowInfo> reportSummaryDataRows = reportSummaryData.getChartDataRows();
    rowNum++;
    for (ChartDataRowInfo summaryDataRow : reportSummaryDataRows) {
        row = summarySheet.createRow(rowNum);
        columnNum = 0;
        for (ChartGroupingInfo grouping : groupings) {
            fieldValue = summaryDataRow.getGroupingValue(grouping);
            row.createCell(columnNum).setCellValue(fieldValue);
            columnNum++;
        }
        for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
            Double number = summaryDataRow.getAggregateValue(aggregateFunction).doubleValue();
            row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC).setCellValue(number);
            columnNum++;
        }
        rowNum++;
    }
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/* w  w w .  ja v a  2 s  . c om*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

From source file:com.helger.genetic.tsp.result.evaluation.MainTSPRunnerBerlin52BestSeveral.java

License:Apache License

public static void main(final String[] args) throws IOException {
    GlobalDebug.setDebugModeDirect(true);
    final Matrix aDistances = readTSPFromFile(new ClassPathResource("tsp/berlin52.tsp"), true);

    final int nOptimumDistance = 7542;
    final int nCities = aDistances.getRowDimension();
    final TSPFitnessFunction ff = new TSPFitnessFunction(aDistances);
    final TSPChromosomeValidator cv = true ? null : new TSPChromosomeValidator(nCities);

    // Use fixed seed only once
    RandomGenerator.setRandomGenerator(new RandomGeneratorRandom(new Random(789234789989L)));

    final int nRepeats = 5;
    final List<TIntList> aDistanceListPerPopulations = new ArrayList<TIntList>(nRepeats);
    final List<TIntList> aDistanceListBest = new ArrayList<TIntList>(nRepeats);
    for (int i = 0; i < nRepeats; ++i) {
        final int nPopulationSize = nCities;
        final TSPEventHandlerGenerationTracker eh = new TSPEventHandlerGenerationTracker(ff);
        IContinuation cont = null;/*from www .  j  a v a2s.co  m*/
        cont = new ContinuationTotalGeneration(1000, cont);
        cont = new ContinuationKnownOptimum(ff.getFitness(nOptimumDistance), eh, cont);
        if (false)
            cont = new ContinuationTimeBased(20 * CGlobal.MILLISECONDS_PER_SECOND, cont);
        final IPopulationCreator pc = new TSPPopulationCreatorRandom(nCities, nPopulationSize, ff, cv);
        final ISelector s = new SelectorAllSortedBest(2);
        final ICrossover c = new CrossoverEdgeRecombination(new DecisionMakerPercentage(2));
        final IMutation m = new TSPMutationGreedyBeginning(aDistances, eh,
                new MutationRandomPartialReverse(new DecisionMakerPercentage(80)));

        new TSPRunner("berlin52").run(aDistances, nOptimumDistance, ff, eh, cont, pc, s, c, m);
        aDistanceListPerPopulations.add(eh.getDistanceListPerPopulation());
        aDistanceListBest.add(eh.getDistanceListBest());
    }

    final boolean bWithBest = false;
    final Workbook aWB = EExcelVersion.XLSX.createWorkbook();
    final Sheet aSheet = aWB.createSheet("STW CT");
    int nColumn = 0;
    Row aRow = aSheet.createRow(0);
    aRow.createCell(nColumn++).setCellValue("Generation");
    aRow.createCell(nColumn++).setCellValue("100%");
    aRow.createCell(nColumn++).setCellValue("110%");
    aRow.createCell(nColumn++).setCellValue("125%");
    for (int i = 0; i < nRepeats; ++i) {
        aRow.createCell(nColumn++).setCellValue("Run " + (i + 1));
        if (bWithBest)
            aRow.createCell(nColumn++).setCellValue("Best " + (i + 1));
    }

    // Ensure all rows are present
    int nMaxRows = 0;
    for (final TIntList aIL : aDistanceListPerPopulations)
        nMaxRows = Math.max(nMaxRows, aIL.size());

    // Create rows and set generation and optimum
    final double dOptimumDistance110 = nOptimumDistance * 1.1;
    final double dOptimumDistance125 = nOptimumDistance * 1.25;
    for (int i = 0; i < nMaxRows; ++i) {
        aRow = aSheet.createRow(1 + i);
        aRow.createCell(0).setCellValue(i + 1);
        aRow.createCell(1).setCellValue(nOptimumDistance);
        aRow.createCell(2).setCellValue(dOptimumDistance110);
        aRow.createCell(3).setCellValue(dOptimumDistance125);
    }

    // Set all values
    nColumn = 4;
    for (int i = 0; i < nRepeats; ++i) {
        int nRow = 1;
        final TIntIterator itPerPop = aDistanceListPerPopulations.get(i).iterator();
        while (itPerPop.hasNext())
            aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itPerPop.next());
        ++nColumn;

        if (bWithBest) {
            nRow = 1;
            final TIntIterator itBest = aDistanceListBest.get(i).iterator();
            while (itBest.hasNext())
                aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itBest.next());
            ++nColumn;
        }
    }
    aWB.write(FileHelper.getOutputStream(new File("data/berlin52/results-best-multiple.xlsx")));
}

From source file:com.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

@Test
public void testGetCellValueObject() {
    for (final EExcelVersion eVersion : EExcelVersion.values()) {
        final Workbook aWB = eVersion.createWorkbook();
        final Sheet aSheet = aWB.createSheet();
        final Row aRow = aSheet.createRow(0);
        final Cell aCell = aRow.createCell(0);

        // boolean
        aCell.setCellValue(true);//  w w w .ja  v  a 2s .  c o m
        assertEquals(Boolean.TRUE, ExcelReadUtils.getCellValueObject(aCell));

        // int
        aCell.setCellValue(4711);
        assertEquals(Integer.valueOf(4711), ExcelReadUtils.getCellValueObject(aCell));

        // long
        aCell.setCellValue(Long.MAX_VALUE);
        assertEquals(Long.valueOf(Long.MAX_VALUE), ExcelReadUtils.getCellValueObject(aCell));

        // double
        aCell.setCellValue(3.14159);
        assertEquals(Double.valueOf(3.14159), ExcelReadUtils.getCellValueObject(aCell));

        // String
        aCell.setCellValue("Anyhow");
        assertEquals("Anyhow", ExcelReadUtils.getCellValueObject(aCell));

        // Rich text string
        final Font aFont = aWB.createFont();
        aFont.setItalic(true);
        final RichTextString aRTS = eVersion.createRichText("Anyhow");
        aRTS.applyFont(1, 3, aFont);
        aCell.setCellValue(aRTS);
        assertEquals("Anyhow", ExcelReadUtils.getCellValueObject(aCell));
    }
}

From source file:com.hotaviano.tableexporter.xls.XLSExporter.java

License:Open Source License

@Override
public byte[] export(String htmlTable) throws DocumentException {
    Document document = createDocument(htmlTable);

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();

    CellStyle headerStyle = createHeaderStyle(wb);

    Element theadTr = document.getRootElement().getChild("thead").getChildren().get(0);
    Element tbody = document.getRootElement().getChild("tbody");

    createHeader(sheet.createRow(0), theadTr, headerStyle);
    createBody(sheet, tbody);/*from  ww w. j  a v a2s  .  c o m*/

    ByteArrayOutputStream out = new ByteArrayOutputStream();

    try {
        wb.write(out);
    } catch (IOException ex) {
        throw new DocumentException(ex);
    }

    return out.toByteArray();
}