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

/**
 * 构建模板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 cell
 * @param entity
 * @param property
 */
private void buildCellValue(SXSSFCell cell, Object entity, ExcelPropertyEntity property) throws Exception {
    Field field = property.getFieldEntity();
    Object cellValue = field.get(entity);
    if (StringUtil.isBlank(cellValue) || "0".equals(cellValue.toString()) || "0.0".equals(cellValue.toString()) || "0.00".equals(cellValue.toString())) {
        nullCellCount++;
    }
    if (cellValue == null) {
        cell.setCellValue("");
    } else if (cellValue instanceof BigDecimal) {
        if (-1 == property.getScale()) {
            cell.setCellValue(cellValue.toString());
        } else {
            cell.setCellValue((((BigDecimal) cellValue).setScale(property.getScale(), property.getRoundingMode())).toString());
        }
    } else if (cellValue instanceof Date) {
        cell.setCellValue(format(property.getDateFormat(), (Date) cellValue));
    } else {
        cell.setCellValue(cellValue.toString());
    }
}
 

/**
 * 自动适配中文单元格
 *
 * @param sheet
 * @param cell
 * @param columnIndex
 */
private void calculateColumWidth(SXSSFCell cell, Integer columnIndex) {
    if (openAutoColumWidth) {
        String cellValue = cell.getStringCellValue();
        int length = cellValue.getBytes().length;
        length += (int) Math.ceil((double) ((cellValue.length() * 3 - length) / 2) * 0.1D);
        length = Math.max(length, CHINESES_ATUO_SIZE_COLUMN_WIDTH_MIN);
        length = Math.min(length, CHINESES_ATUO_SIZE_COLUMN_WIDTH_MAX);
        if (columnWidthMap.get(columnIndex) == null || columnWidthMap.get(columnIndex) < length) {
            columnWidthMap.put(columnIndex, length);
        }
    }
}
 

/**
 * 初始化第一行的属性
 *
 * @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;
}
 

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;
}
 

@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);
}
 

/**
 * @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 static void exportExcelForLecturerProfit(HttpServletResponse response, Page<LecturerProfitVO> result) throws IOException {
	// 创建一个workbook 对应一个excel文件
	final SXSSFWorkbook workBook = new SXSSFWorkbook();
	SXSSFSheet sheet = workBook.createSheet("讲师分润报表");

	// 列名和列宽
	String[] names = { "讲师名称", "银行卡号", "银行名称", "银行开户名", "讲师分润（元）", "平台分润（元）", "时间" };// 表头
	Integer[] widths = { 25, 15, 15, 25, 25, 25, 25 };// 列宽

	// 创建第一行
	SXSSFRow row = sheet.createRow(0);

	// 设置第一行样式
	CellStyle headStyle = workBook.createCellStyle();
	headStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 水平居中
	headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

	// 设置第一行字体
	Font headFont = workBook.createFont();
	headFont.setBold(true);
	headStyle.setFont(headFont);

	// 设置第一行单元格内容、单元格样式
	for (int i = 0; i < names.length; i++) {
		SXSSFCell cell = row.createCell(i);
		cell.setCellValue(names[i]);
		cell.setCellStyle(headStyle);
		sheet.setColumnWidth(i, widths[i] * 256);
	}

	// 从第二行开始遍历出分润记录表的数据，再写入单元格
	SXSSFRow row1 = sheet.createRow(1);
	int r = 1;
	for (LecturerProfitVO bean : result.getList()) {
		row1 = sheet.createRow(r++);
		row1.createCell(0).setCellValue(bean.getLecturerVO().getLecturerName());
		row1.createCell(1).setCellValue(bean.getBankCardNo());
		row1.createCell(2).setCellValue(bean.getBankName());
		row1.createCell(3).setCellValue(bean.getBankUserName());
		row1.createCell(4).setCellValue(bean.getLecturerProfit().doubleValue());
		row1.createCell(5).setCellValue(bean.getPlatformProfit().doubleValue());
		row1.createCell(6).setCellValue(new SimpleDateFormat("yyyy/MM/dd").format(bean.getGmtCreate()));
	}
	try {
		workBook.write(response.getOutputStream());
		response.getOutputStream().flush();
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		if (response.getOutputStream() != null)
			response.getOutputStream().close();
		if (workBook != null)
			workBook.close();
	}
}
 

static SXSSFCell newSXSSFCell(SXSSFRow row, int index) {
    return row.createCell(index);
}