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

Example 86 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project bitcampSCOpen2017 by ryuyj.

the class MemberView method buildExcelDocument.

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
    // 1. 시트 생성
    HSSFSheet sheet = createFirstSheet(workbook);
    // 2. 라벨 처리
    createColumnLabel(sheet);
    // 3. 각 행별 데이터 처리
    List<Member2> members = (List<Member2>) model.get("members");
    int rowNum = 3;
    for (Member2 member : members) {
        createMemberRow(sheet, member, rowNum++);
    }
    // 파일 이름설정
    response.setHeader("Content-Disposition", "attachment; filename=\"pagerank.xls\";");
}

Example 87 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project bitcampSCOpen2017 by ryuyj.

the class MemberView method createFirstSheet.

// 시트를 생성해주는 메서드
private HSSFSheet createFirstSheet(HSSFWorkbook workbook) {
    // 시트생성
    HSSFSheet sheet = workbook.createSheet();
    // 생성된 시트 이름 설정
    workbook.setSheetName(0, "회원목록");
    // 시트 컬럼의 사이즈 지정
    sheet.setColumnWidth(1, 256 * 20);
    return sheet;
}

Example 88 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project rxlib by RockyLOMO.

the class Helper method readExcel.

public static List<Object[]> readExcel(String filePath, boolean skipColumn) throws IOException {
    List<Object[]> result = new ArrayList<>();
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        for (int rowIndex = skipColumn ? 1 : sheet.getFirstRowNum(); rowIndex < sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            List<Object> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cellValues.add(null);
                    continue;
                }
                Object value;
                switch(cell.getCellTypeEnum()) {
                    case NUMERIC:
                        value = cell.getNumericCellValue();
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    default:
                        value = cell.getStringCellValue();
                        break;
                }
                cellValues.add(value);
            }
            if (cellValues.contains(null)) {
                Logger.debug(String.format("current=%s offset=%s count=%s -> %s/%s", toJsonString(cellValues), row.getFirstCellNum(), row.getLastCellNum(), rowIndex, sheetIndex));
            }
            result.add(cellValues.toArray());
        }
    }
    return result;
}

Example 89 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project lavagna by digitalfondue.

the class ExcelExportService method getWorkbookFromSearchFilters.

private HSSFWorkbook getWorkbookFromSearchFilters(int projectId, String sheetName, List<SearchFilter> filters, UserWithPermission user) {
    List<CardLabel> labels = cardLabelRepository.findLabelsByProject(projectId);
    CollectionUtils.filter(labels, new Predicate<CardLabel>() {

        @Override
        public boolean evaluate(CardLabel cl) {
            if (cl.getDomain().equals(CardLabel.LabelDomain.SYSTEM)) {
                if (cl.getName().equals(SYSTEM_LABEL_ASSIGNED) || cl.getName().equals(SYSTEM_LABEL_DUE_DATE) || cl.getName().equals(SYSTEM_LABEL_MILESTONE)) {
                    return true;
                }
                return false;
            }
            return true;
        }
    });
    Collections.sort(labels, new Comparator<CardLabel>() {

        public int compare(CardLabel l1, CardLabel l2) {
            return new CompareToBuilder().append(l1.getDomain(), l2.getDomain()).append(l1.getName(), l2.getName()).toComparison();
        }
    });
    SearchResults cards = searchService.find(filters, projectId, null, user);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    Row header = sheet.createRow(0);
    int headerColPos = 0;
    header.createCell(headerColPos++).setCellValue("Board");
    header.createCell(headerColPos++).setCellValue("ID");
    header.createCell(headerColPos++).setCellValue("Name");
    header.createCell(headerColPos++).setCellValue("Column");
    header.createCell(headerColPos++).setCellValue("Status");
    header.createCell(headerColPos++).setCellValue("Description");
    header.createCell(headerColPos++).setCellValue("Created");
    header.createCell(headerColPos++).setCellValue("Created by");
    for (CardLabel cl : labels) {
        header.createCell(headerColPos++).setCellValue(WordUtils.capitalizeFully(cl.getName().replace("_", " ")));
    }
    Map<Integer, BoardColumnInfo> colCache = new HashMap<>();
    Map<Integer, String> userCache = new HashMap<>();
    Map<Integer, String> cardCache = new HashMap<>();
    Map<Integer, String> listValueCache = new HashMap<>();
    int rowPos = 1;
    for (CardFullWithCounts card : cards.getFound()) {
        int colPos = 0;
        Row row = sheet.createRow(rowPos++);
        // Board
        row.createCell(colPos++).setCellValue(card.getBoardShortName());
        // ID
        row.createCell(colPos++).setCellValue(card.getSequence());
        // Name
        row.createCell(colPos++).setCellValue(card.getName());
        // Column
        if (!colCache.containsKey(card.getColumnId())) {
            colCache.put(card.getColumnId(), boardColumnRepository.getColumnInfoById(card.getColumnId()));
        }
        BoardColumnInfo col = colCache.get(card.getColumnId());
        row.createCell(colPos++).setCellValue(col.getColumnName());
        // ColumnDefinition - status
        row.createCell(colPos++).setCellValue(card.getColumnDefinition().toString());
        // Description
        CardDataHistory desc = cardDataService.findLatestDescriptionByCardId(card.getId());
        row.createCell(colPos++).setCellValue(desc != null ? desc.getContent() : "");
        // Creation date
        row.createCell(colPos++).setCellValue(new SimpleDateFormat("yyyy.MM.dd").format(card.getCreationDate()));
        // Created by
        row.createCell(colPos++).setCellValue(getUserDescription(userCache, card.getCreationUser()));
        // Labels
        fillLabelValues(row, colPos, labels, cardLabelRepository.findCardLabelValuesByCardId(card.getId()), userCache, cardCache, listValueCache);
    }
    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, headerColPos - 1));
    // Auto size the columns except for the description
    for (int i = 0; i < headerColPos; i++) {
        if (!header.getCell(i).getStringCellValue().equals("Description")) {
            sheet.autoSizeColumn(i);
        } else {
            sheet.setColumnWidth(i, 30 * 256);
        }
    }
    return wb;
}

Example 90 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project selenium_java by sergueik.

the class Launcher method writeStatus.

public static void writeStatus(String sheetName, int rowNumber) throws IOException {
    File file = new File(testCase);
    FileInputStream istream = new FileInputStream(file);
    HSSFWorkbook workbook = new HSSFWorkbook(istream);
    HSSFSheet sheet = workbook.getSheet(sheetName);
    Row row = sheet.getRow(rowNumber);
    Cell cell = row.createCell(statusColumn);
    cell.setCellValue(KeywordLibrary.getStatus());
    FileOutputStream ostream = new FileOutputStream(file);
    workbook.write(ostream);
    ostream.close();
    workbook.close();
}