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

protected Sheet createSheet(SXSSFWorkbook wb,Paper paper,String name){
	Sheet sheet = null;
	if(name==null){
		sheet=wb.createSheet();
	}else{			
		sheet=wb.createSheet(name);
	}
	PaperType paperType=paper.getPaperType();
	XSSFPrintSetup printSetup=(XSSFPrintSetup)sheet.getPrintSetup();
	Orientation orientation=paper.getOrientation();
	if(orientation.equals(Orientation.landscape)){
		printSetup.setOrientation(PrintOrientation.LANDSCAPE);			
	}
	setupPaper(paperType, printSetup);
	int leftMargin=paper.getLeftMargin();
	int rightMargin=paper.getRightMargin();
	int topMargin=paper.getTopMargin();
	int bottomMargin=paper.getBottomMargin();
	sheet.setMargin(Sheet.LeftMargin, UnitUtils.pointToInche(leftMargin));
	sheet.setMargin(Sheet.RightMargin, UnitUtils.pointToInche(rightMargin));
	sheet.setMargin(Sheet.TopMargin, UnitUtils.pointToInche(topMargin));
	sheet.setMargin(Sheet.BottomMargin, UnitUtils.pointToInche(bottomMargin));
	return sheet;
}
 

/**
 * 初始化部分信息
 */
private void init(){
	wb = new SXSSFWorkbook(ROW_LIMIT);
	sheet = wb.createSheet();
	
	firstStyle = createFirstCellStyle();
	style = createContentCellStyle();
	datastyle = createContentCellStyle();
}
 

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

/**
 * 创建一个sheet
 *
 * @param workbook  workbook
 * @param sheetName 名称
 * @param header    表头
 * @param data      数据
 */
private void createSheet(SXSSFWorkbook workbook, String sheetName, String[][] header, List<?> data) throws Exception {
    Sheet sheet = workbook.createSheet(sheetName);
    // 单元行，单元格
    Row row;
    Cell cell;
    //列数
    int cellNum = header.length;
    //设置表头
    row = sheet.createRow(0);
    for (int i = 0; i < cellNum; i++) {
        cell = row.createCell(i);
        String str = header[i][1];
        cell.setCellValue(str);
        //设置列宽为表头的宽度+4
        sheet.setColumnWidth(i, (str.getBytes("utf-8").length + 6) * 256);
    }

    int rowNum = data.size();
    if (rowNum == 0) {
        return;
    }
    //获取Object 属性名与field属性的映射，后面通过反射获取值来设置到cell
    Field[] fields = data.get(0).getClass().getDeclaredFields();
    Map<String, Field> fieldMap = new HashMap<>(fields.length);
    for (Field field : fields) {
        field.setAccessible(true);
        fieldMap.put(field.getName(), field);
    }
    Object object;
    for (int i = 0; i < rowNum; i++) {
        row = sheet.createRow(i + 1);
        object = data.get(i);
        for (int j = 0; j < cellNum; j++) {
            cell = row.createCell(j);
            this.setCell(cell, object, fieldMap, header[j][0]);
        }
    }
}
 

private void buildSheet(SXSSFWorkbook wb,VariableCategory vc,XSSFCellStyle style){
	String name=vc.getName();
	Sheet sheet=wb.createSheet(name);
	Row row=sheet.createRow(0);
	List<Variable> variables=vc.getVariables();
	for(int i=0;i<variables.size();i++){
		sheet.setColumnWidth(i,4000);
		Cell cell=row.createCell(i);
		Variable var=variables.get(i);
		cell.setCellValue(var.getLabel());
		cell.setCellStyle(style);
	}
}
 

/**
 * 
 */
public Excel2007Writer(File file, int cache_size) {
	wb = new SXSSFWorkbook(cache_size);
	try {
		out = new FileOutputStream(file);
	} catch (FileNotFoundException e) {
		LOGGER.error("", e);
	}
	createHelper = wb.getCreationHelper();
	sh = wb.createSheet();

	rowHeader = sh.createRow(0);
}
 

/**
 * 
 */
public Excel2007Writer(File file, int cache_size) {
	wb = new SXSSFWorkbook(cache_size);
	try {
		out = new FileOutputStream(file);
	} catch (FileNotFoundException e) {
		LOGGER.error("", e);
	}
	createHelper = wb.getCreationHelper();
	sh = wb.createSheet();

	rowHeader = sh.createRow(0);
}
 

/**
 * 设置数据：无样式（行、列、单元格样式）
 *
 * @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();
	}
}
 

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