POI实现excel多sheet文件导入导出
POI多Sheet文件导入
导入依赖
版本大家可以根据需要自行选择
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
实体类
package com.ruoyi.system.domain.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.core.domain.BaseEntity;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
/**
* 用印申请对象 sys_with_seal
*
* @author ruoyi
* @date 2024-04-18
*/
@Data
public class TestVO
{
/** 签字日期 */
@Excel(value = "签字日期")
private String signTime;
/** 填报日期 */
@Excel((value = "填报日期")
private String fillTime;
/** 事由 */
@Excel((value = "事由")
private String reason;
/** 类型 */
@Excel((value = "类型")
private String type;
/** 申请人 */
@Excel((value = "申请人")
private String applicant;
/** 经办人 */
@Excel((value = "经办人")
private String attn;
/** 份数 */
@Excel((value = "份数")
private String num;
/** 备注 */
@Excel((value = "备注")
private String remark;
}
excel文件数据解析
测试代码的sheet为同一对象,大家根据自己的需要解析成不同的实体类即可
public Integer test() throws IOException {
List<TestVo> sheetList1 = new ArrayList<>();
List<TestVo> sheetList2 = new ArrayList<>();
List<TestVo> sheetList3 = new ArrayList<>();
Workbook workbook = WorkbookFactory.create(new FileInputStream(new File("url")));
// 遍历每个 sheet
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
//根据sheet名称匹配
Sheet sheet = workbook.getSheetAt(i);
if ("测试sheet1".equals(sheet.getSheetName())) {
//测试sheet1列表
sheetList1= testMerge(sheet);
}
if ("测试sheet2".equals(sheet.getSheetName())) {
//测试sheet2列表
sheetList2= testMerge(sheet);
}
if ("测试sheet3".equals(sheet.getSheetName())) {
//测试sheet2列表
sheetList2= testMerge(sheet);
}
}
//业务操作 ...
}
/**
* 数据解析
* @param sheet
* @return
*/
private List<TestVo> textRegistrationMerge(Sheet sheet) {
// 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
int rows = sheet.getLastRowNum();
List<TestVo> list = new ArrayList<>();
// 遍历每行
for (int j = 1; j <= rows; j++) {
Row row = sheet.getRow(j);
TestVo vo= new TestVo ();
vo.setSignTime(getValue(row.getCell(0)));
vo.setFillTime(getValue(row.getCell(1)));
//根据单元格赋值....
list.add(vo);
}
return list;
}
POI多Sheet文件导出
数据填充
public void export(HttpServletResponse response) throws Exception {
try {
File file1 = new File("url");
// 取得文件名。
String filename = file1.getName();
// 取得文件的后缀名。
String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
FileInputStream file = new FileInputStream(file1);
Workbook workbook = WorkbookFactory.create(file);
// 遍历每个 sheet
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
if ("测试sheet1".equals(sheet.getSheetName())) {
//测试sheet1填充
testPadding(sheet);
}
if ("测试sheet2".equals(sheet.getSheetName())) {
//测试sheet2填充
testPadding(sheet);
}
if ("测试sheet3".equals(sheet.getSheetName())) {
//测试sheet3填充
testPadding(sheet);
}
}
response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/msexcel");
OutputStream os = response.getOutputStream();
workbook.write(os);
} catch (IOException ex) {
ex.printStackTrace();
}
}
private void textRegistration(Sheet sheet) {
// 数据库查询测试数据
List<TestVo> list = textRegistrationService.selectTestVoList();
for (int i = 0; i < sysTextRegistrations.size(); i++) {
Row row = sheet.createRow(i + 1);
SysTextRegistration textRegistration = sysTextRegistrations.get(i);
//指定单元格
row.createCell(0).setCellValue(textRegistration.getSignTime());
row.createCell(1).setCellValue(textRegistration.getFillTime());
}
}
以上便是使用poi做excel多sheet的导入导出,需要去指定单元格去做操作,如果字段变更的话改动还是比较大的,我个人感觉EasyExcel挺好用的,如果需要请看之前文章,感谢大家的观看