Java Code Examples for org.apache.poi.xssf.streaming.SXSSFWorkbook#createCellStyle()

/**
 * 大标题样式
 *
 * @param wb
 * @param cell
 * @param sxssfRow
 */
public static void setLabelStyles(SXSSFWorkbook wb, Cell cell, SXSSFRow sxssfRow) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    sxssfRow.setHeight((short) (399 * 2));
    XSSFFont font = (XSSFFont) wb.createFont();
    font.setFontName("宋体");
    font.setFontHeight(16);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);
}
 

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 workbook 工作簿
 * @param fontSize 字体大小
 * @return 单元格样式
 */
private static CellStyle createCellStyle(SXSSFWorkbook workbook, short fontSize, short aligment, Boolean bold) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(aligment);
    //垂直居中
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    org.apache.poi.ss.usermodel.Font font = workbook.createFont();
    if (bold) {
        //加粗字体
        font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    }
    font.setFontHeightInPoints(fontSize);
    cellStyle.setFont(font);
    return cellStyle;
}
 

@SuppressWarnings("unchecked")
public void exportExcelTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
	List<VariableCategory> variableCategories=(List<VariableCategory>)httpSessionKnowledgeCache.get(req, VCS_KEY);
	if(variableCategories==null){
		KnowledgeBase knowledgeBase=buildKnowledgeBase(req);
		variableCategories=knowledgeBase.getResourceLibrary().getVariableCategories();
	}
	SXSSFWorkbook wb = new SXSSFWorkbook();
	XSSFCellStyle style=(XSSFCellStyle)wb.createCellStyle();
	Color c=new Color(147,208,15);
	XSSFColor xssfColor=new XSSFColor(c);
	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	style.setFillForegroundColor(xssfColor);
	for(VariableCategory vc:variableCategories){
		buildSheet(wb, vc,style);
	}
	resp.setContentType("application/x-xls");
	resp.setHeader("Content-Disposition","attachment; filename=urule-batch-test-template.xlsx");
	OutputStream outputStream=resp.getOutputStream();
	wb.write(outputStream);;
	outputStream.flush();
	outputStream.close();
}
 

/**
 * 设置数据：无样式（行、列、单元格样式）
 *
 * @param wb
 * @param sxssfRow
 * @param dataLists
 * @param regionMap
 * @param columnMap
 * @param paneMap
 * @param sheetName
 * @param labelName
 * @param dropDownMap
 * @throws Exception
 */
public static void setDataListNoStyle(SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<String[]>> dataLists, HashMap regionMap,
                                      HashMap columnMap, HashMap paneMap, String[] sheetName, String[] labelName, HashMap dropDownMap,Integer defaultColumnWidth,Integer fontSize) throws Exception {
    if (dataLists == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export data(type:List<List<String[]>>) cannot be empty!");
    }
    if (sheetName == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export sheet(type:String[]) name cannot be empty!");
    }
    int k = 0;
    for (List<String[]> listRow : dataLists) {
        SXSSFSheet sxssfSheet = wb.createSheet();
        sxssfSheet.setDefaultColumnWidth(defaultColumnWidth);
        wb.setSheetName(k, sheetName[k]);
        CellStyle cellStyle = wb.createCellStyle();
        XSSFFont font = (XSSFFont) wb.createFont();

        int jRow = 0;
        //  自定义：大标题（看该方法说明）。
        jRow = setLabelName(jRow, k, wb, labelName, sxssfRow, sxssfSheet, listRow);

        //  自定义：每个表格固定表头（看该方法说明）。
        Integer pane = 1;
        if (paneMap != null && paneMap.get(k + 1) != null) {
            pane = (Integer) paneMap.get(k + 1) + (labelName != null ? 1 : 0);
            createFreezePane(sxssfSheet, pane);
        }
        //  自定义：每个单元格自定义合并单元格：对每个单元格自定义合并单元格（看该方法说明）。
        if (regionMap != null) {
            setMergedRegion(sxssfSheet, (ArrayList<Integer[]>) regionMap.get(k + 1));
        }
        //  自定义：每个单元格自定义下拉列表：对每个单元格自定义下拉列表（看该方法说明）。
        if (dropDownMap != null) {
            setDataValidation(sxssfSheet, (List<String[]>) dropDownMap.get(k + 1), listRow.size());
        }
        //  自定义：每个表格自定义列宽：对每个单元格自定义列宽（看该方法说明）。
        if (columnMap != null) {
            setColumnWidth(sxssfSheet, (HashMap) columnMap.get(k + 1));
        }
        //  默认样式。
        setStyle(cellStyle, font,fontSize);

        //  写入小标题与数据。
        Integer SIZE = listRow.size() < MAX_ROWSUM ? listRow.size() : MAX_ROWSUM;
        for (int i = 0; i < SIZE; i++) {
            sxssfRow = sxssfSheet.createRow(jRow);
            for (int j = 0; j < listRow.get(i).length; j++) {
                Cell cell = createCell(sxssfRow, j, listRow.get(i)[j]);
                cell.setCellStyle(cellStyle);
            }
            jRow++;
        }
        k++;
    }
}
 

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