导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
导出
Controller
import javax.servlet.http.HttpServletResponse;
@GetMapping("exportExcelData")
public Result exportExcelData(HttpServletResponse response) {
service.exportExcelData(response);
return Result.ok();
}
Service
import javax.servlet.http.HttpServletResponse;
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;
@Data
@Builder
public class ExcelData {
@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;
public class ExcelUtil {
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();
}
}
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();
}
}