Java的poi技术读取和导入Excel

た入场券 2022-05-30 00:52 152阅读 0赞

报表输出是Java应用开发中经常涉及的内容，而一般的报表往往缺乏通用性，不方便用户进行个性化编辑。Java程序由于其跨平台特性，不能直接操纵Excel。因此，本文探讨一下POI视线Java程序进行Excel的读取和导入。

作者：Hongten 来源：Hongten的博客| 2012-02-22 09:44

##  ##

项目结构：

![java\_poi\_excel][java_poi_excel]

用到的Excel文件：

![xls][]

XlsMain .java 类

//该类有main方法，主要负责运行程序，同时该类中也包含了用poi读取Excel(2003版)

1.  **import** java.io.FileInputStream; 
2.  **import** java.io.IOException; 
3.  **import** java.io.InputStream; 
4.  **import** java.util.ArrayList; 
5.  **import** java.util.List; 
6.  
7.  **import** org.apache.poi.hssf.usermodel.HSSFCell; 
8.  **import** org.apache.poi.hssf.usermodel.HSSFRow; 
9.  **import** org.apache.poi.hssf.usermodel.HSSFSheet; 
10. **import** org.apache.poi.hssf.usermodel.HSSFWorkbook; 
11. 
12. 
13. /\*\* 
14.  \* 
15.  \* @author Hongten</br> 
16.  \* 
17.  \* 参考地址：http://hao0610.iteye.com/blog/1160678 
18.  \* 
19.  \*/
20. **public****class** XlsMain \{ 
21. 
22. **public****static****void** main(String\[\] args) **throws** IOException \{ 
23.  XlsMain xlsMain = **new** XlsMain(); 
24.  XlsDto xls = **null**; 
25.  List<XlsDto> list = xlsMain.readXls(); 
26. 
27. **try** \{ 
28.  XlsDto2Excel.xlsDto2Excel(list); 
29.  \} **catch** (Exception e) \{ 
30.  e.printStackTrace(); 
31.  \} 
32. **for** (**int** i = 0; i < list.size(); i++) \{ 
33.  xls = (XlsDto) list.get(i); 
34.  System.out.println(xls.getXh() + " " \+ xls.getXm() + " "
35.  \+ xls.getYxsmc() + " " \+ xls.getKcm() + " "
36.  \+ xls.getCj()); 
37.  \} 
38. 
39.  \} 
40. 
41. /\*\* 
42.  \* 读取xls文件内容 
43.  \* 
44.  \* @return List<XlsDto>对象 
45.  \* @throws IOException 
46.  \* 输入/输出(i/o)异常 
47.  \*/
48. **private** List<XlsDto> readXls() **throws** IOException \{ 
49.  InputStream is = **new** FileInputStream("pldrxkxxmb.xls"); 
50.  HSSFWorkbook hssfWorkbook = **new** HSSFWorkbook(is); 
51.  XlsDto xlsDto = **null**; 
52.  List<XlsDto> list = **new** ArrayList<XlsDto>(); 
53. // 循环工作表Sheet 
54. **for** (**int** numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) \{ 
55.  HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 
56. **if** (hssfSheet == **null**) \{ 
57. **continue**; 
58.  \} 
59. // 循环行Row 
60. **for** (**int** rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) \{ 
61.  HSSFRow hssfRow = hssfSheet.getRow(rowNum); 
62. **if** (hssfRow == **null**) \{ 
63. **continue**; 
64.  \} 
65.  xlsDto = **new** XlsDto(); 
66. // 循环列Cell 
67. // 0学号 1姓名 2学院 3课程名 4 成绩 
68. // for (int cellNum = 0; cellNum <=4; cellNum++) \{ 
69.  HSSFCell xh = hssfRow.getCell(0); 
70. **if** (xh == **null**) \{ 
71. **continue**; 
72.  \} 
73.  xlsDto.setXh(getValue(xh)); 
74.  HSSFCell xm = hssfRow.getCell(1); 
75. **if** (xm == **null**) \{ 
76. **continue**; 
77.  \} 
78.  xlsDto.setXm(getValue(xm)); 
79.  HSSFCell yxsmc = hssfRow.getCell(2); 
80. **if** (yxsmc == **null**) \{ 
81. **continue**; 
82.  \} 
83.  xlsDto.setYxsmc(getValue(yxsmc)); 
84.  HSSFCell kcm = hssfRow.getCell(3); 
85. **if** (kcm == **null**) \{ 
86. **continue**; 
87.  \} 
88.  xlsDto.setKcm(getValue(kcm)); 
89.  HSSFCell cj = hssfRow.getCell(4); 
90. **if** (cj == **null**) \{ 
91. **continue**; 
92.  \} 
93.  xlsDto.setCj(Float.parseFloat(getValue(cj))); 
94.  list.add(xlsDto); 
95.  \} 
96.  \} 
97. **return** list; 
98.  \} 
99. 
100. /\*\* 
101.  \* 得到Excel表中的值 
102.  \* 
103.  \* @param hssfCell 
104.  \* Excel中的每一个格子 
105.  \* @return Excel中每一个格子中的值 
106.  \*/
107. @SuppressWarnings("static-access") 
108. **private** String getValue(HSSFCell hssfCell) \{ 
109. **if** (hssfCell.getCellType() == hssfCell.CELL\_TYPE\_BOOLEAN) \{ 
110. // 返回布尔类型的值 
111. **return** String.valueOf(hssfCell.getBooleanCellValue()); 
112.  \} **else****if** (hssfCell.getCellType() == hssfCell.CELL\_TYPE\_NUMERIC) \{ 
113. // 返回数值类型的值 
114. **return** String.valueOf(hssfCell.getNumericCellValue()); 
115.  \} **else** \{ 
116. // 返回字符串类型的值 
117. **return** String.valueOf(hssfCell.getStringCellValue()); 
118.  \} 
119.  \} 
120. 
121. \}

XlsDto2Excel.java类

//该类主要负责向Excel(2003版)中插入数据

1.  **import** java.io.FileOutputStream; 
2.  **import** java.io.OutputStream; 
3.  **import** java.util.List; 
4.  
5.  **import** org.apache.poi.hssf.usermodel.HSSFCell; 
6.  **import** org.apache.poi.hssf.usermodel.HSSFRichTextString; 
7.  **import** org.apache.poi.hssf.usermodel.HSSFRow; 
8.  **import** org.apache.poi.hssf.usermodel.HSSFSheet; 
9.  **import** org.apache.poi.hssf.usermodel.HSSFWorkbook; 
10. 
11. 
12. **public****class** XlsDto2Excel \{ 
13. 
14. /\*\* 
15.  \* 
16.  \* @param xls 
17.  \* XlsDto实体类的一个对象 
18.  \* @throws Exception 
19.  \* 在导入Excel的过程中抛出异常 
20.  \*/
21. **public****static****void** xlsDto2Excel(List<XlsDto> xls) **throws** Exception \{ 
22. // 获取总列数 
23. **int** CountColumnNum = xls.size(); 
24. // 创建Excel文档 
25.  HSSFWorkbook hwb = **new** HSSFWorkbook(); 
26.  XlsDto xlsDto = **null**; 
27. // sheet 对应一个工作页 
28.  HSSFSheet sheet = hwb.createSheet("pldrxkxxmb"); 
29.  HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始 
30.  HSSFCell\[\] firstcell = **new** HSSFCell\[CountColumnNum\]; 
31.  String\[\] names = **new** String\[CountColumnNum\]; 
32.  names\[0\] = "学号"; 
33.  names\[1\] = "姓名"; 
34.  names\[2\] = "学院"; 
35.  names\[3\] = "课程名"; 
36.  names\[4\] = "成绩"; 
37. **for** (**int** j = 0; j < CountColumnNum; j++) \{ 
38.  firstcell\[j\] = firstrow.createCell(j); 
39.  firstcell\[j\].setCellValue(**new** HSSFRichTextString(names\[j\])); 
40.  \} 
41. **for** (**int** i = 0; i < xls.size(); i++) \{ 
42. // 创建一行 
43.  HSSFRow row = sheet.createRow(i + 1); 
44. // 得到要插入的每一条记录 
45.  xlsDto = xls.get(i); 
46. **for** (**int** colu = 0; colu <= 4; colu++) \{ 
47. // 在一行内循环 
48.  HSSFCell xh = row.createCell(0); 
49.  xh.setCellValue(xlsDto.getXh()); 
50.  HSSFCell xm = row.createCell(1); 
51.  xm.setCellValue(xlsDto.getXm()); 
52.  HSSFCell yxsmc = row.createCell(2); 
53.  yxsmc.setCellValue(xlsDto.getYxsmc()); 
54.  HSSFCell kcm = row.createCell(3); 
55.  kcm.setCellValue(xlsDto.getKcm()); 
56.  HSSFCell cj = row.createCell(4); 
57.  cj.setCellValue(xlsDto.getCj()); 
58. (xlsDto.getMessage()); 
59.  \} 
60.  \} 
61. // 创建文件输出流，准备输出电子表格 
62.  OutputStream out = **new** FileOutputStream("POI2Excel/pldrxkxxmb.xls"); 
63.  hwb.write(out); 
64.  out.close(); 
65.  System.out.println("数据库导出成功"); 
66.  \} 
67. 
68. \}

XlsDto .java类

//该类是一个实体类

1.  **public****class** XlsDto \{ 
2.  /\*\* 
3.   \* 选课号 
4.   \*/
5.  **private** Integer xkh; 
6.  /\*\* 
7.   \* 学号 
8.   \*/
9.  **private** String xh; 
10. /\*\* 
11.  \* 姓名 
12.  \*/
13. **private** String xm; 
14. /\*\* 
15.  \* 学院 
16.  \*/
17. **private** String yxsmc; 
18. /\*\* 
19.  \* 课程号 
20.  \*/
21. **private** Integer kch; 
22. /\*\* 
23.  \* 课程名 
24.  \*/
25. **private** String kcm; 
26. /\*\* 
27.  \* 成绩 
28.  \*/
29. **private****float** cj; 
30. **public** Integer getXkh() \{ 
31. **return** xkh; 
32.  \} 
33. **public****void** setXkh(Integer xkh) \{ 
34. **this**.xkh = xkh; 
35.  \} 
36. **public** String getXh() \{ 
37. **return** xh; 
38.  \} 
39. **public****void** setXh(String xh) \{ 
40. **this**.xh = xh; 
41.  \} 
42. **public** String getXm() \{ 
43. **return** xm; 
44.  \} 
45. **public****void** setXm(String xm) \{ 
46. **this**.xm = xm; 
47.  \} 
48. **public** String getYxsmc() \{ 
49. **return** yxsmc; 
50.  \} 
51. **public****void** setYxsmc(String yxsmc) \{ 
52. **this**.yxsmc = yxsmc; 
53.  \} 
54. **public** Integer getKch() \{ 
55. **return** kch; 
56.  \} 
57. **public****void** setKch(Integer kch) \{ 
58. **this**.kch = kch; 
59.  \} 
60. **public** String getKcm() \{ 
61. **return** kcm; 
62.  \} 
63. **public****void** setKcm(String kcm) \{ 
64. **this**.kcm = kcm; 
65.  \} 
66. **public****float** getCj() \{ 
67. **return** cj; 
68.  \} 
69. **public****void** setCj(**float** cj) \{ 
70. **this**.cj = cj; 
71.  \} 
72. 
73. \}

**后台输出：**

数据库导出成功

[java_poi_excel]: /images/20220530/6688b99525284e5a8345fc24cc409323.png
[xls]: /images/20220530/38001ff672054c6385391e04442d0336.png