POI实战-java开发excel详解之简单写出

1.4 POI简单写出Excel

代码部分：

[java]  view plain copy

1. package com.vintage.testpoi;  
2.   
3. import java.io.File;  
4. import java.io.FileNotFoundException;  
5. import java.io.FileOutputStream;  
6. import java.io.IOException;  
7. import java.io.OutputStream;  
8.   
9. import org.apache.poi.hssf.usermodel.HSSFCell;  
10. import org.apache.poi.hssf.usermodel.HSSFRichTextString;  
11. import org.apache.poi.hssf.usermodel.HSSFRow;  
12. import org.apache.poi.hssf.usermodel.HSSFSheet;  
13. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
14.   
15.   
16. /** 
17.  * POI入门 ：简单写出excel数据 
18.  * @author VintageYu 
19.  * 
20.  */  
21. public class WriteExcelTest {  
22.       
23.     public static void write(OutputStream outputStream) throws IOException{  
24.         //初始一个workbook  
25.         HSSFWorkbook workbook = new HSSFWorkbook();  
26.         //创建一个表  
27.         HSSFSheet sheet = workbook.createSheet("firstSheet");  
28.         //创建行  
29.         HSSFRow row = sheet.createRow(0);  
30.         //创建单元格  
31.         HSSFCell cell = row.createCell(0);  
32.         cell.setCellValue(new HSSFRichTextString("hello POI"));  
33.         workbook.write(outputStream);  
34.     }  
35.       
36.     public static void main(String[] args) {  
37.         OutputStream outputStream = null;  
38.         try {  
39.             outputStream = new FileOutputStream(new File("E:\\helloPOI.xls"));  
40.             write(outputStream);  
41.         } catch (FileNotFoundException e) {  
42.             e.printStackTrace();  
43.         } catch (IOException e) {  
44.             e.printStackTrace();  
45.         }finally{  
46.             if(outputStream != null){  
47.                 try {  
48.                     outputStream.close();  
49.                 } catch (IOException e) {  
50.                     e.printStackTrace();  
51.                 }  
52.             }  
53.         }  
54.     }  
55.   
56. }  

图4

 

以下贴近实际进一步修改：

从数据库中读出相关信息并写入到Excel中

数据库表如图：

图5

[java]  view plain copy

1. 主要类：  
2. package com.vintage.testpoi;  
3.   
4. import java.io.File;  
5. import java.io.FileNotFoundException;  
6. import java.io.FileOutputStream;  
7. import java.io.IOException;  
8. import java.io.OutputStream;  
9. import java.util.List;  
10.   
11. import org.apache.poi.hssf.usermodel.HSSFCell;  
12. import org.apache.poi.hssf.usermodel.HSSFRow;  
13. import org.apache.poi.hssf.usermodel.HSSFSheet;  
14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
15.   
16.   
17. /** 
18.  * POI入门 ：简单写出excel数据 
19.  * @author VintageYu 
20.  * 
21.  */  
22. public class WriteExcelTest {  
23.       
24.     public static void write(OutputStream outputStream) throws IOException{  
25.         //初始一个workbook  
26.         HSSFWorkbook workbook = new HSSFWorkbook();  
27.         List<Student> list = Conn.getData();  
28.         //循环创建多个sheet  
29.         for(int sheetIndex = 0; sheetIndex < 3; sheetIndex++){  
30.             HSSFSheet sheet = workbook.createSheet("sheet"+sheetIndex);  
31.             //创建多行  
32.             for(int rowIndex = 0; rowIndex < list.size(); rowIndex++){  
33.                 HSSFRow row = sheet.createRow(rowIndex);  
34.                 Student student = list.get(rowIndex);  
35.                 //创建多列  
36.                 for(int cellnum = 0; cellnum < 4; cellnum++){  
37.                     HSSFCell cell = row.createCell(cellnum);  
38.                     switch (cellnum) {  
39.                     case 0:  
40.                         cell.setCellValue(student.getName());  
41.                         break;  
42.                     case 1:  
43.                         cell.setCellValue(student.getNo());  
44.                         break;  
45.                     case 2:  
46.                         cell.setCellValue(student.getNativePlace());  
47.                         break;  
48.                     case 3:  
49.                         cell.setCellValue(student.getEdu());  
50.                         break;  
51.                     }  
52.                 }  
53.             }  
54.         }  
55.         workbook.write(outputStream);  
56.     }  
57.       
58.     public static void main(String[] args) {  
59.         OutputStream outputStream = null;  
60.         try {  
61.             outputStream = new FileOutputStream(new File("E:\\helloPOI.xls"));  
62.             write(outputStream);  
63.         } catch (FileNotFoundException e) {  
64.             e.printStackTrace();  
65.         } catch (IOException e) {  
66.             e.printStackTrace();  
67.         }finally{  
68.             if(outputStream != null){  
69.                 try {  
70.                     outputStream.close();  
71.                 } catch (IOException e) {  
72.                     e.printStackTrace();  
73.                 }  
74.             }  
75.         }  
76.           
77.     }  
78.   
79. }  

依赖类：

[java]  view plain copy

1. public class Conn {  
2.       
3.     public static Connection getConn(){  
4.         Connection cn = null;  
5.         try {  
6.             Class.forName( "oracle.jdbc.driver.OracleDriver" );  
7.             cn = DriverManager.getConnection( "jdbc:oracle:thin:@****:1521:***", "***", "***");  
8.         } catch (ClassNotFoundException e) {  
9.             e.printStackTrace();  
10.         } catch (SQLException e) {  
11.             e.printStackTrace();  
12.         }  
13.         return cn;  
14.     }  
15.       
16.     public static List<Student> getData(){  
17.         List<Student> list = new ArrayList<Student>();  
18.         Connection conn = getConn();  
19.         String sql = "SELECT * FROM tpoi_vintage t";  
20.         try {  
21.             conn.setAutoCommit(false);  
22.             PreparedStatement ps = conn.prepareStatement(sql);  
23.             ResultSet rs = ps.executeQuery();  
24.             while(rs.next()){  
25.                 Integer id = rs.getInt("id");  
26.                 String name = rs.getString("name");  
27.                 String no = rs.getString("no");  
28.                 String nativePlace = rs.getString("native");  
29.                 String edu = rs.getString("edu");  
30.                 Integer year = rs.getInt("YEAR");  
31.                 Integer math = rs.getInt("MATH");  
32.                 Integer chinese = rs.getInt("CHINESE");  
33.                 Integer english = rs.getInt("ENGLISH");  
34.                 Integer science = rs.getInt("SCIENCE");  
35.                 Integer isCity = rs.getInt("ISCITY");  
36.                 Date schoolDate = rs.getDate("SCHOOLDATE");  
37.                 Date birth = rs.getDate("BIRTH");  
38.                   
39.                 Student student = new Student();  
40.                 student.setId(id);  
41.                 student.setName(name);  
42.                 student.setNo(no);  
43.                 student.setNativePlace(nativePlace);  
44.                 student.setEdu(edu);  
45.                 student.setYear(year);  
46.                 student.setMath(math);  
47.                 student.setChinese(chinese);  
48.                 student.setEnglish(english);  
49.                 student.setScience(science);  
50.                 if(isCity == 0){  
51.                     student.setCity(false);  
52.                 }else if(isCity == 1){  
53.                     student.setCity(true);  
54.                 }  
55.                 student.setSchoolDate(schoolDate);  
56.                 student.setBirth(birth);  
57.                   
58.                 list.add(student);  
59.                   
60.             }  
61.             conn.commit();  
62.         } catch (SQLException e) {  
63.             // TODO Auto-generated catch block  
64.             e.printStackTrace();  
65.         }  
66.           
67.         return list;  
68.     }  
69.   
70. public class Student {  
71.     private Integer id;  
72.     private String name;  
73.     private String no;  
74.     private String nativePlace;  
75.     private String edu;  
76. 以下省略。。。  

写出Excel预览:

图6