关于opencsv注解方式的导入及导出

导出csv文件

使用注解方式。

1.引入maven依赖

首先引入maven依赖，阿里的fastjson包用于方便类属性的备份转换，我使用的spring的版本为 <spring.version>4.3.7.RELEASE</spring.version>.

     <!--openCsv-->
	    <dependency>
	        <groupId>net.sourceforge.javacsv</groupId>
	        <artifactId>javacsv</artifactId>
	        <version>2.0</version>
	    </dependency>
	    <dependency>
	        <groupId>com.opencsv</groupId>
	        <artifactId>opencsv</artifactId>
	        <version>4.6</version>
	    </dependency>
	    <dependency>
	        <groupId>commons-beanutils</groupId>
	        <artifactId>commons-beanutils</artifactId>
	        <version>1.9.2</version>
	    </dependency>
    <!--openCsv-->
    	<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>fastjson</artifactId>
		    <version>1.2.24</version>
		</dependency>

2.引入工具类

/**
 * CSV导出导出工具类
 */
public class OpenCsvUtil {

    Logger log = LoggerFactory.getLogger(OpenCsvUtil.class);

    /**
     * 解析csv文件并转成bean
     * @param file csv文件
     * @param clazz 类
     * @return 泛型bean集合
     */
    public static <T> List<T> getCsvData(MultipartFile file, Class<T> clazz) {
        InputStreamReader in = null;
        try {
            in = new InputStreamReader(file.getInputStream(), "gbk");
        } catch (Exception e) {
        	e.printStackTrace();
        }

        HeaderColumnNameMappingStrategy<T> strategy = new HeaderColumnNameMappingStrategy<>();
        strategy.setType(clazz);

        CsvToBean<T> csvToBean = new CsvToBeanBuilder<T>(in)
                .withSeparator(',')
                .withQuoteChar('\'')
                .withMappingStrategy(strategy).build();
        return csvToBean.parse();
    }

    /**
     * 将数据写入CSV
     * @param fileName 文件名
     * @param data 数据
     * @param bean 类型
     * @param response 响应
     */
    public static <T> void writeCSV(String fileName, List<T> data, Class<T> bean, HttpServletResponse response) {       
        try{
            response.reset();
            response.setContentType("APPLICATION/OCTET-STREAM");
            fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            response.setHeader("Content-disposition", "attachment;  filename=" + fileName + ".csv");
            response.setCharacterEncoding("gbk");

            CSVWriter writer = new CSVWriter(response.getWriter());

            CustomMappingStrategy<T> mappingStrategy = new CustomMappingStrategy<>();
            mappingStrategy.setType(bean);
            StatefulBeanToCsvBuilder<T> builder = new StatefulBeanToCsvBuilder<>(writer);
            StatefulBeanToCsv<T> beanToCsv = builder.withMappingStrategy(mappingStrategy)
                    .withSeparator(',').withApplyQuotesToAll(false).build();
            beanToCsv.write(data);
            writer.flush();
            writer.close();
        }catch(Exception e){
			e.printStackTrace();
        }   
    }

}

添加scv导出策略类，重写opencsv的类导出策略，用于解决导出时列顺序错乱的问题。

/**
 * CSV导出策略类（有序列头）
 * 导出基础类需添加注解CsvBindByName和CsvBindByPosition
 * CsvBindByPosition实现排序，CsvBindByName实现列名映射
 * @author lww
 */
public class CustomMappingStrategy<T> extends ColumnPositionMappingStrategy<T>{

    @Override
    public String[] generateHeader(T bean) throws CsvRequiredFieldEmptyException {
        super.setColumnMapping(new String[ FieldUtils.getAllFields(bean.getClass()).length]);
        final int numColumns = findMaxFieldIndex();
        if (!isAnnotationDriven() || numColumns == -1) {
            return super.generateHeader(bean);
        }

        String[] header = new String[numColumns + 1];

        BeanField beanField;
        for (int i = 0; i <= numColumns; i++) {
            beanField = findField(i);
            String columnHeaderName = extractHeaderName(beanField);
            header[i] = columnHeaderName;
        }
        return header;
    }

    private String extractHeaderName(final BeanField beanField) {
        if (beanField == null || beanField.getField() == null || beanField.getField().getDeclaredAnnotationsByType(CsvBindByName.class).length == 0) {
            return StringUtils.EMPTY;
        }

        final CsvBindByName bindByNameAnnotation = beanField.getField().getDeclaredAnnotationsByType(CsvBindByName.class)[0];
        return bindByNameAnnotation.column();
    }

3.定义实体类注解

/**
 * 实体类
 * @CsvBindByPosition 通过位置获取（第？列）
 * @CsvBindByName 通过名字获取
 * @CsvDate 日期格式转化
 */
public class DetialResult {

    @CsvBindByPosition(position = 0)
    @CsvBindByName(column = "省")
    private String province;

    @CsvBindByPosition(position = 1)
    @CsvBindByName(column = "市")
    private String city;

    @CsvBindByPosition(position = 2)
    @CsvBindByName(column = "县")
    private String county;

    @CsvBindByPosition(position = 3)
    @CsvBindByName(column = "乡")
    private String town;

    @CsvBindByPosition(position = 4)
    @CsvBindByName(column = "村")
    private String village;

    @CsvBindByPosition(position = 5)
    @CsvBindByName(column = "姓名")
    private String name;

    @CsvBindByPosition(position = 17)
    @CsvBindByName(column = "日期")
    @CsvDate("yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern="yyyy-MM-dd",timezone = "GMT+8")
    private Date formationTime;

	//此处省略get，set方法
}

4.在serviceImpl中调用

    @Override
    public void exportCsv(ImportVO vo,  HttpServletResponse response) {
    			//查询数据
                List<Import> list = importService.queryImportList(vo);
                //将list转化为DetialResult类型，日期格式为“yyyy-MM-dd”
                List<DetialResult> m = JSON.parseArray(JSON.toJSONStringWithDateFormat(list,"yyyy-MM-dd"),DetialResult.class);
                //调用工具类，在浏览器中下载
                OpenCsvUtil.writeCSV("名单", m, DetialResult.class, response);
 	}                

导入csv文件

1.在OpenCsvUtil工具类中添加导入方法。

 public class OpenCsvUtil {

   Logger log = LoggerFactory.getLogger(OpenCsvUtil.class);

   /**
    * 解析csv文件并转成bean
    * @param file csv文件
    * @param clazz 类
    * @param <T> 泛型
    * @return 泛型bean集合
    */
   public static <T> List<T> getCsvData(MultipartFile file, Class<T> clazz) {
       InputStreamReader in = null;
       try {
           in = new InputStreamReader(file.getInputStream(), "gbk");
       } catch (Exception e) {

       }
       
       HeaderColumnNameMappingStrategy<T> strategy = new HeaderColumnNameMappingStrategy<>();
       strategy.setType(clazz);

       CsvToBean<T> csvToBean = new CsvToBeanBuilder<T>(in)
               .withSeparator(',')
               .withQuoteChar('\'')
               .withMappingStrategy(strategy).build();
       return csvToBean.parse();
   }
   }

2.调用导入方法

public BaseResult importExcelCSV(ImportVO vo, MultipartFile file) {
  	List<DetialResult> data= OpenCsvUtil.getCsvData(file,DetialResult.class);
  	//data为导入的数据，已被解析为实体，可进行插入数据库等操作
  }