java 使用poi生成Excel ,多个 sheet 页

一、前端

window.location.href=contextPath + '/ExcelManageAction/exportCucCaseExcelDataInfo.action?'+$('#ffsearch').serialize();

注：这里使用的是get请求，直接使用序列化拿走整个搜索栏信息

二、后端

1. @Controller层

@RequestMapping(value = "/exportCucCaseExcelDataInfo", method = RequestMethod.GET)
	@MashupLogAnnotation(operator_id = "201609091514002", value = "", model_name = "导出报表数据", model_desc = "")
	public void exportCucCaseExcelDataInfo(ExcelDataInfo excelDataInfo, HttpServletRequest request,
			HttpServletResponse response, @RequestParam(value = "dataStartTime") String dataStartTime,
			@RequestParam(value = "dataEndTime") String dataEndTime,
			@RequestParam(value = "sheetCategory") String sheetCategory,
			@RequestParam(value = "cucCaseNo") String cucCaseNo,
			@RequestParam(value = "operateType") String operateType,
			@RequestParam(value = "currentState") String currentState,
			@RequestParam(value = "processState") String processState,
			@RequestParam(value = "infoCategory") String infoCategory,
			@RequestParam(value = "handleKey") String handleKey,
			@RequestParam(value = "businessModel1") String businessModel1,
			@RequestParam(value = "resourceServiceType") String resourceServiceType,
			@RequestParam(value = "productType") String productType) {}

// 或者用一个param打包接收，再一个个取
@RequestMapping(value = "/excel/export")
	public void excelExport(@RequestParam Map<String, String> param, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		LOGGER.info("导出excel参数:" + param);
		
		HSSFWorkbook wb = noticeService.export(param);
		response.setContentType("application/vnd.ms-excel");
		// response.setHeader("Content-disposition",
		// "attachment;filename=知识点.xls");
		response.setHeader("Content-disposition", "attachment; filename=" + toUtf8String("作业计划统计.xls"));
		OutputStream ouputStream = response.getOutputStream();
		wb.write(ouputStream);
		ouputStream.flush();
		ouputStream.close();
		System.out.println("导出excel成功");
}

2.@Service层

public File export(ExportMapping mapping,String sql,String path)throws Exception{
		if(mapping==null)
			throw new Exception("获取映射失败");
		        
		File excel=this.createFile(path, mapping);
	
		WritableWorkbook workbook = Workbook.createWorkbook(excel);
//		//配色
//		workbook.setColourRGB(Colour.YELLOW2, 0x00, 0xae, 0x9d);//青绿色
//		workbook.setColourRGB(Colour.YELLOW, 0xa1, 0xa3, 0xa6);//银鼠色
//		workbook.setColourRGB(Colour.WHITE, 0xdf, 0x94, 0x64);//小麦色
//		workbook.setColourRGB(Colour.VIOLET2, 0x65, 0xc2, 0x94);//若竹色
		
		try
		{
			List<Map<String, Object>> data = jdbc.queryForList(sql);
			
			if (data.size()==0){
				// 一个sheet能装下
				WritableSheet sheet = workbook.createSheet("Sheet 1", 0);
				this.setExcelHead(mapping, sheet);
			}
			else if(data.size()<65535) {
				// 一个sheet能装下
				WritableSheet sheet = workbook.createSheet("Sheet 1", 0);
				this.setExcelHead(mapping, sheet);
				setData(sheet, mapping, data, 0, data.size()-1);
			}else{
				// 需要多个sheet页
				int a=data.size()/65535; // sheet 页数
				int b = data.size()%65535; // 最后一个sheet页的条数
				for (int i = 0; i < a; i++) {
					WritableSheet sheet = workbook.createSheet("Sheet "+(i+1), i);
					// 设置标题
					this.setExcelHead(mapping, sheet);
					// 添加单元格
					int start=(i*65535);  
					int end=((i+1)*65535)-1;
					for (int j = start; j <= end; j++) {
						setData(sheet, mapping, data, start, end);
					}
				}
				if(b>=1){
					WritableSheet sheet = workbook.createSheet("Sheet "+(a+1), a);
					// 设置标题
					this.setExcelHead(mapping, sheet);
					setData(sheet, mapping, data, a*655, data.size()-1);
				}
			}
	
			workbook.write();
			return excel;
		}
		finally
		{
			if(workbook!=null)
				workbook.close();
		}
		
	}
	protected void setExcelHead(ExportMapping mapping,WritableSheet sheet) throws RowsExceededException, WriteException
	{
		SheetSettings settings = sheet.getSettings();
		settings.setVerticalFreeze(1);
		
		WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD);// 字体样式
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setBackground(Colour.WHITE);
        
		WritableCellFormat cellFormat=new WritableCellFormat();
		cellFormat.setBackground(Colour.YELLOW2);
		cellFormat.setAlignment(Alignment.CENTRE);
		cellFormat.setWrap(true);
		cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	    sheet.setColumnView(1, 22);
		
		List<ColumnMapping> columns=mapping.getColumns();
		for(ColumnMapping column:columns)
		{
			WritableCellFormat cellFormat3=new WritableCellFormat();
			cellFormat3.setAlignment(Alignment.CENTRE);
			cellFormat3.setWrap(true);
			cellFormat3.setVerticalAlignment(VerticalAlignment.CENTRE);
			int excelIndex=column.getExcelIndex()==-1?column.getIndex()-1:column.getExcelIndex();
			Label cell=new Label(excelIndex,0,mapping.getLocale().equals(java.util.Locale.US) ? column.getDisplayEnName() : column.getDisplayName(),cellFormat3);
			sheet.addCell(cell);
		}
	}

最后

以上就是故意花生为你收集整理的java 使用poi生成Excel ,多个 sheet 页的全部内容，希望文章能够帮你解决java 使用poi生成Excel ,多个 sheet 页所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。