excel表格导入数据库后台JAVA方法

来源：互联网发布：淘宝商家入驻费用编辑：程序博客网时间：2024/05/20 04:48

1.判断文件类型

public class ReadExcal {    /**     * 对外提供读取excel 的方法     * */    public List<List<Object>> readExcel(File file) throws IOException {        String fileName = file.getName();        System.out.println("文件名："+fileName);        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName                .substring(fileName.lastIndexOf(".") + 1);        System.out.println("文件类型："+extension);        if ("xls".equals(extension)) {            return read2003Excel(file);        } else if ("xlsx".equals(extension)) {            return read2007Excel(file);        } else {            throw new IOException("不支持的文件类型");        }    }

2.版本是否正确

 public static Workbook create(InputStream inp) throws IOException,InvalidFormatException {        if (!inp.markSupported()) {            inp = new PushbackInputStream(inp, 8);        }        if (POIFSFileSystem.hasPOIFSHeader(inp)) {            return new HSSFWorkbook(inp);        }        if (POIXMLDocument.hasOOXMLHeader(inp)) {            return new XSSFWorkbook(OPCPackage.open(inp));        }        throw new IllegalArgumentException("你的excel版本目前poi解析不了");    }

3.不同类型对应方法

 /**     * 读取 office 2003 excel     *      * @throws IOException     * @throws FileNotFoundException     */    private static List<List<Object>> read2003Excel(File file)            throws IOException {        List<List<Object>> list = new LinkedList<List<Object>>();        Workbook hwb=null;try {hwb = create(new FileInputStream(file));} catch (InvalidFormatException e) {// TODO Auto-generated catch blocke.printStackTrace();}               //HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));        //表单里的第几张表        int size=hwb.getNumberOfSheets();        for(int k=0;k<size;k++){        Sheet sheet = hwb.getSheetAt(k);        Object value = null;        Row row = null;        Cell cell = null;        int counter = 0;        for (int i = sheet.getFirstRowNum()+1; counter+1 < sheet                .getPhysicalNumberOfRows(); i++) {            row = sheet.getRow(i);            if (row == null) {                continue;            } else {                counter++;            }            List<Object> linked = new LinkedList<Object>();            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {                cell = row.getCell(j);                if (cell == null) {                    continue;                }                DecimalFormat df = new DecimalFormat("0");// 格式化 number String                                                            // 字符                SimpleDateFormat sdf = new SimpleDateFormat(                        "yyyy-MM-dd");// 格式化日期字符串                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字                switch (cell.getCellType()) {                case XSSFCell.CELL_TYPE_STRING:                //  System.out.println(i + "行" + j + " 列 is String type");                    value = cell.getStringCellValue();                    break;                case XSSFCell.CELL_TYPE_NUMERIC:                    /*System.out.println(i + "行" + j                            + " 列 is Number type ; DateFormt:"                            + cell.getCellStyle().getDataFormatString());*/                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {                        value = df.format(cell.getNumericCellValue());                    } else if ("General".equals(cell.getCellStyle()                            .getDataFormatString())) {                        value = nf.format(cell.getNumericCellValue());                    } else {                        value = sdf.format(HSSFDateUtil.getJavaDate(cell                                .getNumericCellValue()));                    }                    break;                case XSSFCell.CELL_TYPE_BOOLEAN:                //  System.out.println(i + "行" + j + " 列 is Boolean type");                    value = cell.getBooleanCellValue();                    break;                case XSSFCell.CELL_TYPE_BLANK:                //  System.out.println(i + "行" + j + " 列 is Blank type");                    value = "";                    break;                default:                //  System.out.println(i + "行" + j + " 列 is default type");                    value = cell.toString();                }                if (value == null || "".equals(value)) {                    continue;                }                linked.add(value);            }            list.add(linked);        }        }        return list;    }

/**     * 读取Office 2007 excel     * */    private static List<List<Object>> read2007Excel(File file)            throws IOException {    System.out.println("进入2007excel");     List<List<Object>> list=null;     Workbook xwb=null;     Sheet sheet=null;       try{    list = new LinkedList<List<Object>>();            // 构造 XSSFWorkbook 对象，strPath 传入文件路径    //(jdk版本是否大于1.6.0_18,若没有，则会在new XSSFWorkbook()时报错)            //xwb = new XSSFWorkbook(new FileInputStream(file));        xwb=create(new FileInputStream(file));            // 读取第一章表格内容              }catch(Exception e){    System.out.println(e);    }    System.out.println(list.size()+"-"+xwb.getNumberOfSheets());     for( int o=0;o<xwb.getNumberOfSheets();o++){     sheet = xwb.getSheetAt(o);        Object value = null;        Row row = null;        Cell cell = null;        int counter = 0;        for (int i = sheet.getFirstRowNum()+1; counter+1 < sheet                .getPhysicalNumberOfRows(); i++) {            row = sheet.getRow(i);            if (row == null) {                continue;            } else {                counter++;            }            List<Object> linked = new LinkedList<Object>();            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {                cell = row.getCell(j);                if (cell == null) {                    continue;                }                DecimalFormat df = new DecimalFormat("0");// 格式化 number String                                                            // 字符                SimpleDateFormat sdf = new SimpleDateFormat(                        "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字                switch (cell.getCellType()) {                case XSSFCell.CELL_TYPE_STRING:                    System.out.println(i + "行" + j + " 列 is String type");                    value = cell.getStringCellValue();                    break;                case XSSFCell.CELL_TYPE_NUMERIC:                /*  System.out.println(i + "行" + j                            + " 列 is Number type ; DateFormt:"                            + cell.getCellStyle().getDataFormatString());*/                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {                        value = df.format(cell.getNumericCellValue());                    } else if ("General".equals(cell.getCellStyle()                            .getDataFormatString())) {                        value = nf.format(cell.getNumericCellValue());                    } else {                        /*value = sdf.format(HSSFDateUtil.getJavaDate(cell                                .getNumericCellValue()));*/                    }                    break;                case XSSFCell.CELL_TYPE_BOOLEAN:            //      System.out.println(i + "行" + j + " 列 is Boolean type");                    value = cell.getBooleanCellValue();                    break;                case XSSFCell.CELL_TYPE_BLANK:                //  System.out.println(i + "行" + j + " 列 is Blank type");                    value = "";                    break;                default:                //  System.out.println(i + "行" + j + " 列 is default type");                    value = cell.toString();                }                if (value == null || "".equals(value)) {                    continue;                }                linked.add(value);            }            list.add(linked);        }    }     System.out.println("size:"+list.size());        return list;    }

阅读全文

0 0