第一步:导入jar包:
Maven项目只需要在pom.xml文件里做如下配置:
<org.apache.poi.version>3.15</org.apache.poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${org.apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>${org.apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${org.apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${org.apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${org.apache.poi.version}</version>
</dependency>
第二部构件实体类:(直接上代码。简单明了)
①实体:
public class DevOpsManage extends BaseEntity {
private String provinceId; //省份id
@ExcelVOAttribute(column = "A", name = "省份")
private String province; //省份
public String getProvinceId() {
return provinceId;
}
public void setProvinceId(String provinceId) {
this.provinceId = provinceId;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
}
@ExcelVOAttribute(column = "A", name = "省份")注解对应你excel中的数据。column对应是表格中列,name对应的表格中的表头。
②impl方法实现
@Override
public void imports(MultipartFile file) {
ExcelUtil<DevOpsManage> util = new ExcelUtil<DevOpsManage>(DevOpsManage.class);// 创建工具类.
byte[] excelDataBytes = null;
int type = -1;
try {
String extension = FilenameUtils.getExtension(file.getOriginalFilename());
if (!extension.equalsIgnoreCase("xlsx") && file.isEmpty()) {// 是否是文件
throw new ServiceException("请选择xlsx类型的Excel的文件上传!", null);
}
type = 2;//xlsx格式excel
excelDataBytes = file.getBytes();
if (null == excelDataBytes) {
// 判断是不是excel文件,并且不为空---没实现
throw new ServiceException("文件为空,请选择正确的文件!", null);
}
} catch (IOException e) {
e.printStackTrace();
}
InputStream inputStream1 = null;
try {
inputStream1 = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
throw new ServiceException("系统异常!", null);
}
List<DevOpsManage> importExcel = util.importExcel(null, inputStream1, 1, type);
//此处做一解释:util是工具类,importExcel方法参数:第一个指的是excel中的sheet的名称,第二个指的是输入流,第三个指的是开始读取数据行数,第四个就是上面指定的参数。
for (DevOpsManage devOpsManage : importExcel) {
if (devOpsManage != null && !"".equals(devOpsManage)) {
try{
devOpsManage.setId(IdGen.uuid());
if (devOpsManage.getSubTime() != null && !"".equals(devOpsManage.getSubTime())) {
Date date2 = DateUtils.parseDate(devOpsManage.getSubTime(),"yyyy-MM-dd");
将String类型的日期转换为Date类型的时间。
devOpsManage.setSubmissionTime(date2);
} else {
devOpsManage.setSubmissionTime(new Date());
}
if (devOpsManage.getSolvedTimes() != null && !"".equals(devOpsManage.getSolvedTimes())) {
Date date1 = DateUtils.parseDate(devOpsManage.getSolvedTimes(),"yyyy-MM-dd");
devOpsManage.setSolvedTime(date1);
} else {
devOpsManage.setSolvedTime(new Date());
}
devOpsManage.setCreatetime(devOpsManage.getSolvedTime());
UserInfo userInfo = getCurrentUserInfo();
devOpsManage.setCreateuserName(userInfo.getUserName());//导入人名称
devOpsManage.setCreateuserId(userInfo.getUserId());//导入人id
devOpsManageService.add(devOpsManage);
}catch (Exception e){
throw new ServiceException("导入数据有不合法信息存在,请核对!!!", null);
}
}
}
}
将业务实现写在service层以便于事务管理。此处如果excel表格中数据量比较大,可以选择批量上传!效率高的不是一点点。具体的代码实现最后再copy!
③工具类方法
主要针对07版本以上xlsx格式excel导入。
public List<T> importExcel(String sheetName, InputStream input,Integer startRow,int type) {
int maxCol = 0;
List<T> list = new ArrayList<T>();
try {
XSSFWorkbook workbook = new XSSFWorkbook (input);
XSSFSheet sheet = null;
if (null != sheetName && !sheetName.trim().equals("")) {
sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
}
if (sheet == null) {
sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
}
int rows = sheet.getPhysicalNumberOfRows();
if (rows > 0) {
// 有数据时才处理
// Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
List<Field> allFields = getMappedFiled(clazz, null);
Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
for (Field field : allFields) {
// 将有注解的field存放到map中.
if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
ExcelVOAttribute attr = field
.getAnnotation(ExcelVOAttribute.class);
int col = getExcelCol(attr.column());// 获得列号
maxCol = Math.max(col, maxCol);
// System.out.println(col + "====" + field.getName());
field.setAccessible(true);// 设置类的私有字段属性可访问.
fieldsMap.put(col, field);
}
}
int i = 1;
if(null != startRow && 0<startRow){
i = startRow;
}
for (; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
XSSFRow row = sheet.getRow(i);
if(row==null||"".equals(row)){
continue;
}
// int cellNum = row.getPhysicalNumberOfCells();
// int cellNum = row.getLastCellNum();
int cellNum = maxCol;
T entity = null;
for (int j = 0; j <= cellNum; j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
continue;
}
int cellType = cell.getCellType();
String c = "";
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
c = sdf.format(date);
}else { //纯数字
c = String.valueOf(cell.getNumericCellValue());
}
} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
c = String.valueOf(cell.getBooleanCellValue());
} else {
c = cell.getStringCellValue();
}
if (c == null || c.equals("")) {
continue;
}
entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
// System.out.println(cells[j].getContents());
Field field = fieldsMap.get(j);// 从map中得到对应列的field.
if (field==null) {
continue;
}
// 取得类型,并根据对象类型设置值.
Class<?> fieldType = field.getType();
if (String.class == fieldType) {
field.set(entity, String.valueOf(c));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
field.set(entity, Integer.parseInt(c));
} else if ((Long.TYPE == fieldType)
|| (Long.class == fieldType)) {
field.set(entity, Long.valueOf(c));
} else if ((Float.TYPE == fieldType)
|| (Float.class == fieldType)) {
field.set(entity, Float.valueOf(c));
} else if ((Short.TYPE == fieldType)
|| (Short.class == fieldType)) {
field.set(entity, Short.valueOf(c));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(entity, Double.valueOf(c));
} else if (Character.TYPE == fieldType) {
if ((c != null) && (c.length() > 0)) {
field.set(entity, Character
.valueOf(c.charAt(0)));
}
} else if( BigDecimal.class == fieldType) {
field.set(entity, new BigDecimal(c));
}
}
if (entity != null) {
list.add(entity);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
return list;
}
下班了,暂时写这么多吧!!!复制粘贴就可以用!!!!!!
忘记批量添加数据到数据库操作了:
后端只需要将数据存储在list集合中。mapping映射文件中的sql如下
mybits配置文件定义方法
<insert id="insertBatch" parameterType="java.util.List" > insert into person ( A,B,C ) values <foreach collection="list" item="item" index="index" separator=","> (#{item.a},#{item.b},#{item.c}) </foreach> </insert>
886 下班了