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