private String templatePath = "D:/file/test";
private String tempPath= "D:/file/excel";
public void reportZipExcel(List<DeviceEnergyReport> deviceEnergyReportList,HttpServletResponse response){
List<File> srcfile = new ArrayList<File>(); //声明一个集合，用来存放多个Excel文件路径及名称
for(DeviceEnergyReport deviceEnergyReport:deviceEnergyReportList){
DeviceEnergyReport exportData = getExportData(deviceEnergyReport);
// 文件模板路径
String rootpath = templatePath+File.separator;
File file=new File(rootpath + File.separator + "tongjis.xls");
//生成临时文件
String filename=exportData.getReportName() + ".xls";
File newFile = new File(tempPath+File.separator,filename);
try {
ExcelUtil.createNewFile(filename,file,tempPath);
} catch (Exception e) {
e.printStackTrace();
}
// 新文件写入数据，并下载*****************************************************
if(exportData.getReportType() == 1){
ymdDataReportExcel(newFile,exportData);
}else if(exportData.getReportType() == 3){
rangeDataReportExcel(newFile,exportData);
}
//生成的excel文件保存，用来后面进行压缩
srcfile.add(newFile);
}
//压缩实现代码
//指定磁盘目录
String strpath=tempPath+File.separator;//例如D://file
//指定.zip格式和名称
String pathname=new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".zip";
File zipfile = new File(strpath+pathname);
//压缩多个excel文件为.zip格式并删除
ExcelUtil.zipFiles(srcfile,zipfile);
ExcelUtil.deleteFiles(srcfile);
//下载.zip格式文件并删除
ExcelUtil.downFile(response,strpath,pathname);
ExcelUtil.deleteZip(zipfile);
}poi操作导出excel内容
private void ymdDataReportExcel(File newFile,DeviceEnergyReport exportData){
InputStream is = null;
HSSFWorkbook sheets = null;
HSSFSheet hssfSheet = null;
FileOutputStream fos = null;
Integer sheetIndex = 0;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
sheets = new HSSFWorkbook(is);// 创建个workbook，
// 写数据
fos = new FileOutputStream(newFile);
int numberOfSheets = sheets.getNumberOfSheets();
String sheetName = "";
String reportName = exportData.getReportName();
if(exportData.getStructureType() == 1){
Map<Integer, Station> stationMap = deviceMixService.getSysStation().stream().collect(Collectors.toMap(Station::getRowId, station -> station, (e1, e2) -> e1));
sheetName = stationMap.get(exportData.getStationId()).getName();
}else{
Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
sheetName = deviceMap.get(exportData.getDeviceId()).getName();
}
Map<String, HSSFRow> hSSFRowMap = setYmdExcelBasic(sheets, exportData.getExportlatitude(), reportName,sheetName,sheetIndex,numberOfSheets);
int num=0;
HSSFRow energyTimeRow = hSSFRowMap.get("energyTimeRow");
HSSFRow energyValueRow = hSSFRowMap.get("energyValueRow");
for(EnergyLog energyLog:exportData.getEnergyLogList()){
num = num+1;
energyTimeRow.createCell(num).setCellValue(energyLog.getDateTime());
energyValueRow.createCell(num).setCellValue(energyLog.getResult());
}
String[] split = reportName.split(":");
if(exportData.getStructureType() == 1){//站点下设备导出
Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
QueryWrapper<DeviceReport> wrapper = new QueryWrapper<>();
wrapper.eq("report_id",exportData.getRowId());
List<DeviceReport> list = deviceReportService.list(wrapper);
JSONArray jsonArray;
List<EnergyLog> energyList;
for(DeviceReport deviceReport:list){
sheetIndex = sheetIndex+1;
jsonArray = JSONObject.parseArray(deviceReport.getReportContent());
energyList = jsonArray.toJavaList(EnergyLog.class);
sheetName = deviceMap.get(deviceReport.getDeviceId()).getName();
reportName = split[0]+":"+deviceMap.get(deviceReport.getDeviceId()).getName()+"能耗数据";
hSSFRowMap = setYmdExcelBasic(sheets, exportData.getExportlatitude(), reportName,sheetName,sheetIndex,numberOfSheets);
num=0;
energyTimeRow = hSSFRowMap.get("energyTimeRow");
energyValueRow = hSSFRowMap.get("energyValueRow");
for(EnergyLog energyLog:energyList){
num = num+1;
energyTimeRow.createCell(num).setCellValue(energyLog.getDateTime());
energyValueRow.createCell(num).setCellValue(energyLog.getResult());
}
}
}
sheets.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
if(null != fos){
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private void rangeDataReportExcel(File newFile,DeviceEnergyReport exportData){
InputStream is = null;
HSSFWorkbook sheets = null;
HSSFSheet hssfSheet = null;
FileOutputStream fos = null;
Integer sheetIndex = 0;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
sheets = new HSSFWorkbook(is);// 创建个workbook，
int numberOfSheets = sheets.getNumberOfSheets();
// 写数据
fos = new FileOutputStream(newFile);
String sheetName = "";
String reportName = exportData.getReportName();
if(exportData.getStructureType() == 1){
Map<Integer, Station> stationMap = deviceMixService.getSysStation().stream().collect(Collectors.toMap(Station::getRowId, station -> station, (e1, e2) -> e1));
sheetName = stationMap.get(exportData.getStationId()).getName();
}else{
Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
sheetName = deviceMap.get(exportData.getDeviceId()).getName();
}
Map<String, Object> hssfSheetMap = setSpanExcelBasic(sheets, exportData.getExportlatitude(), reportName, sheetName,sheetIndex,numberOfSheets);
hssfSheet = (HSSFSheet)hssfSheetMap.get("hssfSheet");
HSSFCell titleCell;
HSSFCellStyle titleStyle = (HSSFCellStyle)hssfSheetMap.get("titleStyle");
Map<String, List<EnergyLog>> energyCollect = exportData.getEnergyLogList().stream().collect(Collectors.groupingBy(EnergyLog::getAnlysisDate));
Set<String> keySet = energyCollect.keySet();
List<String> keyList = new ArrayList<>();
keySet.stream().forEach(key->keyList.add(key));
List<String> sortKeyList = DateUtils.sortDate(keyList);
// Iterator<Map.Entry<String, List<EnergyLog>>> energyIterator = energyCollect.entrySet().iterator();
int cellNum=0;
int rowNum = 1;
for(String key:sortKeyList){
cellNum=+1;
rowNum = rowNum+1;
HSSFRow hssfRow = hssfSheet.createRow(rowNum);
hssfSheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 10, 12));//标题
hssfRow.setHeight((short) (25 * 20));
titleCell = hssfRow.createCell(10);
titleCell.setCellValue(key);
titleCell.setCellStyle(titleStyle);
rowNum = rowNum+1;
HSSFRow hssfRow1 = hssfSheet.createRow(rowNum);
hssfRow1.setHeight((short) (25 * 20));
titleCell = hssfRow1.createCell(0);
titleCell.setCellValue("时间");
titleCell.setCellStyle(titleStyle);
rowNum = rowNum+1;
HSSFRow hssfRow2 = hssfSheet.createRow(rowNum);
hssfRow2.setHeight((short) (25 * 20));
titleCell = hssfRow2.createCell(0);
titleCell.setCellValue("用能值");
titleCell.setCellStyle(titleStyle);
List<EnergyLog> value = energyCollect.get(key);
for(EnergyLog energyLog:value){
hssfRow1.createCell(cellNum).setCellValue(energyLog.getDateTime());
hssfRow2.createCell(cellNum).setCellValue(energyLog.getResult());
cellNum = cellNum+1;
}
}
String[] split = reportName.split(":");
if(exportData.getStructureType() == 1){//站点下设备导出
Map<Integer, Device> deviceMap = deviceMixService.getDeviceMap(deviceMixService.getAllDevice());
QueryWrapper<DeviceReport> wrapper = new QueryWrapper<>();
wrapper.eq("report_id",exportData.getRowId());
List<DeviceReport> list = deviceReportService.list(wrapper);
JSONArray jsonArray;
List<EnergyLog> energyList;
for(DeviceReport deviceReport:list){
sheetIndex = sheetIndex+1;
jsonArray = JSONObject.parseArray(deviceReport.getReportContent());
energyList = jsonArray.toJavaList(EnergyLog.class);
sheetName = deviceMap.get(deviceReport.getDeviceId()).getName();
reportName = split[0]+":"+deviceMap.get(deviceReport.getDeviceId()).getName()+"能耗数据";
hssfSheetMap = setSpanExcelBasic(sheets, exportData.getExportlatitude(), reportName, sheetName,sheetIndex,numberOfSheets);
hssfSheet = (HSSFSheet)hssfSheetMap.get("hssfSheet");
titleStyle = (HSSFCellStyle)hssfSheetMap.get("titleStyle");
energyCollect = energyList.stream().collect(Collectors.groupingBy(EnergyLog::getAnlysisDate));
keySet = energyCollect.keySet();
List<String> keys = new ArrayList<>();
keySet.stream().forEach(key->keys.add(key));
sortKeyList = DateUtils.sortDate(keys);
cellNum=0;
rowNum = 1;
for(String key:sortKeyList){
cellNum=+1;
rowNum = rowNum+1;
HSSFRow hssfRow = hssfSheet.createRow(rowNum);
hssfSheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 10, 12));//标题
hssfRow.setHeight((short) (25 * 20));
titleCell = hssfRow.createCell(10);
titleCell.setCellValue(key);
titleCell.setCellStyle(titleStyle);
rowNum = rowNum+1;
HSSFRow hssfRow1 = hssfSheet.createRow(rowNum);
hssfRow1.setHeight((short) (25 * 20));
titleCell = hssfRow1.createCell(0);
titleCell.setCellValue("时间");
titleCell.setCellStyle(titleStyle);
rowNum = rowNum+1;
HSSFRow hssfRow2 = hssfSheet.createRow(rowNum);
hssfRow2.setHeight((short) (25 * 20));
titleCell = hssfRow2.createCell(0);
titleCell.setCellValue("用能值");
titleCell.setCellStyle(titleStyle);
List<EnergyLog> value = energyCollect.get(key);
for(EnergyLog energyLog:value){
hssfRow1.createCell(cellNum).setCellValue(energyLog.getDateTime());
hssfRow2.createCell(cellNum).setCellValue(energyLog.getResult());
cellNum = cellNum+1;
}
}
}
}
sheets.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
if(null != fos){
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
excel样式 内容操作
/**
*
* @param sheets
* @param exportlatitude 导出纬度
* @param reportName 报表名称
* @param sheetName sheet页名称
* @param sheetIndex sheet页下标 即第几页
* @param numberOfSheets sheet原先有几页
* @return
*/
private Map<String,HSSFRow> setYmdExcelBasic(HSSFWorkbook sheets,String exportlatitude,String reportName,String sheetName,
int sheetIndex,int numberOfSheets){
Map<String,HSSFRow> map = new HashMap<>();
//创建sheet页名称
HSSFSheet hssfSheet;
if(sheetIndex >-1 && sheetIndex<numberOfSheets){//导出zip的excel 是复制excel来导出，已经有sheet页
hssfSheet = sheets.getSheetAt(sheetIndex);
if(null == hssfSheet){
hssfSheet = sheets.createSheet(sheetName);
}else{
sheets.setSheetName(sheetIndex,sheetName);
}
}else{
hssfSheet = sheets.createSheet(sheetName);
}
//单元格合并
hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 12));//标题
hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));//导出对象名称
hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 6));//导出内容
// Sheet样式
HSSFCellStyle titleStyle =sheets.createCellStyle();
// 背景色的设定
titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
// 前景色的设定
titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
// 填充模式
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//创建标题行
HSSFRow titleRow = hssfSheet.createRow(0);
titleRow.setHeight((short) (25 * 20));
HSSFCell titleCell = titleRow.createCell(10);
titleCell.setCellValue("统计报表");
titleCell.setCellStyle(titleStyle);
HSSFRow detailRow = hssfSheet.createRow(1);
detailRow.setHeight((short) (25 * 20));
titleCell = detailRow.createCell(0);
titleCell.setCellValue("导出维度");
titleCell.setCellStyle(titleStyle);
titleCell = detailRow.createCell(1);
titleCell.setCellValue(exportlatitude);
titleCell.setCellStyle(titleStyle);
titleCell = detailRow.createCell(3);
titleCell.setCellValue("导出内容");
titleCell.setCellStyle(titleStyle);
titleCell = detailRow.createCell(4);
titleCell.setCellValue(reportName);
titleCell.setCellStyle(titleStyle);
HSSFRow energyTimeRow = hssfSheet.createRow(2);
energyTimeRow.setHeight((short) (25 * 20));
titleCell = energyTimeRow.createCell(0);
titleCell.setCellValue("时间");
titleCell.setCellStyle(titleStyle);
HSSFRow energyValueRow = hssfSheet.createRow(3);
energyValueRow.setHeight((short) (25 * 20));
titleCell = energyValueRow.createCell(0);
titleCell.setCellValue("用能值");
titleCell.setCellStyle(titleStyle);
map.put("energyTimeRow",energyTimeRow);
map.put("energyValueRow",energyValueRow);
return map;
}工具类
package com.basic.cloud.platform.core.utils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
public class ExcelUtil {
/**
* 读取excel模板，并复制到新文件中供写入和下载
*path 保存文件的路径
* @return
*/
public static File createNewFile(String filename,File file,String path) {
// 读取模板，并赋值到新文件************************************************************
// 判断路径是否存在
File dir = new File(path);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(path, filename);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
e.printStackTrace();
}
return newFile;
}
/**
* 复制文件
*
* @param s
* 源文件
* @param t
* 复制到的新文件
*/
public static void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 将多个Excel打包成zip文件
* @param srcfile
* @param zipfile
*/
public static void zipFiles(List<File> srcfile, File zipfile) {
byte[] buf = new byte[1024];
try {
// Create the ZIP file
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
// Compress the files
for (int i = 0; i < srcfile.size(); i++) {
File file = srcfile.get(i);
FileInputStream in = new FileInputStream(file);
// Add ZIP entry to output stream.
out.putNextEntry(new ZipEntry(file.getName()));
// Transfer bytes from the file to the ZIP file
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
// Complete the entry
out.closeEntry();
in.close();
}
// Complete the ZIP file
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除多个文件方法
*
* @param srcfile
*/
public static void deleteFiles(List<File> srcfile) {
for (File file : srcfile) {
if (file.exists()) {
file.delete();
}
}
}
public static void downFile(HttpServletResponse response, String serverPath, String str) {
try {
String path = serverPath + str;
File file = new File(path);
if (file.exists()) {
InputStream ins = new FileInputStream(path);
BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面
OutputStream outs = response.getOutputStream();// 获取文件输出IO流
BufferedOutputStream bouts = new BufferedOutputStream(outs);
response.setContentType("application/x-download");// 设置response内容的类型
response.setHeader(
"Content-disposition",
"attachment;filename="
+ URLEncoder.encode(str, "GBK"));// 设置头部信息
int bytesRead = 0;
byte[] buffer = new byte[8192];
//开始向网络传输文件流
while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
bouts.write(buffer, 0, bytesRead);
}
bouts.flush();// 这里一定要调用flush()方法
ins.close();
bins.close();
outs.close();
bouts.close();
} else {
response.sendRedirect("../error.jsp");
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除zip
*/
public static void deleteZip(File path) {
if (path.exists()) {
path.delete();
}
}
}
参考博文：poi根据模板导出多个excel文件并压缩成.zip格式_小志的博客的博客-CSDN博客_poi导出多个excel文件
6347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
