Examples with HSSFDataFormat org.apache.poi.hssf.usermodel.HSSFDataFormat used on opensource projects

Example 1 with HSSFDataFormat

use of org.apache.poi.hssf.usermodel.HSSFDataFormat in project poi by apache.

the class InCellLists method bulletedItemInCell.

/**
     * Inserts a single bulleted item into a cell.
     *
     * @param workbook A reference to the HSSFWorkbook that 'contains' the
     *                 cell.
     * @param listItem An instance of the String class encapsulating the
     *                 items text.
     * @param cell An instance of the HSSFCell class that encapsulates a
     *             reference to the spreadsheet cell into which the list item
     *             will be written.
     */
public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) {
    // A format String must be built to ensure that the contents of the
    // cell appear as a bulleted item.
    HSSFDataFormat format = workbook.createDataFormat();
    String formatString = InCellLists.BULLET_CHARACTER + " @";
    int formatIndex = format.getFormat(formatString);
    // Construct an HSSFCellStyle and set it's data formt to use the
    // object created above.
    HSSFCellStyle bulletStyle = workbook.createCellStyle();
    bulletStyle.setDataFormat((short) formatIndex);
    // Set the cells contents and style.
    cell.setCellValue(new HSSFRichTextString(listItem));
    cell.setCellStyle(bulletStyle);
}

Example 2 with HSSFDataFormat

use of org.apache.poi.hssf.usermodel.HSSFDataFormat in project poi by apache.

the class TestTime method setUp.

@Before
public void setUp() {
    wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    style = wb.createCellStyle();
    HSSFDataFormat fmt = wb.createDataFormat();
    style.setDataFormat(fmt.getFormat("hh:mm:ss"));
    cell11 = sheet.createRow(0).createCell(0);
    form = new HSSFDataFormatter();
    evaluator = new HSSFFormulaEvaluator(wb);
}

Example 3 with HSSFDataFormat

use of org.apache.poi.hssf.usermodel.HSSFDataFormat in project wechat by dllwh.

the class ExcelExportUtil method export.

/**
 * -------------------------- 私有方法 end -------------------------------
 */
/**
 * -------------------------- 公有方法 start -------------------------------
 */
/**
 * @Title: export
 * @Description: 基于结果集的导出
 *               <ul>
 *               <li>基于结果集的导出</li>
 *               <li>XSSFWorkbook used for .xslx >= 2007</li>
 *               <li>HSSFWorkbook used for .xsl 03</li>
 *               </ul>
 * @param title
 * @param cellNames
 * @param dbList
 * @return
 * @throws Exception
 */
public static <T> byte[] export(String title, String[][] cellNames, List<T> dbList) throws Exception {
    if (CollectionUtils.isEmpty(dbList) || dbList.size() < 0) {
        return null;
    }
    // 创建Excel的工作书册 Workbook,对应到一个excel文档
    HSSFWorkbook workbook = new HSSFWorkbook();
    Map<String, CellStyle> getStyle = createStyles(workbook);
    // 创建Excel的工作sheet,对应到一个excel文档的tab
    HSSFSheet sheet = workbook.createSheet(title);
    // 创建字体样式
    HSSFFont font = workbook.createFont();
    font.setFontName("宋体");
    // 创建单元格样式
    // 创建Excel的sheet的一行
    HSSFRow row = sheet.createRow(0);
    // 设定行的高度
    row.setHeight((short) 500);
    // 创建一个Excel的单元格
    HSSFCell cell = row.createCell(0);
    // 合并单元格(startRow，endRow，startColumn，endColumn)
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellNames.length - 1));
    // 给合并后的单元格加上样式
    for (int j = 0; j <= cellNames.length - 1; j++) {
        HSSFCell celTemp = row.getCell(j);
        if (celTemp == null) {
            celTemp = row.createCell(j);
        }
    }
    // 给Excel的单元格设置样式和赋值
    cell.setCellValue(title);
    HSSFRow rowTitle = sheet.createRow(1);
    for (int i = 0; i < cellNames.length; i++) {
        HSSFCell cellTitle = rowTitle.createCell(i);
        // 设置excel列名
        cellTitle.setCellValue(cellNames[i][1]);
    }
    int i = 0;
    for (T bd : dbList) {
        row = sheet.createRow(i + 2);
        for (int j = 0; j < cellNames.length; j++) {
            HSSFCell cellvalue = row.createCell(j);
            String value = null;
            Object object = ObjectUtils.getProperty(bd, cellNames[j][0]);
            if (null == object) {
                value = "";
            } else if (object instanceof Date) {
                Date date = (Date) object;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                value = sdf.format(date);
            } else {
                value = String.valueOf(object);
            }
            if ("str".equals(cellNames[j][2])) {
                cellvalue.setCellValue(value);
                cellvalue.setCellStyle(getStyle.get("cell"));
            } else {
                HSSFDataFormat format = workbook.createDataFormat();
                HSSFCellStyle formatStyle = workbook.createCellStyle();
                // 设置边框
                formatStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                formatStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                formatStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
                formatStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
                // 设置字体
                formatStyle.setFont(font);
                if ("amt".equals(cellNames[j][2])) {
                    cellvalue.setCellValue(Double.parseDouble(value));
                    // 设置保留2位小数--使用Excel内嵌的格式
                    formatStyle.setDataFormat(format.getFormat("#,##0.00"));
                } else if ("datetime".equals(cellNames[j][2])) {
                    cellvalue.setCellValue(value);
                    // 设置日期格式--使用Excel内嵌的格式
                    formatStyle.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
                }
                // 设置货币格式--使用自定义的格式
                // cellStyle.setDataFormat(format.getFormat("¥#,##0"));
                // 设置百分比格式--使用自定义的格式
                // cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00%"));
                // 设置中文大写格式--使用自定义的格式
                // cellStyle.setDataFormat(format.getFormat("[DbNum2][$-804]0"));
                cellvalue.setCellStyle(formatStyle);
            }
        }
        i++;
    }
    if (i == 0) {
        return null;
    }
    for (int k = 0; k < cellNames.length; k++) {
        // 自动设置列宽
        sheet.autoSizeColumn(k, true);
    }
    // 将生成的Excel放入IO流中
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    // 在内存中把数据写入ByteArrayOutputStream os
    workbook.write(os);
    // 在内存中,得到os的字节数组
    byte[] content = os.toByteArray();
    return content;
}