读Excel的两种方式:
方法一:同步读
- 创建文件流,并读取文件到dataList
InputStream fileStream = new FileInputStream(localFilePath);//文件流
List<Object> dataList = EasyExcelFactory.read(fileStream,new Sheet(1,1,Table.class));//读取文件流
System.out.println(dataList);//读取结果
- Table.class需要继承BaseRowModel并设置在表格中的index
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import java.io.Serializable;
@Data
public class Table extends BaseRowModel implements Serializable {
private static final long serialVersionUID = -7601964684105378541L;
/**
* 英文名
*/
@ExcelProperty(value = "英文名",index = 0)
private String eName;
/**
* 中文名
*/
@ExcelProperty(value = "中文名",index = 1)
private String zName;
}
方法二:异步读
- 异步读取方法
//异步读取加解析插入数据库
EasyExcel.read(localFilePath, Table.class,new Listener(parmas,service)).sheet().doRead();
- 监听类Listener
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import Table; import Service; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; import java.util.Map; public class Listener extends AnalysisEventListener<Table> { private Service service; private static final Logger LOGGER = LoggerFactory.getLogger(Listener.class); private List<Table> table = new ArrayList<>(); public TableInfoListener(Map parmas,Service service) { this.service = service; } @Override public void invoke(Table data, AnalysisContext context) { //对data进行处理 } @Override //最后执行一次+ public void doAfterAllAnalysed(AnalysisContext context) { saveData(); LOGGER.info("所有数据解析完成!"); } /** * 加上存储 */ private void saveData() { System.out.println("{ "+ tableInfos.size() +" }条数据,开始存储数据库!" + tableInfos.size()); service.save(table); }
Excel写并提供下载:
- 后台入口类
@PostMapping(value = "/local/export")
public void export(@RequestParam("Ids") String Ids, HttpServletResponse response) {
List<Table> data = Service.getTable(Ids);
try {
String fileName = new String(("EXCEL导出").getBytes(),"UTF-8");
String sheetName = "export";
ExcelUtil.writeExcel(response, data, fileName, sheetName, Table.class);
} catch (Exception e) {
RetInfo.error().put("message","下载失败");
}
}
- ExcelUtil类
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
throws Exception{
try{
fileName = URLEncoder.encode(fileName,"utf-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e){
throw new Exception("导出文件失败!");
}
}
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
String sheetName, Class clazz) throws Exception {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, clazz);
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
}
- vue前端写法
async exportIt() {
try {
let that = this;
let formData = { Ids: Ids };
axios({
method: "post",
responseType: "blob",
url: url,
params: that.$format(formData)
})
.then(function(response) {
let blob = new Blob([response.data], {
type: "application/ms-excel;charset=UTF-8"
});
let fileName = decodeURI(
response.headers["content-disposition"].split("filename=")[1]
);
if ("download" in document.createElement("a")) {
// 非IE下载
const elink = document.createElement("a");
elink.download = fileName;
elink.style.display = "none";
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href); // 释放URL 对象
document.body.removeChild(elink);
} else {
// IE10+下载
navigator.msSaveBlob(blob, fileName);
}
this.$notice("导出成功");
})
.catch(function(error) {
console.log(error);
});
} catch (error) {
console.log(error);
that.$notice("导出出错");
}
}