导出execl的两种方法

最新推荐文章于 2021-07-20 11:18:21 发布

原创最新推荐文章于 2021-07-20 11:18:21 发布 · 置顶 · 253 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel导出功能

本文介绍使用Java实现导出Excel数据的两种方法。第一种直接操作单元格，设置样式，填充数据；第二种利用ObjectExcelView视图解析数据模型，简化导出流程。文章详细展示了如何从数据库获取数据并将其格式化为Excel表格，包括设置单元格样式、处理数据类型等。

第一种方式：
public HSSFWorkbook createExcel(List activities) {
InputStream resourceAsStream = null;
HSSFWorkbook xssfWorkbook = null;
try {
resourceAsStream = this.getClass().getResourceAsStream("/moble/activityinfo.xls");
xssfWorkbook = new HSSFWorkbook(resourceAsStream);
HSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
HSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); //新建单元格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFPatriarch patriarch = sheetAt.createDrawingPatriarch();
for (int i = 0; i < activities.size(); i++) {
HSSFRow row = sheetAt.createRow(i+1);
row.setHeight((short) 1200);

			Activity activity = activities.get(i);

			HSSFCell createCell0 = row.createCell(0);
			createCell0.setCellType(HSSFCell.CELL_TYPE_STRING);
			createCell0.setCellValue(activity.getThemeTitle());
			createCell0.setCellStyle(cellStyle);

			BigDecimal service_price = activity.getPrice();
			if (service_price == null) {
				service_price = new BigDecimal("0");
			}
			HSSFCell createCell1 = row.createCell(1);
			createCell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			createCell1.setCellValue( service_price.intValue()+ "");
			createCell1.setCellStyle(cellStyle);

			HSSFCell createCell2 = row.createCell(2);
			createCell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			createCell2.setCellValue(activity.getStartTime());
			createCell2.setCellStyle(cellStyle);

			HSSFCell createCell3 = row.createCell(3);
			createCell3.setCellType(HSSFCell.CELL_TYPE_STRING);
			createCell3.setCellValue(activity.getEndTime() + "");
			createCell3.setCellStyle(cellStyle);


			HSSFCell createCell4 = row.createCell(4);
			createCell4.setCellType(HSSFCell.CELL_TYPE_STRING);
			createCell4.setCellValue(activity.getPeopleNum() + "");
			createCell4.setCellStyle(cellStyle);
			if (activity.getState().equals("0")){
				HSSFCell createCell6 = row.createCell(5);
				createCell6.setCellType(HSSFCell.CELL_TYPE_STRING);
				createCell6.setCellValue("未开始");
				createCell6.setCellStyle(cellStyle);
			}
			if (activity.getState().equals("1")){
				HSSFCell createCell6 = row.createCell(5);
				createCell6.setCellType(HSSFCell.CELL_TYPE_STRING);
				createCell6.setCellValue("进行中");
				createCell6.setCellStyle(cellStyle);
			}
			if (activity.getState().equals("2")){
				HSSFCell createCell6 = row.createCell(5);
				createCell6.setCellType(HSSFCell.CELL_TYPE_STRING);
				createCell6.setCellValue("已满员");
				createCell6.setCellStyle(cellStyle);
			}
			if (activity.getState().equals("3")) {
				HSSFCell createCell6 = row.createCell(5);
				createCell6.setCellType(HSSFCell.CELL_TYPE_STRING);
				createCell6.setCellValue("已结束");
				createCell6.setCellStyle(cellStyle);
			}
		}
	} catch (IOException e) {
		e.printStackTrace();
	}
	return xssfWorkbook;
}

@RequestMapping("/downFile")
public void downFile(HttpServletResponse response) throws Exception{
Activity activity = new Activity();
activity.setCode(getUser().getCode());
List varOList =activityService.likeExample(activity);
HSSFWorkbook excel = createExcel(varOList);

	response.setContentType("application/vnd.ms-excel;charset=UTF-8");
	response.setCharacterEncoding("utf-8");
	//这里对文件名进行编码，保证下载时汉字显示正常
	String fileName = URLEncoder.encode("用户.xls", "utf-8");

	//Content-disposition属性设置成以附件方式进行下载
	response.setHeader("Content-disposition", "attachment;filename="
					+ fileName);

	OutputStream os = response.getOutputStream();
	excel.write(os);
	os.flush();
	os.close();
}	
第二种方式：
	@RequestMapping(value = "/export")
public ModelAndView exportExcel(Activity activity) {
	ModelAndView mv = new ModelAndView();
	try {
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<String> titles = new ArrayList<String>();

		titles.add("主题");    //1
		titles.add("费用");    //2
		titles.add("报名开始时间");    //3
		titles.add("报名结束时间");    //4
		titles.add("接受报名人数");    //5
		titles.add("状态");//6
		dataMap.put("titles", titles);


		activity.setCode(getUser().getCode());

		List<Activity> varOList =activityService.likeExample(activity);
		List<PageData> varList = new ArrayList<PageData>();
		for (Activity activity1 : varOList) {
			PageData vpd = new PageData();

			vpd.put("var1", (activity1.getThemeTitle()));    //1
			vpd.put("var2", (activity1.getPrice()));    //2
			vpd.put("var3", (activity1.getStartTime()));    //3
			vpd.put("var4", activity1.getEndTime());    //4
			vpd.put("var5",activity1.getPeopleNum());    //5
			vpd.put("var6", (activity1.getState()));  //6
			varList.add(vpd);
		}
		dataMap.put("varList", varList);
		ObjectExcelView erv = new ObjectExcelView();
		mv = new ModelAndView(erv, dataMap);
	} catch (Exception e) {
		e.printStackTrace();
	}
	return mv;

}