在这之前写过关于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
E | hongtenzone@foxmail.com B | http://www.cnblogs.com/hongten
========================================================
原标题:java的poi技术写Excel的Sheet
关键词:JAVA