背景
做系统开发遇到的场景:
1.用户要把页面上创建的管理关系(比如部门关系/人员关系/产品关系等)导出,以便离线查看/分析/备份等作用;
2.用户要把本地的关系数据导入到系统
这里我把业务逻辑做了简化;介绍一下如何把数据导出成Excel以及如何将Excel数据导入(只要数据搞定了,具体什么逻辑就可以任意实现了)
ps:pom在最后
把数据导出成Excel
代码
package export;
/**
* 将数据导出成 xlsx 格式
**/
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class MyExport
{
public static void main(String[] args) throws IOException
{
XSSFWorkbook wb = new XSSFWorkbook();
String sheetName = "sheet-1";
Sheet sheet = wb.createSheet(sheetName);
//设置默认的列宽
sheet.setDefaultColumnWidth((short) 15);
// 创建第一行(也可以称为表头)
Row row = sheet.createRow(0);
// 给表头第一行把字段名写上
String[] cellValues = new String[]{"name", "age"};
for (int i = 0; i < cellValues.length; i++)
{
Cell cell = row.createCell((short) i);
cell.setCellValue(cellValues[i]);
}
//写两行数据
List<Student> data = new ArrayList<>();
data.add(new Student("Jone", 18));
data.add(new Student("Alice", 21));
for (int i = 0; i < data.size(); i++)
{
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(data.get(i).name);
row.createCell(1).setCellValue(data.get(i).age);
}
//文件名以 ".xlsx" 结尾纯属是为了Windows上好识别,其实啥名字都行,直接用Excel就能打开
File file = new File("D:\\tmp\\tmp\\student.xlsx");
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
System.out.println("finished");
}
private static class Student
{
private String name;
private int age;
public Student(String name, int age)
{
this.name = name;
this.age = age;
}
}
}
执行效果
Excel数据导入
这一步是将上面导出的文件导入到程序中来,如果能够成功读取到数据,那么具体业务逻辑就可以自己随便写了
代码
package myimport;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
/**
* 导入Excel文件
**/
public class MyImport
{
public static void main(String[] args) throws IOException
{
File file = new File("D:\\tmp\\tmp\\student.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);
XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
int sheetNum = hssfWorkbook.getNumberOfSheets();
for (int i = 0; i < sheetNum; i++)
{
XSSFSheet sheet = hssfWorkbook.getSheetAt(i);
int rowNum = sheet.getLastRowNum();
for (int rowIndex = 1; rowIndex <= rowNum; rowIndex++)
{
XSSFRow row = sheet.getRow(rowIndex);
System.out.println("===========================================");
System.out.println("name:" + row.getCell(0).getStringCellValue());
System.out.println("age:" + row.getCell(1).getNumericCellValue());
}
}
}
}
执行结果
pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>