目录结构
Ⅰ、导入
给到两种导入，大差不差的
一、前端
1.input file
注意1：enctype=“multipart/form-data”，method=“post”
注意2：name=“planImport”，file的name要与java读取流时的名字相同
<!--导入-->
<form action="/plan/import" enctype="multipart/form-data" method="post" id="import">
<input type="file" name="planImport"/>
<input type="submit" value="导入"/>
</form>
2.button
1）隐藏file
2）点击“导入”按钮，触发file点击事件；
3）file改动时，触发ajax
JS
//点击导入，✔
$("#maintain_manage_jButton5").click(function () {
$("#file").click();//选择文件
$("#file").change(function () {
//创建foemdata对象
var formData = new FormData();
//为file赋值
formData.append('file',$('#file')[0].files[0]);
$.ajax({
url : '/maintain/importExcel',
type : 'POST',
data : formData,
contentType: false,// 当有文件要上传时，此项是必须的，否则后台无法识别文件流的起始位置
processData: false,// 是否序列化data属性，默认true(注意：false时type必须是post)
dataType: 'json',//这里是返回类型，一般是json,text等
clearForm: true,//提交后是否清空表单数据
success : function (data) {
alert(data.msg);
if(data.code == 200) {
location.href = "/am/maintainManage";
}
},error : function (data,status,e) {
console.error(e);
}
});
});
});
HTML
<!--隐藏文件-->
<input type="file" name="file" id="file" style="display: none"/>
<!--别看我写的这么多，这就是一个button即可-->
<div id="maintain_manage_jButton5" class="vjbutton vjbutton_c_style2 border_radius_3">
<div class="vjbutton_txtR">
<span class="btniconcum1">
<img src="../static/images/jexcel.png" width="16" height="16"/>
</span>
<span class="btniconcum2">
<img src="../static/images/jexcel.png" width="16" height="16"/>
</span>
<span class="btntxt"></span>
导入Excel
</div>
</div>
二、后端
建议不要循环访问数据库，自个优化
1.Controller
/**
* 计划导入
*/
@RequestMapping(value = "/import",method = RequestMethod.POST)
public BaseResult planImport(HttpServletRequest request){
System.out.println("计划导入------------------------入导划计");
MultipartHttpServletRequest mreq = null;
BaseResult br = new BaseResult();
//判断request是否是MultipartHttpServletRequest的实例
if(request instanceof MultipartHttpServletRequest) {
mreq = (MultipartHttpServletRequest) request;
//计划导入
br = planManageService.planImport(mreq);
} else {
br.setCode(500);
br.setMsg("表单类型异常");
}
return br;
}
2.Service
注意解析文件那一步即可，其余步骤是在做数据处理
/**
* 计划导入
* @param mreq
* @return
*/
@Override
public BaseResult planImport(MultipartHttpServletRequest mreq) {
List<TbWeeklyPlan> list = null;
BaseResult br = new BaseResult();
try {
//解析文件!!!!!!!!!!!!!!!!!!!!!!!!!! here
list = ExcelUtils.analysisFileOne(mreq);
if(list == null || list.size() == 0){
br.setCode(500);
br.setMsg("表单类型异常或数据异常");
System.out.println(br.getMsg());
return br;
} else {
int count = 0;//记录重复行数
int sum = 0;//记录添加行数
int updaSum = 0;//记录修改行数
TbWeeklyPlan weeklyPlan;//用于判断是否完全一致
for (TbWeeklyPlan wp : list) {
//建议不要多次访问数据库！！！这里直观一点
weeklyPlan = planManageMapper.selectPlan(wp);
if(weeklyPlan != null) {
if(StringUtils.equals(wp.getPlanYear(),weeklyPlan.getPlanYear())
&& StringUtils.equals(wp.getSerialNumber(),weeklyPlan.getSerialNumber())
&& StringUtils.equals(wp.getRegion(),weeklyPlan.getRegion())
) {//如果完全一致，当作重复
count ++;
continue;
} else {//否则当作修改
wp.setUpdateTime(new Date());
wp.setUpdatePerson(SecurityContextHolder.getContext().getAuthentication().getName());
updaSum += planManageMapper.updatePlansById(wp);
continue;
}
}
wp.setCreateTime(new Date());
wp.setCreatePerson(SecurityContextHolder.getContext().getAuthentication().getName());
//导入数据库
sum += planManageMapper.planImport(wp);
}
String str = "成功导入 " + sum + " 条数据，" +
(updaSum == 0 ? "" : "成功修改" + updaSum + "条数据，") +
"有 " + count + " 条重复数据，不可导入!";
System.out.println(str);
br.setCode(200);
br.setMsg(str);
return br;
}
} catch (Exception e){
e.printStackTrace();
br.setCode(500);
br.setMsg(e.getMessage());
return br;
}
}
3.解析类ExcelUtils
给到浅显的两种方式（表头，列顺序），整理一下就是一个可以复用的工具类了，见》》》4.工具类ExcelUtils
package com.***.util;
import com.***.pojo.TbMaintain;
import com.***.pojo.TbWeeklyPlan;
import org.apache.poi.ss.usermodel.*;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName ExcelUtils
* @Description Excel处理工具类
* @Author HBW
* @Date 2021/8/30 14:32
* @Version 1.0
*/
public class ExcelUtils {
/**
* 解析计划Excel
* @param mreq
* @return
*/
public static List<TbWeeklyPlan> analysisFileOne(MultipartHttpServletRequest mreq)throws Exception{
InputStream inputStream= null;
try {
inputStream = mreq.getFile ("planImport").getInputStream ();
} catch (IOException e) {
throw e;
}
String fileName = mreq.getFile ("planImport").getOriginalFilename ();
Workbook workbook = null;
try {
//判断什么类型文件
if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
workbook = WorkbookFactory.create(inputStream);
} else {
return null;
}
} catch (Exception e) {
throw e;
}
//获取所有的工作表的的数量
int numOfSheet = workbook.getNumberOfSheets ();
System.out.println ("Sheet数量------------->" + numOfSheet);
List<TbWeeklyPlan> list = new ArrayList<> ();
//遍历表
for (int i = 0; i < numOfSheet; i++) {//第i行
//获取第i个sheet
Sheet sheet = workbook.getSheetAt (i);
if (sheet == null) {
continue;
}
//获取当前sheet有多少Row
int lastRowNum = sheet.getLastRowNum ();
if (lastRowNum == 0) {
continue;
}
Row row;
// 行头
List<String> headName = new ArrayList<>();
if(lastRowNum>0){
row = sheet.getRow (0);
// 获取一个Row有多少Cell
short lastCellNum = row.getLastCellNum ();
if(lastCellNum < 3){//浅显的判断，看自己的需求
System.out.println("表头异常");
return null;
}
for (int k = 0; k <= lastCellNum; k++) {
if (row.getCell (k) == null) {
continue;
}
String res = getCellVal(row.getCell (k));
headName.add (res);
}
}
// 每一行的内容
//保存weeks，下一行weeks为空时，用此填充
String weeks = null;
for (int j = 1; j <= lastRowNum; j++) {
row = sheet.getRow (j);
if (row == null) {
continue;
}
//获取一个Row有多少Cell
short lastCellNum = row.getLastCellNum ();
TbWeeklyPlan weeklyPlan = new TbWeeklyPlan();
for (int k = 0; k <= lastCellNum; k++) {
try {
if (row.getCell (k) == null) {
continue;
}
String res = getCellVal(row.getCell (k));
switch (headName.get(k)){
case "年份" :
weeklyPlan.setPlanYear(res);
break;
case "周次" :
weeklyPlan.setWeeks(res.contains("WW") ? res : "WW" + res);
break;
case "区域" :
weeklyPlan.setRegion(res);
break;
default:
return null;
}
} catch (Exception e){
e.printStackTrace();
return null;
}
}
list.add (weeklyPlan);
}
System.out.println("共有：" + lastRowNum + "条数据");
}
return list;
}
/**
* 解析来料Excel
* @param mreq
* @return
* @throws Exception
*/
public static List<TbMaintain> analysisFileTwo(MultipartHttpServletRequest mreq) throws Exception {
InputStream inputStream= null;
try {
inputStream = mreq.getFile ("file").getInputStream ();
} catch (IOException e) {
throw e;
}
String fileName = mreq.getFile ("file").getOriginalFilename ();
Workbook workbook = null;
try {
//判断什么类型文件
if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
workbook = WorkbookFactory.create(inputStream);
} else {
return null;
}
} catch (Exception e) {
throw e;
}
//获取所有的工作表的的数量
int numOfSheet = workbook.getNumberOfSheets ();
System.out.println ("Sheet数量------------->" + numOfSheet);
List<TbMaintain> list = new ArrayList<> ();
//遍历表
for (int i = 0; i < numOfSheet; i++) {//第i行
//获取第i个sheet
Sheet sheet = workbook.getSheetAt (i);
if (sheet == null) {
continue;
}
//获取当前sheet有多少Row
int lastRowNum = sheet.getLastRowNum ();
if (lastRowNum == 0) {
continue;
}
Row row;
// 行头
List<String> headName = new ArrayList<>();
if(lastRowNum>0){
row = sheet.getRow (0);
// 获取一个Row有多少Cell
short lastCellNum = row.getLastCellNum ();
for (int k = 0; k <= lastCellNum; k++) {
if (row.getCell (k) == null) {
continue;
}
String res = getCellVal(row.getCell (k));
headName.add (res);
}
}
// 每一行的内容，跳过表头
for (int j = 1; j <= lastRowNum; j++) {
row = sheet.getRow (j);
if (row == null) {
continue;
}
//获取一个Row有多少Cell
short lastCellNum = row.getLastCellNum ();
TbMaintain tm = new TbMaintain();
tm.setCreateTime(new Date());
tm.setCreatePerson(SecurityContextHolder.getContext().getAuthentication().getName());
for (int k = 0; k <= lastCellNum; k++) {
try {
if (row.getCell (k) == null) {
continue;
}
String res = getCellVal(row.getCell (k));
switch (k){
case 0 :
tm.setSendSamplePerson(res);
break;
case 1 :
tm.setSendSampleDate(res);
break;
case 2 :
tm.setItemNo(res);
break;
default://异常
return null;
}
} catch (Exception e){
e.printStackTrace();
return null;
}
}
list.add (tm);
}
System.out.println("共有：" + lastRowNum + "条数据");
}
return list;
}
/**
* 单元格类型处理
* @param cell
* @return 转为String
*/
private static String getCellVal(Cell cell) {
if(cell == null){
return null;
}
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式yyyy-mm-dd
DecimalFormat df = new DecimalFormat("0");
String val;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
val = fmt.format(cell.getDateCellValue()); //日期型
} else {
val = df.format(cell.getNumericCellValue()); //数字型
}
break;
case Cell.CELL_TYPE_STRING: //文本类型
val = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: //布尔型
val = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: //空白
val = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: //错误
val = "错误";
break;
case Cell.CELL_TYPE_FORMULA: //公式
try {
val = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
val = String.valueOf(cell.getNumericCellValue());
}
break;
default:
val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
}
return val;
}
}
4.👉工具类ExcelUtils👈
为了复用嘛，特此整理了一下
import org.apache.poi.ss.usermodel.*;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName ExcelUtils
* @Description Excel工具类
* @Author HBW
* @Date 2021/7/8 14:32
* @Version 1.0
*/
public class ExcelUtils {
/**
* 解析Excel
* @param mreq
* @return
* @throws Exception
*/
public static Set<Object[]> analysisFile(MultipartHttpServletRequest mreq) throws Exception {
InputStream inputStream= null;
Set<Object[]> set = new HashSet<>();
try {
inputStream = mreq.getFile("file").getInputStream();
String fileName = mreq.getFile("file").getOriginalFilename();
Workbook workbook = null;
//判断什么类型文件
if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
workbook = WorkbookFactory.create(inputStream);
} else {
return null;
}
//获取所有的工作表的的数量
int numOfSheet = workbook.getNumberOfSheets();
System.out.println("Sheet数量------------->" + numOfSheet);
//遍历表
for (int i = 0; i < numOfSheet; i++) {//第i行
//获取第i个sheet
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
//获取当前sheet有多少Row
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) {
continue;
}
Row row;
// 内容：每一行的内容，跳过表头(j = 1)
for (int j = 1; j <= lastRowNum; j++) {
row = sheet.getRow(j);
if (row == null) {
continue;
}
//获取一个Row有多少Cell
short lastCellNum = row.getLastCellNum();
Object[] obj = new Object[lastCellNum + 2];
for (int k = 0; k <= lastCellNum; k++) {
if (row.getCell(k) == null) {
continue;
}
String res = getCellVal(row.getCell(k));
obj[k] = res;
}
obj[22] = new Date();
obj[23] = SecurityContextHolder.getContext().getAuthentication().getName();
set.add(obj);
}
System.out.println("共有：" + (lastRowNum - 1) + "条数据");
}
} catch (Exception e){
throw e;
}
return set;
}
private static String getCellVal(Cell cell) {
if(cell == null){
return null;
}
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //日期格式yyyy-mm-dd
DecimalFormat df = new DecimalFormat("0");
String val;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
val = fmt.format(cell.getDateCellValue()); //日期型
} else {
val = df.format(cell.getNumericCellValue()); //数字型
}
break;
case Cell.CELL_TYPE_STRING: //文本类型
val = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: //布尔型
val = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: //空白
val = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: //错误
val = "错误";
break;
case Cell.CELL_TYPE_FORMULA: //公式
try {
val = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
// val = String.valueOf(cell.getNumericCellValue());
}
break;
default:
val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
}
return val;
}
}
5.Mapper
与数据库交互，巴拉巴拉。
<insert id="importExcel" parameterType="list">
INSERT INTO tb_maintain
(
send_sample_person,
send_sample_date,
item_no
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.sendSamplePerson},
#{item.sendSampleDate},
#{item.itemNo}
)
</foreach>
</insert>
Ⅱ、导出
查询数据库某些记录，（生成本地临时文件），再以浏览器的形式下载
一、前端
1.方式一
JS
//导出数据
$("#plan_manage_jButton4").click(function () {
//1.拿到勾选数据的ID
var ids = new Array();//保存勾选的id(数据库ID)
var selectIds = new Array();//保存勾选的id(索引id)
var flag1 = false;
var flag2 = false;
getIds(ids,selectIds);//获取勾选数据的id
//2.判断
if(ids.length == 0){
flag1 = confirm("未勾选数据，是否导出模板？");
} else {
flag2 = confirm("是否导出，勾选的 " + ids.length + " 条数据？");
}
//3.正式开始
if(flag1 || flag2){
//1.创建XMLHttpRequest组建
//2.设置回调函数
//3.初始化XMLHttpRequest组建
//4.发送请求
//区别：get路径传参；post设置请求头，send处传参
var xhr = new XMLHttpRequest();
xhr.open('GET','/plan/export?keys=' + ids.toString(),true);//get请求、请求路径、是否异步
xhr.responseType = "blob";//返回类型blob
xhr.onload = function () {//请求完成处理函数
if(this.status == 200){
var blob = this.response;//获取返回值
var a = document.createElement('a');
a.download = "计划表" + new Date().toLocaleDateString() + ".xlsx";
a.href = window.URL.createObjectURL(blob);
a.click();
}
};
//发送ajax请求
xhr.send();
//取消勾选
for (var i = 0; i < selectIds.length; i++) {
$("tr[id='" + selectIds[i] + "'] td:eq(0) input").prop("checked",false);
}
}
});
HTML
<!--一个button即可-->
<div id="plan_manage_jButton4" class="vjbutton vjbutton_c_style2 border_radius_3">
<div class="vjbutton_txtR">
<span class="btniconcum1">
<img src="../static/images/jexcel.png" width="16" height="16"/>
</span>
<span class="btniconcum2">
<img src="../static/images/jexcel.png" width="16" height="16"/>
</span>
<span class="btntxt"></span>
导出Excel
</div>
</div>
2.方式二
就一个button按钮，跳转时想办法把数据带过去即可；不带数据，下载全表数据，下方这般操作即可：
<!--导出-->
<input type="button" name="planExport" value="导出" onclick="location='/plan/export'"/>
二、后端
以流的方式在本地生成Excel，再以浏览器的方式打开【经常使用，搞成工具类就好】
1.Controller
浏览器打开本地临时Excel，完成下载动作
/**
* 计划导出
* @param keys 被勾选数据的ID
* @param response
*/
@RequestMapping("/export")
public void planExport(String keys,HttpServletResponse response){
System.out.println("计划导出------------------------出导划计");
Date date=new Date();
DateFormat format=new SimpleDateFormat("yyyyMMdd");
String fileName = "计划表" + format.format(date) + ".xlsx";
//清空response
response.reset();
//让浏览器下载文件,fileName是上述默认文件下载名
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//设置response的Header
try {
//header仍是乱码？？？
response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName,"UTF8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.addHeader("Cache-Control","no-cache");
InputStream inputStream=null;
ByteArrayOutputStream bos=null;
//在service层中已经将数据存成了excel临时文件，并返回了临时文件的路径
String downloadPath = planManageService.exportStu(keys,"");
//根据临时文件路径创建文件对象
File file = new File(downloadPath);
try {
inputStream = new FileInputStream(file);
bos = new ByteArrayOutputStream(1024);
int len = -1;
byte[] b = new byte[1024];
while((len = inputStream.read(b)) != -1){
bos.write(b,0,len);
}
response.getOutputStream().write(bos.toByteArray());
//刷新
bos.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
//关闭流
try {
if(inputStream != null) {
inputStream.close();
}
if(bos != null) {
bos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//删除临时文件
file.delete();
}
2.Service
以流的形式在本地生成临时Excel
/**
* 导出数据库数据到本地
* @return
*/
@Override
public String exportStu(String keys) {
//计划列表
List<TbWeeklyPlan> list = null;
//创建工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = xssfWorkbook.createSheet();
xssfWorkbook.setSheetName(0,"计划表");
//创建表头
XSSFRow head = sheet.createRow(0);
String[] heads = {"年份","周次","区域"};
for(int i = 0;i < heads.length;i++){
XSSFCell cell = head.createCell(i);
cell.setCellValue(heads[i]);
}
if(!"".equals(keys) && keys != null) {//导出勾选部分
List<String> keyList = Arrays.asList(keys.split(","));
list = planManageMapper.selectPlanKeys(keyList);
} else {//导出模板
list = null;
}
//内容填充，除导出模板、未查到数据不需要
if(list!=null && list.size()>0){
for(int i = 1 ; i<=list.size() ; i++) {
TbWeeklyPlan weeklyPlan = list.get(i-1);
//创建行，从第二行开始（i=1）
XSSFRow row = sheet.createRow(i);
//创建单元格，并填充数据
XSSFCell cell = row.createCell(0);
cell.setCellValue(weeklyPlan.getPlanYear());
cell = row.createCell(1);
cell.setCellValue(weeklyPlan.getWeeks());
cell = row.createCell(2);
cell.setCellValue(weeklyPlan.getRegion());
}
}
//创建文件目录
File file = new File(customPath.getXls());//你给个路径就行，比如：C:\\Users\\Administrator\\Desktop\\xls\\
if(!file.exists()) {
file.mkdirs();
}
//导出名称
String fileName = "计划表.xlsx";
//导出路径
String exportPath = file + "\\" + fileName;
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(exportPath);
xssfWorkbook.write(outputStream);
outputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("导出数据：" + (list != null && list.size()>0 ? list.size() : 0) + "条");
System.out.println("导出路径" + exportPath);
return exportPath;
}
3.Mapper
查数据库~
<select id="selectMaintainKeys" parameterType="list" resultMap="tmr">
SELECT
id,send_sample_person,send_sample_date,item_no,
DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') AS create_time,
create_person,
DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s') AS update_time,
update_person
FROM tb_maintain
WHERE id IN
<foreach collection="keyList" item="id" index="index" separator="," open="(" close=")">
#{id}
</foreach>
</select>
👉导出2，友情参考链接：工具类——Java导出EXCEL2（设置样式、加载并填充图片、加载指定模板、大数据量设置窗口大小与刷新频率）
一次Demo记录：初次使用时，还是有些麻烦的。记录、分享一下！
2442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
