h

    h

关注
发信
关注(28)粉丝(399)

Java利用POI实现导入导出Excel表格示例代码

转自：https://www.jb51.net/article/95526.htm

介绍

Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html

实现

已经在代码中加入了完整的注释。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
public class ExcelOperate {
 
  public static void main(String[] args) {
    // 创建Excel表格
    createExcel(getStudent());
 
    // 读取Excel表格
    List<Student> list = readExcel();
    System.out.println(list.toString());
  }
 
  /**
   * 初始化数据
   *
   * @return 数据
   */
  private static List<Student> getStudent() {
    List<Student> list = new ArrayList<Student>();
    Student student1 = new Student("小明", 8, "二年级");
    Student student2 = new Student("小光", 9, "三年级");
    Student student3 = new Student("小花", 10, "四年级");
    list.add(student1);
    list.add(student2);
    list.add(student3);
    return list;
  }
 
  /**
   * 创建Excel
   *
   * @param list
   *      数据
   */
  private static void createExcel(List<Student> list) {
    // 创建一个Excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 创建一个工作表
    HSSFSheet sheet = workbook.createSheet("学生表一");
    // 添加表头行
    HSSFRow hssfRow = sheet.createRow(0);
    // 设置单元格格式居中
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 
    // 添加表头内容
    HSSFCell headCell = hssfRow.createCell(0);
    headCell.setCellValue("姓名");
    headCell.setCellStyle(cellStyle);
 
    headCell = hssfRow.createCell(1);
    headCell.setCellValue("年龄");
    headCell.setCellStyle(cellStyle);
 
    headCell = hssfRow.createCell(2);
    headCell.setCellValue("年级");
    headCell.setCellStyle(cellStyle);
 
    // 添加数据内容
    for (int i = 0; i < list.size(); i++) {
      hssfRow = sheet.createRow((int) i + 1);
      Student student = list.get(i);
 
      // 创建单元格，并设置值
      HSSFCell cell = hssfRow.createCell(0);
      cell.setCellValue(student.getName());
      cell.setCellStyle(cellStyle);
 
      cell = hssfRow.createCell(1);
      cell.setCellValue(student.getAge());
      cell.setCellStyle(cellStyle);
 
      cell = hssfRow.createCell(2);
      cell.setCellValue(student.getGrade());
      cell.setCellStyle(cellStyle);
    }
 
    // 保存Excel文件
    try {
      OutputStream outputStream = new FileOutputStream("D:/students.xls");
      workbook.write(outputStream);
      outputStream.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
 
  /**
   * 读取Excel
   *
   * @return 数据集合
   */
  private static List<Student> readExcel() {
    List<Student> list = new ArrayList<Student>();
    HSSFWorkbook workbook = null;
 
    try {
      // 读取Excel文件
      InputStream inputStream = new FileInputStream("D:/students.xls");
      workbook = new HSSFWorkbook(inputStream);
      inputStream.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
 
    // 循环工作表
    for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
      HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
      if (hssfSheet == null) {
        continue;
      }
      // 循环行
      for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
        if (hssfRow == null) {
          continue;
        }
 
        // 将单元格中的内容存入集合
        Student student = new Student();
 
        HSSFCell cell = hssfRow.getCell(0);
        if (cell == null) {
          continue;
        }
        student.setName(cell.getStringCellValue());
 
        cell = hssfRow.getCell(1);
        if (cell == null) {
          continue;
        }
        student.setAge((int) cell.getNumericCellValue());
 
        cell = hssfRow.getCell(2);
        if (cell == null) {
          continue;
        }
        student.setGrade(cell.getStringCellValue());
 
        list.add(student);
      }
    }
    return list;
  }
}

附上Student类的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
public class Student {
 
  private String name;
  private int age;
  private String grade;
 
  public Student() {
  }
 
  public Student(String name, int age, String grade) {
    super();
    this.name = name;
    this.age = age;
    this.grade = grade;
  }
 
  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 getGrade() {
    return grade;
  }
 
  public void setGrade(String grade) {
    this.grade = grade;
  }
 
  @Override
  public String toString() {
    return "Student [name=" + name + ", age=" + age + ", grade=" + grade
        + "]";
  }
}

测试结果

导出的Excel表格


students

打印读取的Excel数据

1
[Student [name=小明, age=8, grade=二年级], Student [name=小光, age=9, grade=三年级], Stu
01-23 05:54
Powered by LMLPHP ©2024     h 0.063582