bootstrap-fileinput结合POI实现Excel导入数据库的功能

本文主要是介绍bootstrap-fileinput结合POI实现Excel导入数据库的功能，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、前台

1.首先引入

<link rel="stylesheet" href="${contextPath}/resources/plugs/bootstrap-fileinput/css/fileinput.min.css"/>
<script src="${contextPath}/resources/plugs/bootstrap-fileinput/js/fileinput.min.js"></script>
<script src="${contextPath}/resources/plugs/bootstrap-fileinput/js/locales/zh.js"></script>

2.JSP页面 

bootstrap的导入的模态框

<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal"><i class="fa fa-file-excel-o"></i>批量导入<!-- 模态框（Modal） --><div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true"><div class="modal-dialog"><div class="modal-content"><div class="modal-header"><button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button><h4 class="modal-title" id="myModalLabel">批量导入</h4></div><div class="modal-body"><form id="importFile" name="importFile" class="form-horizontal" method="post" enctype="multipart/form-data"><div><label class="control-label">请选择要导入的excel文件：</label><input id="excelFile" name="excelFile" class="file-loading" type="file" multiple accept=".xls,.xlsx"></div>	</form></div><div class="modal-footer"></div></div><!-- /.modal-content --></div><!-- /.modal --></div>

3.JS

	//批量导入excelinitUpload("excelFile","/admin/excelInput");function initUpload(ctrlName,uploadUrl){var control = $("#"+ctrlName);control.fileinput({language:"zh",//设置语言uploadUrl:uploadUrl,//上传的地址maxFilesNum : 1000,//上传最大的文件数量  uploadAsync: true, //默认异步上传  showUpload: false, //是否显示上传按钮  showRemove : true, //显示移除按钮  showPreview : true, //是否显示预览autoReplace:false, //再次选中文件时,不会替换掉当前文件showCaption: true,//是否显示标题  browseClass: "btn btn-primary", //按钮样式  maxFileSize: 0,//单位为kb，如果为0表示不限制文件大小  enctype: 'multipart/form-data',  validateInitialCount:true,  previewFileIcon: "<i class='glyphicon glyphicon-king'></i>",  msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}！", });}$("#excelFile").on("fileuploaded",function(exevt,data,previewId,index){if(data.response.success == true){alert("导入成功");$("#excelFile").fileinput("clear");$("#excelFile").fileinput("reset");$("#excelFile").fileinput("refresh");$("#excelFile").fileinput("enable");$(".close").click();$("#reload").click();$('.close.fileinput-remove').hide();}else{alert("导入失败");$("#excelFile").fileinput("clear");$("#excelFile").fileinput("reset");$("#excelFile").fileinput("refresh");$("#excelFile").fileinput("enable");$('.close.fileinput-remove').hide();}});

二、后台

1.controller

    @RequestMapping("/admin/excelInput")@ResponseBodypublic Result importExcel(@RequestParam(value="excelFile",required=false)MultipartFile file,HttpServletRequest request) throws IOException, InterruptedException{MultipartRequest multipartRequest = (MultipartRequest)request;MultipartFile excelFile = multipartRequest.getFile("excelFile");CommonsMultipartFile cf = (CommonsMultipartFile)file;DiskFileItem fi = (DiskFileItem) cf.getFileItem();File files = fi.getStoreLocation();if(excelFile != null){String fileName = excelFile.getOriginalFilename();String type = fileName.substring(fileName.lastIndexOf(".")+1);if("xls".equals(type)){String[][] data = ExcelOperate.getData(files, 1);//读取的内容后处理for(int i = 0; i < data.length; i++) {System.out.println("第i行第一列数据的值"+data[i][0]);}return new Result(true);}else {return new Result(false,"请使用xls格式导入！");}}else{return new Result(false);}}

2.自定义返回结果集Result类

public class Result {private boolean success;private String message;public Result(boolean success){this.success = success;}public Result(boolean success,String message){this.success = success;this.message = message;}public boolean isSuccess(){return success;}public void setSuccess(boolean success){this.success = success;}public String getMessage(){return message;}public void setMessage(String message){this.message = message;}
}

3.解析Excel的工具类

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class ExcelOperate {public static void main(String[] args) throws Exception {try {File file = new File("");String[][] result = getData(file, 1);int rowLength = result.length;for (int i = 0; i < rowLength; i++) {for (int j = 0; j < result[i].length; j++) {System.out.print(result[i][j] + "\t\t");}System.out.println();}} catch (Exception ex) {ex.printStackTrace();}}/*** 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行* * @param file*            读取数据的源Excel* @param ignoreRows*            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1* @return 读出的Excel中数据的内容* @throws FileNotFoundException* @throws IOException*/public static String[][] getData(File file, int ignoreRows)throws FileNotFoundException, IOException {List<String[]> result = new ArrayList<String[]>();int rowSize = 0;BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));// 打开HSSFWorkbookPOIFSFileSystem fs = new POIFSFileSystem(in);HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFCell cell = null;for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {HSSFSheet st = wb.getSheetAt(sheetIndex);// 第一行为标题，不取for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {HSSFRow row = st.getRow(rowIndex);if (row == null) {continue;}int tempRowSize = row.getLastCellNum() + 1;if (tempRowSize > rowSize) {rowSize = tempRowSize;}String[] values = new String[rowSize];Arrays.fill(values, "");boolean hasValue = false;for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {String value = "";cell = row.getCell(columnIndex);if (cell != null) {// 注意：一定要设成这个，否则可能会出现乱码// cell.setEncoding(HSSFCell.ENCODING_UTF_16);switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {Date date = cell.getDateCellValue();if (date != null) {value = new SimpleDateFormat("yyyy-MM-dd").format(date);} else {value = "";}} else {value = new DecimalFormat("0").format(cell.getNumericCellValue());}break;case HSSFCell.CELL_TYPE_FORMULA:// 导入时如果为公式生成的数据则无值if (!cell.getStringCellValue().equals("")) {value = cell.getStringCellValue();} else {value = cell.getNumericCellValue() + "";}break;case HSSFCell.CELL_TYPE_BLANK:break;case HSSFCell.CELL_TYPE_ERROR:value = "";break;case HSSFCell.CELL_TYPE_BOOLEAN:value = (cell.getBooleanCellValue() == true ? "Y": "N");break;default:value = "";}}if (columnIndex == 0 && value.trim().equals("")) {break;}values[columnIndex] = rightTrim(value);hasValue = true;}if (hasValue) {result.add(values);}}}in.close();String[][] returnArray = new String[result.size()][rowSize];for (int i = 0; i < returnArray.length; i++) {returnArray[i] = (String[]) result.get(i);}return returnArray;}/*** 去掉字符串右边的空格* * @param str*            要处理的字符串* @return 处理后的字符串*/public static String rightTrim(String str) {if (str == null) {return "";}int length = str.length();for (int i = length - 1; i >= 0; i--) {if (str.charAt(i) != 0x20) {break;}length--;}return str.substring(0, length);}
}

 

这篇关于bootstrap-fileinput结合POI实现Excel导入数据库的功能的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---