可以在评论区交流!!!
大数据的导入请参考:https://blog.csdn.net/qq_42992729/article/details/102514065
前端:
html代码:
<div id="choose" style="display: none; height: 100px; 300px" >
<form enctype="multipart/form-data" id="batchUpload" class="form-horizontal">
<button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;" type="button" >选择文件</button>
<input type="file" name="file" style="0px;height:0px;" id="uploadEventFile">
<input id="uploadEventPath" disabled="disabled" type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px; 200px;" />
</form>
<button type="button" class="btn btn-success btn-sm" id="uploadBtn" >上传</button>
</div>
<button type="button" class="layui-btn" id="excelImport"><i class="layui-icon"></i>导入</button>
<button type="button" class="layui-btn" id="excelExport"><i class="layui-icon"></i>导出</button>
JS代码:
/**
* 导入
*/
var excelImport = document.getElementById("excelImport");
excelImport.onclick = function() {
layer.open({
type: 1,
content: $('#choose'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响
});
}
var User = function() {
this.init = function() {
//模拟上传excel
$("#uploadEventBtn").unbind("click").bind("click", function() {
$("#uploadEventFile").click();
});
$("#uploadEventFile").bind("change", function() {
$("#uploadEventPath").attr("value", $("#uploadEventFile").val());
});
};
//点击上传钮
var uploadBtn = document.getElementById("uploadBtn");
uploadBtn.onclick = function() {
var uploadEventFile = $("#uploadEventFile").val();
if (uploadEventFile == '') {
alert("请择excel,再上传");
} else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel
alert("只能上传Excel文件");
} else {
var url = "/phySsPersonInfo/import";
var formData = new FormData($('form')[0]);
user.sendAjaxRequest(url, "POST", formData);
}
};
this.sendAjaxRequest = function(url, type, data) {
$.ajax({
url : url,
type : type,
data : data,
dataType : "json",
success : function(result) {
alert(result.message);
layer.closeAll();
$('#SsPersonInfoTable').bootstrapTable('refresh');
},
error : function(result) {
alert(result.message);
layer.closeAll();
$('#SsPersonInfoTable').bootstrapTable('refresh');
},
cache : false,
contentType : false,
processData : false
});
};
};
var user;
$(function() {
user = new User();
user.init();
});
var exportBtn = document.getElementById("excelExport");
exportBtn.onclick = function() {
if (confirm("确定导出?")) {
$.ajax({
url : "/phySsPersonInfo/exportVillageFile",
type : "post",
data:{},
dataType : "json",
success : function(result) {
alert(result.message);
},
error : function(result) {
alert(result.message);
},
cache : false,
contentType : false,
processData : false
});
}
}
后端代码:
Controller层:
/**
* 导入excl
*/
//导入excel
@RequestMapping(value = "/import", method=RequestMethod.POST)
@ResponseBody
public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response){
Map<String, Object> map = new HashMap<String, Object>();
String result = ssPersonInfoService.readExcelFile(file);
map.put("message", result);
return map;
}
/**
* 按村为单位导出excel
*/
@RequestMapping("/exportVillageFile")
@ResponseBody
public Map<String, Object> exportVillageFile(HttpServletResponse response,HttpServletRequest request) {
String result = "";
try{
result = ssPersonInfoService.VillageFile(response,request);
}catch(Exception e){
e.printStackTrace();
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("message", result);
return map;
}
Service接口:
/**
* 插入数据
* */
Integer insertall(SsPersonInfo ssPersonInfo);
/**
* 获取所有的镇
*/
List<Map<String,Object>> getAllTown();
/**
* 根据镇获取所有的村
*/
List<Map<String,Object>> getAllVillage(Map<String,Object> map);
/**
* 以村为单位获取人员信息
*/
List<SsPersonInfo> getPayableInfoByTown(Map<String,Object> map);
Service实现类:
@Override
public String readExcelFile(MultipartFile file) {
String result = "";
//创建处理EXCEL的类
ReadExcel readExcel = new ReadExcel();
//解析excel，获取上传的事件单
List<Map<String, Object>> userList = readExcel.getExcelInfo(file);
//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
for(Map<String, Object> user:userList){
String nativePlace = user.get("nativePlace").toString();
String [] arr = nativePlace.split(" ");
SsPersonInfo ssPersonInfo = new SsPersonInfo();
ssPersonInfo.setIdentityCard(user.get("identityCard").toString());
ssPersonInfo.setName(user.get("name").toString());
ssPersonInfo.setNativePlace(user.get("nativePlace").toString());
ssPersonInfo.setTown(arr[1]);
ssPersonInfo.setVillage(arr[2]);
int ret = ssPersonInfoMapper.insertall(ssPersonInfo);
if(ret == 0){
result = "插入数据库失败";
}
}
if(userList != null && !userList.isEmpty()){
result = "上传成功";
}else{
result = "上传失败";
}
return result;
}
/**
* 获取所有的乡镇/街道
* @return 所有的乡镇/街道
*/
@Override
public List<Map<String, Object>> getAllTown() {
return ssPersonInfoMapper.getAllTown();
}
/**
* 根据乡镇获取所辖的村/社区
* @param map 乡镇
* @return 本镇所有的村
*/
@Override
public List<Map<String, Object>> getAllVillage(Map<String, Object> map) {
return ssPersonInfoMapper.getAllVillage(map);
}
/**
* 用于导出以村为单位的数据
* @param response 用于输出文件
*/
@Override
public String VillageFile(HttpServletResponse response, HttpServletRequest request) {
String result = "";
List<Map<String, Object>> allTowns = ssPersonInfoMapper.getAllTown();
if (allTowns.size()!=0){
result = "导出成功";
}
// String dirPath = request.getSession().getServletContext().getRealPath("/static/download/");
String dirPath = "D:\study\金现代\冠县社保信息\";
for (Map<String, Object> town : allTowns){
//获取乡镇名称
String Town = (String) town.get("Town");
//获取乡镇下辖的村
List<Map<String, Object>> allVillages = ssPersonInfoMapper.getAllVillage(town);
new Thread(){
@Override
public void run() {
for (Map<String, Object> village : allVillages){
Map<String,Object> param = new HashMap<>(5);
String Village = (String) village.get("Village");
param.put("Town",Town);
param.put("Village",Village);
//填入Excel表格中的数据
List<SsPersonInfo> ssPersonInfos = ssPersonInfoMapper.getPayableInfoByTown(param);
String fileName = Village+"村（社区）社保人员清单.xls";
String path = dirPath+Town+"\"+Village+"\";
File targetFile = new File(path);
if(!targetFile.exists()){//如果文件夹不存在
targetFile.mkdirs();
}
response.setContentType("octets/stream");
try {
FileOutputStream fos = new FileOutputStream(new File(path+fileName));
ByteArrayOutputStream os = new ByteArrayOutputStream();
response.addHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
HSSFWorkbook wb = new HSSFWorkbook();
String sheetName=Village+"村（社区）社保人员清单";
HSSFSheet sheet = wb.createSheet(sheetName);
/* 设置打印格式 */
HSSFPrintSetup hps = sheet.getPrintSetup();
hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
hps.setLandscape(true);
hps.setFitHeight((short) 1);
hps.setFitWidth((short) 1);
hps.setScale((short) 65);
hps.setFooterMargin(0);
hps.setHeaderMargin(0);
sheet.setMargin(HSSFSheet.TopMargin, 0.3);
sheet.setMargin(HSSFSheet.BottomMargin, 0);
sheet.setMargin(HSSFSheet.LeftMargin, 0.3);
sheet.setMargin(HSSFSheet.RightMargin, 0);
//创建第一行
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell ;
row.setHeightInPoints(40);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
//粗体显示
font.setBold(true);
font.setFontHeightInPoints((short) 16);
cell = row.createCell(0);
cell.setCellValue("身份证");
cell = row.createCell(1);
cell.setCellValue("姓名");
cell = row.createCell(2);
cell.setCellValue("籍贯");
cell = row.createCell(3);
cell.setCellValue("镇");
cell = row.createCell(4);
cell.setCellValue("村");
sheet.setColumnWidth(0, 4096);
sheet.setColumnWidth(1, 4096);
sheet.setColumnWidth(2, 4096);
sheet.setColumnWidth(3, 4096);
sheet.setColumnWidth(4, 4096);
//设置列值-内容
for (int i = 0; i < ssPersonInfos.size(); i++) {
row = sheet.createRow(i + 1);
row.setHeightInPoints(20);
SsPersonInfo ssPersonInfo = ssPersonInfos.get(i);
cell = row.createCell(0);
cell.setCellValue(ssPersonInfo.getIdentityCard());
cell = row.createCell(1);
cell.setCellValue(ssPersonInfo.getName());
cell = row.createCell(2);
cell.setCellValue(ssPersonInfo.getNativePlace());
cell = row.createCell(3);
cell.setCellValue(ssPersonInfo.getTown());
cell = row.createCell(4);
cell.setCellValue(ssPersonInfo.getVillage());
}
wb.write(os);
InputStream excelStream = new ByteArrayInputStream(os.toByteArray());
//写入目标文件
byte[] buffer = new byte[1024*1024];
int byteRead = 0;
while((byteRead= excelStream.read(buffer))!=-1){
fos.write(buffer, 0, byteRead);
fos.flush();
}
fos.close();
excelStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}.start();
}
return result;
}
Mapper接口:
/**
* 插入数据
* */
Integer insertall(SsPersonInfo ssPersonInfo);
/**
* 获取所有的镇
*/
List<Map<String,Object>> getAllTown();
/**
* 根据镇获取所有的村
*/
List<Map<String,Object>> getAllVillage(Map<String,Object> map);
/**
* 以村为单位获取人员信息
*/
List<SsPersonInfo> getPayableInfoByTown(Map<String,Object> map);
Mapper.xml:
<!--获取所有镇-->
<select id="getAllTown" resultType="map">
SELECT DISTINCT town Town FROM ss_person_info
</select>
<!--根据镇获取所有的村-->
<select id="getAllVillage" resultType="map" parameterType="map"> SELECT DISTINCT village Village FROM ss_person_info WHERE town=#{Town} </select>
<!--根据镇和村获取一个村的人员信息-->
<select id="getPayableInfoByTown" parameterType="map" resultMap="BaseResultMap"> select identity_card,name,native_place,town,village from ss_person_info WHERE town=#{Town} and village=#{Village} </select>
实体类:
@TableName("ss_person_info")
public class SsPersonInfo extends Model<SsPersonInfo> {
private static final long serialVersionUID = 1L;
/**
* 身份证
*/
@TableField("identity_card")
private String identityCard;
/**
* 姓名
*/
@TableField("name")
private String name;
/**
* 籍贯
*/
@TableField("native_place")
private String nativePlace;
/**
* 镇
*/
@TableField("town")
private String town;
/**
* 村
*/
@TableField("village")
private String village;
public String getIdentityCard() {
return identityCard;
}
public void setIdentityCard(String identityCard) {
this.identityCard = identityCard;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNativePlace() {
return nativePlace;
}
public void setNativePlace(String nativePlace) {
this.nativePlace = nativePlace;
}
public String getTown() {
return town;
}
public void setTown(String town) {
this.town = town;
}
public String getVillage() {
return village;
}
public void setVillage(String village) {
this.village = village;
}
@Override
protected Serializable pkVal() {
return this.serialVersionUID;
}
@Override
public String toString() {
return "SsPersonInfo{" +
"identityCard=" + identityCard +
", name=" + name +
", nativePlace=" + nativePlace +
", town=" + town +
", village=" + village +
"}";
}
}
PS.根据实体类改动service层和自定义类的内容
自定义读取Excel类:
*
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {
try {
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
return readExcelValue(wb);// 读取Excel里面客户的信息
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读取Excel里面客户的信息
*/
private List<Map<String, Object>> readExcelValue(Workbook wb) {
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
// 循环Excel的列
Map<String, Object> map = new HashMap<String, Object>();
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 1) {
// 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
if (cell.getCellTypeEnum() == NUMERIC) {
String identityCard = String.valueOf(cell.getNumericCellValue());
map.put("identityCard", identityCard.substring(0, identityCard.length() - 2 > 0 ? identityCard.length() - 2 : 1));// 身份证
} else {
map.put("identityCard", cell.getStringCellValue());// 身份证
}
} else if (c == 2) {
if (cell.getCellTypeEnum() == NUMERIC) {
String name = String.valueOf(cell.getNumericCellValue());
map.put("name",name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));// 姓名
} else {
map.put("name",cell.getStringCellValue());// 姓名
}
} else if (c == 17) {
if (cell.getCellTypeEnum() == NUMERIC) {
String nativePlace = String.valueOf(cell.getNumericCellValue());
map.put("nativePlace", nativePlace.substring(0, nativePlace.length() - 2 > 0 ? nativePlace.length() - 2 : 1));// 籍贯
} else {
map.put("nativePlace", cell.getStringCellValue());// 籍贯
}
}
}
}
// 添加到list
userList.add(map);
}
return userList;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\.(?i)(xls)$");
}
// @描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\.(?i)(xlsx)$");
}
}
最终效果:
导入:
导出:
按照镇,村结构导出!!!
