导入excel

转载：https://www.itxm.cn/post/29113.html

一、导入jar包

poi-3.7.jar
poi-scratchpad-3.7.jar
poi-examples-3.7.jar
poi-ooxml-3.7.jar
poi-ooxml-schemas-3.7.jar

xmlbeans-2.3.0.jar

maven

 <dependency> 
            <groupId>org.apache.poi</groupId> 
            <artifactId>poi</artifactId> 
            <version>3.7</version> 
        </dependency> 
        <dependency> 
            <groupId>org.apache.poi</groupId> 
            <artifactId>poi-ooxml</artifactId> 
            <version>3.7</version> 
        </dependency>

二、编写工具类ExcelTool.java

 Java

package com.test; 
 
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFDateUtil; 
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.ss.usermodel.DateUtil; 
import org.apache.poi.xssf.usermodel.XSSFCell; 
 
import java.text.DecimalFormat; 
import java.text.SimpleDateFormat; 
import java.util.Calendar; 
import java.util.Date; 
 
public class ExcelTool { 
    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"; 
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; 
    public static final String EMPTY = ""; 
    public static final String POINT = "."; 
    public static SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd"); 
    /** 
     * 获得path的后缀名 
     * @param path 
     * @return 
     */ 
    public static String getPostfix(String path){ 
        if(path==null || EMPTY.equals(path.trim())){ 
            return EMPTY; 
        } 
        if(path.contains(POINT)){ 
            return path.substring(path.lastIndexOf(POINT)+1,path.length()); 
        } 
        return EMPTY; 
    } 
    /** 
     * 单元格格式 
     * @param hssfCell 
     * @return 
     */ 
    @SuppressWarnings({ "static-access", "deprecation" }) 
    public static String getHValue(HSSFCell hssfCell){ 
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { 
            return String.valueOf(hssfCell.getBooleanCellValue()); 
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { 
            String cellValue = ""; 
            if(HSSFDateUtil.isCellDateFormatted(hssfCell)){ 
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()); 
                cellValue = sdf.format(date); 
            }else{ 
                DecimalFormat df = new DecimalFormat("#.##"); 
                cellValue = df.format(hssfCell.getNumericCellValue()); 
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length()); 
                if(strArr.equals("00")){ 
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT)); 
                } 
            } 
            return cellValue; 
        } else { 
            return String.valueOf(hssfCell.getStringCellValue()); 
        } 
    } 
    /** 
     * 单元格格式 
     * @param xssfCell 
     * @return 
     */ 
    public static String getXValue(XSSFCell xssfCell){ 
        if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { 
            return String.valueOf(xssfCell.getBooleanCellValue()); 
        } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { 
            String cellValue = ""; 
            if(XSSFDateUtil.isCellDateFormatted(xssfCell)){ 
                Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue()); 
                cellValue = sdf.format(date); 
            }else{ 
                DecimalFormat df = new DecimalFormat("#.##"); 
                cellValue = df.format(xssfCell.getNumericCellValue()); 
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length()); 
                if(strArr.equals("00")){ 
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT)); 
                } 
            } 
            return cellValue; 
        } else { 
            return String.valueOf(xssfCell.getStringCellValue()); 
        } 
    } 
    /** 
     * 自定义xssf日期工具类 
     * @author lp 
     * 
     */ 
    static class XSSFDateUtil extends DateUtil { 
        protected static int absoluteDay(Calendar cal, boolean use1904windowing) { 
            return DateUtil.absoluteDay(cal, use1904windowing); 
        } 
    } 
} 

　　

三、编写调用类ExcelUtils.java（File类型使用）

import com.test.ExcelTool; 
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.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 java.io.*; 
 
import java.util.ArrayList; 
import java.util.List; 
 
 
public class ExcelUtils { 
 
    public static int totalRows; //sheet中总行数 
    public static int totalCells; //每一行总单元格数 
 
 
    /** 
     * read the Excel .xlsx,.xls 
     * @param file jsp中的上传文件 
     * @return 
     * @throws IOException 
     */ 
    public static List<ArrayList<String>> readExcel(File file) throws IOException { 
        if(file==null){ 
            return null; 
        }else{ 
            String postfix = ExcelTool.getPostfix(file.getName()); 
            if(!ExcelTool.EMPTY.equals(postfix)){ 
                if(ExcelTool.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){ 
                    return readXls(file); 
                }else if(ExcelTool.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){ 
                    return readXlsx(file); 
                }else{ 
                    return null; 
                } 
            } 
        } 
        return null; 
    } 
 
 
    /** 
     * read the Excel 2010 .xlsx 
     * @param file 
     * @return 
     * @throws IOException 
     */ 
    @SuppressWarnings("deprecation") 
    public static List<ArrayList<String>> readXlsx(File file){ 
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>(); 
        // IO流读取文件 
        InputStream input = null; 
        XSSFWorkbook wb = null; 
        ArrayList<String> rowList = null; 
        try { 
            input = new FileInputStream(file); 
            // 创建文档 
            wb = new XSSFWorkbook(input); 
            //读取sheet(页) 
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){ 
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet); 
                if(xssfSheet == null){ 
                    continue; 
                } 
                totalRows = xssfSheet.getLastRowNum(); 
                //读取Row,从第二行开始 
                for(int rowNum = 0;rowNum <= totalRows;rowNum++){ 
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum); 
                    if(xssfRow!=null){ 
                        rowList = new ArrayList<String>(); 
                        totalCells = xssfRow.getLastCellNum(); 
                        //读取列，从第一列开始 
                        for(int c=0;c<=totalCells+1;c++){ 
                            XSSFCell cell = xssfRow.getCell(c); 
                            if(cell==null){ 
                                rowList.add(ExcelTool.EMPTY); 
                                continue; 
                            } 
                            rowList.add(ExcelTool.getXValue(cell).trim()); 
                        } 
                        list.add(rowList); 
                    } 
                } 
            } 
            return list; 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } finally{ 
            try { 
                input.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } 
        return null; 
 
    } 
    /** 
     * read the Excel 2003-2007 .xls 
     * @param file 
     * @return 
     * @throws IOException 
     */ 
    public static List<ArrayList<String>> readXls(File file){ 
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>(); 
        // IO流读取文件 
        InputStream input = null; 
        HSSFWorkbook wb = null; 
        ArrayList<String> rowList = null; 
        try { 
            input = new FileInputStream(file); 
            // 创建文档 
            wb = new HSSFWorkbook(input); 
            //读取sheet(页) 
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){ 
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet); 
                if(hssfSheet == null){ 
                    continue; 
                } 
                totalRows = hssfSheet.getLastRowNum(); 
                //读取Row,从第二行开始 
                for(int rowNum = 0;rowNum <= totalRows;rowNum++){ 
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum); 
                    if(hssfRow!=null){ 
                        rowList = new ArrayList<String>(); 
                        totalCells = hssfRow.getLastCellNum(); 
                        //读取列，从第一列开始 
                        for(short c=0;c<=totalCells+1;c++){ 
                            HSSFCell cell = hssfRow.getCell(c); 
                            if(cell==null){ 
                                rowList.add(ExcelTool.EMPTY); 
                                continue; 
                            } 
                            rowList.add(ExcelTool.getHValue(cell).trim()); 
                        } 
                        list.add(rowList); 
                    } 
                } 
            } 
            return list; 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } finally{ 
            try { 
                input.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } 
        return null; 
    } 
 
 
 
}

　

四、调用方法

1、本地调用方式

 public static void main(String[] args) throws Exception { 
        File file = new File("E:\\导入模板表.xlsx"); 
        List<ArrayList<String>> list = ExcelUtils.readExcel(file); 
        for (int i = 0; i < list.size(); i++) { 
            //第一行全部数据 
            List list1=list.get(i); 
            for (int j = 0; j < list1.size(); j++) { 
                //第一行每个单元格数据 
                System.out.println(list1.get(j)); 
            } 
        } 
    }

2、JavaWeb调用方式（只需把上面的方法中File类型改成MultipartFile类型,使用以下的ExcelUtils类）

@RequestMapping(value = "o_import.do",method = RequestMethod.POST) 
    public String importXls( 
            @RequestParam(value = "Filedata", required = false) MultipartFile file) { 
        try { 
            //list为excel数据集合 
            List<ArrayList<String>> list = ExcelUtils.readExcel(file); 
            for (int i = 0; i < list.size(); i++) { 
                //第一行数据集合 
                List list1=list.get(i); 
                for (int j = 0; j < list1.size(); j++) { 
                    //第一行每个单元格数据 
                    System.out.println(list1.get(j)); 
                } 
                 
            } 
 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
        return null; 
    }

ExcelUtils类（MultipartFile类型使用）

import org.apache.poi.hssf.usermodel.*; 
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 org.springframework.web.multipart.MultipartFile; 
 
import java.io.IOException; 
import java.io.InputStream; 
import java.util.ArrayList; 
import java.util.List; 
 
public class ExcelUtils { 
 
 
    public static int totalRows; //sheet中总行数 
    public static int totalCells; //每一行总单元格数 
    /** 
     * read the Excel .xlsx,.xls 
     * @param file jsp中的上传文件 
     * @return 
     * @throws IOException 
     */ 
    public  static List<ArrayList<String>>  readExcel(MultipartFile file) throws IOException { 
        if(file==null||ExcelTool.EMPTY.equals(file.getOriginalFilename().trim())){ 
            return null; 
        }else{ 
            String postfix = ExcelTool.getPostfix(file.getOriginalFilename()); 
            if(!ExcelTool.EMPTY.equals(postfix)){ 
                if(ExcelTool.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){ 
                    return readXls(file); 
                }else if(ExcelTool.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){ 
                    return readXlsx(file); 
                }else{ 
                    return null; 
                } 
            } 
        } 
        return null; 
    } 
    /** 
     * read the Excel 2010 .xlsx 
     * @param file 
     * @return 
     * @throws IOException 
     */ 
    @SuppressWarnings("deprecation") 
    public static List<ArrayList<String>> readXlsx(MultipartFile file){ 
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>(); 
        // IO流读取文件 
        InputStream input = null; 
        XSSFWorkbook wb = null; 
        ArrayList<String> rowList = null; 
        try { 
            input = file.getInputStream(); 
            // 创建文档 
            wb = new XSSFWorkbook(input); 
            //读取sheet(页) 
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){ 
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet); 
                if(xssfSheet == null){ 
                    continue; 
                } 
                totalRows = xssfSheet.getLastRowNum(); 
                //读取Row,从第二行开始 
                for(int rowNum = 0;rowNum <= totalRows;rowNum++){ 
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum); 
                    if(xssfRow!=null){ 
                        rowList = new ArrayList<String>(); 
                        totalCells = xssfRow.getLastCellNum(); 
                        //读取列，从第一列开始 
                        for(int c=0;c<=totalCells+1;c++){ 
                            XSSFCell cell = xssfRow.getCell(c); 
                            if(cell==null){ 
                                rowList.add(ExcelTool.EMPTY); 
                                continue; 
                            } 
                            rowList.add(ExcelTool.getXValue(cell).trim()); 
                        } 
                        list.add(rowList); 
                    } 
                } 
            } 
            return list; 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } finally{ 
            try { 
                input.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } 
        return null; 
 
    } 
    /** 
     * read the Excel 2003-2007 .xls 
     * @param file 
     * @return 
     * @throws IOException 
     */ 
    public static List<ArrayList<String>> readXls(MultipartFile file){ 
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>(); 
        // IO流读取文件 
        InputStream input = null; 
        HSSFWorkbook wb = null; 
        ArrayList<String> rowList = null; 
        try { 
            input = file.getInputStream(); 
            // 创建文档 
            wb = new HSSFWorkbook(input); 
            //读取sheet(页) 
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){ 
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet); 
                if(hssfSheet == null){ 
                    continue; 
                } 
                totalRows = hssfSheet.getLastRowNum(); 
                //读取Row,从第二行开始 
                for(int rowNum = 0;rowNum <= totalRows;rowNum++){ 
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum); 
                    if(hssfRow!=null){ 
                        rowList = new ArrayList<String>(); 
                        totalCells = hssfRow.getLastCellNum(); 
                        //读取列，从第一列开始 
                        for(short c=0;c<=totalCells+1;c++){ 
                            HSSFCell cell = hssfRow.getCell(c); 
                            if(cell==null){ 
                                rowList.add(ExcelTool.EMPTY); 
                                continue; 
                            } 
                            rowList.add(ExcelTool.getHValue(cell).trim()); 
                        } 
                        list.add(rowList); 
                    } 
                } 
            } 
            return list; 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } finally{ 
            try { 
                input.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } 
        return null; 
    } 
}