org.apache.poi.xssf.streaming.SXSSFSheet Java Examples

/**
 * Add date validation when export
 *
 * @param operatorType operatorType
 * @param expr1        Date expression 1, such as: 2019-12-12
 * @param expr2        Date expression 2(Only operation types between and notBetween are required)，such as：2019-12-24
 * @param pattern      Date pattern
 * @param sheet        Current sheet
 * @param firstRow     Start row
 * @param lastRow      End row
 * @param colIndex     Column index
 * @param showErrorBox Whether show error box
 * @param errorBoxRank Error box rank
 * @param errorTitle   Error box title
 * @param errorContent Error box value
 * @param showTip      Whether show tip
 * @param tipContent   Tip content
 * @param tipTitle     Tip title
 */
public static void addDateValid(OperatorType operatorType, String expr1, String expr2, String pattern, Sheet sheet, int firstRow, int lastRow,
                                int colIndex, boolean showErrorBox, Rank errorBoxRank, String errorTitle, String errorContent, boolean showTip,
                                String tipTitle, String tipContent) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint;
    if (sheet instanceof SXSSFSheet) {
        dvConstraint = helper.createDateConstraint(operatorType.getType(), "date(" + expr1.replaceAll("-", ",") + ")",
                "date(" + expr2.replaceAll("-", ",") + ")", pattern);
    } else {
        dvConstraint = helper.createDateConstraint(operatorType.getType(), expr1, expr2, pattern);
    }
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
    DataValidation dataValidation = helper.createValidation(dvConstraint, regions);
    dataValidation.setShowErrorBox(showErrorBox);
    dataValidation.setErrorStyle(errorBoxRank.getRank());
    dataValidation.createErrorBox(errorTitle, errorContent);
    if (showTip) {
        dataValidation.createPromptBox(tipTitle, tipContent);
    }
    sheet.addValidationData(dataValidation);
}
 

/**
 * 构建模板Excel
 *
 * @param <R>
 * @param <T>
 * @return
 */
public SXSSFWorkbook generateTemplateWorkbook() {
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);

    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    SXSSFRow row = sheet.createRow(1);
    for (int j = 0; j < propertyList.size(); j++) {
        SXSSFCell cell = row.createCell(j);
        cell.setCellValue(propertyList.get(j).getTemplateCellValue());
        calculateColumWidth(cell, j);
    }
    sizeColumWidth(sheet, propertyList.size());
    return workbook;
}
 

/**
 * 设置每列宽度
 *
 * @param colMaxWidthMap 列最大宽度Map
 * @param sheet          sheet
 * @param maxColIndex    最大列索引
 */
protected void setColWidth(Map<Integer, Integer> colMaxWidthMap, Sheet sheet, int maxColIndex) {
    if (WidthStrategy.isAutoWidth(widthStrategy)) {
        if (sheet instanceof SXSSFSheet) {
            throw new UnsupportedOperationException("SXSSF does not support automatic width at this time");
        }
        for (int i = 0; i <= maxColIndex; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    colMaxWidthMap.forEach((key, value) -> {
        int contentLength = value << 1;
        if (contentLength > 255) {
            contentLength = 255;
        }
        sheet.setColumnWidth(key, contentLength << 8);
    });
}
 

/**
 * 功能描述:下拉列表
 *
 * @param xssfWsheet
 * @param list
 * @param firstRow
 * @param lastRow
 * @param firstCol
 * @param lastCol
 */
public static void setDataValidation(SXSSFSheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
    DataValidationHelper helper = xssfWsheet.getDataValidationHelper();
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
    DataValidation dataValidation = helper.createValidation(constraint, addressList);
    dataValidation.createErrorBox(DataValidationError1, DataValidationError2);
    //  处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {
        dataValidation.setSuppressDropDownArrow(false);
    }
    xssfWsheet.addValidationData(dataValidation);
}
 

/**
 * 设定单元格宽度 (手动/自动)
 *
 * @param sheet 工作薄对象
 * @param index 单元格索引
 * @param width 指定宽度,-1为自适应
 * @param value 自适应需要单元格内容进行计算
 */
static void setColumnWidth(SXSSFSheet sheet, int index, short width, String value) {
    if (width == -1 && value != null && !"".equals(value)) {
        sheet.setColumnWidth(index, (short) (value.length() * 512));
    } else {
        width = width == -1 ? 200 : width;
        sheet.setColumnWidth(index, (short) (width * 35.7));
    }
}
 

private void closeOutputFile() throws HopException {
  try ( BufferedOutputStreamWithCloseDetection out = new BufferedOutputStreamWithCloseDetection( HopVfs.getOutputStream( data.file, false ) ) ) {
    // may have to write a footer here
    if ( meta.isFooterEnabled() ) {
      writeHeader();
    }
    // handle auto size for columns
    if ( meta.isAutoSizeColums() ) {

      // track all columns for autosizing if using streaming worksheet
      if ( data.sheet instanceof SXSSFSheet ) {
        ( (SXSSFSheet) data.sheet ).trackAllColumnsForAutoSizing();
      }

      if ( meta.getOutputFields() == null || meta.getOutputFields().length == 0 ) {
        for ( int i = 0; i < data.inputRowMeta.size(); i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      } else {
        for ( int i = 0; i < meta.getOutputFields().length; i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      }
    }
    // force recalculation of formulas if requested
    if ( meta.isForceFormulaRecalculation() ) {
      recalculateAllWorkbookFormulas();
    }

    data.wb.write( out );
  } catch ( IOException e ) {
    throw new HopException( e );
  }
}
 

@Test
public void lastRowNum() throws IOException {
    String file = "D:\\test\\原文件.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

@Test
public void write0() throws IOException {
    FileOutputStream fileOutputStream =
        new FileOutputStream("D://test//tt132" + System.currentTimeMillis() + ".xlsx");
    SXSSFWorkbook sxxsFWorkbook = new SXSSFWorkbook();
    SXSSFSheet sheet = sxxsFWorkbook.createSheet("t1");
    SXSSFRow row = sheet.createRow(0);
    SXSSFCell cell1 = row.createCell(0);
    cell1.setCellValue(999999999999999L);
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(1000000000000001L);
    SXSSFCell cell32 = row.createCell(2);
    cell32.setCellValue(300.35f);
    sxxsFWorkbook.write(fileOutputStream);
}
 

@Test
public void write() throws IOException {
    FileOutputStream fileOutputStream =
        new FileOutputStream("D://test//tt132" + System.currentTimeMillis() + ".xlsx");
    SXSSFWorkbook sxxsFWorkbook = new SXSSFWorkbook();
    SXSSFSheet sheet = sxxsFWorkbook.createSheet("t1");
    SXSSFRow row = sheet.createRow(0);
    SXSSFCell cell1 = row.createCell(0);
    cell1.setCellValue(Long.toString(999999999999999L));
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(Long.toString(1000000000000001L));
    sxxsFWorkbook.write(fileOutputStream);
}
 

@Test
public void test() throws IOException {
    String file = "D:\\test\\珠海.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
}
 

@Test
public void lastRowNum() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

@Test
public void cp() throws IOException, InvalidFormatException {
    String file = "d://test/tt.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

@Override
public CellPosition write(Sheet sheet, Collection<T> data) {
    //auto size will be slow for large data
    boolean isAdjustWidth = data.size() <= 100;
    if (isAdjustWidth && sheet instanceof SXSSFSheet) {
        this.prepareAdjustForSXSSFSheet((SXSSFSheet) sheet);
    }
    CellPosition end = super.write(sheet, data);
    if (isAdjustWidth) {
        adjustColumnWidth(sheet, end);
    }

    return end;
}
 

private void prepareAdjustForSXSSFSheet(SXSSFSheet sheet) {
    for (int i = getStartColumn(); i < autoSizeColumn.size(); i++) {
        if (autoSizeColumn.get(i) == 0) {
            sheet.trackColumnForAutoSizing(i);
        }
    }
}
 

public static void setMergeRegion(Sheet sheet, int row, int lastRow, int col, int lastCol, CellStyle cellStyle) {

        int i = sheet.addMergedRegion(new CellRangeAddress(row, lastRow, col, lastCol));

        /**
         * seems like a bug
         */
        CellRangeAddress region = sheet.getMergedRegion(sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet ? i - 1 : i);

        RegionUtil.setBorderTop(cellStyle.getBorderTopEnum(), region, sheet);
        RegionUtil.setBorderLeft(cellStyle.getBorderLeftEnum(), region, sheet);
        RegionUtil.setBorderBottom(cellStyle.getBorderBottomEnum(), region, sheet);
        RegionUtil.setBorderRight(cellStyle.getBorderRightEnum(), region, sheet);
    }
 

public ExcelSheet autoSizeColumn(int startCol, int endCol) {
    if (currentSheet instanceof SXSSFSheet) {
        ((SXSSFSheet) currentSheet).trackAllColumnsForAutoSizing();
    }
    IntStream.rangeClosed(startCol, endCol).forEach(i -> currentSheet.autoSizeColumn(i));
    return this;
}
 

public ExcelSheet tabColour(IndexedColors colour) {
    if (sheet() instanceof XSSFSheet) {
        ((XSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    } else if (sheet() instanceof SXSSFSheet) {
        ((SXSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    }
    return this;
}
 

private void flushSheet(final int currentCount, final Sheet sheet)
        throws IOException {
    if (sheet instanceof SXSSFSheet) {
        if (currentCount % SXSSF_FLUSH_COUNT == 0) {
            ((SXSSFSheet) sheet).flushRows(0);
        }
    }
}
 

private void closeOutputFile() throws KettleException {
  try ( BufferedOutputStreamWithCloseDetection out =  new BufferedOutputStreamWithCloseDetection( KettleVFS.getOutputStream( data.file, false ) ) ) {
    // may have to write a footer here
    if ( meta.isFooterEnabled() ) {
      writeHeader();
    }
    // handle auto size for columns
    if ( meta.isAutoSizeColums() ) {

      // track all columns for autosizing if using streaming worksheet
      if (  data.sheet instanceof SXSSFSheet ) {
        ( (SXSSFSheet) data.sheet ).trackAllColumnsForAutoSizing();
      }

      if ( meta.getOutputFields() == null || meta.getOutputFields().length == 0 ) {
        for ( int i = 0; i < data.inputRowMeta.size(); i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      } else {
        for ( int i = 0; i < meta.getOutputFields().length; i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      }
    }
    // force recalculation of formulas if requested
    if ( meta.isForceFormulaRecalculation() ) {
      recalculateAllWorkbookFormulas();
    }

    data.wb.write( out );
  } catch ( IOException e ) {
    throw new KettleException( e );
  }
}
 

public static <T> SXSSFWorkbook generateExcel(List<T> list, Class<T> clazz, String[] fieldsName, String[] fields, String sheetName) {
    //1、创建工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    if (list != null && !list.isEmpty()) {
        //1.3、列标题样式
        CellStyle style2 = createCellStyle(workbook, (short) 13, CellStyle.ALIGN_LEFT, true);
        //1.4、强制换行
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        //2、创建工作表
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        //设置默认列宽
        sheet.setDefaultColumnWidth(13);
        SXSSFRow row2 = sheet.createRow(0);
        row2.setHeight((short) 260);
        for (int j = 0; j < list.size(); j++) {
            SXSSFRow row = sheet.createRow(j + 1);
            row.setHeight((short) 260);
            for (int i = 0; i < fieldsName.length; i++) {
                //3.3设置列标题
                SXSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(style2);
                cell2.setCellValue(fieldsName[i]);
                //4、操作单元格；将数据写入excel
                handleWriteCell(row, i, j, list, cellStyle, fields, clazz);
            }
        }
    }
    return workbook;
}
 

/**
 * 功能描述:下拉列表
 *
 * @param sheet
 * @param dropDownListData
 * @param dataListSize
 */
public static void setDataValidation(SXSSFSheet sheet, List<String[]> dropDownListData, int dataListSize) {
    if (dropDownListData.size() > 0) {
        for (int col = 0; col < dropDownListData.get(0).length; col++) {
            Integer colv = Integer.parseInt(dropDownListData.get(0)[col]);
            setDataValidation(sheet, dropDownListData.get(col + 1), 1, dataListSize < 100 ? 500 : dataListSize, colv, colv);
        }
    }
}
 

/**
 * 自动适配中文单元格
 *
 * @param sheet
 * @param cell
 * @param columnIndex
 */
private void sizeColumWidth(SXSSFSheet sheet, Integer columnSize) {
    if (openAutoColumWidth) {
        for (int j = 0; j < columnSize; j++) {
            if (columnWidthMap.get(j) != null) {
                sheet.setColumnWidth(j, columnWidthMap.get(j) * 256);
            }
        }
    }
}
 

/**
 * 功能描述: excel 合并单元格
 *
 * @param sheet
 * @param rowColList
 */
public static void setMergedRegion(SXSSFSheet sheet, ArrayList<Integer[]> rowColList) {
    if (rowColList != null && rowColList.size() > 0) {
        for (int i = 0; i < rowColList.size(); i++) {
            Integer[] str = rowColList.get(i);
            if (str.length > 0 && str.length == 4) {
                Integer firstRow = str[0];
                Integer lastRow = str[1];
                Integer firstCol = str[2];
                Integer lastCol = str[3];
                setMergedRegion(sheet, firstRow, lastRow, firstCol, lastCol);
            }
        }
    }
}
 

/**
 * 功能描述: 自定义列宽
 *
 * @param sxssfSheet
 * @param map
 */
public static void setColumnWidth(SXSSFSheet sxssfSheet, HashMap map) {
    if (map != null) {
        Iterator iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry entry = (Map.Entry) iterator.next();
            Object key = entry.getKey();
            Object val = entry.getValue();
            sxssfSheet.setColumnWidth((int) key, (int) val * 512);
        }
    }
}
 

/**
 * 初始化第一行的属性
 *
 * @param workbook
 * @param propertyList
 * @param sheetName
 * @return
 */
private SXSSFSheet generateHeader(SXSSFWorkbook workbook, List<ExcelPropertyEntity> propertyList, String sheetName) {
    SXSSFSheet sheet = workbook.createSheet(sheetName);
    SXSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeight((short) 600);
    CellStyle headCellStyle = getHeaderCellStyle(workbook);
    for (int i = 0; i < propertyList.size(); i++) {
        SXSSFCell cell = headerRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellValue(propertyList.get(i).getColumnName());
        calculateColumWidth(cell, i);
    }
    return sheet;
}
 

/**
 * 自定义：大标题
 *
 * @param jRow
 * @param k
 * @param wb
 * @param labelName
 * @param sxssfRow
 * @param sxssfSheet
 * @param listRow
 * @return
 */
private static int setLabelName(Integer jRow, Integer k, SXSSFWorkbook wb, String[] labelName, SXSSFRow sxssfRow, SXSSFSheet sxssfSheet, List<String[]> listRow) {

    if (labelName != null) {
        //  自定义：大标题和样式。参数说明：new String[]{"表格数据一", "表格数据二", "表格数据三"}
        sxssfRow = sxssfSheet.createRow(0);
        Cell cell = createCell(sxssfRow, 0, labelName[k]);
        setMergedRegion(sxssfSheet, 0, 0, 0, listRow.get(0).length - 1);
        setLabelStyles(wb, cell, sxssfRow);
        jRow = 1;
    }
    return jRow;
}
 

static SXSSFSheet newSXSSFSheet(SXSSFWorkbook wb, String sheetName) {
    return wb.createSheet(sheetName);
}
 

/**
 * @param param
 * @param exportFunction
 * @param <P>
 * @param <T>
 * @return
 * @throws InvocationTargetException
 * @throws NoSuchMethodException
 * @throws ParseException
 * @throws IllegalAccessException
 */
public <P, T> SXSSFWorkbook generateWorkbook(P param, ExportFunction<P, T> exportFunction) throws Exception {
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
    int sheetNo = 1;
    int rowNum = 1;
    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    //初始化第一行
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    //生成其他行
    int firstPageNo = 1;
    while (true) {
        List<T> data = exportFunction.pageQuery(param, firstPageNo, pageSize);
        if (data == null || data.isEmpty()) {
            if (rowNum != 1) {
                sizeColumWidth(sheet, propertyList.size());
            }
            log.warn("查询结果为空,结束查询!");
            break;
        }
        int dataSize = data.size();
        for (int i = 1; i <= dataSize; i++, rowNum++) {
            T queryResult = data.get(i - 1);
            Object convertResult = exportFunction.convert(queryResult);
            if (rowNum > MAX_RECORD_COUNT_PEER_SHEET) {
                sizeColumWidth(sheet, propertyList.size());
                sheet = generateHeader(workbook, propertyList, excelEntity.getFileName() + "_" + sheetNo);
                sheetNo++;
                rowNum = 1;
                columnWidthMap.clear();
            }
            SXSSFRow row = sheet.createRow(rowNum);
            for (int j = 0; j < propertyList.size(); j++) {
                SXSSFCell cell = row.createCell(j);
                buildCellValue(cell, convertResult, propertyList.get(j));
                calculateColumWidth(cell, j);
            }
            if (nullCellCount == propertyList.size()) {
                log.warn("忽略一行空数据!");
                sheet.removeRow(row);
                rowNum--;
            }
            nullCellCount = 0;

        }
        if (data.size() < pageSize) {
            sizeColumWidth(sheet, propertyList.size());
            log.warn("查询结果数量小于pageSize,结束查询!");
            break;
        }
        firstPageNo++;
    }
    return workbook;
}
 

/**
 * 构建多Sheet Excel
 *
 * @param param
 * @param exportFunction
 * @param <R>
 * @param <T>
 * @return
 * @throws InvocationTargetException
 * @throws NoSuchMethodException
 * @throws ParseException
 * @throws IllegalAccessException
 */
public <R, T> SXSSFWorkbook generateMultiSheetWorkbook(R param, ExportFunction<R, T> exportFunction) throws Exception {
    int pageNo = 1;
    int sheetNo = 1;
    int rowNum = 1;
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    while (true) {
        List<T> data = exportFunction.pageQuery(param, pageNo, pageSize);
        if (data == null || data.isEmpty()) {
            if (rowNum != 1) {
                sizeColumWidth(sheet, propertyList.size());
            }
            log.warn("查询结果为空,结束查询!");
            break;
        }
        for (int i = 1; i <= data.size(); i++, rowNum++) {
            T queryResult = data.get(i - 1);
            Object convertResult = exportFunction.convert(queryResult);
            if (rowNum > recordCountPerSheet) {
                sizeColumWidth(sheet, propertyList.size());
                sheet = generateHeader(workbook, propertyList, excelEntity.getFileName() + "_" + sheetNo);
                sheetNo++;
                rowNum = 1;
                columnWidthMap.clear();
            }
            SXSSFRow bodyRow = sheet.createRow(rowNum);
            for (int j = 0; j < propertyList.size(); j++) {
                SXSSFCell cell = bodyRow.createCell(j);
                buildCellValue(cell, convertResult, propertyList.get(j));
                calculateColumWidth(cell, j);
            }
            if (nullCellCount == propertyList.size()) {
                log.warn("忽略一行空数据!");
                sheet.removeRow(bodyRow);
                rowNum--;
            }
            nullCellCount = 0;
        }
        if (data.size() < pageSize) {
            sizeColumWidth(sheet, propertyList.size());
            log.warn("查询结果数量小于pageSize,结束查询!");
            break;
        }
        pageNo++;
    }
    return workbook;
}
 

public ExcelSheet(SXSSFSheet sheet, ExcelWorkbook book) {
    workbook = book;
    currentSheet = sheet;
}