java的poi技术读，写Excel[2003-2007,2010]
java的poi技术读，写Excel[2003-2007,2010]
在上一篇blog：java的poi技术读取Excel[2003-2007,2010] 中介绍了关于java中的poi技术读取excel的相关操作
读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL
你也可以在 ： java的poi技术读取和导入Excel 了解到写入Excel的方法信息
使用JXL技术 ： java的jxl技术导入Excel
本文主要讲的是java中poi读和写excel（版本是2003-2007,2010）
项目结构：
所用到的excel
运行效果：
Processing...lib/student_info.xlsNo. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
Processing...lib/student_info.xlsx
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
write data to file : lib/student_info_2003-2007.xls
write data to file : lib/student_info_2010.xlsx
======================================
Processing...lib/student_info_2003-2007.xls
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
======================================
Processing...lib/student_info_2010.xlsx
No. : 201401, name : Hongten, age : 22.0, score : 98.0
No. : 201402, name : Hanyuan, age : 21.0, score : 96.0
No. : 201403, name : Tom, age : 20.0, score : 94.0
No. : 201404, name : HanKet, age : 21.0, score : 90.0
No. : 201405, name : Jone, age : 22.0, score : 42.0
No. : 201406, name : Kite, age : 23.0, score : 59.0
No. : 201407, name : Lucy, age : 25.0, score : 60.0
No. : 201408, name : Mark, age : 23.0, score : 89.0
No. : 201409, name : Jeson, age : 24.0, score : 79.0
No. : 201410, name : Steven, age : 23.0, score : 59.0
No. : 201411, name : Tide, age : 22.0, score : 88.0
No. : 201412, name : Deli, age : 22.0, score : 89.0
No. : 201413, name : Goerme, age : 21.0, score : 98.0
No. : 201414, name : Som, age : 23.0, score : 95.0
=================================================
源码部分：
=================================================
/Excel2010/src/com/b510/excel/client/Client.java
1 /**2 *
3 */
4 package com.b510.excel.client;
5
6 import java.util.List;
7
8 import com.b510.excel.common.Common;
9 import com.b510.excel.util.ExcelUtil;
10 import com.b510.excel.vo.Student;
11
12 /**
13 * @author Hongten
14 * @created 2014-5-21
15 */
16 public class Client {
17
18 public static void main(String[] args) throws Exception {
19 String read_excel2003_2007_path = Common.STUDENT_INFO_XLS_PATH;
20 String read_excel2010_path = Common.STUDENT_INFO_XLSX_PATH;
21 // read the 2003-2007 excel
22 List<Student> list = new ExcelUtil().readExcel(read_excel2003_2007_path);
23 if (list != null) {
24 for (Student student : list) {
25 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
26 }
27 }
28 System.out.println("======================================");
29 // read the 2010 excel
30 List<Student> list1 = new ExcelUtil().readExcel(read_excel2010_path);
31 if (list1 != null) {
32 for (Student student : list1) {
33 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
34 }
35 }
36 System.out.println("======================================");
37 String write_excel2003_2007_path = Common.STUDENT_INFO_XLS_OUT_PATH;
38 String write_excel2010_path = Common.STUDENT_INFO_XLSX_OUT_PATH;
39 new ExcelUtil().writeExcel(list, write_excel2003_2007_path);
40 new ExcelUtil().writeExcel(list, write_excel2010_path);
41 System.out.println("======================================");
42
43 // read the 2003-2007 excel
44 List<Student> list2 = new ExcelUtil().readExcel(write_excel2003_2007_path);
45 if (list != null) {
46 for (Student student : list2) {
47 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
48 }
49 }
50 System.out.println("======================================");
51 // read the 2010 excel
52 List<Student> list3 = new ExcelUtil().readExcel(write_excel2010_path);
53 if (list1 != null) {
54 for (Student student : list3) {
55 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
56 }
57 }
58 }
59 }
/Excel2010/src/com/b510/excel/common/Common.java
1 /**2 *
3 */
4 package com.b510.excel.common;
5
6 /**
7 * @author Hongten
8 * @created 2014-5-21
9 */
10 public class Common {
11
12 public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
13 public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
14
15 public static final String EMPTY = "";
16 public static final String POINT = ".";
17 public static final String LIB_PATH = "lib";
18 public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
19 public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
20 public static final String STUDENT_INFO_XLS_OUT_PATH = "lib/student_info_2003-2007.xls";
21 public static final String STUDENT_INFO_XLSX_OUT_PATH = "lib/student_info_2010.xlsx";
22 public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
23 public static final String PROCESSING = "Processing...";
24 public static final String WRITE_DATA = "write data to file : ";
25
26 }
/Excel2010/src/com/b510/excel/util/ExcelUtil.java
1 /**2 *
3 */
4 package com.b510.excel.util;
5
6 import java.io.File;
7 import java.io.FileInputStream;
8 import java.io.FileOutputStream;
9 import java.io.IOException;
10 import java.io.InputStream;
11 import java.io.OutputStream;
12 import java.util.ArrayList;
13 import java.util.List;
14
15 import org.apache.poi.hssf.usermodel.HSSFCell;
16 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
17 import org.apache.poi.hssf.usermodel.HSSFRow;
18 import org.apache.poi.hssf.usermodel.HSSFSheet;
19 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
20 import org.apache.poi.xssf.usermodel.XSSFCell;
21 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
22 import org.apache.poi.xssf.usermodel.XSSFRow;
23 import org.apache.poi.xssf.usermodel.XSSFSheet;
24 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
25
26 import com.b510.excel.common.Common;
27 import com.b510.excel.vo.Student;
28
29 /**
30 * @author Hongten
31 * @created 2014-5-20
32 */
33 public class ExcelUtil {
34
35 public void writeExcel(List<Student> list, String path) throws Exception {
36 if (list == null) {
37 return;
38 } else if (path == null || Common.EMPTY.equals(path)) {
39 return;
40 } else {
41 String postfix = Util.getPostfix(path);
42 if (!Common.EMPTY.equals(postfix)) {
43 if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
44 writeXls(list, path);
45 } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
46 writeXlsx(list, path);
47 }
48 }else{
49 System.out.println(path + Common.NOT_EXCEL_FILE);
50 }
51 }
52 }
53
54 /**
55 * read the Excel file
56 * @param path the path of the Excel file
57 * @return
58 * @throws IOException
59 */
60 public List<Student> readExcel(String path) throws IOException {
61 if (path == null || Common.EMPTY.equals(path)) {
62 return null;
63 } else {
64 String postfix = Util.getPostfix(path);
65 if (!Common.EMPTY.equals(postfix)) {
66 if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
67 return readXls(path);
68 } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
69 return readXlsx(path);
70 }
71 } else {
72 System.out.println(path + Common.NOT_EXCEL_FILE);
73 }
74 }
75 return null;
76 }
77
78 /**
79 * Read the Excel 2010
80 * @param path the path of the excel file
81 * @return
82 * @throws IOException
83 */
84 public List<Student> readXlsx(String path) throws IOException {
85 System.out.println(Common.PROCESSING + path);
86 InputStream is = new FileInputStream(path);
87 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
88 Student student = null;
89 List<Student> list = new ArrayList<Student>();
90 // Read the Sheet
91 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
92 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
93 if (xssfSheet == null) {
94 continue;
95 }
96 // Read the Row
97 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
98 XSSFRow xssfRow = xssfSheet.getRow(rowNum);
99 if (xssfRow != null) {
100 student = new Student();
101 XSSFCell no = xssfRow.getCell(0);
102 XSSFCell name = xssfRow.getCell(1);
103 XSSFCell age = xssfRow.getCell(2);
104 XSSFCell score = xssfRow.getCell(3);
105 student.setNo(getValue(no));
106 student.setName(getValue(name));
107 student.setAge(getValue(age));
108 student.setScore(Float.valueOf(getValue(score)));
109 list.add(student);
110 }
111 }
112 }
113 return list;
114 }
115
116 /**
117 * Read the Excel 2003-2007
118 * @param path the path of the Excel
119 * @return
120 * @throws IOException
121 */
122 public List<Student> readXls(String path) throws IOException {
123 System.out.println(Common.PROCESSING + path);
124 InputStream is = new FileInputStream(path);
125 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
126 Student student = null;
127 List<Student> list = new ArrayList<Student>();
128 // Read the Sheet
129 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
130 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
131 if (hssfSheet == null) {
132 continue;
133 }
134 // Read the Row
135 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
136 HSSFRow hssfRow = hssfSheet.getRow(rowNum);
137 if (hssfRow != null) {
138 student = new Student();
139 HSSFCell no = hssfRow.getCell(0);
140 HSSFCell name = hssfRow.getCell(1);
141 HSSFCell age = hssfRow.getCell(2);
142 HSSFCell score = hssfRow.getCell(3);
143 student.setNo(getValue(no));
144 student.setName(getValue(name));
145 student.setAge(getValue(age));
146 student.setScore(Float.valueOf(getValue(score)));
147 list.add(student);
148 }
149 }
150 }
151 return list;
152 }
153
154 @SuppressWarnings("static-access")
155 private String getValue(XSSFCell xssfRow) {
156 if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
157 return String.valueOf(xssfRow.getBooleanCellValue());
158 } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
159 return String.valueOf(xssfRow.getNumericCellValue());
160 } else {
161 return String.valueOf(xssfRow.getStringCellValue());
162 }
163 }
164
165 @SuppressWarnings("static-access")
166 private String getValue(HSSFCell hssfCell) {
167 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
168 return String.valueOf(hssfCell.getBooleanCellValue());
169 } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
170 return String.valueOf(hssfCell.getNumericCellValue());
171 } else {
172 return String.valueOf(hssfCell.getStringCellValue());
173 }
174 }
175
176 public void writeXls(List<Student> list, String path) throws Exception {
177 if (list == null) {
178 return;
179 }
180 int countColumnNum = list.size();
181 HSSFWorkbook book = new HSSFWorkbook();
182 HSSFSheet sheet = book.createSheet("studentSheet");
183 // option at first row.
184 HSSFRow firstRow = sheet.createRow(0);
185 HSSFCell[] firstCells = new HSSFCell[countColumnNum];
186 String[] options = { "no", "name", "age", "score" };
187 for (int j = 0; j < options.length; j++) {
188 firstCells[j] = firstRow.createCell(j);
189 firstCells[j].setCellValue(new HSSFRichTextString(options[j]));
190 }
191 //
192 for (int i = 0; i < countColumnNum; i++) {
193 HSSFRow row = sheet.createRow(i + 1);
194 Student student = list.get(i);
195 for (int column = 0; column < options.length; column++) {
196 HSSFCell no = row.createCell(0);
197 HSSFCell name = row.createCell(1);
198 HSSFCell age = row.createCell(2);
199 HSSFCell score = row.createCell(3);
200 no.setCellValue(student.getNo());
201 name.setCellValue(student.getName());
202 age.setCellValue(student.getAge());
203 score.setCellValue(student.getScore());
204 }
205 }
206 File file = new File(path);
207 OutputStream os = new FileOutputStream(file);
208 System.out.println(Common.WRITE_DATA + path);
209 book.write(os);
210 os.close();
211 }
212
213 public void writeXlsx(List<Student> list, String path) throws Exception {
214 if (list == null) {
215 return;
216 }
217 //XSSFWorkbook
218 int countColumnNum = list.size();
219 XSSFWorkbook book = new XSSFWorkbook();
220 XSSFSheet sheet = book.createSheet("studentSheet");
221 // option at first row.
222 XSSFRow firstRow = sheet.createRow(0);
223 XSSFCell[] firstCells = new XSSFCell[countColumnNum];
224 String[] options = { "no", "name", "age", "score" };
225 for (int j = 0; j < options.length; j++) {
226 firstCells[j] = firstRow.createCell(j);
227 firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
228 }
229 //
230 for (int i = 0; i < countColumnNum; i++) {
231 XSSFRow row = sheet.createRow(i + 1);
232 Student student = list.get(i);
233 for (int column = 0; column < options.length; column++) {
234 XSSFCell no = row.createCell(0);
235 XSSFCell name = row.createCell(1);
236 XSSFCell age = row.createCell(2);
237 XSSFCell score = row.createCell(3);
238 no.setCellValue(student.getNo());
239 name.setCellValue(student.getName());
240 age.setCellValue(student.getAge());
241 score.setCellValue(student.getScore());
242 }
243 }
244 File file = new File(path);
245 OutputStream os = new FileOutputStream(file);
246 System.out.println(Common.WRITE_DATA + path);
247 book.write(os);
248 os.close();
249 }
250 }
/Excel2010/src/com/b510/excel/util/Util.java
1 /**2 *
3 */
4 package com.b510.excel.util;
5
6 import com.b510.excel.common.Common;
7
8 /**
9 * @author Hongten
10 * @created 2014-5-21
11 */
12 public class Util {
13
14 /**
15 * get postfix of the path
16 * @param path
17 * @return
18 */
19 public static String getPostfix(String path) {
20 if (path == null || Common.EMPTY.equals(path.trim())) {
21 return Common.EMPTY;
22 }
23 if (path.contains(Common.POINT)) {
24 return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
25 }
26 return Common.EMPTY;
27 }
28 }
/Excel2010/src/com/b510/excel/vo/Student.java
1 /**2 *
3 */
4 package com.b510.excel.vo;
5
6 /**
7 * Student
8 *
9 * @author Hongten
10 * @created 2014-5-18
11 */
12 public class Student {
13 /**
14 * id
15 */
16 private Integer id;
17 /**
18 * 学号
19 */
20 private String no;
21 /**
22 * 姓名
23 */
24 private String name;
25 /**
26 * 学院
27 */
28 private String age;
29 /**
30 * 成绩
31 */
32 private float score;
33
34 public Integer getId() {
35 return id;
36 }
37
38 public void setId(Integer id) {
39 this.id = id;
40 }
41
42 public String getNo() {
43 return no;
44 }
45
46 public void setNo(String no) {
47 this.no = no;
48 }
49
50 public String getName() {
51 return name;
52 }
53
54 public void setName(String name) {
55 this.name = name;
56 }
57
58 public String getAge() {
59 return age;
60 }
61
62 public void setAge(String age) {
63 this.age = age;
64 }
65
66 public float getScore() {
67 return score;
68 }
69
70 public void setScore(float score) {
71 this.score = score;
72 }
73
74 }
源码下载：http://pan.baidu.com/s/1eQpEf0u
========================================================
More reading,and english is important.
I\'m Hongten
大哥哥大姐姐，觉得有用打赏点哦！多多少少没关系，一分也是对我的支持和鼓励。谢谢。
Hongten博客排名在100名以内。粉丝过千。
Hongten出品，必是精品。
E | hongtenzone@foxmail.com B | http://www.cnblogs.com/hongten
========================================================
以上是 java的poi技术读，写Excel[2003-2007,2010] 的全部内容， 来源链接： utcz.com/z/391103.html
