java 导出excel实例(内含通用excel导出工具类)

1.创建一个ExportPropertiesDto，用于动态导出表头（前端传过来的json数组类似：[{"field":"year","fieldName":"年份"},{"field":"departmentName","fieldName":"部门"},{"field":"typeName","fieldName":"类型名"}]）

import java.io.Serializable;
public class ExportPropertiesDto implements Serializable{

	private static final long serialVersionUID = 1L;

	private String field;
	
	private String fieldName;

	public ExportPropertiesDto() {
		super();
	}

	public ExportPropertiesDto(String field, String fieldName) {
		super();
		this.field = field;
		this.fieldName = fieldName;
	}

	public String getField() {
		return field;
	}

	public void setField(String field) {
		this.field = field;
	}

	public String getFieldName() {
		return fieldName;
	}

	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}

}

2.整一个ExportExcelUtil工具类

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import java.io.IOException;
import java.util.List;

public final class ExportExcelUtil {

    private static final int HEADER_WIDTH = 20 * 256;
    private static final int COLUMN_WIDTH = 30 * 256;
    private static final int COLUMN_INDEX1 = 2;
    private static final int COLUMN_INDEX2 = 7;
    private static final int ROW_NUMBER = 0;
    private static final int FONT_HEIGHT1 = 10;
    private static final int FONT_HEIGHT2 = 12;

    private ExportExcelUtil() {

    }

    public static void writeExcel(HSSFWorkbook wb,HSSFSheet sheet, String[] headers, List<String[]> list)
                        throws IOException {
        for (int i = 0; i < headers.length; i++) {
            sheet.setColumnWidth(i, HEADER_WIDTH);
        }
        sheet.setColumnWidth(COLUMN_INDEX1, COLUMN_WIDTH);
        sheet.setColumnWidth(COLUMN_INDEX2, COLUMN_WIDTH);
        HSSFRow row = sheet.createRow(ROW_NUMBER);
        HSSFFont font = wb.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) FONT_HEIGHT1);
        HSSFFont font2 = wb.createFont();
        font2.setFontName("Arial");
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font2.setFontHeightInPoints((short) FONT_HEIGHT2);
        HSSFCellStyle headerStyle = baseStyle(wb);
        HSSFCellStyle borderStyle = baseStyle(wb);
        headerStyle.setFont(font2);
        borderStyle.setFont(font);
        HSSFCell cell;
        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);

        }
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            String[] obs = list.get(i);
            HSSFCell cellJ;
            for (int j = 0; j < obs.length; j++) {
                cellJ = row.createCell(j);
                cellJ.setCellValue(obs[j]);
                cellJ.setCellType(HSSFCell.CELL_TYPE_STRING);
                cellJ.setCellStyle(borderStyle);
            }
        }
//        wb.write(os);
//        os.flush();
//        os.close();
    }

    private static HSSFCellStyle baseStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true);
        return style;
    }
}

3.BudgetListRequestDto

增加搜索条件Dto，可以按照搜索条件导出

import java.io.Serializable;
import java.util.List;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class BudgetListRequestDto implements Serializable{

	private static final long serialVersionUID = 1L;
	
	private String staffId;
	private Integer id;
	private String year;//年份
	private Integer departmentId;//部门id
	private Integer typeId;//预算类型
	private String typeName;
	private String description;
	private List<Integer> departmentIds;
	private List<Integer> typeIds;
        private int pageNum;
        private int pageSize;
	
	public BudgetListRequestDto() {
	}
	
	public BudgetListRequestDto(String staffId, Integer id, String year, Integer departmentId, Integer typeId,
			String typeName, String description, List<Integer> departmentIds) {
		this.staffId = staffId;
		this.id = id;
		this.year = year;
		this.departmentId = departmentId;
		this.typeId = typeId;
		this.typeName = typeName;
		this.description = description;
		this.departmentIds = departmentIds;
	}
	
}

4.ExcelExportContoller

    @RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
    @ApiOperation(value = "导出Excel")
    public ResultDto<String> exportExcel(@RequestParam(required = true) String listProperties, @RequestParam(required=false) String queryCondition, HttpServletResponse response) {
        try {
            JSONArray data = JSONArray.fromObject(listProperties);
            List<ExportPropertiesDto> properties = JSONArray.toList(data, ExportPropertiesDto.class);

            BudgetListRequestDto request = null;
            if(null!=queryCondition){
                request = (BudgetListRequestDto) JSONObject.toBean(JSONObject.fromObject(queryCondition), BudgetListRequestDto.class);
            }

            Integer size = properties.size();
            String[] header = new String[size];
            if (null != properties && !properties.isEmpty()) {
                int index = 0;
                for (ExportPropertiesDto epd : properties) {
                    header[index] = epd.getFieldName();
                    index++;
                }
            }

            StringBuilder excelName = new StringBuilder("导出excel案例");
            excelName.append(".xls");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.toString().getBytes("GB2312"), "ISO8859-1"));
            // 设定输出文件头
            response.setContentType("application/msexcel");
            // sheet名称
            String sheetName = "detail";
            OutputStream output = response.getOutputStream();
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetName);
            //锁定第一行
            sheet.createFreezePane(0,1,0,1);

            List<String[]> list = excelService.exportExcel(request.getYear(), request.getDepartmentIds(), request.getTypeIds(), properties);
            //调用导出工具类
            ExportExcelUtil.writeExcel(wb, sheet, header, list);
            wb.write(output);
            output.flush();
            output.close();
            return ResultDtoFactory.toAck("success");
        } catch (Exception e) {
            log.error("excel导出" + e);
            return ResultDtoFactory.toNack("excel导出失败");
        }
    }

5.ExcelService接口

public interface ExcelService{
 
    List<String[]> exportExcel(String year, List<Integer> departmentIds,             
    List<Integer> typeIds, List<ExportPropertiesDto> properties) throws 
    ParseException,NoSuchMethodException,IllegalAccessException,     
    InvocationTargetException;

}

7.ExcelService的实现类ExcelServiceImpl

public class ExcelServiceImpl implements ExcelService {

    @Autowired
    ExcelMapper excelMapper;

    /**
     * 导出excel
     */
    @Override
    public List<String[]> exportExcel(String year, List<Integer> departmentIds, List<Integer> typeIds, List<ExportPropertiesDto> properties) throws ParseException,
            NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        List<String[]> totalList = new ArrayList<String[]>();
        //把查询条件赋值到requestDto中
        BudgetListRequestDto budgetListRequestDto = new BudgetListRequestDto();
        budgetListRequestDto.setYear(year);
        budgetListRequestDto.setDepartmentIds(departmentIds);
        budgetListRequestDto.setTypeIds(typeIds);

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //导出合计sheet，根据具体业务查询具体数据
        List<BudgetDetailDto> totals = excelMapper.getBudget(budgetListRequestDto);

        for (BudgetDetailDto total : totals) {
            List<String> temp = new ArrayList<>();
            for (ExportPropertiesDto epd : properties) {
                //通过反射获取和动态表头对应的字段的值
                Method m = null;
                //通过类对象获取对应表头字段的get方法
                m = total.getClass().getMethod("get" + epd.getField().substring(0, 1).toUpperCase() + epd.getField().substring(1));

                if ("null".equals(String.valueOf(m.invoke(total)))) {
                    temp.add("");
                } else {
                    //对Date类型的字段进行处理
                    if ("导入时间".equals(epd.getFieldName())) {
                        temp.add(sdf.format(m.invoke(total)));
                    } else if ("预算内金额".equals(epd.getFieldName()) || "预算内已用金额".equals(epd.getFieldName()) || "预算内调整".equals(epd.getFieldName())) {
                        //利用DecimalFormat来处理导出后长数字显示为科学计数问题
                        temp.add(new DecimalFormat("0.00").format(m.invoke(total)));
                    } else {
                        temp.add(String.valueOf(m.invoke(total)));
                    }
                }
            }
            //添加一行数据到list中去，数组的大小就是动态表头的长度
            totalList.add(temp.toArray(new String[properties.size()]));
        }

        //移除合计时无需累加的行
        for (int i = 0; i < totals.size(); i++) {
            if ("人工费用".equals(totals.get(i).getTypeName()) || "日常费用".equals(totals.get(i).getTypeName()) || "平台费用".equals(totals.get(i).getTypeName())
                    || "专项费用".equals(totals.get(i).getTypeName()) || "付现费用总计".equals(totals.get(i).getTypeName())) {
                totals.remove(i);
            }
        }

        //追加一行汇总行
        Map<String, String> map = new HashMap();
        Double budgetaryAmount = 0.0;
        Double budgetary = 0.0;
        Double adjustment = 0.0;
        Double budgetaryBtotal = 0.0;
        Double extraBudgetary = 0.0;
        Double extraBudgetaryUsed = 0.0;
        Double budgetBalance = 0.0;
        Double budgetaryTotal = 0.0;
        //对需要合计的列进行累加
        for (BudgetDetailDto budgetDetailDto : totals) {
            budgetaryAmount = budgetDetailDto.getBudgetaryAmount() + budgetaryAmount;
            map.put("budgetaryAmount", new DecimalFormat("0.00").format(budgetaryAmount));

            budgetary = budgetDetailDto.getBudgetary() + budgetary;
            map.put("budgetary", new DecimalFormat("0.00").format(budgetary));

            adjustment = budgetDetailDto.getAdjustment() + adjustment;
            map.put("adjustment", new DecimalFormat("0.00").format(adjustment));

            budgetaryBtotal = budgetDetailDto.getBudgetaryBtotal() + budgetaryBtotal;
            map.put("budgetaryBtotal", new DecimalFormat("0.00").format(budgetaryBtotal));

            extraBudgetary = budgetDetailDto.getExtraBudgetary() + extraBudgetary;
            map.put("extraBudgetary", new DecimalFormat("0.00").format(extraBudgetary));

            extraBudgetaryUsed = budgetDetailDto.getExtraBudgetaryUsed() + extraBudgetaryUsed;
            map.put("extraBudgetaryUsed", new DecimalFormat("0.00").format(extraBudgetaryUsed));

            budgetBalance = budgetDetailDto.getBudgetBalance() + budgetBalance;
            map.put("budgetBalance", new DecimalFormat("0.00").format(budgetBalance));

            budgetaryTotal = budgetDetailDto.getBudgetaryTotal() + budgetaryTotal;
            map.put("budgetaryTotal", new DecimalFormat("0.00").format(budgetaryTotal));
        }

        List<String> temp1 = new ArrayList<>();
        for (int j = 0; j < properties.size(); j++) {
            ExportPropertiesDto exportPropertiesDtoTemp = properties.get(j);
            String prop = exportPropertiesDtoTemp.getFieldName();
            switch (prop) {
                case "预算年份":
                    temp1.add("");
                    break;
                case "部门":
                    temp1.add("");
                    break;
                case "导入时间":
                    temp1.add("");
                    break;
                case "预算类型":
                    temp1.add("合计");
                    break;
                case "预算内金额":
                    temp1.add(map.get("budgetaryAmount"));
                    break;
                case "预算内已用金额":
                    temp1.add(map.get("budgetary"));
                    break;
                default:
                    break;
            }
        }
        //追加一行合计
        totalList.add(temp1.toArray(new String[properties.size()]));
        return totalList;
    }