简单的测试demo,针对场景:只对一个sheet的导出操作
1,首先引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
2,假设只对名称字段导出,对应的pojo类
@Data
public class User {
private String Name;
}
3,我们要创建一个对应pojo导出数据的的excel类,只是不建议直接在pojo上直接操作。
注意下面注解的使用
@Data
@Builder
@HeadRowHeight(value = 20)//设置表头行高
@ColumnWidth(value = 15)//设置表头行宽
public class UserExcel {
@ColumnWidth(value = 15)//单独设置表头行宽
@ExcelProperty(value = "患者姓名", index = 0)//”0“表示excel中的第一列
private String Name;
}
4,创建Excel导出工具类
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
public class ExcelUtils {
/**
* 导出
*
* @param response
* @param data
* @param fileName
* @param sheetName
* @param clazz
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName,
String sheetName, Class clazz) throws Exception {
// 表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + "-" + LocalDate.now() + ".xlsx");
return response.getOutputStream();
}
}
5,controller层
@GetMapping("/export")
public void Export(HttpServletResponse response) {
// 获取数据
List<UserExcel> UserExcelList =userservice.getData();
if (CollectionUtils.isEmpty(UserExcelList)) {
return;
}
try {
ExcelUtils.writeExcel(response, data, "文件名",
"sheet名", UserExcel.class);
} catch (Exception e) {
e.printStackTrace();
}
}
6.service层
public List<UserExcel> getData (){
List<User> userList=getUserData();
return getExcelData(userList);
}
private List<UserExcel>
getExcelData(List<User> userList) {
List<UserExcel> userExcelList = new ArrayList<>();
for (User user : userList) {
UserExcel userExcel = UserExcel.builder()
.name(user.getName())
.build();
userExcelList.add(userExcel);
}
return userExcelList;
}