jsp
<div class="subtext1">
<div class="rtxt">
<input id="importData" name="importData" type="file"><a href="javascript:importData();">导入</a>
</div>
</div>
jquery
//数据导入
function importData(){
var files = $('input[name="importData"]').prop('files');//获取到文件列表
if(files.length == 0){
alert('请选择文件');
return;
}else{
var formData = new FormData();
var name = $("#importData").val();
formData.append("file",$("#importData")[0].files[0]);
formData.append("name",name);
var reg = /^.*\.(?:xls|xlsx)$/i;
if (!reg.test(name)) {//校验是否是excel格式的文件
alert("请上传excel格式的文件!");
return;
}
var url = "url";
$.ajax({
type : "post",
async : false,
traditional : true,
url : url,
dataType : "json",
data : formData,
processData : false,
contentType : false,
success : function(result) {
if (result.success) {
//保存刷新
//........
alert("导入成功!");
}
},
error : function(errorMsg) {
alert("导入失败!");
}
});
}
}
java
@Autowired
private IDimUserFileConfService dimUserFileConfService;
@Autowired
private IDimUserGiConfService dimUserGiConfService;
@RequestMapping(value = "importExel.json", method = RequestMethod.POST)
@ResponseBody
public Object importdata(@RequestParam("file") MultipartFile file,HttpServletRequest request, HttpServletResponse response,@RequestParam("user_id") String user_id,
@RequestParam("user_name") String user_name) {
String id = user_id+DateUtils.getDateTimeStr();
Date create_time = new Date();
String filename = file.getOriginalFilename();//原文件名字
XSSFWorkbook workbook1 = null;
HSSFWorkbook workbook2 = null;
try {
InputStream is = file.getInputStream();//获取输入流
if(filename.endsWith(".xlsx")){
workbook1 = new XSSFWorkbook(is);
XSSFSheet sheet = workbook1.getSheetAt(0); // 创建对工作表的引用
int rows = sheet.getPhysicalNumberOfRows();// 获取表格的
int columns = 0;
for (int r = 0; r < rows; r++) { // 循环遍历表格的行
if(r==0){
//在第一行标题行计算出列宽度,因为数据行中可能会有空值
columns = sheet.getRow(r).getLastCellNum();
continue;
}
String value = "";
Row row = sheet.getRow(r); // 获取单元格中指定的行对象
if (row != null) { //遍历单元格
for(int i = 0 ; i < columns; i++) {
Cell cell = row.getCell(i);
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
if (temp.indexOf(".") > -1) { //处理有小数的读取错误
value += String.valueOf(new Double(temp)).trim()+",";
} else {
value += temp.trim()+",";
}
}
}
String[] str = value.split(",");
int k = 0;
if(r % 200 == 0) {//文件拆分,每200行一个文件
k++;
id = k+user_id+DateUtils.getDateTimeStr()+"p";
//保存文件信息
dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());
}
//处理用户地理信息保存
dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);
}
if(!id.endsWith("p")) {
//保存文件信息
dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);
}
} else {
workbook2 = new HSSFWorkbook(is);
HSSFSheet sheet = workbook2.getSheetAt(0); // 创建对工作表的引用
int rows = sheet.getPhysicalNumberOfRows();// 获取表格的
int columns = 0;
for (int r = 0; r < rows; r++) { // 循环遍历表格的行
if(r==0){
//在第一行标题行计算出列宽度,因为数据行中可能会有空值
columns = sheet.getRow(r).getLastCellNum();
continue;
}
String value = "";
Row row = sheet.getRow(r); // 获取单元格中指定的行对象
if (row != null) { //遍历单元格
for(int i = 0 ; i < columns; i++) {
Cell cell = row.getCell(i);
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
if (temp.indexOf(".") > -1) { //处理有小数的读取错误
value += String.valueOf(new Double(temp)).trim()+",";
} else {
value += temp.trim()+",";
}
}
}
String[] str = value.split(",");
int k = 0;
if(r % 200 == 0) {//文件拆分,每200行一个文件
k++;
id = k+user_id+DateUtils.getDateTimeStr()+"p";
//保存文件信息
dimUserFileConfService.addUserGiFile(id, filename+"_p"+k, user_id, user_name, new Date());
}
//处理用户地理信息保存
dimUserGiConfService.addUserGi(str[0], str[1], str[2], id);
}
if(!id.endsWith("p")) {
//保存文件信息
dimUserFileConfService.addUserGiFile(id, filename, user_id, user_name, create_time);
}
}
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if(workbook1 != null) {
workbook1.close();
}
if(workbook2 != null) {
workbook2.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return new RestResult<Object>(true, "ok");
}
maybatis
<!-- 导入文件 -->
<insert id="addUserGiFile">
INSERT INTO dim_user_gi_file
values
(#{id},#{f_name},#{user_id},#{user_name},#{create_time, jdbcType=DATE})
</insert>