Example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormats

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormats.

Prototype

public static List<String> getBuiltinFormats() 

Source Link

Usage

From source file:org.opentaps.common.util.UtilCommon.java

License:Open Source License

/**
 * Creates an Excel document with a given column name list, and column data list.
 * The String objects in the column name list are used as Map keys to look up the corresponding
 * column header and data. The column data to be exported is a List of Map objects where
 * the first Map element contains column headers, and the rest has all the column data.
 * @param workBookName a String object as Excel file name
 * @param workSheetName a String object as the name of the Excel sheet
 * @param columnNameList a List of String objects as column names, they usually correspond to entity field names
 * @param data a List of Map objects to be exported where the first Map element contains column headers,
 *        and the rest has all the column data.
 * @throws IOException if an error occurs
 *///  w  w w.j a v  a2  s.c o  m
public static void saveToExcel(final String workBookName, final String workSheetName,
        final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException {
    if (StringUtils.isEmpty(workBookName)) {
        throw new IllegalArgumentException("Argument workBookName can't be empty");
    }

    if (StringUtils.isEmpty(workSheetName)) {
        throw new IllegalArgumentException("Argument workSheetName can't be empty");
    }

    if (columnNameList == null || columnNameList.isEmpty()) {
        throw new IllegalArgumentException("Argument columnNameList can't be empty");
    }

    // the data list should have at least one element for the column headers
    if (data == null || data.isEmpty()) {
        throw new IllegalArgumentException("Argument data can't be empty");
    }

    FileOutputStream fileOut = new FileOutputStream(new File(workBookName));
    assert fileOut != null;

    HSSFWorkbook workBook = new HSSFWorkbook();
    assert workBook != null;

    HSSFSheet workSheet = workBook.createSheet(workSheetName);
    assert workSheet != null;

    // create the header row

    HSSFRow headerRow = workSheet.createRow(0);
    assert workSheet != null;

    HSSFFont headerFont = workBook.createFont();
    assert headerFont != null;

    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setColor(HSSFColor.BLACK.index);

    HSSFCellStyle headerCellStyle = workBook.createCellStyle();
    assert headerCellStyle != null;

    headerCellStyle.setFont(headerFont);

    // the first data list element should always be the column header map
    Map<String, Object> columnHeaderMap = data.get(0);

    if (columnHeaderMap != null) {
        for (short i = 0; i < columnNameList.size(); i++) {
            HSSFCell cell = headerRow.createCell(i);
            assert cell != null;

            cell.setCellStyle(headerCellStyle);

            Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i));
            if (columnHeaderTitle != null) {
                cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString()));
            }
        }
    }

    // create data rows

    // column data starts from the second element
    if (data.size() > 1) {

        // Create the style used for dates.
        HSSFCellStyle dateCellStyle = workBook.createCellStyle();
        String dateFormat = "mm/dd/yyyy hh:mm:ss";
        HSSFDataFormat hsfDateFormat = workBook.createDataFormat();
        short dateFormatIdx = hsfDateFormat.getFormat(dateFormat);
        if (dateFormatIdx == -1) {
            Debug.logWarning("Date format [" + dateFormat
                    + "] could be found or created, try one of the pre-built instead:"
                    + HSSFDataFormat.getBuiltinFormats(), MODULE);
        }
        dateCellStyle.setDataFormat(dateFormatIdx);

        for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) {
            Map<String, Object> rowDataMap = data.get(dataRowIndex);
            if (rowDataMap == null) {
                continue;
            }

            HSSFRow dataRow = workSheet.createRow(dataRowIndex);
            assert dataRow != null;

            for (short i = 0; i < columnNameList.size(); i++) {
                HSSFCell cell = dataRow.createCell(i);
                assert cell != null;

                Object cellData = rowDataMap.get(columnNameList.get(i));
                if (cellData != null) {
                    // Note: dates are actually numeric values in Excel and so the cell need to have
                    //  a special style set so it actually displays as a date
                    if (cellData instanceof Calendar) {
                        cell.setCellStyle(dateCellStyle);
                        cell.setCellValue((Calendar) cellData);
                    } else if (cellData instanceof Date) {
                        cell.setCellStyle(dateCellStyle);
                        cell.setCellValue((Date) cellData);
                    } else if (cellData instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) cellData).doubleValue());
                    } else if (cellData instanceof Double) {
                        cell.setCellValue(((Double) cellData).doubleValue());
                    } else if (cellData instanceof Integer) {
                        cell.setCellValue(((Integer) cellData).doubleValue());
                    } else if (cellData instanceof BigInteger) {
                        cell.setCellValue(((BigInteger) cellData).doubleValue());
                    } else {
                        cell.setCellValue(new HSSFRichTextString(cellData.toString()));
                    }
                }
            }
        }
    }

    // auto size the column width
    if (columnHeaderMap != null) {
        for (short i = 0; i < columnNameList.size(); i++) {
            workSheet.autoSizeColumn(i);
        }
    }

    // create the Excel file
    workBook.write(fileOut);
    fileOut.close();
}

---