java Excel导出

Excel 导出

• srv

/**
 * 
 * @param response 
 * @param list 需导出的数据
 */
void createExcelWorkBookInfo(HttpServletResponse response, List<RtHtsyPositionCoverPojo> list);

• srvImpl

@Override
	public void createExcelWorkBookInfo(HttpServletResponse response, List<RtHtsyPositionCoverPojo> list) {
		String nowStr = new Date().getTime() + "";
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			response.reset();
			response.setHeader("content-disposition",
					"attachment;filename=" + new String(("信息导出" + nowStr).getBytes("gb2312"), "ISO8859-1") + ".xls");
			response.setContentType("APPLICATION/msexcel");
			HSSFWorkbook workbook = new HSSFWorkbook();
            //需要导出的字段
			Map<String, String> fildsMap = new HashMap<>();
			fildsMap.put("declareYear", "申报年度");
			fildsMap.put("working","单位");
			fildsMap.put("name","姓名");
			fildsMap.put("udicTecOqCurVal","现任职资格");
			fildsMap.put("udicTecOqDecVal","申请任职资格");
			fildsMap.put("status","申报状态");
			excelService.export(list, fildsMap, workbook, null);
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				// 强行将响应缓存中的内容发送到目的地
				response.flushBuffer();
				if (out != null) {
					out.flush();
					out.close();
				}
			} catch (IOException e) {
				LOGGER.error(e.toString());
			}
		}
	}

• ExcelService

public interface ExcelService {
	<T> void export(List<T> results, Map<String, String> fildsMap, HSSFWorkbook wb, Integer rowNum);
}

• ExcelService

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
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;
import org.springframework.stereotype.Service;
import com.wisdombud.casic4.web.commonexcel.service.ExcelService;
@Service
public class ExcelServiceImpl implements ExcelService{
	public <T> void export(List<T> results, Map<String, String> fildsMap, HSSFWorkbook wb, Integer rowNum) {
		if (rowNum == null) {
			rowNum = rowMaxNum;
		}
		for (int i = 0; i < results.size(); i += rowNum) {
			List<T> newlist = new ArrayList<>();
			if ((i + rowNum) < results.size()) {
				newlist = results.subList(i, i + rowNum);
			} else {
				newlist = results.subList(i, results.size());
			}
			HSSFSheet sheet = wb.createSheet();
			for (int j = 0; j < fildsMap.size(); j++) {
				sheet.setColumnWidth(i, 5000);
			}
			this.fillCommonHeader(wb, sheet, fildsMap);
			this.fillCommonBody(newlist, sheet, wb, fildsMap);
		}
	}
	
	private void fillCommonHeader(HSSFWorkbook wb, HSSFSheet sheet, Map<String, String> fildsMap) {
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 18);
		style.setFont(font);
		HSSFRow row = sheet.createRow(0);
		int i = 0;
		for (String key : fildsMap.keySet()) {
			HSSFCell cell = row.createCell(i++);
			String value = fildsMap.get(key);
			cell.setCellValue(value);
			cell.setCellStyle(style);
		}
	}
	
	private <T> void fillCommonBody(List<T> results, HSSFSheet sheet, HSSFWorkbook wb, Map<String, String> fildsMap) {
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 16);
		style.setFont(font);
		int i = 1;
		for (T inst : results) {
			HSSFRow row = sheet.createRow(i++);
			row.setRowStyle(style);
			int j = 0;
			for (String key : fildsMap.keySet()) {
				HSSFCell cell = row.createCell(j++);
				String val = setCell(key, inst);
				cell.setCellValue(val);
			}
		}
	}
	

	@SuppressWarnings("rawtypes")
	private <T> String setCell(String field, T data) {
		String val = "";

		Class clazz = data.getClass();
		Field[] fields = ArrayUtils.addAll(clazz.getDeclaredFields(), clazz.getSuperclass().getDeclaredFields());
		for (int i = 0; i < fields.length; i++) {
			if (fields[i].getName().equals(field)) {
				try {
					Object resultObject = invokeMethod(data, fields[i].getName(), null);
					if (resultObject == null) {
						resultObject = "";
					}
					val = resultObject.toString();
					break;
				} catch (SecurityException | NoSuchMethodException | IllegalArgumentException | IllegalAccessException
						| InvocationTargetException e) {
					e.printStackTrace();
				}
			}
		}

		return val;
	}
	
	
	public Object invokeMethod(Object owner, String fieldname, Object[] args) throws SecurityException,
			NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
		Class ownerClass = owner.getClass();
		
		Method method = null;
		method = ownerClass.getMethod(this.toGetter(fieldname));
		
		Object object = null;
		object = method.invoke(owner);
		
		return object;
		}
	
	public String toGetter(String fieldname) {

		if (fieldname == null || fieldname.length() == 0) {
			return null;
		}

		if (fieldname.length() > 2) {
			String second = fieldname.substring(1, 2);
			if (second.equals(second.toUpperCase())) {
				return new StringBuffer("get").append(fieldname).toString();
			}
		}

		fieldname = new StringBuffer("get").append(fieldname.substring(0, 1).toUpperCase())
				.append(fieldname.substring(1)).toString();

		return fieldname;
	}

	
}

• maven

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