<!-- 工具类相关 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
private void exportExcel(List<PatientBean> patientBeanList, HttpServletResponse response){
List<PatientExcelDto> excelDtoList = new ArrayList<>();
PatientExcelDto excelDto = new PatientExcelDto();
List<List<String>> patientHeads = new ArrayList<>();
patientHeads.add(Lists.newArrayList("编号"));
patientHeads.add(Lists.newArrayList("手机号"));
patientHeads.add(Lists.newArrayList("姓名"));
patientHeads.add(Lists.newArrayList("性别"));
patientHeads.add(Lists.newArrayList("年龄"));
patientHeads.add(Lists.newArrayList("创建日期"));
patientHeads.add(Lists.newArrayList("所属医生"));
patientHeads.add(Lists.newArrayList("最近多少天未训练"));
excelDto.setHeadList(patientHeads);
excelDto.setSheetName("患者信息");
if (CollectionUtil.isNotEmpty(patientBeanList)){
List<List<Object>> patientDataList = new ArrayList<>();
patientBeanList.forEach(patientBean -> {
List<Object> data = new ArrayList<>();
data.add(patientBean.getPatientId());
data.add(patientBean.getPhone());
data.add(patientBean.getPatientName());
data.add(patientBean.getGenderText());
data.add(patientBean.getAge());
data.add(patientBean.getCreateTime());
data.add(patientBean.getDoctor());
data.add(patientBean.getLatelyNoTrainingDays());
patientDataList.add(data);
});
excelDto.setDataList(patientDataList);
}
excelDtoList.add(excelDto);
try {
ExcelUtils.write(response, DateUtil.today()+"患者信息.xlsx",excelDtoList);
} catch (IOException e) {
log.error("invoke exportExcel IOException : {}",e);
throw new BusinessException(400,e.getMessage());
}
}
这一步是引用阿里easyexcel的方法 ExcelUtils.write(response, DateUtil.today()+“患者信息.xlsx”,excelDtoList)写入倒出。
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.bj66nao.prod.center.bff.patient.dto.patient.PatientExcelDto;
import com.bj66nao.prod.center.common.base.web.ApiResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
@Slf4j
public class ExcelUtils {
public static <T> void write(HttpServletResponse response, String filename, String sheetName,
Class<T> head, List<T> data) throws IOException {
EasyExcel.write(response.getOutputStream(), head)
.autoCloseStream(false)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(sheetName).doWrite(data);
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
}
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
return EasyExcel.read(file.getInputStream(), head, null)
.autoCloseStream(false)
.doReadAllSync();
}
public static <T> void write(HttpServletResponse response, String filename, List<PatientExcelDto> dataList) throws IOException {
try {
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet patientSheet = EasyExcel.writerSheet(0, dataList.get(0).getSheetName())
.head(dataList.get(0).getHeadList())
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(16))
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short)55,(short)33))
.build();
excelWriter.write(dataList.get(0).getDataList(), patientSheet);
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
excelWriter.finish();
} catch (Exception e) {
log.info("invoke excelUtil write error : {}",e.getMessage());
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
ApiResult result = new ApiResult(60021, "导出Excel文件异常!");
response.getWriter().println(JSON.toJSONString(result));
}
}
}