使用POI结合bootstrap-fileinput上传Excel内容到数据库

最新推荐文章于 2025-03-27 15:10:49 发布

_渔歌向晚

最新推荐文章于 2025-03-27 15:10:49 发布

阅读量6.2k

点赞数 1

分类专栏： Java 文章标签： Excel上传 POI bootstrap-fileinput 文件上传

本文链接：https://blog.youkuaiyun.com/lpy1239064101/article/details/79173809

版权

Java 专栏收录该内容

29 篇文章

订阅专栏

之前做过一个上传Excel表格内容到数据库的功能，项目使用的是SSM框架，使用的是POI。
POI全称 PoorObfuscation Implementation,直译为“可怜的模糊实现”，利用POI接口可以通过JAVA操作Microsoft office 套件工具的读写功能。官网：http://poi.apache.org
在官网下载jar包及源码包。
Jar包的引入：

① 对于只操作2003 及以前版本的excel，只需要poi-3.16.jar
② 如果需要同时对2007及以后版本进行操作则需要poi-ooxml-3.16.jar，poi-ooxml-schemas-3.16.jar，以及ooxml-lib目录下的 xmlbeans-2.6.0.jar，dom4j-1.6.1.jar

如果是maven项目，pom.xml中添加如下：

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.16</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
</dependency>

前端页面中我使用的是bootStrap-fileinput进行文件上传，相关API使用可参考：http://blog.youkuaiyun.com/u012526194/article/details/69937741
html中添加：

<div class="col-sm-9">
	<input id="uploadFile" type="file" name="file" multiple data-min-file-count="1">
</div>

在js中对input进行初始化，如下：

function initFileUpload(idName){
	$("#" + idName + "").fileinput({
	    language: 'zh', //设置语言
	    uploadUrl: getContextPath() + '/scsScheduleConfig/importFile', //上传的地址
	    showUpload: false,
	    allowedFileExtensions : ['xls', 'xlsx'],
	    uploadAsync : false,
//	    maxFileCount: 1,
	    browseOnZoneClick: true,
	    dropZoneEnabled : true,
	    showPreview : true,
	    enctype: 'multipart/form-data',
	    showCaption: true,//是否显示标题
	    browseClass: "btn btn-info", //按钮样式      
	    dropZoneEnabled: false,
	    showPreview: true,
	    previewFileIcon: "<i class='glyphicon glyphicon-king'></i>", 
	});
}

bootstrap-fileinput默认是带着一个上传按钮的，可以进行添加扩展参数的，当点击上传按钮，默认就会调用触发后台事件进行上传
，为了使自己定义的Buttton进行上传，我将showUpload属性设置为false,在自己写的onclick事件中调用bootstrap的upload方法。

$('#uploadFile').fileinput('upload');
 var flag = true;
 $('#uploadFile').on('filebatchuploadsuccess', function(event, filestack, extraData) {
	 var data = filestack.response;
	 if (data.success == true) {
		if(flag){
			$("#scsScheduleConfigTable").bootstrapTable('refresh');
			$("#importFileDialog").modal('hide');
			$('#uploadFile').fileinput('reset');
			var count = data.obj;
			$.utils.alertSuccess("成功导入" + count + "条数据");
			flag = false;
		}
		} else {
			$.utils.alertWarning(data.ErrorMessage);
	}
});

至此，页面的代码完成。
后端java代码中使用MultipartFile进行接收文件。
首先在spring中添加文件上传解析器配置，如下：

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<!--one of the properties available;the maximum upload size in bytes 10M -->
		<property name="maxUploadSize" value="10485760" />
</bean>

Controller层的代码如下：

@RequestMapping(value = "/importFile", method = RequestMethod.POST)
@ResponseBody
public Result<Integer> importFile(@RequestParam("file") MultipartFile[] file){
	Result<Integer> resResult = new Result<Integer>(true);
	
	int count = 0;
	if (file != null && file.length > 0) {
		try {
			for(MultipartFile multipartFile : file){
				count += scsScheduleConfigService.importSchedule(multipartFile);
			}
			resResult.setSuccess(true);
			resResult.setObj(count);
		} catch (IOException e) {
			resResult.setSuccess(false);
			resResult.setObj(null);
			resResult.setErrorMessage("importSchedule error");
			logger.error("importSchedule error",e);
		}
	}
	return resResult;
}

中间的过程没什么，主要来看一下对Excel的处理代码：

package com.arain.scs;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * @author Arain.liu 2017年8月8日 下午3:05:07
 */
public class ExcelUtils {
	/**
	 * read the Excel file
	 * 
	 * @param path
	 *            the path of the Excel file
	 * @return
	 * @throws IOException
	 */
	public static List<ScsScheduleConfig> readExcel(MultipartFile file) throws IOException {
		List<ScsScheduleConfig> scheduleList = new ArrayList<ScsScheduleConfig>();
		String fileName = file.getOriginalFilename();
		String postfixName = fileName.substring(fileName.lastIndexOf(SystemConstants.STRING_POINT) + 1);
		if (postfixName.equals("xlsx")) {
			scheduleList = readXlsx(file);
		} else if (postfixName.equalsIgnoreCase("xls")) {
			scheduleList = readXls(file);
		} else {
			return null;
		}
		return scheduleList;
	}

	/**
	 * Read the Excel 2010
	 * 
	 * @param path
	 *            the path of the excel file
	 * @return
	 * @throws IOException
	 */
	public static List<ScsScheduleConfig> readXlsx(MultipartFile fileName) throws IOException {
		InputStream is = fileName.getInputStream();
		@SuppressWarnings("resource")
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		ScsScheduleConfig scheduleConfig = null;
		List<ScsScheduleConfig> list = new ArrayList<>();
		// Read the Sheet
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
		// Read the Row
		for (int rowNum = 2; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (xssfRow != null) {
				scheduleConfig = new ScsScheduleConfig();
				XSSFCell cityCode = xssfRow.getCell(1);
				XSSFCell carCode = xssfRow.getCell(2);
				XSSFCell stopSort = xssfRow.getCell(3);
				XSSFCell startNet = xssfRow.getCell(4);
				XSSFCell endNet = xssfRow.getCell(5);
				XSSFCell arriveTime = xssfRow.getCell(6);// 到达始发网点时间
				XSSFCell beginTime = xssfRow.getCell(7);// 发车时间
				XSSFCell unloadTime = xssfRow.getCell(8);// 卸货时间
				XSSFCell workDate = xssfRow.getCell(9);// 工作日
				XSSFCell shift = xssfRow.getCell(10);// 班次
				if (getValue(cityCode).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setCityCode(getValue(cityCode).substring(0,							getValue(cityCode).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setCityCode(getValue(cityCode));
				}
				scheduleConfig.setCarNo(getValue(carCode));
				if (getValue(stopSort).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setStopSort(getValue(stopSort).substring(0,
							getValue(stopSort).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setStopSort(getValue(stopSort));
				}
				scheduleConfig.setStartNet(getValue(startNet));
				scheduleConfig.setEndNet(getValue(endNet));
				scheduleConfig.setArriveTime(StringUtils.isNotEmpty(getValue(arriveTime)) ? (DateUtils.strToDate(getValue(arriveTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				scheduleConfig.setStartTime(StringUtils.isNotEmpty(getValue(beginTime)) ? (DateUtils.strToDate(getValue(beginTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				scheduleConfig.setUnloadTime(StringUtils.isNotEmpty(getValue(unloadTime)) ? (DateUtils.strToDate(getValue(unloadTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				if (getValue(workDate).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setWorkDate(getValue(workDate).substring(0,
							getValue(workDate).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setWorkDate(getValue(workDate));
				}
				scheduleConfig.setShift(getValue(shift));
				scheduleConfig.setCreater(UserManager.getCurrentUserNo());
				scheduleConfig.setOperator(UserManager.getCurrentUserNo());
				list.add(scheduleConfig);
			}
		}
		return list;
	}
	/**
	 * Read the Excel 2003-2007
	 * 
	 * @param path
	 *            the path of the Excel
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("resource")
	public static List<ScsScheduleConfig> readXls(MultipartFile fileName) throws IOException {
		InputStream is = fileName.getInputStream();
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		ScsScheduleConfig scheduleConfig = null;
		List<ScsScheduleConfig> list = new ArrayList<ScsScheduleConfig>();
		/**
		 * 只取第一个sheet
		 */
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
		// Read the Row
		for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				scheduleConfig = new ScsScheduleConfig();
				HSSFCell cityCode = hssfRow.getCell(1);
				HSSFCell carCode = hssfRow.getCell(2);
				HSSFCell stopSort = hssfRow.getCell(3);
				HSSFCell startNet = hssfRow.getCell(4);
				HSSFCell endNet = hssfRow.getCell(5);
				HSSFCell arriveTime = hssfRow.getCell(6);
				HSSFCell beginTime = hssfRow.getCell(7);
				HSSFCell unloadTime = hssfRow.getCell(8);
				HSSFCell workDate = hssfRow.getCell(9);
				HSSFCell shift = hssfRow.getCell(10);
				if (getValue(cityCode).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setCityCode(getValue(cityCode).substring(0,							getValue(cityCode).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setCityCode(getValue(cityCode));
				}
				scheduleConfig.setCarNo(getValue(carCode));
				if (getValue(stopSort).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setStopSort(getValue(stopSort).substring(0,
							getValue(stopSort).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setStopSort(getValue(stopSort));
				}
				scheduleConfig.setStartNet(getValue(startNet));
				scheduleConfig.setEndNet(getValue(endNet));
				scheduleConfig.setArriveTime(StringUtils.isNotEmpty(getValue(arriveTime)) ? (DateUtils.strToDate(getValue(arriveTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				scheduleConfig.setStartTime(StringUtils.isNotEmpty(getValue(beginTime)) ? (DateUtils.strToDate(getValue(beginTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				scheduleConfig.setUnloadTime(StringUtils.isNotEmpty(getValue(unloadTime)) ? (DateUtils.strToDate(getValue(unloadTime), DateUtils.FORMAT_DATEONLYDAY)) : null);
				if (getValue(workDate).contains(SystemConstants.STRING_POINT)) {
					scheduleConfig.setWorkDate(getValue(workDate).substring(0,
							getValue(workDate).lastIndexOf(SystemConstants.STRING_POINT)));
				} else {
					scheduleConfig.setWorkDate(getValue(workDate));
				}
				list.add(scheduleConfig);
			}
		}
		return list;
	}
	@SuppressWarnings({ "static-access", "deprecation" })
	private static String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(xssfRow)) {
				SimpleDateFormat sdf = null;
				if (xssfRow.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm:ss");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = xssfRow.getDateCellValue();
				return sdf.format(date);
			} else {
				return String.valueOf(xssfRow.getNumericCellValue());
			}
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings({ "static-access", "deprecation" })
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				SimpleDateFormat sdf = null;
				if (hssfCell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm:ss");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = hssfCell.getDateCellValue();
				return sdf.format(date);
			} else {
				return String.valueOf(hssfCell.getNumericCellValue());
			}
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}

代码主要是通过readXls()方法对文件格式进行判断，对于.xlxs和.xls的文件采取不同的方式进行解析，然后写入到数据库中。
这里需要注意的是excel中的数据格式转换问题，后台最好通过debug看一下每一个字段取到的是什么格式，然后转成对应的格式再写入数据库。、
有同事反映说使用POI上传Excel使用JDK6会遇到问题，这个情况我没有亲测，我使用的是JDK8。
推荐一篇看到的关于POI的文章，可以参考，地址：http://blog.youkuaiyun.com/qq_1017097573/article/details/55683280