你的位置:首页 > Java教程

[Java教程]java的poi技术写Excel的Sheet

在这之前写过关于java读,写Excel的blog如下:

Excel转Html

java的poi技术读,写Excel[2003-2007,2010]

java的poi技术读取Excel[2003-2007,2010]

java的poi技术读取Excel数据到MySQL

java的jxl技术导入Excel

java的poi技术读取和导入Excel

 

然而,这篇blog主要内容是关于Excel里面怎样去写Sheet数据。

那么在Excel里面什么叫做Sheet呢?如下图红色框里面的内容就是Excel的Sheet了。

我们需要知道怎样创建一个Sheet,下面是一个Sample:

 1 Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); 2 Sheet sheet1 = wb.createSheet("new sheet"); 3 Sheet sheet2 = wb.createSheet("second sheet"); 4  5  6 // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} 7 // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') 8 String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales  " 9 Sheet sheet3 = wb.createSheet(safeName);10 11 FileOutputStream fileOut = new FileOutputStream("workbook.xls");12 wb.write(fileOut);13 fileOut.close();

看了以后,会不会觉得So easy!这是因为Apache的poi给我们封装了很多方法,提供了很好的接口。

下面是我做的一个Demo,这个Demo的数据流如下:

MySQL数据库 -- > Demo 程序 -- > Excel 文件

我们的Demo程序会从MySQL数据库中读取数据,然后把数据写入到Excel文件中。

项目结构:

注意:红色框里面的jar包,你在下载源码后,这些jar包不会存放到源码里面,需要手动下载!

在MySQL数据库中,我们会用到两张表: t_school, t_student.

 1 -- Table "t_school" DDL 2  3 CREATE TABLE `t_school` ( 4  `no` int(16) NOT NULL AUTO_INCREMENT, 5  `name` varchar(50) COLLATE utf8_bin NOT NULL, 6  `desc` varchar(500) COLLATE utf8_bin DEFAULT NULL, 7  `ranking` int(3) DEFAULT NULL, 8  `address` varchar(200) COLLATE utf8_bin DEFAULT NULL, 9  PRIMARY KEY (`no`)10 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;11 12 -- Table "t_student" DDL13 14 CREATE TABLE `t_student` (15  `student_no` int(16) NOT NULL AUTO_INCREMENT,16  `school_no` int(16) NOT NULL,17  `name` varchar(100) COLLATE utf8_bin DEFAULT NULL,18  `address` varchar(200) COLLATE utf8_bin DEFAULT NULL,19  `birthdate` varchar(15) COLLATE utf8_bin DEFAULT NULL,20  `phone` varchar(15) COLLATE utf8_bin DEFAULT NULL,21  PRIMARY KEY (`student_no`,`school_no`),22  KEY `school_no` (`school_no`),23  CONSTRAINT `school_no` FOREIGN KEY (`school_no`) REFERENCES `t_school` (`no`)24 ) ENGINE=InnoDB AUTO_INCREMENT=100000023 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

在Excel文件中,我们要做的是在'School Summary' Sheet里面写入School的信息,而对于其他的Sheet,我们会把Shool的名称命名为Sheet的Name,然后在每一个Sheet里面写入在这个Shool里面的Student的信息。

两张表里面数据,大家可以通过blog末尾的下载链接获得。

---------------------------------------------

                    代码部分

---------------------------------------------

/ExcelHandler/src/com/b510/hongten/client/Client.java

 1 package com.b510.hongten.client; 2  3 import java.util.List; 4  5 import com.b510.hongten.db.SchoolDAO; 6 import com.b510.hongten.excel.WriteExcel; 7 import com.b510.hongten.vo.School; 8  9 /**10  * @author hongten11  * @created Jun 16, 201612 */13 public class Client {14 15   public static void main(String[] args) {16     List<School> schools = SchoolDAO.getSchools();17     WriteExcel writeExcel = new WriteExcel();18     writeExcel.writeExcel(schools);19   }20 21 }

/ExcelHandler/src/com/b510/hongten/common/Common.java

 1 package com.b510.hongten.common; 2  3 /** 4  * @author hongten 5  * @created Jun 16, 2016 6 */ 7 public class Common { 8  9   // MySQL database connection configuration, you could write in *.properties10   // file as also. For this demo, we write this configuration in this class11   // file. By the way, I don't recommend this way ^_^.12   public static String URL = "jdbc:mysql://localhost:3306/school";13   public static String USER_NAME = "root";14   public static String PASSWORD = "password1";15   16   public static String TARGET_FILE_PATH = "lib/excel_resources/school.xls";17 18   public static String SUMMARY = "Shool Summary";19   public static String[] TITLES = { "S/N", "Name", "Description", "Ranking", "Address" };20   public static String[] STUDENT_TITLE = { "S/N", "Name", "Birth Date", "Phone", "Address" };21 }

/ExcelHandler/src/com/b510/hongten/db/ConnectionUtil.java

 1 package com.b510.hongten.db; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6  7 import org.apache.log4j.Logger; 8  9 import com.b510.hongten.common.Common;10 11 /**12  * @author hongten13  * @created Jun 16, 201614 */15 public class ConnectionUtil {16 17   static Logger logger = Logger.getLogger(ConnectionUtil.class);18 19   public static Connection getConn() {20     logger.debug("-------- MySQL JDBC Connection Testing ------------");21 22     Connection connection = null;23     try {24       Class.forName("com.mysql.jdbc.Driver");25     } catch (ClassNotFoundException e) {26       logger.error("Where is your MySQL JDBC Driver?");27       e.printStackTrace();28     }29 30     logger.info("MySQL JDBC Driver Registered!");31 32     try {33       connection = DriverManager.getConnection(Common.URL, Common.USER_NAME, Common.PASSWORD);34       if(connection != null){35         logger.info("connecte successfully!");36       }37     } catch (SQLException e) {38       logger.error("Connection Failed! Check output console");39       e.printStackTrace();40     }41     return connection;42 43   }44 45   public static void closeConn(Connection conn) {46     if (conn != null) {47       try {48         logger.info("closing connection begin!");49         conn.close();50         logger.info("closing connection end!");51       } catch (SQLException e) {52         e.printStackTrace();53       }54     } else {55       logger.info("connection is not null!");56     }57   }58 }

/ExcelHandler/src/com/b510/hongten/db/SchoolDAO.java

 1 package com.b510.hongten.db; 2  3 import java.sql.Connection; 4 import java.sql.ResultSet; 5 import java.sql.SQLException; 6 import java.util.ArrayList; 7 import java.util.List; 8  9 import org.apache.log4j.Logger;10 11 import com.b510.hongten.vo.School;12 import com.b510.hongten.vo.Student;13 import com.mysql.jdbc.PreparedStatement;14 15 /**16  * @author hongten17  * @created Jun 16, 201618 */19 public class SchoolDAO {20 21   static Logger logger = Logger.getLogger(SchoolDAO.class);22 23   public static List<School> getSchools() {24     Connection conn = null;25     PreparedStatement preparedStatement = null, preStat = null;26     ResultSet rs = null, rs_student = null;27     List<School> schools = new ArrayList<School>();28 29     try {30       conn = ConnectionUtil.getConn();31       String sql = "select * from t_school order by no";32       preparedStatement = (PreparedStatement) conn.prepareStatement(sql);33       rs = preparedStatement.executeQuery();34       while (rs.next()) {35         int school_no = rs.getInt(1);36         if (school_no > 0) {37           School school = new School();38           school.setNo(school_no);39           school.setName(rs.getString("name"));40           school.setAddrss(rs.getString("address"));41           school.setDesc(rs.getString("desc"));42           school.setRanking(rs.getString("ranking"));43 44           String studentSQL = "select * from t_student where school_no = ? ";45           preStat = (PreparedStatement) conn.prepareStatement(studentSQL);46           preStat.setInt(1, school_no);47           rs_student = preStat.executeQuery();48           List<Student> students = new ArrayList<>();49           while (rs_student.next()) {50             Student student = new Student();51             int std_no = rs_student.getInt(1);52             student.setStudentNo(std_no);53             student.setName(rs_student.getString("name"));54             student.setBirthdate(rs_student.getString("birthdate"));55             student.setPhone(rs_student.getString("phone"));56             student.setAddress(rs.getString("address"));57             students.add(student);58           }59           school.setStudents(students);60           schools.add(school);61         }62       }63     } catch (SQLException e) {64       e.printStackTrace();65       logger.error(e.getMessage());66     } finally {67       if (rs != null) {68         try {69           rs.close();70         } catch (SQLException e) {71           e.printStackTrace();72         }73       }74       if (preparedStatement != null) {75         try {76           preparedStatement.close();77         } catch (SQLException e) {78           e.printStackTrace();79         }80       }81       ConnectionUtil.closeConn(conn);82     }83     return schools;84   }85 }

/ExcelHandler/src/com/b510/hongten/excel/WriteExcel.java

 1 package com.b510.hongten.excel; 2  3 import java.io.FileNotFoundException; 4 import java.io.FileOutputStream; 5 import java.io.IOException; 6 import java.util.List; 7  8 import org.apache.log4j.Logger; 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 10 import org.apache.poi.ss.usermodel.Cell; 11 import org.apache.poi.ss.usermodel.Row; 12 import org.apache.poi.ss.usermodel.Sheet; 13 import org.apache.poi.ss.usermodel.Workbook; 14  15 import com.b510.hongten.common.Common; 16 import com.b510.hongten.vo.School; 17 import com.b510.hongten.vo.Student; 18  19 /** 20  * @author hongten 21  * @created Jun 13, 2016 22 */ 23 public class WriteExcel { 24  25   static Logger logger = Logger.getLogger(WriteExcel.class); 26  27   public void writeExcel(List<School> schools) { 28     if (schools == null || schools.size() == 0) { 29       return; 30     } 31     FileOutputStream fileOut = null; 32     Workbook wb = new HSSFWorkbook(); 33     Sheet shool_sheet = wb.createSheet(Common.SUMMARY); 34     shool_sheet.setAutobreaks(true); 35     // Create a row and put some cells in it. Rows are 0 based. 36     Row row = shool_sheet.createRow(0); 37     String[] titles = Common.TITLES; 38     int num = 0; 39     for (String title : titles) { 40       // Create a cell 41       Cell cell = row.createCell(num++); 42       cell.setCellValue(title); 43     } 44     int rowNum = 1; 45     for (School school : schools) { 46       row = shool_sheet.createRow(rowNum++); 47       Cell cell = row.createCell(0); 48       cell.setCellValue(school.getNo()); 49       cell = row.createCell(1); 50       cell.setCellValue(school.getName()); 51       cell = row.createCell(2); 52       cell.setCellValue(school.getDesc()); 53       cell = row.createCell(3); 54       cell.setCellValue(school.getRanking()); 55       cell = row.createCell(4); 56       cell.setCellValue(school.getAddrss()); 57  58       List<Student> students = school.getStudents(); 59       if (students != null && students.size() > 0) { 60         Sheet student_sheet = wb.createSheet(school.getName()); 61         student_sheet.setAutobreaks(true); 62         // Create a row and put some cells in it. Rows are 0 based. 63         Row student_row = student_sheet.createRow(0); 64         String[] student_titles = Common.STUDENT_TITLE; 65         num = 0; 66         for (String title : student_titles) { 67           // Create a cell 68           Cell student_cell = student_row.createCell(num++); 69           student_cell.setCellValue(title); 70         } 71         int stuRowNum = 1; 72         for (Student student : students) { 73           student_row = student_sheet.createRow(stuRowNum++); 74           Cell student_cell = student_row.createCell(0); 75           student_cell.setCellValue(student.getStudentNo()); 76           student_cell = student_row.createCell(1); 77           student_cell.setCellValue(student.getName()); 78           student_cell = student_row.createCell(2); 79           student_cell.setCellValue(student.getBirthdate()); 80           student_cell = student_row.createCell(3); 81           student_cell.setCellValue(student.getPhone()); 82           student_cell = student_row.createCell(4); 83           student_cell.setCellValue(student.getAddress()); 84         } 85       } 86     } 87  88     try { 89       fileOut = new FileOutputStream(Common.TARGET_FILE_PATH); 90     } catch (FileNotFoundException e) { 91       e.printStackTrace(); 92     } 93     try { 94       wb.write(fileOut); 95     } catch (IOException e1) { 96       e1.printStackTrace(); 97     } 98     try { 99       fileOut.close();100     } catch (IOException e) {101       e.printStackTrace();102     }103     logger.info("done");104   }105 106 }

/ExcelHandler/src/com/b510/hongten/vo/School.java

 1 package com.b510.hongten.vo; 2  3 import java.util.ArrayList; 4 import java.util.List; 5  6 /** 7  * @author hongten 8  * @created Jun 16, 2016 9 */10 public class School {11 12   private int no;13   private String name;14   private String desc;15   private String ranking;16   private String addrss;17 18   private List<Student> students = new ArrayList<Student>();19 20   public int getNo() {21     return no;22   }23 24   public void setNo(int no) {25     this.no = no;26   }27 28   public String getName() {29     return name;30   }31 32   public void setName(String name) {33     this.name = name;34   }35 36   public String getDesc() {37     return desc;38   }39 40   public void setDesc(String desc) {41     this.desc = desc;42   }43 44   public String getRanking() {45     return ranking;46   }47 48   public void setRanking(String ranking) {49     this.ranking = ranking;50   }51 52   public String getAddrss() {53     return addrss;54   }55 56   public void setAddrss(String addrss) {57     this.addrss = addrss;58   }59 60   public List<Student> getStudents() {61     return students;62   }63 64   public void setStudents(List<Student> students) {65     this.students = students;66   }67 68 }

/ExcelHandler/src/com/b510/hongten/vo/Student.java

 1 package com.b510.hongten.vo; 2  3 /** 4  * @author hongten 5  * @created Jun 16, 2016 6 */ 7 public class Student { 8  9   private int studentNo;10   private String name;11   private String address;12   private String birthdate;13   private String note;14   private String phone;15 16   public int getStudentNo() {17     return studentNo;18   }19 20   public void setStudentNo(int studentNo) {21     this.studentNo = studentNo;22   }23 24   public String getName() {25     return name;26   }27 28   public void setName(String name) {29     this.name = name;30   }31 32   public String getAddress() {33     return address;34   }35 36   public void setAddress(String address) {37     this.address = address;38   }39 40   public String getBirthdate() {41     return birthdate;42   }43 44   public void setBirthdate(String birthdate) {45     this.birthdate = birthdate;46   }47 48   public String getNote() {49     return note;50   }51 52   public void setNote(String note) {53     this.note = note;54   }55 56   public String getPhone() {57     return phone;58   }59 60   public void setPhone(String phone) {61     this.phone = phone;62   }63 64 }

/ExcelHandler/src/log4j.

 1 <??> 2 <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"> 3 <log4j:configuration debug="true" 4   ='http://jakarta.apache.org/log4j/'> 5  6   <appender name="console" class="org.apache.log4j.ConsoleAppender"> 7     <layout class="org.apache.log4j.PatternLayout"> 8     <param name="ConversionPattern"  9      value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />10     </layout>11   </appender>12 13   <root>14     <level value="DEBUG" />15     <appender-ref ref="console" />16   </root>17 </log4j:configuration>

 

源码下载:

http://files.cnblogs.com/files/hongten/ExcelHandler.rar

 

测试数据下载:

http://files.cnblogs.com/files/hongten/t_shool_and_t_student_data.rar

 

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

More reading,and english is important.

I'm Hongten

hongten

 

E | [email protected]  B | http://www.cnblogs.com/hongten

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