使用POI读取Excel表格导入
1.封装一个utils类
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ImportExcel {
public static boolean isXls(String fileName){
if(fileName.matches("^.+\\.(?i)(xls)$")){
return true;
}else if(fileName.matches("^.+\\.(?i)(xlsx)$")){
return false;
}else{
throw new RuntimeException("格式不对");
}
}
public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) throws Exception{
boolean ret = isXls(fileName);
Workbook workbook = null;
if(ret){
workbook = new HSSFWorkbook(inputStream);
}else{
workbook = new XSSFWorkbook(inputStream);
}
Sheet sheet = workbook.getSheetAt(0);
Row titleRow = sheet.getRow(0);
int lastRowNum = sheet.getLastRowNum();
int lastCellNum = titleRow.getLastCellNum();
List<Map<String, Object>> list = new ArrayList<>();
for(int i = 1; i <= lastRowNum; i++ ){
Map<String, Object> map = new HashMap<>();
Row row = sheet.getRow(i);
for(int j = 0; j < lastCellNum; j++){
String key = titleRow.getCell(j).getStringCellValue();
Cell cell = row.getCell(j);
cell.setCellType(CellType.STRING);
map.put(key, cell.getStringCellValue());
}
list.add(map);
}
workbook.close();
return list;
}
}
2.在controller层调用
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
@Controller
public class ImporExcelController {
@Autowired
private SysUserService userService = null;
@RequestMapping("/import.do")
@ResponseBody
public JsonBean importExcel(@RequestParam MultipartFile excelFile){
try {
String fileName = excelFile.getOriginalFilename();
InputStream inputStream = excelFile.getInputStream();
List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);
ObjectMapper objMapper = new ObjectMapper();
String infos = objMapper.writeValueAsString(sourceList);
List<SysUser> list = objMapper.readValue(infos, new TypeReference<List<SysUser>>() {});
userService.addUserBatch(list);
return JsonUtils.createJsonBean(1, null);
} catch (Exception e) {
e.printStackTrace();
return JsonUtils.createJsonBean(0, e.getMessage());
}
}
}