简化excel生成的工具类

 

首先引入apache poi包。

示例，可以标记到field或者method上。

import org.springframework.core.annotation.Order;
@AllArgsConstructor
@Getter
@Setter
public class Info{
	@Order(1)
	private String a;
	@Order(2)
	private int b;
	@Order(3)
	private Date c;
	@Order(4)
	public Date getC(){
		return c;
	}
	
	@Order(5)
	public String getD(){
		return "d";
	}
}

 可以一次性添加多个sheet

new ExcelBuilder().newSheet("sheet1").data(infos).clazz(Info.class).header("文章标题", "发布日期","作者", "栏目名字")
.newSheet("sheet2").data(infos).clazz(Info.class).header("文章标题2", "发布日期2","作者2", "栏目名字2")
.build()
.output(response，"下载文件名");

源码如下

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.annotation.Order;
@SuppressWarnings("rawtypes")
public class ExcelBuilder {
	private List<String> sheetNames = new ArrayList<>();
	private List<String[]> headers = new ArrayList<>();
	private List<Class> clazzes = new ArrayList<>();
	private List<List> datas = new ArrayList<>();

	private Workbook workbook;
	private int offset = -1;

	public ExcelBuilder newSheet(String sheetName) {
		sheetNames.add(++offset, sheetName);
		return this;
	}

	public ExcelBuilder header(String... header) {
		headers.add(offset, header);
		return this;
	}

	public ExcelBuilder clazz(Class clazz) {
		clazzes.add(offset, clazz);
		return this;
	}

	public ExcelBuilder data(List data) {
		datas.add(offset, data);
		return this;
	}

	public ExcelBuilder build() throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
		workbook = new HSSFWorkbook();

		for (int i = 0; i < sheetNames.size(); i++) {
			Sheet sheet = workbook.createSheet(sheetNames.get(i));

			int rowOffset = 0;
			// header
			Row headerRow = sheet.createRow(rowOffset++);
			for (int j = 0; j < headers.get(i).length; j++) {
				headerRow.createCell(j).setCellValue(headers.get(i)[j]);
			}
			// datas
			Class clazz = clazzes.get(i);
			List<Object> data = datas.get(i);
			// find available fields or method and sort
			List<Method> methods = findAvailableMethod(clazz);
			
			// k should equal j
			// reflect and populate
			for (int k = 0; k < data.size(); k++) {
				Row row = sheet.createRow(rowOffset++);
				for (int j = 0; j < methods.size(); j++) {
					Method method = methods.get(j);
					Object invoke = method.invoke(data.get(k));
					row.createCell(j).setCellValue(invoke+"");
				}
			}
			//adjust column length,but cost time
			adjustColLength(sheet, data.size());
		}

		return this;

	}
	public Workbook get() {
		return workbook;
	}

	public void output(HttpServletResponse response,String fileName) throws IOException {
		response.setHeader("Cache-Control","no-cache");
		response.setHeader("Pragma","no-cache");
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;charset=GBK;filename="+ URLEncoder.encode(fileName+".xls", "UTF-8"));//设置文件名
		workbook.write(response.getOutputStream());
		
	}
	private String getMethod(Field field) {
		String str = field.getName().substring(0, 1).toUpperCase();
		str += field.getName().substring(1);
		str = "get" + str;
		return str;

	}
	private void adjustColLength(Sheet sheet,int col) {
		for(int i=0;i<col;i++) {
			sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, sheet.getColumnWidth(i)*175/100);
		}
	}
	
	private List<Method> findAvailableMethod(Class clazz) throws NoSuchMethodException, SecurityException{
		List<Field> fields = Stream.of(clazz.getDeclaredFields()).filter(a -> a.getAnnotation(Order.class) != null)
		.collect(Collectors.toList());
		HashMap<Integer, Method> map=new HashMap<>();
		for(Field field:fields) {
			String methodName = getMethod(field);
			Method method = clazz.getMethod(methodName);
			map.put(field.getAnnotation(Order.class).value(), method);
		}
		List<Method> methods=Stream.of(clazz.getMethods()).filter(m->m.getAnnotation(Order.class)!=null).collect(Collectors.toList());
		for (Method method : methods) {
			map.put(method.getAnnotation(Order.class).value(), method);
		}
		Set<Integer> keySet = map.keySet();
		List<Integer> keyList=new ArrayList<>(keySet);
		Collections.sort(keyList);
		List<Method> result=new ArrayList<>();
		for (Integer key : keyList) {
			result.add(map.get(key));
		}
		return result;
		
	}
	

}