java工具类（excel导入到数据库）

package com.longrise.LGCS.Logic;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
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.HSSFDateUtil;
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.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.longrise.LEAP.Base.Logic.LEAPLogic;
import com.longrise.LEAP.Base.Service.Resource.NameedPathUtil;
import com.longrise.LGCS.Util.PR;

public class LGCSReadExcelData extends LEAPLogic
{
    
String[] FIRSTROWNAME={"*村（社区）名称","*村（居）统一社会信用代码","*姓名","*类型","职务","*身份证号","*性别","*出生年月","*政治面貌","*学历","任期开始时间","任期结束时间"};
    
    public PR ReadExamExcelData ( String path)
    {
        try
        {
            if ( path == null || "".equals(path))
                return new PR(0, "读取参数失败!", false);
            path = path.replace("default/", NameedPathUtil.getPath("default"));
            File file = new File(path);
            List<List<String>> list = readExcel(file, 2, 0, 11);
            if(list==null || list.size()==0)
                return new PR(0,"导入模板错误,请点击【模板下载】按钮下载模板!",false);
            return new PR(1,"读取excel数据成功!",list);
        }
        catch (IOException e)
        {
            e.printStackTrace();
            return new PR(0,e.getMessage(),false);
        }
        catch (ParseException e)
        {
            e.printStackTrace();
            return new PR(0,"时间解析出错",false);
        }
        
    }
    
    /**
     * 读取Excel内容
     * 
     * @param file
     * @param startRowIndex
     *            起始行下标，从0开始
     * @param startColumnIndex
     *            起始列下标，从0开始
     * @param endColumnIndex
     *            结束列下标
     * @return
     * @throws IOException
     * @throws ParseException 
     */
    public List<List<String>> readExcel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
    {
        if ( file == null || !file.isFile() )
        {
            return null;
        }
        String fileName = file.getName().toLowerCase();
        if ( fileName.endsWith(".xls") )
        {
            return read2003Excel(file, startRowIndex, startColumnIndex, endColumnIndex);
        }
        else if ( fileName.endsWith(".xlsx") )
        {
            return read2007Excel(file, startRowIndex, startColumnIndex, endColumnIndex);
        }
        else
        {
            throw new IOException("不支持的文件类型");
        }
    }
    
    private List<List<String>> read2003Excel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
    {
        HSSFWorkbook hwb=null;
        try{
            List<List<String>> list = new ArrayList<List<String>>();
            hwb = new HSSFWorkbook(new FileInputStream(file));
            HSSFSheet sheet = hwb.getSheetAt(0);
            HSSFRow row = null;
            HSSFCell cell = null;
            String cellValue = null;
            //判断导入的模板是不是我们的模板,获取第一行作为验证标准 
            boolean flag=true;
            for (int i = 0; i <= endColumnIndex; i++)
            {
                HSSFRow firstRow = sheet.getRow(1);
                HSSFCell firstCell = firstRow.getCell(i);
                if(!FIRSTROWNAME[i].equals(firstCell.toString())){
                    flag=false;
                    break;
                }
            }
            if(flag){
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++)
                {   
                    int count_blank = 0;
                    row = sheet.getRow(i);
                    if ( row == null )
                    {
                        continue;
                    }
                    List<String> rowData = new ArrayList<String>();
                    for (int j = startColumnIndex; j <= endColumnIndex; j++)
                    {
                        cell = row.getCell(j);
                        if ( cell == null )
                        {
                            count_blank++;
                            rowData.add(null);
                            continue;
                        }
                        DecimalFormat df = new DecimalFormat("0");// 格式化 number String
                        // 字符
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                        FormulaEvaluator formula = hwb.getCreationHelper().createFormulaEvaluator();
                       
                        switch (cell.getCellType())
                        {
                            case XSSFCell.CELL_TYPE_STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                double num = formula.evaluate(cell).getNumberValue();
                                if(num == 0){
                                    cellValue = df.format(num);
                                }
                                else if(num <= 100 && num >= 0){
                                    cellValue = nf.format(num);
                                }
                                else if("0_ ".equals(cell.getCellStyle().getDataFormatString()) || "0;[Red]0".equals(cell.getCellStyle().getDataFormatString())){
                                    cellValue = df.format(cell.getNumericCellValue());
                                }
                                else if ( "@".equals(cell.getCellStyle().getDataFormatString()) )
                                {
                                    cellValue = df.format(cell.getNumericCellValue());
                
                                }
                                else if ( "General".equals(cell.getCellStyle().getDataFormatString()) )
                                {
                                    cellValue = nf.format(cell.getNumericCellValue());
                                }
                                else
                                {
                                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case XSSFCell.CELL_TYPE_BLANK:
                                cellValue = "";
                                count_blank++;
                                break;
                            case  XSSFCell.CELL_TYPE_FORMULA:
                                String str = "";
                                str = formula.evaluate(cell).getStringValue();
                                cellValue = str;
                                break;
                            default:
                                cellValue = cell.toString();
                        }
                        cellValue = cellValue.replaceAll("\\s*","");
                        cellValue = cellValue.replace(" ", "");
//                        cellValue = cellValue.replaceAll("<","&lt;");
//                        cellValue = cellValue.replaceAll(">","&gt;");
                        rowData.add(cellValue);
                    }
                    if(count_blank!=endColumnIndex+1){
                        list.add(rowData);
                    }
                }
            }
            return list;
        }catch(IOException e){
            throw e;
        }finally{
            try{
                if(hwb!=null){
                    hwb.close();
                }
                if(file!=null && file.exists() && file.isFile()){
                    file.delete();
                }
            }catch(IOException e){
                e.printStackTrace();
            }
        }
    }

    private List<List<String>> read2007Excel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
    {
        XSSFWorkbook xwb=null;
        try{
            List<List<String>> list = new ArrayList<List<String>>();
            xwb = new XSSFWorkbook(new FileInputStream(file));
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(0);
            XSSFRow row = null;
            XSSFCell cell = null;
            String cellValue = null;
            //判断导入的模板是不是我们的模板,获取第一行作为验证标准
            boolean flag=true;
            for (int i = 0; i <= endColumnIndex; i++)
            {
                XSSFRow firstRow = sheet.getRow(1);
                XSSFCell firstCell = firstRow.getCell(i);
                if(!FIRSTROWNAME[i].equals(firstCell.toString())){
                    flag=false;
                    break;
                }
            }
            
            if(flag){
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++)
                {
                    int count_blank = 0;
                    row = sheet.getRow(i);
                    if ( row == null )
                    {
                        continue;
                    }
                    List<String> rowData = new ArrayList<String>();
                    for (int j = startColumnIndex; j <= endColumnIndex; j++)
                    {
                        // for (int j = row.getFirstCellNum(); j < row.getLastCellNum();
                        // j++) {
                        cell = row.getCell(j);
                        if ( cell == null )
                        {
                            count_blank++;
                            rowData.add(null);
                            continue;
                        }
                        DecimalFormat df = new DecimalFormat("0");// 格式化 number String
                        // 字符
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                        FormulaEvaluator formula = xwb.getCreationHelper().createFormulaEvaluator();
                        switch (cell.getCellType())
                        {
                            case XSSFCell.CELL_TYPE_STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                double num = formula.evaluate(cell).getNumberValue();
                                if(num == 0){
                                    cellValue = df.format(num);
                                }
                                else if(num <= 100 && num >= 0){
                                    cellValue = nf.format(num);
                                }
                                else if("0_ ".equals(cell.getCellStyle().getDataFormatString()) || "0;[Red]0".equals(cell.getCellStyle().getDataFormatString())){
                                    cellValue = df.format(cell.getNumericCellValue());
                                }
                                else if ( "@".equals(cell.getCellStyle().getDataFormatString()) )
                                {
                                    cellValue = df.format(cell.getNumericCellValue());
                
                                }
                                else if ( "General".equals(cell.getCellStyle().getDataFormatString()) )
                                {
                                    cellValue = nf.format(cell.getNumericCellValue());
                                }
                                else
                                {
                                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case XSSFCell.CELL_TYPE_BLANK:
                                cellValue = "";
                                count_blank ++;
                                break;
                            case  XSSFCell.CELL_TYPE_FORMULA:
                                String str = "";
                                str = formula.evaluate(cell).getStringValue();
                                cellValue = str;
                                break;   
                            default:
                                cellValue = cell.toString();
                        }
                        cellValue = cellValue.replaceAll("\\s*","");
                        cellValue = cellValue.replace(" ", "");
//                        cellValue = cellValue.replaceAll("<","&lt;");
//                        cellValue = cellValue.replaceAll(">","&gt;");
                        rowData.add(cellValue);
                    }
                    if(count_blank!=endColumnIndex+1){
                        list.add(rowData);
                    }
                }
            }
            return list;
        }catch(IOException e){
            throw e;
        }finally{
            try{
                if(xwb!=null){
                    xwb.close();
                }
                if(file!=null && file.exists() && file.isFile()){
                    file.delete();
                }
            }catch(IOException e){
                e.printStackTrace();
            }
        }
    }

}