poi实现上传下载 Java

maven 的配置，pom.xml加入下面的依赖

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

POI  java 实现导出

/**
 * @Probject Name: testSpringmvc
 * @Path: com.test.mySpringmvc.javaUplodeUplodeExcel.java
 * @Create By sumeimei
 * @Create In Mar 21, 2019 10:06:05 AM
 */
package com.test.mySpringmvc.javaUplode;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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;
import org.apache.commons.io.*;

/**
 * @Class Name ExportExcel
 * @Author sumeimei
 * @Create In Mar 21, 2019
 */
public class ExportExcel {

  private final static String XLS = "xls";
  private final static String XLSX = "xlsx";
  private final static String SEPARATOR = "|";

  public static List<String> exportListExcel(File file, int sheetNum)
      throws FileNotFoundException {
    return exportListExcel(new FileInputStream(file),
        FilenameUtils.getExtension(file.getName()), sheetNum);
  }

  public static List<String> exportListExcel(InputStream is, String extensionName, int sheetNum) {

    Workbook workbook = null;
    if (extensionName.toLowerCase().equals(XLS)) {
      try {
        workbook = new HSSFWorkbook(is);
      } catch (IOException e) {
        e.printStackTrace();
      }
    } else if (extensionName.toLowerCase().equals(XLSX)) {
      try {
        workbook = new XSSFWorkbook(is);
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    return exportListFromExcel(workbook, sheetNum);
  }

  private static List<String> exportListFromExcel(Workbook workbook, int sheetNum) {
    Sheet sheet = workbook.getSheetAt(sheetNum);
    // 解析公式结果
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    List<String> list = new ArrayList<String>();
    // 获取第一行
    int minRowIx = sheet.getFirstRowNum();
    // 获取最后一行
    int maxRowIx = sheet.getLastRowNum();
    //循环获取每一行的
    for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
      Row row = sheet.getRow(rowIx);
      StringBuilder sb = new StringBuilder();
     //获取第一列
      short minColIx = row.getFirstCellNum();
      //获取最后一列
      short maxColIx = row.getLastCellNum();
     //循环获取每一列的值
      for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
        Cell cell = row.getCell(new Integer(colIx));
        CellValue cellValue = evaluator.evaluate(cell);
        if (cellValue == null) {
          continue;
        }
        // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
        // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
        switch (cellValue.getCellType()) {
          case Cell.CELL_TYPE_BOOLEAN:
            sb.append(SEPARATOR + cellValue.getBooleanValue());
            break;
          case Cell.CELL_TYPE_NUMERIC:
            // 这里的日期类型会被转换为数字类型，需要判别后区分处理
            if (DateUtil.isCellDateFormatted(cell)) {
              sb.append(SEPARATOR + cell.getDateCellValue());
            } else {
              // 把手机号码转换为字符串
              DecimalFormat df = new DecimalFormat("#");
              sb.append(SEPARATOR + df.format(cellValue.getNumberValue()));
            }
            break;
          case Cell.CELL_TYPE_STRING:
            sb.append(SEPARATOR + cellValue.getStringValue());
            break;
          case Cell.CELL_TYPE_FORMULA:
            break;
          case Cell.CELL_TYPE_BLANK:
            break;
          case Cell.CELL_TYPE_ERROR:
            break;
          default:
            break;
        }
      }
      list.add(sb.toString());
    }
    return list;
  }

  public static void main(String[] args) {
    String path = "D:/test.xlsx";
    List<String> listS;
    try {
      listS = exportListExcel(new File(path), 0);
      for (int i = 0; i < listS.size(); i++) {
        System.out.println(listS.get(i));
      }
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }

  }
}
 

运行结果集：

 

POI  java 实现导入

/**
 * @Probject Name: testSpringmvc
 * @Path: com.test.mySpringmvc.javaUplodeImportExcel.java
 * @Create By sumeimei
 * @Create In Mar 21, 2019 10:44:14 AM 
 */
package com.test.mySpringmvc.javaUplode;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
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.alibaba.fastjson.JSON;
import com.test.mySpringmvc.until.DateUtils;

/**
 * @Class Name ImportExcel
 * @Author sumeimei
 * @Create In Mar 21, 2019
 */
public class ImportExcel {
  public static void main(String args[]) {

    ImportExcel excelImport = new ImportExcel();
    try {
   // 文件路径
      String filePath = "D:/test.xlsx";
      excelImport.importExcelAction(filePath);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  // 导入Excel数据

  @SuppressWarnings({ "resource"})
  public void importExcelAction(String filePath) throws Exception {
    XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
    XSSFSheet sheet1 = null;
   //获取所有的sheet 
    wookbook.getNumberOfSheets();
    //然后循环每个sheet的每行每列 
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    for(int i =0 ; i < wookbook.getNumberOfSheets(); i ++){
       sheet1=wookbook.getSheetAt(i);
       int rowAll = sheet1.getPhysicalNumberOfRows();
       for (int k = 1; k < rowAll; k++) {
      // 读取左上端单元格
         XSSFRow row = sheet1.getRow(k);
         // 行不为空
         if (row != null) {
           Map<String, Object> map = new HashMap<String, Object>();
           // 姓名
           XSSFCell nameCell = row.getCell(0);
           String name = getValue(nameCell);
           // 性别
           XSSFCell sexCell = row.getCell(1);
           String sex = getValue(sexCell);
           // 年龄
           XSSFCell ageCell = row.getCell(2);
           String age = getValue(ageCell);
           // 出生年月
           XSSFCell birthCell = row.getCell(3);
//         String birth = DateUtils.getHMS(birthCell.getDateCellValue());
           String birth = getValue(birthCell);
           XSSFCell creatCell = row.getCell(4);
           String creat = DateUtils.getHMS(creatCell.getDateCellValue());
           map.put("name", name);
           map.put("sex", sex);
           map.put("age", age);
           map.put("birth", birth);
           map.put("creat", creat);
           list.add(map);
         }
       }
    }
    /**
    //只获取Sheet1
    XSSFSheet sheet = wookbook.getSheet("Sheet1");
    // 获取到Excel文件中的所有行数
    int rows = sheet.getPhysicalNumberOfRows();
    // 遍历行
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    for (int i = 1; i < rows; i++) {
      // 读取左上端单元格
      XSSFRow row = sheet.getRow(i);
      // 行不为空
      if (row != null) {
        Map<String, Object> map = new HashMap<String, Object>();
        // 获取到Excel文件中的所有的列
        int cells = row.getPhysicalNumberOfCells();
        // 姓名
        XSSFCell nameCell = row.getCell(0);
        String name = getValue(nameCell);
        // 性别
        XSSFCell sexCell = row.getCell(1);
        String sex = getValue(sexCell);
        // 年龄
        XSSFCell ageCell = row.getCell(2);
        String age = getValue(ageCell);
        // 出生年月
        XSSFCell birthCell = row.getCell(3);
//      String birth = DateUtils.getHMS(birthCell.getDateCellValue());
        String birth = getValue(birthCell);
        map.put("name", name);
        map.put("sex", sex);
        map.put("age", age);
        map.put("birth", birth);
        list.add(map);
      }

    } * 
     */
    System.out.println("list = " + JSON.toJSONString(list));
  }

  /**
   * @Methods Name getValue
   * @Create In Mar 21, 2019 By ex_18824292876
   * @param nameCell
   * @return String
   */
  private String getValue(XSSFCell nameCell) {
    if(null == nameCell){
      return "";
    }
    if(nameCell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
      return String.valueOf(nameCell.getBooleanCellValue());
    }else if(nameCell.getCellType() == Cell.CELL_TYPE_NUMERIC){
      return String.valueOf(nameCell.getNumericCellValue());
    }else{
      return String.valueOf(nameCell.getStringCellValue());
    }
  }
  }

运行结果集：

excel示例：