导表是不能完全copy的，每个表的结构，和需要导入mysql的需求不一样，需要理解了，按自己的需求导表：
首先添加jar包：
<!--导excel到数据库 -->
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
html页面和js：
<h3>导入excel表格</h3>
<form action="uploadFile" method="post" enctype="multipart/form-data" οnsubmit="return check();">
<input type="file" name="file" id="uploadEventFile">
</form>
<button type="button" class="btn btn-success btn-sm" οnclick="user.uploadBtn()" >上传</button>
</body>
<script type="text/javascript">
function uploadBtn(){
//模拟上传excel
$("#uploadEventBtn").unbind("click").bind("click",function(){
$("#uploadEventFile").click();
});
$("#uploadEventFile").bind("change",function(){
$("#uploadEventPath").attr("value",$("#uploadEventFile").val());
});
//点击上传按钮
var uploadEventFile = $("#uploadEventFile").val();
if(uploadEventFile == ''){
layer.alert("请选择excel,再上传", {icon: 6});
}else if(uploadEventFile.lastIndexOf(".xls")<0){//可判断以.xls和.xlsx结尾的excel
layer.alert("只能上传Excel文件", {icon: 6});
}else{
$.ajax({
url : 'uploadFile',
type : 'post',
data : new FormData($('form')[0]),
success : function(result) {
layer.alert(result, {icon: 6});
},
error : function() {
layer.msg('excel上传失败', {icon: 5});
},
cache : false,
contentType : false,
processData : false
});
}
};
</script>
controller里的代码:
/**接收上传的文件*/
@RequestMapping(value="uploadFile",method=RequestMethod.POST)
@ResponseBody
public boolean upload(@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response){
boolean a = false;
String fileName = file.getOriginalFilename();
try {
a = schoolTaskService.batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return a;
}
service接口:
public interface SchoolTaskService {
/**
* 读取excel中的数据,生成list
*/
boolean batchImport(String fileName, MultipartFile file) throws Exception;
}
serviceImp代码:
@Service
public class SchoolTaskServiceImp implements SchoolTaskService{
@Autowired
public SchoolTaskDao schoolTaskDao;
@Transactional(readOnly = false,rollbackFor = Exception.class)
@Override
public boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notNull = false;
if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
throw new MyException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
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){
notNull = true;
}
int totalRows=sheet.getLastRowNum();//得到表的最后行数（总行数）
// System.out.println("rowNum="+totalRows);
List<SchoolTask> userList = new ArrayList<SchoolTask>();
String[] strs = new String[8];
Row rowtitle=sheet.getRow(0);//得到表下标为0的第一行
System.out.println(rowtitle.getLastCellNum());//得到表的总列数
for(int y=1;y<rowtitle.getLastCellNum();y++) {
strs[y-1]=getValue(rowtitle.getCell(y));//把标题放进数组
}
String subjectName=fileName.substring(0,fileName.lastIndexOf("."));
for (int r = 1; r <= totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
String schoolName = getValue(row.getCell(0));//得到下标为0的第一列的所有值
// System.out.println("rowCell="+row.getLastCellNum());
for(int i=1;i<row.getLastCellNum();i++) {
SchoolTask info = new SchoolTask();
info.setSubjectName(subjectName);
info.setSchoolName(schoolName);
info.setQuestionName(strs[i-1]);
info.setTask(Integer.parseInt(getValue(row.getCell(i))));
userList.add(info);
}
}
for (SchoolTask userResord : userList) {
String school = userResord.getSchoolName();
String subject = userResord.getSubjectName();
schoolTaskDao.deleteBySS(school,subject);
}
for (SchoolTask userResord : userList) {
schoolTaskDao.save(userResord);
}
return notNull;
}
private String getValue(Cell cell) {
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
//如果为时间格式的内容
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
value=sdf.format(HSSFDateUtil.getJavaDate(cell.
getNumericCellValue())).toString();
break;
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
return value;
}
}
加一个util里面抛出的异常：
接下来就是dao里面把得到的数据save进mysql：
弹出true在查看mysql数据库就能成功，mysql数据库要创建空表，这个非常重要！！！
/**
* 接收上传的文件
*/
@RequestMapping(value = "uploadFile", method = RequestMethod.POST)
@ResponseBody
public boolean upload(@RequestParam(value = "file", required = false) MultipartFile file,
HttpServletRequest request, HttpServletResponse response) {
boolean a = false;
String fileName = file.getOriginalFilename();
try {
a = batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return a;
}
@Transactional(readOnly = false, rollbackFor = Exception.class)
public boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notNull = false;
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
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) {
notNull = true;
}
int totalRows = sheet.getLastRowNum();//得到表的最后行数（总行数）
// System.out.println("rowNum="+totalRows);
List<SchoolTask> userList = new ArrayList<SchoolTask>();
String[] strs = new String[8];
Row rowtitle = sheet.getRow(0);//得到表下标为0的第一行
System.out.println(rowtitle.getLastCellNum());//得到表的总列数
for (int y = 1; y < rowtitle.getLastCellNum(); y++) {
strs[y - 1] = getValue(rowtitle.getCell(y));//把标题放进数组
}
String subjectName = fileName.substring(0, fileName.lastIndexOf("."));
for (int r = 1; r <= totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
String schoolName = getValue(row.getCell(0));//得到下标为0的第一列的所有值
// System.out.println("rowCell="+row.getLastCellNum());
for (int i = 1; i < row.getLastCellNum(); i++) {
SchoolTask info = new SchoolTask();
info.setSubjectName(subjectName);
info.setSchoolName(schoolName);
info.setQuestionName(strs[i - 1]);
info.setTask(Integer.parseInt(getValue(row.getCell(i))));
userList.add(info);
}
}
for (SchoolTask userResord : userList) {
String school = userResord.getSchoolName();
String subject = userResord.getSubjectName();
schoolTaskDao.deleteBySS(school, subject);
}
for (SchoolTask userResord : userList) {
schoolTaskDao.save(userResord);
}
return notNull;
}
private String getValue(Cell cell) {
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
//如果为时间格式的内容
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
value = sdf.format(HSSFDateUtil.getJavaDate(cell.
getNumericCellValue())).toString();
break;
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
return value;
}
