MultipartFile文件导入
本章用的是MultipartFile实现的文件导入,页面使用的是html、thymeleaf以及若依封装的js,该章节基于若依一体项目!说明:switch的判断是表格中自己需要数据的列,你需要的字段在第几列写第几列就好。1. 创建实体,这里我就省略了,换成自己需要导入的实体即可。本章结束,如果有什么问题欢迎留言,可发源码!来吧,话不多说上代码。
·
本章用的是MultipartFile实现的文件导入,页面使用的是html、thymeleaf以及若依封装的js,该章节基于若依一体项目!!!!!请注意!!!!!
来吧,话不多说上代码。
1. 创建实体,这里我就省略了,换成自己需要导入的实体即可。
2. 下面是自己写的一个工具类,用于根据文件头来判断文件类型,代码如下:
public class MineTypeUtils {
/**
* 缓存文件头信息-文件头信息
*/
public static final HashMap<String, String> mFileTypes = new HashMap<String, String>();
static {
// images
mFileTypes.put("FFD8FF", "jpg");
mFileTypes.put("89504E47", "png");
mFileTypes.put("47494638", "gif");
mFileTypes.put("49492A00", "tif");
mFileTypes.put("424D", "bmp");
/*CAD*/
mFileTypes.put("41433130", "dwg");
mFileTypes.put("38425053", "psd");
/* 日记本 */
mFileTypes.put("7B5C727466", "rtf");
mFileTypes.put("3C3F786D6C", "xml");
mFileTypes.put("68746D6C3E", "html");
// 邮件
mFileTypes.put("44656C69766572792D646174653A", "eml");
mFileTypes.put("D0CF11E0", "doc");
//excel2003版本文件
mFileTypes.put("D0CF11E0", "xls");
mFileTypes.put("5374616E64617264204A", "mdb");
mFileTypes.put("252150532D41646F6265", "ps");
mFileTypes.put("255044462D312E", "pdf");
mFileTypes.put("504B0304", "docx");
//excel2007以上版本文件
mFileTypes.put("504B0304", "xlsx");
mFileTypes.put("52617221", "rar");
mFileTypes.put("57415645", "wav");
mFileTypes.put("41564920", "avi");
mFileTypes.put("2E524D46", "rm");
mFileTypes.put("000001BA", "mpg");
mFileTypes.put("000001B3", "mpg");
mFileTypes.put("6D6F6F76", "mov");
mFileTypes.put("3026B2758E66CF11", "asf");
mFileTypes.put("4D546864", "mid");
mFileTypes.put("1F8B08", "gz");
}
/**
* <p>Title:getFileType </p>
* <p>Description: 根据文件路径获取文件头信息</p>
*
* @param filePath 文件路径(非网络文件)
* @return 文件头信息
*/
public static String getFileType(String filePath) {
//返回十六进制 如:504B0304
//System.out.println(mFileTypes.get(getFileHeader(filePath))); //xlsx
return mFileTypes.get(getFileHeader(filePath));
}
/**
* <p>Title:getFileTypeByFileInputStream </p>
* <p>Description: 根据文件流获取文件头信息</p>
*
* @param is 文件流
* @return 文件头信息
*/
public static String getFileTypeByFileInputStream(InputStream is) {
return mFileTypes.get(getFileHeaderByFileInputStream(is));
}
/**
* <p>Title:getFileHeader </p>
* <p>Description: 根据网络文件路径获取文件类型信息 </p>
*
* @param netFilePath
* @return 文件类型 @see mFileTypes
* @throws Exception
*/
public static String getFileTypeByNetFilePath(String netFilePath) throws Exception {
trustAllHosts();
URLConnection url = new URL(netFilePath).openConnection();
url.connect();
return getFileTypeByFileInputStream(url.getInputStream());
}
/**
* <p>Title:getFileHeader </p>
* <p>Description: 根据文件路径获取文件头信息 </p>
*
* @param filePath 文件路径
* @return 十六进制文件头信息
*/
private static String getFileHeader(String filePath) {
FileInputStream is = null;
String value = null;
try {
is = new FileInputStream(filePath);
byte[] b = new byte[4];
/*
* int read() 从此输入流中读取一个数据字节。int read(byte[] b) 从此输入流中将最多 b.length
* 个字节的数据读入一个 byte 数组中。 int read(byte[] b, int off, int len)
* 从此输入流中将最多 len 个字节的数据读入一个 byte 数组中。
*/
is.read(b, 0, b.length);
value = bytesToHexString(b);
} catch (Exception e) {
} finally {
if (null != is) {
try {
is.close();
} catch (IOException e) {
}
}
}
return value;
}
/**
* <p>Title:getFileHeaderByFileInputStream </p>
* <p>Description: 根据文件流获取文件头信息</p>
*
* @param is 文件流
* @return 十六进制文件头信息
*/
private static String getFileHeaderByFileInputStream(InputStream is) {
String value = null;
try {
byte[] b = new byte[4];
/*
* int read() 从此输入流中读取一个数据字节。int read(byte[] b) 从此输入流中将最多 b.length
* 个字节的数据读入一个 byte 数组中。 int read(byte[] b, int off, int len)
* 从此输入流中将最多 len 个字节的数据读入一个 byte 数组中。
*/
is.read(b, 0, b.length);
value = bytesToHexString(b);
} catch (Exception e) {
} finally {
if (null != is) {
try {
is.close();
} catch (IOException e) {
}
}
}
return value;
}
/**
* <p>Title:bytesToHexString </p>
* <p>Description: 将要读取文件头信息的文件的byte数组转换成string类型表示 </p>
*
* @param src 要读取文件头信息的文件的byte数组
* @return 文件头信息
*/
private static String bytesToHexString(byte[] src) {
StringBuilder builder = new StringBuilder();
if (src == null || src.length <= 0) {
return null;
}
String hv;
for (int i = 0; i < src.length; i++) {
// 以十六进制(基数 16)无符号整数形式返回一个整数参数的字符串表示形式,并转换为大写
hv = Integer.toHexString(src[i] & 0xFF).toUpperCase();
if (hv.length() < 2) {
builder.append(0);
}
builder.append(hv);
}
//System.out.println(builder.toString());
return builder.toString();
}
/**
* 解决网络证书问题
* 如下异常: unable to find valid certification path to requested target
*
* @throws Exception
*/
public static void trustAllHosts() throws Exception {
// Create a trust manager that does not validate certificate chains
TrustManager[] trustAllCerts = new TrustManager[]{new X509TrustManager() {
@Override
public java.security.cert.X509Certificate[] getAcceptedIssuers() {
return new java.security.cert.X509Certificate[]{};
}
@Override
public void checkClientTrusted(X509Certificate[] chain, String authType)
throws CertificateException {
}
@Override
public void checkServerTrusted(X509Certificate[] chain, String authType)
throws CertificateException {
}
}
};
try {
SSLContext sc = SSLContext.getInstance("TLS");
sc.init(null, trustAllCerts, new java.security.SecureRandom());
HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
} catch (Exception e) {
e.printStackTrace();
}
}
//测试
public static void main(String[] args) {
String panFile = "E:\\杆塔.xls";
String type = MineTypeUtils.getFileType(panFile);
System.out.println(type);
}
}
3. 在业务层撰写读取文件逻辑,代码如下:
说明:switch的判断是表格中自己需要数据的列,你需要的字段在第几列写第几列就好。
public class ReadXlsServiceImpl implements ReadXlsService {
//判断该文件的内容是否是杆塔信息
@Override
public Boolean judgmentFileTower(String excelPath) {
int number = 0;
try {
File excel = new File(excelPath);
if (excel.isFile() && excel.exists()) { //判断文件是否存在
//获取文件类型
String type = MineTypeUtils.getFileType(excelPath);
// String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义
Workbook wb = null;
try {
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(type)) {
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(type)) {
wb = new XSSFWorkbook(excel);
} else {
log.error("文件类型错误!");
}
Sheet sheet = wb.getSheetAt(0); //读取sheet 0
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getFirstRowNum() + 1;
for (int rIndex = firstRowIndex; rIndex < lastRowIndex; rIndex++) { //遍历行
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列
Cell cell = row.getCell(cIndex);
if (cell != null) {
try {
switch (cell.getColumnIndex()) {
case 0: if("设备名称".equals(cell.toString()))
number += 1;
break;
case 5: if("*所属线路".equals(cell.toString()))
number += 1;
break;
case 6: if("PM编号".equals(cell.toString()))
number += 1;
break;
case 8: if("*运维单位".equals(cell.toString()))
number += 1;
break;
case 9: if("*维护班组".equals(cell.toString()))
number += 1;
break;
case 10: if("是否农网".equals(cell.toString()))
number += 1;
break;
case 12: if("*电压等级".equals(cell.toString()))
number += 1;
break;
case 13: if("投运日期".equals(cell.toString()))
number += 1;
break;
case 14: if("*设备状态".equals(cell.toString()))
number += 1;
break;
case 16: if("*杆塔材质".equals(cell.toString()))
number += 1;
break;
case 17: if("*杆塔性质".equals(cell.toString()))
number += 1;
break;
case 51: if("运行设备编码".equals(cell.toString()))
number += 1;
break;
case 63: if("物理设备编码".equals(cell.toString()))
number += 1;
break;
case 65: if("地区特征".equals(cell.toString()))
number += 1;
break;
case 87: if("obj_id".equals(cell.toString()))
number += 1;
break;
case 88: if("x坐标".equals(cell.toString()))
number += 1;
break;
case 89: if("y坐标".equals(cell.toString()))
number += 1;
break;
}
}catch (Exception e) {
log.error(e.toString());
}
}
}
}
}
}catch (Exception e) {
log.error("文本错误:"+e.toString());
}
} else {
log.error("找不到指定的文件");
}
} catch (Exception e) {
e.printStackTrace();
}
if(number == 17){
return true;
}
return false;
}
@Override
public int readXls(String excelPath) {
if(judgmentFileTower(excelPath)){
try {
File excel = new File(excelPath);
if (excel.isFile() && excel.exists()) { //判断文件是否存在
//获取文件类型
String type = MineTypeUtils.getFileType(excelPath);
// String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义
Workbook wb = null;
try {
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(type)) {
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(type)) {
wb = new XSSFWorkbook(excel);
} else {
log.error("文件类型错误!");
}
//开始解析
Sheet sheet = wb.getSheetAt(0); //读取sheet 0
int firstRowIndex = sheet.getFirstRowNum() + 1; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
//System.out.println("firstRowIndex: " + firstRowIndex);
//System.out.println("lastRowIndex: " + lastRowIndex);
List<Power> list = new ArrayList<>();
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
Power power = new Power();
// System.out.println("rIndex: " + rIndex);
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列
Cell cell = row.getCell(cIndex);
if (cell != null) {
try {
switch (cell.getColumnIndex()) {
case 0: power.setEquipmentName(cell.toString());
break;
case 5: power.setLine(cell.toString());
break;
case 6: power.setPmNumber(cell.toString());
break;
case 8: power.setOperation(cell.toString());
break;
case 9: power.setMaintain(cell.toString());
break;
case 10: power.setRuralPowerFrid(cell.toString());
break;
case 12: power.setVoltageClasses(cell.toString());
break;
case 13: power.setCommissioningDate(cell.toString());
break;
case 14: power.setEquipmentStatus(cell.toString());
break;
case 16: power.setTowerTexture(cell.toString());
break;
case 17: power.setNature(cell.toString());
break;
case 51: power.setRunNumber(cell.toString());
break;
case 63: power.setMacNumber(cell.toString());
break;
case 65: power.setRegionalism(cell.toString());
break;
case 87: power.setObjId(cell.toString());
break;
case 88: if(PositionUtil.isNumeric(cell.toString()))
power.setWGSLng(cell.toString());
break;
case 89: if(PositionUtil.isNumeric(cell.toString()))
power.setWGSLat(cell.toString());
break;
}
if(null != power.getWGSLat() && null != power.getWGSLng()){
Gps gcj02 = PositionUtil.gps84_To_Gcj02(Double.valueOf(power.getWGSLat()), Double.valueOf(power.getWGSLng()));
power.setGcj02Lng(String.valueOf(gcj02.getWgLon()));
power.setGcj02Lat(String.valueOf(gcj02.getWgLat()));
Gps bd09 = PositionUtil.gcj02_To_Bd09(gcj02.getWgLat(), gcj02.getWgLon());
power.setBd09Lng(String.valueOf(bd09.getWgLon()));
power.setBd09Lat(String.valueOf(bd09.getWgLat()));
}
}catch (Exception e) {
log.error(e.toString());
}
}
}
list.add(power);
}
}
if(list.size() > 0){
return readXlsMapper.insertXls(list);
}
}catch (Exception e) {
log.error("文本错误:"+e.toString());
}
} else {
log.error("找不到指定的文件");
return 0;
}
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
return -1;
}
}
4. mapper代码如下:
int insertXls(@Param("list") List<Power> powerList);
5. XML文件如下:
<insert id="insertXls" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into station_coord_dict(
equipment_name,
bd09_lng,
bd09_lat,
WGS_lng,
WGS_lat,
gcj02_lng,
gcj02_lat,
line,
operation,
maintain,
voltage_classes,
equipment_status,
tower_texture,
nature,
pm_number,
run_number,
mac_number,
regionalism,
rural_power_frid,
commissioning_date,
obj_id,
flg
) values
<foreach item="data" collection="list" separator=",">
(
#{data.equipmentName},
#{data.bd09Lng},
#{data.bd09Lat},
#{data.WGSLng},
#{data.WGSLat},
#{data.gcj02Lng},
#{data.gcj02Lat},
#{data.line},
#{data.operation},
#{data.maintain},
#{data.voltageClasses},
#{data.equipmentStatus},
#{data.towerTexture},
#{data.nature},
#{data.pmNumber},
#{data.runNumber},
#{data.macNumber},
#{data.regionalism},
#{data.ruralPowerFrid},
#{data.commissioningDate},
#{data.objId},
0
)
</foreach>
on duplicate key update
equipment_name = values(equipment_name),
bd09_lng = values(bd09_lng),
bd09_lat = values(bd09_lat),
WGS_lng = values(WGS_lng),
WGS_lat = values(WGS_lat),
gcj02_lng = values(gcj02_lng),
gcj02_lat = values(gcj02_lat),
line = values(line),
operation = values(operation),
maintain = values(maintain),
voltage_classes = values(voltage_classes),
equipment_status = values(equipment_status),
tower_texture = values(tower_texture),
nature = values(nature),
pm_number = values(pm_number),
run_number = values(run_number),
mac_number = values(mac_number),
regionalism = values(regionalism),
rural_power_frid = values(rural_power_frid),
commissioning_date = values(commissioning_date),
obj_id = values(obj_id)
</insert>
6. controller代码来了:
//将杆塔数据导入数据库
@PostMapping("/importXls")
@ResponseBody
public AjaxResult importXls(@RequestParam("file") MultipartFile file) {
//获取上传文件 MultipartFile
//获取文件的内容
try {
InputStream is = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
//获取原始文件名
String originalFilename = file.getOriginalFilename();
//System.out.println(originalFilename);
//生成一个uuid名称
String uuidFilename = UploadUtils.getUUIDName(originalFilename);
//创建新的文件
File newFile = new File("D:/",uuidFilename);
//将文件输出到目标的文件中
try {
file.transferTo(newFile);
int i = readXlsService.readXls("D:/" + uuidFilename);
newFile.delete();
if(i == -1){
return toAjaxStant(i);
}
return toAjax(i);
} catch (IOException e) {
e.printStackTrace();
}
return toAjax(0);
}
7. 页面代码如下:
<!DOCTYPE html>
<html lang="zh" xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"
xmlns:loding="http://www.w3.org/1999/xhtml">
<head>
<th:block th:include="include :: header('XXXX')"/>
</head>
<body class="gray-bg">
<style>
button.btn.btn-success {
margin-left: 1622px;
}
.fixed-table-toolbar {
float: left;
}
.table-striped {
min-height: 100%;
}
.nav>li>a:hover {
background-color: #2e6da4;
}
.nav>li {
border-radius: 4px;
}
</style>
<div class="container-div">
<ul class="nav nav-pills">
<li role="presentation" style="background-color: #2e6da4"><a href="/readXls" style="color: #FFF">杆塔</a></li>
<li role="presentation"><a href="/readXls/powerPage">变电站</a></li>
</ul>
<div class="btn-group-sm" id="toolbar" role="group">
<form th:action="@{/readXls/importXls}" method="post" enctype="multipart/form-data" style="display: flex"
id="formFile">
<button type="button" class="btn btn-sm btn-primary" style="margin-right: 10px" onclick="submitFile()" id="subButton">
导入杆塔
</button>
<button type="button" class="btn btn-danger" onclick="deletePole()">清空</button>
<input type="file" name="file" id="FileUpload" style="width: 187px; margin-left:10px;margin-top: 5px; display: none" onchange="fileInp()">
<button type="button" class="btn btn-success" onclick="location='/'">返回首页</button>
</form>
</div>
<div class="row" style="height: 94%">
<div class="col-sm-12 select-table table-striped" style="margin-top: 0">
<table id="bootstrap-table"></table>
</div>
</div>
</div>
<div th:include="include :: footer"></div>
<script th:inline="javascript">
var prefix = ctx + "readXls";
var datas = [[${@dict.getType('sys_normal_disable')}]];
$(function () {
var options = {
url: prefix + "/poleList",
showFooter: true,
showSearch: false,
showRefresh: false,
showToggle: false,
showColumns: false,
modalName: "杆塔信息",
pageSize: 15, //每页的记录行数(*)
pageList: [10, 15, 50, 100, 200], //可供选择的每页的行数(*)
footerStyle: footerStyle,
columns: [{
checkbox: true
},
{
field: 'id',
title: '序号'
},
{
field: 'equipmentName',
title: '杆塔名称'
},
{
field: 'line',
title: '所属线路'
},
{
field: 'nature',
title: '杆塔性质'
},
{
field: 'towerTexture',
title: '杆塔材质'
},
{
field: 'macNumber',
title: '物理设备编码'
},
{
field: 'operation',
title: '运维单位'
},
{
field: 'maintain',
title: '运维班组'
},
{
field: 'voltageClasses',
title: '电压等级'
},
{
field: 'equipmentStatus',
title: '设备状态'
},
{
field: 'commissioningDate',
title: '投运日期'
}
]
};
$.table.init(options);
});
function footerStyle(column) {
return {
// userBalance: {
// classes: 'class'
// },
userBalance: {
css: {color: 'red', 'font-weight': 'normal'}
}
}[column.field]
}
function deletePole() {
$.modal.confirm("确认要清空杆塔数据吗?", function () {
$.operate.post(prefix + "/deleteInfoByFlg", {"flg": 0});
})
}
function submitFile() {
$("#FileUpload").click()
}
function fileInp(){
var file = new FormData(document.getElementById("formFile"));
if (file != null || file != '') {
$.ajax({
url: prefix + "/importXls",
type: "post",
dataType: "json",
data: file,
processData: false,
contentType: false,
beforeSend: function () {
$.modal.loading("正在处理中,请稍后...");
$.modal.disable();
},
success: function (result) {
$.operate.ajaxSuccess(result);
$("#FileUpload")[0].value = null
if (result.code === 0){
//调用修改地址接口
$.ajax({
url: prefix + "/updateSitePole",
type: "post",
processData: false,
contentType: false,
beforeSend: function () {
$.modal.loading("正在处理中,请稍后...");
$.modal.disable();
},
success: function (result) {
$.operate.ajaxSuccess(result);
$("#FileUpload")[0].value = null
}
})
}
}
})
}
}
</script>
<style>
</style>
</body>
</html>
本章结束,如果有什么问题欢迎留言,可发源码!
更多推荐
已为社区贡献1条内容
所有评论(0)