导入excel的请求代码,直接copy的代码,改改就能用了
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ResponseBody
public String importExcel(@RequestParam("file") MultipartFile file)
throws Exception {
Json result=new Json();
RestExcelMsg restExcelMsg = new RestExcelMsg();
try {
if (file.isEmpty()) {
result.put("success", false);
result.put("reason", String.format( "导入excel为空文件"));
return result.toString();
}
String type = ImportExcelUtil.checkFileType(file.getOriginalFilename());
if ("".equals(type)) {
result.put("success", false);
result.put("reason", String.format( "excel格式不正确"));
return result.toString();
}
String sheetId = "machine";
List<MgMachine> excelList = null;
excelList = (List<MgMachine>) ImportExcelUtil.parseExcel(sheetId, file.getInputStream(), type, restExcelMsg);
String msg = restExcelMsg.getMsg();
if(!"".equals(msg)){
result.put("success", false);
result.put("reason", msg);
return result.toString();
}else{
MgMachine obj = null;
MgMachine mm = null;
String deptName = "";
int len = excelList.size();
for(int i = 0; i < len; i++){
mm = new MgMachine();
mm.setMachineId(excelList.get(i).getMachineId());
obj = machineService.getByParam(mm);
if(obj != null && !"".equals(obj.getMachineId())){
msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "编号" + "已存在\n";
result.put("success", false);
result.put("reason", msg);
return result.toString();
} else if(obj == null){
mm = new MgMachine();
mm.setIpAddress(excelList.get(i).getIpAddress());
obj = machineService.getByParam(mm);
if(obj != null && !"".equals(obj.getIpAddress())) {
msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + " IP地址" + "已存在\n";
result.put("success", false);
result.put("reason", msg);
return result.toString();
}
}
deptName = departmentService.getName(excelList.get(i).getDeptId());
if(deptName == null || "".equals(deptName)){
msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "编号" + "不存在\n";
result.put("success", false);
result.put("reason", msg);
return result.toString();
}else if(!excelList.get(i).getDeptName().equals(deptName)){
msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "名称和编号不匹配" + "\n";
result.put("success", false);
result.put("reason", msg);
return result.toString();
}
}
}
List<MgMachine> list = new ArrayList<MgMachine>();
if(!CollectionUtils.isEmpty(excelList)){
for(int i =0; i< excelList.size(); i++){
MgMachine obj = excelList.get(i);
obj.setCreateBy(requestContext.getUser().getUsername());
obj.setOperatorName(requestContext.getUser().getUsername());
obj.setCreateOn(date);
obj.setId(UUID.randomUUID().toString());
list.add(obj);
}
}
machineService.batchSave(list);
result.put("success", true);
result.put("reason", "导入成功");
} catch (Exception e) {
e.printStackTrace();
}
return result.toString();
}
package com.xxx.util;
import com.xxx.RestExcelMsg;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.util.ResourceUtils;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;
public class ImportExcelUtil {
public static List parseExcel(String sheetId, InputStream input, String type, RestExcelMsg restExcelMsg) throws Exception {
// 根据sheetId解析xml,获取Excel模板
String msg = "";
Element element = readXML(sheetId);
String clazzString = element.attributeValue("class");
// 从哪行开始解析
int start = Integer.valueOf(element.attributeValue("startRow"));
// 反射创建对象实例
Class<? extends Object> clazz = Class.forName(clazzString);
List<Element> columns = element.elements();
Object instance = null;
// 结果集
List resultList = new ArrayList();
// 要调用的方法
Method method = null;
// 调用Set方法要传递的参数
Class<?>[] args = null;
Cell cell = null;
Workbook wb=null;
Sheet sheet = null;
if("xlsx".equals(type)){
wb=new XSSFWorkbook(input);
sheet = wb.getSheetAt(0);
}else{
wb=new HSSFWorkbook(input);
sheet = wb.getSheetAt(0);
}
try
{
if(sheet.getLastRowNum() <= 0){
msg += "导入excel为空文件\n";
restExcelMsg.setMsg(msg);
return resultList;
}
String cname = "";
String name = "";
int columnSize = columns.size();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Map<String, Map<String, String>> mapList = new HashMap<String, Map<String, String>>();
Map<String, String> map = new HashMap<>();
// 获取第一个Sheet页
for (int i = start; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if(row == null)
{
continue;
}
instance = clazz.newInstance();
StringBuffer buf = new StringBuffer();
for (int j = 0; j < columnSize; j++) {
cname = columns.get(j).attributeValue("cname");
name = columns.get(j).attributeValue("name");
cell = row.getCell(j);
if (cell == null) {
if("N".equals(columns.get(j).attributeValue("isnull"))){
msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "不能为空\n";
}
continue;
}
args = new Class[1];
args[0] = getParameterType(columns.get(j).attributeValue("type"));
Object value = getCellValue(cell,args[0],dateFormat);
//累计字符串内容
buf.append(getStringValue(value));
//Date情况转成String,其他类型保持原样
args[0] = Date.class.equals(args[0]) ? String.class : args[0];
if(value !=null && !"".equals(value.toString().trim()) && value.toString().length() < Integer.valueOf(columns.get(j).attributeValue("length").split(",")[0]) || value.toString().length() > Integer.valueOf(columns.get(j).attributeValue("length").split(",")[1])){
cname = columns.get(j).attributeValue("cname");
msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "字符长度应在" + Integer.valueOf(columns.get(j).attributeValue("length").split(",")[0]) + "到" + Integer.valueOf(columns.get(j).attributeValue("length").split(",")[1]) + "之间";
}else if(!"".equals(columns.get(j).attributeValue("reg")) && Pattern.matches(columns.get(j).attributeValue("reg"), String.valueOf(value))){
msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "格式不正确";
}else{
if("Y".equals(columns.get(j).attributeValue("unique"))){
if(mapList != null && mapList.containsKey(name) && mapList.get(name).containsKey(value)){
msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "重复\n";
}else{
map = new HashMap<>();
map.put(value.toString(), "");
mapList.put(name, map);
}
}
method = clazz.getDeclaredMethod("set" + name, args);
method.invoke(instance, value);
}
}
//整行为空时丢弃该行数据
if ("".equals(buf.toString().trim()))
{
continue;
}
//加到集合中
resultList.add(instance);
}
} catch (Exception e) {
throw e;
} finally {
if (wb != null) {
wb.close();
}
}
System.out.println(msg);
restExcelMsg.setMsg(msg);
return resultList;
}
public static String checkFileType(String filename) {
if ((filename == null) || (filename.length() == 0)) {
return "";
}
String type = "";
int dot = filename.lastIndexOf('.');
if ((dot > -1) && (dot < (filename.length() - 1))) {
type = filename.substring(dot + 1);
}
if (!"xlsx".equalsIgnoreCase(type) && !"xls".equalsIgnoreCase(type)) {
return "";
}
return type;
}
@SuppressWarnings("unchecked")
private static Element readXML(String sheetId) throws DocumentException, FileNotFoundException {
File file = ResourceUtils.getFile("classpath:excel/Sheet.xml");
SAXReader reader = new SAXReader();
Document document = reader.read(file);
Element root = document.getRootElement();
Iterator<Element> iterator = root.elementIterator();
Element element = null;
while (iterator.hasNext()) {
element = iterator.next();
if (sheetId.equals(element.attributeValue("id"))) {
break;
}
}
return element;
}
private static Object getCellValue(Cell cell,Class<? extends Object> clazz,SimpleDateFormat dateFormat) {
Object obj = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR:
obj = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
//用户自定义类型检测
if(String.class.equals(clazz))
{
DecimalFormat df = new DecimalFormat("#.#########"); // 数字格式,防止长数字成为科学计数
obj = df.format(cell.getNumericCellValue());
}else if(Date.class.equals(clazz))
{
obj = dateFormat.format(cell.getDateCellValue());
}else if(Double.class.equals(clazz)) {
obj = cell.getNumericCellValue();
}else if(Integer.class.equals(clazz)) {
DecimalFormat df = new DecimalFormat("#.#########"); // 数字格式,防止长数字成为科学计数
String val = df.format(cell.getNumericCellValue());
obj = Integer.parseInt(val);
}
break;
case Cell.CELL_TYPE_STRING:
String cellValue = cell.getStringCellValue();
if (Double.class.equals(clazz)) {
Double d = Double.valueOf(cellValue);
obj = d;
}else if(Integer.class.equals(clazz)) {
obj = Integer.parseInt(cellValue);
}else {
obj=cellValue;
}
break;
default:
break;
}
return obj;
}
/**
* 获取参数类型,默认为String
*
* @param parameterType
* @return
*/
private static Class<?> getParameterType(String parameterType) {
switch (parameterType) {
case "Byte":
return Byte.class;
case "Short":
return Short.class;
case "Integer":
return Integer.class;
case "Long":
return Long.class;
case "Float":
return Float.class;
case "Double":
return Double.class;
case "char":
return char.class;
case "String":
return String.class;
case "Date":
return Date.class;
default:
return String.class;
}
}
/**
* 获取字符串内容
* @param obj
* @return
*/
private static String getStringValue(Object obj)
{
if (obj != null)
{
return String.valueOf(obj);
}
return "";
}
}
public class RestExcelMsg {
private String msg;
public RestExcelMsg() {
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
<?xml version="1.0" encoding="UTF-8"?>
<import>
<!-- type类型:Byte,Short,Integer,Long,Float,Double,char,String,缺省为String -->
<sheet id="machine" startRow="1" class="com.xxx.MgMachine">
<column name="DeptId" type="String" isnull="N" length="2,20" unique="N" reg="" cname="机编号"></column>
<column name="DeptName" type="String" isnull="N" length="2,50" unique="N" reg="" cname="名称"></column>
<column name="MachineId" type="String" isnull="N" length="2,20" unique="Y" reg="" cname="编号"></column>
<column name="IpAddress" type="String" isnull="N" length="7,15" unique="Y" reg="" cname="IP地址"></column>
<column name="Description" type="String" isnull="Y" length="2,200" unique="N" reg="" cname="描述"></column>
<column name="WorkingDays" type="String" isnull="N" length="2,20" unique="N" reg="" cname="工作日"></column>
<column name="WorkingHours" type="String" isnull="N" length="2,20" unique="N" reg="" cname="工作时间"></column>
<column name="ServiceTime" type="String" isnull="N" length="2,20" unique="N" reg="" cname="上传时间"></column>
</sheet>
</import>
这里需要注意的是com.xxx.MgMachine实体类中的私有属性和Sheet.xml中的column中的name属性必须保持一致性,cname就是属性的名称。
其中type表示数据类型,isnull,length,unique是否唯一,reg是正则表达式,cname就是名称,对于校验excel导入的数据格式有很好的作用,其实很多导入excel最重要的不是导入,而是数据校验,重复性,格式,长度,唯一性,是否为空这些才是最主要的,demo没有单独写,这些代码已经足够了,代码肯定有不完善的地方,大家多多包涵,希望能够帮助到各位,写成更好的代码。
excel的Sheet的路径
pom依赖
<!--execl解析-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
打赏二维码,多谢支持