本人上班摸鱼时间写了一个极简的导入供新手参考 拷贝即可用,将查询方法,和实体类换成自己需要的就可以;
引入依赖 使用的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; } }