Example usage for org.apache.poi.ss.usermodel Sheet 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:Documentos.ClaseAlmacenXLS.java

public void crearExcel() {
    try {/*  w  w w  .  j  a  v  a2 s.  c o  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 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 from producto where tipoProducto=2 ");
            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:eleanalysis.SampleLibrary.java

/**
 * writes a report in the form of an excel spreadsheet that is exported.
 * Stage variable is for using a filechooser to pick where to save file.
 * @param myStage Stage is for saving file using FileChooser
 * @throws FileNotFoundException /*from   w w  w .  ja  va  2 s .c o m*/
 */
public void writeReport(Stage myStage) throws FileNotFoundException {
    FileChooser pickFile = new FileChooser();
    pickFile.setInitialDirectory(new File("C:\\Users\\Yan\\Documents\\NetBeansProjects\\EleAnalysis\\"));
    pickFile.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("XLS", "*.xls"),
            new FileChooser.ExtensionFilter("XLSX", "*.xlsx"));
    File writeFile = pickFile.showSaveDialog(myStage);
    FileOutputStream fileOut;
    fileOut = new FileOutputStream(writeFile);

    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("WDXRF");
    sheet1.setDefaultColumnWidth(15);
    // Create a cell space
    for (int i = 0; i < ElementUtils.skf.length + 5; i++) {
        Row tempR = sheet1.createRow(i);
        for (int j = 0; j <= array.size(); j++) {
            Cell tempC = tempR.createCell(j);
        }
    }
    CellStyle csCenter = wb.createCellStyle();
    csCenter.setAlignment(CellStyle.ALIGN_CENTER);
    csCenter.setBorderTop(CellStyle.BORDER_THIN);
    csCenter.setBorderLeft(CellStyle.BORDER_THIN);
    csCenter.setBorderRight(CellStyle.BORDER_THIN);
    csCenter.setBorderBottom(CellStyle.BORDER_THIN);
    CellStyle csRight = wb.createCellStyle();
    csRight.setAlignment(CellStyle.ALIGN_RIGHT);
    csRight.setBorderTop(CellStyle.BORDER_THIN);
    csRight.setBorderLeft(CellStyle.BORDER_THIN);
    csRight.setBorderRight(CellStyle.BORDER_THIN);
    csRight.setBorderBottom(CellStyle.BORDER_THIN);
    CellStyle csLeft = wb.createCellStyle();
    csLeft.setAlignment(CellStyle.ALIGN_LEFT);
    csLeft.setBorderTop(CellStyle.BORDER_THIN);
    csLeft.setBorderLeft(CellStyle.BORDER_THIN);
    csLeft.setBorderRight(CellStyle.BORDER_THIN);
    csLeft.setBorderBottom(CellStyle.BORDER_THIN);

    //Top Row
    sheet1.getRow(0).setHeightInPoints(25);
    sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, array.size()));
    sheet1.getRow(0).getCell(0).setCellValue("WDXRF Analysis");
    sheet1.getRow(0).getCell(0).setCellStyle(csCenter);
    sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, array.size()));

    //Second Row
    sheet1.getRow(1).getCell(0).setCellValue("Conc as Wt%");
    sheet1.getRow(1).getCell(0).setCellStyle(csCenter);

    // Third Row
    sheet1.getRow(2).setHeightInPoints(35);
    sheet1.getRow(2).getCell(0).setCellValue("Common Oxides/Oxication States");
    sheet1.getRow(2).getCell(0).setCellStyle(csLeft);
    for (int j = 1; j <= array.size(); j++) {
        sheet1.getRow(2).getCell(j).setCellStyle(csLeft);
        sheet1.getRow(2).getCell(j).setCellValue(array.get(j - 1).getName());
    }

    //Fourth Row
    sheet1.getRow(3).getCell(0).setCellValue("% Detectable");
    sheet1.getRow(3).getCell(0).setCellStyle(csLeft);
    for (int j = 1; j <= array.size(); j++) {
        sheet1.getRow(3).getCell(j).setCellValue("0.00");
        sheet1.getRow(3).getCell(j).setCellStyle(csLeft);
    }

    //Fifth Row
    sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, array.size()));
    sheet1.getRow(4).getCell(0).setCellValue("Results Normalized with Respect to Detectable Concentration");
    sheet1.getRow(4).getCell(0).setCellStyle(csCenter);

    //Rows 6 and beyond. Prints element list and defaults values to 0
    for (int i = 5; i < ElementUtils.skf.length + 5; i++) {
        sheet1.getRow(i).getCell(0).setCellValue(ElementUtils.skf[i - 5]);
        sheet1.getRow(i).getCell(0).setCellStyle(csLeft);
        for (int j = 1; j <= array.size(); j++) {
            sheet1.getRow(i).getCell(j).setCellValue("0.0");
            sheet1.getRow(i).getCell(j).setCellStyle(csRight);
        }
    }

    // Copies values in SampleLibrary array into report
    for (int i = 0; i < array.size(); i++) {
        List<Element> eleArray = array.get(i).getArrayCopy();
        for (int j = 0; j < ElementUtils.skf.length; j++) {
            for (int k = 0; k < eleArray.size(); k++) {

                if (ElementUtils.skf[j].contains(eleArray.get(k).getBaseElement()))
                    sheet1.getRow(j + 5).getCell(i + 1).setCellValue(eleArray.get(k).getConcWeight());
            }

        }
    }
    try {
        wb.write(fileOut);
        wb.close();
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(SampleLibrary.class.getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:Export.ExportMapaProducaoExcel__.java

public void CreateCell(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {

    c = r.createCell(linhaI);//from  ww  w  .  j av  a  2s  .c om
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

---

From source file:Export.GenericExcel.java

public static void createCellM(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {

    c = r.createCell(linhaI);//w ww. j av  a  2  s .c o m
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

---

From source file:Export.ListaVeiculo.java

public static void CreateCellM(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {
    c = r.createCell(linhaI);/*  w w  w.j av  a 2  s  . c  om*/
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

---

From source file:fi.thl.pivot.export.XlsxExporter.java

private void mergeTopLeftCorner(Sheet sheet, Pivot pivot) {
    sheet.addMergedRegion(
            new CellRangeAddress(0, pivot.getColumns().size() - 1, 0, pivot.getRows().size() - 1));
}

---

From source file:fi.thl.pivot.export.XlsxExporter.java

private void printCopyrightNotice(Sheet sheet, int initialRowNumber, Map<String, ?> params, int columns) {
    int rowNumber = initialRowNumber + 1;
    Boolean isOpenData = (Boolean) params.get("isOpenData");
    Cell c1 = sheet.createRow(++rowNumber).createCell(0);
    c1.setCellValue(//from ww w . j  a  va2 s  .  co  m
            String.format("%1$s %2$te.%2$tm.%2$tY", message("cube.updated", "date"), params.get("updated")));
    c1.setCellStyle(defaultStyle);
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, columns - 1));

    Cell c2 = sheet.createRow(++rowNumber).createCell(0);
    c2.setCellValue(String.format("(c) %s %d %s", message("site.company", "THL"),
            Calendar.getInstance().get(Calendar.YEAR),
            isOpenData != null && isOpenData ? ", " + message("site.license.dd", "CC BY 4.0") : ""));
    c2.setCellStyle(defaultStyle);
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, columns - 1));

}

---

From source file:fi.thl.pivot.export.XlsxExporter.java

@SuppressWarnings("unchecked")
private int printFilters(Map<String, ?> params, Sheet sheet, int initialRowNumber, int columns) {
    int rowNumber = initialRowNumber + 2;
    Row r = sheet.createRow(rowNumber);/*  w  ww  .ja  va  2s .c  o m*/
    Cell c1 = r.createCell(0);
    c1.setCellValue(message("cube.filter.selected", ""));
    c1.setCellStyle(defaultStyle);
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, columns - 1));

    for (Dimension d : (Collection<Dimension>) params.get("dimensions")) {
        for (IDimensionNode f : (Collection<IDimensionNode>) params.get("filters")) {
            if (f.getDimension().getId().equals(d.getId())) {
                Row filterRow = sheet.createRow(++rowNumber);
                Cell cell1 = filterRow.createCell(0);
                cell1.setCellStyle(defaultStyle);
                cell1.setCellValue(d.getLabel().getValue(language));

                Cell cell2 = filterRow.createCell(1);
                cell2.setCellStyle(defaultStyle);
                cell2.setCellValue(f.getLabel().getValue(language));
                sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 1, columns - 1));

            }
        }
    }

    return rowNumber;
}

---

From source file:fi.thl.pivot.export.XlsxExporter.java

private void mergeInColumn(Sheet sheet, int rowNumber, int firstColumnWithSameHeader,
        int lastColumnWithSameHeader) {
    if (lastColumnWithSameHeader - firstColumnWithSameHeader > 0) {
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, firstColumnWithSameHeader,
                lastColumnWithSameHeader));
    }//from w ww  .  j  a  v a 2 s. c  om
}

---

From source file:fi.thl.pivot.export.XlsxExporter.java

private void mergeInRow(Sheet sheet, int columnNumber, int firstRowWithSameHeader, int lastRowWithSameHeader) {
    if (lastRowWithSameHeader - firstRowWithSameHeader > 0) {
        sheet.addMergedRegion(new CellRangeAddress(firstRowWithSameHeader, lastRowWithSameHeader, columnNumber,
                columnNumber));// w ww  .  j av a 2s  . c o  m
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »