Excel导出导入大数据量，65536条数限制及内存溢出问题解决
Excel大数据量导出导入
·
大数据量导出 方法
导出EXCEL前端代码
function export(){
setTimeout(function() {
//可加入加载中控件
//导出Excel文件
//URL地址
var url = "/api/hello";
//传递参数名称
var ids = "123,321"
function getiframeDocument($iframe) {
var iframeDoc = $iframe[0].contentWindow || $iframe[0].contentDocument;
if (iframeDoc.document) {
iframeDoc = iframeDoc.document;
}
return iframeDoc;
}
//标识是否导出成功
var isSuccess = true;
var $iframe = $("<iframe style='display: none' src='about:blank'></iframe>").appendTo("body");
var formDoc = getiframeDocument($iframe);
formDoc.write("<html><head></head><body><form id='form1' method='post' enctype='application/json' action='" + url + "'><input type='hidden' name='ids' value='" + ids + "' /></form></body></html>");
var $form = $(formDoc).find('form');
$form.submit();
//如果后端报错
$iframe.load(function() {
debugger;
isSuccess = false;
})
setTimeout(function() {
//导出成功
if (isSuccess) {
}
}, 2000);
}, 500);
}
导出EXCEL后端代码
// 后端control层
@Path("/")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public interface TestExportApi{
/***
* 导出方法
* @return
*/
@POST
@Path("exportData")
@Consumes(MediaType.APPLICATION_FORM_URLENCODED)
public void exportData(@Context HttpServletResponse response, @FormParam("ids") String ids);
}
// 后端接口实现类service层方法
@PersistenceContext(type = PersistenceContextType.EXTENDED)
EntityManager entityManagerExport;
@Transactional(rollbackFor = Exception.class)
@Override
public void exportData(HttpServletResponse response, String ids) {
//HSSFWorkbook wb = new HSSFWorkbook(); 之前使用的是这个，备注一下
// 创建Excel SXSSFWorkbook 支持大数据量导出
SXSSFWorkbook wb = new SXSSFWorkbook();
String[] idsArr=ids.split(",");
Sheet sheet=null;
Row row=null;
Cell cell=null;
//数据库表名称
String tableName="tablename,tablename02";
int cellIndex=0;
int rowIndex=0;
try{
//设置表头样式
// 创建字体样式
CellStyle headerStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
headerStyle.setDataFormat(format.getFormat("@"));
headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
Font headerFont = wb.createFont();
headerFont.setBold(true); // 字体加粗
headerStyle.setFont(headerFont); //为标题样式设置字体样式
//设置内容样式
CellStyle contentStyle = wb.createCellStyle();
contentStyle.setDataFormat(format.getFormat("@"));
//处理导出逻辑
for(String item:idsArr){
item=item.trim();
if(item.length()!=0) {
cellIndex = 0;
rowIndex = 0;
// 获取需要导出的表格
String[] splitTable = tableName.split(",");
for (String tableMc : splitTable) {
cellIndex = 0;
rowIndex = 0;
List<Map> resultList = new ArrayList<>();
String tableColInfo = "";
// 获取到指定数据库表的所有列，根据表处理条件
switch (tableMc){
case "tablename":
tableColInfo = "SELECT * FROM tablename where ID = ?1";
break;
case "tablename02" :
tableColInfo = "SELECT * FROM tablename where ID = ?1";
break;
default:
break;
}
Query nativeQuery = entityManagerExport.createNativeQuery(tableColInfo)
.setParameter(1, item);
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP.ALIAS_TO_ENTITY_MAP);
resultList =nativeQuery.getResultList();
if (resultList.size() != 0) {
if(wb.getSheet(tableMc) == null){
sheet = wb.createSheet(tableMc);
// region 样式
sheet.setVerticallyCenter(true);
sheet.setHorizontallyCenter(true);
//查询数据库数据并将其放入当前sheet页
row = sheet.createRow(0);
row.setHeightInPoints(23);
//设置表头
for (Object key : resultList.get(0).keySet()) {
sheet.setColumnWidth(cellIndex, 6000);
cell = row.createCell(cellIndex);
cell.setCellValue(key.toString());
cell.setCellStyle(headerStyle);
cellIndex++;
}
sheet.setColumnWidth(cellIndex, 18000);
rowIndex = 1;
}else{
sheet = wb.getSheet(tableMc);
rowIndex = sheet.getLastRowNum() + 1;
}
//设置内容
for (Map mapData : resultList) {
cellIndex = 0;
row = sheet.createRow(rowIndex);
row.setHeightInPoints(23);
for (Object key : mapData.keySet()) {
cell = row.createCell(cellIndex);
cell.setCellValue(mapData.get(key) != null ? mapData.get(key).toString() : "");
cell.setCellStyle(contentStyle);
cellIndex++;
}
rowIndex++;
}
}
}
}
}
// 输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
String dateStr= LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")).toString();
String fileName="生成文件名称"
/ // 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String((fileName+dateStr+".xlsx").getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
}catch(Exception e){
//异常回滚，注释开事务后有try catch自动回滚会失效，需要手动回滚事务
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
大数据量导入方法
导入EXCEL前端代码,跟之前的一样，就格式改成xlsx
function importData() {
debugger
var upload = $("<input>");
upload.attr("style", "display:none");
upload.attr("type", "file");
upload.attr("id", "upload")
$("body").append(upload);
//选中文件出触发
$('#upload').on('change', function(event) {
//可加入加载中控件
setTimeout(function() {
var formData = new FormData();
var name = $("#upload").val();
var fileName = $("#upload")[0].files[0];
var point = fileName.name.lastIndexOf(".");
var type = fileName.name.substr(point);
if (type != ".xlsx") {
return false;
}
formData.append("file", $("#upload")[0].files[0]);
formData.append("filename", name);
$.ajax({
url: '/api/hello',
dataType: 'json',
type: 'POST',
async: false,
data: formData,
processData: false, // 使数据不做处理
contentType: false, // 不要设置Content-Type请求头
success: function(data) {
if (data.Code !== 'error') {
//成功操作
}else{
//失败操作
}
//解决不能重复上传同一相同文件的问题
$("#upload").attr("type", "text");
$("#upload").attr("type", "file");
},
//后端报错
error: function(response) {
$("#upload").remove();
console.log(error);
}
});
}, 500);
});
$("#upload").click();
}
导入修改比较多，POI之前使用的用户模式，原理是把整个文件读取到内存中,对于大量数据很容易内存溢出，虽然好用但是不支持大数据量，今天给大家贴的是事件模式，虽然有点复杂,但是支持大数据量。
首先是读取Excel文件的工具类,支持多个sheet页的海量数据返回类型为Map<String,List<List>>
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @desc POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式
* 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
* 该模式是POI官方推荐的读取大数据的模式，
* 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
*
* 解决版本大数据量问题
**/
public class ExcelXlsxReader extends DefaultHandler {
//存储所有数据
private List<List<String>> dataList = new ArrayList<List<String>>();
//存储所有数据Map
private Map<String,List<List<String>>> dataListMap = new HashMap();
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的索引值
*/
private String lastIndex;
/**
* 文件的绝对路径
*/
private String filePath = "";
/**
* 工作表索引
*/
private int sheetIndex = 0;
/**
* sheet名
*/
private String sheetName = "";
/**
* 总行数
*/
private int totalRows=0;
/**
* 一行内cell集合
*/
private List<String> cellList = new ArrayList<String>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
/**
* 当前行
*/
private int curRow = 1;
/**
* 当前列
*/
private int curCol = 0;
/**
* T元素标识
*/
private boolean isTElement;
/**
* 判断上一单元格是否为文本空单元格
*/
private boolean startElementFlag = true;
private boolean endElementFlag = false;
private boolean charactersFlag = false;
/**
* 异常信息，如果为空则表示没有异常
*/
private String exceptionMessage;
/**
* 单元格数据类型，默认为字符串类型
*/
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
/**
* 单元格日期格式的索引
*/
private short formatIndex;
/**
* 日期格式字符串
*/
private String formatString;
//定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
private String prePreRef = "A", preRef = null, ref = null;
//定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
private String maxRef = null;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @param filename
* @throws Exception
*/
public int process(String filename) throws Exception {
filePath = filename;
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) { //遍历sheet
curRow = 1; //标记初始行为第一行
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
}
return totalRows; //返回该excel文件的总行数，不包括首列和空行
}
public Map<String,List<List<String>>> process(InputStream in) throws Exception {
//filePath = filename;
OPCPackage pkg = OPCPackage.open(in);
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
//XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
XMLReader parser = XMLReaderFactory.createXMLReader();
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) { //遍历sheet
curRow = 1; //标记初始行为第一行
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
//每次都新创建一个数组对象对应一个sheet页
dataList = new ArrayList<List<String>>();
sheet.close();
}
return dataListMap;
}
/**
* 第一个执行
*
* @param uri
* @param localName
* @param name
* @param attributes
* @throws SAXException
*/
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
//c => 单元格
if ("c".equals(name)) {
//前一个单元格的位置
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
//中部文本空单元格标识 ‘endElementFlag’ 判断前一次是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串跳过把空字符串的位置赋予preRef
if (endElementFlag){
preRef = ref;
}
}
//当前单元格的位置
ref = attributes.getValue("r");
//首部文本空单元格标识 ‘startElementFlag’ 判断前一次，即首部是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串, 且已知当前格，即第二格带“B”标志，则ref赋予preRef
if (!startElementFlag && !flag){ //上一个单元格为文本空单元格，执行下面的，使ref=preRef；flag为true表明该单元格之前有数据值，即该单元格不是首部空单元格，则跳过
// 这里只有上一个单元格为文本空单元格，且之前的几个单元格都没有值才会执行
preRef = ref;
}
//设定单元格类型
this.setNextDataType(attributes);
endElementFlag = false;
charactersFlag = false;
startElementFlag = false;
}
//当元素为t时
if ("t".equals(name)) {
isTElement = true;
} else {
isTElement = false;
}
//置空
lastIndex = "";
}
/**
* 第二个执行
* 得到单元格对应的索引值或是内容值
* 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
* 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
* @param ch
* @param start
* @param length
* @throws SAXException
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
startElementFlag = true;
charactersFlag = true;
lastIndex += new String(ch, start, length);
}
/**
* 第三个执行
*
* @param uri
* @param localName
* @param name
* @throws SAXException
*/
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
//t元素也包含字符串
if (isTElement) {
//将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
String value = lastIndex.trim();
cellList.add(curCol, value);
endElementFlag = true;
curCol++;
isTElement = false;
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
//v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
//补全单元格之间的空单元格
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i = 0; i < len; i++) {
cellList.add(curCol, "");
curCol++;
}
} else if (ref.equals(preRef) && !ref.startsWith("A")){ //ref等于preRef，且以B或者C...开头，表明首部为空格
int len = countNullCell(ref, "A");
for (int i = 0; i <= len; i++) {
cellList.add(curCol, "");
curCol++;
}
}
cellList.add(curCol, value);
curCol++;
endElementFlag = true;
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else {
//如果标签名称为row，这说明已到行尾，调用optRows()方法
if ("row".equals(name)) {
//默认第一行为表头，以该行单元格数目为最大数目
if (curRow == 1) {
maxRef = ref;
}
//补全一行尾部可能缺失的单元格
if (maxRef != null) {
int len = -1;
//前一单元格，true则不是文本空字符串，false则是文本空字符串
if (charactersFlag){
len = countNullCell(maxRef, ref);
}else {
len = countNullCell(maxRef, preRef);
}
for (int i = 0; i <= len; i++) {
cellList.add(curCol, "");
curCol++;
}
}
if (flag){ //该行不为空行则发送（第一行为列名）
//ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);
totalRows++;
//添加到数据集合中
dataList.add(cellList);
}
//将当前sheet页的数据存为map，这样支持多个sheet页的海量数据
dataListMap.put(sheetName,dataList);
//清空容器
cellList = new ArrayList<String>();
//cellList.clear();
curRow++;
curCol = 0;
preRef = null;
prePreRef = null;
ref = null;
flag=false;
}
}
}
/**
* 处理数据类型
*
* @param attributes
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t"); //单元格类型
String cellStyleStr = attributes.getValue("s"); //
String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
if ("b".equals(cellType)) { //处理布尔值
nextDataType = CellDataType.BOOL;
} else if ("e".equals(cellType)) { //处理错误
nextDataType = CellDataType.ERROR;
} else if ("inlineStr".equals(cellType)) {
nextDataType = CellDataType.INLINESTR;
} else if ("s".equals(cellType)) { //处理字符串
nextDataType = CellDataType.SSTINDEX;
} else if ("str".equals(cellType)) {
nextDataType = CellDataType.FORMULA;
}
if (cellStyleStr != null) { //处理日期
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
formatIndex = style.getDataFormat();
formatString = style.getDataFormatString();
if (formatString.contains("m/d/yyyy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d") ) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd hh:mm:ss";
}
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
}
/**
* 对解析出来的数据进行类型处理
* @param value 单元格的值，
* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
* @param thisStr 一个空字符串
* @return
*/
@SuppressWarnings("deprecation")
public String getDataValue(String value, String thisStr) {
switch (nextDataType) {
// 这几个的顺序不能随便交换，交换了很可能会导致数据错误
case BOOL: //布尔值
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR: //错误
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA: //公式
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = rtsi.toString();
rtsi = null;
break;
case SSTINDEX: //字符串
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
thisStr = rtss.toString();
//System.out.println(thisStr);
//有些字符串是文本格式的，但内容却是日期
rtss = null;
} catch (NumberFormatException ex) {
thisStr = value.toString();
}
break;
case NUMBER: //数字
if (formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
} else {
thisStr = value;
}
thisStr = thisStr.replace("_", "").trim();
break;
case DATE: //日期
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
// 对日期字符串作特殊处理，去掉T
thisStr = thisStr.replace("T", " ");
break;
default:
thisStr = " ";
break;
}
return thisStr;
}
public int countNullCell(String ref, String preRef) {
//excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
String xfd = ref.replaceAll("\\d+", "");
String xfd_1 = preRef.replaceAll("\\d+", "");
xfd = fillChar(xfd, 3, '@', true);
xfd_1 = fillChar(xfd_1, 3, '@', true);
char[] letter = xfd.toCharArray();
char[] letter_1 = xfd_1.toCharArray();
int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
return res - 1;
}
public String fillChar(String str, int len, char let, boolean isPre) {
int len_1 = str.length();
if (len_1 < len) {
if (isPre) {
for (int i = 0; i < (len - len_1); i++) {
str = let + str;
}
} else {
for (int i = 0; i < (len - len_1); i++) {
str = str + let;
}
}
}
return str;
}
/**
* @return the exceptionMessage
*/
public String getExceptionMessage() {
return exceptionMessage;
}
}
封装调用上面的方法
import java.io.InputStream;
import java.util.List;
import java.util.Map;
public class ExcelReaderUtil {
//读取xlsx格式
public static Map<String,List<List<String>>> readExcelXlsx(InputStream in) throws Exception {
ExcelXlsxReader excelXls=new ExcelXlsxReader();
Map<String,List<List<String>>> dataList =excelXls.process(in);
return dataList;
}
}
实际使用，导入Excel格式为xlsx
//导入后端接口control层
/**
* 导入Excel
*
* @return
*/
@POST
@Path("importData")
@Consumes(MediaType.MULTIPART_FORM_DATA)
String importData(@Multipart("file") Attachment attachment, @Multipart("filename") String filename, @Context HttpServletResponse response);
//导入后端接口control层
@PersistenceContext(type = PersistenceContextType.EXTENDED)
EntityManager entityManagerBasicDataOp;
@Override
@Transactional(rollbackFor = Exception.class)
public String importBigData(Attachment attachment, String filename, String errorImport, String netType, HttpServletResponse response) {
InputStream inputStream=null;
try {
//获取文件名
String name=attachment.getDataHandler().getName();
if(!StringUtils.isEmpty(name)){
inputStream=attachment.getObject(InputStream.class);
}
if(null == filename) {
return null;
}
if (!inputStream.markSupported()) {
inputStream = new PushbackInputStream(inputStream, 8);
}
//批量优化插入用
List<String> arrList = new ArrayList<>();
//保存新增的列名字符串
StringBuilder insertColStr=new StringBuilder();
//保存新增的数据字符串
StringBuilder insertValueStr=new StringBuilder();
//根据导入文件流和之前封装的导入工具类将导入数据转换成Map<String,List<List<String>>>
Map<String,List<List<String>>> lists = ExcelReaderUtil.readExcelXlsx(inputStream);
//搭建插入列名
List <String> strKeyList = new ArrayList<>();
//遍历转换后的Map
for (String sheetOf : lists.keySet()) {
//获取当前sheet页
List<List<String>> listsOfSheet = lists.get(sheetOf);
//清空列名
strKeyList.clear();
insertColStr.setLength(0);
//搭建插入值名
List <String> strValList = new ArrayList<>();
//遍历sheet页里的每一行数据
for(int i = 0;i < listsOfSheet.size();i++){
//清空值信息
strValList.clear();
insertValueStr.setLength(0);
//取每一行数值
List<String> strings = listsOfSheet.get(i);
int cellSum=strings.size();
//遍历每一列值
for(int cellIndex=0;cellIndex<cellSum;cellIndex++){
//获取列值信息
String cellValue=strings.get(cellIndex);
if(i == 0) {//当是第一行时获取列名并存入当前数组
//拼接插入列名字符串
insertColStr.append(cellValue+",");
strKeyList.add(cellValue);
}else{//处理值信息
strValList.add(cellValue);
//拼接插入值名字符串
insertValueStr.append((cellValue.length()==0?"null,":("'" + (cellValue.replaceAll("'","''"))+ "',")));
}
/*
由于List的有序性，我们已经把列名和值信息都存入了strKeyList 和 strValList,
想获取对应的列对应的值可直接获取类似于
//获取ID
String ids = strValList.get(strKeyList.indexOf("ID"));
使用（非线性安全）StringBuilder或者StringBuffer就可以拼接出
对应的插入语句。
最重要的就是转换Excel为 Map<String,List<List<String>>>*/
}
}
//拼接插入语句
if(i!=0){
arrList.add(" insert into " + sheetOf+ " (" + insertColStr + ") values (" + insertValueStr.deleteCharAt(insertValueStr.length() - 1) + "); ");
}
}
//最后是优化批量执行，避免超长执行sql
//保存所有sql语句条数
long sqlCount=0;
//指定每次运行2000条sql
int sqlRunCount=1000;
//保存所有insert语句
Map<String,String> sqlMap=new HashMap<String,String>();
for (String sql : arrList) {
sqlAppend.append(sql);
sqlCount++;
if(sqlCount==sqlRunCount){
sqlMap.put(String.valueOf(sqlMap.size()+1),sqlAppend.toString());
sqlAppend.setLength(0);
sqlCount=0;
}
}
if(sqlAppend.length()!=0){
sqlMap.put(String.valueOf(sqlMap.size()+1),sqlAppend.toString());
sqlAppend.setLength(0);
sqlCount=0;
}
for(String key:sqlMap.keySet()){
entityManagerBasicDataOp.createNativeQuery(sqlMap.get(key)).executeUpdate();
}
}catch (Exception ex) {
throw new RuntimeException("导入失败，原因是 : "+ex.getMessage());
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}finally {
try {
inputStream.close();
} catch (IOException e) {
throw new RuntimeException("导入失败，原因是 : "+ex.getMessage());
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
}
所需依赖
<!--读取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
至此结束，目前来说我导出导入测完是三十多万数据没问题，由于这里贴的代码都是临时写的，有什么问题及哪里写的不对请各位大佬及时提出，以免误导他人。另外工具类有借鉴其他博主的方法，找不到对应链接就不加说明了。
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)