JAVA导入(极简)

本人上班摸鱼时间写了一个极简的导入供新手参考 拷贝即可用,将查询方法，和实体类换成自己需要的就可以；   

引入依赖  使用的easypoi  复杂功能可参考官方文档http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8

<!-- easypoi功能 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
</dependency>

 

实现代码：

@Override
    public String doImport (MultipartFile file) {
        try {
            ImportExcel ei = new ImportExcel(file, 0, 0);
            List<String> titleRow = new ArrayList<>();
            for (int i = ei.getDataRowNum() - 1; i <= ei.getLastDataRowNum(); i++) {
                Row row = ei.getRow(i);
                Emp temp = new Emp ();
                for (int j = 0; j < ei.getLastCellNum(); j++) {
                    String cellValue = ei.getCellValue(row, j).toString();
                    if(i == 0) {
                        // 获取标题
                        titleRow.add(cellValue);
                    } else {
                        // 拼接实体类
                        if ("名称".equals(titleRow.get(j))) {
                            temp.setName(cellValue);
                        }

                        if ("主键".equals(titleRow.get(j))) {
                            temp.setId(cellValue);
                        }
                    }
                }
                insert(temp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "ok";
    }

 

用到的工具类

ImportExcel.java

 

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


public class ImportExcel {
   
   private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
         
   /**
    * 工作薄对象
    */
   private Workbook wb;
   
   /**
    * 工作表对象
    */
   private Sheet sheet;
   
   /**
    * 标题行号
    */
   private int headerNum;
   
   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(String fileName, int headerNum) 
         throws InvalidFormatException, IOException {
      this(new File(fileName), headerNum);
   }
   
   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(File file, int headerNum) 
         throws InvalidFormatException, IOException {
      this(file, headerNum, 0);
   }

   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @param sheetIndex 工作表编号
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(String fileName, int headerNum, int sheetIndex) 
         throws InvalidFormatException, IOException {
      this(new File(fileName), headerNum, sheetIndex);
   }
   
   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @param sheetIndex 工作表编号
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(File file, int headerNum, int sheetIndex) 
         throws InvalidFormatException, IOException {
      this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
   }
   
   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @param sheetIndex 工作表编号
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
         throws InvalidFormatException, IOException {
      this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
   }

   /**
    * 构造函数
    * @param headerNum 标题行号，数据行号=标题行号+1
    * @param sheetIndex 工作表编号
    * @throws InvalidFormatException 
    * @throws IOException 
    */
   public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
         throws InvalidFormatException, IOException {
      if (StringUtils.isBlank(fileName)){
         throw new RuntimeException("导入文档为空!");
      }else if(fileName.toLowerCase().endsWith("xls")){    
         this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
           this.wb = new XSSFWorkbook(is);
        }else{  
           throw new RuntimeException("文档格式不正确!");
        }  
      if (this.wb.getNumberOfSheets()<sheetIndex){
         throw new RuntimeException("文档中没有工作表!");
      }
      this.sheet = this.wb.getSheetAt(sheetIndex);
      this.headerNum = headerNum;
      log.debug("Initialize success.");
   }
   
   /**
    * 获取行对象
    * @param rownum
    * @return
    */
   public Row getRow(int rownum){
      return this.sheet.getRow(rownum);
   }

   /**
    * 获取数据行号
    * @return
    */
   public int getDataRowNum(){
      return headerNum+1;
   }
   
   /**
    * 获取最后一个数据行号
    * @return
    */
   public int getLastDataRowNum(){
      return this.sheet.getLastRowNum()+headerNum;
   }
   
   /**
    * 获取最后一个列号
    * @return
    */
   public int getLastCellNum(){
      return this.getRow(headerNum).getLastCellNum();
   }
   
   /**
    * 获取单元格值
    * @param row 获取的行
    * @param column 获取单元格列号
    * @return 单元格值
    */
   public Object getCellValue(Row row, int column) {
      Object val = "";
      try {
         Cell cell = row.getCell(column);
         if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
               // val = cell.getNumericCellValue();
               // 当excel 中的数据为数值或日期是需要特殊处理
               if (HSSFDateUtil.isCellDateFormatted(cell)) {
                  double d = cell.getNumericCellValue();
                  Date date = HSSFDateUtil.getJavaDate(d);
                  SimpleDateFormat dformat = new SimpleDateFormat(
                        "yyyy-MM-dd");
                  val = dformat.format(date);
               } else {
                  NumberFormat nf = NumberFormat.getInstance();
                  nf.setGroupingUsed(false);// true时的格式：1,234,567,890
                  val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下
               }
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
               val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
               val = cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
               val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
               val = cell.getErrorCellValue();
            }
         }
      } catch (Exception e) {
         return val;
      }
      return val;
   }
}