Example usage for org.apache.poi.xssf.streaming SXSSFWorkbook createCellStyle

List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.streaming.*
  5. SXSSFWorkbook
  6. createCellStyle

Introduction

In this page you can find the example usage for org.apache.poi.xssf.streaming SXSSFWorkbook createCellStyle.

Prototype

@Override
public CellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:cfdi.clases.db.DerbyUtilities.java

License:Open Source License

/**
 * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se
 * haya utilizado en la interface grfica
 * /* w  ww . j a v a 2  s.com*/
 * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE
 * @param nombre nombre del archivo 
 * @param path directorio donde se va a crear el archivo de excel
 * @return the boolean
 */
public boolean exportarExcel(String query, String nombre, String path) {
    Connection connection = null;
    Statement st = null;
    ResultSet rs = null;
    boolean respuesta = false;
    BoneCP connectionPool = null;
    try {
        Class.forName(propiedades.getProperty("DB_DRIVER"));
        // setup the connection pool
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
        config.setUsername(propiedades.getProperty("DB_USER"));
        config.setPassword(propiedades.getProperty("DB_PASSWORD"));
        config.setMinConnectionsPerPartition(5);
        config.setMaxConnectionsPerPartition(10);
        config.setPartitionCount(1);
        connectionPool = new BoneCP(config); // setup the connection pool
        FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx");
        connection = connectionPool.getConnection(); // fetch a connection

        if (connection != null) {
            st = connection.createStatement();
            rs = st.executeQuery(query);
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
            Sheet sheet = workbook.createSheet(nombre);
            int rownum = 0;
            Row row = sheet.createRow(rownum++);
            CellStyle stylec = workbook.createCellStyle();
            stylec.setBorderBottom(CellStyle.BORDER_THIN);
            stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            Font fontc = workbook.createFont();
            fontc.setBoldweight(Font.BOLDWEIGHT_BOLD);
            stylec.setFont(fontc);
            for (int i = 1; i <= count; i++) {
                row.createCell(i).setCellValue(metaData.getColumnName(i));
                row.getCell(i).setCellStyle(stylec);
            }
            while (rs.next()) {
                Row rowh = sheet.createRow(rownum++);
                for (int i = 1; i <= count; i++) {
                    if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT")
                            || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED"))
                        rowh.createCell(i).setCellValue(rs.getInt(i));
                    else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE"))
                        rowh.createCell(i).setCellValue(rs.getDouble(i));
                    else
                        rowh.createCell(i).setCellValue(rs.getString(i));
                }
            }
            /*if(rownum<5000){
            for (int i = 1; i <= count; i++)
                sheet.autoSizeColumn(i); 
            }*/
            try {
                workbook.write(fileOut);
                fileOut.flush();
                fileOut.close();

            } catch (FileNotFoundException e) {
                System.out.println("Error: export 1");
            } catch (IOException e) {
                System.out.println("Error: export 2");
            }
            respuesta = true;
            connectionPool.shutdown();
        }
    } catch (SQLException e) {
        System.out.println("Error: insertDatos 3");
        logger.log(Level.SEVERE, null, e);
    } catch (ClassNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (Exception ex) {
        System.out.println("Error: insertDatos 5");
        logger.log(Level.SEVERE, null, ex);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("Error: insertDatos 4");
                logger.log(Level.SEVERE, null, e);
            }
        }
    }
    return respuesta;
}

From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java

License:Apache License

/**
 * Translate styling to workbook CellStyle.
 *
 * @param stylesUsed tracks the styles that have been used in the workbook; it will be updated
 * @param fontsUsed tracks the fonts that have been used in the workbook; it may be updated
 * @param colorsUsed tracks the colors that have been used in the workbook; it may be updated
 *//*from   ww  w  .  j  a  va2 s . co  m*/
public CellStyle getCellStyle(SXSSFWorkbook book, Map<StylesWithFormatting, XSSFCellStyle> stylesUsed,
        Map<FontStyles, XSSFFont> fontsUsed, Map<Integer, XSSFColor> colorsUsed) {

    XSSFCellStyle cellStyle = stylesUsed.get(this);
    if (cellStyle != null) {
        return cellStyle;
    }

    cellStyle = (XSSFCellStyle) book.createCellStyle();
    cellStyle.cloneStyleFrom(book.getCellStyleAt(formatIndex));

    if (styles.bottomBorder.style != null) {
        cellStyle.setBorderBottom(resolveBorderStyle(styles.bottomBorder));
    }
    if (styles.leftBorder.style != null) {
        cellStyle.setBorderLeft(resolveBorderStyle(styles.leftBorder));
    }
    if (styles.rightBorder.style != null) {
        cellStyle.setBorderRight(resolveBorderStyle(styles.rightBorder));
    }
    if (styles.topBorder.style != null) {
        cellStyle.setBorderTop(resolveBorderStyle(styles.topBorder));
    }

    if (styles.bottomBorder.color != null) {
        cellStyle.setBottomBorderColor(getColor(styles.bottomBorder.color, book, colorsUsed));
    }
    if (styles.leftBorder.color != null) {
        cellStyle.setLeftBorderColor(getColor(styles.leftBorder.color, book, colorsUsed));
    }
    if (styles.rightBorder.color != null) {
        cellStyle.setRightBorderColor(getColor(styles.rightBorder.color, book, colorsUsed));
    }
    if (styles.topBorder.color != null) {
        cellStyle.setTopBorderColor(getColor(styles.topBorder.color, book, colorsUsed));
    }

    if (styles.backgroundColor != null) {
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(getColor(styles.backgroundColor, book, colorsUsed));
    }

    if (styles.textAlign != null) {
        cellStyle.setAlignment(styles.textAlign);
    }

    if (!styles.font.areDefault()) {
        cellStyle.setFont(getFont(styles.font, book, fontsUsed, colorsUsed));
    }

    stylesUsed.put(this, cellStyle);

    return cellStyle;
}

From source file:com.rapidminer.operator.io.ExcelExampleSetWriter.java

License:Open Source License

/**
 * Writes the provided {@link ExampleSet} to a XLSX formatted data sheet.
 *
 * @param wb//from   w ww .  j a  va  2 s. c o m
 *            the workbook to use
 * @param sheet
 *            the excel sheet to write to.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param exampleSet
 *            the exampleSet to write
 * @param op
 *            needed for checkForStop
 * @throws ProcessStoppedException
 *             if the process was stopped by the user.
 * @throws WriteException
 */
private static void writeXLSXDataSheet(SXSSFWorkbook wb, Sheet sheet, String dateFormat, String numberFormat,
        ExampleSet exampleSet, Operator op) throws WriteException, ProcessStoppedException {

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

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFont(headerFont);

    // create the header
    Iterator<Attribute> a = exampleSet.getAttributes().allAttributes();
    int columnCounter = 0;
    int rowCounter = 0;
    Row headerRow = sheet.createRow(rowCounter);
    while (a.hasNext()) {
        Attribute attribute = a.next();
        Cell headerCell = headerRow.createCell(columnCounter);
        headerCell.setCellValue(attribute.getName());
        headerCell.setCellStyle(headerStyle);
        columnCounter++;
    }
    rowCounter++;

    // body font
    Font bodyFont = wb.createFont();
    bodyFont.setBold(false);

    CreationHelper createHelper = wb.getCreationHelper();

    // number format
    CellStyle numericalStyle = wb.createCellStyle();
    numericalStyle.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat));
    numericalStyle.setFont(bodyFont);

    // date format
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));
    dateStyle.setFont(bodyFont);

    // create nominal cell style
    CellStyle nominalStyle = wb.createCellStyle();
    nominalStyle.setFont(bodyFont);

    // fill body
    for (Example example : exampleSet) {

        // create new row
        Row bodyRow = sheet.createRow(rowCounter);

        // iterate over attributes and save examples
        a = exampleSet.getAttributes().allAttributes();
        columnCounter = 0;
        while (a.hasNext()) {
            Attribute attribute = a.next();
            Cell currentCell = bodyRow.createCell(columnCounter);
            if (!Double.isNaN(example.getValue(attribute))) {
                if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) {
                    Date dateValue = example.getDateValue(attribute);
                    currentCell.setCellValue(dateValue);
                    currentCell.setCellStyle(dateStyle);
                } else if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.NUMERICAL)) {
                    double numericalValue = example.getNumericalValue(attribute);
                    currentCell.setCellValue(numericalValue);
                    currentCell.setCellStyle(numericalStyle);
                } else {
                    currentCell.setCellValue(
                            stripIfNecessary(replaceForbiddenChars(example.getValueAsString(attribute))));
                    currentCell.setCellStyle(nominalStyle);
                }
            }
            columnCounter++;
        }
        rowCounter++;

        // checkForStop every 100 examples
        if (op != null && rowCounter % 100 == 0) {
            op.checkForStop();
        }
    }
}

From source file:com.repository2excel.Main.java

License:Apache License

/**
 * @param args/*from  w w w  .j  a va  2  s.  c  om*/
 */
@SuppressWarnings("deprecation")
public static void main(String[] args) {
    String xmlRepositoryDefFilePath = "";

    /** Read user input */
    Scanner scnr = new Scanner(System.in);
    System.out.println("Enter fully qualified path to customCatalog.xml:");
    try {
        xmlRepositoryDefFilePath = scnr.next();
    } catch (InputMismatchException e) {
        // TODO:
    } finally {
        scnr.close();
    }

    RepositoryDefinitionReader reader = new RepositoryDefinitionReader();
    System.out.println("Begin reading XML Repository definition file...");
    HashSet<Item> items = reader.loadRepositoryDefinition(new File(xmlRepositoryDefFilePath));
    System.out.println("Finished reading XML file!");
    if (items != null && items.size() > 0) {
        System.out.println("Preparing to export " + items.size() + " items into Excel Spreadsheet...");
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sh = wb.createSheet();

        /** Create cell styles */
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);

        Iterator<Item> iter = items.iterator();
        int rownum = 0;
        while (iter.hasNext()) {
            Item item = iter.next();
            Row row = sh.createRow(rownum);
            row.createCell(0, CellType.STRING).setCellValue("Item");
            row.createCell(1, CellType.STRING).setCellValue(item.getName());
            rownum++;

            row = sh.createRow(rownum);
            row.createCell(0, CellType.STRING).setCellValue("Query Cache Size");
            row.createCell(1, CellType.STRING).setCellValue(item.getQueryCacheSize());
            rownum++;

            row = sh.createRow(rownum);
            row.createCell(0, CellType.STRING).setCellValue("Item Cache Size");
            row.createCell(1, CellType.STRING).setCellValue(item.getItemCacheSize());
            rownum++;
            HashSet<Property> properties = item.getProperties();
            if (properties != null && properties.size() > 0) {
                Cell cell;
                row = sh.createRow(rownum);
                cell = row.createCell(0, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Property");

                cell = row.createCell(1, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Type");

                cell = row.createCell(2, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Readable");

                cell = row.createCell(3, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Writable");

                cell = row.createCell(4, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Hidden");

                cell = row.createCell(5, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Table");

                cell = row.createCell(6, CellType.STRING);
                cell.setCellStyle(style);
                cell.setCellValue("Column");

                Iterator<Property> pIter = properties.iterator();
                while (pIter.hasNext()) {
                    rownum++;
                    row = sh.createRow(rownum);
                    Property property = pIter.next();
                    /** 0. Name */
                    cell = row.createCell(0, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.getName());

                    /** 1. Data Type */
                    cell = row.createCell(1, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.getDataType());

                    /** 2. Is Readable */
                    cell = row.createCell(2, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.isReadable());

                    /** 3. Is Writable */
                    cell = row.createCell(3, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.isWriteable());

                    /** 4. Is Hidden */
                    cell = row.createCell(4, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.isHidden());

                    /** 5. Table */
                    cell = row.createCell(5, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.getTable());

                    /** 6. Column */
                    cell = row.createCell(6, CellType.STRING);
                    cell.setCellStyle(style);
                    cell.setCellValue(property.getColumn());
                }
            }
            rownum++;
            rownum++;
        }

        try {
            File f = new File("test.xlsx");
            FileOutputStream out = new FileOutputStream(f);
            wb.write(out);
            out.close();
            wb.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // dispose of temporary files backing this workbook on disk
            wb.dispose();
        }
    }
}

From source file:com.romeikat.datamessie.core.base.util.ExcelSheet.java

License:Open Source License

public ExcelSheet(final SXSSFWorkbook workbook, String sheetname) {
    // Sheet/*from   ww  w  . j  a  va  2s  . c om*/
    sheetname = normalizeSheetname(sheetname);
    sheet = workbook.createSheet(sheetname);
    // Date format
    final short dateFormat = workbook.createDataFormat().getFormat("dd.MM.yyyy");
    dateFormatCellStyle = workbook.createCellStyle();
    dateFormatCellStyle.setDataFormat(dateFormat);
    // Double number format
    final short doubleFormat = workbook.createDataFormat().getFormat("0.00");
    doubleFormatCellStyle = workbook.createCellStyle();
    doubleFormatCellStyle.setDataFormat(doubleFormat);
    // Indices
    currentRowIndex = 0;
    columnCursorIndex = 0;
    // Create first row
    currentRow = sheet.createRow(currentRowIndex);
}

From source file:com.softtek.mdm.web.institution.UserController.java

/**
  * /*from   w w w .ja  va2  s.  c  o  m*/
  * 
  * @param request
  * @param response
  * @throws Exception
  */
@RequestMapping(value = "/exportuser", method = RequestMethod.GET)
@ResponseBody
public void exportuser(String groupid, HttpServletRequest request, HttpServletResponse response,
        HttpSession session) throws Exception {
    @SuppressWarnings("unchecked")
    List<StructureModel> list = (List<StructureModel>) session
            .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString());
    Integer id = Integer.parseInt(groupid);
    String name = structureService.queryNameById(id);
    request.setAttribute("name", name);
    List<Integer> idList = new ArrayList<Integer>();
    structureService.queryAllChildrenId(id, list, idList);
    idList.add(id);
    // ?ID?
    List<UserExportModel> userExportModel = userService.exportUsersById(idList);
    // ??
    for (int i = 0; i < idList.size(); i++) {
        StructureModel temp = structureService.getParents(idList.get(i));
        String belongStr = temp.getName();
        if (temp != null) {
            for (int j = 0; j < userExportModel.size(); j++) {
                if (userExportModel.get(j).getGroup_id().equals(idList.get(i))) {
                    // String belongStr = temp.getName();
                    while (temp.getParent() != null) {
                        belongStr = StringUtil.insert(belongStr, temp.getParent().getName() + "/");
                        temp = temp.getParent();
                    }
                    userExportModel.get(j).setGroup_name(belongStr);
                }
            }
        }
    }

    ExportData exportData = new ExportData();
    String headers[][] = {
            { messageSource.getMessage("web.institution.usercontroller.export.users.label1", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label2", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label3", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label4", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label5", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label6", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label7", null,
                    LocaleContextHolder.getLocale()), "String" } };
    SXSSFWorkbook workbook = exportData.getwb(headers, "sheet1");
    int currentRow = 1;
    Sheet sheet = workbook.getSheetAt(0);
    CellStyle cellStyle = workbook.createCellStyle();
    // cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    for (int i = 0; i < userExportModel.size(); i++) {
        Row row = sheet.createRow(currentRow);
        for (int j = 0; j < 7; j++) {
            Cell cell = row.createCell(j);
            if (j == 0) {
                cell.setCellValue(userExportModel.get(i).getGroup_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 1) {
                cell.setCellValue(userExportModel.get(i).getUser_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 2) {
                cell.setCellValue(userExportModel.get(i).getReal_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 3) {
                cell.setCellValue(userExportModel.get(i).getPhone());
                cell.setCellStyle(cellStyle);
            }
            if (j == 4) {
                cell.setCellValue(userExportModel.get(i).getEmail());
                cell.setCellStyle(cellStyle);
            }
            if (j == 5) {
                cell.setCellValue(userExportModel.get(i).getMark());
                cell.setCellStyle(cellStyle);
            }
            if (j == 6) {
                String sex = messageSource.getMessage("web.institution.usercontroller.sex.woman", null,
                        LocaleContextHolder.getLocale());

                if (userExportModel.get(i).getGender().equals("1")) {
                    sex = messageSource.getMessage("web.institution.usercontroller.sex.man", null,
                            LocaleContextHolder.getLocale());
                }

                cell.setCellValue(sex);
                cell.setCellStyle(cellStyle);
            }
        }
        currentRow++;
    }
    // XSSFWorkbook workbook = new XSSFWorkbook();
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-msdownload");
    OutputStream os = null;
    String fileName = messageSource.getMessage("web.institution.usercontroller.export.users.model", null,
            LocaleContextHolder.getLocale());
    response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    os = response.getOutputStream();
    workbook.write(os);
    os.flush();
    os.close();
}

From source file:de.ks.idnadrev.expimp.xls.ReflectionColumn.java

License:Apache License

@Override
public CellStyle getCellStyle(SXSSFWorkbook workbook) {
    CreationHelper creationHelper = workbook.getCreationHelper();
    if (LocalDateTime.class.isAssignableFrom(fieldType)) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss"));
        return cellStyle;
    } else if (LocalDate.class.isAssignableFrom(fieldType)) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd"));
        return cellStyle;
    }// w w w  .ja  v  a2  s  . co m
    return null;
}

From source file:edu.harvard.hms.dbmi.bd2k.irct.ws.rs.resultconverter.XSLXTabularDataConverter.java

License:Mozilla Public License

@Override
public StreamingOutput createStream(final Result result) {
    StreamingOutput stream = new StreamingOutput() {
        @Override// w w  w  .  j  a va 2s  .co m
        public void write(OutputStream outputStream) throws IOException, WebApplicationException {
            ResultSet rs = null;
            SXSSFWorkbook wb = null;
            try {
                rs = (ResultSet) result.getData();
                rs.load(result.getResultSetLocation());

                wb = new SXSSFWorkbook(100);
                // Create Sheet
                Sheet sh = wb.createSheet("Results");

                // Create Header
                CellStyle headerStyle = wb.createCellStyle();
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                headerStyle.setFont(font);

                Row headerRow = sh.createRow(0);
                for (int i = 0; i < rs.getColumnSize(); i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellStyle(headerStyle);
                    cell.setCellValue(rs.getColumn(i).getName());
                }

                // Add data
                rs.beforeFirst();
                int rowNum = 1;
                while (rs.next()) {
                    Row row = sh.createRow(rowNum);
                    for (int i = 0; i < rs.getColumnSize(); i++) {
                        String value = rs.getString(i);
                        Cell cell = row.createCell(i);
                        if (value != null) {
                            cell.setCellValue(rs.getString(i));
                        }
                    }
                    rowNum++;
                }
                wb.write(outputStream);

            } catch (ResultSetException | PersistableException e) {
                log.info("Error creating XSLX Stream: " + e.getMessage());
            } finally {
                if (wb != null) {
                    wb.close();
                }
                if (rs != null && !rs.isClosed()) {
                    try {
                        rs.close();
                    } catch (ResultSetException e) {
                        e.printStackTrace();
                    }
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            }
        }
    };
    return stream;
}

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

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from w w w  .j a  v  a2s . c om
@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:excelmasivo.ExcelMasivo.java

/**
 * @param args the command line arguments
 *///from   w ww .java2s  . c om
public static void main(String[] args) {
    String driver = "oracle.jdbc.OracleDriver";
    String user = "DRKL";
    String pass = "DRKL";
    String url = "jdbc:oracle:thin:@localhost:1521:XE";
    String query = "SELECT * FROM PRODUCTOS ORDER BY TO_NUMBER(SUBSTR(CODIGO_PRODUCTO,7))";
    Connection con;
    Statement st;
    ResultSet rs;
    ResultSetMetaData rsm;

    SXSSFWorkbook libro = new SXSSFWorkbook();
    SXSSFSheet hoja = libro.createSheet("Reporte");
    SXSSFRow fila;
    SXSSFCell celda;
    FileOutputStream out;
    int x = 0;

    CellStyle cs = libro.createCellStyle();
    cs.getFillForegroundColor();
    Font f = libro.createFont();
    //f.setBoldweight(Font.BOLDWEIGHT_BOLD);
    f.setFontHeightInPoints((short) 12);
    cs.setFont(f);

    try {
        Class.forName(driver);
        con = DriverManager.getConnection(url, user, pass);
        st = con.createStatement();
        rs = st.executeQuery(query);
        rsm = rs.getMetaData();
        while (rs.next()) {
            //crear la fila
            fila = hoja.createRow(x++);
            for (int i = 1; i <= rsm.getColumnCount(); i++) {
                //recorrer las columnas
                celda = fila.createCell(i);
                celda.setCellStyle(cs);
                celda.setCellValue(rs.getString(i));
                //System.out.print(rs.getString(i));
            }
            //System.out.println();                
            if (x % 50000 == 0) {
                System.out.println("Se procesaron:" + x);
            }
        }

        out = new FileOutputStream(new File("D:\\java\\Productos_" + GregorianCalendar.MILLISECOND + ".xlsx"));
        libro.write(out);
        out.close();
        System.out.println("Archivo generado con exito");
    } catch (ClassNotFoundException | SQLException | FileNotFoundException ex) {
        Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex);
    }

}