Example usage for org.apache.poi.ss.usermodel CellStyle setWrapText

List of usage examples for org.apache.poi.ss.usermodel CellStyle setWrapText

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setWrapText

Introduction

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

Prototype

void setWrapText(boolean wrapped);

Source Link

Document

Set whether the text should be wrapped.

Usage

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//*from www .  j a v a 2s .  c o  m*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        log.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
        assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        log.debug("getFillPattern : " + cs1.getFillPattern());
        assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
        log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?//from   www.  j  a  v a 2  s  .  co m
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx");

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new XSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

        //  ? 
        XSSFWorkbook wb = null;
        wb = excelService.loadWorkbook(sb.toString(), wb);
        LOGGER.debug("testModifyCellContents after loadWorkbook....");

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cellx = row.createCell(cellnum);
        cellx.setCellType(XSSFCell.CELL_TYPE_STRING);
        cellx.setCellValue(new XSSFRichTextString(content));
        cellx.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

        FileOutputStream outx = new FileOutputStream(sb.toString());
        wb.write(outx);
        outx.close();

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  //  w ww  .j a v a2 s .  c  o  m
 */
@Test
public void testGetCellContents() throws Exception {

    try {
        LOGGER.debug("testGetCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.xlsx");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new XSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        LOGGER.debug("testGetCellContents after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet");

        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheet("cell test sheet");

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell : {}", i, j, cell1.getRichStringCellValue());
                assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testGetCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? //from  w w  w  .  j av  a2 s.com
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        LOGGER.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new XSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        LOGGER.debug("testModifyCellAttribute after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet2");
        sheet.setColumnWidth((short) 3, (short) 200); // column Width

        CellStyle cs = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        font.setFontHeight(16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            row.setHeight((short) 300); // row? height 

            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheet("cell test sheet2");
        LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles());

        XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        XSSFFont fontT = cs1.getFont();
        LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight());
        LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight());
        LOGGER.debug("font getFontName : {}", fontT.getFontName());
        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        LOGGER.debug("getWrapText : {}", cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue());
                assertEquals(320, fontT.getFontHeight());
                assertEquals(400, fontT.getBoldweight());
                LOGGER.debug("fontT.getBoldweight()? ? 400? ?");

                assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellAttribute end....");
    }
}

From source file:eu.esdihumboldt.hale.io.xls.writer.XLSLookupTableWriter.java

License:Open Source License

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 *///from  w  ww.j  a  v a2 s  . co  m
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter)
        throws IOProviderConfigurationException, IOException {

    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    }
    // write xlsx file
    else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }

    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Lookup table");
    Row row = null;
    Cell cell = null;
    DataFormat df = workbook.createDataFormat();

    // create cell style of the header
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));

    // create cell style
    CellStyle rowStyle = workbook.createCellStyle();
    // set thin border around the cell
    rowStyle.setBorderBottom(CellStyle.BORDER_THIN);
    rowStyle.setBorderLeft(CellStyle.BORDER_THIN);
    rowStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    rowStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    rowStyle.setWrapText(true);

    Map<Value, Value> table = getLookupTable().getTable().asMap();

    int rownum = 0;

    // write header
    row = sheet.createRow(rownum++);
    cell = row.createCell(0);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    cell = row.createCell(1);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    for (Value key : table.keySet()) {
        // create a row
        row = sheet.createRow(rownum);

        cell = row.createCell(0);
        cell.setCellValue(key.as(String.class));
        cell.setCellStyle(rowStyle);

        Value entry = table.get(key);
        cell = row.createCell(1);
        cell.setCellValue(entry.as(String.class));
        cell.setCellStyle(rowStyle);
        rownum++;
    }

    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();

    reporter.setSuccess(true);
    return reporter;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the normal cell style//from   w ww .  j  a  v  a 2s.c  o  m
 */
public static CellStyle getNormalStyle(Workbook workbook, boolean strikeOut) {

    // create cell style
    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    cellStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    cellStyle.setWrapText(true);

    if (strikeOut) {
        // strike out font
        Font disabledFont = workbook.createFont();
        disabledFont.setStrikeout(true);
        disabledFont.setColor(IndexedColors.GREY_40_PERCENT.getIndex());
        cellStyle.setFont(disabledFont);
    }

    return cellStyle;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the highlighted cell style//  w  ww.j  a v  a2s . c  o  m
 */
public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) {

    // create highlight style for type cells
    CellStyle highlightStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    highlightStyle.setBorderBottom(CellStyle.BORDER_THIN);
    highlightStyle.setBorderLeft(CellStyle.BORDER_THIN);
    highlightStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    highlightStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    highlightStyle.setWrapText(true);
    highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    if (strikeOut) {
        Font disabledTypeFont = workbook.createFont();
        disabledTypeFont.setStrikeout(true);
        disabledTypeFont.setColor(IndexedColors.BLACK.getIndex());
        highlightStyle.setFont(disabledTypeFont);
    }

    return highlightStyle;
}

From source file:eventHandlers.CompareDrotVSRoster.java

private CellStyle createStandardStyle(XSSFWorkbook myWorkBook) {

    CellStyle style = myWorkBook.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setWrapText(true);

    return style;
}

From source file:Export.ExporOnlyViagemExcel.java

private void dataTableDados(Workbook wb, Date dataInicio, Date dataFim) throws ParseException {

    Font fTitulo = wb.createFont();
    fTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    fTitulo.setFontHeightInPoints((short) 14);

    Font fTituloP = wb.createFont();
    fTituloP.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    fTituloP.setFontHeightInPoints((short) 12);
    //            fTituloP.setStrikeout(true);
    fTituloP.setUnderline(org.apache.poi.ss.usermodel.Font.U_SINGLE);

    Font fTituloTabela = wb.createFont();
    fTituloTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    fTituloTabela.setFontHeightInPoints((short) 8);

    Font fCorpoTabela = wb.createFont();
    fCorpoTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_NORMAL);
    fCorpoTabela.setFontHeightInPoints((short) 8.5);

    Font fRodapeTabela = wb.createFont();
    fRodapeTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    fRodapeTabela.setFontHeightInPoints((short) 8.5);

    Font fNormal = wb.createFont();
    fNormal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    fNormal.setFontHeightInPoints((short) 8.5);

    CellStyle csTitulo = wb.createCellStyle();
    csTitulo.setFont(fTitulo);// w w w  .j  ava 2 s.  co  m
    csTitulo.setAlignment((short) 1);
    csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    csTitulo.setBorderBottom((short) 0);
    csTitulo.setBorderTop((short) 0);
    csTitulo.setBorderRight((short) 0);
    csTitulo.setBorderLeft((short) 0);
    csTitulo.setWrapText(true);

    CellStyle csTituloP = wb.createCellStyle();
    csTituloP.setFont(fTituloP);
    csTituloP.setAlignment((short) 1);
    csTituloP.setVerticalAlignment((short) 1);
    csTituloP.setBorderBottom((short) 0);
    csTituloP.setBorderTop((short) 0);
    csTituloP.setBorderRight((short) 0);
    csTituloP.setBorderLeft((short) 0);
    csTituloP.setWrapText(true);

    CellStyle csTituloT = wb.createCellStyle();
    csTituloT.setFont(fTituloP);
    csTituloT.setAlignment((short) 1);
    csTituloT.setVerticalAlignment((short) 1);
    csTituloT.setBorderBottom((short) 0);
    csTituloT.setBorderTop((short) 0);
    csTituloT.setBorderRight((short) 0);
    csTituloT.setBorderLeft((short) 0);
    csTituloT.setWrapText(true);

    CellStyle csTituloTabela = wb.createCellStyle();
    csTituloTabela.setFont(fTituloTabela);
    csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER);
    csTituloTabela.setVerticalAlignment((short) 2);
    csTituloTabela.setBorderBottom((short) 2);
    csTituloTabela.setBorderTop((short) 2);
    csTituloTabela.setBorderRight((short) 2);
    csTituloTabela.setBorderLeft((short) 2);
    csTituloTabela.setWrapText(true);

    CellStyle csTituloTabelaNBorder = wb.createCellStyle();
    csTituloTabelaNBorder.setFont(fTituloTabela);
    csTituloTabelaNBorder.setAlignment(CellStyle.ALIGN_CENTER);
    csTituloTabelaNBorder.setVerticalAlignment((short) 2);
    csTituloTabelaNBorder.setBorderBottom((short) 2);
    csTituloTabelaNBorder.setBorderTop((short) 2);
    csTituloTabelaNBorder.setBorderRight((short) 2);
    csTituloTabelaNBorder.setBorderLeft((short) 2);
    csTituloTabelaNBorder.setWrapText(true);

    CellStyle csCorpoTabela = wb.createCellStyle();
    csCorpoTabela.setFont(fCorpoTabela);
    csCorpoTabela.setAlignment((short) 2);
    csCorpoTabela.setVerticalAlignment((short) 1);
    csCorpoTabela.setBorderBottom((short) 1);
    csCorpoTabela.setBorderTop((short) 1);
    csCorpoTabela.setBorderRight((short) 1);
    csCorpoTabela.setBorderLeft((short) 1);
    csCorpoTabela.setWrapText(true);

    CellStyle csCorpoTabelaR = wb.createCellStyle();
    csCorpoTabelaR.setFont(fCorpoTabela);
    csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
    csCorpoTabelaR.setVerticalAlignment((short) 1);
    csCorpoTabelaR.setBorderBottom((short) 1);
    csCorpoTabelaR.setBorderTop((short) 1);
    csCorpoTabelaR.setBorderRight((short) 1);
    csCorpoTabelaR.setBorderLeft((short) 1);
    csCorpoTabelaR.setWrapText(true);

    CellStyle csCorpoTabelaL = wb.createCellStyle();
    csCorpoTabelaL.setFont(fCorpoTabela);
    csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT);
    csCorpoTabelaL.setVerticalAlignment((short) 1);
    csCorpoTabelaL.setBorderBottom((short) 1);
    csCorpoTabelaL.setBorderTop((short) 1);
    csCorpoTabelaL.setBorderRight((short) 1);
    csCorpoTabelaL.setBorderLeft((short) 1);
    csCorpoTabelaL.setWrapText(true);

    CellStyle csRodapeTabela = wb.createCellStyle();
    csRodapeTabela.setFont(fRodapeTabela);
    csRodapeTabela.setAlignment((short) 1);
    csRodapeTabela.setVerticalAlignment((short) 2);
    csRodapeTabela.setBorderBottom((short) 2);
    csRodapeTabela.setBorderTop((short) 2);
    csRodapeTabela.setBorderRight((short) 2);
    csRodapeTabela.setBorderLeft((short) 2);
    csRodapeTabela.setWrapText(true);

    CellStyle csRodapeTabelaR = wb.createCellStyle();
    csRodapeTabelaR.setFont(fRodapeTabela);
    csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
    csRodapeTabelaR.setVerticalAlignment((short) 2);
    csRodapeTabelaR.setBorderBottom((short) 2);
    csRodapeTabelaR.setBorderTop((short) 2);
    csRodapeTabelaR.setBorderRight((short) 2);
    csRodapeTabelaR.setBorderLeft((short) 2);
    csRodapeTabelaR.setWrapText(true);

    CellStyle csNomal = wb.createCellStyle();
    csNomal.setFont(fCorpoTabela);
    csNomal.setAlignment((short) 1);
    csNomal.setVerticalAlignment((short) 1);
    csNomal.setBorderBottom((short) 0);
    csNomal.setBorderTop((short) 0);
    csNomal.setBorderRight((short) 0);
    csNomal.setBorderLeft((short) 0);
    csNomal.setWrapText(true);

    int i, p = 0;
    dataViagem(dataInicio, dataFim);
    for (Map.Entry<String, ArrayList<HashMap<String, Object>>> data : hasList.entrySet()) {

        totalSelo = 0f;
        totalConsumo = 0f;
        totalTotal = 0f;
        totalPremio = 0f;
        totalComissao = 0f;

        linha = 0;

        SimpleDateFormat sdfEsp = new SimpleDateFormat("MMMM, yyyy", Locale.ENGLISH);
        SimpleDateFormat sdf = new SimpleDateFormat("MM-yyyy");

        String titile = sdfEsp.format(sdf.parse((data.getKey()))).toUpperCase();

        Sheet s = wb.createSheet(titile);

        dataTableTitile(s, titile, csTitulo, csTituloP, csTituloTabelaNBorder);

        Row r = s.createRow(linha);
        Cell c = r.createCell(2);

        for (int j = 0; j < 13; j++) {
            switch (j) {
            case 0:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 1, 4);
                break;
            case 1:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 2, 6);
                break;
            case 2:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 3, 8);
                break;
            case 3:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 4, 6);
                break;
            case 4:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 5, 6);
                break;
            case 5:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 6, 4);
                break;
            case 6:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 7, 25);
                break;
            case 7:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 8, 10);
                break;
            case 8:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 9, 10);
                break;
            case 9:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 10, 8);
                break;
            case 10:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 11, 8);
                break;
            case 11:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 12, 8);
                break;
            case 12:
                createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 13, 8);
                break;
            default:
                break;
            }
        }
        i = 1;

        for (HashMap<String, Object> hashMap : data.getValue()) {

            linha++;
            r = s.createRow(linha);
            c = r.createCell(2);

            createCell(c, r, s, csCorpoTabela, linha, linha, (i + ""), 1, 4);

            createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(DATA)), 2, 6);

            createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(NUMEROAPOLICE)), 3, 7);

            createCell(c, r, s, csCorpoTabela, linha, linha,
                    ConfigDoc.toFormat(toString(hashMap.get(INICIO)), "dd-MM-yyyy", "yyyy-MM-dd"), 4, 6);

            createCell(c, r, s, csCorpoTabela, linha, linha,
                    ConfigDoc.toFormat(toString(hashMap.get(FIM)), "dd-MM-yyyy", "yyyy-MM-dd"), 5, 6);

            createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(DIAS)), 6, 4);

            createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(hashMap.get(CLIENTE)), 7, 20);

            createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(RECIBO)), 8, 10);

            createCell(c, r, s, csCorpoTabelaR, linha, linha, toString(hashMap.get(PREMIO)), 9, 10);

            totalComissao += toFloat(toString(hashMap.get(COMISSAO)));
            createCell(c, r, s, csCorpoTabelaR, linha, linha,
                    ConfigDoc.toMoeda(toString(hashMap.get(COMISSAO)), ""), 10, 8);

            totalConsumo += toFloat(toString(hashMap.get(CONSUMO)));
            createCell(c, r, s, csCorpoTabelaR, linha, linha,
                    ConfigDoc.toMoeda(toString(hashMap.get(CONSUMO)), ""), 11, 8);

            totalSelo += toFloat(toString(hashMap.get(SELO)));
            createCell(c, r, s, csCorpoTabelaR, linha, linha,
                    ConfigDoc.toMoeda(toString(hashMap.get(SELO)), ""), 12, 8);

            totalTotal += toFloat(toString(hashMap.get(TOTAL)));
            createCell(c, r, s, csCorpoTabelaR, linha, linha,
                    ConfigDoc.toMoeda(toString(hashMap.get(TOTAL)), ""), 13, 8);

            i++;
        }
        linha++;
        radapeTable(s, csRodapeTabelaR);
    }
}

From source file:Export.ExportMapaProducaoExcel__.java

public String criarDoc(String user, Date incio, Date fim) {
    try {/*w  w w .j  a v  a2s . c  om*/
        Workbook wb = new HSSFWorkbook();

        Font fTitulo = wb.createFont();
        fTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTitulo.setFontHeightInPoints((short) 22);

        Font fTituloP = wb.createFont();
        fTituloP.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTituloP.setFontHeightInPoints((short) 13);

        Font fTituloTabela = wb.createFont();
        fTituloTabela.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTituloTabela.setFontHeightInPoints((short) 12.5);

        Font fCorpoTabela = wb.createFont();
        fCorpoTabela.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        fCorpoTabela.setFontHeightInPoints((short) 11.5);

        Font fRodapeTabela = wb.createFont();
        fRodapeTabela.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fRodapeTabela.setFontHeightInPoints((short) 11.5);

        Font fNormal = wb.createFont();
        fNormal.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fNormal.setFontHeightInPoints((short) 11);

        CellStyle csTitulo = wb.createCellStyle();
        csTitulo.setFont(fTitulo);
        csTitulo.setAlignment((short) 1);
        csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        csTitulo.setWrapText(true);
        csTitulo.setBorderBottom((short) 0);
        csTitulo.setBorderTop((short) 0);
        csTitulo.setBorderRight((short) 0);
        csTitulo.setBorderLeft((short) 0);
        csTitulo.setWrapText(true);

        CellStyle csTituloP = wb.createCellStyle();
        csTituloP.setFont(fTituloP);
        csTituloP.setAlignment((short) 1);
        csTituloP.setVerticalAlignment((short) 1);
        csTituloP.setWrapText(true);
        csTituloP.setBorderBottom((short) 0);
        csTituloP.setBorderTop((short) 0);
        csTituloP.setBorderRight((short) 0);
        csTituloP.setBorderLeft((short) 0);
        csTituloP.setWrapText(true);

        CellStyle csTituloT = wb.createCellStyle();
        csTituloT.setFont(fTituloP);
        csTituloT.setAlignment((short) 1);
        csTituloT.setVerticalAlignment((short) 1);
        csTituloT.setWrapText(true);
        csTituloT.setBorderBottom((short) 0);
        csTituloT.setBorderTop((short) 0);
        csTituloT.setBorderRight((short) 0);
        csTituloT.setBorderLeft((short) 0);
        csTituloT.setWrapText(true);

        CellStyle csTituloTabela = wb.createCellStyle();
        csTituloTabela.setFont(fTituloTabela);
        csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER);
        csTituloTabela.setVerticalAlignment((short) 2);
        csTituloTabela.setBorderBottom((short) 2);
        csTituloTabela.setBorderTop((short) 2);
        csTituloTabela.setBorderRight((short) 2);
        csTituloTabela.setBorderLeft((short) 2);
        csTituloTabela.setWrapText(true);

        CellStyle csCorpoTabela = wb.createCellStyle();
        csCorpoTabela.setFont(fCorpoTabela);
        csCorpoTabela.setAlignment((short) 2);
        csCorpoTabela.setVerticalAlignment((short) 1);
        csCorpoTabela.setBorderBottom((short) 1);
        csCorpoTabela.setBorderTop((short) 1);
        csCorpoTabela.setBorderRight((short) 1);
        csCorpoTabela.setBorderLeft((short) 1);
        csCorpoTabela.setWrapText(true);

        CellStyle csCorpoTabelaR = wb.createCellStyle();
        csCorpoTabelaR.setFont(fCorpoTabela);
        csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csCorpoTabelaR.setVerticalAlignment((short) 1);
        csCorpoTabelaR.setBorderBottom((short) 1);
        csCorpoTabelaR.setBorderTop((short) 1);
        csCorpoTabelaR.setBorderRight((short) 1);
        csCorpoTabelaR.setBorderLeft((short) 1);
        csCorpoTabelaR.setWrapText(true);

        CellStyle csCorpoTabelaL = wb.createCellStyle();
        csCorpoTabelaL.setFont(fCorpoTabela);
        csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT);
        csCorpoTabelaL.setVerticalAlignment((short) 1);
        csCorpoTabelaL.setBorderBottom((short) 1);
        csCorpoTabelaL.setBorderTop((short) 1);
        csCorpoTabelaL.setBorderRight((short) 1);
        csCorpoTabelaL.setBorderLeft((short) 1);
        csCorpoTabelaL.setWrapText(true);

        CellStyle csRodapeTabela = wb.createCellStyle();
        csRodapeTabela.setFont(fRodapeTabela);
        csRodapeTabela.setAlignment((short) 1);
        csRodapeTabela.setVerticalAlignment((short) 2);
        csRodapeTabela.setBorderBottom((short) 2);
        csRodapeTabela.setBorderTop((short) 2);
        csRodapeTabela.setBorderRight((short) 2);
        csRodapeTabela.setBorderLeft((short) 2);
        csRodapeTabela.setWrapText(true);

        CellStyle csRodapeTabelaR = wb.createCellStyle();
        csRodapeTabelaR.setFont(fRodapeTabela);
        csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csRodapeTabelaR.setVerticalAlignment((short) 2);
        csRodapeTabelaR.setBorderBottom((short) 2);
        csRodapeTabelaR.setBorderTop((short) 2);
        csRodapeTabelaR.setBorderRight((short) 2);
        csRodapeTabelaR.setBorderLeft((short) 2);
        csRodapeTabelaR.setWrapText(true);

        CellStyle csNomal = wb.createCellStyle();
        csNomal.setFont(fCorpoTabela);
        csNomal.setAlignment((short) 1);
        csNomal.setVerticalAlignment((short) 1);
        csNomal.setBorderBottom((short) 0);
        csNomal.setBorderTop((short) 0);
        csNomal.setBorderRight((short) 0);
        csNomal.setBorderLeft((short) 0);
        csNomal.setWrapText(true);

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy hh'.'mm'.'ss");
        SimpleDateFormat sdfPt = new SimpleDateFormat("dd-MM-yyyy");

        File ff = new File(getDiretorio() + "/" + user + "/Relatorio/");
        ff.mkdirs();

        String stringData = sdf.format(new Date());

        ff = new File(ff.getAbsoluteFile() + "/" + "Export Mapa ProducaoExel " + stringData + ".xls");

        String reString = "../Documentos/" + user + "/Relatorio/" + "Export Mapa ProducaoExel " + stringData
                + ".xls";

        Sheet s = wb.createSheet("Mapa de produo de ".toUpperCase()
                + ((incio != null) ? sdfPt.format(incio) + "  " : " dos Ultimos anos te hoje".toUpperCase())
                + ((fim == null) ? "" : sdfPt.format(fim)));
        int linha = 0;

        //            int pictureIdx;
        //            try (InputStream inputStream = new FileInputStream("logo1.png")) {
        //                byte[] bytes = IOUtils.toByteArray(inputStream);
        //                pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //            }
        //            CreationHelper helper = wb.getCreationHelper();
        //            Drawing drawing = s.createDrawingPatriarch();
        //            ClientAnchor anchor = helper.createClientAnchor();
        //            anchor.setCol1(0);
        //            anchor.setCol2(3);
        //            anchor.setRow1(0);
        //            Picture pict = drawing.createPicture(anchor, pictureIdx);
        //            pict.resize();

        Row r = s.createRow(linha);
        Cell c = r.createCell(2);
        CreateCell(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22);
        linha += 4;

        r = s.createRow(linha);
        CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL,
                1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22);

        ResultSet rs = ud.relatorioSeguroForImpresao(incio, fim);
        Consumer<HashMap<String, Object>> act = (map) -> {
            list = new ArrayList<>();
            putNewDado(map, incio, fim);
        };

        Call.forEchaResultSet(act, rs);

        for (Map.Entry<String, ArrayList<Producao>> al : hasList.entrySet()) {
            linha += 4;
            r = s.createRow(linha);
            CreateCell(c, r, s, csTituloT, linha, linha, al.getKey(), 1, 22);

            linha++;
            linha++;
            r = s.createRow(linha);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "Nr. Factura", 1, 2);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "Nome do Segurado", 3, 7);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "Premio", 8, 10);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "Imposto 6%", 11, 13);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "Imposto 5%", 14, 16);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "FGA 2.6%", 17, 19);
            CreateCell(c, r, s, csTituloTabela, linha, linha, "TOTAL", 20, 22);
            for (Producao pro : al.getValue()) {

                linha++;
                r = s.createRow(linha);
                if (!pro.DATA.equals("SOMATORIO")) {
                    CreateCell(c, r, s, csCorpoTabela, linha, linha, pro.NUMAPOLICE, 1, 2);
                    CreateCell(c, r, s, csCorpoTabelaL, linha, linha, pro.CLIENTESEGURO, 3, 7);
                    CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.PREMIO + " " + pro.MOEDA, 8, 10);
                    CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.IMPOSTOCONSUMO + " " + pro.MOEDA, 11,
                            13);
                    CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.IMPOSTOSELO + " " + pro.MOEDA, 14,
                            16);
                    CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.FGA + " " + pro.MOEDA, 17, 19);
                    CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.VALORTOTAL + " " + pro.MOEDA, 20, 22);
                } else {
                    CreateCell(c, r, s, csRodapeTabela, linha, linha, "Total " + al.getKey(), 1, 7);
                    CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.PREMIO + " " + pro.MOEDA, 8, 10);
                    CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.IMPOSTOCONSUMO + " " + pro.MOEDA, 11,
                            13);
                    CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.IMPOSTOSELO + " " + pro.MOEDA, 14,
                            16);
                    CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.FGA + " " + pro.MOEDA, 17, 19);
                    CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.VALORTOTAL + " " + pro.MOEDA, 20,
                            22);
                }
            }
        }

        try (FileOutputStream out = new FileOutputStream(ff)) {
            wb.write(out);
        }

        return reString;
    } catch (IOException ex) {
        Logger.getLogger(ExportMapaProducaoExcel__.class.getName()).log(Level.SEVERE, null, ex);
        return null;
    }
}