package com.bj58.saletb.news.utils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

;

/**
*
*

• • 用于读取office文件 -->
    
    org.apache.poi
    poi
    3.9
    
    同时注意在配置文件下放入excel/*.xls文件

• Created by ******** on 20160905

• 通读excel，由于excel的读取是按照字段读取，故需要设置边界量，表明已经到达边界
  /
  public class ImportExcelUtil {
  private final static Logger log = Logger.getLogger(ImportExcelUtil.class);
  private static final DataFormatter FORMATTER = new DataFormatter();/** 格式化数值型单元格内容 */

  /**

  • (⊙o⊙)… excel表格中的数据按行转成map集合
  • 暂不支持按列转集合，待开发行转列
  • @param filePath 路径
  • @param sheetNum 第几个sheet档
  • @param lists 转换集合，在外部实例化，不能为null
  • /
    public static void getLineMessFromExcel(String filePath,int sheetNum,List<List> lists){
    try {
    readExcel(filePath,sheetNum,lists);
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    public static void readExcel(String filePath,int sheetNum,List<List> lists){
    InputStream is = null;
    XSSFWorkbook wb = null;
    if (!validateFileType(filePath)) {
    throw new RuntimeException("文件格式不正确！");
    }
    try {
    is = new FileInputStream(filePath);
    wb = new XSSFWorkbook(is);
    } catch (Exception e) {
    log.error("读取EXCEL文档失败！", e);
    e.printStackTrace();
    throw new RuntimeException("读取EXCEL文档失败！", e);
    }
    //多个sheet，具体看取哪一个
    XSSFSheet sheet = wb.getSheetAt(sheetNum);
    //获取数据
    getMess(sheet,lists);
    //关闭资源
    closeInputStream(is);
    }
    /
  • @param sheet excel内的sheet
  • @param lists 每行信息
  • **/
    public static void getMess(XSSFSheet sheet,List<List> lists){
    try{
    List lineList = null;
    //1、遍历行数
    for (int i=sheet.getFirstRowNum(); i<sheet.getPhysicalNumberOfRows(); i++) {
    if(lists!=null){
    lineList = new ArrayList();
    }
    //2、遍历列数
    Row row = sheet.getRow(i);
    if(row!=null) {
    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
    //即使为null也存入list中，以便一一对应数据
    if (lineList != null) {
    lineList.add(row.getCell(j) == null ? "" : FORMATTER.formatCellValue(row.getCell(j)) + "");
    }
    }
    if (lists != null) {
    lists.add(lineList);
    }
    }
    }
    }catch (Throwable e){
    log.error("装载Excel数据出错！", e);
    e.printStackTrace();
    throw new RuntimeException("装载Excel数据出错！", e);
    }
    }
    public static boolean validateFileType(String filePath) {
    File excel = new File(filePath);
    String fileName = excel.getName();
    if (fileName != null && !"".equals(fileName.trim())) {
    if (fileName.endsWith(".xlsx") || fileName.endsWith(".xls")) {
    return true;
    }
    }
    return false;
    }
    public static void closeInputStream(InputStream is) {
    if (is != null) {
    try {
    is.close();
    log.debug("......输入流关闭成功");
    } catch (IOException e) {
    log.error(e);
    e.printStackTrace();
    }
    }
    }
    }