V2AS问路
JAVA导入(读取)Excel中的数据（支持xls与xlsx文件）
阅读原文时间：2023年07月08日阅读：3

一、导入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

org.apache.poi poi 3.7
org.apache.poi poi-ooxml 3.7

jar包下载地址：https://yvioo.lanzous.com/b00no8wbe
                        密码:eln5

二、编写工具类ExcelTool.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> 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> 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;  
}

}

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章

dubbo源码分析- 集群容错之Cluster（一）
Dubbo 源码分析 - 集群容错之 Directory
Redis进阶实践之九 独立封装的RedisClient客户端工具类

4

Java-马士兵设计模式学习笔记-代理模式-动态代理 调用Proxy.newProxyInstance()

5

INotifyPropertyChanged接口的实现

6

mongoDB 学习笔记纯干货（mongoose、增删改查、聚合、索引、连接、备份与恢复、监控等等）

7

DataGridView导出Excel

8

aspnetpager分页，不使用存储过程

9

使用Aes对称加密解密Web.Config数据库连接串

10

Solr5.0源码分析-SolrDispatchFilter