JAVA利用POI实现excel的导入导出

首先去apache官网下载最新版的poi支持jar包，较早的版本不支持office2003：
http://poi.apache.org/download.html#POI-3.14
项目需要添加的jar包：
log4j-12.28.jar
poi-3.14-20160307.jar
poi-ooxml-3.14-20160307.jar
poi-ooxml-schemas-3.14-20160307.jar
xmlbeans-2.6.0.jar

excel导入代码如下：

package Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.XSSFWorkbook;

public class ExcelImport {
    private static ExcelImport lp = new ExcelImport();
    private static String a = lp.getClass().getResource("/").getPath();

    public static List importExl(String filePath) throws IOException {
        String inPath = a.substring(1);
        String before = inPath.substring(0,inPath.indexOf("WEB-INF"));
        filePath = filePath.replaceAll("\r|\n", "");
        String oldExcelPath = before  +"excelimport/" + filePath;
        List conSheet = new ArrayList<Test>();

        conSheet = dealExl(oldExcelPath) ;
        return conSheet;
    }

    public static List dealExl(String filePath){
        List conSheet = new ArrayList<Test>();
        Test conObj = null;
        try {
            InputStream is = new FileInputStream(filePath);
            Workbook hssfWorkbook = null;
            try {
                hssfWorkbook = new XSSFWorkbook(filePath);
            } catch (Exception ex) {
                hssfWorkbook = new HSSFWorkbook(is);
            }
        // 循环工作表Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                // 循环行Row
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    conObj = new Test();

                    // 循环列Cell
                    // 0姓名 1姓名拼音 2性别 3出生年月日
                    Cell xm = hssfRow.getCell((short) 0);
                    if (xm != null) {
                        conObj.setName(getValue(xm));
                    }

                    Cell xmpy = hssfRow.getCell((short) 1);
                    if (xmpy == null){
                        conObj.setPinyinName(getValue(xmpy));
                    }

                    Cell xb = hssfRow.getCell((short) 2);
                    if (xb == null){
                        conObj.setGender(getValue(xb));
                    }

                    Cell csnyr = hssfRow.getCell((short) 3);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    sdf.setLenient(false);
                    Date birth = sdf.parse(getValue(csnyr));
                    if (csnyr == null){
                        conObj.setBirthdate(birth);
                    }

                    conSheet.add(conObj);
                }
            }
            is.close();
            hssfWorkbook.close();
        }catch (FileNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } catch (IOException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } catch (ParseException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return conSheet;
    }

    private static String getValue(Cell xm) {
        if (xm.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型
            return String.valueOf(xm.getBooleanCellValue());
        } else if (xm.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型
            return String.valueOf(xm.getNumericCellValue());
        } else {
            // 返回字符串类型
            return String.valueOf(xm.getStringCellValue());
        }
    }
}

导出到excel代码如下：

package Test; 

import java.io.File; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.text.SimpleDateFormat; 
import java.util.Date; 

import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFSheet; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 

import commonj.sdo.DataObject; 

public class ExcelExport {
    private static ExcelExport lp = new ExcelExport();
    private static String a = lp.getClass().getResource("/").getPath();

    public static String export(DataObject[] scheSheets) throws IOException {
        String outPath = a.substring(1);
        String xlsPath = "";

        xlsPath = first(scheSheets,outPath) ;
        return xlsPath;
    }

    @SuppressWarnings("deprecation")
    public static String first(DataObject[] scheSheets,String outPath){
        String destDir = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssS");
        String nowTime = sdf.format(new Date());
        //文件名
        String fileName = nowTime +".xls";
        //headers
        String[] rowName = {"序号","开始时间","结束时间"};

        try{
            //创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //定位到索引为0的工作表
            HSSFSheet sheet = workbook.createSheet("sheet1");

            //初始化表头行，获得列数
            int columnNum = rowName.length;  
            HSSFRow rowRowName = sheet.createRow(0);

            // 将列头设置到sheet的单元格中  
            for(int n=0;n<columnNum;n++){  
                HSSFCell  cellRowName = rowRowName.createCell((short) n);
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                String text = new String(rowName[n]);  
                cellRowName.setCellValue(text);
            }

            //将查询出的数据设置到sheet对应的单元格中  
            for(int i=0;i<scheSheets.length;i++){  

                DataObject obj = scheSheets[i];//遍历每个对象  
                HSSFRow row = sheet.createRow(i+1);//创建所需的行数  

                HSSFCell  cell = null;   //设置单元格的数据类型  
                cell = row.createCell((short) 0,HSSFCell.CELL_TYPE_NUMERIC);  
                cell.setCellValue(i+1);   
                cell = row.createCell((short) 1,HSSFCell.CELL_TYPE_STRING);  
                cell.setCellValue(obj.getString("startDate"));//设置单元格的值
                cell = row.createCell((short) 2,HSSFCell.CELL_TYPE_STRING);  
                cell.setCellValue(obj.getString("endDate"));//设置单元格的值
                cell = row.createCell((short) 3,HSSFCell.CELL_TYPE_STRING);  
                cell.setCellValue(obj.getString("createTime"));//设置单元格的值
            }
            if(workbook !=null){  
                try{ 
                    String before = outPath.substring(0,outPath.indexOf("WEB-INF"));
                    String oldExcelPath=before  +"excelexport/"+ fileName;
                    File file = new File(oldExcelPath);
                    FileOutputStream fileoutputstream = new FileOutputStream(file);
                    workbook.write(fileoutputstream);
                    fileoutputstream.close();

                    destDir = "excelexport/"+ fileName;
                }catch (IOException e){
                    e.printStackTrace();  
                }  
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return destDir;
    }
}