cool hit counter poi technology for java read, write excel [2003-2007,2010]_Intefrankly

poi technology for java read, write excel [2003-2007,2010]


In the last blog post. poi technology for java to read Excel [2003-2007,2010] The operations related to reading excel with poi technology in java are described in

Read excel and MySQL related. java's poi technique to read Excel data to MySQL

You can also find it at. java's poi technology to read and import Excel Get information on how to write to Excel

Use of JXL technology. java's jxl technique to import Excel

This article is about poi read and write excel in java (version is 2003-2007,2010)

Project structure.

The excel used

Running effect.

Processing...lib/student_info.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.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

=================================================

Source code section.

=================================================

/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      *  student number
19      */
20     private String no;
21     /**
22      *  name and surname
23      */
24     private String name;
25     /**
26      *  academy
27      */
28     private String age;
29     /**
30      *  performance records
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 }

Source Code Download.http://pan.baidu.com/s/1eQpEf0u

E | hongtenzone@foxmail.com B |http://www.cnblogs.com/hongten


Recommended>>
1、What the hell SQL can actually solve brain teaser questions
2、LintCodeN Queens Problem II Topic Code
3、Git Quick Reference Manual for Common Commands
4、State of objects in hibernate
5、Java Concurrency JUC Concurrency Tool Class Semaphore

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号