Очень часто требуется экспортировать отчет, отображаемый на веб-странице, в файл Excel. Однако, когда объем данных велик, проблема, заключающаяся в том, что Excel поддерживает до 65535 строк данных, становится заметной. Решение для экспорта больших объемов данных в Excel приведено ниже.
Прежде всего, для проблемы, когда данные превышают 65535 строк, естественно подумать о разделении всех данных на блоки и использовании функции многостраничных страниц Excel для записи данных за пределами 65535 строк на следующую страницу листа, то есть через Многостраничный метод преодолевает ограничение до 65535 строк данных.
Конкретный метод состоит в том, чтобы сделать отдельную ссылку, использовать JSP для экспорта и использовать программу для оценки количества строк отчета в JSP и записать его в SHEET после более чем 65535 строк. Итак, проблема решена.
Кроме того, при создании отчета и экспорте такого большого объема данных требуется много памяти, особенно в случае использования TOMCAT, JVM может поддерживать только до 2 ГБ памяти, и произойдет переполнение памяти. Накладные расходы памяти в настоящее время в основном состоят из двух частей: одна — это накладные расходы при создании отчета, а другая — накладные расходы при написании EXCEL после создания отчета. Поскольку механизм GC JVM не может принудительно перезапускать, мы даем обходное решение для этой ситуации.
Во-первых,Задайте параметры start row и end row для отчета, и пошагово рассчитайте отчет в процессе генерации отчета из API (основная производительность тратится на отчет генерации запросов)Например, отчет с 200 000 строк данных может быть сгенерирован 4–5 раз через начальную и конечную строки. Таким образом, использование памяти во время создания отчета уменьшается.В более позднем процессе создания отчета, если обнаружится, что памяти недостаточно, механизм GC JVM может быть автоматически активирован для повторного использования кеша предыдущего отчета.
Процесс экспорта EXCEL осуществляется сразу после формирования каждого отчета, а несколько страниц SHEET заменяются на несколько EXCEL, то есть при пошаговом формировании отчетов EXCEL генерируется пошагово, а потребление памяти при формировании EXCEL через Пакет POI также уменьшен.. Путем многократного создания также можно эффективно освободить память, занятую предыдущим поколением EXCEL, когда памяти, необходимой для последующего поколения EXCEL, недостаточно.
Затем используйте файловую операцию для создания уникального временного каталога на стороне сервера в соответствии с SESSIONID и временем входа в систему для каждого запроса на экспорт клиента, который используется для размещения сгенерированных нескольких файлов EXCEL., затем вызовите системную консоль,Упакуйте несколько файлов EXCEL в формат RAR или JAR и, наконец, отправьте пользователю пакет RAR или JAR. После ответа на запрос клиента снова вызовите консоль, чтобы удалить временный каталог..
Используя этот метод, сначалаКусочные операции и генерация, что эффективно снижает затраты памяти на отчет от создания результатов до создания EXCEL. Во-вторых, при использовании сжатого пакета размер сгенерированного файла для пользователя значительно уменьшается, что снижает нагрузку на сервер по загрузке файлов при одновременном доступе нескольких пользователей и эффективно снижает трафик на стороне сервера, когда несколько пользователей экспортируют и скачать, чтобы еще больше снизить нагрузку на сервер.
- Создайте системный глобальный пул потоков
final int numOfCpuCores = Runtime.getRuntime().availableProcessors();
final double blockingCoefficient = 0.9;// 阻尼系数
final int maximumPoolSize = (int)(numOfCpuCores / (1 - blockingCoefficient));
ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores,
maximumPoolSize,
0L,
TimeUnit.MILLISECONDS,
new LinkedBlockingQueue <Runnable>(),
Executors.privilegedThreadFactory(),
new ThreadPoolExecutor.DiscardOldestPolicy());
- Используйте многопоточный сегментированный запрос для создания отчетов, синхронного создания Excel и, наконец, сжатия в Zip-файлы.
// 1.这里每个Excel放6万条数据（分6个sheet页，每个1万条），当数据量超过6万条时，数据采用分段查询
// 传递（起始行，结束行）参数，分段查询，即分步生成报表的同时分步生成EXCEL
int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000;
int count = bo.getTotalRecord();
final String fileNameWithTimestamp = fileName + "_" + DateUtil.getNowDateminStr();
if (count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) {
int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM +
(count % SINGLE_EXCEPORT_EXCEL_MAX_NUM != 0 ? 1 : 0);
final CountDownLatch latch = new CountDownLatch(excelCount);
final Long userId = user.getUserId();
for(int i = 1; i <= excelCount; i++){
bo.setPageNo(i);
bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM);
final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo);
final int index = i;
// 取一线程执行本次查询
threadPool.execute(new Runnable(){
@Override
public void run() {
Page page = service.getParkRecord(itemBo);
List<ParkRecordQryBO> records = page.getResults();
try {
// 2.生成单个excel
ExportExcelUtil.createOneExcel(fileNameWithTimestamp, index ,
expRowsList, records, userId);
} catch (Exception e) {
e.printStackTrace();
}
latch.countDown();
}
});
}
// 3.压缩excel文件并导出
latch.await();
ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId);
- Создайте хранилище Excel по локальному пути
/**
* @Description: 生成一个Excel存放到本地路径
* @param fileNameWithTimestamp
* @param index
* @param excelHeader
* @param dataList
* @param <T>
* @param userId
*/
public static <T> void createOneExcel(final String fileNameWithTimestamp,
int index,
final String[] excelHeader,
final List<T> dataList,
Long userId ) {
final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp ;
Workbook wb = null;
FileOutputStream fos = null;
try {
// 创建一个Workbook，对应一个Excel文件
wb = writeExcel(dataList, excelHeader);
// 生成本地Excel初始文件
Map<String, Object> fileInfo = new HashMap<String, Object>();
FileUtil.createFile(localRelativePath, fileNameWithTimestamp +
"_" + index + "_.xls", fileInfo);
fos = new FileOutputStream(fileInfo.get("realPath").toString() );
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (wb != null)
wb.close();
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
- Сжать все файлы Excel и экспортировать
/**
* @param request
* @param response
* @param fileNameWithTimestamp
* @param userId
*/
public static void createZipExport(HttpServletRequest request,
HttpServletResponse response,
final String fileNameWithTimestamp,
Long userId) throws Exception{
final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp;
// 创建文件夹,先将生成的excel保存到服务器本地目录
// excel文件路径:'/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS'
String excelFold = FileUtil.getFileRootPath() + localRelativePath;
// zip文件所在路径："/app/file/userId/fileNameWithTimestamp.zip"
String zipFold = FileUtil.getFileRootPath() + userId;
// 生成zip文件
final String zipFileName = fileNameWithTimestamp +".zip";
FileUtil.createZipFile(excelFold, zipFold, zipFileName);
// 创建导出输入流
InputStream is = null;
try{
is = new FileInputStream(new File(zipFold + File.separator + zipFileName));
} catch(IOException e){
e.printStackTrace();
}
BufferedInputStream bis = new BufferedInputStream(is);
// ServletOutputStream out = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
// 解决设置名称时的乱码问题
String zipName = handleFileName(request, zipFileName);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + zipName);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while ((bytesRead = bis.read(buff, 0, buff.length)) != -1 ) {
bos.write(buff, 0, bytesRead);
}
bis.close();
bos.close();
// 删除用来临时保存Excel的文件夹及zip文件
FileUtil.deleteDir(new File(zipFold));
}