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;
@IgnoreColumn
private 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);
}