Springboot POIを使用してExcelのデータテーブルをデータベース・ケースにインポート
12659 ワード
ページ:
Controllerレイヤ:
サービス :
のインポート
function upload() {
if (M) {
return M.show();
}
M = jqueryAlert({
'style': 'pc',
'title': ' ',
'content': $("#uploadFileWrap"),
'modal': true,
'contentTextAlign': 'left',
'animateType': 'linear',
'buttons': {
' ': function () {
// excele
// document.getElementById("excelForm").submit();
$(".alert-content #excelForm").submit();
M.close();
},
}
})
}
Controllerレイヤ:
package com.haue.jobonline.controller;
import com.haue.jobonline.service.ExcelService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
/**
* @author
* @Time: 2018/7/21
* @Email:[email protected]
*/
@Controller
public class ExcelController {
@Resource
private ExcelService excelService;
@RequestMapping(value = "/admin/excel")
public String upload(@RequestParam("file_Name") MultipartFile file,Model model) {
boolean flag;
String name = file.getOriginalFilename();
// File file1 = new File("E:\\" + name);
System.out.println(name);
if (file.getOriginalFilename().isEmpty() || file.getSize() == 0) {
String message=" ";
model.addAttribute("m",message);
return "/admin/400";
}
try {
String message=excelService.studentExcel(name,file);
if (!message.equals("success")){
model.addAttribute("m",message);
return "/admin/400";
}
} catch (Exception e) {
e.printStackTrace();
return "/admin/404";
}
return "redirect:/admin/toStudentListIndex";
}
}
サービス :
import org.springframework.web.multipart.MultipartFile;
/**
* @author
* @Time: 2018/7/21
* @Email:[email protected]
*/
public interface ExcelService {
String studentExcel(String fileName, MultipartFile file) throws Exception;
}
import com.haue.jobonline.dao.StudentDao;
import com.haue.jobonline.entity.Student;
import com.haue.jobonline.service.ExcelService;
//import com.haue.jobonline.utils.MD5Utils;
import com.haue.jobonline.service.StudentManagerService;
import com.haue.jobonline.utils.MD5Utils;
import com.haue.jobonline.utils.UUIDUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author
* @Time: 2018/7/21
* @Email:[email protected]
*/
@Service
@Transactional
public class ExcelServiceImpl implements ExcelService {
@Resource
private StudentManagerService studentManagerService;
@Resource
private StudentDao studentDao;
@Override
@Transactional(readOnly = false, rollbackFor = Exception.class)
public String studentExcel(String fileName, MultipartFile file) throws Exception {
MD5Utils md5Utils =new MD5Utils();
UUIDUtils uuidUtils=new UUIDUtils();
String message = "success";
List studentList = new ArrayList();
boolean isExcel2003 = false;
// excle isExcel2003
DecimalFormat df = new DecimalFormat("0");
InputStream is=file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet == null) {
message = "Excel ";
}
Student student;
System.out.println(sheet.getLastRowNum());
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
student = new Student();
//
if (row.getCell(0).getCellType() != 1) {
message=" ( " + (r + 1) + " , ）";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
String name = row.getCell(0).getStringCellValue();
if (name == null || name.isEmpty()) {
message=" ( " + (r + 1) + " , ）";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
//
if (row.getCell(1).getCellType() != 1) {
message=" ( " + (r + 1) + " , ";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
String sex = row.getCell(1).getStringCellValue();
if (sex == null || sex.isEmpty()) {
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
String level=null;
//
if (row.getCell(2).getCellType() == 1) {
level = row.getCell(2).getStringCellValue();
if (level == null || level.isEmpty()) {
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
// if (row.getCell(2).getCellType()==0){
//
// }
// throw new Exception(" ( " + (r + 1) + " , )");
}else if (row.getCell(2).getCellType() == 0){
level=df.format(row.getCell(2).getNumericCellValue());
if (level == null || level.isEmpty()) {
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
}else{
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , ");
}
System.out.println(level);
//
// if (row.getCell(3).getCellType() != 1) {
// throw new Exception(" ( " + (r + 1) + " , )");
// }
// String studentClass = row.getCell(3).getStringCellValue();
//
// if (studentClass == null || studentClass.isEmpty()) {
// throw new Exception(" ( " + (r + 1) + " , )");
// }
String studentClass=null;
if (row.getCell(3)!=null){
if (row.getCell(3)!=null){
if (row.getCell(3).getCellType() == 1) {
studentClass = row.getCell(3).getStringCellValue();
if (studentClass == null || studentClass.isEmpty()){
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
}else if (row.getCell(3).getCellType() == 0){
studentClass =df.format(row.getCell(3).getNumericCellValue());
if (studentClass == null || studentClass.isEmpty()){
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
}
}
} else{
message=" ( " + (r + 1) + " , ))";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
System.out.println(studentClass);
//
if (row.getCell(4).getCellType() != 1) {
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
String studentSpecially = row.getCell(4).getStringCellValue();
System.out.println(studentSpecially);
if (studentSpecially == null || studentSpecially.isEmpty()) {
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
//
String studentNum=null;
if (row.getCell(5)!=null){
if (row.getCell(5).getCellType() == 1) {
studentNum = row.getCell(5).getStringCellValue();
if (studentNum == null || studentNum.isEmpty()){
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
}else if (row.getCell(5).getCellType() == 0){
studentNum =df.format(row.getCell(5).getNumericCellValue());
if (studentNum == null || studentNum.isEmpty()){
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
}
}
else{
message=" ( " + (r + 1) + " , )";
return message;
// throw new Exception(" ( " + (r + 1) + " , )");
}
System.out.println(" :" + name + "---" + " :" + sex + "----" + " :" + level + "-----" + " :" + studentClass + "---" + " :" + studentSpecially + "---" + " :" + studentNum + "");
student.setStudentName(name);
student.setStudentSex(sex);
student.setStudentLevel(level);
student.setStudentClass(studentClass);
student.setStudentSpecialty(studentSpecially);
student.setStudentSchoolNum(studentNum);
student.setStudentLoginPassword(md5Utils.GetMD5Code(studentNum));
student.setStudentId(uuidUtils.getUUID());
//
List list=studentDao.findAll();
for (int i=0;i