Examples with XSSFCell org.apache.poi.xssf.usermodel.XSSFCell used on opensource projects

Example 36 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project swift by luastar.

the class ExcelUtils method readXlsxSheet.

/**
 * 从sheet中读取数据
 *
 * @param workbook
 * @param sheetConfig
 * @throws Exception
 */
private static void readXlsxSheet(XSSFWorkbook workbook, ImportSheet sheetConfig) throws Exception {
    if (workbook == null || sheetConfig == null || sheetConfig.getDataClass() == null || sheetConfig.getColumnList() == null) {
        throw new IllegalArgumentException("excel导入参数错误！");
    }
    // 公式执行器
    CreationHelper createHelper = workbook.getCreationHelper();
    FormulaEvaluator formulaEvaluator = createHelper.createFormulaEvaluator();
    int sheetNum = workbook.getNumberOfSheets();
    if (sheetConfig.getIndex() >= sheetNum) {
        String msg = StrUtils.formatString("sheet【{0}】不存在", sheetConfig.getIndex() + 1);
        throw new RuntimeException(msg);
    }
    XSSFSheet sheet = workbook.getSheetAt(sheetConfig.getIndex());
    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();
    if (lastRowNum < 1) {
        String msg = StrUtils.formatString("sheet【{0}】数据为空", sheet.getSheetName());
        throw new RuntimeException(msg);
    }
    // 通过标题找对应的列
    List<String> columnNotFound = Lists.newArrayList();
    List<ImportColumn> columnList = sheetConfig.getColumnList();
    XSSFRow titleRow = sheet.getRow(firstRowNum);
    int titleNum = columnList.size();
    int columnNum = titleRow.getLastCellNum();
    for (int i = 0; i < titleNum; i++) {
        ImportColumn column = columnList.get(i);
        for (int j = 0; j < columnNum; j++) {
            XSSFCell cell = titleRow.getCell(j);
            if (cell != null && column.getTitle().equals(cell.getStringCellValue())) {
                column.setColumnIndex(j);
            }
        }
        if (column.getColumnIndex() == null) {
            columnNotFound.add(column.getTitle());
        }
    }
    // 找不到对应的列
    if (columnNotFound.size() > 0) {
        String msg = StrUtils.formatString("列【{0}】不存在", StringUtils.join(columnNotFound, "，"));
        throw new RuntimeException(msg);
    }
    // 获取数据
    List<ExcelData> dataList = Lists.newArrayList();
    for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
        XSSFRow row = sheet.getRow(i);
        Object data = sheetConfig.getDataClass().newInstance();
        if (row == null) {
            ExcelData excelData = new ExcelData(i, data);
            excelData.setCheckMsg("获取行数据为空");
            dataList.add(excelData);
            continue;
        }
        // 行不为空
        List<String> setPropList = Lists.newArrayList();
        for (int j = 0; j < titleNum; j++) {
            ImportColumn column = columnList.get(j);
            XSSFCell cell = row.getCell(column.getColumnIndex());
            setPropList.add(setProperty(column, cell, data, formulaEvaluator));
        }
        ExcelData excelData = new ExcelData(i, data);
        // 赋值失败的列
        List setErrList = setPropList.stream().filter(rs -> rs != null).collect(Collectors.toList());
        if (CollectionUtils.isNotEmpty(setErrList)) {
            String msg = StrUtils.formatString("获取以下属性的值失败：{0}", StringUtils.join(setErrList, ","));
            excelData.setCheckMsg(msg);
        }
        dataList.add(excelData);
    }
    sheetConfig.setDataList(dataList);
}

Example 37 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project baseio by generallycloud.

the class TestExcel method exportExcel.

// 流式导出时需要逐行写入 sheet, 因此需要创建新的 sheet, 整体思路：
// 遇到非循环结构则直接复制 cell 内容到新 sheet, 如果需要做值替换则进行替换，
// 遇到循环结构则读取 meta(循环的替换行信息) 信息在循环中使用，目的是避免在循环中读取 meta, 以减少不必要的对象创建
// 单元格合并（这里采用栈式处理）：
// 遇到非循环结构时 pop 出单元格合并信息，并根据新 sheet 实际行数构建新的合并信息
// 遇到循环结构时先将该循环 meta 里的合并信息 pop 出来，在循环中根据新 sheet 的实际 rowIndex 构建新的合并信息
// 这里需要注意的是 mergeBase, 该属性作用是对新 sheet 行数进行补偿，以达到新 sheet 行号与模版行号一致，在代码块中会对该属性做出解释
public static void exportExcel(Map data, OutputStream dataOut, InputStream modelInput) throws IOException {
    long startTime = System.currentTimeMillis();
    XSSFWorkbook wb = new XSSFWorkbook(modelInput);
    SXSSFWorkbook swb = new SXSSFWorkbook(wb, 1024);
    System.out.println("init cost: " + (System.currentTimeMillis() - startTime));
    try {
        XSSFSheet temp = wb.getSheetAt(0);
        SXSSFSheet sheet = swb.createSheet("export");
        XSSFSheet _sheet = wb.getSheet("export");
        CTWorksheet ctWorksheet = _sheet.getCTWorksheet();
        List<CellRangeAddress> mergedRegions = temp.getMergedRegions();
        Stack<CellRangeAddress> mergeStack = new Stack<>();
        for (int i = mergedRegions.size() - 1; i >= 0; i--) {
            mergeStack.push(mergedRegions.get(i));
        }
        int rowIndex = 0;
        int mergeBase = 0;
        int rowCount = temp.getLastRowNum();
        for (int i = 0; i <= rowCount; i++) {
            XSSFRow tempRow = temp.getRow(i);
            if (isList(tempRow)) {
                String value = tempRow.getCell(0).getStringCellValue();
                String key = value.substring(1);
                List<Map> rowData = (List<Map>) data.get(key);
                tempRow = temp.getRow(++i);
                if (!mergeStack.isEmpty()) {
                    mergeStack.pop();
                }
                // 模板中忽略 meta 头(#list.xxx)的合并信息，对 mergeBase + 1 操作
                mergeBase++;
                List<String> metas = readMeta(tempRow);
                if (rowData != null) {
                    List<CellRangeAddress> listAddress = new ArrayList<>();
                    for (; !mergeStack.isEmpty(); ) {
                        // 这里因为先判断是否需要 merge 所以 rowIndex 尚未 +1，所以采用大于等于判断
                        if (rowIndex + mergeBase >= mergeStack.peek().getLastRow()) {
                            CellRangeAddress merge = mergeStack.pop();
                            listAddress.add(merge);
                        } else {
                            break;
                        }
                    }
                    for (int j = 0; j < rowData.size(); j++) {
                        SXSSFRow row = sheet.createRow(rowIndex++);
                        Map map = rowData.get(j);
                        for (int k = 0; k < metas.size(); k++) {
                            SXSSFCell cell = row.createCell(k);
                            String meta = metas.get(k);
                            String cellValue = null;
                            if (!Strings.isNullOrEmpty(meta)) {
                                cellValue = getDataValue(map, meta);
                            }
                            cell.setCellValue(cellValue);
                            XSSFCell tempCell = tempRow.getCell(k);
                            if (tempCell != null) {
                                cell.setCellStyle(tempCell.getCellStyle());
                            }
                        }
                        for (CellRangeAddress merge : listAddress) {
                            CellRangeAddress address = new CellRangeAddress(rowIndex - 1, rowIndex - 1, merge.getFirstColumn(), merge.getLastColumn());
                            addMergedRegion(sheet, ctWorksheet, address);
                        }
                        // 新 sheet 行数增加，减小 mergeBase
                        mergeBase--;
                    }
                    // 循环结构有数据，则模版循环 meta 生效，对 mergeBase + 1 操作
                    mergeBase++;
                }
            } else {
                short cellNum = tempRow.getLastCellNum();
                SXSSFRow row = sheet.createRow(rowIndex++);
                for (int j = 0; j < cellNum; j++) {
                    XSSFCell tempCell = tempRow.getCell(j);
                    if (tempCell != null) {
                        SXSSFCell cell = row.createCell(j);
                        String tempValue = tempCell.getStringCellValue();
                        if (tempValue.startsWith("#")) {
                            cell.setCellValue(getDataValue(data, tempValue.substring(1)));
                        } else {
                            cell.setCellValue(tempValue);
                        }
                        cell.setCellStyle(tempCell.getCellStyle());
                    }
                }
                // 这里因为 rowIndex 已经加一，所以采用大于判断
                for (; !mergeStack.isEmpty(); ) {
                    if (rowIndex + mergeBase > mergeStack.peek().getLastRow()) {
                        int base = rowIndex - 1;
                        CellRangeAddress merge = mergeStack.pop();
                        CellRangeAddress address = new CellRangeAddress(base - (merge.getLastRow() - merge.getFirstRow()), base, merge.getFirstColumn(), merge.getLastColumn());
                        addMergedRegion(sheet, ctWorksheet, address);
                    } else {
                        break;
                    }
                }
            }
        }
        // 处理 Excel 末尾行单元格合并
        for (; !mergeStack.isEmpty(); ) {
            int base = rowIndex - 1;
            CellRangeAddress merge = mergeStack.pop();
            CellRangeAddress address = new CellRangeAddress(base, base + merge.getLastRow() - merge.getFirstRow(), merge.getFirstColumn(), merge.getLastColumn());
            addMergedRegion(sheet, ctWorksheet, address);
        }
        swb.removeSheetAt(0);
        swb.setSheetOrder("export", 0);
        swb.setActiveSheet(0);
        swb.write(dataOut);
    } finally {
        close(swb);
        swb.dispose();
    }
}

Example 38 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project baseio by generallycloud.

the class TestExcel method readMeta.

static List<String> readMeta(XSSFRow row) {
    short lastCellNum = row.getLastCellNum();
    List<String> list = new ArrayList<>();
    for (int i = 0; i < lastCellNum; i++) {
        XSSFCell cell = row.getCell(i);
        if (cell != null) {
            String value = cell.getStringCellValue();
            if (Strings.isNullOrEmpty(value)) {
                list.add("");
            } else {
                list.add(value.substring(1));
            }
        } else {
            list.add("");
        }
    }
    return list;
}

Example 39 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project tutorials by eugenp.

the class ExcelPOIHelper method readXSSFWorkbook.

private Map<Integer, List<MyCell>> readXSSFWorkbook(FileInputStream fis) throws IOException {
    XSSFWorkbook workbook = null;
    Map<Integer, List<MyCell>> data = new HashMap<>();
    try {
        workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            data.put(i, new ArrayList<>());
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        XSSFCellStyle cellStyle = cell.getCellStyle();
                        MyCell myCell = new MyCell();
                        XSSFColor bgColor = cellStyle.getFillForegroundColorColor();
                        if (bgColor != null) {
                            byte[] rgbColor = bgColor.getRGB();
                            myCell.setBgColor("rgb(" + (rgbColor[0] < 0 ? (rgbColor[0] + 0xff) : rgbColor[0]) + "," + (rgbColor[1] < 0 ? (rgbColor[1] + 0xff) : rgbColor[1]) + "," + (rgbColor[2] < 0 ? (rgbColor[2] + 0xff) : rgbColor[2]) + ")");
                        }
                        XSSFFont font = cellStyle.getFont();
                        myCell.setTextSize(font.getFontHeightInPoints() + "");
                        if (font.getBold()) {
                            myCell.setTextWeight("bold");
                        }
                        XSSFColor textColor = font.getXSSFColor();
                        if (textColor != null) {
                            byte[] rgbColor = textColor.getRGB();
                            myCell.setTextColor("rgb(" + (rgbColor[0] < 0 ? (rgbColor[0] + 0xff) : rgbColor[0]) + "," + (rgbColor[1] < 0 ? (rgbColor[1] + 0xff) : rgbColor[1]) + "," + (rgbColor[2] < 0 ? (rgbColor[2] + 0xff) : rgbColor[2]) + ")");
                        }
                        myCell.setContent(readCellContent(cell));
                        data.get(i).add(myCell);
                    } else {
                        data.get(i).add(new MyCell(""));
                    }
                }
            }
        }
    } finally {
        if (workbook != null) {
            workbook.close();
        }
    }
    return data;
}

Example 40 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project selenium_java by sergueik.

the class ExcelConfiguration method CreateHeader.

@SuppressWarnings("deprecation")
public static XSSFRow CreateHeader(XSSFWorkbook book, XSSFSheet sheet, String[] headers) {
    XSSFRow row = sheet.createRow(0);
    for (int column = 0; column < headers.length; column++) {
        XSSFCell headerCell = row.createCell(column);
        XSSFCellStyle headerStyle = book.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerStyle.setLocked(true);
        headerCell.setCellStyle(headerStyle);
        headerCell.setCellValue(headers[column]);
    }
    return row;
}