poi操作excel demo

使用poi3.11版本，要求jdk1.7以上

支持导入导出后缀为xls或xlsx的excel文件

源代码:

package com.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.PostConstruct;

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 org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
/**
 *
 * @author guoming
 *
 */
@Service("testPOI")
public class Test {

     @Autowired
     private JdbcTemplate jdbcTemplate;
    
    @PostConstruct
    public void test(){
        
        List<EStudent> studentList=getStudentList();
        File file = new File("D:\\student.xls");
        try {
            
            //writeDataToExcel(studentList,file);
            writeDataToDB(file);
        } catch (Exception e) {
            
            e.printStackTrace();
        }
    }
    /**
     *
     * 功能描述:判断是否是excel2007
     *@author guoming
     *@date 2014-12-25下午5:18:26
     *@return boolean
     */
    private static boolean isExcel2007(String extensionName){
        
         return extensionName.matches("\\.xlsx");
    }
    public static void main(String[] args) {
        
        String s="ss.xlsx";
        s=s.substring(s.lastIndexOf("."),s.length());
        System.out.println("s="+s);
        System.out.println(isExcel2007(s));
    }
    private Workbook createWorkbook(File file) throws Exception{
        
          if (!file.exists()) {
              
              Assert.notNull(file,"file can't exists....");
          }
          String fileName=file.getName();
          String extenName=fileName.substring(fileName.lastIndexOf("."),fileName.length());
          boolean isExcel2003=true;
          if(isExcel2007(extenName)) {
              isExcel2003=false;
          }
          System.out.println("isExcel2003="+isExcel2003);
          InputStream in = new FileInputStream(file);
          
          Workbook workbook=null;
          try {
              workbook = isExcel2003?new HSSFWorkbook(in):new XSSFWorkbook(in);
          } catch (Exception e) {
            
            System.out.println("实例化workbook失败，原因是："+e.getMessage());
          }
          return workbook;
          
    }
    private void saveListToDB(List<EStudent> studentList) throws Exception{
        
          String insertSql="insert into student(name,age,hobby,department) values(?,?,?,?)";
          if(!CollectionUtils.isEmpty(studentList)) {
            Connection con=null;
            PreparedStatement ps=null;
                try {
                      con = jdbcTemplate.getDataSource().getConnection();
                      con.setAutoCommit(false);
                      ps = con.prepareStatement(insertSql);
                      for(EStudent stu:studentList) {
                          
                          ps.setString(1,stu.getName());
                          ps.setInt(2,stu.getAge());
                          ps.setString(3,stu.getHobby());
                          ps.setString(4,stu.getDepartment());
                          ps.addBatch();
                      }
                      ps.executeBatch();
                      con.commit();
                } catch (Exception e) {
                    con.rollback();
                    e.printStackTrace();
                }finally{
                    
                    ps.close();
                    con.close();
                }
          }
    }
    /**
     *
     * 功能描述:从excel表中读取数据并保存到数据库中
     *@author guoming
     *@date 2014-12-25下午2:54:50
     *@return void
     */
    public void writeDataToDB(File file) throws Exception{
        
          Assert.notNull(file,"file can't be null");
          Workbook workbook=createWorkbook(file);
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
          String startTime=sdf.format(new Date());
          
          System.out.println("\n************【准备处理导入工作】************");
        
          int sheetNum=workbook.getNumberOfSheets();
          System.out.println("\n\n**************共有工作表总数*****************:"+sheetNum);
          String[] sheetNames=new String[sheetNum];
          Sheet sheet=null;
          EStudent student=null;
          List<EStudent> studentList = new ArrayList<EStudent>();
          for(int i=0;i<sheetNum;i++) {
              studentList = new ArrayList<EStudent>();
              sheet=workbook.getSheetAt(i);
              sheetNames[i]=sheet.getSheetName();// 获取表名，存入数组
              System.out.println("\n************【开始处理工作表----"+sheetNames[i]+"("+startTime+")】***************");
              System.out.println("\n\n-----正在读取和匹配工作表\t《"+sheetNames[i]+"》\t的数据-----\n");
              int rowNum=sheet.getLastRowNum();
              int physicalNum=sheet.getPhysicalNumberOfRows();
              System.out.println("\n\n-----表\t《"+sheetNames[i]+"》\t共有数据-----:\t"+rowNum+"\t行"+"\t-----");
              Row row=null;
              Cell cell=null;
              Object[] cellValues=null;
              int totalCells=0;
              boolean flag=false;
              if(physicalNum>0) {// 说明这个工作表中确实存在数据
                  rowNum++;// 工作行默认是从0开始的，所以需要加1
              }
              for(int rows=0;rows<rowNum;rows++) {
                  row=sheet.getRow(rows);
                  if(null != row && !flag) {
                      totalCells=row.getPhysicalNumberOfCells();
                      System.out.println("totalCells="+totalCells);
                      flag=true;
                      continue;
                  }
                  if(null != row) {
                      
                      //int cellNum=row.getLastCellNum();
                      cellValues=new Object[totalCells];// 单元格值数组
                      for(int column=0;column<totalCells;column++) {
                          
                          cell=row.getCell(column);
                          if(null != cell) {
                              
                              switch(cell.getCellType()){
                              
                                      case XSSFCell.CELL_TYPE_STRING:
                                          cellValues[column]=cell.getStringCellValue();
                                          if(null == cellValues[column]) {
                                              
                                              cellValues[column]="";
                                          }
                                          break;
                                      case XSSFCell.CELL_TYPE_NUMERIC:
                                          double strCell=cell.getNumericCellValue();
                                          cellValues[column]=strCell;
                                          break;
                                      case XSSFCell.CELL_TYPE_BLANK:
                                          cellValues[column]="";
                                          break;
                                      case XSSFCell.CELL_TYPE_BOOLEAN:
                                          cellValues[column]=cell.getBooleanCellValue();
                                          break;
                                      default:
                                          System.out.println("\n\n----单元格格式不支持---");
                                          break;
                                  
                              }
                          }
                      }
                      student=new EStudent();
                      try {
                        student.setName(cellValues[0]==null?"":cellValues[0].toString());
                        student.setAge(cellValues[1]==null?0:Double.valueOf(cellValues[1].toString()).intValue());
                        student.setHobby(cellValues[2]==null?"":cellValues[2].toString());
                        student.setDepartment(cellValues[3]==null?"":cellValues[3].toString());
                      } catch (Exception e) {
                        System.out.println("warnings!,information have not fill in it fully");
                      }
                      System.out.println(student);
                      studentList.add(student);
                  }
              }
              saveListToDB(studentList);
              String endTime=sdf.format(new Date());
              System.out.println("\n************【处理完成----"+sheetNames[i]+"("+endTime+")】************");
          }
    }
    /**
     *
     * 功能描述:将数据填充到excel表中
     *@author guoming
     *@date 2014-12-25下午1:35:02
     *@return void
     */
    public void writeDataToExcel(List<EStudent> list,File file) throws Exception{
        
        Assert.notNull(file,"file can't be null");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startTime=sdf.format(new Date());
        System.out.println("\n************【开始处理导出------("+startTime+")】************");
        // 创建excel工作薄
        Workbook  wb = createWorkbook(file);
        int sheetIndex=wb.getSheetIndex("学生信息");
        if(sheetIndex>=0) {
            
            wb.removeSheetAt(sheetIndex);
        }
        // 创建一个sheet页，命名为"学生信息"
        Sheet sheet = wb.createSheet("学生信息");
        // 创建一行
        Row row = sheet.createRow(0);
        
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("年龄");
        row.createCell(2).setCellValue("爱好");
        row.createCell(3).setCellValue("部门");
        
        if(!CollectionUtils.isEmpty(list)) {
            
            int size = list.size();
            EStudent student=null;
            for(int i=1;i<=size;i++) {
                
                row = sheet.createRow(i);
                student=list.get(i-1);
                row.createCell(0).setCellValue(student.getName());
                row.createCell(1).setCellValue(student.getAge());
                row.createCell(2).setCellValue(student.getHobby());
                row.createCell(3).setCellValue(student.getDepartment());
                
            }
        }
        FileOutputStream fos = new FileOutputStream(file);
        wb.write(fos);
        wb.close();
        fos.close();
        String endTime=sdf.format(new Date());
        System.out.println("\n************【导出处理结束------("+endTime+")】************");
    }
    /**
     *
     * 功能描述:获取学生实体列表
     *@author guoming
     *@date 2014-12-25上午11:25:42
     *@return List<EStudent>
     */
    public List<EStudent> getStudentList(){
        
        List<EStudent> studentList = new ArrayList<EStudent>();
        final String sql="select * from student";
        try {
            studentList=jdbcTemplate.query(new PreparedStatementCreator(){

                @Override
                public PreparedStatement createPreparedStatement(Connection connection)
                        throws SQLException {
                    
                    PreparedStatement ps = connection.prepareStatement(sql);
                    return ps;
                }
                
            }, new RowMapper<EStudent>(){

                @Override
                public EStudent mapRow(ResultSet resultSet, int arg1)
                        throws SQLException {
                    EStudent student = new EStudent();
                    student.setName(resultSet.getString("name"));
                    student.setAge(resultSet.getInt("age"));
                    student.setHobby(resultSet.getString("hobby"));
                    student.setDepartment(resultSet.getString("department"));
                    return student;
                }

                
                
            });
        } catch (Exception e) {
            System.out.println("query error!"+e.getMessage());
            studentList=new ArrayList<EStudent>();
        }
        return studentList;
        
    }
}