1. <dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
<exclusions>
<exclusion>
<groupId>org.ow2.asm</groupId>
<artifactId>asm</artifactId>
</exclusion>
</exclusions>
</dependency>
2. @PostMapping(value="/Import")
public Response importDispatch(@RequestParam("file") MultipartFile file) {
long start = System.currentTimeMillis();
Map<String,Object> result = excelUtil.readExcel(file, new DispatchExcelImport(),1);
List<DispatchExcelImport> dispatchExcelImports = new ArrayList<>();
Boolean flag = (Boolean) result.get("flag");
Response response;
List<Object> list = new ArrayList<>();
if(flag){
list = (List<Object>) result.get("datas");
if(!CollectionUtils.isEmpty(list)){
dispatchExcelImports = (List)list;
}
}else{
return BaseUtil.createResponse(ReturnCode.ERROR, "", Arrays.asList(ReturnMessage.EXCEL_HEAD_ERROR));
}
//do something
long end = System.currentTimeMillis();
System.out.println("=======================导入耗时:" + (end - start));
return response;
}
3. @GetMapping(value = "/Export")
public void export(HttpServletResponse response, DispatchDTO dispatchDTO) {
Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
List<DispatchExcelExport> models = new ArrayList<>();
List<DispatchImport> exportDatas = dispatchImportDao.selectDataForExport(dispatchDTO);
if (CollectionUtils.isEmpty(exportDatas)) {
models.add(new DispatchExcelExport());
} else {
exportDatas.forEach(dispatchData -> {
DispatchExcelExport excelExport = new DispatchExcelExport();
BaseUtil.copy(dispatchData, excelExport);
models.add(excelExport);
});
}
map.put("wenjian", models);
excelUtil.createExcel(response, map, ExcelTypeEnum.XLSX, "wenjian");
}
4. @Data
public class DispatchExcelImport extends BaseRowModel {
@ExcelProperty(value = "员编", index = 0)
private String employeeId;
@ExcelProperty(value = "姓名", index = 1)
private String employeeName;
@ExcelProperty(value = "身份证", index = 2)
private String identityId;
@ExcelProperty(value = "城市", index = 3)
private String cityName; }
package com.cmbchina.ccd.oa.socialsecurity.util;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.cmbchina.ccd.oa.socialsecurity.model.bo.ExcelListener;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public class ExcelUtil {
private static Sheet initSheet;
static {
initSheet = new Sheet(1, 0);
initSheet.setSheetName("sheet");
//设置自适应宽度
initSheet.setAutoWidth(Boolean.TRUE);
}
/**
* 读取少于1000行数据
* 数据量少时,同步读取
*
* @param file 读取的文件
* @return
*/
public List<Object> readLessThan1000Row(MultipartFile file) throws IOException {
return readLessThan1000RowBySheet(file, null);
}
/**
* 读小于1000行数据
* filePath 文件绝对路径
* initSheet :
* sheetNo: sheet页码,默认为1
* headLineMun: 从第几行开始读取数据,默认为0, 表示从第一行开始读取
* clazz: 返回数据List<Object> 中Object的类名
*/
public List<Object> readLessThan1000RowBySheet(MultipartFile file, Sheet sheet) throws IOException {
if (file == null) {
//log.info("导入文件为空", file);
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
try {
fileStream = file.getInputStream();
return EasyExcelFactory.read(fileStream, sheet);
} catch (FileNotFoundException e) {
//log.info("文件有误, 文件:{}", file);
} finally {
try {
if (fileStream != null) {
fileStream.close();
}
} catch (IOException e) {
//log.info("excel文件读取失败, 失败原因:{}", e);
}
}
return null;
}
/**
* 生成excle
*
* @param filePath 绝对路径, 如:/home/Downloads/aaa.xlsx
* @param data 数据源
* @param head 表头
*/
public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
writeSimpleBySheet(filePath, data, head, null);
}
/**
* 生成excle
*
* @param filePath 绝对路径
* @param data 数据源
* @param sheet excle页面样式
* @param head 表头
*/
public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {
sheet = (sheet != null) ? sheet : initSheet;
if (head != null) {
List<List<String>> list = new ArrayList<>();
head.forEach(h -> list.add(Collections.singletonList(h)));
sheet.setHead(list);
}
OutputStream outputStream = null;
ExcelWriter writer = null;
try {
outputStream = new FileOutputStream(filePath);
writer = EasyExcelFactory.getWriter(outputStream);
writer.write1(data, sheet);
} catch (FileNotFoundException e) {
//log.error("找不到文件或文件路径错误, 文件:{}", filePath);
} finally {
try {
if (writer != null) {
writer.finish();
}
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
//log.error("excel文件导出失败");
}
}
}
/**
* @Description 导出excel 支持一张表导出多个sheet
* @Param OutputStream 输出流
* Map<String, List> sheetName和每个sheet的数据
* ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
*/
public void createExcel(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> sheetNameAndDateList, ExcelTypeEnum type, String fileName) {
// if (checkParam(SheetNameAndDateList, type)) return;
try {
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
//解决导出文件名中文乱码
fileName = new String(fileName.getBytes(), "iso8859-1") + DateUtil.todayStr();
response.setHeader("Content-disposition", "attachment;filename=" + fileName + type.getValue());
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, type, true);
setSheet(sheetNameAndDateList, writer);
writer.finish();
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @Description //setSheet数据
*/
private void setSheet(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelWriter writer) {
int sheetNum = 1;
for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : SheetNameAndDateList.entrySet()) {
Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
sheet.setSheetName(stringListEntry.getKey());
writer.write(stringListEntry.getValue(), sheet);
sheetNum++;
}
}
/**
* @Description 校验参数
*/
private static boolean checkParam(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) {
if (CollectionUtils.isEmpty(SheetNameAndDateList)) {
//log.error("SheetNameAndDateList不能为空");
return true;
} else if (type == null) {
//log.error("导出的excel类型不能为空");
return true;
}
return false;
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
return readExcel(excel, rowModel, 1, 1);
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public Map<String, Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
Map<String, Object> result = new HashMap<>();
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
//校验表头
Boolean flag = false;
//维护实体类中 没有@ExcelProperty 放置在最后,会被映射出null表头
String head = excelListener.getImportHeads().replace("null,", "");
if (head.equals(excelListener.getModelHeads())) {
flag = true;
}
result.put("flag", flag);
result.put("datas", excelListener.getDatas());
return result;
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数,默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}
/**
* 读取指定sheetName的Excel(多个 sheet)
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
* @throws IOException
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
//读取指定名称的sheet
if (sheet.getSheetName().contains(sheetName)) {
reader.read(sheet);
break;
}
}
return excelListener.getDatas();
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
* @throws IOException
*/
private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
String filename = excel.getOriginalFilename();
if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
InputStream is = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(is, null, excelListener, false);
} else {
return null;
}
}
}
easyexcel 导入导出
最新推荐文章于 2024-05-09 16:17:14 发布