java-文件上传-excel存入数据库全代码及流程（附前端代码）

今天给大家带来的是文件上传中比较常用的，上传excel文件，将表格中的数据存入数据库中的一个转化的工具类；大致的流程是：前端点击上传按钮–>选择需要上传的excel表格–>确认上传–>文件传到后台–>后台处理file文件–>将文件转化成List–>将List集合存入数据库

首先通过前端点击按钮：

 导入

点击导入按钮，触发input框点击事件：

 $('#input').click(function () {$('#import_modal').modal('show');})

点击按钮后，弹出框：

弹窗代码：

×Close
上传数据
 上传文件确定取消

选择要上传的文件，然后点击确定：

点击确定触发：将请求地址改成自己本地的

 $('#import_modal form').bootstrapValidator('destroy');$('#import_modal form').bootstrapValidator({message: '输入不合法',fields: {file: {validators: {notEmpty: {message: '文件不能为空'}}}}}).on('success.form.bv', function (e) {var files = $("#file").get(0).files;if (files.length > 0) {if (files[0].size > 1024 * 1024 * 100) {alert('文件大小不能超过100MB');return false;}var formData = new FormData();formData.append('file', files[0]);$.ajax({url: '${pageContext.request.contextPath}/backend/form/evaluation_update/import',type: 'POST',cache: false,data: formData,processData: false,contentType: false,dataType: "json",beforeSend: function () {$('#import').button('loading');},success: function (data) {if (data.errorCode === 0) {alert(data.message);$('#import_modal').modal('hide');location.reload();} else {alert(data.message);}$('#import').button('reset');}, error: function () {$('#import').button('reset');}});}});

请求到后台：

poi依赖

org.apache.poipoi3.14org.apache.poipoi-ooxml3.14

controller层

@RequestMapping("evaluation_update/import")@ResponseBodypublic Result evaluationUpdate(@RequestParam(value = "file", required = true) MultipartFile file,UserSession session) throws Exception {try {Result result = new Result();result.setMessage("导入成功");int count = wfOrderService.importAnnualDemand(session, file);result.setMessage(count > 0 " />

service层：

public int importAnnualDemand(UserSession session, MultipartFile file) throws ErrorCodeException {List<Map<String, ArrayList>> allDateList = new ArrayList();Listlist=new ArrayList();List dataList;try {String filePath=getFilePath(file);Map insert = new HashMap();insert.put("file", filePath);File excelFile = uploadManager.file(filePath);dataList = POIExcelUtil.readExcel(excelFile, 1, false);System.out.println(dataList);for (String[] strings : dataList) {Requirement r = new Requirement();r.setErpCode(strings[0]);r.setAnnualDemand(new BigDecimal(strings[1]));r.setYear(strings[2]);list.add(r);}if(!list.isEmpty()){return this.insert(STATEMENT_ID + "importAnnualDemand", list);}return 0;}catch (Exception e){e.printStackTrace();return 0;}}

POIExcelUtil.readExcel工具类代码(拷贝即用)：

//包名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 java.io.*;import java.text.DecimalFormat;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.*;public class POIExcelUtil {private final static String XLS = "xls";private final static String XLSX = "xlsx";private static NumberFormat numberFormat = NumberFormat.getNumberInstance();static {numberFormat.setGroupingUsed(false);}public static List readExcel(File file, int firstRowNum, boolean needTitle) throws IOException {// 检查文件checkFile(file);Workbook workBook = getWorkBook(file);// 返回对象,每行作为一个数组，放在集合返回ArrayList rowList = new ArrayList();if (null != workBook) {// 获得当前sheet工作表Sheet sheet = workBook.getSheetAt(0);if (sheet != null) {// 获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();sheet.getSheetName();int firstCellNum = sheet.getRow(firstRowNum - 1).getFirstCellNum();int lastCellNum = sheet.getRow(firstRowNum - 1).getLastCellNum();if (lastCellNum > 200) {lastCellNum = 200;}if (needTitle) {// 获取标题行，并返回在第一个list元素Row titleRow = sheet.getRow(firstRowNum - 1);String[] titleCells = new String[lastCellNum];// 循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = titleRow.getCell(cellNum);titleCells[cellNum] = getOriginalCellValue(cell);}rowList.add(titleCells);}// 循环所有行数据for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {// 获得当前行Row row = sheet.getRow(rowNum);if (row == null) {continue;}String[] cells = new String[lastCellNum];// 循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);cells[cellNum] = getOriginalCellValue(cell);}rowList.add(cells);}}workBook.close();}return rowList;}public static List readExcelPrecision(File file, int firstRowNum) throws IOException {// 检查文件checkFile(file);Workbook workBook = getWorkBook(file);// 返回对象,每行作为一个数组，放在集合返回ArrayList rowList = new ArrayList();if (null != workBook) {// 获得当前sheet工作表Sheet sheet = workBook.getSheetAt(0);if (sheet != null) {// 获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();sheet.getSheetName();int firstCellNum = sheet.getRow(firstRowNum - 1).getFirstCellNum();int lastCellNum = sheet.getRow(firstRowNum - 1).getLastCellNum();if (lastCellNum > 200) {lastCellNum = 200;}// 循环所有行数据for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {// 获得当前行Row row = sheet.getRow(rowNum);if (row == null) {continue;}String[] cells = new String[lastCellNum];// 循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);cells[cellNum] = getOriginalCellValuePrecision(cell);}rowList.add(cells);}}workBook.close();}return rowList;}public static List<Map<String, ArrayList>> readMultiExcel(File file, int firstRowNum, int firstRowNum2, boolean needTitle) throws IOException {// 检查文件checkFile(file);Workbook workBook = getWorkBook(file);List<Map<String, ArrayList>> resultList = new ArrayList();// 返回对象,每行作为一个数组，放在集合返回if (null != workBook) {// 获得当前sheet工作表for (int w = 0; w < workBook.getNumberOfSheets(); w++) {Sheet sheet = workBook.getSheetAt(w);if (sheet != null) {ArrayList rowList = new ArrayList();Map<String, ArrayList> sheetMap = new HashMap();String sheetName = sheet.getSheetName();// 获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();int firstCellNum = sheet.getRow(w > 0 ? firstRowNum2 - 1 : firstRowNum - 1).getFirstCellNum();int lastCellNum = sheet.getRow(w > 0 ? firstRowNum2 - 1 : firstRowNum - 1).getLastCellNum();if (needTitle) {// 获取标题行，并返回在第一个list元素Row titleRow = sheet.getRow(w > 0 ? firstRowNum2 - 1 : firstRowNum - 1);String[] titleCells = new String[lastCellNum];// 循环当前行for (int cellNum = firstCellNum; cellNum  0 ? firstRowNum2 - 1 : firstRowNum - 1; rowNum <= lastRowNum; rowNum++) {// 获得当前行Row row = sheet.getRow(rowNum);if (row == null) {continue;}String[] cells = new String[lastCellNum];// 循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);cells[cellNum] = getOriginalCellValueByMulti(cell);}rowList.add(cells);}sheetMap.put(sheetName, rowList);resultList.add(sheetMap);}}workBook.close();}return resultList;}public static String getOriginalCellValueByMulti(Cell cell) {DecimalFormat originalValueDecimalFormat = new DecimalFormat("#.##");SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");if (cell == null) {return "";}int cellType = cell.getCellType();switch (cellType) {case Cell.CELL_TYPE_FORMULA:return numberFormat.format(cell.getNumericCellValue());case Cell.CELL_TYPE_NUMERIC:if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {Date dateCellValue = cell.getDateCellValue();if (dateCellValue != null) {return simpleDateFormat.format(dateCellValue);}return "";}return originalValueDecimalFormat.format(cell.getNumericCellValue());case Cell.CELL_TYPE_STRING:return cell.getStringCellValue();case Cell.CELL_TYPE_BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case Cell.CELL_TYPE_BLANK:return "";case Cell.CELL_TYPE_ERROR:return String.valueOf(cell.getErrorCellValue());}return "";}public static String getOriginalCellValue(Cell cell) {DecimalFormat originalValueDecimalFormat = new DecimalFormat("#.##");SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");if (cell == null) {return "";}int cellType = cell.getCellType();switch (cellType) {case Cell.CELL_TYPE_FORMULA:return String.valueOf(cell.getNumericCellValue());case Cell.CELL_TYPE_NUMERIC:if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {Date dateCellValue = cell.getDateCellValue();if (dateCellValue != null) {return simpleDateFormat.format(dateCellValue);}return "";}return originalValueDecimalFormat.format(cell.getNumericCellValue());case Cell.CELL_TYPE_STRING:return cell.getStringCellValue();case Cell.CELL_TYPE_BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case Cell.CELL_TYPE_BLANK:return "";case Cell.CELL_TYPE_ERROR:return String.valueOf(cell.getErrorCellValue());}return "";}public static String getOriginalCellValuePrecision(Cell cell) {DecimalFormat originalValueDecimalFormat = new DecimalFormat("#.####");SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");if (cell == null) {return "";}int cellType = cell.getCellType();switch (cellType) {case Cell.CELL_TYPE_FORMULA:return String.valueOf(cell.getNumericCellValue());case Cell.CELL_TYPE_NUMERIC:if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {Date dateCellValue = cell.getDateCellValue();if (dateCellValue != null) {return simpleDateFormat.format(dateCellValue);}return "";}return originalValueDecimalFormat.format(cell.getNumericCellValue());case Cell.CELL_TYPE_STRING:return cell.getStringCellValue();case Cell.CELL_TYPE_BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case Cell.CELL_TYPE_BLANK:return "";case Cell.CELL_TYPE_ERROR:return String.valueOf(cell.getErrorCellValue());}return "";}/** * 获得工作簿对象 */private static Workbook getWorkBook(File file) throws IOException {String filename = file.getName();Workbook workbook = null;InputStream is = new FileInputStream(file);if (filename.endsWith(XLS)) {// 2003workbook = new HSSFWorkbook(is);} else if (filename.endsWith(XLSX)) {// 2007workbook = new XSSFWorkbook(is);}return workbook;}/** * 检查文件 */private static void checkFile(File file) throws IOException {if (null == file) {throw new FileNotFoundException("文件不存在！");}// 获取文件名String filename = file.getName();// 判断是否为excel文件if (!filename.endsWith(XLS) && !filename.endsWith(XLSX)) {throw new IOException(filename + "不是excel文件");}}/** * 取单元格的值 */private static String getCellValue(Cell cell) {String cellValue = "";if (cell == null) {return cellValue;}// 把数字当成String来读，防止1读成1.0if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 日期格式short format = cell.getCellStyle().getDataFormat();if (format == 14 || format == 31 || format == 57 || format == 58|| (182 <= format && format <= 196)|| (210 <= format && format <= 213) || (208 == format)) { // 日期SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);if (date == null || "".equals(date)) {return "";}return sdf.format(date);} else { // 不是日期格式cell.setCellType(Cell.CELL_TYPE_STRING);}}// 判断数据的类型switch (cell.getCellType()) {// 数字case Cell.CELL_TYPE_NUMERIC:cellValue = String.valueOf(cell.getNumericCellValue());break;// 字符串case Cell.CELL_TYPE_STRING:cellValue = String.valueOf(cell.getStringCellValue()).trim().replaceAll(String.valueOf((char) 10), "").replaceAll(String.valueOf((char) 11), "").replaceAll(String.valueOf((char) 12), "").replaceAll(String.valueOf((char) 13), "");break;// 布尔case Cell.CELL_TYPE_BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());break;// 公式case Cell.CELL_TYPE_FORMULA://cellValue = String.valueOf(cell.getCellFormula());try {cellValue = String.valueOf(cell.getNumericCellValue());} catch (IllegalStateException e) {cellValue = String.valueOf(cell.getRichStringCellValue());}break;// 空case Cell.CELL_TYPE_BLANK:cellValue = "";break;// 错误case Cell.CELL_TYPE_ERROR:cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}}

service注入一个上传类：

@Autowiredprivate UploadManager uploadManager;

上传工具类（拷贝即用）：

import com.yogapay.core.LangUitls;import com.yogapay.sql.mapping2.StringListSQLData;import java.io.File;import java.io.IOException;import java.net.URI;import java.util.Collections;import java.util.Date;import java.util.HashMap;import java.util.Map;import javax.servlet.ServletContext;import org.apache.commons.lang.StringUtils;import org.springframework.beans.factory.InitializingBean;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.multipart.MultipartFile;public class UploadManager implements InitializingBean {public static final String PATH_PREFIX = "/uploads";private final String basePath;private final String[] dirNames;@Autowired(required = false)private ServletContext sc;//private File baseDir;private URI baseDirUri;private Map dirs;public UploadManager(String basePath, String dirNames) {this.basePath = StringUtils.trimToNull(basePath);this.dirNames = dirNames.split("\\s+");}@Overridepublic void afterPropertiesSet() throws Exception {if (sc == null) {baseDir = new File("target");} else {baseDir = new File(basePath == null ? sc.getRealPath("uploads") : basePath);}baseDirUri = baseDir.toURI();dirs = new HashMap();for (String dir : dirNames) {dirs.put(dir, createDir(dir));}dirs = Collections.unmodifiableMap(dirs);}private File createDir(String name) {File t = new File(baseDir, name);if (!t.mkdir() && !t.exists()) {throw new RuntimeException(t.getAbsolutePath());}return t;}public File file(String src) {if (!src.startsWith(PATH_PREFIX)) {throw new IllegalArgumentException();}src = src.substring(PATH_PREFIX.length());if (src.isEmpty() || src.charAt(0) != '/') {throw new IllegalArgumentException();}return new File(baseDirUri.resolve(src.substring(1)));}public String src(File f) {URI uri = baseDirUri.relativize(f.toURI());if (uri.isAbsolute()) {throw new IllegalArgumentException();}return PATH_PREFIX + "/" + uri;}public StringListSQLData saveDailyFiles(File rdir, Date date, MultipartFile[] files, String prefix, String suffix) throws IOException {if (files == null) {return null;}File dir = LangUitls.dailyFile(rdir, date);StringListSQLData fileList = new StringListSQLData();for (MultipartFile img : files) {if (!img.isEmpty()) {File f = File.createTempFile(prefix, suffix, dir);img.transferTo(f);fileList.add(src(f));}}return fileList;}public File getBaseDir() {return baseDir;}public Map getDirs() {return dirs;}}

StringListSQLData：

public class StringListSQLData extends ArrayList implements SQLDataConvertible {public StringListSQLData(int initialCapacity) {super(initialCapacity);}public StringListSQLData() {}public StringListSQLData(Collection c) {super(c);}@Overridepublic void toSQLData(PreparedStatement pstmt, int index) throws SQLException {Element eArray = DocumentHelper.createElement("List");for (String t : this) {Element eValue = DocumentHelper.createElement("value");eValue.setText(t == null ? "" : t);eArray.add(eValue);}pstmt.setString(index, eArray.asXML());}@Overridepublic boolean fromSQLData(ResultSet rs, int index) throws SQLException {String xml = rs.getString(index);if (rs.wasNull()) {return false;}try {Document doc = DocumentHelper.parseText(xml);for (Iterator iterator = doc.getRootElement().elementIterator("value"); iterator.hasNext();) {Element next = iterator.next();this.add(next.getText());}} catch (DocumentException ex) {throw new SQLException("\r\n" + xml, ex);}return true;}

获取文件地址：

public String getFilePath(MultipartFile file) throws IOException {MultipartFile[] files = {file};String targetDir = uploadManager.getBaseDir() + "/" + "";int index = file.getOriginalFilename().lastIndexOf(".");String ext = index > 0 ? file.getOriginalFilename().substring(index + 1) : "";StringListSQLData fileData = uploadManager.saveDailyFiles(new File(targetDir), new Date(), files, "file_", "." + ext);return fileData.get(0);}

最后在Mybatis中将对应映射的sql写好就行了哦

INSERT IGNORE INTO 表名(字段名)VALUES(#{i.属性名})

最后导入成功：

今天的分享结束啦，谢谢大家哦~~