poi与easyexcel的基本使用_又菜又爱玩呜呜呜~的博客-爱代码爱编程
1.poi
先加载到内存,再写入文件
xlsx使用的是XSSF
Workbook workbook = new XSSFWorkbook(inputStream);
xls使用的是HSSF
Workbook workbook = new HSSFWorkbook(inputStream);
读取一个sheet中的所有数据
Sheet sheet = book.getSheetAt(page);
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
System.out.println(cell.toString());
}
}
将数据写入
sheet.creatRow(第几行)
row.createCell(第几列)
生成表
FileOutputStream fileOutputStream = new FileOutputStream("...xlsx");
book.writer(fileOutputStream)
poi的excel工具类
public class ExcelUtils {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
/**
* 输出数据到自定义模版的Excel输出流
*
* @param excelTemplate 自定义模版文件
* @param data 数据
* @param outputStream Excel输出流
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static void writeDataToTemplateOutputStream(File excelTemplate, List<List<Object>> data, OutputStream outputStream) throws IOException {
Workbook book = ExcelUtils.getWorkbookFromExcel(excelTemplate);
ExcelUtils.writeDataToWorkbook(null, data, book, 0);
ExcelUtils.writeWorkbookToOutputStream(book, outputStream);
}
/**
* 从Excel文件获取Workbook对象
*
* @param excelFile Excel文件
* @return Workbook对象
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static Workbook getWorkbookFromExcel(File excelFile) throws IOException {
try (
InputStream inputStream = new FileInputStream(excelFile);
) {
if (excelFile.getName().endsWith(XLS)) {
return new HSSFWorkbook(inputStream);
} else if (excelFile.getName().endsWith(XLSX)) {
return new XSSFWorkbook(inputStream);
} else {
throw new IOException("文件类型错误");
}
}
}
/**
* 把Workbook对象内容输出到Excel文件
*
* @param book Workbook对象
* @param file Excel文件
* @throws FileNotFoundException 找不到文件异常,文件已创建,实际不存在该异常
* @throws IOException 输入输出异常
*/
public static void writeWorkbookToFile(Workbook book, File file) throws FileNotFoundException, IOException {
if (!file.exists()) {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
file.createNewFile();
}
try (
OutputStream outputStream = new FileOutputStream(file);
) {
writeWorkbookToOutputStream(book, outputStream);
}
}
/**
* 把Workbook对象输出到Excel输出流
*
* @param book Workbook对象
* @param outputStream Excel输出流
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throws IOException {
book.write(outputStream);
}
/**
* 输出数据到Workbook对象中指定页码
*
* @param title 标题,写在第一行,可传null
* @param data 数据
* @param book Workbook对象
* @param page 输出数据到Workbook指定页码的页面数
*/
public static void writeDataToWorkbook(List<String> title, List<List<Object>> data, Workbook book, int page) {
Sheet sheet = book.getSheetAt(page);
Row row = null;
Cell cell = null;
// 设置表头
if (null != title && !title.isEmpty()) {
row = sheet.getRow(0);
if (null == row) {
row = sheet.createRow(0);
}
for (int i = 0; i < title.size(); i++) {
cell = row.getCell(i);
if (null == cell) {
cell = row.createCell(i);
}
cell.setCellValue(title.get(i));
}
}
List<Object> rowData = null;
for (int i = 0; i < data.size(); i++) {
row = sheet.getRow(i + 1);
if (null == row) {
row = sheet.createRow(i + 1);
}
rowData = data.get(i);
if (null == rowData) {
continue;
}
for (int j = 0; j < rowData.size(); j++) {
cell = row.getCell(j);
if (null == cell) {
cell = row.createCell(j);
}
setValue(cell, rowData.get(j));
}
}
}
/**
* 读取Excel文件第一页
*
* @param pathname 文件路径名
* @return 第一页数据集合
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static List<List<Object>> readExcelFirstSheet(String pathname) throws IOException {
File file = new File(pathname);
return readExcelFirstSheet(file);
}
/**
* 读取Excel文件第一页
*
* @param file Excel文件
* @return 第一页数据集合
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static List<List<Object>> readExcelFirstSheet(File file) throws IOException {
try (
InputStream inputStream = new FileInputStream(file);
) {
if (file.getName().endsWith(XLS)) {
return readXlsFirstSheet(inputStream);
} else if (file.getName().endsWith(XLSX)) {
return readXlsxFirstSheet(inputStream);
} else {
throw new IOException("文件类型错误");
}
}
}
/**
* 读取xls格式Excel文件第一页
*
* @param inputStream Excel文件输入流
* @return 第一页数据集合
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static List<List<Object>> readXlsFirstSheet(InputStream inputStream) throws IOException {
Workbook workbook = new HSSFWorkbook(inputStream);
return readExcelFirstSheet(workbook);
}
/**
* 读取xlsx格式Excel文件第一页
*
* @param inputStream Excel文件输入流
* @return 第一页数据集合
* @throws IOException 错误时抛出异常,由调用者处理
*/
public static List<List<Object>> readXlsxFirstSheet(InputStream inputStream) throws IOException {
Workbook workbook = new XSSFWorkbook(inputStream);
return readExcelFirstSheet(workbook);
}
/**
* 读取Workbook第一页
*
* @param book Workbook对象
* @return 第一页数据集合
*/
public static List<List<Object>> readExcelFirstSheet(Workbook book) {
return readExcel(book, 0);
}
/**
* 读取指定页面的Excel
*
* @param book Workbook对象
* @param page 页码
* @return 指定页面数据集合
*/
public static List<List<Object>> readExcel(Workbook book, int page) {
List<List<Object>> list = new ArrayList<>();
Sheet sheet = book.getSheetAt(page);
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// 如果当前行为空,则加入空,保持行号一致
if (null == row) {
list.add(null);
continue;
}
List<Object> columns = new ArrayList<>();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
columns.add(getValue(cell));
}
list.add(columns);
}
return list;
}
/**
* 解析单元格中的值
*
* @param cell 单元格
* @return 单元格内的值
*/
private static Object getValue(Cell cell) {
if (null == cell) {
return null;
}
Object value = null;
switch (cell.getCellType()) {
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case NUMERIC:
// 日期类型,转换为日期
if (DateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
}
// 数值类型
else {
// 默认返回double,创建BigDecimal返回准确值
value = new BigDecimal(cell.getNumericCellValue());
}
break;
default:
value = cell.toString();
break;
}
return value;
}
/**
* 设置单元格值
*
* @param cell 单元格
* @param value 值
*/
private static void setValue(Cell cell, Object value) {
if (null == cell) {
return;
}
if (null == value) {
cell.setCellValue((String) null);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue(FORMAT.format((Date) value));
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else {
cell.setCellValue(value.toString());
}
}
}
2.easyExcel
一行一行返回
1.读excel
controller层调用service
@RequestMapping(value = "/uploadRank", method = RequestMethod.POST)
public CommonResponse fileRank(@RequestParam("uploadFile") MultipartFile uploadFile, HttpServletRequest request) throws IOException {
boolean b = studentrankService.readExcel(uploadFile);
if (!b){
throw new BusinessException(403, "导入错误");
}
return CommonResponse.ok("导入成功");
}
service层
@Override
public boolean readExcel(MultipartFile file) {
try {
//三个参数 文件流对象,对应的类.class, easyexcel的监听器
EasyExcel.read(file.getInputStream(),Studentrank.class,new EasyExcelListener()).sheet().
//跳过头部从第一行开始
headRowNumber(1).doRead();
} catch (IOException e) {
throw new RuntimeException(e);
}
return true;
}
实体类
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Studentrank对象", description="")
public class Studentrank implements Serializable {
@ExcelIgnore
@TableId(value = "Id", type = IdType.ASSIGN_ID)
private String id;
@ExcelProperty(value = "理科分数", index = 0)
@ApiModelProperty(value = "理科分数")
private String scienceScore;
@ExcelProperty(value = "理科人数", index = 1)
@ApiModelProperty(value = "理科人数")
private String scienceNumber;
@ExcelProperty(value = "理科位次", index = 2)
@ApiModelProperty(value = "理科位次")
private String scienceRank;
@ExcelProperty(value = "文科分数", index = 3)
@ApiModelProperty(value = "文科分数")
private String liberalScore;
@ExcelProperty(value = "文科人数", index = 4)
@ApiModelProperty(value = "文科人数")
private String liberalNumber;
@ExcelProperty(value = "文科位次", index = 5)
@ApiModelProperty(value = "文科位次")
private String liberalRank;
@ExcelProperty(value = "文科位次", index = 6)
@ApiModelProperty(value = "年度")
private String year;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getScienceScore() {
return scienceScore;
}
public void setScienceScore(String scienceScore) {
this.scienceScore = scienceScore;
}
public String getScienceNumber() {
return scienceNumber;
}
public void setScienceNumber(String scienceNumber) {
this.scienceNumber = scienceNumber;
}
public String getScienceRank() {
return scienceRank;
}
public void setScienceRank(String scienceRank) {
this.scienceRank = scienceRank;
}
public String getLiberalScore() {
return liberalScore;
}
public void setLiberalScore(String liberalScore) {
this.liberalScore = liberalScore;
}
public String getLiberalNumber() {
return liberalNumber;
}
public void setLiberalNumber(String liberalNumber) {
this.liberalNumber = liberalNumber;
}
public String getLiberalRank() {
return liberalRank;
}
public void setLiberalRank(String liberalRank) {
this.liberalRank = liberalRank;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
}
easyexcel监听器
@Slf4j
@Component
public class EasyExcelListener extends AnalysisEventListener<Studentrank> {
private static StudentrankService studentrankService;
public EasyExcelListener (){
}
@Autowired
public void setStudentrankService(StudentrankService studentrankService){
EasyExcelListener.studentrankService = studentrankService;
}
/**
* 设置最大数
*/
private static final int BATCH_COUNT = 100;
/**
* 临时缓存List
*/
public static List<Studentrank> importList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
log.info(exception.getMessage());
}
@Override
public void invoke(Studentrank studentrank, AnalysisContext analysisContext) {
importList.add(studentrank);
if (importList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
importList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
}
private void saveData(){
log.info("{}条数据,开始存储数据库!", importList.size());
boolean b = studentrankService.saveDate(importList);
if (b){
log.info("存储数据库成功!");
} else {
log.info("存储数据库失败!");
}
}
}
数据库插入多条数据 mybatis-plus
@Override
public boolean saveDate(List<Studentrank> studentrank) {
boolean b = saveBatch(studentrank);
return b;
}
数据库插入多条数据 mybatis
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.school.zhaoban.mapper.StudentrankMapper">
<insert id="saveAll" parameterType="java.util.ArrayList">
insert into studentrank (science_score, science_number, science_rank, liberal_score, liberal_number, liberal_rank, `year`)
values
<foreach collection="list" index="index" separator="," item="item">
(#item.science_score, #item.science_number, #item.science_rank, #item.liberal_score, #item.liberal_number, #item.liberal_rank, #item.year)
</foreach>
</insert>
</mapper>
2.写excel
实体类 注解@ExcelProperty(“属性名”)
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="UserInfo对象", description="")
public class UserInfo implements Serializable {
@ExcelProperty("编号")
@TableId(value = "Id", type = IdType.ASSIGN_ID)
private String id;
@ExcelProperty("用户姓名")
@ApiModelProperty(value = "用户姓名")
@TableField("userName")
private String username;
@ApiModelProperty(value = "用户图像")
@TableField("userAvatar")
private String useravatar;
@ExcelIgnore
@ApiModelProperty(value = "小程序身份识别")
@TableField("openId")
private String openid;
@ExcelProperty("电话")
@ApiModelProperty(value = "电话")
@TableField("phoneNumber")
private String phonenumber;
@ExcelIgnore
@ExcelProperty("性别")
@ApiModelProperty(value = "性别")
private String sex;
@ExcelProperty("加入时间")
@ApiModelProperty(value = "插入时间")
@TableField(value = "creatTime", fill = FieldFill.INSERT)
private String creattime;
@ExcelProperty("更新时间")
@ApiModelProperty(value = "更新时间")
@TableField(value = "updateTime", fill = FieldFill.INSERT_UPDATE)
private String updatetime;
@ExcelProperty("QQ")
@TableField("QQ")
private String qq;
@ExcelProperty("高中学校")
@ApiModelProperty(value = "高中学校")
private String highSchool;
@ExcelProperty("市")
@ApiModelProperty(value = "市")
private String city;
}
controller层
//查询所有数据
List<UserInfo> list = userService.list();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
String fileName = "用户信息";
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
//输出流对象 对应的类
EasyExcel.write(response.getOutputStream(), UserInfo.class).autoCloseStream(Boolean.FALSE).sheet("用户信息").doWrite(list);