Excel文件导入导出
依赖包为apach的poi包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
普通形式文件导入
文件导入比较简单,poi包里面有专门的类处理excel文件,XSSFWorkbook,XSSFSheet,XSSFRow,XSSFCell分别为不同的层级
public class XmlFileImport {
public static void main(String[] args) throws IOException {
String path = "C:\\Users\\shenp\\Desktop\\部分三方数据调用统计(1).xlsx";
File file = new File(path);
// JFileChooser jFileChooser = new JFileChooser();
// int state=jFileChooser.showOpenDialog(null);
// File file2 = jFileChooser.getSelectedFile();
//获取excel
XSSFWorkbook xs = new XSSFWorkbook(new FileInputStream(file));
List<String> reslist = new ArrayList<>();
int sheetcount = xs.getNumberOfSheets();
for (int i = 0; i < sheetcount; i++) {
//获取excel的sheet
XSSFSheet xssfSheet = xs.getSheetAt(i);
if(xssfSheet==null){
continue;
}
for (int i1 = 0; i1 < xssfSheet.getLastRowNum(); i1++) {
//获取excel中的行
XSSFRow xssfRow = xssfSheet.getRow(i1);
if(xssfRow==null){
continue;
}
for (int i2 = 0; i2 < xssfRow.getLastCellNum(); i2++) {
//获取excel中的单元格
XSSFCell xssfCell = xssfRow.getCell(i2);
if(xssfCell==null){
continue;
}
if(xssfCell.getCellType()== CellType.STRING){
reslist.add(xssfCell.getStringCellValue());
}
}
}
}
log.info(JSONArray.toJSONString(reslist));
}
}
阿里巴巴文件导入导出
文件导出
使用阿里巴巴的easyexcel,注意easyexcel自带poi包,同时引入easyexcel和poi会导致报错
下面是简单例子,具体参考阿里巴巴easyexcel仓库文档
https://easyexcel.opensource.alibaba.com/qa
<!--阿里巴巴easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
简单例子
public class AliFileoutput {
public static void main(String[] args) {
// 注意 simpleWrite在数据量不大的情况下可以使用(5000以内,具体也要看实际情况),数据量大参照 重复多次写入
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = "D:\\111.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.doWrite(data());
}
private static List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
}
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
web形式
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("模板").doWrite(data());
}
文件导入
String fileName = "import.xlsx";
EasyExcel.read(fileName, USER.class, new PageReadListener<USER>(dataList -> {
for (USER demoData : dataList) {
log.info("读取到一条数据{}", JSON.toJSONString(demoData));
try{
//do something
}catch (Exception e){
log.info("{}数据出现异常,跳过",JSON.toJSONString(demoData));
}
}
})).sheet().doRead();