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

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

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

Introduction

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

Prototype

int addMergedRegion(CellRangeAddress region);

Source Link

Document

Adds a merged region of cells (hence those cells form one)

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractSheetGenerator.java

License:Open Source License

/**
 * Creates the header for the specified sheet. 
 *///from w  ww .java2 s  .co  m
private void createSheetHeadline() {
    Sheet sheet = sheetContext.getSheet();
    sheet.createFreezePane(0, FIRST_DATA_ROW_NO);

    Cell sheetHeaderCell = sheet.createRow(0).createCell(0);
    sheetHeaderCell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.HEADER));
    sheetHeaderCell.setCellValue(createCompleteSheetName());
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // headline
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelTemplateGenerator.java

License:Open Source License

private void addSheetTitle(TimeseriesSheetContext sheetContext, Map<IteraExcelStyle, CellStyle> styles) {
    Sheet sheet = sheetContext.getSheet();
    String sheetTitle = MessageAccess.getStringOrNull(sheetContext.getTobb().getPluralValue()) + " - "
            + sheetContext.getAt().getName();

    Cell sheetHeaderCell = sheet.getRow(0).getCell(0);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    sheetHeaderCell.setCellStyle(styles.get(IteraExcelStyle.HEADER));
    sheetHeaderCell.setCellValue(sheetTitle);
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesIntroSheetGenerator.java

License:Open Source License

@Override
protected void createSummary() {
    Sheet introSheet = getIntroductionSheet();

    int rowNum = SUMMARY_ROW;
    Cell headerCell = introSheet.createRow(rowNum++).createCell(SUMMARY_COL);
    headerCell.setCellValue(MessageAccess.getStringOrNull("excel.export.timeseries.intro.description"));
    headerCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HEADER));
    introSheet.addMergedRegion(new CellRangeAddress(headerCell.getRowIndex(), headerCell.getRowIndex(),
            headerCell.getColumnIndex(), headerCell.getColumnIndex() + 1));

    CreationHelper createHelper = getWorkbook().getCreationHelper();

    // TODO group the summary by building block type with subheaders
    for (int i = 0; i < getWorkbook().getNumberOfSheets(); i++) {
        Sheet sheet = getWorkbook().getSheetAt(i);
        String sheetName = sheet.getSheetName();
        if (!introSheet.equals(sheet)) {
            Row summaryRow = introSheet.createRow(rowNum++);
            Cell hyperlinkCell = summaryRow.createCell(SUMMARY_COL);
            hyperlinkCell.setCellValue(sheetName);
            summaryRow.createCell(SUMMARY_COL + 1)
                    .setCellValue(ExcelUtils.getStringCellValue(sheet.getRow(0).getCell(0)));

            Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
            link.setAddress("'" + sheetName + "'!A1");
            hyperlinkCell.setHyperlink(link);
            hyperlinkCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HYPERLINK));
        }//from  w  w w. j  ava2 s.  co  m
    }

    adjustSheetColumnWidths();
}

From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java

License:Apache License

private void createTextualAnswers(@NonNull List<Vote> answers, @NonNull List<String> questionTexts, Sheet sheet,
        Workbook wb) {/*from  w w  w .j  av a 2s  .  c  om*/
    Row row;
    Cell cell;
    row = sheet.createRow(sheet.getLastRowNum() + 2);
    cell = row.createCell(1);
    CellStyle helpStyle = wb.createCellStyle();
    helpStyle.cloneStyleFrom(headerStyle);
    helpStyle.setBorderBottom(CellStyle.BORDER_NONE);
    helpStyle.setBorderTop(CellStyle.BORDER_NONE);
    helpStyle.setBorderLeft(CellStyle.BORDER_NONE);
    helpStyle.setBorderRight(CellStyle.BORDER_NONE);
    cell.setCellValue("Kommentare");
    cell.setCellStyle(helpStyle);

    //TODO used to determine style for current line -> its stupid. Think of something better
    int styleCounter = 0;
    for (String textualQuestion : questionTexts) {
        row = sheet.createRow(sheet.getLastRowNum() + 3);
        cell = row.createCell(1);
        cell.setCellValue(textualQuestion);
        setTextQuestionStyle(cell, styleCounter, true);

        //colorize horizontal neighbour cells of headline
        for (int i = 2; i < 5; i++) {
            cell = row.createCell(i);
            setTextQuestionStyle(cell, styleCounter, false);
        }

        int rowNum = sheet.getLastRowNum();
        int counter = 1;

        for (String comment : aggregateTextAnswers(answers, textualQuestion)) {
            row = sheet.createRow(rowNum + 1);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);

            //              introduces line breaks in long comments
            ArrayList<String> commentChunks = splitComment(comment);
            StringBuilder formattedComment = new StringBuilder();
            formattedComment.append(Integer.toString(counter));
            formattedComment.append(": ");

            int chunkCounter = 0;
            for (String chunk : commentChunks) {
                formattedComment.append(chunk);

                if ((chunkCounter + 1) < commentChunks.size()) {
                    formattedComment.append(System.lineSeparator());
                }
                chunkCounter++;
            }
            cell.setCellValue(formattedComment.toString());

            CellStyle style = setTextQuestionStyle(cell, styleCounter, false);

            // increase height of row based on font size, number of lines and line spacing
            // the origin of 140 % -> http://superuser.com/questions/337181/how-many-pts-is-1-5-line-spacing-in-microsoft-word-2007
            float pointsPerLine = (wb.getFontAt(style.getFontIndex()).getFontHeightInPoints() * 140) / 100;
            row.setHeightInPoints(pointsPerLine * commentChunks.size());

            //colorize horizontal neighbour cells of comment
            for (int i = 2; i < 17; i++) {
                cell = row.createCell(i);
                setTextQuestionStyle(cell, styleCounter, false);
            }
            sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 1, 17));

            rowNum++;
            counter++;
        }
        styleCounter++;
    }
}

From source file:demo.poi.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from w ww . j  a  v  a2 s.  c om*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "target/loan-calculator.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:demo.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from   w ww  .  j av  a2s. c o m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new HSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                // the 10th cell contains sum over week days, e.g.
                // SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "target/timesheet.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:Demos.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {//  w  ww  .j av  a 2 s  . c om
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

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

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

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        // create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

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

        // the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        // the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        // header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            // set column widths, the width is measured in units of 1/256th
            // of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); // the column is 5
            // characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); // the column is 13
            // characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    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 = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:document.ExcelDocument.java

protected void mergeCellsInRow(Sheet sheet, int rowIdx, int startCellIdx, int span) {
    sheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, startCellIdx, startCellIdx + span - 1));
}

From source file:Documentos.ClaseAlmacenGeneral.java

public void crearExcel() {
    try {/*from w ww  .  ja va2 s  . com*/

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true,
                true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idProducto, nombre, cantidad,UnidadExistencia,minStock  where tipoProducto=2 from producto");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                String minStock = String.valueOf(resultado.getInt("minStock"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + UnidadExistencia + " - " + minStock);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, UnidadExistencia);
                creandoCelda(wb, fila, 4, minStock);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:Documentos.ClaseAlmacenProducto.java

public void crearExcel() {
    try {//from w w w. j ava2 s.co m

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos existentes-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Precio de Venta", true,
                true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Ganancia", true, true);
        createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idproducto,nombre,format(cantidad,0) as cantidad,preciocompra,precioVenta,concat('$ ',format(((precioventa-preciocompra)*cantidad),2)) as ganancia, UnidadExistencia from producto where tipoProducto = 3;");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String precioVenta = String.valueOf(resultado.getString("precioVenta"));
                String ganancia = String.valueOf(resultado.getString("ganancia"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                //String Image = String.valueOf(resultado.getBlob("Image"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + precioVenta + " - " + ganancia + " - " + UnidadExistencia);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, precioVenta);
                creandoCelda(wb, fila, 4, ganancia);
                creandoCelda(wb, fila, 5, UnidadExistencia);
                //creandoCelda(wb, fila, 5, Image);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Productos" + ControllerFechas.getFechaActual()
                + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}