工作当中经常会遇到poi的操作以及批量数据导入库中，使用PLSQL或者Navicat这两种工具导入数据量大的时候也很耗费时间 以下是我个人项目当中总结的POI操作和批量数据入库
导入POI的坐标或者下载jar包导入项目
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
首先一个简单的前台页面用来上传文件
<form id="imageForm" name="imageForm" method="post" enctype="multipart/form-data" action="/njTest/inputExcelAll.jhtml">
<h3>选择一个文件:</h3>
<input id="file" type="file" name="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
<br/>
<input type="submit" value="上传" />
<span style="color: red">增量上传数据</span>
</form>
<c:if test="${res != null}">
成功导入：${res}
<br>
</c:if>
后端代码实现
/**
* Excel数据批量导入库中
* @param file
* @param request
* @return
*/
@RequestMapping("/njTest/inputExcelAll.jhtml")
public String inputExcelAll(@RequestParam("file") MultipartFile file, HttpServletRequest request,ModelMap modelMap) throws Exception {
String originalFilename = file.getOriginalFilename();// 原文件名字
logger.info("文件名：" + originalFilename);
List<List<Object>> dataList = ImportExcelUtils.importExcel(file);
try {
String url = "jdbc:oracle:thin:@ip:1521:表名";
String user = ""; //数据库连接用户名
String password = ""; //数据库链接密码
StringBuffer sql = new StringBuffer();
sql.append("insert into njtest(id,name,age,number) values (?,?,?,?)");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = (Connection) DriverManager.getConnection(url,user,password);
//关闭事务自动提交;首先批量操作的时候需要把事务自动提交关闭 要不然会默认更新一条提交一次
con.setAutoCommit(false);
Long startTime = System.currentTimeMillis();
PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql.toString());
for (int i = 1; i < dataList.size(); i++) {
//防止把excel当中设置过单元格格式为文本添加进来
if (StringUtils.isNotEmpty(dataList.get(i).get(0).toString())){
pst.setString(1, dataList.get(i).get(0).toString());
pst.setString(2, dataList.get(i).get(1).toString());
pst.setString(3, dataList.get(i).get(2).toString());
pst.setString(4, dataList.get(i).get(3).toString());
// 把一个SQL命令加入命令列表
pst.addBatch();
}
}
// 执行批量更新
pst.executeBatch();
// 语句执行完毕，提交本事务
con.commit();
Long endTime = System.currentTimeMillis();
//查看更新数据所需要的时间
System.out.println("用时：" + (endTime - startTime));
pst.close();
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
modelMap.addAttribute("res",dataList.size()-1);
return "forward:/software/importExcelinit.jhtml";
}
操作Excel的工具类
package com.tmg.utils;
import org.apache.poi.hssf.usermodel.*;
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;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
/**
* @Description excel 导入数据
*/
public class ImportExcelUtils {
public static List<List<Object>> importExcel(MultipartFile file) throws IOException {
String fileName = file.getOriginalFilename();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
return read2003Excel(file.getInputStream());
} else if ("xlsx".equals(extension)) {
return read2007Excel(file.getInputStream());
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
private static List<List<Object>> read2003Excel(InputStream is) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
HSSFWorkbook hwb = new HSSFWorkbook(is);
//读取Excel当中的第一张表
HSSFSheet sheet = hwb.getSheetAt(0);
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
cell.setCellValue("1");
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
// System.out.println(i+"行"+j+" 列 is String type");
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
}
linked.add(value);
}
list.add(linked);
}
return list;
}
/**
* 读取Office 2007 excel
*/
private static List<List<Object>> read2007Excel(InputStream is) throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(is);
//int numberOfSheets = xwb.getNumberOfSheets(); 获取excel有多少个页签从1开始获取
//Sheet sheetAt = xwb.getSheetAt(numberOfSheets);
//int rowNum = sheetAt.getLastRowNum();
//Row row1 = sheetAt.getRow(rowNum);
//short cellNum = row1.getLastCellNum();
//获取第几行第几列数据
//ell cell1 = sheetAt.getRow(rowNum).getCell(cellNum);
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
Object value = null;
XSSFRow row = null;
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new LinkedList<Object>();
short lastCellNum = row.getLastCellNum();
for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellValue("");
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-ddHH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
}
linked.add(value);
}
list.add(linked);
}
return list;
}
}
以上是自己对excel的导入功能和批量添加操作 欢迎一起讨论
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/77259.html
