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

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

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

Introduction

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

Prototype

void setFont(Font font);

Source Link

Document

set the font for this style

Usage

From source file:com.seer.datacruncher.profiler.spring.ExporterController.java

License:Open Source License

public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    String type = CommonUtil.notNullValue(request.getParameter("exportaction"));
    String columns = CommonUtil.notNullValue(request.getParameter("exportcolumns"));
    String data = CommonUtil.notNullValue(request.getParameter("exportdata"));

    if (type.equals("csv")) {
        PrintWriter out = response.getWriter();
        response.setContentType("application/csv");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.csv"); // set the file
        // name to
        // whatever
        // required..
        out.println(columns.replace("&&&&&", ","));
        for (String strData : data.split("@@@@@")) {
            out.println(strData.replace("&&&&&", ","));
        }//from  w  w  w.  j a  v a  2  s .c o  m
        out.flush();
        out.close();
    } else if (type.equals("xml")) {
        PrintWriter out = response.getWriter();
        response.setContentType("text/xml");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.xml"); // set the file
        // name to
        // whatever
        // required..
        try {
            StringBuffer xml = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
            xml.append("<table><header>");
            String colArr[] = columns.split("&&&&&");
            for (String col : colArr) {
                xml.append("<columnName>" + col + "</columnName>");
            }
            xml.append("</header>");

            for (String strData : data.split("@@@@@")) {
                xml.append("<row>");
                int ind = 0;
                for (String val : strData.split("&&&&&")) {
                    xml.append("<" + colArr[ind] + ">" + val + "</" + colArr[ind] + "/>");
                    ind++;
                }
                xml.append("</row>");
            }
            xml.append("</table>");
            out.print(xml.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        out.flush();
        out.close();
    } else if (type.equals("excel")) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=analysis_data.xls");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        String colArr[] = columns.split("&&&&&");
        short ind = 0;
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        Row row = sheet.createRow(0);
        for (String col : colArr) {
            Cell cell = row.createCell(ind);
            cell.setCellValue(col);
            cell.setCellStyle(style);
            ind++;
        }
        ind = 1;
        for (String strData : data.split("@@@@@")) {
            Row valRow = sheet.createRow(ind);
            short cellInd = 0;
            for (String val : strData.split("&&&&&")) {
                valRow.createCell(cellInd).setCellValue(val);
                cellInd++;
            }
            ind++;
        }

        // Write the output to a file
        OutputStream resOout = response.getOutputStream();
        wb.write(resOout);
        resOout.close();

    }

    return null;
}

From source file:com.shiyq.poi.HSSFTest.java

public static boolean createExcel(String excelName) {
    boolean created = false;
    Workbook wb = new HSSFWorkbook();
    Font font = wb.createFont();//ww w . j ava 2  s  .c  om
    font.setBold(true);
    CellStyle headStyle = wb.createCellStyle();
    headStyle.setFont(font);

    Sheet sheet = wb.createSheet("20165???");
    String[] head = { "??", "?", "??", "???", "????",
            "????", "?", "??", "", "",
            "??" };
    String[] code = { "card_no", "card_type", "spread_time", "spread_emp_no", "spread_emp_name", "owner_name",
            "plate", "blance", "start_time", "end_time", "month_money" };

    List<Map<String, Object>> list = setList();
    setSheet(sheet, list, head, headStyle, code);
    //
    int startRow = 4;
    int endRow = 8;
    int startColumn = head.length + 4;
    int endColumn = head.length + 8;

    String describe = "1?" + (new Date().toString()) + "\n";
    describe += "2.\n";
    describe += "3.?2016-4-42016-5-4";
    CellStyle descStyle = wb.createCellStyle();
    descStyle.setAlignment(CellStyle.ALIGN_LEFT);
    descStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    setSheet(sheet, startRow, endRow, startColumn, endColumn, describe, descStyle);

    Sheet sheet1 = wb.createSheet("20166???");
    setSheet(sheet1, list, head, headStyle, code);
    setSheet(sheet1, startRow, endRow, startColumn, endColumn, describe, descStyle);
    try {
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            wb.write(fileOut);
            created = true;
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
    }
    return created;
}

From source file:com.siemens.sw360.exporter.ExcelExporter.java

License:Open Source License

/**
 * Create header style, same has cell style but with bold font
 *//*w  w  w  .j a v a 2  s .c om*/
private static CellStyle createHeaderStyle(Workbook workbook) {
    CellStyle headerCellStyle = createCellStyte(workbook);
    Font font = workbook.createFont();
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    headerCellStyle.setFont(font);
    return headerCellStyle;
}

From source file:com.tecnosur.util.Excel.java

public void ExportarMatriculados(CControlmatricula datos, String aula) {
    try { // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet = wb.createSheet("matriculados");

        // quito las lineas del libro para darle un mejor acabado
        sheet.setDisplayGridlines(false);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));

        // creo una nueva fila
        Row trow = sheet.createRow((short) 1);

        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "ALUMNOS MATRICULADOS");

        Row trow2 = sheet.createRow((short) 3);
        createTituloCell(wb, trow2, 0, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, "AULA: " + aula);

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

        // Creo las celdas de mi fila, se puede poner un diseo a la celda_codigo
        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CDIGO", true, true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "ALUMNO", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "TIPO PAGO", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CRONO. PAGO", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "N CUOT.", true, true);
        createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. MATR.", true, true);
        createCell(wb, row, 6, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. CUOT.", true, true);

        // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //la celda_codigo se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.setColumnWidth(0, 60 * 40);
        ssheet.setColumnWidth(1, 255 * 40);
        ssheet.setColumnWidth(2, 90 * 40);
        ssheet.autoSizeColumn(3);//from   w w w  .jav  a 2s  . c o  m
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);

        HSSFFont cellFont = wb.createFont();

        cellFont.setFontHeightInPoints((short) 8);
        cellFont.setFontName(HSSFFont.FONT_ARIAL);

        CellStyle cellStyle = wb.createCellStyle();

        cellStyle.setFont(cellFont);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor((short) 8);

        for (int i = 0; i < datos.size(); i++) {
            row = sheet.createRow((short) i + 6);
            Cell celda_codigo = row.createCell(0);

            celda_codigo.setCellStyle(cellStyle);
            celda_codigo.setCellValue(datos.get(i).getIdalumno());

            Cell celda_alumno = row.createCell(1);
            celda_alumno.setCellStyle(cellStyle);
            celda_alumno.setCellValue(datos.get(i).getNom_completo());

            Cell celda_tipopago = row.createCell(2);
            celda_tipopago.setCellStyle(cellStyle);
            celda_tipopago.setCellValue(datos.get(i).getTipopagante());

            Cell celda_crono = row.createCell(3);
            celda_crono.setCellStyle(cellStyle);
            celda_crono.setCellValue(datos.get(i).getDesc_cronopagtable());

            Cell celda_ncuota = row.createCell(4);
            celda_ncuota.setCellStyle(cellStyle);
            celda_ncuota.setCellValue(datos.get(i).getNum_cuota());

            Cell celda_matricula = row.createCell(5);
            celda_matricula.setCellStyle(cellStyle);
            celda_matricula.setCellValue(datos.get(i).getMonmatricula());

            Cell celda_moncuota = row.createCell(6);
            celda_moncuota.setCellStyle(cellStyle);
            celda_moncuota.setCellValue(datos.get(i).getMoncuota());
        }

        String strRuta = "TYSAC_Matriculados.xls";
        FileOutputStream fileOut = new FileOutputStream(strRuta);
        wb.write(fileOut);

        fileOut.close();
        Runtime.getRuntime().exec("cmd /c start " + strRuta);

    } catch (IOException e) {
        System.out.println("Error de escritura");
        e.printStackTrace();
    }
}

From source file:com.tecnosur.util.Excel.java

private static void createTituloCell(HSSFWorkbook wb, Row row, int column, short halign, short valign,
        String strContenido) {// ww  w  . j a  v a2s.  c o  m

    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);
    cell.setCellValue(ch.createRichTextString(strContenido));

    HSSFFont cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 11);
    cellFont.setFontName(HSSFFont.FONT_ARIAL);
    cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cellStyle.setFont(cellFont);
    cell.setCellStyle(cellStyle);

}

From source file:com.tecnosur.util.Excel.java

private static void createCell(HSSFWorkbook wb, Row row, int column, short halign, short valign,
        String strContenido, boolean booBorde, boolean booCabecera) {
    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);//  w  ww  .j a  v  a  2  s  . com
    HSSFFont cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 8);
    cellFont.setFontName(HSSFFont.FONT_ARIAL);
    cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    cell.setCellValue(ch.createRichTextString(strContenido));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cellStyle.setFont(cellFont);
    if (booBorde) {
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor((short) 8);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor((short) 8);
    }
    if (booCabecera) {
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor((short) 8);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor((short) 8);

        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }
    cell.setCellStyle(cellStyle);
}

From source file:com.tm.hiber.service.util.DatabaseUtil.java

/**
 *
 * @param objFile//www.jav a  2 s . c  o  m
 * @param objJTable
 * @return number of records exported
 */
public int exportData(File objFile, JTable objJTable) throws FileNotFoundException, IOException {
    int response = 0;

    if (objFile == null) {
        return response;
    }

    DefaultTableModel tm = (DefaultTableModel) objJTable.getModel();
    Object[] rows = tm.getDataVector().toArray();

    JTableHeader columnNames = objJTable.getTableHeader();
    TableColumnModel columnModel = columnNames.getColumnModel();
    int columnCount = columnModel.getColumnCount();
    Vector<String> vecColumnNames = new Vector<String>();
    for (int c = 0; c < columnCount; c++) {
        vecColumnNames.add(columnModel.getColumn(c).getHeaderValue().toString());
    }

    HSSFWorkbook exportReadyWorkbook = new HSSFWorkbook();
    HSSFSheet dataSheet = exportReadyWorkbook.createSheet(getExportSheetName());

    if (vecColumnNames.size() > 0) {
        int columnCounter = 0;
        Row objHSSFColumnName = dataSheet.createRow(0);

        for (String strColumnName : vecColumnNames) {
            /* Set Header CSS */

            Cell objHSSFCell = objHSSFColumnName.createCell(columnCounter);
            objHSSFCell.setCellValue(strColumnName);
            CellStyle csll = exportReadyWorkbook.createCellStyle();
            Font objFont = exportReadyWorkbook.createFont();
            objFont.setFontName("Calibri");
            objFont.setColor(IndexedColors.BLACK.index);
            objFont.setBold(true);
            csll.setFont(objFont);
            csll.setFillBackgroundColor(HSSFColor.YELLOW.index);
            csll.setFillForegroundColor(HSSFColor.YELLOW.index);
            csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            csll.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //csll.setWrapText(true);
            objHSSFCell.setCellStyle(csll);
            columnCounter++;
        }
    }

    if (rows != null && rows.length > 0) {
        /* Set Data into Sheet */
        for (int i = 0; i < rows.length; i++) {
            Vector objCellsData = (Vector) rows[i];
            Row objHSSFRow = dataSheet.createRow(i + 1);
            if (objCellsData != null && objCellsData.size() > 0) {
                for (int j = 0; j < objCellsData.size(); j++) {

                    /* Set Cell Data CSS */

                    Cell objHSSFCell = objHSSFRow.createCell(j);
                    CellStyle csll = exportReadyWorkbook.createCellStyle();
                    Font objFont = exportReadyWorkbook.createFont();
                    objFont.setColor(IndexedColors.BLACK.index);
                    objFont.setBold(false);
                    objFont.setFontName("Calibri");
                    csll.setFont(objFont);
                    csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    csll.setFillBackgroundColor(IndexedColors.WHITE.index);
                    csll.setFillForegroundColor(IndexedColors.WHITE.index);

                    csll.setAlignment(HSSFCellStyle.ALIGN_CENTER);

                    //csll.setWrapText(true);
                    csll.setBorderBottom(CellStyle.BORDER_THIN);
                    csll.setBorderTop(CellStyle.BORDER_THIN);

                    csll.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index);
                    csll.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index);

                    objHSSFCell.setCellStyle(csll);
                    Object cellData = objCellsData.get(j);
                    objHSSFCell.setCellValue((String) cellData);
                }
            }
        }

        for (int i = 0; i < columnCount; i++) {
            if (i == 2) {
                dataSheet.setColumnWidth(i, 30 * 256);
            } else {
                dataSheet.autoSizeColumn(i);
            }
        }

        /* Write File */
        FileOutputStream objFileOutputStream = new FileOutputStream(objFile);
        exportReadyWorkbook.write(objFileOutputStream);
        objFileOutputStream.flush();
        objFileOutputStream.close();
        response = rows.length;
    }

    return response;
}

From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;//ww  w  .ja va 2  s  . c o  m

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new HSSFWorkbook();

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java

License:Open Source License

@SuppressWarnings("resource")
public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception {
    String fileName = null;// w  w  w.  ja v  a 2 s .  c om

    OutputStream out = null;

    if (params.getOutputStream() == null) {
        fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName(
                dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true));

        params.setRealFileName(fileName);

        out = new FileOutputStream(fileName);

        if (params.getTransactionMonitor() != null)
            params.getTransactionMonitor().addFile(fileName);
    } else
        out = params.getOutputStream();

    params.setOut(out);

    Workbook workbook = new SXSSFWorkbook(100);

    params.setWorkbook(workbook);

    Sheet sheet = workbook
            .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName());

    params.setSheet(sheet);

    Font labelFont = workbook.createFont();
    labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle labelCellStyle = workbook.createCellStyle();
    labelCellStyle.setFont(labelFont);

    DataFormat dateTimeFormat = workbook.createDataFormat();
    CellStyle dateTimeCellStyle = workbook.createCellStyle();
    dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat()));

    params.setDateTimeCellStyle(dateTimeCellStyle);

    DataFormat dateFormat = workbook.createDataFormat();
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat()));

    params.setDateCellStyle(dateCellStyle);

    DataFormat timeFormat = workbook.createDataFormat();
    CellStyle timeCellStyle = workbook.createCellStyle();
    timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat()));

    params.setTimeCellStyle(timeCellStyle);

    // column names
    Row excelRow = sheet.createRow(0);

    // metadata
    int col = 0;
    for (FieldDef fieldDef : dataSet.getFields().getList()) {
        if (!fieldDef.isVisible())
            continue;

        Cell labelCell = excelRow.createCell(col++);
        labelCell.setCellStyle(labelCellStyle);
        labelCell.setCellValue(fieldDef.getName());
    }

    params.setPrePersistOccured(true);
}

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

License:Open Source License

/**
 * Creates a CellStyle to be used with hyperlinks
 * //w w  w  .  ja  va 2  s  .  c o  m
 * @return A new hyperlink CellStyle
 */
public CellStyle createHyperlinkCellStyle() {
    Workbook wb = spreadsheet.getWorkbook();
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setFontName(defaultFont.getFontName());
    hlink_font.setFontHeightInPoints(defaultFontHeightInPoints);
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    return hlink_style;
}