--文章结尾附带一个不存储,直接读取前端传过来的流然后进行其他操作的。
java layui 导入excel(先将xlmx保存在服务器,然后在读取服务器的文件保存至数据库)(数据量过大的话需要配置vm大小和gc大小,在上篇文章)
前端
<button class="layui-btn layui-btn-radius" id="uplod">数据导入</button>
<script>
layui.use(['table', 'layer', 'laydate','upload', 'jquery'], function () {
$("#uplod").click(function () {
var $ = layui.jquery
,upload = layui.upload;
var default_config = {
msg:"数据导入成功!"
}
// $.extend( default_config, config);
var idRandom = "importData" + Math.ceil(Math.random()*10000)
var htmlContent = '<div class="layui-upload-drag" id="'+idRandom+'">';
htmlContent += '<i class="layui-icon"></i>';
htmlContent += '<p>点击选择文件,或将文件拖拽到此处</p>';
htmlContent += '</div>';
htmlContent += '<div style="text-align: center">';
htmlContent += '<button id="test19" class="layui-btn">点击上传</button>';
htmlContent += '</div>';
layer.open({
type: 1
,offset: "auto" //具体配置参考:http://www.layui.com/doc/modules/layer.html#offset
,id: 'layer_importData' //防止重复弹出
,title:'导入'
, headers: {"authorization": token} //请求头里放了个参数
,content: htmlContent
,shadeClose: true //开启遮罩关闭
,maxWidth:800
// ,btn: ['确认']
,btnAlign: 'c' //按钮居中
,shade: 0 //不显示遮罩
,yes: function(){//提交
// var iframe = $("<iframe></iframe>");
// iframe.attr("src",default_config.downUrl);
// iframe.css("display","none");
// $("#"+idRandom).append(iframe);
}
});
form.render();
var loadings;
//拖拽上传
upload.render({
elem: "#"+idRandom
,url: "../busineAssessment/uploadExcel"
,accept: 'file'
, headers: {"authorization": token}
,btn: ['确认']
,auto: false
,bindAction: '#test19'
,before: function(obj){
//bindAction按钮执行的点击事件
layer.closeAll();
loadings = layer.msg('导入中请稍后...', {icon: 16, shade: 0.3, time:0});//遮罩层开启
}
,done: function(data){
layer.close(loadings);//遮罩层关闭
layer.msg(data.msg);
if(data.code == 0){
// if($("#query")){
// $("#query").click();
// }
// if(default_config.done){
// default_config.done(data);
// }else{
// layer.msg(default_config.msg);
// }
}else{
//失败了怎么办
}
}
});
return false;
});
}
</script>
后台
@ApiOperation(value = "execl数据持久化")
@PostMapping("busineAssessment/uploadExcel")
@ResponseBody
public DataResult uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
System.out.println("开始导入");
Date startDate = new Date();
File targetFile=null;
String msg="";//返回存储路径
int code=1;
String fileName=file.getOriginalFilename();//获取文件名加后缀
String path = "D:\\";//文件后缀
if(fileName!=null&&fileName!=""){
//String returnUrl = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath() +"/upload/excel/";//存储路径
// String path = request.getSession().getServletContext().getRealPath("upload/excel"); //文件存储位置
String fileF = fileName.substring(fileName.lastIndexOf("."), fileName.length());//文件后缀
fileName=new Date().getTime()+"_"+new Random().nextInt(1000)+fileF;//新的文件名
System.out.println(path);
//先判断文件是否存在
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String fileAdd = sdf.format(new Date());
path = path+"/"+fileAdd;
File file1 =new File(path);
//如果文件夹不存在则创建
if(!file1.exists() && !file1.isDirectory()){
file1.mkdir();
}
targetFile = new File(file1, fileName);
// targetFile = new File(path, fileName);
try {
file.transferTo(targetFile);
msg=path+"/"+fileAdd+"/"+fileName;
code=0;
} catch (Exception e) {
e.printStackTrace();
}
}
busineAssessmentService.persistence(path+"/"+fileName);
// String aa = busineAssessmentService.uploadExcel(file, request, response);
Date endDate = new Date();
System.out.println(DateUtils.getDatePoor(startDate,endDate));
return DataResult.success();
}
service(数据量小版本)
@Override
public void persistence(String filename) {
try {
//创建工作簿
// XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\20210517\\1621213596507_516.xlsx"));
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filename!=null?filename:"D:\\assessmentV1.7.xlsm"));
System.out.println("xssfWorkbook对象:" + xssfWorkbook);
//读取第一个工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
System.out.println("sheet对象:" + sheet);
//获取最后一行的num,即总行数。此处从0开始计数
int maxRow = sheet.getLastRowNum();
System.out.println("总行数为:" + maxRow);
for (int row = 1; row <= maxRow; row++) { //第0行为标题,数据从第1行开始
//获取最后单元格num,即总单元格数 ***注意:此处从1开始计数***
int maxRol = sheet.getRow(row).getLastCellNum();
System.out.println("--------第" + row + "行的数据如下--------");
// for (int rol = 0; rol < maxRol; rol++){
// System.out.print(sheet.getRow(row).getCell(rol) + ",");
// }
System.out.println();
BusineAssessmentEntity busineAssessmentEntity = new BusineAssessmentEntity();
busineAssessmentEntity.setCity(sheet.getRow(row).getCell(0).toString());
busineAssessmentEntity.setCellName(sheet.getRow(row).getCell(1).toString());
busineAssessmentEntity.setCgi(sheet.getRow(row).getCell(2).toString());
busineAssessmentEntity.setScene(sheet.getRow(row).getCell(3).toString());
busineAssessmentEntity.setBandwidth(sheet.getRow(row).getCell(4).toString());
busineAssessmentEntity.setMrCoverageRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(5).toString()),2));//
busineAssessmentEntity.setPrbAverageUtilizationRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(6).toString()),2));
busineAssessmentEntity.setInterfereRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(7).toString()),2));
busineAssessmentEntity.setCqiProportionRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(8).toString()),2));
busineAssessmentEntity.setPhrProportionRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(9).toString()),2));
busineAssessmentEntity.setRtpPacketLossRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(10).toString()),2));
busineAssessmentEntity.setConnectRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(11).toString()),2));
busineAssessmentEntity.setOffLineRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(12).toString()),2));
busineAssessmentEntity.setSwitchRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(13).toString()),2));
busineAssessmentEntity.setAmzith(new Double(Double.parseDouble(sheet.getRow(row).getCell(14).toString())).intValue());
busineAssessmentEntity.setLon(setScale2(Double.parseDouble(sheet.getRow(row).getCell(15).toString()),6));
busineAssessmentEntity.setLat(setScale2(Double.parseDouble(sheet.getRow(row).getCell(16).toString()),6));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
busineAssessmentEntity.setCreateTime(new Date());
busineAssessmentMapper.insert(busineAssessmentEntity);
}
// String sql = "";
// busineAssessmentMapper.persistence(sql);
System.out.println("导入成功");
} catch (IOException e) {
e.printStackTrace();
}
}
service(数据量大版本,由于数据量过大,一条条插入太慢,这里是批量插入)
@Override
public void persistence(String filename) {
try {
//创建工作簿
// XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\20210517\\1621213596507_516.xlsx"));
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filename!=null?filename:"D:\\assessmentV1.7.xlsm"));
System.out.println("xssfWorkbook对象:" + xssfWorkbook);
//读取第一个工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
System.out.println("sheet对象:" + sheet);
//获取最后一行的num,即总行数。此处从0开始计数
int num = sheet.getLastRowNum();
System.out.println("总行数为:" + num);
//这里采用批量插入方式,要不一条一条插浪费时间浪费性能
int chushu = 2000;
//商
int quotient = num / chushu;
//
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO busine_assessment (id , " +
"city," +
"cell_name," +
"cgi," +
"scene," +
"bandwidth," +
"mr_coverage_rate," +
"prb_average_utilization_rate," +
"interfere_rate," +
"cqi_proportion_rate," +
"phr_proportion_rate," +
"rtp_packet_loss_rate," +
"connect_rate," +
"off_line_rate," +
"switch_rate," +
"lon," +
"lat," +
"create_time," +
"amzith," +
"score " +
")" +
" VALUES " );
BusineWeightSetEntity busineWeightSetEntity = busineWeightSetMapper.getDetail();//计算分数的
for (int row = 1; row <= num; row++) { //第0行为标题,数据从第1行开始
//获取最后单元格num,即总单元格数 ***注意:此处从1开始计数***
// int maxRol = sheet.getRow(row).getLastCellNum();
BusineAssessmentEntity busineAssessmentEntity = new BusineAssessmentEntity();
busineAssessmentEntity.setCity(sheet.getRow(row).getCell(0).toString());
busineAssessmentEntity.setCellName(sheet.getRow(row).getCell(1).toString());
busineAssessmentEntity.setCgi(sheet.getRow(row).getCell(2).toString());
busineAssessmentEntity.setScene(sheet.getRow(row).getCell(3).toString());
busineAssessmentEntity.setBandwidth(sheet.getRow(row).getCell(4).toString());
busineAssessmentEntity.setMrCoverageRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(5).toString()),2));//
busineAssessmentEntity.setPrbAverageUtilizationRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(6).toString()),2));
busineAssessmentEntity.setInterfereRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(7).toString()),2));
busineAssessmentEntity.setCqiProportionRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(8).toString()),2));
busineAssessmentEntity.setPhrProportionRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(9).toString()),2));
busineAssessmentEntity.setRtpPacketLossRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(10).toString()),2));
busineAssessmentEntity.setConnectRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(11).toString()),2));
busineAssessmentEntity.setOffLineRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(12).toString()),2));
busineAssessmentEntity.setSwitchRate(setScale2(Double.parseDouble(sheet.getRow(row).getCell(13).toString()),2));
busineAssessmentEntity.setLon(setScale2(Double.parseDouble(sheet.getRow(row).getCell(15).toString()),6));
busineAssessmentEntity.setLat(setScale2(Double.parseDouble(sheet.getRow(row).getCell(16).toString()),6));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
busineAssessmentEntity.setCreateTime(new Date());
busineAssessmentEntity.setScore(scoreCalculation(busineAssessmentEntity,busineWeightSetEntity));//我自己计算分数的方法
System.out.println("--------第" + row + "行--------");
if(row<=quotient*chushu){//当前数据小于等于商乘以除数
sb.append("(replace(uuid(),\"-\",\"\"),").append("'" +sheet.getRow(row).getCell(0).toString() + "'"+
",'"+sheet.getRow(row).getCell(1).toString() +"'"+
",'"+sheet.getRow(row).getCell(2).toString() +"'"+
",'"+sheet.getRow(row).getCell(3).toString() +"'"+
",'"+sheet.getRow(row).getCell(4).toString() +"'"+
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(5).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(6).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(7).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(8).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(9).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(10).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(11).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(12).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(13).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(15).toString()),6) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(16).toString()),6) +
", STR_TO_DATE('"+sheet.getRow(row).getCell(17).toString()+"','%Y-%m-%d %H:%i:%s') "+
","+new Double(Double.parseDouble(sheet.getRow(row).getCell(14).toString())).intValue() +
","+busineAssessmentEntity.getScore()).append(")");
if(row%2000 != 0){ //取余2000 如果有余数则拼个逗号 然后继续
sb.append(",");
}
if(row%2000 == 0){ //取余2000 如果没有余数则保存,然后清空sb,然后继续重新拼sql
busineAssessmentMapper.saveAssessment(sb);
sb.setLength(0);
sb.append("INSERT INTO busine_assessment (id," +
"city," +
"cell_name," +
"cgi," +
"scene," +
"bandwidth," +
"mr_coverage_rate," +
"prb_average_utilization_rate," +
"interfere_rate," +
"cqi_proportion_rate," +
"phr_proportion_rate," +
"rtp_packet_loss_rate," +
"connect_rate," +
"off_line_rate," +
"switch_rate," +
"lon," +
"lat," +
"create_time," +
"amzith," +
"score " +
")" +
" VALUES " );
}
}else{ //最后一个不到2000的走else里
sb.append("(replace(uuid(),\"-\",\"\"),").append("'" +sheet.getRow(row).getCell(0).toString() + "'"+
",'"+sheet.getRow(row).getCell(1).toString() +"'"+
",'"+sheet.getRow(row).getCell(2).toString() +"'"+
",'"+sheet.getRow(row).getCell(3).toString() +"'"+
",'"+sheet.getRow(row).getCell(4).toString() +"'"+
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(5).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(6).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(7).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(8).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(9).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(10).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(11).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(12).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(13).toString()),2) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(15).toString()),6) +
","+setScale2(Double.parseDouble(sheet.getRow(row).getCell(16).toString()),6) +
", STR_TO_DATE('"+sheet.getRow(row).getCell(17).toString()+"','%Y-%m-%d %H:%i:%s') "+
","+new Double(Double.parseDouble(sheet.getRow(row).getCell(14).toString())).intValue() +
","+busineAssessmentEntity.getScore()).append(")");
if(row!=num){ //
System.out.println(row+"================="+num);
sb.append(",");
}else{
busineAssessmentMapper.saveAssessment(sb);
}
}
}
// String sql = "";
// busineAssessmentMapper.persistence(sql);
System.out.println("导入成功");
} catch (IOException e) {
e.printStackTrace();
}
}
mapper
@Insert("${sb}")
void saveAssessment(@Param("sb")StringBuilder sb);
这里有个不用写入文件,直接存数据库的,没用这种,有需要时再试,留着备用
https://www.cnblogs.com/zexin/p/10839196.html
======我自己测试了一下,用的上边那个前端的上传按钮,调用这个接口======
这个是封装的工具类
package com.company.project.common.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Constant
*
* @author wenbin
* @version V1.0
* @date 2020年3月18日
*/
public class ExcelUtils {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 读取Excel的内容
*
* @param fileType 文件类型,xls或xlsx
* @param startRows 开始读取行数,比喻行头不需要读入 忽略的行数为1
* @param ignoreRowBlank 是否忽略空行
* @param is 文件输入流
* @return 读出的Excel中数据的内容
* @throws Exception duxxxxx
*/
public static List<String[]> readData(String fileType, int startRows, boolean ignoreRowBlank, InputStream is) throws Exception {
List<String[]> result = new ArrayList<>();
Workbook wb = readExcel(fileType, is);
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = wb.getSheetAt(sheetIndex);
for (int rowIndex = startRows, z = sheet.getLastRowNum(); rowIndex <= z; rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
int rowSize = row.getLastCellNum();
String[] values = new String[rowSize];
boolean hasValue = false;
for (int columnIndex = 0; columnIndex < rowSize; columnIndex++) {
String value = "";
Cell cell = row.getCell(columnIndex);
if (cell != null) {
// 注意:一定要设成这个,否则可能会出现乱码,后面版本默认设置
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
//value = new DecimalFormat("0").format(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = String.valueOf(cell.getDateCellValue());
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
// 判断是否包含小数点,如果不含小数点,则以字符串读取,如果含小数点,则转换为Double类型的字符串
if (temp.indexOf(".") > -1) {
value = String.valueOf(new Double(temp)).trim();
} else {
value = temp.trim();
}
}
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
values[columnIndex] = value;
if (!value.isEmpty()) {
hasValue = true;
}
}
if (!ignoreRowBlank || hasValue) {//不为忽略空行模式或不为空行
result.add(values);
}
}
}
return result;
}
//读取excel
private static Workbook readExcel(String fileType, InputStream is) throws Exception {
if (excel2003L.equals(fileType)) {
return new HSSFWorkbook(is);
} else if (excel2007U.equals(fileType)) {
return new XSSFWorkbook(is);
} else {
throw new IllegalArgumentException("不支持的文件类型,仅支持xls和xlsx");
}
}
}
、、这个是controller
@ApiOperation(value = "execl数据持久化")
@PostMapping("busineAssessment/uploadExceltest")
@ResponseBody
public DataResult uploadExceltest(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
if (!file.isEmpty()) {
try {
//获取原始的文件名
String originalFilename = file.getOriginalFilename();
//获取文件类型,用来判断是否为可用类型
String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
//默认从第一行开始读取
Integer startRows = 1;
//获取输入流
InputStream is = file.getInputStream();
//Excel导入导出的单元类
List<String[]> strings = ExcelUtils.readData(fileType, startRows, true, is);
//遍历Excel表每一行的数据
for (String[] str : strings) {
System.out.print(str[0]+";");
System.out.print(str[1]+";");
System.out.println(str[2]);
}
//将数据批量保存到数据库
//......
} catch (Exception e) {
e.printStackTrace();
}
}
return DataResult.success();
}