1.通过表头headers，属性字段exportFields 配置导入导出功能
设备导出
Controller层
//Controller
@GetMapping(value = "/system/exportEquipment", name = "导出excel")
public void exportEquipment(@RequestParam(value = "params", required = false) String params, HttpServletResponse response) throws UnsupportedEncodingException {
Gson gson = new Gson();
SfEqBasicInfoModel entity = gson.fromJson(params, SfEqBasicInfoModel.class);
sfEqBasicInfoService.exportEquipment(entity, response);
}
@PostMapping(value = "/system/importEquipment", name = "导入excel")
public Object importUser(HttpServletRequest request) {
MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;
MultipartFile file = mulRequest.getFile("fileName");
Map map = sfEqBasicInfoService.importEquipment(file);
return JsonData.buildSuccess(map);
}
service层
static String[] headers = {"高校编号", "高校名称", "设备编号", "设备名称",
"购置日期",
"国别", "生产厂家", "联系人", "联系电话", "型号",
"规格", "总价", "组织机构编码","组织机构", "设备分类", "安置地点",
"是否涉密", "设备现状","功能范围", "技术指标"};
static String[] exportFields = {"universityCode", "universityName", "eqId", "eqName",
"buyDate",
"countryName", "productFactory", "contact", "contactTelephone","modelNumber",
"specification", "totalPrice","deptId","deptName", "typeName", "location",
"isAuth", "presentSituationName", "rangeApplication", "technicalIndicators"};
@Override
public void exportEquipment(SfEqBasicInfoModel model, HttpServletResponse response) {
try {
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition","attachment;filename=" + new String("设备信息.xlsx".getBytes("gbk"), "iso8859-1"));
//查询数据接口
List<SfEqBasicInfo> dataList = sfEqBasicInfoMapper.selectRecordList(model);
ExcelUtil.doExport(headers, exportFields, dataList, "设备信息", response.getOutputStream());
} catch (Exception e) {
logger.info(e.getMessage());
}
}
@Override
public Map importEquipment(MultipartFile file) {
Map<String, Object> map = new HashMap<String, Object>();
InputStream is = null;
List<SfEqBasicInfo> eqList = new ArrayList<>();
List<SfEqPerfectInfo> eqPerfectList = new ArrayList<>();
try {
is = file.getInputStream();
String fileName = file.getOriginalFilename();
Workbook workbook = null;
if (fileName.endsWith("xlsx")) {
//Excel 2007
workbook = new XSSFWorkbook(is);
//workbook = new SXSSFWorkbook(new XSSFWorkbook(is));
} else if (fileName.endsWith("xls")) {
//Excel 2003
workbook = new HSSFWorkbook(is);
}
Map<String, String> headerMap = new HashMap<>();
for (int i = 0; i < headers.length; i++) {
headerMap.put(headers[i], exportFields[i]);
}
//解析excel
ArrayList<Map<String, Object>> maps = ExcelUtil.readExcel(workbook, headerMap);
Gson gson = GSONUtil.create();//支撑字符串转时间类型 LocalDateTime
Type type = new TypeToken<ArrayList<SfEqBasicInfo>>() {
}.getType();
eqList = gson.fromJson(gson.toJson(maps), type);
//方法一：使用流遍历操作
for (SfEqBasicInfo info : eqList) {
SfEqPerfectInfo sfEqPerfectInfo = new SfEqPerfectInfo();
sfEqPerfectInfo.setEqId(info.getEqId());
sfEqPerfectInfo.setUniversityCode(info.getUniversityCode());
sfEqPerfectInfo.setTechnicalIndicators(info.getTechnicalIndicators());
sfEqPerfectInfo.setRangeApplication(info.getRangeApplication());
sfEqPerfectInfo.setJprofiles(info.getJprofiles());
sfEqPerfectInfo.setJcontentinfo(info.getJcontentinfo());
eqPerfectList.add(sfEqPerfectInfo);
}
List<List<SfEqBasicInfo>> eqListSub = ExcelUtil.getSubList(100, eqList);
List<List<SfEqPerfectInfo>> eqPerfectListSub = ExcelUtil.getSubList(100, eqPerfectList);
//清空临时表,插入临时表
sfEqBasicInfoMapper.truncateTemp();
sfEqPerfectInfoMapper.truncateTemp();
eqListSub.forEach(
c -> {
sfEqBasicInfoMapper.insertList(c);
}
);
eqPerfectListSub.forEach(
c -> {
sfEqPerfectInfoMapper.insertList(c);
}
);
//更新正式表
sfEqBasicInfoMapper.mergeList();
sfEqPerfectInfoMapper.mergeList();
map.put("code", 200);
map.put("msg", "导入成功");
} catch (Exception e) {
e.printStackTrace();
map.put("message", e.getMessage());
map.put("code", 201);
map.put("msg", "导入失败");
} finally {
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
return map;
}
}
ExcelUtil工具类
//
public class ExcelUtil {
/**
* 将list拆分成指定数量的小list
* 注: 使用的subList方式,返回的是list的内部类,不可做元素的删除,修改,添加操作
*
* @param length 数量
* @param list 大list
*/
public static <T> List<List<T>> getSubList(int length, List<T> list) {
int size = list.size();
int temp = size / length + 1;
boolean result = size % length == 0;
List<List<T>> subList = new ArrayList<>();
for (int i = 0; i < temp; i++) {
if (i == temp - 1) {
if (result) {
break;
}
subList.add(list.subList(length * i, size));
} else {
subList.add(list.subList(length * i, length * (i + 1)));
}
}
return subList;
}
}
private static <T> void createSheet(SXSSFWorkbook wb, String[] exportFields, String[] headers, Collection<T> dataList, String fileName, int maxBuff) throws NoSuchFieldException, IllegalAccessException, IOException {
Sheet sh = wb.createSheet(fileName);
CellStyle style = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
//创建表头
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 11);//设置字体大小
style.setFont(font);//选择需要用到的字体格式
style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setFont(font);//选择需要用到的字体格式
style2.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景色
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平向下居中
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
Row headerRow = sh.createRow(0); //表头
int headerSize = headers.length;
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
Cell cell = headerRow.createCell(cellnum);
cell.setCellStyle(style);
sh.setColumnWidth(cellnum, 4000);
cell.setCellValue(headers[cellnum]);
}
int rownum = 0;
Iterator<T> iterator = dataList.iterator();
while (iterator.hasNext()) {
T data = iterator.next();
Row row = sh.createRow(rownum + 1);
Field[] fields = getExportFields(data.getClass(), exportFields);
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
Cell cell = row.createCell(cellnum);
cell.setCellStyle(style2);
Field field = fields[cellnum];
setData(field, data, field.getName(), cell);
}
rownum = sh.getLastRowNum();
// 大数据量时将之前的数据保存到硬盘
if (rownum % maxBuff == 0) {
((SXSSFSheet) sh).flushRows(maxBuff); // 超过100行后将之前的数据刷新到硬盘
}
}
}
public static <T> void doExport(String[] headers, String[] exportFields, Collection<T> dataList,
String fileName, OutputStream outputStream) {
int maxBuff = 100;
// 创建excel工作文本，100表示默认允许保存在内存中的行数
SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
try {
createSheet(wb, exportFields, headers, dataList, fileName, maxBuff);
if (outputStream != null) {
wb.write(outputStream);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
/**
* 根据属性设置对应的属性值
*
* @param dataField 属性
* @param object 数据对象
* @param property 表头的属性映射
* @param cell 单元格
* @param <T>
* @return
* @throws IllegalAccessException
* @throws NoSuchFieldException
*/
private static <T> void setData(Field dataField, T object, String property, Cell cell)
throws IllegalAccessException, NoSuchFieldException {
dataField.setAccessible(true); //允许访问private属性
Object val = dataField.get(object); //获取属性值
Sheet sh = cell.getSheet(); //获取excel工作区
CellStyle style = cell.getCellStyle(); //获取单元格样式
int cellnum = cell.getColumnIndex();
if (val != null) {
if (dataField.getType().toString().endsWith("String")) {
cell.setCellValue((String) val);
} else if (dataField.getType().toString().endsWith("Integer") || dataField.getType().toString().endsWith("int")) {
cell.setCellValue((Integer) val);
} else if (dataField.getType().toString().endsWith("Long") || dataField.getType().toString().endsWith("long")) {
cell.setCellValue(val.toString());
} else if (dataField.getType().toString().endsWith("Double") || dataField.getType().toString().endsWith("double")) {
cell.setCellValue((Double) val);
} else if (dataField.getType().toString().endsWith("Date")) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(format.format((Date) val));
}else if (dataField.getType().toString().endsWith("LocalDateTime")) {
//LocalDateTime 转成字符串 导出
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
String createTime = dateTimeFormatter.format((LocalDateTime)val);
cell.setCellValue(createTime);
}else if (dataField.getType().toString().endsWith("List")) {
List list1 = (List) val;
int size = list1.size();
for (int i = 0; i < size; i++) {
//加1是因为要去掉点号
int start = property.indexOf(dataField.getName()) + dataField.getName().length() + 1;
String tempProperty = property.substring(start, property.length());
Field field = getDataField(list1.get(i), tempProperty);
Cell tempCell = cell;
if (i > 0) {
int rowNum = cell.getRowIndex() + i;
Row row = sh.getRow(rowNum);
if (row == null) {//另起一行
row = sh.createRow(rowNum);
//合并之前的空白单元格（在这里需要在header中按照顺序把list类型的字段放到最后，方便显示和合并单元格）
for (int j = 0; j < cell.getColumnIndex(); j++) {
sh.addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + size - 1, j, j));
Cell c = row.createCell(j);
c.setCellStyle(style);
}
}
tempCell = row.createCell(cellnum);
tempCell.setCellStyle(style);
}
//递归传参到单元格并获取偏移量（这里获取到的偏移量都是第二层后list的偏移量）
setData(field, list1.get(i), tempProperty, tempCell);
}
} else {
if (property.contains(".")) {
String p = property.substring(property.indexOf(".") + 1, property.length());
Field field = getDataField(val, p);
setData(field, val, p, cell);
} else {
cell.setCellValue(val.toString());
}
}
}
}
DAO层 mapper.xml
<delete id="truncateTemp">
truncate table sf_eq_basic_info_t
</delete>
<insert id="insertList" parameterType="java.util.List">
insert ALL into sf_eq_basic_info_t(
eq_ID, eq_Name,buy_Date,model_Number, specification, total_Price,
product_Factory, country_Name,universityCode,universityName
,contact,contacttelephone,MODEL_NUMBER,DEPT_ID,DEPT_NAME,
TYPE_NAME,location,PRESENT_SITUATION_NAME,RZ_STATUS
)
<foreach collection="list" item="info" index="index" separator="union all">
select
#{info.eqId},#{info.eqName},#{info.buyDate},#{info.modelNumber},#{info.specification},#{info.totalPrice},#{info.productFactory},#{info.countryName},#{info.universityCode},#{info.universityName}
,#{info.contact},#{info.contactTelephone},#{info.modelNumber},#{info.deptId},#{info.deptName},
#{info.typeName},#{info.location},#{info.presentSituationName},'2'
RZ_STATUS
from dual
</foreach>
</insert>
<update id="mergeList">
merge into sf_eq_basic_info a
using sf_eq_basic_info_t b on (a.eq_id = b.eq_id and a.universitycode = b.universitycode)
when matched then
update set a.eq_name = b.eq_name
,a.buy_Date = b.buy_Date
,a.model_number = b.model_number
,a.specification = b.specification
,a.total_price = b.total_price
,a.product_factory = b.product_factory
,a.country_name = b.country_name
,a.universityname = b.universityname
,a.contact = b.contact
,a.contacttelephone = b.contacttelephone
,a.dept_id = b.dept_id
,a.dept_name = b.dept_name
,a.type_name = b.type_name
,a.location = b.location
,a.present_situation_name = b.present_situation_name
,a.RZ_STATUS = b.RZ_STATUS
when not matched then
insert (a.eq_id, a.eq_name,a.buy_Date, a.model_number, a.specification, a.total_price, a.product_factory,
a.country_name, a.universitycode, a.universityname, a.contact, a.contacttelephone, a.dept_id,a.dept_name,
a.type_name, a.location, a.present_situation_name, a.RZ_STATUS)
values ( b.eq_id, b.eq_name,b.buy_Date, b.model_number, b.specification, b.total_price, b.product_factory
, b.country_name, b.universitycode, b.universityname
, b.contact, b.contacttelephone, b.dept_id,b.dept_name, b.type_name, b.location
, b.present_situation_name, b.RZ_STATUS)
</update>
