jsp
導入
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(true, "ok");
}
maybatis
INSERT INTO dim_user_gi_file
values
(#{id},#{f_name},#{user_id},#{user_name},#{create_time, jdbcType=DATE})