myexcel的使用

参考：

（1）api文档：https://www.bookstack.cn/read/MyExcel-2.x/624d8ce73162300b.md

（2）源代码：  https://github.com/liaochong/myexcel/issues

我：

（1）maven依赖

<dependency><!--excel--><groupId>com.github.liaochong</groupId><artifactId>myexcel</artifactId><version>3.7.1</version>
</dependency>

（2）myexcel工具类

import com.github.liaochong.myexcel.core.DefaultExcelBuilder;
import com.github.liaochong.myexcel.core.DefaultExcelReader;
import com.github.liaochong.myexcel.utils.AttachmentExportUtil;
import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;/*** MyExcel:导入工具*/
public class MyExcelUtils
{// 默认导入public static <T> List<T> defaultImport(InputStream stream, Class<T> clazz){List<T> list = new ArrayList<T>() {{try {DefaultExcelReader.of(clazz).sheet(0).rowFilter(row -> row.getRowNum() > 0) // 如无需过滤，可省略该操作，0代表第一行.readThen(stream ,(Consumer<T>)d -> add(d));} catch (Exception e) {if(e.getLocalizedMessage().contains("Duplicate key")){throw new RuntimeException("图片定位重复，单元格图片不可以越线");}if(e.getLocalizedMessage().contains("Java heap space")){throw new RuntimeException("excel文件太大，堆内存不够用");}throw new RuntimeException(e);}}};return list;}// 默认导出public static void defaultExport(HttpServletResponse response, List list, Class<?> clazz, String fileName) throws IOException{Workbook workbook = DefaultExcelBuilder.of(clazz).build(list);AttachmentExportUtil.export(workbook, (fileName == null || "".equals(fileName.trim())) ? "文件" : fileName, response);}
}

（3）实体类映射模板

@Data
@ExcelModel(sheetName = "商品导入模板", rowHeight = 50,style={"vertical-align:center"}, dateTimeFormat="yyyy-MM-dd HH:mm", dateFormat="yyyy-MM-dd", decimalFormat="#")
@EqualsAndHashCode(callSuper = false)
public class ProductExcelModel {@ExcelColumn(index = 0, title = "商品名称")private String title;@ExcelColumn(index = 1, title = "商品图片")private InputStream image;@IgnoreColumnprivate String result; // 导入反馈错误信息}

（4）导出excel模板

public void loadTemplate(HttpServletResponse response){List<ProductExcelModel> list = new ArrayList<>();ProductExcelModel model = new ProductExcelModel();model.setTitle("产品名称");model.setImage(new FileInputStream("xx.jpg"));list.add(model);ExcelExportBuilder.defaultExcelExport(response, list, ProductExcelModel.class, "商品导入模板");}

（5）解析excel文件内容

public ResultInfo parseExcel(MultipartFile file) throws Exception{List<ProductExcelModel> list = ExcelImportBuilder.defaultImport(file.getInputStream(), ProductExcelModel.class);for(ProductExcelModel model:list){String result = "";if(StringUtils.isBlank(model.getTitle())){result = result + "\n商品名称不能为空";}if(null == model.getImage()){result = result + "\n商品图片不能为空";}model.setResult(result);}return ResultInfo.valueOf(list);}