gradle
compile group: 'org.apache.poi', name: 'poi', version: '3.9';
compile group: 'org.apache.poi', name: 'poi-excelant', version: '3.9';
compile group: 'org.apache.poi', name: 'poi-scratchpad', version: '3.9';
Controller
@PostMapping(value = "/excelImport")
@ResponseBody
public HttpResult excelImport(@RequestParam("file") MultipartFile multipartFile, @RequestParam("deptId") Long deptId) throws IOException, InterruptedException {
String fileName = multipartFile.getOriginalFilename();
if (!fileName.endsWith("xlsx") && !fileName.endsWith("xls")) {
return HttpResult.error("请上传excel文件");
}
String result = sysSubjectService.excelImport(multipartFile);
if (!"succeed".equals(result)) {
return HttpResult.error(result);
}
return HttpResult.ok(result);
}
Service
@Override
public String excelImport(MultipartFile multipartFile) throws IOException {
String fileName = multipartFile.getOriginalFilename();
Workbook workbook = null;
InputStream is = multipartFile.getInputStream();
if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(is);
}
if (workbook != null) {
List<SysSubject> subjectList = new ArrayList<>();
//读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
SysSubject sysSubject = new SysSubject();
// XSSFRow 代表一行数据
Row row = sheet.getRow(rowIndex);
//获取单元格信息
Cell dateCell = row.getCell(0);
dateCell.setCellType(Cell.CELL_TYPE_STRING);
String code = ExcelCellToStringUtil.getStringCellValue(dateCell);
code = code.replaceAll("\s*", "").replaceAll("[\u00A0]+", "");
//防空异常 必加
if (null == code || "".equals(code)) {
continue;
}
//科目代码
sysSubject.setCode(code);
subjectList.add(sysSubject);
}
sysSubjectMapper.insertBatchSubject(tableName, subjectList);
}
return "succeed";
}
RedisUtils
package cn.edu.qfnu.soft.common.util;
import java.io.Serializable;
import java.util.List;
import java.util.Set;
import java.util.concurrent.TimeUnit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.HashOperations;
import org.springframework.data.redis.core.ListOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.SetOperations;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.data.redis.core.ZSetOperations;
import org.springframework.stereotype.Service;
/**
* redis操作工具类
* @author 吕嘉鸣
*/
@Service
public class RedisUtils {
@Autowired
private RedisTemplate redisTemplate;
/**
* 写入缓存
*
* @param key
* @param value
* @return
*/
public boolean set(final String key, Object value) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
operations.set(key, value);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 写入缓存设置时效时间
*
* @param key
* @param value
* @return
*/
public boolean set(final String key, Object value, Long expireTime, TimeUnit timeUnit) {
boolean result = false;
try {
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
operations.set(key, value);
redisTemplate.expire(key, expireTime, timeUnit);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 批量删除对应的value
*
* @param keys
*/
public void remove(final String... keys) {
for (String key : keys) {
remove(key);
}
}
/**
* 批量删除key
*
* @param pattern
*/
public void removePattern(final String pattern) {
Set<Serializable> keys = redisTemplate.keys(pattern);
if (keys.size() > 0) {
redisTemplate.delete(keys);
}
}
/**
* 删除对应的value
*
* @param key
*/
public void remove(final String key) {
if (exists(key)) {
redisTemplate.delete(key);
}
}
/**
* 判断缓存中是否有对应的value
*
* @param key
* @return
*/
public boolean exists(final String key) {
return redisTemplate.hasKey(key);
}
/**
* 读取缓存
*
* @param key
* @return
*/
public Object get(final String key) {
Object result = null;
ValueOperations<Serializable, Object> operations = redisTemplate.opsForValue();
result = operations.get(key);
return result;
}
/**
* 哈希 添加
*
* @param key
* @param hashKey
* @param value
*/
public void hmSet(String key, Object hashKey, Object value) {
HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
hash.put(key, hashKey, value);
}
/**
* 哈希获取数据
*
* @param key
* @param hashKey
* @return
*/
public Object hmGet(String key, Object hashKey) {
HashOperations<String, Object, Object> hash = redisTemplate.opsForHash();
return hash.get(key, hashKey);
}
/**
* 列表添加
*
* @param k
* @param v
*/
public void lPush(String k, Object v) {
ListOperations<String, Object> list = redisTemplate.opsForList();
list.rightPush(k, v);
}
/**
* 列表获取
*
* @param k
* @param l
* @param l1
* @return
*/
public List<Object> lRange(String k, long l, long l1) {
ListOperations<String, Object> list = redisTemplate.opsForList();
return list.range(k, l, l1);
}
/**
* 集合添加
*
* @param key
* @param value
*/
public void add(String key, Object value) {
SetOperations<String, Object> set = redisTemplate.opsForSet();
set.add(key, value);
}
/**
* 集合获取
*
* @param key
* @return
*/
public Set<Object> setMembers(String key) {
SetOperations<String, Object> set = redisTemplate.opsForSet();
return set.members(key);
}
/**
* 有序集合添加
*
* @param key
* @param value
* @param scoure
*/
public void zAdd(String key, Object value, double scoure) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
zset.add(key, value, scoure);
}
/**
* 有序集合获取
*
* @param key
* @param scoure
* @param scoure1
* @return
*/
public Set<Object> rangeByScore(String key, double scoure, double scoure1) {
ZSetOperations<String, Object> zset = redisTemplate.opsForZSet();
return zset.rangeByScore(key, scoure, scoure1);
}
}
ExcelCellToStringUtil
package cn.edu.qfnu.soft.common.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
/**
* excel单元格数据工具类
* @author ljm
*/
public class ExcelCellToStringUtil {
/**
* 获取excel单元格数据内容中为字符串类型的数据
* @author ljm
* @param cell
* @return
*/
public static String getStringCellValue(Cell cell)
{
String strCell = "";
if(cell == null)
{
return "";
}else{
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
DataFormatter dataFormatter = new DataFormatter();
strCell = dataFormatter.formatCellValue(cell);
break;
case Cell.CELL_TYPE_STRING: //字符串
strCell = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
strCell = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
strCell = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
strCell = "非法字符";
break;
default:
strCell = "未知类型";
break;
}
}
if("".equals(strCell)||strCell==null)
{
return "";
}
return strCell;
}
}