关于excel表导入ssh框架写的mysql数据库中

最新推荐文章于 2021-02-02 21:27:36 发布

置顶 sinat_29185819

最新推荐文章于 2021-02-02 21:27:36 发布

阅读量1.7k

点赞数 3

分类专栏： java和excel表导入

本文链接：https://blog.csdn.net/sinat_29185819/article/details/52785343

版权

java和excel表导入专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第一步、需要一个这个jar，好像是只支持07及以上版本的excel。

第二步、你需要建一个表，属性要和excel里面的一致，然后用hibernate的逆向工程建到model里

第三步、 /**导入excel
* @throws Exception */
public String excel() throws Exception{
String directory = "/file";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
File target = UploadFile.Upload(uploadFile, uploadFileFileName,targetDirectory);
List<Excel> sList = new ArrayList<Excel>();
excelFile = new FileInputStream(target);
Workbook wb = new HSSFWorkbook(excelFile);
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;

for (int i = 1; i < rowNum; i++) {
Excel excel = new Excel();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
System.out.println("现在是第"+cellNum);
for (int j = 0; j < cellNum; j++) {
Cell cell = row.getCell(j);
String cellValue = null;
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:

if(HSSFDateUtil.isCellDateFormatted(cell)){
Date a =cell.getDateCellValue();
DateFormat formater =new SimpleDateFormat("yyyy-MM-dd");
cellValue=formater.format(a);
break;
}else {

cellValue = String
.valueOf((int) cell.getNumericCellValue());
break;
}

case 1:
cellValue = cell.getStringCellValue();
break;

case 2:
cellValue = cell.getStringCellValue();
break;
case 3:
cellValue = cell.getStringCellValue();
break;
case 4:
cellValue = cell.getStringCellValue();
break;
case 5:
cellValue = cell.getStringCellValue();
break;
case 6:
cellValue = cell.getStringCellValue();
break;
case 7:
cellValue = cell.getStringCellValue();
break;
case 8:

if(HSSFDateUtil.isCellDateFormatted(cell)){
Date a =cell.getDateCellValue();
DateFormat formater =new SimpleDateFormat("yyyy-MM-dd");
cellValue=formater.format(a);
break;
}else {

cellValue = String
.valueOf((int) cell.getNumericCellValue());
break;
}

}

switch (j) {// 通过列数来判断对应插如的字段
case 1:
excel.setName(cellValue);
break;
case 2:
excel.setCardid(cellValue);
break;
case 3:
excel.setEdu(cellValue);
break;
case 4:
excel.setMajor(cellValue);
break;
case 5:
excel.setTel(cellValue);
break;
case 6:
excel.setQqnum(cellValue);
break;
case 7:
excel.setCity(cellValue);
break;
case 8:
excel.setDate(cellValue);
break;

}
}
sList.add(excel);
}
accountService.excel(sList);

return "succ";
}