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:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();
    XSSFWorkbook wb = contexto.getWb();/*from w w w .  ja  va2s. c  o m*/
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Hojalteria y pintura");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalHojalateria", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();
    XSSFWorkbook wb = contexto.getWb();/*from   w  ww.j  ava  2s .  com*/
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Mecanica");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroMecanica()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalMecanica", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.DatosAutoReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();
    XSSFWorkbook wb = contexto.getWb();/*from  w w  w . j ava  2s. c o m*/
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    //escribir el primer renglon
    Row row = getRow(sheet, initialRow);
    Cell cell = row.createCell(initialColumn);
    cell.setCellValue("Auto");
    //estilo .-.
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < encabezados.length; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 7 //last column  (0-based)
    ));
    //segundo renglon encabezado
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    for (int i = 0; i < encabezados.length; i++) {
        cell = row.createCell(initialColumn + i);
        cell.setCellValue(encabezados[i]);
        cellStyle = wb.createCellStyle();
        addHeaderStyle(cellStyle, wb);
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    //tercer renglon encabezado
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    for (int i = 0; i < atributos.length; i++) {
        cell = row.createCell(initialColumn + i);
        try {
            cell.setCellValue(PropertyUtils.getProperty(datos, "auto." + atributos[i]).toString());
        } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException ex) {
            cell.setCellValue("");
        }
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }

    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 7);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, r);
    return r;
}

From source file:com.tremolosecurity.scale.ui.reports.GenerateSpreadsheet.java

License:Apache License

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

    resp.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
    resp.setHeader("Pragma", "no-cache");

    ReportViewer scaleReport = (ReportViewer) req.getSession().getAttribute("scaleReportCached");

    Workbook wb = new XSSFWorkbook();

    Font font = wb.createFont();/*from www .ja  v a  2s . c  o m*/
    font.setBold(true);

    Font titleFont = wb.createFont();
    titleFont.setBold(true);
    titleFont.setFontHeightInPoints((short) 16);

    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(scaleReport.getReportInfo().getName()));

    //Create a header
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);

    RichTextString title = new XSSFRichTextString(scaleReport.getReportInfo().getName());
    title.applyFont(titleFont);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

    cell.setCellValue(title);

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getReportInfo().getDescription());

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    row = sheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getRunDateTime());

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

    row = sheet.createRow(3);

    int rowNum = 4;

    if (scaleReport.getResults().getGrouping().isEmpty()) {
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("There is no data for this report");
    } else {

        for (ReportGrouping group : scaleReport.getResults().getGrouping()) {
            for (String colHeader : scaleReport.getResults().getHeaderFields()) {
                row = sheet.createRow(rowNum);
                cell = row.createCell(0);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);

                cell.setCellValue(rcolHeader);
                cell = row.createCell(1);
                cell.setCellValue(group.getHeader().get(colHeader));

                rowNum++;
            }

            row = sheet.createRow(rowNum);

            int cellNum = 0;
            for (String colHeader : scaleReport.getResults().getDataFields()) {
                cell = row.createCell(cellNum);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);
                cell.setCellValue(rcolHeader);
                cellNum++;
            }

            rowNum++;

            for (Map<String, String> dataRow : group.getData()) {
                cellNum = 0;
                row = sheet.createRow(rowNum);
                for (String colHeader : scaleReport.getResults().getDataFields()) {
                    cell = row.createCell(cellNum);
                    cell.setCellValue(dataRow.get(colHeader));
                    cellNum++;
                }
                rowNum++;
            }

            row = sheet.createRow(rowNum);
            rowNum++;
        }

    }

    resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    wb.write(resp.getOutputStream());

}

From source file:com.tremolosecurity.scalejs.ws.ScaleMain.java

License:Apache License

private void exportToExcel(HttpFilterRequest request, HttpFilterResponse response, Gson gson)
        throws IOException {
    int lastslash = request.getRequestURI().lastIndexOf('/');
    int secondlastslash = request.getRequestURI().lastIndexOf('/', lastslash - 1);

    String id = request.getRequestURI().substring(secondlastslash + 1, lastslash);

    ReportResults res = (ReportResults) request.getSession().getAttribute(id);

    if (res == null) {
        response.setStatus(404);/*w  w  w.j  a va 2s.c  o  m*/
        ScaleError error = new ScaleError();
        error.getErrors().add("Report no longer available");
        ScaleJSUtils.addCacheHeaders(response);
        response.getWriter().print(gson.toJson(error).trim());
        response.getWriter().flush();
    } else {

        response.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
        response.setHeader("Pragma", "no-cache");

        Workbook wb = new XSSFWorkbook();

        Font font = wb.createFont();
        font.setBold(true);

        Font titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);

        Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(res.getName()));

        //Create a header
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        RichTextString title = new XSSFRichTextString(res.getName());
        title.applyFont(titleFont);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        cell.setCellValue(title);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(res.getDescription());

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

        row = sheet.createRow(2);
        cell = row.createCell(0);
        //cell.setCellValue(new DateTime().toString("MMMM Do, YYYY h:mm:ss a"));

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

        row = sheet.createRow(3);

        int rowNum = 4;

        if (res.getGrouping().isEmpty()) {
            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            cell.setCellValue("There is no data for this report");
        } else {

            for (ReportGrouping group : res.getGrouping()) {
                for (String colHeader : res.getHeaderFields()) {
                    row = sheet.createRow(rowNum);
                    cell = row.createCell(0);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);

                    cell.setCellValue(rcolHeader);
                    cell = row.createCell(1);
                    cell.setCellValue(group.getHeader().get(colHeader));

                    rowNum++;
                }

                row = sheet.createRow(rowNum);

                int cellNum = 0;
                for (String colHeader : res.getDataFields()) {
                    cell = row.createCell(cellNum);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);
                    cell.setCellValue(rcolHeader);
                    cellNum++;
                }

                rowNum++;

                for (Map<String, String> dataRow : group.getData()) {
                    cellNum = 0;
                    row = sheet.createRow(rowNum);
                    for (String colHeader : res.getDataFields()) {
                        cell = row.createCell(cellNum);
                        cell.setCellValue(dataRow.get(colHeader));
                        cellNum++;
                    }
                    rowNum++;
                }

                row = sheet.createRow(rowNum);
                rowNum++;
            }

        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        wb.write(response.getOutputStream());
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private void createMergedRegionIntoSheet(CellRangeAddress region) {
    Sheet sheet = getActiveSheet();
    int addMergedRegionIndex = sheet.addMergedRegion(region);
    MergedRegion mergedRegion = new MergedRegion();
    mergedRegion.col1 = region.getFirstColumn() + 1;
    mergedRegion.col2 = region.getLastColumn() + 1;
    mergedRegion.row1 = region.getFirstRow() + 1;
    mergedRegion.row2 = region.getLastRow() + 1;
    mergedRegion.id = mergedRegionCounter++;
    if (getState().mergedRegions == null) {
        getState().mergedRegions = new ArrayList<MergedRegion>();
    }//from   w  w  w  . j  av a 2  s  . c o  m
    getState().mergedRegions.add(addMergedRegionIndex - 1, mergedRegion);
    // update the style & data for the region cells, effects region + 1
    // FIXME POI doesn't seem to care that the other cells inside the merged
    // region should be removed; the values those cells have are still used
    // in formulas..
    for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) {
        Row row = sheet.getRow(r - 1);
        for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) {
            if (row != null) {
                Cell cell = row.getCell(c - 1);
                if (cell != null) {
                    styler.cellStyleUpdated(cell, false);
                    if ((c != mergedRegion.col1 || r != mergedRegion.row1) && c <= mergedRegion.col2
                            && r <= mergedRegion.row2) {
                        getCellValueManager().markCellForRemove(cell);
                    }
                }
            }
        }
    }
    styler.loadCustomBorderStylesToState();
    updateMarkedCells();
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public void setRegionCellStringValue(Workbook workbook, Sheet sheet, CellRangeAddress region,
        CellStyle cellStyle, String cellvalue) {
    createRowAndColInRegion(sheet, region, cellStyle);
    Row rowTmp = sheet.getRow(region.getFirstRow());
    Cell cellTmp = rowTmp.getCell(region.getFirstColumn());
    cellTmp.setCellStyle(cellStyle);//from  w  ww.  ja v  a2  s . co m
    cellTmp.setCellValue(cellvalue);
    sheet.addMergedRegion(region);
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public void setRegionCellRealTypeValue(Workbook workbook, Sheet sheet, CellRangeAddress region,
        CellStyle cellStyle, CellStyle cellStyleWithFormat, String align, Object cellvalue, IDataType typeObj) {
    createRowAndColInRegion(sheet, region, cellStyle);
    Row rowTmp = sheet.getRow(region.getFirstRow());//rowsetRegionStyle()
    Cell cellTmp = rowTmp.getCell(region.getFirstColumn());
    cellTmp.setCellStyle(cellStyle);//from  ww  w  . j  a  v a2 s.co  m
    this.setCellValue(workbook, align, cellTmp, cellvalue, typeObj, cellStyleWithFormat);
    sheet.addMergedRegion(region);
}

From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java

License:Apache License

public static void main(String[] args) {
    if (args.length != 4)
        usage("need four command arguments");

    String type = args[0];//www.j  a v a 2  s .c om
    long timeStarted = System.currentTimeMillis();
    Workbook workBook = createWorkbook(type);
    boolean isHType = workBook instanceof HSSFWorkbook;

    int rows = parseInt(args[1], "Failed to parse rows value as integer");
    int cols = parseInt(args[2], "Failed to parse cols value as integer");
    boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0;

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

    Sheet sheet = workBook.createSheet("Main Sheet");

    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));

    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;
        }

        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            Cell cell = row.createCell(colIndex);
            String address = new CellReference(cell).formatAsString();
            switch (colIndex) {
            case 0:
                // column A: default number format
                cell.setCellValue(value++);
                break;
            case 1:
                // column B: #,##0
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("#,##0.00"));
                break;
            case 2:
                // column C: $#,##0.00
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("$#,##0.00"));
                break;
            case 3:
                // column D: red bold text on yellow background
                cell.setCellValue(address);
                cell.setCellStyle(styles.get("red-bold"));
                break;
            case 4:
                // column E: boolean
                // TODO booleans are shown as 1/0 instead of TRUE/FALSE
                cell.setCellValue(rowIndex % 2 == 0);
                break;
            case 5:
                // column F:  date / time
                cell.setCellValue(calendar);
                cell.setCellStyle(styles.get("m/d/yyyy"));
                calendar.roll(Calendar.DAY_OF_YEAR, -1);
                break;
            case 6:
                // column F: formula
                // TODO formulas are not yet supported  in SXSSF
                //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")");
                //break;
            default:
                cell.setCellValue(value++);
                break;
            }
        }
        rowIndexInSheet++;
    }
    if (saveFile) {
        String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]);
        try {
            FileOutputStream out = new FileOutputStream(fileName);
            workBook.write(out);
            out.close();
        } catch (IOException ioe) {
            System.err
                    .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage());
        }
    }
    long timeFinished = System.currentTimeMillis();
    System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds");
}

From source file:Compras.altaCompras.java

   private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed
    // TODO add your handling code here:
    javax.swing.JFileChooser jF1= new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;/*from  w ww.  java 2  s.  com*/
    if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION)
    {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        File archivoXLS = new File(ruta+".xls");
        try
        {
            if(archivoXLS.exists())
            archivoXLS.delete();
            archivoXLS.createNewFile();
            Workbook libro = new HSSFWorkbook();
            FileOutputStream archivo = new FileOutputStream(archivoXLS);
            Sheet hoja = libro.createSheet("Muestras");
                
            org.apache.poi.ss.usermodel.Font font10 = libro.createFont();
            font10.setFontHeightInPoints((short)10);
            font10.setFontName("Arial");
            font10.setItalic(false);
            font10.setBold(false);
            font10.setColor(new HSSFColor.YELLOW().getIndex());
                
                
            CellStyle titulo = libro.createCellStyle();
            CellStyle contenido = libro.createCellStyle();
            CellStyle firma = libro.createCellStyle();
            CellStyle costado = libro.createCellStyle();
            CellStyle derecha = libro.createCellStyle();
            CellStyle derecha_borde = libro.createCellStyle();
                
            titulo.setFont(font10);
            titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex());
            titulo.setAlignment(CellStyle.ALIGN_CENTER);
                
            contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderTop(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderRight(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT);
                
            derecha.setAlignment(CellStyle.ALIGN_RIGHT);
                
            firma.setBorderTop(HSSFCellStyle.BORDER_THIN);
            firma.setAlignment(CellStyle.ALIGN_CENTER);
                
            //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            hoja.setColumnWidth(0, 3000);
            hoja.setColumnWidth(2, 3000);
            hoja.setColumnWidth(3, 8000);
            hoja.setColumnWidth(4, 5000);
            try
            {
                InputStream is = new FileInputStream("imagenes/grande300115.jpg");
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                is.close();
                CreationHelper helper = libro.getCreationHelper();
                Drawing drawing = hoja.createDrawingPatriarch();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(0);
                anchor.setRow1(0);
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                pict.resize();
            }catch(Exception e){e.printStackTrace();}
            Row r7 = hoja.createRow(7);
            r7.createCell(0).setCellValue("ORDEN:");
            r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden()));
            r7.createCell(2).setCellValue("Hrs. Entrega:");
            r7.createCell(3).setCellValue("");
            hoja.addMergedRegion(new CellRangeAddress(7,7,4,7));
            r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS");
            r7.getCell(4).setCellStyle(derecha);
                
            Date fecha = new Date();
            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS
            String valor=dateFormat.format(fecha);
            Row r8 = hoja.createRow(8);
            r8.createCell(0).setCellValue("Supervisor:");
            hoja.addMergedRegion(new CellRangeAddress(8,8,1,3));
            r8.createCell(1).setCellValue("");
            r8.createCell(4).setCellValue("F. Elaboracin:");
            r8.createCell(5).setCellValue(valor);
                
            Row r9 = hoja.createRow(9);
            r9.createCell(0).setCellValue("Comprador:");
            hoja.addMergedRegion(new CellRangeAddress(9,9,1,3));
            r9.createCell(1).setCellValue("");
            r9.createCell(4).setCellValue("F. Entrega:");
            r9.createCell(5).setCellValue("");
                
            Row r10 = hoja.createRow(10);
            r10.createCell(0).setCellValue("Cantidad");
            r10.getCell(0).setCellStyle(titulo);
            hoja.addMergedRegion(new CellRangeAddress(10,10,1,7));
            r10.createCell(1).setCellValue("Descripcin");
            r10.getCell(1).setCellStyle(titulo);
                
            int ren=11;
            for(int r=0;r<(t_datos.getRowCount());r++)
            {
                if((boolean)t_titulos.getValueAt(r, 3)==true)
                {
                    Row fila = hoja.createRow(ren);
                    Cell celda = fila.createCell(0);
                    celda.setCellStyle(derecha_borde);
                    Cell celda1 = fila.createCell(1);
                    celda1.setCellStyle(contenido);
                    fila.createCell(2).setCellStyle(contenido);
                    fila.createCell(3).setCellStyle(contenido);
                    fila.createCell(4).setCellStyle(contenido);
                    fila.createCell(5).setCellStyle(contenido);
                    fila.createCell(6).setCellStyle(contenido);
                    fila.createCell(7).setCellStyle(contenido);
                    //Cell celda8 = fila.createCell(8);
                    //celda8.setCellStyle(costado);
                    try{
                        celda.setCellValue(t_datos.getValueAt(r, 14).toString());
                        hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7));
                        celda1.setCellValue(t_titulos.getValueAt(r, 2).toString());
                        //celda8.setCellValue("");
                    }catch(Exception e){
                        celda.setCellValue("");
                    }
                    ren++;
                }
            }
                
            Row rx = hoja.createRow(ren+5);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2));
            rx.createCell(0).setCellValue("Recibe Muestras");
            rx.getCell(0).setCellStyle(firma);
            rx.createCell(1).setCellStyle(firma);
            rx.createCell(2).setCellStyle(firma);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7));
            rx.createCell(5).setCellValue("Entrega Muestras");
            rx.getCell(5).setCellStyle(firma);
            rx.createCell(6).setCellStyle(firma);
            rx.createCell(7).setCellStyle(firma);
                
            libro.write(archivo);
            archivo.close();
            Desktop.getDesktop().open(archivoXLS);
        }catch(Exception e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto");
        }
    }
}