《SpringMvc+POI 处理Excel的导出操作》

• 导出工具类

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

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;

public class ExportUtil {

    /**
     *
     * @param ls
     * @param targetClazz
     * @param response
     * @param fileName
     * @throws IOException
     */
    public static void downLoadListAsXls(List<? extends Object> ls, Class<?> targetClazz, HttpServletResponse response,
            String fileName) throws IOException {
        response.setContentType("application/msexcel;charset=UTF-8");
        List<?> converTo = convertTo(ls, targetClazz);
        downLoadListAsXls2(converTo, targetClazz, response, fileName);

    }

    /**
     *
     * @param ls
     * @param targetClazz
     * @param response
     * @param fileName
     * @throws IOException
     */
    public static void downLoadListAsXls2(List<? extends Object> ls, Class<?> targetClazz, HttpServletResponse response,
            String fileName) throws IOException {
        OutputStream out = response.getOutputStream();
        response.setHeader("Content-disposition",
                "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
        response.setContentType("application/msexcel;charset=UTF-8");
        writeListToXls(ls, targetClazz, out);

    }

    /**
     *
     * @param ls
     * @param clazz
     * @param out write到哪里
     */
    private static void writeListToXls(List<? extends Object> ls, Class<?> clazz, OutputStream out) {

        if (ls == null) {
            return;
        }

        if (out == null) {
            return;
        }
        HSSFWorkbook book = new HSSFWorkbook();
        Sheet sheet = book.createSheet();
        Field[] fields = clazz.getDeclaredFields();
        // 写标题
        Row headRow = sheet.createRow(0);
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            PropertyColumnName annotation = field.getAnnotation(PropertyColumnName.class);
            if (annotation == null) {
                continue;
            }
            Cell createCell = headRow.createCell(i);
            createCell.setCellValue(annotation.value());
        }
        // 写标题对应的value-原则上需要一一对应
        for (int i = 0; i < ls.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Object temObject = ls.get(i);
            for (int j = 0; j < fields.length; j++) {
                Field field = fields[j];
                if (!field.isAnnotationPresent(PropertyColumnName.class)) {
                    continue;
                }
                Cell cell = row.createCell(j);
                try {
                    Object valueObj = field.get(temObject);
                    if (valueObj == null) {
                        continue;
                    }
                    if (valueObj instanceof Float) {
                        cell.setCellValue(new Double(valueObj.toString()));
                    } else {
                        cell.setCellValue(valueObj.toString());
                    }
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            book.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 把一个List转换成另外一个List,需要targetClass中的属性签名和srcList中的类属性签名一样
     * 
     * @param srcList
     * @param targetClass
     * @return
     */
    private static <T> List<T> convertTo(List<? extends Object> srcList, Class<T> targetClass) {
        List<T> result = new ArrayList<T>();
        try {
            Field[] fields = targetClass.getDeclaredFields();
            for (Object obj : srcList) {
                T newObj = targetClass.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String name = field.getName();
                    Class<? extends Object> srcClass = obj.getClass();
                    Field srcDeclaredField = srcClass.getDeclaredField(name);
                    srcDeclaredField.setAccessible(true);
                    Object srcFieldValue = srcDeclaredField.get(obj);
                    Field targetField = targetClass.getDeclaredField(name);
                    targetField.setAccessible(true);
                    targetField.set(newObj, srcFieldValue);
                }
                result.add(newObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

}

• 导数据

    @RequestMapping("")
    public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
        try {
            Map<String, Object> paramMap = RequestUtils.convertRequestToMap(request);
            Long orgId = ((AuctionUser) SecurityUtils.getSubject().getSession().getAttribute(CommonConstant.USER))
                    .getOrgId();
            String condition = getString(paramMap, "condition");
            List<MConvocator> convocatorList = mConvocatorService.findConList(orgId, condition);
            ExportUtil.downLoadListAsXls(convocatorList, ConvocatorExportBO.class, response,
                    "参会人员导出-" + System.currentTimeMillis());
        } catch (Exception e) {
            LoggerHelper.err(getClass(), e.getMessage(), e);
        }
    }

• service

    public List<*> findConList(Long orgId, String condition) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Map<String, Object> param = new HashMap<String, Object>();
        if (orgId != null) {
            param.put("orgId", orgId);
        }
        if (condition != null && condition.length() > 0) {
            param.put("condition", condition);
        }
        List<MConvocator> conList = mConvocatorDao.selectByCondition(param);
        for (MConvocator mConvocator : conList) {
            mConvocator.setStatusDesc(ConvovatorStatusEnum.getDesc(mConvocator.getStatus()));
            mConvocator.setCreateTimeDesc(df.format(mConvocator.getCreateTime()));
            if (mConvocator.getSignTime() != null) {
                mConvocator.setSignTimeDesc(df.format(mConvocator.getSignTime()));
            }
        }
        return conList;
    }

• BO

public class ConvocatorExportBO {

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

    @PropertyColumnName("手机")
    private String mobile;
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }
  }