EXCEL导出工具类

EXCEL导出工具类

在做导入的时候碰到了个很坑的问题 excel版本问题

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

1 .引入pom

        <!-- poi -->
        <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-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>stax</groupId>
            <artifactId>stax-api</artifactId>
            <version>1.0.1</version>
        </dependency>

2. ExcelUtil工具类

import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil
{

    public static Workbook fillExcelData(List<Map<String, Object>> list, String[] headers,
            String[] keys)
    {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);
        Row row = sheet.createRow(0);
        Cell cell = null;
        CellStyle cellStyle = getTitleStyle(wb);
        Map<String, Object> map;
        for (int i = 0; i < headers.length; i++)
        {
            cell = row.createCell(i);
            cell.setCellValue(headers[i].trim());
            cell.setCellStyle(cellStyle);
        }
        cellStyle =getCellStyle(wb);
        for (int j = 0; j < list.size(); j++)
        {
            map = list.get(j);
            row = sheet.createRow(j + 1);
            String value=null;
            for (int k = 0; k < keys.length; k++)
            {
                cell = row.createCell(k);
                if(map.get(keys[k].trim()) == null){
                    value = "";
                }else{
                    value = map.get(keys[k].trim()).toString() == null ? " ":(String)map.get(keys[k]).toString();
                }
                cell.setCellValue(value);
                cell.setCellStyle(cellStyle);
            }
        }
        return wb;
    }

    /**
     * 标题栏格式
     * 
     * @param cell
     * @return
     */
    private static CellStyle getTitleStyle(Workbook wb)
    {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setColor(HSSFColor.WHITE.index);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    /**
     * CELL格式
     * 
     * @param cell
     * @return
     */
    private static CellStyle getCellStyle(Workbook wb)
    {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

}

3.StringUtil工具类

import java.util.ArrayList;
import java.util.List;

/**
 * 字符串工具类
 * @author 
 *
 */
public class StringUtil {

    /**
     * 判断是否为空
     * @param str
     * @return
     */
    public static boolean isEmpty(String str){
        if(str==null||"".equals(str.trim())){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 判断是否不为空
     * @param str
     * @return
     */
    public static boolean isNotEmpty(String str){
        if((str!=null)&&!"".equals(str.trim())){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 格式化模糊查询
     * @param str
     * @return
     */
    public static String formatLike(String str){
        if(isNotEmpty(str)){
            return "%"+str+"%";
        }else{
            return null;
        }
    }

    /**
     * 排除list空值
     * @param list
     * @return
     */
    public static List<String> filterWhite(List<String> list){
        List<String> resultList=new ArrayList<String>();
        for(String l:list){
            if(isNotEmpty(l)){
                resultList.add(l);
            }
        }
        return resultList;
    }

}

4.DataConverterUtil工具类

数据格式转换类

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtilsBean;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class DataConverterUtil {
    /**
     * map转对象
     * 
     * @param map
     * @param beanClass
     * @return
     */
    public static <T> T map2Object(Map<String, Object> map, Class<T> beanClass) {
        if (map == null)
            return null;

        T obj = null;
        try {
            obj = beanClass.newInstance();
            org.apache.commons.beanutils.BeanUtils.populate(obj, map);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return obj;
    }

    public static Map<String, Object> beanToMap(Object obj) {
        Map<String, Object> params = new HashMap<String, Object>(0);
        try {
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
            PropertyDescriptor[] descriptors = propertyUtilsBean
                    .getPropertyDescriptors(obj);
            for (int i = 0; i < descriptors.length; i++) {
                String name = descriptors[i].getName();
                if (!"class".equals(name)) {
                    params.put(name,
                            propertyUtilsBean.getNestedProperty(obj, name));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return params;
    }

    /**
     * 对象转Json
     * 
     * @param obj
     * @return
     */
    public static String object2Json(Object obj) {
        ObjectMapper mapper = new ObjectMapper();
        // 忽略空属性
        mapper.setSerializationInclusion(Include.NON_NULL);
        try {
            return mapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 对象转Json
     * 
     * @param obj
     * @return
     */
    public static String object2JsonWithNULL(Object obj) {
        ObjectMapper mapper = new ObjectMapper();
        // 忽略空属性
        try {
            return mapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * json转对象
     * 
     * @param json
     * @param objectClass
     * @return
     */
    public static <T> T json2Object(String json, Class<T> objectClass) {
        ObjectMapper mapper = new ObjectMapper();
        try {
            return mapper.readValue(json, objectClass);
        } catch (JsonParseException e) {
            e.printStackTrace();
        } catch (JsonMappingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * list 转 字符转，
     * 
     * @param list
     * @param 分隔符
     * @return
     */
    public static String list2String(List<String> list, String spit) {
        if (list == null || list.isEmpty()) {
            return "";
        }

        String result = "";
        for (int i = 0; i < list.size(); i++) {
            String tmp = list.get(i);
            if ("".equals(tmp)) {
                continue;
            }

            if (result.equals("")) {
                result = result + tmp;
            } else {
                result = result + spit + tmp;
            }
        }

        return result;
    }

    /**
     * list<对象>转list<Map<String,Object>>
     * 
     * @param obj
     * @return
     */
    public static List<Map<String, Object>> listO2listMap(List list) {
        String json = object2JsonWithNULL(list);
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> listMap = json2Object(json, List.class);
        return listMap;
    }

}

5. ResponseUtil工具类

import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;

public class ResponseUtil {

    public static void write(HttpServletResponse response,Object o)throws Exception{
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.print(o.toString());
        out.flush();
        out.close();
    }

    //导出Excel
    public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }
}

6. Controller类

    /**
     * @Description: (导出用户列表)
     */
    @RequestMapping(value = "/exportTask", method = RequestMethod.POST)
    @ResponseBody
    public String exportTask(
            @RequestBody ExportRequestDto exportRequest,
            HttpServletResponse response) {
        Workbook wb = userService.exportTask(exportRequest.getUserDto(), exportRequest.getHeaders(),
                exportRequest.getKeys());
        try {
            ResponseUtil.export(response, wb, "用户列表.xlsx");
        } catch (Exception e) {
            log.info("导出excel出现异常!");
            e.printStackTrace();
        }
        return null;
    }

6. 使用方法

请求参数：

userDto : 实体类-用于查询条件

headers : 导出的Excel标题栏

keys : 导出的对应Excel标题栏的键
例如：EXCEL中第二行是标题，对应数据库中字段是title，用于检索
这样就支持自定义导出列了

{
   "userDto":{
           "user":"xxx"   
   },
   "headers":["id","标题"],
   "keys":["id","title"]
}

喜欢请点赞，谢谢^^