Excel表格的生成

迷南。 2022-05-22 11:40 195阅读 0赞

本文用的的框架是springMVC，当然不使用springmvc也可以使用。

用到的插件是apache.poi，一下是自己测试的一个例子

由于自己需要，所以编写的方法传入的是List<Map<String,Object>>类型参数，读者可根据自己需要编写不同的方法

package com.springmvc.handler;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.math.BigDecimal;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.servlet.View;
    
    @Controller
    public class BuildExcelHander implements View{
    	
    	@Override
    	public String getContentType() {
    		return "application/vnd.ms-excel;charset=" + System.getProperty("file.encoding");
    	}
    
    	@Override
    	public void render(Map<String, ?> model, HttpServletRequest request, HttpServletResponse response)
    			throws Exception {
    		String encoding = System.getProperty("file.encoding");
    		response.setContentType("application/vnd.ms-excel;charset=" + encoding);
    		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(new Date().getTime() + ".xls",encoding));
    		String[] headers = {"ID","姓名","性别","邮箱"};
    		List<Map<String,Object>> datas = new ArrayList<>();
    		Map<String, Object> map = new LinkedHashMap<>();
    		map.put("id", 23);
    		map.put("name", "lqf");
    		map.put("sex", "男");
    		map.put("email", "liqingfeng@csii.com.cn");
    		datas.add(map);
    		String pattern = "yyyy-MM-dd:hh-mm-ss";
    		exportExcel(headers, datas, response.getOutputStream(), pattern);
    		
    	}
    	
    	
    	public void exportExcel(String[] headers, List<Map<String,Object>> datas, OutputStream os, String pattern) throws IOException {
    		HSSFWorkbook workBook = new HSSFWorkbook();
    		Sheet sheet = workBook.createSheet();
    		//设置表格属性
    		Row row = setSheetProperty(sheet);
    		//设置表格头
    		setSheetHeader(headers, workBook, row);
    		//为表格体赋值
    		setSheetBody(datas, sheet);
    		
    		workBook.write(os);
    	}
    
    	/**
    	 * 设置表格头
    	 * @param headers
    	 * @param workBook
    	 * @param row
    	 */
    	private void setSheetHeader(String[] headers, HSSFWorkbook workBook, Row row) {
    		//设置表格头样式
    		HSSFCellStyle headerStyle = getHeaderStyle(workBook);
    		Cell cell ;
    		for(int i = 0; headers.length > i; i++) {
    			cell = row.createCell(i);
    			cell.setCellValue(headers[i]);
    			cell.setCellStyle(headerStyle);
    		}
    	}
    	
    	/**
    	 * 设置Sheet属性
    	 * @param sheet
    	 * @return
    	 */
    	private Row setSheetProperty(Sheet sheet) {
    		sheet.setColumnWidth(3, 25 * 256);
    		Row row = sheet.createRow(0);
    		return row;
    	}
    
    	/**
    	 * 设置表格中的数据
    	 * @param datas
    	 * @param sheet
    	 */
    	private void setSheetBody(List<Map<String, Object>> datas, Sheet sheet) {
    		Row row;
    		Cell cell;
    		for (int i = 0; datas.size() > i; i++) {
    			row = sheet.createRow(i + 1);
    			int j = 0;
    			for (Map.Entry<String, Object> set : datas.get(i).entrySet()) {
    				cell = row.createCell(j);
    				Object obj = set.getValue();
    				setCellValue(cell, obj);
    				j++;
    			}
    		}
    	}
    
    	/**
    	 * 设置表格头样式
    	 * @param workBook
    	 * @return
    	 */
    	private HSSFCellStyle getHeaderStyle(HSSFWorkbook workBook) {
    		HSSFCellStyle headerStyle = workBook.createCellStyle();
    		HSSFFont headerFont = workBook.createFont();
    		headerFont.setFontHeight((short) (15 * 20));
    		headerFont.setFontName("黑体");
    		headerStyle.setFont(headerFont);
    		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		return headerStyle;
    	}
    
    	/**
    	 * 在表格中添加数据
    	 * @param cell
    	 * @param obj
    	 */
    	private void setCellValue(Cell cell, Object obj) {
    		if (obj instanceof Number) {
    			if (obj instanceof Integer) {
    				cell.setCellValue(((Integer) obj).doubleValue());
    			} else if (obj instanceof BigDecimal) {
    				cell.setCellValue(((BigDecimal) obj).doubleValue());
    			} else if (obj instanceof Long) {
    				cell.setCellValue(((Long) obj).doubleValue());
    			} else if (obj instanceof Double) {
    				cell.setCellValue((double) obj);
    			} else if (obj instanceof Short) {
    				cell.setCellValue(((Short) obj).doubleValue());
    			}
    		} else if (obj instanceof Date) {
    			cell.setCellValue((Date) obj);
    		} else {
    			cell.setCellValue(obj.toString());
    		}
    	}
    
    }

以下是在springmvc配置文件对改类的配置，若果没有使用springmvc或没有又自定义视图则不需要下面的配置，若没有使用@RequestMapping对方法进行注释的方法，二十使用了实现View接口的方法实现则需要在配置文件中对自定义视图进行配置，让框架能处理。

<mvc:annotation-driven></mvc:annotation-driven>
    	
    	
    	<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
    		<property name="order" value="100"></property>
    	</bean>