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

Example 61 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project Hotchpotch by carryxyh.

the class ExportExcelUtils method createTitleRow.

/**
 * 生成标题（第零行创建）
 *
 * @param titleMap  对象属性名称->表头显示名称
 * @param sheetName sheet名称
 */
private static void createTitleRow(Map<String, String> titleMap, String sheetName) {
    CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);
    sheet.addMergedRegion(titleRange);
    HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);
    HSSFCell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(sheetName);
}

Example 62 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project zoj by licheng.

the class ShowRankListAction method exportToExcel.

private byte[] exportToExcel(AbstractContest contest, List<Problem> problems, RankList ranklist) throws Exception {
    List<RankListEntry> entries = ranklist.getEntries();
    long time = this.getTimeEscaped(contest);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue(contest.getTitle());
    if (ranklist.getRole() != null) {
        row = sheet.createRow(1);
        cell = row.createCell((short) 0);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(ranklist.getRole().getDescription());
    }
    row = sheet.createRow(2);
    cell = row.createCell((short) 0);
    cell.setCellValue("Length");
    cell = row.createCell((short) 1);
    cell.setCellValue(Utility.toTime(contest.getLength() / 1000));
    row = sheet.createRow(3);
    cell = row.createCell((short) 0);
    cell.setCellValue("Time Escaped");
    cell = row.createCell((short) 1);
    cell.setCellValue(Utility.toTime(time / 1000));
    row = sheet.createRow(5);
    row.createCell((short) 0).setCellValue("Rank");
    row.createCell((short) 1).setCellValue("Handle");
    row.createCell((short) 2).setCellValue("Nickname");
    row.createCell((short) 3).setCellValue("Solved");
    short columnIndex = 4;
    for (Problem problem2 : problems) {
        Problem problem = problem2;
        row.createCell(columnIndex).setCellValue(problem.getCode());
        columnIndex++;
    }
    row.createCell(columnIndex).setCellValue("Penalty");
    int rowIndex = 6;
    for (RankListEntry rankListEntry : entries) {
        RankListEntry entry = rankListEntry;
        row = sheet.createRow(rowIndex);
        row.createCell((short) 0).setCellValue(rowIndex - 5);
        row.createCell((short) 1).setCellValue(entry.getUserProfile().getHandle());
        String nick = entry.getUserProfile().getHandle();
        if (entry.getUserProfile().getNickName() != null) {
            nick = entry.getUserProfile().getNickName();
        }
        cell = row.createCell((short) 2);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(nick);
        row.createCell((short) 3).setCellValue(entry.getSolved());
        for (short i = 0; i < problems.size(); ++i) {
            String score = entry.getAcceptTime(i) > 0 ? entry.getAcceptTime(i) + "(" + entry.getSubmitNumber(i) + ")" : "" + entry.getSubmitNumber(i);
            row.createCell((short) (4 + i)).setCellValue(score);
        }
        row.createCell((short) (4 + problems.size())).setCellValue(entry.getPenalty());
        rowIndex++;
    }
    // output to stream
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
        return out.toByteArray();
    } finally {
        out.close();
    }
}

Example 63 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project zoj by licheng.

the class UserSearchAction method exportToExcel.

private byte[] exportToExcel(UserCriteria criteria, List<UserProfile> users) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    short rowId = 0;
    for (Object user : users) {
        HSSFRow row = sheet.createRow(rowId);
        rowId++;
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(((UserProfile) user).getHandle());
    }
    // output to stream
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
        return out.toByteArray();
    } finally {
        out.close();
    }
}

Example 64 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell 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;
}

Example 65 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project openolat by klemens.

the class ExcelDocument method readContent.

@Override
protected FileContent readContent(VFSLeaf leaf) throws IOException, DocumentException {
    int cellNullCounter = 0;
    int rowNullCounter = 0;
    int sheetNullCounter = 0;
    try (BufferedInputStream bis = new BufferedInputStream(leaf.getInputStream());
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(bis))) {
        LimitedContentWriter content = new LimitedContentWriter((int) leaf.getSize(), FileDocumentFactory.getMaxFileSize());
        for (int sheetNumber = 0; sheetNumber < workbook.getNumberOfSheets(); sheetNumber++) {
            HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            if (sheet != null) {
                for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) {
                    HSSFRow row = sheet.getRow(rowNumber);
                    if (row != null) {
                        for (int cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
                            HSSFCell cell = row.getCell(cellNumber);
                            if (cell != null) {
                                if (cell.getCellTypeEnum() == CellType.STRING) {
                                    content.append(cell.getStringCellValue()).append(' ');
                                }
                            } else {
                                cellNullCounter++;
                            }
                        }
                    } else {
                        rowNullCounter++;
                    }
                }
            } else {
                sheetNullCounter++;
            }
        }
        if (log.isDebug()) {
            if ((cellNullCounter > 0) || (rowNullCounter > 0) || (sheetNullCounter > 0)) {
                log.debug("Read Excel content cell=null #:" + cellNullCounter + ", row=null #:" + rowNullCounter + ", sheet=null #:" + sheetNullCounter);
            }
        }
        content.close();
        return new FileContent(content.toString());
    } catch (Exception ex) {
        throw new DocumentException("Can not read XLS Content. File=" + leaf.getName(), ex);
    }
}