poi 导出工具类

自己利用反射写的一个poi的导出工具类。(poi版本3.17)

工具类中使用的Share.isEmpty()方法

package com.xxqy.utils;

import org.apache.commons.lang.StringUtils;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

/**
 * @author yww
 * @createTime 2021/4/9
 */
public class Share {
    /**
     * 判断是否为空
     * @param obj
     * @return
     */
    public static boolean isEmpty(Object obj){
        if(obj instanceof CharSequence){
            return StringUtils.isBlank((String)obj);
        }else if(obj instanceof Collection){
            Collection coll = (Collection) obj;
            return coll == null || coll.size() == 0;
        }else if(obj instanceof Map){
            Map map = (Map) obj;
            return map == null || map.size() == 0;
        }else if(obj instanceof Object[]){
            Object[] array = (Object[]) obj;
            return array == null || array.length == 0;
        }
        return obj == null;
    }
}

导出工具类代码如下

	/**
     * 导出,利用反射将值塞入cell中
     * @param kvMap 自定义标题属性,k:标题 v:属性
     * @param list 数据集合
     * @param out 输出流
     * @param dateFormat  日期格式,默认为 yyyy-MM-dd 可根据自己需求定义格式 yyyy-MM-dd hh:mm:ss
     */
    public static void export(LinkedHashMap<String,String> kvMap, List<?> list, OutputStream out, String dateFormat){
        try{
            dateFormat = !Share.isEmpty(dateFormat) ? dateFormat : "yyyy-MM-dd";
            HSSFWorkbook wb = new HSSFWorkbook();
            //表头样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            //给cell上下左右设置边框
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderRight(BorderStyle.THIN);
            //设置字体垂直居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置换行
            titleStyle.setWrapText(true);

            //设置字体样式
            HSSFFont titleFont = wb.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 15);
            titleStyle.setFont(titleFont);


            //内容样式
            HSSFCellStyle contentStyle = wb.createCellStyle();
            //给cell上下左右设置边框
            contentStyle.setBorderTop(BorderStyle.THIN);
            contentStyle.setBorderLeft(BorderStyle.THIN);
            contentStyle.setBorderBottom(BorderStyle.THIN);
            contentStyle.setBorderRight(BorderStyle.THIN);
            //设置字体垂直居中
            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            contentStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置换行
            contentStyle.setWrapText(true);

            HSSFSheet sheet = wb.createSheet();
            HSSFRow oneRow = sheet.createRow(0);
            oneRow.setHeightInPoints(33);

            //标题循环,赋值
            Set<String> keySet = kvMap.keySet();
            Integer setIndex = 0;
            for (String key : keySet) {
                sheet.setColumnWidth(setIndex,60*80);
                HSSFCell oneRowCell = oneRow.createCell(setIndex);
                oneRowCell.setCellValue(key);
                oneRowCell.setCellStyle(titleStyle);
                setIndex++;
            }

            //数据循环
            for (int i = 0; i < list.size(); i++) {
                Object obj = list.get(i);
                HSSFRow row = sheet.createRow(i+1);//添加行
                row.setHeightInPoints(33);
                String values [] = new String [kvMap.size()];
                Field[] fields = obj.getClass().getDeclaredFields();
                Integer count = 0;
                //属性循环赋值
                for (String key : keySet) {
                    String value = kvMap.get(key);
                    if(value.equals("序号")){
                        values[count] = (i+1)+"";
                        count++;
                    }else{
                        for (int j = 0; j < fields.length; j++) {
                            Field field = fields[j];
                            field.setAccessible(true);
                            String name = field.getName();//属性
                            Object valueObj = field.get(obj);
                            if(name.equals(value)){//如果两个属性相同
                                if(valueObj instanceof Date){
                                    valueObj = new SimpleDateFormat(dateFormat).format(valueObj);
                                }
                                String dataValue = !Share.isEmpty(valueObj) ? valueObj.toString() : "";
                                values[count] = dataValue;
                                count++;
                            }
                        }
                    }
                }
                for (int c = 0; c < keySet.size(); c++) {//需创建的单元格
                    HSSFCell cell = row.createCell(c);
                    cell.setCellValue(!Share.isEmpty(values[c]) ? values[c] : "");
                    cell.setCellStyle(contentStyle);
                }
            }

            try {
                wb.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

下面是Controller中导出方法使用到工具类的的示例代码

	/**
	 * 导出销售下单信息
	 * @param salesOrderQuery
	 * @param response
	 */
	@RequestMapping("/exportSalesOrder")
	public void  exportSalesOrder(SalesOrderQuery salesOrderQuery, HttpServletResponse response){
		List<NSalesOrderEntity>  dataList = new ArrayList<>();
		//代表选中数据导出
		if(!Share.isEmpty(salesOrderQuery.getSalesOrderIds())){
			String[] salesOrderIds = salesOrderQuery.getSalesOrderIds();
			dataList = nSalesOrderService.searchListInIds(salesOrderIds);
			//条件处理
		}else{
			Query query = setQuery(salesOrderQuery,Constant.SUPER_ADMIN_STR);
			dataList = nSalesOrderService.queryList(query);
		}
		OutputStream out = null;
		try{
			out = response.getOutputStream();
			String fileName = new String("销售下单信息".getBytes("utf-8"), "iso8859-1");
			response.setHeader("Content-disposition","attachment;filename="+fileName+".xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Pragma","No-cache");
			response.setHeader("Cache-Control","no-cache");
			response.setDateHeader("Expires",0);

			LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
			kvMap.put("序号","序号");
			kvMap.put("业务端","businessName");
			kvMap.put("客户名称","customerName");
			kvMap.put("业务员代号","salesmanCode");
			kvMap.put("内部订单号","orderNo");
			kvMap.put("淘宝订单号","taobaoOrderNo");
			kvMap.put("下单名称","orderName");
			kvMap.put("产品型号","productModel");
			kvMap.put("快递种类","transport");
			kvMap.put("付款金额","paymentAmount");
			kvMap.put("返款金额","refundAmount");
			kvMap.put("订单总价","totalPrice");
			kvMap.put("实际收入","realIncome");
			kvMap.put("备注信息","remarkInfo");
			kvMap.put("收货地址","receivingAddress");
			kvMap.put("发票信息","invoice");
			kvMap.put("发票金额","incoicePrice");
			kvMap.put("发票类型","invoiceType");
			kvMap.put("发票状态","invoiceStatus");
			kvMap.put("是否已付全款","isFullPayment");
			kvMap.put("标识订单","orderTypeValue");
			kvMap.put("是否下错单","isErrorOrder");
			kvMap.put("备注","remark");
			kvMap.put("日期","createTime");
			
			//导出excel
			ExportUtils.export(kvMap,dataList,out,"yyyy-MM-dd");
			out.flush();
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			try{
				out.close();
			}catch (IOException e){
				e.printStackTrace();
			}
		}
	}

导出效果