本节介绍一下使用poi读写excel。
1、操作步骤
- 创建一个maven项目 poi-test
- 加入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
- 在src文件夹下创建Student.java
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
public class Student implements Serializable {
private int id;
private String name;
private int age;
private String gender;
public Student(int id, String name, int age, String gender) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
}
public Student() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
'}';
}
public List<String> convertObj2List() {
List<String> ret = new ArrayList<>();
ret.add(this.id + "");
ret.add(this.name);
ret.add(this.age + "");
ret.add(this.gender);
return ret;
}
}
- 在src文件夹下创建PoiTest.java
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class PoiTest {
public static boolean createWorkBook(String excelType, String sheetName, List<String> headList, List<List<String>> dataList, String path) {
Workbook wb = new HSSFWorkbook();
//创建表单
Sheet sheet = wb.createSheet(sheetName != null ? sheetName : "new sheet");
//设置字体
Font headFont = wb.createFont();
headFont.setFontHeightInPoints((short) 14);
headFont.setFontName("Courier New");
headFont.setItalic(false);
headFont.setStrikeout(false);
//设置头部单元格样式
CellStyle headStyle = wb.createCellStyle();
//设置单元格下线条及颜色
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
//设置单元格左线条及颜色
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
//设置单元格又线条及颜色
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
//设置单元格线条及颜色
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
//设置水平对齐方式
headStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐方式
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setFont(headFont); //设置字体
Row headRow = sheet.createRow(0);
CreationHelper createHelper = wb.getCreationHelper();
for (int i = 0; i < headList.size(); i++) { //遍历表头数据
Cell cell = headRow.createCell(i); //创建单元格
cell.setCellValue(createHelper.createRichTextString(headList.get(i))); //设置值
cell.setCellStyle(headStyle); //设置样式
}
int rowIndex = 1; //当前行索引
//创建Rows
//设置数据单元格格式
CellStyle dataStyle = wb.createCellStyle();
dataStyle.setBorderBottom(BorderStyle.THIN); //设置单元格线条
dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //设置单元格颜色
dataStyle.setBorderLeft(BorderStyle.THIN);
dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
dataStyle.setBorderRight(BorderStyle.THIN);
dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
dataStyle.setBorderTop(BorderStyle.THIN);
dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
dataStyle.setAlignment(HorizontalAlignment.LEFT); //设置水平对齐方式
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐方式
for (List<String> rowdata : dataList) { //遍历所有数据
Row row = sheet.createRow(rowIndex++); //第一行为头
for (int j = 0; j < rowdata.size(); j++) { //编译每一行
Cell cell = row.createCell(j);
cell.setCellStyle(dataStyle);
cell.setCellValue(createHelper.createRichTextString(rowdata.get(j)));
}
}
/*设置列自动对齐*/
for (int i = 0; i < headList.size(); i++) {
sheet.autoSizeColumn(i);
}
try (OutputStream fileOut = new FileOutputStream(path)) { //获取文件流
wb.write(fileOut); //将workbook写入文件流
} catch (FileNotFoundException e) {
e.printStackTrace();
return false;
} catch (IOException e) {
e.printStackTrace();
return false;
}
return true;
}
public static void readExcel(String path, String sheetName) throws IOException {
//创建输入流
FileInputStream fileInputStream = new FileInputStream(path);
//获得poi输入流
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fileInputStream);
//获得文档
HSSFWorkbook Workbook = new HSSFWorkbook(poifsFileSystem);
//根据name获取sheet表
HSSFSheet sheet = Workbook.getSheet(sheetName);
//获得行数，下标从0开始
int lastRow = sheet.getLastRowNum();
System.out.println("行数：" + (lastRow + 1));
//获取第二行（第一行一般是标题）
HSSFRow row = sheet.getRow(1);
//获得列数，下标从1开始
int lastCell = row.getLastCellNum();
System.out.println("列数：" + lastCell);
for (int i = 0; i <= lastRow; i++) {
//遍历每一行
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < lastCell; j++) {
//遍历每一列的cell
HSSFCell cell = row.getCell(j);
if (cell != null) System.out.print(cell.getStringCellValue() + " ");
}
System.out.println();
}
}
}
public static void main(String[] args) throws Exception {
//创建一个Excel
List<Student> list = new ArrayList<>();
list.add(new Student(1, "ali123", 34, "男"));
list.add(new Student(2, "xiaoli123", 3, "男"));
list.add(new Student(3, "zhangli123", 34, "男"));
List<List<String>> ret = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
List<String> obj2List = list.get(i).convertObj2List();
ret.add(obj2List);
}
createWorkBook("2003","test", Arrays.asList(new String[]{"id","name","age","性别"}),ret,"D:/1.xls");
//读取excel
readExcel("D:/1.xls", "test");
}
}
