Example usage for org.apache.poi.ss.usermodel Cell setCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellValue

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, List<TesteCaseTSBean> listTestCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from ww  w .ja  va 2 s . c  o m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    if (listTestCase.size() > 0) {
        descriptionPlan.setCellValue(listTestCase.get(0).getTestPlan());
        prj.setCellValue(listTestCase.get(0).getSTIPRJ());
        fase.setCellValue(listTestCase.get(0).getFASE());
        testPhase.setCellValue(listTestCase.get(0).getTestPhase());
    }

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

        //            estilo.setDataFormat(format.getFormat(formatData));
        //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        testScriptName.setCellValue(listTestCase.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTestCase.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTestCase.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTestCase.get(i).getStepDescription());
        expectedResults.setCellValue(listTestCase.get(i).getExpectedResults());
        product.setCellValue(listTestCase.get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(listTestCase.get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from ww w.j  av a  2s. c  o  m
    destinationSheet = new File(pathSheet + "\\" + nameSheet + ".xlsx");
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(FunctiosDates.getDateActual());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean createSpreadsheetTS(String pathSheet, String nameSheet, TestPlanTSBean testPlan)
        throws Exception {

    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from  www. j  ava2 s .c om
    String sheetTI = pathSheet + "\\" + nameSheet;
    nameSheet = nameSheet.replace("xlsx", "xlsm");
    ;
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File("C:\\FastPlan\\sheets\\TS_NEW.xlsm");
    logger.info("Realizando cpia da planilha");
    boolean existInList = false;

    List<TesteCaseTSBean> testCasesAutomatizados = new ArrayList<TesteCaseTSBean>();

    //run macro
    String cmd = "C:\\FastPlan\\runMacro.vbs";
    Runtime.getRuntime().exec("cmd /c" + cmd);

    Thread.sleep(2000);

    copySheet(sourceStheet, destinationSheet);
    logger.info("Planilha copiada");
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);

    XSSFSheet sheetTS = workbook.getSheetAt(0);
    XSSFCellStyle estilo = workbook.createCellStyle();

    //        workbook.setSheetName(workbook.getSheetIndex(sheetTS), FunctiosDates.dateToString(FunctiosDates.getDateActual(), "yyyy-MM-dd-HH-mm-ss"));
    int linha = 2;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell release = row.getCell(1);
    Cell prj = row.getCell(2);
    Cell fase = row.getCell(3);
    Cell testPhase = row.getCell(4);
    Cell testScriptName = row.getCell(5);
    Cell testScriptDescription = row.getCell(6);
    Cell stepNo = row.getCell(7);
    Cell stepDescription = row.getCell(8);
    Cell expectedResults = row.getCell(9);
    Cell product = row.getCell(10);
    Cell dataPlanejada = row.getCell(11);
    Cell qtdSteps = row.getCell(12);
    Cell complexidade = row.getCell(13);
    Cell automatizado = row.getCell(14);

    logger.info("Inserindo dados do plano");
    descriptionPlan.setCellValue(testPlan.getName());
    release.setCellValue(testPlan.getRelease());

    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);

        logger.info("Inserindo dados dos TCs");
        System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - " + testPlan.getSti()
                + " - " + "row:" + linha);
        prj.setCellValue(testPlan.getSti());
        fase.setCellValue(testPlan.getCrFase());
        testPhase.setCellValue(testPlan.getTestPhase());
        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        qtdSteps.setCellValue(testPlan.getTestCase().get(i).getListStep().size());
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        //set colors 
        if (i % 2 == 0) {

            System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - entrou");
            //                setColorCells(new Cell[]{descriptionPlan, release, prj, fase, testPhase, testScriptName, testScriptDescription, stepNo,
            //                    stepDescription, expectedResults, product, dataPlanejada, qtdSteps, complexidade}, workbook);

            XSSFCellStyle styleColor = (XSSFCellStyle) product.getCellStyle();
            styleColor.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);
            product.setCellStyle(styleColor);

        }

        for (int j = 0; j < testPlan.getTestCase().get(i).getListStep().size(); j++) {

            row = sheetTS.getRow(linha);
            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);
            //                
            //                stepNo.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getNomeStep());
            logger.info("Inserindo dados dos Steps");
            stepNo.setCellValue(j + 1);
            stepDescription.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getDescStep());
            expectedResults.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getResultadoStep());

            linha = linha + 1;

            row = sheetTS.getRow(linha);

            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);

        }

        linha = linha + 1;
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);
        logger.info("Dados inseridos na planilha");

        if (testPlan.getTestCase().get(i).isAutomatizado()) {

            for (int j = 0; j < testCasesAutomatizados.size(); j++) {
                if (testPlan.getTestCase().get(i).equals(testCasesAutomatizados.get(j).getTestScriptName())) {
                    existInList = true;
                }
            }
            if (!existInList) {
                testCasesAutomatizados.add(testPlan.getTestCase().get(i));
                existInList = true;
            }
        }

    }

    ExtraiPlanilha extraiPlanilha = new ExtraiPlanilha();
    extraiPlanilha.exportTStoTI(testCasesAutomatizados, sheetTI);

    logger.info("Preparando para salvar planilha");
    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    logger.info("Fim mtodo - new FileOutputStream(destinationSheet) ");
    logger.info("Tentando gravar na planilha.");
    workbook.write(fileOut);
    logger.info("Fim mtodo - workbook.write(fileOut)");
    fileOut.close();
    fileSheet.close();
    sucess = true;
    logger.info("Planilha gerada.");
    return sucess;

}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from   w  w  w. j av a2 s  .com
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);

    descriptionPlan.setCellValue(testPlan.getName());
    prj.setCellValue(testPlan.getSti());
    fase.setCellValue(testPlan.getCrFase());
    testPhase.setCellValue(testPlan.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);
    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        stepNo.setCellValue(testPlan.getTestCase().get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(testPlan.getTestCase().get(i).getStepDescription());
        expectedResults.setCellValue(testPlan.getTestCase().get(i).getExpectedResults());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        linha = linha + 2;

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean updateTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//  w ww .  jav a 2 s. c om
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);

    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(testCase.getDataPlanejada());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell set(Sheet sheet, int row, int col, Object text, Style style, int rowspan, int colspan) {
    Row r = sheet.getRow(row);/*from www.  j a  v a 2 s .co  m*/
    if (r == null)
        r = sheet.createRow(row);
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    if (text == null) {
        if (c.getCellStyle().getDataFormatString().startsWith("0")) {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue("");
        } else {
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue("");
        }
    } else if (text instanceof String) {
        try {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(Integer.parseInt((String) text));
        } catch (Exception e) {
            try {
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(Double.parseDouble((String) text));
            } catch (Exception e2) {
                c.setCellType(Cell.CELL_TYPE_STRING);
                c.setCellValue((String) text);
            }
        }
    } else if (text instanceof Double) {
        c.setCellValue((Double) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Integer) {
        c.setCellValue((Integer) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Date) {
        c.setCellValue((Date) text);
    }
    if (rowspan > 1 || colspan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(row, row + rowspan - 1, col, col + colspan - 1));
        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i > 0 || j > 0)
                    set(sheet, row + i, col + j, "", style);
            }
        }
    }
    return c;
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell setFormula(Sheet sheet, int row, int col, String text, Style style) {
    Row r = sheet.getRow(row);//ww  w.  j a v  a  2s .c o  m
    if (r == null)
        r = sheet.createRow(row);
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    c.setCellType(Cell.CELL_TYPE_STRING);
    try {
        c.setCellFormula(text);
    } catch (Exception e) {
        e.printStackTrace();
        c.setCellValue("Err. " + e.getMessage());
    }
    return c;
}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
    Class[] types = getTypes(table);
    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;//  w ww .  j  a  va2s .c  o m
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 15);
    style = wb.createCellStyle();
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 10);
    style = wb.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.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("th", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-border", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-double", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-right", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-bold", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-date", style);

    Sheet sheet = wb.createSheet();
    sheet.setFitToPage(true);

    Cell cell;

    int maxRows = 0;
    for (int r = 0; r < table.length; r++) {
        Row row = sheet.createRow(r);
        if (r == 0) {
            row.setRowStyle(styles.get("th"));
        }

        int rows = 1;
        for (int c = 0; c < table[r].length; c++) {
            cell = row.createCell(c);
            String s = table[r][c];
            if (s == null)
                continue;
            rows = Math.max(rows, s.split("\n").length);
            try {
                if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
                    cell.setCellStyle(styles.get("th"));
                    cell.setCellValue(s);

                } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
                    if (r == 0 && c == 0) {
                        cell.setCellStyle(styles.get("td"));
                    } else {
                        cell.setCellStyle(styles.get("th"));
                    }
                    cell.setCellValue(s);
                } else if (types[c] == Double.class) {
                    cell.setCellStyle(styles.get("td-double"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(s));
                } else if (types[c] == String.class) {
                    cell.setCellStyle(
                            styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(s);
                } else {
                    cell.setCellStyle(styles.get("td-right"));
                    cell.setCellValue(s);
                }
            } catch (Exception e) {
                cell.setCellStyle(styles.get("td"));
                cell.setCellValue(s);
            }
        }
        maxRows = Math.max(maxRows, rows);
        row.setHeightInPoints(rows * 16f);

    }

    // Add footer notes
    if (footerData.size() > 0) {
        Row row = sheet.createRow(table.length);
        row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
        cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
                row.getRowNum(), //last row  (0-based)
                0, //first column (0-based)
                table[0].length - 1 //last column  (0-based)
        ));
        //for ( String data : footerData ) {
        style = wb.createCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
        //}
    }
    footerData.clear();

    autoSizeColumns(sheet);
    if (table.length > 0) {
        for (int c = 0; c < table[0].length; c++) {
            if (sheet.getColumnWidth(c) > 10000)
                sheet.setColumnWidth(c, 3000);
        }
    }

    if (exportMode == ExportMode.HEADERS_TOPLEFT) {
        for (int r = 1; r < table.length; r++) {
            sheet.getRow(r).setHeightInPoints(maxRows * 16f);
        }
    }

    File reportFile = IOUtils.createTempFile("export_", ".xlsx");
    FileOutputStream out = new FileOutputStream(reportFile);
    wb.write(out);
    wb.close();
    out.close();
    Desktop.getDesktop().open(reportFile);
}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 * /* w w  w .  j av  a  2s.c  o m*/
 *
 * @param excelSheet sheet ?
 * @param sheet      sheet
 */
private static void writeHead(ExcelSheet excelSheet, Sheet sheet) {
    Row row = Optional.ofNullable(sheet.getRow(excelSheet.getHeadRowIndex()))
            .orElse(sheet.createRow(excelSheet.getHeadRowIndex()));

    for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping()
            .export().entrySet()) {
        ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue();
        Integer colIndex = attribute.getIndex();
        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        cell.setCellValue(attribute.getHead());
    }

}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 * ??/* w ww  . ja  v  a 2 s  .c  o  m*/
 *
 * @param cell ?
 * @param val  
 */
private static void writeCell(Cell cell, Object val) {
    if (val == null) {
        cell.setCellValue((String) null);
        return;
    }

    if (val instanceof Double) {
        cell.setCellValue((Double) val);
    } else {// String
        cell.setCellValue(Objects.toString(val));
    }
}