把Execl表格中的数据获取出来保存到数据库中
比如我们遇到一些需要把execl表格中的数据保存到数据库中，一条一条保存效率底下而且容易出错，数据量少还好，一旦遇到数据量大的时候就会累死个人啊，下面我们就来把execl表格中数据保存到对应的数据库中
<div id="deploydiv"> <form id="ff" action="<%=request.getContextPath()%>/theta/file/fileReadExcel" method="post" enctype="multipart/form-data"> <table align="center"> <tr> <td>文件:</td> <td><input name="file" class="f1 easyui-filebox"/> <!-- <input name="op" type="hidden" id="op"/></td> --> </tr> <tr> <td colspan="2" align="center"><input type="submit" value="提交"></input></td> </tr> </table> </form> </div>
jsp页面写好之后，进入Controller具体实现类
@RequestMapping("/fileReadExcel")
@ResponseBody
public AjaxCommonResultBean getFileReadExcel(@RequestParam MultipartFile file){
AjaxCommonResultBean res = new AjaxCommonResultBean();
boolean result = filereadservice.readExcelFile(file);
if(result){
res.setSuccess(true);
res.setMessage("提交成功");
}else{
res.setSuccess(false);
res.setMessage("提交失败");
}
return res;
}
具体实现类
public boolean readExcelFile(MultipartFile file) {
boolean result =false;
List<fileReadBean> fileList = getExcelInfo(file);
if(fileList != null && !fileList.isEmpty()){
result = true;
}else{
result = false;
}
return result;
}
public List<fileReadBean> getExcelInfo(MultipartFile file) {
String fileName = file.getOriginalFilename();//获取文件名
String ext = fileName.substring(fileName.lastIndexOf("."));
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
List<fileReadBean> fileList = createExcel(file.getInputStream(),ext);
return fileList;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private List<fileReadBean> createExcel(InputStream is,String ext) {
try{
HSSFWorkbook wb = null;
XSSFWorkbook xwb = null;
List<fileReadBean> fileList = null;
if(".xls".equals(ext)){ //HSSF方式获取文件
wb = new HSSFWorkbook(is);
fileList = readExcelValue(wb); // 读取Excel里面客户的信息
}else if(".xlsx".equals(ext)){ //XSSF方式获取文件
xwb = new XSSFWorkbook(is);
fileList = readXExcelValue(xwb);
}
return fileList;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private List<fileReadBean> readXExcelValue(XSSFWorkbook xwb) {
List<fileReadBean> fileList = new ArrayList<fileReadBean>();
for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xwb.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
int num=fileReaddao.findSame(getValue(xssfRow.getCell(1)));
// 循环列Cell
// for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
// XSSFCell xssfCell = xssfRow.getCell(cellNum);
// if (xssfCell == null) {
// continue;
// }
// System.out.print(" " + getValue(xssfCell));
// }
if(rowNum > 3 && num < 1){
fileReadBean fileread = new fileReadBean();
fileread.setId(UUID.randomUUID().toString());
fileread.setTransactionDate(getValue(xssfRow.getCell(0)));
fileread.setTransationId(getValue(xssfRow.getCell(1)));
fileread.setRemark(getValue(xssfRow.getCell(2)));
fileread.setOtherBankId(getValue(xssfRow.getCell(3)));
fileread.setOtherBankName(getValue(xssfRow.getCell(4)));
fileread.setTransfer(getValue(xssfRow.getCell(5)));
fileread.setPayment(getValue(xssfRow.getCell(6)));
fileread.setReceived(getValue(xssfRow.getCell(7)));
fileread.setBalance(getValue(xssfRow.getCell(8)));
fileReaddao.insertFileRead(fileread); //把文件中的数据插入数据库
fileList.add(fileread);
}
}
}
return fileList;
}
private List<fileReadBean> readExcelValue(HSSFWorkbook wb) {
List<fileReadBean> fileList = new ArrayList<fileReadBean>();
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
//查询是否有重复的交易号
int num=fileReaddao.findSame(getValue(hssfRow.getCell(1)));
// 循环列Cell
// for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
// HSSFCell hssfCell = hssfRow.getCell(cellNum);
// if (hssfCell == null) {
// continue;
// }
// 循环列Cell
if(rowNum > 3 && num < 1){
fileReadBean fileread = new fileReadBean();
fileread.setId(UUID.randomUUID().toString());
fileread.setTransactionDate(getValue(hssfRow.getCell(0)));
fileread.setTransationId(getValue(hssfRow.getCell(1)));
fileread.setRemark(getValue(hssfRow.getCell(2)));
fileread.setOtherBankId(getValue(hssfRow.getCell(3)));
fileread.setOtherBankName(getValue(hssfRow.getCell(4)));
fileread.setTransfer(getValue(hssfRow.getCell(5)));
fileread.setPayment(getValue(hssfRow.getCell(6)));
fileread.setReceived(getValue(hssfRow.getCell(7)));
fileread.setBalance(getValue(hssfRow.getCell(8)));
fileReaddao.insertFileRead(fileread); //把文件中的数据插入数据库
fileList.add(fileread);
}
/* for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
System.out.print(" " + getValue(hssfCell));
}*/
}
}
return fileList;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
//验证文件名是否合格
private boolean validateExcel(String fileName) {
if (fileName == null) {
String errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
这样就可以把execl表格中的数据全部保存到数据库中了！如有不当之处请多多指正，一起交流，共同学习！
