如果想要将Excel中的数据导入到数据库中，肯定是以一定的格式要求才可以读取并导入，因此首先项目中需要 有一个Excel的导入模板，供用户填写数据。因此，该功能的实现需要两步，第一步需要提供下载模板的接口，第二步才是将上传的Excel文件内容导入到数据库中
第一步：提供下载模板的接口
在项目的webapp目录下存放模板文件，如下图位置
controller层提供接口：
@RequestMapping(value = "/downloadTemplate")
public void downloadTemplate(final HttpServletRequest request, final HttpServletResponse response) {
//获取模板在项目中的路径
String path = request.getServletContext().getRealPath("/") + "设备导入模板.xls";
if (log.isDebugEnabled()) {
log.debug("DeviceController");
log.debug("downloadTemplate");
log.debug("path=" + path);
}
FileInputStream in;
BufferedOutputStream out;
File file = new File(path);
try {
in = new FileInputStream(file);
out = new BufferedOutputStream(response.getOutputStream());
response.reset();
response.setHeader("Content-Disposition",
"attachment;Filename=" + URLEncoder.encode("设备导入模板.xls", "UTF-8"));
response.addHeader("Content-Length", "" + file.length());
response.setContentType("application/octet-stream;charset=UTF-8");
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = in.read(buff, 0, buff.length))) {
out.write(buff, 0, bytesRead);
}
// 关闭流
in.close();
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
log.error(e);
} catch (IOException e) {
e.printStackTrace();
log.error(e);
}
}第二步：将上传的Excel内的内容导入到数据库中
对于excel的导入导出网上有很多资源，可以参考以下两篇文章：
https://www.jianshu.com/p/dd1e4f28757b
https://blog.csdn.net/ethan_10/article/details/80335350
这边需下载jar包，并放在工程的WEB-INF——>lib目录下
下载地址：http://poi.apache.org/download.html
Excel模板形式：
数据库字段（我用的MySQL）：
实体类：
public void setAssType(Byte assType) {
this.assType = assType;
}
public Integer getTempId() {
return tempId;
}
public void setTempId(Integer tempId) {
this.tempId = tempId;
}
public Integer getUnitId() {
return unitId;
}
public void setUnitId(Integer unitId) {
this.unitId = unitId;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc == null ? null : desc.trim();
}
public String getDevName() {
return devName;
}
public void setDevName(String devName) {
this.devName = devName;
}
public Integer getManufacture() {
return manufacture;
}
public void setManufacture(Integer manufacture) {
this.manufacture = manufacture;
}
public Date getUpdateTime() {
return updateTime;
}
// public void setUpdateTime(Date updateTime) {
// this.updateTime = updateTime;
// }
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
public BigDecimal getTaxRate() {
return taxRate;
}
public void setTaxRate(BigDecimal taxRate) {
this.taxRate = taxRate;
}
public BigDecimal getTax() {
return tax;
}
public void setTax(BigDecimal tax) {
this.tax = tax;
}
public Float getPower() {
return power;
}
public void setPower(Float power) {
this.power = power;
}
public byte[] getImage() {
return image;
}
public void setImage(byte[] image) {
this.image = image;
}
}controller层接口：
@RequestMapping(value = "/importData")
@ResponseBody
public WebResultDto upload(@RequestParam("file") CommonsMultipartFile file) {
WebResultDto result = new WebResultDto();
try {
HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
int count = deviceService.insertFromWorkbook(wb);
result.success(count);
if (log.isDebugEnabled())
log.debug(count);
} catch (IOException e) {
e.printStackTrace();
log.error(e.getMessage());
result.setInfo(e.getMessage());
}
return result;
}
其次便是service层的实现逻辑：
@Override
public int insertFromWorkbook(HSSFWorkbook wb) {
// DataFormatter formatter = new DataFormatter();
int count = 0;
Sheet sheet1 = wb.getSheetAt(0);
int i = 0;
for (Row row : sheet1) {
// 跳过第一行
if (i++ == 0)
continue;
int j = 0;
DeviceInfo entity = new DeviceInfo();
for (Cell cell : row) {
switch (j++) {
// 设备编号
case 0:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setDevCode(cell.getStringCellValue());
}
break;
// 设备名称
case 1:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setDevName(cell.getStringCellValue());
}
break;
// 设备型号
case 2:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setDevModel(cell.getStringCellValue());
}
break;
// 设备类型
case 3:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
String devTypeName = cell.getStringCellValue();
if (ConstantUtils.getDeviceTypeMap().containsValue(devTypeName)) {
for (Byte key : ConstantUtils.getDeviceTypeMap().keySet()) {
if (ConstantUtils.getDeviceTypeName(key).equals(devTypeName)) {
entity.setDevType(key);
break;
}
}
}
}
break;
// 启用日期
case 4:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
try {
entity.setUseDate(format.parse(cell.getStringCellValue()));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
break;
// 设备状态
case 5:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
String devStatusName = cell.getStringCellValue();
if (ConstantUtils.getDeviceStatusMap().containsValue(devStatusName)) {
for (Byte key : ConstantUtils.getDeviceStatusMap().keySet()) {
if (ConstantUtils.getDeviceStatusName(key).equals(devStatusName)) {
entity.setDevStatus(key);
break;
}
}
}
}
break;
// 数量
case 6:
if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setQuantity(((Double) cell.getNumericCellValue()).intValue());
}
break;
// 单价
case 7:
if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setDevPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
}
break;
// 税率
case 8:
if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setTaxRate(BigDecimal.valueOf(cell.getNumericCellValue()));
}
break;
// 税
case 9:
if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setTax(BigDecimal.valueOf(cell.getNumericCellValue()));
}
break;
// 重要级别
case 10:
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
String impLevelName = cell.getStringCellValue();
if (ConstantUtils.getImportanceLevelMap().containsValue(impLevelName)) {
for (Byte key : ConstantUtils.getImportanceLevelMap().keySet()) {
if (ConstantUtils.getImportanceLevelMap().get(key).equals(impLevelName)) {
entity.setImpLevel(key);
break;
}
}
}
}
break;
case 11:
// 资产类型
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
String assTypeName = cell.getStringCellValue();
if (ConstantUtils.getAssetsTypeMap().containsValue(assTypeName)) {
for (Byte key : ConstantUtils.getAssetsTypeMap().keySet()) {
if (ConstantUtils.getAssetsTypeMap().get(key).equals(assTypeName)) {
entity.setAssType(key);
break;
}
}
}
}
break;
// 功率
case 12:
if (cell.getCellTypeEnum().equals(CellType.NUMERIC)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
entity.setPower(((Double) cell.getNumericCellValue()).floatValue());
}
break;
case 13:
// 单位
if (cell.getCellTypeEnum().equals(CellType.STRING)
|| cell.getCellTypeEnum().equals(CellType.FORMULA)) {
String unitName = cell.getStringCellValue();
Integer unitId = this.unitInfoMapper.getUnitIdByName(unitName);
if (unitId != null)
entity.setUnitId(unitId);
}
break;
}
}
try {
count += this.deviceInfoDao.insert(entity);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return count;
}这样就可以将Excel中的内容一一转化为数据库中的数据了