Java 导出Excel范例

最新推荐文章于 2022-11-16 17:46:35 发布

原创最新推荐文章于 2022-11-16 17:46:35 发布 · 362 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Java

本文介绍了一个使用Spring MVC Controller实现的日常费用Excel报表导出功能，包括处理表头、数据填充及响应设置。

SpringMvc Contorller

@SuppressWarnings("resource")
	@RequestMapping("/exportDailyExcel")
	public void exportDailyExcel(HttpServletRequest request,HttpServletResponse response){
		HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet1 = wb.createSheet("日常费用主表信息");  
        processDailyTableHead(sheet1);  
        
        Map<String,String> params = this.processDaily(request);
    	List<DailyExpense> lst = this.expenseService.findDailyExpenseByCondition(params,-1,-1);
        this.expenseService.processTableData(sheet1, lst, getDailyExcelModel());
        String fileName = "dailyExpense"+ DateTimeUtils.getNowTimeStr(null) +".xls";  
        response.setContentType("application/vnd.ms-excel");  
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");  
        try {  
            wb.write(response.getOutputStream());  
        } catch (IOException e) {  
            e.printStackTrace();  
        }   
        
	}

处理表头的方法 processDailyTableHead

 public void processDailyTableHead(HSSFSheet sheet){  
	        List<ExcelModel> list = getDailyExcelModel();  
	        HSSFRow row = sheet.createRow(0);  
	        for(int i=0; i<list.size(); i++){  
	            HSSFCell cell = row.createCell(i);  
	            cell.setCellValue(list.get(i).getColumName());  
	        }  
	 }

处理表格数据的方法processTableData，此处采用的是泛型，较为通用

public <T> void processTableData(HSSFSheet sheet, List<T> rList,
			List<ExcelModel> list) {
		for (int i = 0; i < rList.size(); i++) {
			HSSFRow row = sheet.createRow(i + 1);
			T t = rList.get(i);
			for (int m = 0; m < list.size(); m++) {
				try {
					HSSFCell cell = row.createCell(m);
					String fieldValueStr = "";
					String fieldName = list.get(m).getFieldName();
					Object fieldValue = null;
					if(fieldName.contains(".")){
						String str = fieldName.substring(0,fieldName.indexOf('.'));
						fieldValue = PropertyUtils.getProperty(t, str);
						if(fieldValue == null){
							fieldValueStr="";
							cell.setCellValue(fieldValueStr);
							continue;
						}
					}
					fieldValue = PropertyUtils.getProperty(t, list
							.get(m).getFieldName());
					
					if (fieldValue != null) {
						fieldValueStr = fieldValue.toString();
						if(fieldValueStr.equals("true")){
							fieldValueStr="是";
						}else if(fieldValueStr.equals("false")){
							fieldValueStr="否";
						}
					}
					
					cell.setCellValue(fieldValueStr);
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				}
			}
		}
	}

Excel 单元格的抽象类 ExcelModel

public class ExcelModel{
	private int index=0;
	private String columName;
	private String fieldName;
	public ExcelModel(){
		
	}
	public ExcelModel(String columName,String fieldName){
		this.setIndex(index);
		this.setColumName(columName);
		this.setFieldName(fieldName);
	}
	public ExcelModel(int index,String columName,String fieldName){
		this.setIndex(index);
		this.setColumName(columName);
		this.setFieldName(fieldName);
	}
	public int getIndex() {
		return index;
	}
	public void setIndex(int index) {
		this.index = index;
	}
	public String getColumName() {
		return columName;
	}
	public void setColumName(String columName) {
		this.columName = columName;
	}
	public String getFieldName() {
		return fieldName;
	}
	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}
}

拿到单元格头部的方法getDailyExcelModel

 public List<ExcelModel> getDailyExcelModel(){  
	        List<ExcelModel> list = new ArrayList<ExcelModel>();  
	        list.add(new ExcelModel(list.size(),"单据编号","expenseCode"));  
	        list.add(new ExcelModel(list.size(),"申请人","applicantName"));  
	        list.add(new ExcelModel(list.size(),"费用承担部门","requestByDeparmentName"));  
	        
	        list.add(new ExcelModel(list.size(),"专项","specialProject.name"));  
	        list.add(new ExcelModel(list.size(),"申请日期","applicationDate"));  
	        list.add(new ExcelModel(list.size(),"支付方式","paymentTypeName")); 
	        
	        list.add(new ExcelModel(list.size(),"报销金额","requestAmount"));  
	        list.add(new ExcelModel(list.size(),"是否冲销款","payedByLoanOption"));  
	        list.add(new ExcelModel(list.size(),"冲销款金额","payedByLoanAmount")); 
	        
	        return list;  
	 }

采用的POI版本

<dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>3.11</version>
    </dependency>
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.11</version>
    </dependency>

标签

#java Excel