一、文件导入
一、为什么一定要在代码实现
说说为什么不能通过SQL直接导入到数据库，而是通过程序实现：
-
首先，这个导入功能开始提供页面导入，只是开始业务方保证的一次只有<3W的数据导入；
-
其次，业务方导入的内容需要做校验，比如门店号，商品号等是否系统存在，需要程序校验；
-
最后，业务方导入的都是编码，数据库中还要存入对应名称，方便后期查询，SQL导入也是无法实现的。
基于以上上三点，就无法直接通过SQL语句导入数据库。那就只能老老实实的想办法通过程序实现。
二、程序实现有以下技术难点
-
一次读取这么大的数据量，肯定会导致服务器内存溢出；
-
调用接口保存一次传输数据量太大，网络传输压力会很大；
-
最终通过SQL一次批量插入，对数据库压力也比较大，如果业务同时操作这个表数据，很容易造成死锁
三、解决思路
根据列举的技术难点我的解决思路是：
-
既然一次读取整个导入文件，那就先将文件流上传到服务器磁盘，然后分批从磁盘读取（支持多线程读取），这样就防止内存溢出；
-
调用插入数据库接口也是根据分批读取的内容进行调用；
-
分批插入数据到数据库。
四、具体实现代码
1. 流式上传文件到服务器磁盘
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
public class FileUploader {
private static final int BUFFER_SIZE = 4096;
public static void uploadFile(String targetUrl, String filePath) throws IOException {
File file = new File(filePath);
FileInputStream inputStream = new FileInputStream(file);
URL url = new URL(targetUrl);
HttpURLConnection connection = (HttpURLConnection) url.openConnection();
connection.setDoOutput(true);
connection.setRequestMethod("POST");
OutputStream outputStream = connection.getOutputStream();
byte[] buffer = new byte[BUFFER_SIZE];
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
outputStream.close();
inputStream.close();
int responseCode = connection.getResponseCode();
if (responseCode == HttpURLConnection.HTTP_OK) {
System.out.println("File uploaded successfully.");
} else {
System.out.println("File upload failed.");
}
connection.disconnect();
}
public static void main(String[] args) {
String targetUrl = "
String filePath = "path/to/file";
try {
uploadFile(targetUrl, filePath);
} catch (IOException e) {
e.printStackTrace();
}
}
}
流程图：
下面是使用mermaid语法的流程图，表示流式上传的流程
2. 多线程分批从磁盘读取
批量读取文件：
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.RandomAccessFile;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
/**
* 类功能描述：批量读取文件
*
* @author WangXueXing create at 19-3-14 下午6:47
* @version 1.0.0
*/
public class BatchReadFile {
private final Logger LOGGER = LoggerFactory.getLogger(BatchReadFile.class);
/**
* 字符集UTF-8
*/
public static final String CHARSET_UTF8 = "UTF-8";
/**
* 字符集GBK
*/
public static final String CHARSET_GBK = "GBK";
/**
* 字符集gb2312
*/
public static final String CHARSET_GB2312 = "gb2312";
/**
* 文件内容分割符－逗号
*/
public static final String SEPARATOR_COMMA = ",";
private int bufSize = 1024;
// 换行符
private byte key = "\n".getBytes()[0];
// 当前行数
private long lineNum = 0;
// 文件编码,默认为gb2312
private String encode = CHARSET_GB2312;
// 具体业务逻辑监听器
private ReaderFileListener readerListener;
public void setEncode(String encode) {
this.encode = encode;
}
public void setReaderListener(ReaderFileListener readerListener) {
this.readerListener = readerListener;
}
/**
* 获取准确开始位置
* @param file
* @param position
* @return
* @throws Exception
*/
public long getStartNum(File file, long position) throws Exception {
long startNum = position;
FileChannel fcin = new RandomAccessFile(file, "r").getChannel();
fcin.position(position);
try {
int cache = 1024;
ByteBuffer rBuffer = ByteBuffer.allocate(cache);
// 每次读取的内容
byte[] bs = new byte[cache];
// 缓存
byte[] tempBs = new byte[0];
while (fcin.read(rBuffer) != -1) {
int rSize = rBuffer.position();
rBuffer.rewind();
rBuffer.get(bs);
rBuffer.clear();
byte[] newStrByte = bs;
// 如果发现有上次未读完的缓存,则将它加到当前读取的内容前面
if (null != tempBs) {
int tL = tempBs.length;
newStrByte = new byte[rSize + tL];
System.arraycopy(tempBs, 0, newStrByte, 0, tL);
System.arraycopy(bs, 0, newStrByte, tL, rSize);
}
// 获取开始位置之后的第一个换行符
int endIndex = indexOf(newStrByte, 0);
if (endIndex != -1) {
return startNum + endIndex;
}
tempBs = substring(newStrByte, 0, newStrByte.length);
startNum += 1024;
}
} finally {
fcin.close();
}
return position;
}
/**
* 从设置的开始位置读取文件，一直到结束为止。如果 end设置为负数,刚读取到文件末尾
* @param fullPath
* @param start
* @param end
* @throws Exception
*/
public void readFileByLine(String fullPath, long start, long end) throws Exception {
File fin = new File(fullPath);
if (!fin.exists()) {
throw new FileNotFoundException("没有找到文件：" + fullPath);
}
FileChannel fileChannel = new RandomAccessFile(fin, "r").getChannel();
fileChannel.position(start);
try {
ByteBuffer rBuffer = ByteBuffer.allocate(bufSize);
// 每次读取的内容
byte[] bs = new byte[bufSize];
// 缓存
byte[] tempBs = new byte[0];
String line;
// 当前读取文件位置
long nowCur = start;
while (fileChannel.read(rBuffer) != -1) {
int rSize = rBuffer.position();
rBuffer.rewind();
rBuffer.get(bs);
rBuffer.clear();
byte[] newStrByte;
//去掉表头
if(nowCur == start){
int firstLineIndex = indexOf(bs, 0);
int newByteLenth = bs.length-firstLineIndex-1;
newStrByte = new byte[newByteLenth];
System.arraycopy(bs, firstLineIndex+1, newStrByte, 0, newByteLenth);
} else {
newStrByte = bs;
}
// 如果发现有上次未读完的缓存,则将它加到当前读取的内容前面
if (null != tempBs && tempBs.length != 0) {
int tL = tempBs.length;
newStrByte = new byte[rSize + tL];
System.arraycopy(tempBs, 0, newStrByte, 0, tL);
System.arraycopy(bs, 0, newStrByte, tL, rSize);
}
// 是否已经读到最后一位
boolean isEnd = false;
nowCur += bufSize;
// 如果当前读取的位数已经比设置的结束位置大的时候，将读取的内容截取到设置的结束位置
if (end > 0 && nowCur > end) {
// 缓存长度 - 当前已经读取位数 - 最后位数
int l = newStrByte.length - (int) (nowCur - end);
newStrByte = substring(newStrByte, 0, l);
isEnd = true;
}
int fromIndex = 0;
int endIndex = 0;
// 每次读一行内容，以 key（默认为\n） 作为结束符
while ((endIndex = indexOf(newStrByte, fromIndex)) != -1) {
byte[] bLine = substring(newStrByte, fromIndex, endIndex);
line = new String(bLine, 0, bLine.length, encode);
lineNum++;
// 输出一行内容，处理方式由调用方提供
readerListener.outLine(line.trim(), lineNum, false);
fromIndex = endIndex + 1;
}
// 将未读取完成的内容放到缓存中
tempBs = substring(newStrByte, fromIndex, newStrByte.length);
if (isEnd) {
break;
}
}
// 将剩下的最后内容作为一行，输出，并指明这是最后一行
String lineStr = new String(tempBs, 0, tempBs.length, encode);
readerListener.outLine(lineStr.trim(), lineNum, true);
} finally {
fileChannel.close();
fin.deleteOnExit();
}
}
/**
* 查找一个byte[]从指定位置之后的一个换行符位置
*
* @param src
* @param fromIndex
* @return
* @throws Exception
*/
private int indexOf(byte[] src, int fromIndex) throws Exception {
for (int i = fromIndex; i < src.length; i++) {
if (src[i] == key) {
return i;
}
}
return -1;
}
/**
* 从指定开始位置读取一个byte[]直到指定结束位置为止生成一个全新的byte[]
*
* @param src
* @param fromIndex
* @param endIndex
* @return
* @throws Exception
*/
private byte[] substring(byte[] src, int fromIndex, int endIndex) throws Exception {
int size = endIndex - fromIndex;
byte[] ret = new byte[size];
System.arraycopy(src, fromIndex, ret, 0, size);
return ret;
}
}
以上是关键代码：利用FileChannel与ByteBuffer从磁盘中分批读取数据
多线程调用批量读取：
/**
* 类功能描述: 线程读取文件
*
* @author WangXueXing create at 19-3-14 下午6:51
* @version 1.0.0
*/
public class ReadFileThread extends Thread {
private ReaderFileListener processDataListeners;
private String filePath;
private long start;
private long end;
private Thread preThread;
public ReadFileThread(ReaderFileListener processDataListeners,
long start,long end,
String file) {
this(processDataListeners, start, end, file, null);
}
public ReadFileThread(ReaderFileListener processDataListeners,
long start,long end,
String file,
Thread preThread) {
this.setName(this.getName()+"-ReadFileThread");
this.start = start;
this.end = end;
this.filePath = file;
this.processDataListeners = processDataListeners;
this.preThread = preThread;
}
@Override
public void run() {
BatchReadFile readFile = new BatchReadFile();
readFile.setReaderListener(processDataListeners);
readFile.setEncode(processDataListeners.getEncode());
try {
readFile.readFileByLine(filePath, start, end + 1);
if(this.preThread != null){
this.preThread.join();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
监听读取：
import java.util.ArrayList;
import java.util.List;
/**
* 类功能描述：读文件监听父类
*
* @author WangXueXing create at 19-3-14 下午6:52
* @version 1.0.0
*/
public abstract class ReaderFileListener<T> {
// 一次读取行数，默认为1000
private int readColNum = 1000;
/**
* 文件编码
*/
private String encode;
/**
* 分批读取行列表
*/
private List<String> rowList = new ArrayList<>();
/**
*其他参数
*/
private T otherParams;
/**
* 每读取到一行数据，添加到缓存中
* @param lineStr 读取到的数据
* @param lineNum 行号
* @param over 是否读取完成
* @throws Exception
*/
public void outLine(String lineStr, long lineNum, boolean over) throws Exception {
if(null != lineStr && !lineStr.trim().equals("")){
rowList.add(lineStr);
}
if (!over && (lineNum % readColNum == 0)) {
output(rowList);
rowList = new ArrayList<>();
} else if (over) {
output(rowList);
rowList = new ArrayList<>();
}
}
/**
* 批量输出
*
* @param stringList
* @throws Exception
*/
public abstract void output(List<String> stringList) throws Exception;
/**
* 设置一次读取行数
* @param readColNum
*/
protected void setReadColNum(int readColNum) {
this.readColNum = readColNum;
}
public String getEncode() {
return encode;
}
public void setEncode(String encode) {
this.encode = encode;
}
public T getOtherParams() {
return otherParams;
}
public void setOtherParams(T otherParams) {
this.otherParams = otherParams;
}
public List<String> getRowList() {
return rowList;
}
public void setRowList(List<String> rowList) {
this.rowList = rowList;
}
}
实现监听读取并分批调用插入数据接口：
import com.today.api.finance.ImportServiceClient;
import com.today.api.finance.request.ImportRequest;
import com.today.api.finance.response.ImportResponse;
import com.today.api.finance.service.ImportService;
import com.today.common.Constants;
import com.today.domain.StaffSimpInfo;
import com.today.util.EmailUtil;
import com.today.util.UserSessionHelper;
import com.today.util.readfile.ReadFile;
import com.today.util.readfile.ReadFileThread;
import com.today.util.readfile.ReaderFileListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.FutureTask;
import java.util.stream.Collectors;
/**
* 类功能描述：报表导入服务实现
*
* @author WangXueXing create at 19-3-19 下午1:43
* @version 1.0.0
*/
@Service
public class ImportReportServiceImpl extends ReaderFileListener<ImportRequest> {
private final Logger LOGGER = LoggerFactory.getLogger(ImportReportServiceImpl.class);
@Value("${READ_COL_NUM_ONCE}")
private String readColNum;
@Value("${REPORT_IMPORT_RECEIVER}")
private String reportImportReceiver;
/**
* 财务报表导入接口
*/
private ImportService service = new ImportServiceClient();
/**
* 读取文件内容
* @param file
*/
public void readTxt(File file, ImportRequest importRequest) throws Exception {
this.setOtherParams(importRequest);
ReadFile readFile = new ReadFile();
try(FileInputStream fis = new FileInputStream(file)){
int available = fis.available();
long maxThreadNum = ３L;
// 线程粗略开始位置
long i = available / maxThreadNum;
this.setRowList(new ArrayList<>());
StaffSimpInfo staffSimpInfo = ((StaffSimpInfo)UserSessionHelper.getCurrentUserInfo().getData());
String finalReportReceiver = getEmail(staffSimpInfo.getEmail(), reportImportReceiver);
this.setReadColNum(Integer.parseInt(readColNum));
this.setEncode(ReadFile.CHARSET_GB2312);
//这里单独使用一个线程是为了当maxThreadNum大于1的时候，统一管理这些线程
new Thread(()->{
Thread preThread = null;
FutureTask futureTask = null ;
try {
for (long j = 0; j < maxThreadNum; j++) {
//计算精确开始位置
long startNum = j == 0 ? 0 : readFile.getStartNum(file, i * j);
long endNum = j + 1 < maxThreadNum ? readFile.getStartNum(file, i * (j + 1)) : -2L;
//具体监听实现
preThread = new ReadFileThread(this, startNum, endNum, file.getPath(), preThread);
futureTask = new FutureTask(preThread, new Object());
futureTask.run();
}
if(futureTask.get() != null) {
EmailUtil.sendEmail(EmailUtil.REPORT_IMPORT_EMAIL_PREFIX, finalReportReceiver, "导入报表成功", "导入报表成功" ); //todo 等文案
}
} catch (Exception e){
futureTask.cancel(true);
try {
EmailUtil.sendEmail(EmailUtil.REPORT_IMPORT_EMAIL_PREFIX, finalReportReceiver, "导入报表失败", e.getMessage());
} catch (Exception e1){
//ignore
LOGGER.error("发送邮件失败", e1);
}
LOGGER.error("导入报表类型:"+importRequest.getReportType()+"失败", e);
} finally {
futureTask.cancel(true);
}
}).start();
}
}
private String getEmail(String infoEmail, String reportImportReceiver){
if(StringUtils.isEmpty(infoEmail)){
return reportImportReceiver;
}
return infoEmail;
}
/**
* 每批次调用导入接口
* @param stringList
* @throws Exception
*/
@Override
public void output(List<String> stringList) throws Exception {
ImportRequest importRequest = this.getOtherParams();
List<List<String>> dataList = stringList.stream()
.map(x->Arrays.asList(x.split(ReadFile.SEPARATOR_COMMA)).stream().map(String::trim).collect(Collectors.toList()))
.collect(Collectors.toList());
LOGGER.info("上传数据:{}", dataList);
importRequest.setDataList(dataList);
// LOGGER.info("request对象：{}",importRequest, "request增加请求字段：{}", importRequest.data);
ImportResponse importResponse = service.batchImport(importRequest);
LOGGER.info("===========SUCESS_CODE======="+importResponse.getCode());
//导入错误,输出错误信息
if(!Constants.SUCESS_CODE.equals(importResponse.getCode())){
LOGGER.error("导入报表类型:"+importRequest.getReportType()+"失败","返回码为：", importResponse.getCode() ,"返回信息：",importResponse.getMessage());
throw new RuntimeException("导入报表类型:"+importRequest.getReportType()+"失败"+"返回码为："+ importResponse.getCode() +"返回信息："+importResponse.getMessage());
}
// if(importResponse.data != null && importResponse.data.get().get("batchImportFlag")!=null) {
// LOGGER.info("eywa-service请求batchImportFlag不为空");
// }
importRequest.setData(importResponse.data);
}
}
注意：
第53行代码：
long maxThreadNum = ３L;
就是设置分批读取磁盘文件的线程数，我设置为３，大家不要设置太大，不然多个线程读取到内存，也会造成服务器内存溢出。以上所有批次的批量读取并调用插入接口都成功发送邮件通知给导入人，任何一个批次失败直接发送失败邮件。
数据库分批插入数据：
/**
* 批量插入非联机第三方导入账单
* @param dataList
*/
def insertNonOnlinePayment(dataList: List[NonOnlineSourceData]) : Unit = {
if (dataList.nonEmpty) {
CheckAccountDataSource.mysqlData.withConnection { conn =>
val sql =
s""" INSERT INTO t_pay_source_data
(store_code,
store_name,
source_date,
order_type,
trade_type,
third_party_payment_no,
business_type,
business_amount,
trade_time,
created_at,
updated_at)
VALUES (?,?,?,?,?,?,?,?,?,NOW(),NOW())"""
conn.setAutoCommit(false)
var stmt = conn.prepareStatement(sql)
var i = 0
dataList.foreach { x =>
stmt.setString(1, x.storeCode)
stmt.setString(2, x.storeName)
stmt.setString(3, x.sourceDate)
stmt.setInt(4, x.orderType)
stmt.setInt(5, x.tradeType)
stmt.setString(6, x.tradeNo)
stmt.setInt(7, x.businessType)
stmt.setBigDecimal(8, x.businessAmount.underlying())
stmt.setString(9, x.tradeTime.getOrElse(null))
stmt.addBatch()
if ((i % 5000 == 0) && (i != 0)) { //分批提交
stmt.executeBatch
conn.commit
conn.setAutoCommit(false)
stmt = conn.prepareStatement(sql)
}
i += 1
}
stmt.executeBatch()
conn.commit()
}
}
}
以上代码实现每5000 行提交一次批量插入，防止一次提较数据库的压力。
二、文件导出
使用POI或JXLS导出大数据量（百万级）Excel报表常常面临两个问题：
-
服务器内存溢出；
-
一次从数据库查询出这么大数据，查询缓慢。
当然也可以分页查询出数据，分别生成多个Excel打包下载，但这种生成还是很缓慢。
那么如何解决呢？
我们可以借助XML格式利用模板替换，分页查询出数据从磁盘写入XML，最终会以Excel多sheet形式生成。亲测2400万行数据，生成Excel文件4.5G，总耗时1.5分钟。
我利用StringTemplate模板解析技术对XML模板进行填充。当然也可以使用FreeMarker, Velocity等Java模板技术实现。
首先引入StringTemplate所需Jar包：
使用技术为 stringTemplate
pom.xml：
<dependency>
<groupId>antlr</groupId>
<artifactId>antlr</artifactId>
<version>2.7.7</version>
</dependency>
<dependency>
<groupId>org.antlr</groupId>
<artifactId>stringtemplate</artifactId>
<version>3.2.1</version>
</dependency>
首先准备导出Excel模板，然后打开－》另存为－》选择格式为XML，然后用文本打开XML,提取XML头模板(head.st可通用)，数据体模板(boday.st):
head.st可通用:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>1996-12-17T01:32:42Z</Created>
<LastSaved>2013-08-02T09:21:24Z</LastSaved>
<Version>11.9999</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>4530</WindowHeight>
<WindowWidth>8505</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>120</WindowTopY>
<AcceptLabelsInFormulas/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
boday.st:
$worksheet:{
<Worksheet ss:Name="$it.sheet$">
<Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
$it.rows:{
<Row>
<Cell><Data ss:Type="String">$it.name1$</Data></Cell>
<Cell><Data ss:Type="String">$it.name2$</Data></Cell>
<Cell><Data ss:Type="String">$it.name3$</Data></Cell>
</Row>
}$
</Table>
</Worksheet>
}$
生成大数据量Excel类：
ExcelGenerator:
package test.exportexcel;
import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;
import test.exportexcel.bean.Row;
import test.exportexcel.bean.Worksheet;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
/**
* 类功能描述：generator big data Excel
*
* @author WangXueXing create at 19-4-13 下午10:23
* @version 1.0.0
*/
public class ExcelGenerator {
public static void main(String[] args) throws FileNotFoundException{
ExcelGenerator template = new ExcelGenerator();
template.output2();
}
/**
* 生成数据量大的时候，该方法会出现内存溢出
* @throws FileNotFoundException
*/
public void output1() throws FileNotFoundException{
StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
StringTemplate st4 = stGroup.getInstanceOf("test/exportexcel/template/test");
List<Worksheet> worksheets = new ArrayList<>();
File file = new File("/home/barry/data/output.xls");
PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
for(int i=0;i<30;i++){
Worksheet worksheet = new Worksheet();
worksheet.setSheet("第"+(i+1)+"页");
List<Row> rows = new ArrayList<>();
for(int j=0;j<6000;j++){
Row row = new Row();
row.setName1("zhangzehao");
row.setName2(""+j);
row.setName3(i+" "+j);
rows.add(row);
}
worksheet.setRows(rows);
worksheets.add(worksheet);
}
st4.setAttribute("worksheets", worksheets);
writer.write(st4.toString());
writer.flush();
writer.close();
System.out.println("生成excel完成");
}
/**
* 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短
* 经测试，生成2400万数据，2分钟内，4.5G大的文件，打开大文件就看内存是否足够大了
* 数据量小的时候，推荐用JXLS的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用
* @throws FileNotFoundException
*/
public void output2() throws FileNotFoundException{
long startTimne = System.currentTimeMillis();
StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
//写入excel文件头部信息
StringTemplate head = stGroup.getInstanceOf("test/exportexcel/template/head");
File file = new File("/home/barry/data/output.xls");
PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
writer.print(head.toString());
writer.flush();
int sheets = 400;
//excel单表最大行数是65535
int maxRowNum = 60000;
//写入excel文件数据信息
for(int i=0;i<sheets;i++){
StringTemplate body = stGroup.getInstanceOf("test/exportexcel/template/body");
Worksheet worksheet = new Worksheet();
worksheet.setSheet(" "+(i+1)+" ");
worksheet.setColumnNum(3);
worksheet.setRowNum(maxRowNum);
List<Row> rows = new ArrayList<>();
for(int j=0;j<maxRowNum;j++){
Row row = new Row();
row.setName1(""+new Random().nextInt(100000));
row.setName2(""+j);
row.setName3(i+""+j);
rows.add(row);
}
worksheet.setRows(rows);
body.setAttribute("worksheet", worksheet);
writer.print(body.toString());
writer.flush();
rows.clear();
rows = null;
worksheet = null;
body = null;
Runtime.getRuntime().gc();
System.out.println("正在生成excel文件的 sheet"+(i+1));
}
//写入excel文件尾部
writer.print("</Workbook>");
writer.flush();
writer.close();
System.out.println("生成excel文件完成");
long endTime = System.currentTimeMillis();
System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
}
}
定义JavaBean:
WorkSheet.java:
package test.exportexcel.bean;
import java.util.List;
/**
* 类功能描述：Excel sheet Bean
*
* @author WangXueXing create at 19-4-13 下午10:21
* @version 1.0.0
*/
public class Worksheet {
private String sheet;
private int columnNum;
private int rowNum;
private List<Row> rows;
public String getSheet() {
return sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public List<Row> getRows() {
return rows;
}
public void setRows(List<Row> rows) {
this.rows = rows;
}
public int getColumnNum() {
return columnNum;
}
public void setColumnNum(int columnNum) {
this.columnNum = columnNum;
}
public int getRowNum() {
return rowNum;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
}
Row.java:
package test.exportexcel.bean;
/**
* 类功能描述：Excel row bean
*
* @author WangXueXing create at 19-4-13 下午10:22
* @version 1.0.0
*/
public class Row {
private String name1;
private String name2;
private String name3;
public String getName1() {
return name1;
}
public void setName1(String name1) {
this.name1 = name1;
}
public String getName2() {
return name2;
}
public void setName2(String name2) {
this.name2 = name2;
}
public String getName3() {
return name3;
}
public void setName3(String name3) {
this.name3 = name3;
}
}
三、超过25列Excel导出
模块二：导出 在Excel列较少时，按以上实际验证能很快实现生成。但如果列较多时用StringTemplate写入时会出现内存溢出。那么我的解决方案如下：
将数据列表分成多份，如果从数据库查询就是分页查询出多页数据进行分批在磁盘插入
1. 创建模板
举例Excel截图如下（有27列）：
模板分三部分（head，body及foot），分别如下：
operation_data_head.st
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<KSOProductBuildVer dt:dt="string">2052-11.1.0.9339</KSOProductBuildVer>
</CustomDocumentProperties>
<ExcelWorkbook
xmlns="urn:schemas-microsoft-com:office:excel">
<WindowWidth>20490</WindowWidth>
<WindowHeight>7860</WindowHeight>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="s16" ss:Name="警告文本">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FF0000"/>
</Style>
<Style ss:ID="s1" ss:Name="货币[0]">
<NumberFormat
ss:Format="_ "￥"* #,##0_ ;_ "￥"* \-#,##0_ ;_ "￥"* "-"_ ;_ @_ "/>
</Style>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s42" ss:Name="40% - 强调文字颜色 4">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#FFE699" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s26" ss:Name="检查单元格">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/>
<Border ss:Position="Left" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/>
<Border ss:Position="Right" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/>
<Border ss:Position="Top" ss:LineStyle="Double" ss:Weight="3" ss:Color="#3F3F3F"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/>
<Interior ss:Color="#A5A5A5" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s25" ss:Name="计算">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00" ss:Bold="1"/>
<Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s8" ss:Name="千位分隔">
<NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="s38" ss:Name="40% - 强调文字颜色 2">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#F8CBAD" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s2" ss:Name="20% - 强调文字颜色 3">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#EDEDED" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s7" ss:Name="差">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C0006"/>
<Interior ss:Color="#FFC7CE" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s31" ss:Name="好">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#006100"/>
<Interior ss:Color="#C6EFCE" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s24" ss:Name="输出">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#3F3F3F"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F3F" ss:Bold="1"/>
<Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s19" ss:Name="标题 1">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#5B9BD5"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="15" ss:Color="#44546A" ss:Bold="1"/>
</Style>
<Style ss:ID="s10" ss:Name="超链接">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#0000FF" ss:Underline="Single"/>
</Style>
<Style ss:ID="s6" ss:Name="40% - 强调文字颜色 3">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#DBDBDB" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s37" ss:Name="20% - 强调文字颜色 2">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s14" ss:Name="60% - 强调文字颜色 2">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#F4B084" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s3" ss:Name="输入">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#7F7F7F"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F76"/>
<Interior ss:Color="#FFCC99" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s5" ss:Name="千位分隔[0]">
<NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * "-"_ ;_ @_ "/>
</Style>
<Style ss:ID="s36" ss:Name="40% - 强调文字颜色 1">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#BDD7EE" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s35" ss:Name="20% - 强调文字颜色 1">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s21" ss:Name="60% - 强调文字颜色 1">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#9BC2E6" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s4" ss:Name="货币">
<NumberFormat
ss:Format="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="s40" ss:Name="强调文字颜色 4">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#FFC000" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s28" ss:Name="强调文字颜色 2">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#ED7D31" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s11" ss:Name="百分比">
<NumberFormat ss:Format="0%"/>
</Style>
<Style ss:ID="s9" ss:Name="60% - 强调文字颜色 3">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#C9C9C9" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s41" ss:Name="20% - 强调文字颜色 4">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#FFF2CC" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s34" ss:Name="强调文字颜色 1">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#5B9BD5" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s29" ss:Name="链接单元格">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#FF8001"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00"/>
</Style>
<Style ss:ID="s12" ss:Name="已访问的超链接">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#800080" ss:Underline="Single"/>
</Style>
<Style ss:ID="s18" ss:Name="解释性文本">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#7F7F7F" ss:Italic="1"/>
</Style>
<Style ss:ID="s13" ss:Name="注释">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#B2B2B2"/>
</Borders>
<Interior ss:Color="#FFFFCC" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s15" ss:Name="标题 4">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A" ss:Bold="1"/>
</Style>
<Style ss:ID="s17" ss:Name="标题">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Color="#44546A" ss:Bold="1"/>
</Style>
<Style ss:ID="s44" ss:Name="40% - 强调文字颜色 5">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#B4C6E7" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s20" ss:Name="标题 2">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#5B9BD5"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="13" ss:Color="#44546A" ss:Bold="1"/>
</Style>
<Style ss:ID="s43" ss:Name="强调文字颜色 5">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#4472C4" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s27" ss:Name="20% - 强调文字颜色 6">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#E2EFDA" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s22" ss:Name="标题 3">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#ACCCEA"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A" ss:Bold="1"/>
</Style>
<Style ss:ID="s23" ss:Name="60% - 强调文字颜色 4">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#FFD966" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s39" ss:Name="强调文字颜色 3">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#A5A5A5" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s32" ss:Name="适中">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C6500"/>
<Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s30" ss:Name="汇总">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3" ss:Color="#5B9BD5"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#5B9BD5"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" ss:Bold="1"/>
</Style>
<Style ss:ID="s45" ss:Name="60% - 强调文字颜色 5">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#8EA9DB" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s33" ss:Name="20% - 强调文字颜色 5">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#D9E1F2" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s47" ss:Name="40% - 强调文字颜色 6">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#C6E0B4" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s46" ss:Name="强调文字颜色 6">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#70AD47" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s48" ss:Name="60% - 强调文字颜色 6">
<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
<Interior ss:Color="#A9D08E" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s49"/>
<Style ss:ID="s50">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
</Style>
<Style ss:ID="s51">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s52">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
</Style>
<Style ss:ID="s53">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0_ "/>
</Style>
<Style ss:ID="s54">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s55">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
</Style>
<Style ss:ID="s56">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s57">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
</Style>
<Style ss:ID="s58">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0_ "/>
</Style>
<Style ss:ID="s59">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s60">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s61">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0_ "/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0_ "/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s66">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s67">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"/>
<Interior/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s68">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
</Style>
<Style ss:ID="s69">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
</Style>
<Style ss:ID="s70">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s71">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s72">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s73">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
</Style>
<Style ss:ID="s74">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
<NumberFormat ss:Format="0_ "/>
</Style>
</Styles>
<Worksheet ss:Name="$it.sheet$">
<Table ss:ExpandedColumnCount="28" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:StyleID="s52" ss:DefaultColumnWidth="54.5" ss:DefaultRowHeight="19">
<Column ss:Index="1" ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="176.25"/>
<Column ss:Index="2" ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="91.5"/>
<Column ss:StyleID="s52" ss:AutoFitWidth="0" ss:Width="85"/>
<Column ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="45.5" ss:Span="1"/>
<Column ss:Index="6" ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="85"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="55" ss:Span="2"/>
<Column ss:Index="10" ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="64.5"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5" ss:Span="1"/>
<Column ss:Index="14" ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="27"/>
<Column ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="62"/>
<Column ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="64.5"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="117.5"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="121.5" ss:Span="1"/>
<Column ss:Index="20" ss:StyleID="s54"/>
<Column ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="45.5"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5" ss:Span="1"/>
<Column ss:Index="24" ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5"/>
<Column ss:StyleID="s55" ss:AutoFitWidth="0" ss:Width="45.5"/>
<Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="64.5"/>
<Column ss:StyleID="s55" ss:AutoFitWidth="0" ss:Width="45.5"/>
<Row ss:StyleID="s50">
<Cell ss:StyleID="s71" ss:MergeAcross="5">
<Data ss:Type="String">基础信息</Data>
</Cell>
<Cell ss:StyleID="s72" ss:MergeAcross="3">
<Data ss:Type="String">订单信息（统计周期内）</Data>
</Cell>
<Cell ss:StyleID="s73" ss:MergeAcross="8">
<Data ss:Type="String">销售信息（统计周期内）</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">库存信息</Data>
</Cell>
<Cell ss:StyleID="s74" ss:MergeAcross="7">
<Data ss:Type="String">保理/融资信息（统计周期内）</Data>
</Cell>
</Row>
<Row ss:StyleID="s50">
<Cell ss:StyleID="s60">
<Data ss:Type="String">供应商名称</Data>
</Cell>
<Cell ss:StyleID="s60">
<Data ss:Type="String">供应商组号</Data>
</Cell>
<Cell ss:StyleID="s61">
<Data ss:Type="String">首次合同签署时间</Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String">卡号数量</Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String">卡号账期</Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String">异常状态卡号数量</Data>
</Cell>
<Cell ss:StyleID="s63">
<Data ss:Type="String">订货单金额</Data>
</Cell>
<Cell ss:StyleID="s63">
<Data ss:Type="String">送货单金额</Data>
</Cell>
<Cell ss:StyleID="s63">
<Data ss:Type="String">退货单金额</Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String">订货单数量</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">未税销售金额</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">综合毛利</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">净毛利</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">费用</Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="String">是否有进货记录</Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="String">是否有销售记录</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">90天销售额（T-1至T-90）</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">90天销售额（T-91至T-180）</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">90天综合毛利（T-1至T-90）</Data>
</Cell>
<Cell ss:StyleID="s63">
<Data ss:Type="String">期末库存</Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String">放款笔数</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">放款金额</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">放款利息</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">保理手续费</Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String">逾期罚息</Data>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="String">逾期次数</Data>
</Cell>
<Cell ss:StyleID="s70">
<Data ss:Type="String">月均放款额度</Data>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="String">坏账笔数</Data>
</Cell>
</Row>
operation_data_body.st
$worksheet:{
$it.rows:{
<Row>
<Cell ss:StyleID="s51">
<Data ss:Type="String">$it.supplierName$</Data>
</Cell>
<Cell ss:StyleID="s51">
<Data ss:Type="String">$it.groupNumber$</Data>
</Cell>
<Cell ss:StyleID="s52">
<Data ss:Type="String">$it.firstContYear$</Data>
</Cell>
<Cell ss:StyleID="s53">
<Data ss:Type="String">$it.cardNumber$</Data>
</Cell>
<Cell ss:StyleID="s53">
<Data ss:Type="String">$it.cardPeriod$</Data>
</Cell>
<Cell ss:StyleID="s53">
<Data ss:Type="String">$it.badCardNumber$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.orderAmount$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.receiveOrderAmount$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.backOrderAmount$</Data>
</Cell>
<Cell ss:StyleID="s53">
<Data ss:Type="String">$it.orderNumber$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.saleAmount$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.conPg$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.netPg$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.fee$</Data>
</Cell>
<Cell ss:StyleID="s51">
<Data ss:Type="String">$it.receiveRecord$</Data>
</Cell>
<Cell ss:StyleID="s51">
<Data ss:Type="String">$it.saleRecord$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.saleAmount90$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.saleAmount180$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.conPg90$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.endInventAm$</Data>
</Cell>
<Cell ss:StyleID="s53">
<Data ss:Type="String">$it.makeLoanNum$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.makeLoanAm$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.makeLoanInt$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.factFee$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.overdueInt$</Data>
</Cell>
<Cell ss:StyleID="s55">
<Data ss:Type="String">$it.overdueNum$</Data>
</Cell>
<Cell ss:StyleID="s54">
<Data ss:Type="String">$it.avgMakeLoanAm$</Data>
</Cell>
<Cell ss:StyleID="s55">
<Data ss:Type="String">$it.lossNum$</Data>
</Cell>
</Row>
}$
}$
operation_data_foot.st
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.511805555555556"/>
<Footer x:Margin="0.511805555555556"/>
</PageSetup>
<Selected/>
<TopRowVisible>0</TopRowVisible>
<LeftColumnVisible>0</LeftColumnVisible>
<PageBreakZoom>100</PageBreakZoom>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>9</ActiveRow>
<ActiveCol>1</ActiveCol>
<RangeSelection>R10C2</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>C1</Range>
<InputHide/>
<ErrorHide/>
<ErrorStyle>Stop</ErrorStyle>
</DataValidation>
</Worksheet>
</Workbook>
2. 引入必要Jar
我利用StringTemplate模板解析技术对XML模板进行填充。当然也可以使用FreeMarker, Velocity等Java模板技术实现。
首先引入StringTemplate所需Jar包：
使用技术为 stringTemplate
pom.xml：
<dependency>
<groupId>antlr</groupId>
<artifactId>antlr</artifactId>
<version>2.7.7</version>
</dependency>
<dependency>
<groupId>org.antlr</groupId>
<artifactId>stringtemplate</artifactId>
<version>3.2.1</version>
</dependency>
3. 创建JavaBean
创建对应绑定Java对象：
Worksheet.java
import java.util.List;
/**
* 类功能描述：Excel sheet Bean
*
* @author WangXueXing create at 19-4-13 下午10:21
* @version 1.0.0
*/
public class Worksheet<T> {
private String sheet;
private int columnNum;
private int rowNum;
private List<T> rows;
public String getSheet() {
return sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public List<T> getRows() {
return rows;
}
public void setRows(List<T> rows) {
this.rows = rows;
}
public int getColumnNum() {
return columnNum;
}
public void setColumnNum(int columnNum) {
this.columnNum = columnNum;
}
public int getRowNum() {
return rowNum;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
}
具体对应列对象：
OperationData.java
import lombok.Builder;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Value;
import lombok.experimental.Accessors;
/**
* @ClassName OperationData
* @Description TODO
* @Author wangxuexing
* @Date 2020/2/14 12:04
* @Version 1.0
*/
@Data
@EqualsAndHashCode()
@Accessors(chain = true)
public class OperationData {
/*基础信息*/
private String supplierName; // 供应商名称
private String groupNumber; // 供应商组号
private String firstContYear; // 首次合同签署时间
private String cardNumber; // 卡号数量
private String cardPeriod; // 卡号账期
private String badCardNumber; // 异常状态卡号数量
/*订单信息*/
private String orderAmount; // 订货单金额
private String receiveOrderAmount; // 送货单金额
private String backOrderAmount; // 退货单金额
private String orderNumber; // 订货单数量
/*销售信息*/
private String saleAmount; // 未税销售金额
private String conPg; // 综合毛利
private String netPg; // 净毛利
private String fee; // 费用
private String receiveRecord;
private String saleRecord;
private String saleAmount90; // 90天销售额（T-1至T-90）
private String saleAmount180; // 90天销售额（T-91至T-180）
private String conPg90; // 90天综合毛利（T-1至T-90）
/*库存信息*/
private String endInventAm; // 期末库存
/*保理/融资信息*/
private String makeLoanNum; // 放款笔数
private String makeLoanAm; // 放款金额
private String makeLoanInt; // 放款利息
private String factFee; // 保理手续费
private String overdueInt; // 逾期罚息
private String overdueNum; // 逾期次数
private String avgMakeLoanAm;
private String lossNum; // 坏账笔数
}
4. 生成Excel
首先生成100万条数据：
List<OperationData> dataList = Lists.newArrayList();
for(int i=0; i<1000000; i++){
int val = (int)(Math.random()*10+1);
OperationData operationData = new OperationData();
operationData.setAvgMakeLoanAm("4343"+val).setBackOrderAmount("4343"+val).setBadCardNumber("4343"+val)
.setCardPeriod("4343"+val).setConPg("4343"+val)
.setConPg90("4343"+val).setEndInventAm("4343"+val).setEndInventAm("4343"+val)
.setFactFee("4343"+val).setFee("4343"+val).setFirstContYear("4343"+val).setLossNum("4343"+val)
.setGroupNumber("4343"+val).setCardNumber("4343"+val)
.setMakeLoanInt("4343"+val).setMakeLoanNum("4343"+val)
.setNetPg("4343"+val).setOrderAmount("4343"+val).setOverdueInt("4343"+val)
.setMakeLoanAm("4343"+val).setOverdueNum("4343"+val)
.setOverdueNum("4343"+val).setSaleAmount("4343"+val).setReceiveOrderAmount("4343"+val)
.setSaleAmount90("4343"+val).setSaleAmount180("4343"+val)
.setSaleRecord("4343"+val).setSupplierName("4343"+val)
.setOrderNumber("4343"+val).setReceiveRecord("4343"+val);
dataList.add(operationData);
}
我们假定每次插入Excel为2万行，我们先拆分这100万行数据：
拆分方法如下：
/**
* 将一个list均分成n个list,主要通过偏移量来实现的
* @param source
* @return
*/
public static <T> List<List<T>> averageAssignList(List<T> source, int n) {
List<List<T>> result = new ArrayList<List<T>>();
int remaider = source.size() % n; //(先计算出余数)
int number = source.size() / n; //然后是商
int offset = 0;//偏移量
for (int i = 0; i < n; i++) {
List<T> value = null;
if (remaider > 0) {
value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
remaider--;
offset++;
} else {
value = source.subList(i * number + offset, (i + 1) * number + offset);
}
result.add(value);
}
return result;
}
最后，我们来看下我们生成Excel方法如下：
/**
* 写入单个Sheet的Excel
* @param templatePrefix 模板前缀，默认两个模板后缀分别为head及body
* @param outFile 生成Excel文件
* @param sheetName 单个sheet名称
* @param dataList 填充数据列表
* @param <T> 填充对象泛型
* @throws FileNotFoundException
* @throws ClassNotFoundException
*/
public static <T> void writeExcelOneSheetByList(String templatePrefix, File outFile, String sheetName, Class clazz, List<List<T>> dataList){
long startTimne = System.currentTimeMillis();
StringTemplateGroup stGroup = new StringTemplateGroup(String.valueOf(startTimne));
try(PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(outFile)))) {
//写入excel文件头部信息
StringTemplate head = stGroup.getInstanceOf("template"+File.separator+templatePrefix+"head");
writer.print(head.toString());
writer.flush();
//excel单表最大行数是65535
Field[] fields = clazz.getDeclaredFields();
dataList.forEach(x->{
long startTimne1 = System.currentTimeMillis();
//写入excel文件数据信息
StringTemplate body = stGroup.getInstanceOf("template"+File.separator+templatePrefix+"body");
Worksheet worksheet = new Worksheet();
worksheet.setSheet(sheetName);
worksheet.setColumnNum(fields.length);
worksheet.setRowNum(ONE_SHEET_LIMIT_ROW);
worksheet.setRows(x);
body.setAttribute("worksheet", worksheet);
writer.print(body.toString());
writer.flush();
long endTime1 = System.currentTimeMillis();
System.out.println("用时="+((endTime1-startTimne1)/1000)+"秒");
});
//写入excel文件头部信息
StringTemplate foot = stGroup.getInstanceOf("template"+File.separator+templatePrefix+"foot");
writer.print(foot.toString());
writer.flush();
} catch (Exception e) {
log.error("写入Excel异常", e);
}
long endTime = System.currentTimeMillis();
System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
}
这样就不会导致生成Excel时内存溢出了。
整体代码如果需要，请留言并附联系方式，我会抽时间发送。
四、POI 百万规模数据的导入和导出
1、百万数据导入 ?
1.1 需求分析 ?
使用POI基于事件模式解析案例提供的Excel文件
1.2 思路分析 ?
用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。
例如读取我们刚刚导出的百万数据：
package com.itheima.test;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//测试百万数据的导入
public class POIDemo5 {
public static void main(String[] args) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\syl\\Desktop\\百万用户数据的导出.xlsx");
XSSFSheet sheetAt = workbook.getSheetAt(0);
String stringCellValue = sheetAt.getRow(0).getCell(0).getStringCellValue();
System.out.println(stringCellValue);
}
}
会直接报内存溢出的错误：
事件模式： 它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。
1.3 代码实现 ?
1.3.1 步骤分析 ?
（1）设置POI的事件模式
根据Excel获取文件流
根据文件流创建OPCPackage 用来组合读取到的xml 组合出来的数据占用的空间更小
创建XSSFReader对象
（2）Sax解析
自定义Sheet处理器
创建Sax的XmlReader对象
设置Sheet的事件处理器
逐行读取
1.3.2 自定义处理器 ?
package com.itheima.test;
import com.itheima.pojo.User;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
// 编号 用户名 手机号 入职日期 现住址
private User user=null;
@Override
public void startRow(int rowIndex) { //每一行的开始 rowIndex代表的是每一个sheet的行索引
if(rowIndex==0){
user = null;
}else{
user = new User();
}
}
@Override //处理每一行的所有单元格
public void cell(String cellName, String cellValue, XSSFComment comment) {
if(user!=null){
String letter = cellName.substring(0, 1); //每个单元名称的首字母 A B C
switch (letter){
case "A":{
user.setId(Long.parseLong(cellValue));
break;
}
case "B":{
user.setUserName(cellValue);
break;
}
}
}
}
@Override
public void endRow(int rowIndex) { //每一行的结束
if(rowIndex!=0){
System.out.println(user);
}
}
}
1.3.3 自定义解析 ?
package com.itheima.test;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
/**
* 自定义Excel解析器
*/
public class ExcelParser {
public void parse (String path) throws Exception {
//1.根据Excel获取OPCPackage对象
OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
try {
//2.创建XSSFReader对象
XSSFReader reader = new XSSFReader(pkg);
//3.获取SharedStringsTable对象
SharedStringsTable sst = reader.getSharedStringsTable();
//4.获取StylesTable对象
StylesTable styles = reader.getStylesTable();
XMLReader parser = XMLReaderFactory.createXMLReader();
// 处理公共属性：Sheet名，Sheet合并单元格
parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandler(), false));
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
while (sheets.hasNext()) {
InputStream sheetstream = sheets.next();
InputSource sheetSource = new InputSource(sheetstream);
try {
parser.parse(sheetSource);
} finally {
sheetstream.close();
}
}
} finally {
pkg.close();
}
}
}
1.3.4 测试 ?
用户模式下读取测试Excel文件直接内存溢出，测试Excel文件映射到内存中还是占用了不少内存；事件模式下可以流畅的运行。
使用事件模型解析
public class POIDemo5 {
public static void main(String[] args) throws Exception{
new ExcelParser().parse("C:\\Users\\syl\\Desktop\\百万用户数据的导出.xlsx");
}
}
2、百万数据导出 ?
2.1、概述 ?
我们都知道Excel可以分为早期的Excel2003版本（使用POI的HSSF对象操作）和Excel2007版本（使用POI的XSSF操作），两者对百万数据的支持如下：
1.Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。
2.Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。
2.2、解决方案分析 ?
对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：
java代码解析xml
dom4j:一次性加载xml文件再解析
SAX:逐行加载，逐行解析
用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）
事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel
2.3、原理分析 ?
在实例化SXSSFWorkBook这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。
2.4、百万数据的导出 ?
2.4.1、模拟数据 ⭐️
第一步、创建表
CREATE TABLE `tb_user2` (
`id` bigint(20) NOT NULL COMMENT '用户ID',
`user_name` varchar(100) DEFAULT NULL COMMENT '姓名',
`phone` varchar(15) DEFAULT NULL COMMENT '手机号',
`province` varchar(50) DEFAULT NULL COMMENT '省份',
`city` varchar(50) DEFAULT NULL COMMENT '城市',
`salary` int(10) DEFAULT NULL,
`hire_date` datetime DEFAULT NULL COMMENT '入职日期',
`dept_id` bigint(20) DEFAULT NULL COMMENT '部门编号',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`photo` varchar(200) DEFAULT NULL COMMENT '照片路径',
`address` varchar(300) DEFAULT NULL COMMENT '现在住址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二步、创建存储过程，来插入数据
DELIMITER $$ -- 重新定义“;”分号
DROP PROCEDURE IF EXISTS test_insert $$ -- 如果有test_insert这个存储过程就删除
CREATE PROCEDURE test_insert() -- 创建存储过程
BEGIN
DECLARE n int DEFAULT 1; -- 定义变量n=1
SET AUTOCOMMIT=0; -- 取消自动提交
while n <= 5000000 do
INSERT INTO `tb_user2` VALUES ( n, CONCAT('测试', n), '13800000001', '北京市', '北京市', '11000', '2001-03-01 21:18:29', '1', '1981-03-02 00:00:00', '\\static\\user_photos\\1.jpg', '北京市西城区宣武大街1号院');
SET n=n+1;
END while;
COMMIT;
END $$
第三步、在新建查询处，点击开始执行
CALL test_insert();
插入500W数据大概需要300至600秒左右（当然，这个要根据电脑的配置而定）
查看插入结果
2.4.2、思路分析 ☘️
导出时使用的是SXSSFWorkBook这个类，一个工作表sheet最多只能放1048576行数据， 当我们的业务数据已超过100万了，一个sheet就不够用了，必须拆分到多个工作表。
导出百万数据时有两个弊端：
1、不能使用模板
2、不能使用太多的样式
也就是说导出的数据太多时必须要放弃一些。
2.4.3、代码实现 ?
UserController代码
@GetMapping(value = "/downLoadMillion",name = "导出用户百万数据的导出")
public void downLoadMillion(Long id,HttpServletRequest request,HttpServletResponse response) throws Exception{
userService.downLoadMillion(request,response);
}
UserService代码
public void downLoadMillion(HttpServletRequest request, HttpServletResponse response) throws Exception {
// 创建一个空的工作薄
Workbook workbook = new SXSSFWorkbook();
int page = 1;
int pageSize=200000;
int rowIndex = 1; //每一个工作页的行数
int num = 0; //总数据量
Row row = null;
Cell cell = null;
Sheet sheet = null;
while (true){ //不停地查询
List<User> userList = this.findPage(page,pageSize);
if(CollectionUtils.isEmpty(userList)){ //如果查询不到就不再查询了
break;
}
if(num%1000000==0){ //每100W个就重新创建新的sheet和标题
rowIndex = 1;
// 在工作薄中创建一个工作表
sheet = workbook.createSheet("第"+((num/1000000)+1)+"个工作表");
// 设置列宽
sheet.setColumnWidth(0,8*256);
sheet.setColumnWidth(1,12*256);
sheet.setColumnWidth(2,15*256);
sheet.setColumnWidth(3,15*256);
sheet.setColumnWidth(4,30*256);
// 处理标题
String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};
// 创建标题行
Row titleRow = sheet.createRow(0);
for (int i = 0; i < titles.length; i++) {
cell = titleRow.createCell(i);
cell.setCellValue(titles[i]);
}
}
// 处理内容
for (User user : userList) {
row = sheet.createRow(rowIndex);
cell = row.createCell(0);
cell.setCellValue(user.getId());
cell = row.createCell(1);
cell.setCellValue(user.getUserName());
cell = row.createCell(2);
cell.setCellValue(user.getPhone());
cell = row.createCell(3);
cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
cell = row.createCell(4);
cell.setCellValue(user.getAddress());
rowIndex++;
num++;
}
page++;// 继续查询下一页
}
// 导出的文件名称
String filename="百万数据.xlsx";
// 设置文件的打开方式和mime类型
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader( "Content-Disposition", "attachment;filename=" + new String(filename.getBytes(),"ISO8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
workbook.write(outputStream);
}
2.4.4、测试结果 ?
导出的这个文档大概需要3-5分钟的时间，有105 MB，内容如下
五、POI结合线程池批量处理导入减少导入时间
核心思想
1、利用
ExecutorService service = Executors.newFixedThreadPool(10);
创建一个长度为10的线城池，用以导入开线程导入
2、创建两个计数器
CountDownLatch rowLatch = new CountDownLatch(1);
CountDownLatch exceLatch = new CountDownLatch(10);
rowLatch用于控制每个线程的执行和挂起；
exceLatch用于控制主线程的执行和挂起。用以导入统计时间。
3、jdbc批量提交
if(list.size()>=200) {
prepareStatement.executeBatch();
list.clear();
connection.commit();
prepareStatement.clearBatch();
}
利用批量提交技术，单线程上，每两百条提交一次。
测试结果
用笔记本测试;
最高频率2.6Ghz，内存8G，硬盘5400转，数据库oracle数据库，装载第二分区，程序在第三个分区。
6万导入，在不加线程时，用了40多分钟；
如果不加jdbc的批量提交，则时间为9秒左右；
如果加了jdbc的批量提交，则时间缩短为2秒左右。
源码实例
package com.wayne;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POI2Excel2 {
public static void main(String[] args) throws Exception {
// SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd hh:mm:ss");
Class.forName("oracle.jdbc.driver.OracleDriver");
ExecutorService service = Executors.newFixedThreadPool(10);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(
new FileInputStream(new File("E:\\workspace\\Java\\Staff\\MyThreadPool\\Output\\测试1.xlsx")));
Sheet sheet = xssfWorkbook.getSheetAt(0);
long start = System.currentTimeMillis();
CountDownLatch rowLatch = new CountDownLatch(1);
CountDownLatch exceLatch = new CountDownLatch(10);
for (int i = 0; i < 10; i++) {
final int page = i;
service.submit(new Runnable() {
@Override
public void run() {
Connection connection =null;
try {
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "resdb", "tnms320_resdb");
rowLatch.await();
connection.setAutoCommit(false);
//synchronized(connection) {}
String sql = "insert into person(name,age,birthday) values(?,?,?)";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
List<Integer> list = new ArrayList<Integer>(200);
for(int j = 0;j<sheet.getLastRowNum()/10;j++) {
list.add(j);
Row r = sheet.getRow(j+page*sheet.getLastRowNum()/10);
String name = r.getCell(0).getStringCellValue();
int age = (int)(r.getCell(1).getNumericCellValue());
String birthday = r.getCell(2).getStringCellValue();
prepareStatement.setString(1, name);
prepareStatement.setInt(2, age);
prepareStatement.setString(3,birthday);
prepareStatement.addBatch();
if(list.size()>=200) {
prepareStatement.executeBatch();
list.clear();
connection.commit();
prepareStatement.clearBatch();
}
}
if(!list.isEmpty()) {
prepareStatement.executeBatch();
list.clear();
connection.commit();
prepareStatement.clearBatch();
}
prepareStatement.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
exceLatch.countDown();
try {
connection.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
});
}
rowLatch.countDown();
exceLatch.await();
long end = System.currentTimeMillis();
System.out.println("导入时间为" + (end - start));
service.shutdown();
}
}