EasyExcel导出_Java_移动技术网

导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

导出

Controller

import javax.servlet.http.HttpServletResponse;

/**
 * 导出数据
 * @param response response
 * @return result
 * @author liu
 */
@GetMapping("exportExcelData")
public Result exportExcelData(HttpServletResponse response) {
    service.exportExcelData(response);  
    return Result.ok();
}

Service

import javax.servlet.http.HttpServletResponse;

/**
 * 导出数据
 * @param response response
 * @author liu
 */
 void exportExcelData(HttpServletResponse response);

ServiceImpl

import javax.servlet.http.HttpServletResponse;

@Override
public void exportExcelData(HttpServletResponse response) {
    String fileName = "数据信息";
	String sheetName = "数据信息";
	/**  数据库查询数据并处理 */
	List<ExcelData> dataList = mapper.exportExcelData(); //返回数据信息
	
	List<ExcelData> list = new ArrayList<>();
    
    for (ExcelData excelData : dataList) {
		ExcelData excelPlanData = ExcelData.builder()
		        .id(excelData.getId)
		        .name(excelData.getName)
		        .age(excelData.getAge)
		        .sex(excelData.getSex).build();
		list.add(excelPlanData);
    }
    try {
    	ExcelUtil.writeExcel(response,list,fileName,sheetName,ExcelData.class);	
    } catch (Exception e) {
    	e.printStackTrace();	
    }
	
}

Model

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Builder;
import lombok.Data;

/**
 * 数据导出实体类
 * @author liu
 */
@Data
@Builder
public class ExcelData {
	
	/**
	 * id
	 */
	//导出的Excel标头信息，以及所在第几列
	@ExcelProperty(value = "id", index = 0)  
	private Integer id;

	/**
	 * 姓名
	 */
	@ExcelProperty(value = "姓名", index = 1) 
	private String name;

	/**
	 * 年龄
	 */
	@ExcelProperty(value = "年龄", index = 2) 
	private Integer age;

	/**
	 * 性别
	 */
    @ExcelProperty(value = "性别", index = 3) 
	private String sex;
}

ExcelUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyleStrategy;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

/**
 * 导出工具类
 * @author liu
 */
public class ExcelUtil {
	
    /**
	 * 导出
	 * @param response response
	 * @param data data
	 * @param fileName fileName
	 * @param sheetName sheetName
	 * @param clazz clazz
     */
    public static void writeExcel(HttpServletResponse response,List<? extends Object> data, 
            String fileName,String sheetName,Class clazz) {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
 		//设置表头居中对齐
 		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
 		//内容样式
 		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
 		//设置内容靠左对齐
 		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
 		HorizontalCellStyleStrategy horizontalCellStyleStrategy = 
 		        new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
 		try {
		    EasyExcel.write(getOutputStream(fileName,response),clazz)
		 	        .excelType(ExcelTypeEnum.XLSX)
		 	        .sheet(sheetName)
		 	        .registerWriteHandler(horizontalCellStyleStrategy)
		 	        .doWrite(data);
		} catch (Exception e) {
		    e.printStackTrace();
		}
    }
    
    /**
     * 格式
     * @param fileName fileName
     * @param response response
     * @throws Exception exception
     * @author liu
     * @return java.io.OutputStream 
     */ 
    private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName,"UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + ".xlsx");
        return response.getOutputStream();
    }
}