在这之前写过关于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：

Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");


// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
Sheet sheet3 = wb.createSheet(safeName);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

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

下面是我做的一个Demo，这个Demo的数据流如下：

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

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

项目结构：

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

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

-- Table "t_school" DDL

CREATE TABLE `t_school` (
  `no` int(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `desc` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `ranking` int(3) DEFAULT NULL,
  `address` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Table "t_student" DDL

CREATE TABLE `t_student` (
  `student_no` int(16) NOT NULL AUTO_INCREMENT,
  `school_no` int(16) NOT NULL,
  `name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `address` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `birthdate` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `phone` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`student_no`,`school_no`),
  KEY `school_no` (`school_no`),
  CONSTRAINT `school_no` FOREIGN KEY (`school_no`) REFERENCES `t_school` (`no`)
) 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

package com.b510.hongten.client;

import java.util.List;

import com.b510.hongten.db.SchoolDAO;
import com.b510.hongten.excel.WriteExcel;
import com.b510.hongten.vo.School;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class Client {

    public static void main(String[] args) {
        List<School> schools = SchoolDAO.getSchools();
        WriteExcel writeExcel = new WriteExcel();
        writeExcel.writeExcel(schools);
    }

}

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

package com.b510.hongten.common;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class Common {

    // MySQL database connection configuration, you could write in *.properties
    // file as also. For this demo, we write this configuration in this class
    // file. By the way, I don't recommend this way ^_^.
    public static String URL = "jdbc:mysql://localhost:3306/school";
    public static String USER_NAME = "root";
    public static String PASSWORD = "password1";
    
    public static String TARGET_FILE_PATH = "lib/excel_resources/school.xls";

    public static String SUMMARY = "Shool Summary";
    public static String[] TITLES = { "S/N", "Name", "Description", "Ranking", "Address" };
    public static String[] STUDENT_TITLE = { "S/N", "Name", "Birth Date", "Phone", "Address" };
}

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

package com.b510.hongten.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.b510.hongten.common.Common;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class ConnectionUtil {

    static Logger logger = Logger.getLogger(ConnectionUtil.class);

    public static Connection getConn() {
        logger.debug("-------- MySQL JDBC Connection Testing ------------");

        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            logger.error("Where is your MySQL JDBC Driver?");
            e.printStackTrace();
        }

        logger.info("MySQL JDBC Driver Registered!");

        try {
            connection = DriverManager.getConnection(Common.URL, Common.USER_NAME, Common.PASSWORD);
            if(connection != null){
                logger.info("connecte successfully!");
            }
        } catch (SQLException e) {
            logger.error("Connection Failed! Check output console");
            e.printStackTrace();
        }
        return connection;

    }

    public static void closeConn(Connection conn) {
        if (conn != null) {
            try {
                logger.info("closing connection begin!");
                conn.close();
                logger.info("closing connection end!");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            logger.info("connection is not null!");
        }
    }
}

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

package com.b510.hongten.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.b510.hongten.vo.School;
import com.b510.hongten.vo.Student;
import com.mysql.jdbc.PreparedStatement;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class SchoolDAO {

    static Logger logger = Logger.getLogger(SchoolDAO.class);

    public static List<School> getSchools() {
        Connection conn = null;
        PreparedStatement preparedStatement = null, preStat = null;
        ResultSet rs = null, rs_student = null;
        List<School> schools = new ArrayList<School>();

        try {
            conn = ConnectionUtil.getConn();
            String sql = "select * from t_school order by no";
            preparedStatement = (PreparedStatement) conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                int school_no = rs.getInt(1);
                if (school_no > 0) {
                    School school = new School();
                    school.setNo(school_no);
                    school.setName(rs.getString("name"));
                    school.setAddrss(rs.getString("address"));
                    school.setDesc(rs.getString("desc"));
                    school.setRanking(rs.getString("ranking"));

                    String studentSQL = "select * from t_student where school_no = ? ";
                    preStat = (PreparedStatement) conn.prepareStatement(studentSQL);
                    preStat.setInt(1, school_no);
                    rs_student = preStat.executeQuery();
                    List<Student> students = new ArrayList<>();
                    while (rs_student.next()) {
                        Student student = new Student();
                        int std_no = rs_student.getInt(1);
                        student.setStudentNo(std_no);
                        student.setName(rs_student.getString("name"));
                        student.setBirthdate(rs_student.getString("birthdate"));
                        student.setPhone(rs_student.getString("phone"));
                        student.setAddress(rs.getString("address"));
                        students.add(student);
                    }
                    school.setStudents(students);
                    schools.add(school);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e.getMessage());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            ConnectionUtil.closeConn(conn);
        }
        return schools;
    }
}

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

package com.b510.hongten.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.b510.hongten.common.Common;
import com.b510.hongten.vo.School;
import com.b510.hongten.vo.Student;

/**
 * @author hongten
 * @created Jun 13, 2016
 */
public class WriteExcel {

    static Logger logger = Logger.getLogger(WriteExcel.class);

    public void writeExcel(List<School> schools) {
        if (schools == null || schools.size() == 0) {
            return;
        }
        FileOutputStream fileOut = null;
        Workbook wb = new HSSFWorkbook();
        Sheet shool_sheet = wb.createSheet(Common.SUMMARY);
        shool_sheet.setAutobreaks(true);
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = shool_sheet.createRow(0);
        String[] titles = Common.TITLES;
        int num = 0;
        for (String title : titles) {
            // Create a cell
            Cell cell = row.createCell(num++);
            cell.setCellValue(title);
        }
        int rowNum = 1;
        for (School school : schools) {
            row = shool_sheet.createRow(rowNum++);
            Cell cell = row.createCell(0);
            cell.setCellValue(school.getNo());
            cell = row.createCell(1);
            cell.setCellValue(school.getName());
            cell = row.createCell(2);
            cell.setCellValue(school.getDesc());
            cell = row.createCell(3);
            cell.setCellValue(school.getRanking());
            cell = row.createCell(4);
            cell.setCellValue(school.getAddrss());

            List<Student> students = school.getStudents();
            if (students != null && students.size() > 0) {
                Sheet student_sheet = wb.createSheet(school.getName());
                student_sheet.setAutobreaks(true);
                // Create a row and put some cells in it. Rows are 0 based.
                Row student_row = student_sheet.createRow(0);
                String[] student_titles = Common.STUDENT_TITLE;
                num = 0;
                for (String title : student_titles) {
                    // Create a cell
                    Cell student_cell = student_row.createCell(num++);
                    student_cell.setCellValue(title);
                }
                int stuRowNum = 1;
                for (Student student : students) {
                    student_row = student_sheet.createRow(stuRowNum++);
                    Cell student_cell = student_row.createCell(0);
                    student_cell.setCellValue(student.getStudentNo());
                    student_cell = student_row.createCell(1);
                    student_cell.setCellValue(student.getName());
                    student_cell = student_row.createCell(2);
                    student_cell.setCellValue(student.getBirthdate());
                    student_cell = student_row.createCell(3);
                    student_cell.setCellValue(student.getPhone());
                    student_cell = student_row.createCell(4);
                    student_cell.setCellValue(student.getAddress());
                }
            }
        }

        try {
            fileOut = new FileOutputStream(Common.TARGET_FILE_PATH);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            wb.write(fileOut);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        try {
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        logger.info("done");
    }

}

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

package com.b510.hongten.vo;

import java.util.ArrayList;
import java.util.List;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class School {

    private int no;
    private String name;
    private String desc;
    private String ranking;
    private String addrss;

    private List<Student> students = new ArrayList<Student>();

    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

    public String getRanking() {
        return ranking;
    }

    public void setRanking(String ranking) {
        this.ranking = ranking;
    }

    public String getAddrss() {
        return addrss;
    }

    public void setAddrss(String addrss) {
        this.addrss = addrss;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

}

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

package com.b510.hongten.vo;

/**
 * @author hongten
 * @created Jun 16, 2016
 */
public class Student {

    private int studentNo;
    private String name;
    private String address;
    private String birthdate;
    private String note;
    private String phone;

    public int getStudentNo() {
        return studentNo;
    }

    public void setStudentNo(int studentNo) {
        this.studentNo = studentNo;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(String birthdate) {
        this.birthdate = birthdate;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

}

/ExcelHandler/src/log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration debug="true"
    xmlns:log4j='http://jakarta.apache.org/log4j/'>

    <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" 
          value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />
        </layout>
    </appender>

    <root>
        <level value="DEBUG" />
        <appender-ref ref="console" />
    </root>
</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博客排名在100名以内。粉丝过千。
Hongten出品，必是精品。

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

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