POI实现导出Excel数据工具类

  项目中很多地方需要导出excel数据，因此需要将导出excel数据封装成工具类。

实现思路:

      1、将查询结果List<T>以及T作为入参。

      2、新建一个注解，在T对象需要生成表格数据的字段加上这个注解。

供外部调用的方法:

import java.io.IOException;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import com.coolpad.store.common.export.DataExportUtil;

public class WebDataExporter {
	static public <T> boolean exportWebExcelWithAnnotation(HttpServletResponse resp, List<T> dataList, Class<T> clzz) throws IOException {
		resp.setContentType("application/vnd.ms-excel");
		resp.setHeader("Content-disposition", "attachment;filename=ExportData.xls");  
		return DataExportUtil.<T> exportExcelWithAnnotation(resp.getOutputStream(),dataList,clzz);	
	}
}

导出Excel实现方法:

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class DataExportUtil {

	static public <T> boolean exportExcelWithAnnotation(OutputStream ouputStream, List<T> dataList, Class<T> clzz) {
	
		HSSFWorkbook wb = new HSSFWorkbook();
		
		HSSFSheet sheet = wb.createSheet("导出数据");
		
		sheet.setColumnWidth(0, 20 * 256);
		
		HSSFCellStyle style = wb.createCellStyle();
		// style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
		
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setColor(HSSFColor.BLACK.index);
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		style.setFont(font);
		
		HSSFCellStyle styleHeader = wb.createCellStyle();
		styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		HSSFFont fontHeader = wb.createFont();
		fontHeader.setFontHeightInPoints((short) 15);
		fontHeader.setColor(HSSFColor.BLACK.index);
		fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontHeader.setFontName("黑体");
		styleHeader.setFont(font);
		// 背景色HSSFColor.LIGHT_GREEN.index
		styleHeader.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		styleHeader.setFillBackgroundColor(HSSFColor.YELLOW.index);
		// row.setRowStyle(styleHeader);
		HSSFRow row = sheet.createRow((int) 0);
		row.setHeightInPoints(30);
		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("序号");
		cell.setCellStyle(styleHeader);

		Map<String, String> headerMap = new LinkedHashMap<String, String>();
		List<String> headers = new LinkedList<String>();
		
		Field[] fields = clzz.getDeclaredFields();
		for (Field field : fields) {
			ExportHeader exHeader = field.getAnnotation(ExportHeader.class);
			if(null==exHeader){
				continue;
			}
			String header = exHeader.value();
			headers.add(header);
			
			headerMap.put(header, field.getName());
		}
		for (int i = 0; i < headers.size(); i++) {
			cell = row.createCell((short) i + 1);
			cell.setCellValue(headers.get(i));
			cell.setCellStyle(styleHeader);
		}
		for (int i = 0; i < dataList.size(); i++) {
			sheet.setColumnWidth(i, 20 * 256);

			row = sheet.createRow((int) i + 1);
			row.setHeightInPoints(20);
			BeanMap beanMap = new BeanMap(dataList.get(i));

			cell = row.createCell((short) 0);
			cell.setCellValue(i + 1);
			cell.setCellStyle(style);

			int j = 0;
			for (Map.Entry<String, String> kv : headerMap.entrySet()) {
				String header = kv.getKey();
				String fieldName = kv.getValue();
				Object val = beanMap.get(fieldName);

				cell = row.createCell((short) j + 1);
				cell.setCellValue(val!=null?val.toString():"");
				cell.setCellStyle(style);
				j++;
			}
		}
		try {
			wb.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}
}

注解类:

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportHeader {
	String value();
}

查询结果数据对象:

public class User {
	private int id;//没有注解，不需要导出这个字段

	@ExportHeader("姓名")
	private String name;

	@ExportHeader("年龄")
	private int age;

	@ExportHeader("地址")
	private String address;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
}

  至此，已经完成了Excel导出工具的编写。在项目的其他地方调用WebDataExporter的exportWebExcelWithAnnotation方法即可。