使用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;
}
}