openCsv通用导出

淡淡的烟草味﹌ 2022-07-17 02:58 224阅读 0赞

## 准备 ##

[git整体项目下载地址][git]  
准备jar包，包含：

<dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-compress</artifactId>
                <version>1.3</version>
            </dependency>
            <dependency>
                <groupId>ant</groupId>
                <artifactId>ant</artifactId>
                <version>1.7.0</version>
            </dependency>
            <dependency>
                <groupId>net.sf.opencsv</groupId>
                <artifactId>opencsv</artifactId>
                <version>2.3</version>
            </dependency>

## 工具类ExportByOpenCsvUtils.java ##

[git源码下载][git 1]

package com.paincupid.springmvc.util.export;
    
    import java.io.UnsupportedEncodingException;
    import java.lang.reflect.Field;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map.Entry;
    import javax.servlet.http.HttpServletResponse;
    import org.apache.commons.lang.StringUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import au.com.bytecode.opencsv.CSVWriter;
    
    /** * 通用csv导出类 * @author arthur.dy.lee * @since 2016.08.28 */
    public class ExportByOpenCsvUtils { 
        private static final Logger logger = LoggerFactory.getLogger(ExportByOpenCsvUtils.class);
    
        /** * 导出Csv * * @param csvName * 要导出的excel名称 * @param list * 要导出的数据集合 * @param fieldMap * 中英文字段对应Map,即要导出的excel表头 * @param response * 使用response可以导出到浏览器 * @return */
        public static <T> void export(String csvName, List<T> list, LinkedHashMap<String, String> fieldMap,
                HttpServletResponse response) {
    
            // 设置默认文件名为当前时间：年月日时分秒
            if (StringUtils.isBlank(csvName)) {
                csvName = new SimpleDateFormat("yyyy-MM-dd hhmmss").format(new Date()).toString();
            }else{
                csvName = csvName + new SimpleDateFormat("_yyyy-MM-dd hhmmss").format(new Date()).toString();
            }
    
            try {
                // 设置response头信息
                response.reset(); // 清空输出流
                response.setContentType("APPLICATION/OCTET-STREAM"); // 定义输出类型
                csvName = new String(csvName.getBytes("gbk"), "ISO-8859-1");
                response.setHeader("Content-disposition", "attachment; filename=" + csvName + ".csv");
                response.setCharacterEncoding("gbk");
    
            } catch (UnsupportedEncodingException e1) {
                logger.info(e1.getMessage());
            }
    
            try {
                CSVWriter writer =  new CSVWriter(response.getWriter());
                // 组装文件并输出
                fillCsv(writer, list, fieldMap);
    
                writer.flush();  
                writer.close(); 
            } catch (Exception e) {
                logger.info("导出Csv失败！");
                logger.error(e.getMessage());
            }
        }
    
        /** * 根据字段名获取字段对象 * * @param fieldName * 字段名 * @param clazz * 包含该字段的类 * @return 字段 */
        private static Field getFieldByName(String fieldName, Class<?> clazz) {
            //logger.info("根据字段名获取字段对象:getFieldByName(), fieldName: "+fieldName);
            // 拿到本类的所有字段
            Field[] selfFields = clazz.getDeclaredFields();
    
            // 如果本类中存在该字段，则返回
            for (Field field : selfFields) {
                // 如果本类中存在该字段，则返回
                if (field.getName().equals(fieldName)) {
                    return field;
                }
            }
    
            // 否则，查看父类中是否存在此字段，如果有则返回
            Class<?> superClazz = clazz.getSuperclass();
            if (superClazz != null && superClazz != Object.class) {
                // 递归
                return getFieldByName(fieldName, superClazz);
            }
    
            // 如果本类和父类都没有，则返回空
            return null;
        }
    
        /** * 根据字段名获取字段值 * * @param fieldName * 字段名 * @param o * 对象 * @return 字段值 * @throws Exception * 异常 * */
        private static Object getFieldValueByName(String fieldName, Object o) throws Exception {
    
            //logger.info("根据字段名获取字段值:getFieldValueByName()");
            Object value = null;
            // 根据字段名得到字段对象
            Field field = getFieldByName(fieldName, o.getClass());
    
            // 如果该字段存在，则取出该字段的值
            if (field != null) {
                field.setAccessible(true);// 类中的成员变量为private,在类外边使用属性值，故必须进行此操作
                value = field.get(o);// 获取当前对象中当前Field的value
            } else {
                throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
            }
            logger.info("value: "+ value);
            return value;
        }
    
        /** * 根据带路径或不带路径的属性名获取属性值,即接受简单属性名， * 如userName等，又接受带路径的属性名，如student.department.name等 * * @param fieldNameSequence * 带路径的属性名或简单属性名 * @param o * 对象 * @return 属性值 * @throws Exception * 异常 * */
        private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {
            //logger.info("根据带路径或不带路径的属性名获取属性值,即接受简单属性名:getFieldValueByNameSequence(): "+fieldNameSequence);
            Object value = null;
    
            // 将fieldNameSequence进行拆分
            String[] attributes = fieldNameSequence.split("\\.");
            if (attributes.length == 1) {
                value = getFieldValueByName(fieldNameSequence, o);
            } else {
                // 根据数组中第一个连接属性名获取连接属性对象，如student.department.name
                Object fieldObj = getFieldValueByName(attributes[0], o);
                // 截取除第一个属性名之后的路径
                String subFieldNameSequence = fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
                // 递归得到最终的属性对象的值
                value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
            }
            return value;
    
        }
    
        /** * 向csv中填充数据 * * @param writer * csv文件的名称 * @param list * 数据源 * @param fieldMap * 中英文字段对应关系的Map * @param style * 表格中的格式 * @throws Exception * 异常 * */
        private static <T> void fillCsv(CSVWriter writer, List<T> list, LinkedHashMap<String, String> fieldMap) throws Exception {
            //logger.info("向csv file中填充数据");
            // 定义存放英文字段名和中文字段名的数组
            String[] enFields = new String[fieldMap.size()];
            String[] cnFields = new String[fieldMap.size()];
    
            // 填充数组
            int count = 0;
            for (Entry<String, String> entry : fieldMap.entrySet()) {
                enFields[count] = entry.getKey();
                cnFields[count] = entry.getValue();
                count++;
            }
    
            // 填充表头
            String [] includeHeaders = new String[count];
            for (int i = 0; i < cnFields.length; i++) {
                includeHeaders[i] =  cnFields[i];
            }
            writer.writeNext(includeHeaders);
    
    
            // 填充内容
            String [] data = new String[count];
            for (int index = 0; index < list.size(); index++) {
                // 获取单个对象
                T item = list.get(index);
                for (int i = 0; i < enFields.length; i++) {
                    Object objValue = getFieldValueByNameSequence(enFields[i], item);
                    String fieldValue = objValue == null ? "" : objValue.toString();
                    data[i] = fieldValue;
                }
                writer.writeNext(data);
            }
    
        }
    
    }

## 使用 ##

@ResponseBody
        @RequestMapping(value="/downloadCsv", method=RequestMethod.POST)
        public void downloadCsv(String param1, String param2){
            List<Person> list = CreatMockData.createPersonList(20,1);
    
            LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();
            fieldMap.put("id", "id");
            fieldMap.put("name", "名称");
            fieldMap.put("age", "年龄");
            fieldMap.put("tel", "电话");
            fieldMap.put("prov", "省份");
            fieldMap.put("city", "城市");
            fieldMap.put("sex", "性别");
            fieldMap.put("location", "地址");
            fieldMap.put("company", "公司");
            fieldMap.put("comment", "备注");
    
            ExportByOpenCsvUtils.export("李-test-excel", list, fieldMap, response);
        }

## download.jsp ##

这里只是一提供了一个下载按钮，表示了一下而已。下载的话用的是form的形式。  
Ajax下载Excel是有点问题，因为涉及到流下载，不过没有试过jquery.form.js  
以下是jsp页面：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; %> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta name="description" content=""> <meta name="author" content=""> <title>paincupid admin</title>  <link href="<%=basePath%>/resources/themes/bower_components/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet">  <link href="<%=basePath%>/resources/themes/bower_components/metisMenu/dist/metisMenu.min.css" rel="stylesheet">  <link href="<%=basePath%>/resources/themes/dist/css/sb-admin-2.css" rel="stylesheet">  <link href="<%=basePath%>/resources/themes/bower_components/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css"> <link rel="stylesheet" href="<%=basePath%>/resources/css/zTreeStyle/zTreeStyle.css" type="text/css">    <style> </style> </head> <body> <div class="container"> <div class="row"> <div class="col-md-4 col-md-offset-4"> <div class="login-panel panel panel-default"> <div class="panel-heading"> <h3 class="panel-title">下载</h3> </div> <div class="panel-body"> <form> <fieldset> <div class="form-group"> </div> <div class="form-group"> </div>  <a class="btn btn-lg btn-success btn-block" id="btnDownloadExcel" type="submit" onclick="btnDownloadExcel()">下载Excel</a> <a class="btn btn-lg btn-success btn-block" id="btnDownloadCsv" type="submit" onclick="btnDownloadCsv()">下载Csv</a> </fieldset> </form> </div> </div> </div> </div> </div> </body> </html> <script type="text/javascript" src="<%=basePath%>/resources/js/jquery/jquery-1.11.3.min.js"></script> <script type="text/javascript" src="<%=basePath%>/resources/js/jquery-ui/jquery-ui.js"></script> <script> $(function(){ }); function btnDownloadExcel(){ var form = document.forms[0]; var url = "${pageContext.request.contextPath}/widget/downloadExcel"; form.action = url; form.method = "post"; form.submit(); } function btnDownloadCsv(){ var form = document.forms[0]; var url = "${pageContext.request.contextPath}/widget/downloadCsv"; form.action = url; form.method = "post"; form.submit(); } </script>

转载请注明来源：[paincupid][]

[git]: https://git.oschina.net/paincupid/springmvc/tree/bootstrap/
[git 1]: https://git.oschina.net/paincupid/springmvc/blob/bootstrap/src/main/java/com/paincupid/springmvc/util/export/ExportByOpenCsvUtils.java?dir=0&filepath=src%2Fmain%2Fjava%2Fcom%2Fpaincupid%2Fspringmvc%2Futil%2Fexport%2FExportByOpenCsvUtils.java&oid=2940b7541a6919944cb4382d153581191afdf49a&sha=b4c366e232c0c04128811c46df0d8a196b967a10
[paincupid]: http://blog.csdn.net/paincupid/article/details/52348129