1：所需jar包
2：Mysql数据库表内容如下：
3：代码结构如下：
（1）User.java
public class User {
private int id;
private String name;
private String no;
private String nativePlace;
private String edu;
private Double math;
private Double computer;
private Double english;
private Double sumcount;
private Double avgcount;
//setter-getter ...
}
（2）JdbcUtil.java
public class JdbcUtil {
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final String USER = "root";
private static final String PASSWORD = "mysql";
private JdbcUtil() {
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static Connection getConnection() throws Exception {//建立连接
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void free(ResultSet rs, Statement st, Connection conn) {//释放资源
try {
if (rs != null) {
rs.close();
}
} catch (SQLException ex) {
Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (st != null) {
st.close();
}
} catch (SQLException ex) {
Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}
}
（3）UserService.java
public class UserService {
public static List<User> getUserList(){
User user;
List<User> list = new ArrayList<>();
String sql = "select id,name,no,nativeplace,edu,math,computer,english,sumcount,avgcount from t_user";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setNo(rs.getString("no"));
user.setNativePlace(rs.getString("nativeplace"));
user.setEdu(rs.getString("edu"));
user.setMath(rs.getDouble("math"));
user.setComputer(rs.getDouble("computer"));
user.setEnglish(rs.getDouble("english"));
user.setSumcount(rs.getDouble("sumcount"));
user.setAvgcount(rs.getDouble("avgcount"));
list.add(user);
}
} catch (Exception ex) {
Logger.getLogger(UserService.class.getName()).log(Level.SEVERE, null, ex);
}finally{
JdbcUtil.free(rs, ps, conn);
}
return list;
}
}
（4）Poitest.java
public class Poitest {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
InputStream inputStream = null;
OutputStream outputStream =null;
try {
inputStream = new FileInputStream(new File("E:\\hello_temp.xls"));
outputStream = new FileOutputStream(new File("E:\\hello1.xls"));
writeToExcelByTemp2(inputStream,outputStream);
System.out.println("成功生成");
} catch (FileNotFoundException ex) {
Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex);
}finally{
if(null!=outputStream){
try {
outputStream.close();
} catch (IOException ex) {
Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(null!=inputStream){
try {
inputStream.close();
} catch (IOException ex) {
Logger.getLogger(Poitest.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
/**
* 根据模版生成Excel
* @param inputStream
* @param outputStream
* @throws IOException
*/
public static void writeToExcelByTemp2(InputStream inputStream, OutputStream outputStream) throws IOException{
List<User> list = UserService.getUserList();
int length = list.size();
//New Workbook
Workbook wb = new HSSFWorkbook(inputStream);
//New Sheet
Sheet sheet = wb.getSheetAt(0);
int curRowIndex = 0;
for(int rowIndex=1; rowIndex<=length;rowIndex++){
curRowIndex = rowIndex;
//获取一行，如果为空则新建
Row row = sheet.getRow(rowIndex);
if(row == null){
row = sheet.createRow(rowIndex);
}
User user = list.get(rowIndex-1);
//根据user的对象个数创建列数
for(int cellNum=0; cellNum<10;cellNum++){
Cell cell = row.getCell(cellNum);
if(cell==null){
cell = row.createCell(cellNum);
}
switch(cellNum){
case 0:
cell.setCellValue(user.getId());
break;
case 1:
cell.setCellValue(user.getName());
break;
case 2:
cell.setCellValue(user.getNo());
break;
case 3:
cell.setCellValue(user.getNativePlace());
break;
case 4:
cell.setCellValue(user.getEdu());
break;
case 5:
cell.setCellValue(user.getMath());
break;
case 6:
cell.setCellValue(user.getComputer());
break;
case 7:
cell.setCellValue(user.getEnglish());
break;
case 8:
cell.setCellFormula("SUM(F"+(rowIndex+1)+":H"+(rowIndex+1)+")");
break;
case 9:
cell.setCellFormula("I" + (rowIndex+1) +"/3");
break;
}
}
}
curRowIndex++;
Row row = sheet.createRow(curRowIndex);
Cell cell0 = row.createCell(0);
cell0.setCellValue("总计");
Cell cell5 = row.createCell(5);
cell5.setCellFormula("SUM(F2:"+"F"+(curRowIndex)+")");
Cell cell6 = row.createCell(6);
cell6.setCellFormula("SUM(G2:"+"G"+(curRowIndex)+")");
Cell cell7 = row.createCell(7);
cell7.setCellFormula("SUM(H2:"+"H"+(curRowIndex)+")");
Cell cell8 = row.createCell(8);
cell8.setCellFormula("SUM(I2:"+"I"+(curRowIndex)+")");
Cell cell9 = row.createCell(9);
cell9.setCellFormula("AVERAGE(J2:"+"J"+(curRowIndex)+")");
wb.write(outputStream);
}
}
Excel模版：
生成结果：
