package com.meritdata.cloud.cosp.profilemanagement.util;
import java.io.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;
/**
* @Description: 文件压缩工具类 将指定文件/文件夹压缩成zip、rar压缩文件
*/
@Slf4j
public class CompressedFileUtil {
public static void compressedFile(String resourcesPath, String targetPath) {
try {
compressedFile(resourcesPath, targetPath, "");
} catch (IOException e) {
log.error("IO流异常",e);
}
}
/**
* @param
* @param targetPath 目的压缩文件保存路径
* @return void
* @throws Exception
* @desc 将源文件/文件夹生成指定格式的压缩文件,格式zip
*/
public static void compressedFile(String resourcesPath, String targetPath, String fileName) throws IOException {
File resourcesFile = new File(resourcesPath); // 源文件
File targetFile = new File(targetPath); // 目的
// 如果目的路径不存在,则新建
if (!targetFile.exists()) {
targetFile.mkdirs();
}
String targetName = (StringUtils.isEmpty(fileName) ? resourcesFile.getName() : fileName) + (fileName.indexOf(".zip") == -1 ? ".zip" : ""); // 目的压缩文件名
ZipOutputStream out = null;
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(new File(targetPath, targetName));
out = new ZipOutputStream(new BufferedOutputStream(outputStream));
createCompressedFile(out, resourcesFile, "");
} catch (FileNotFoundException e) {
log.error("文件找不到",e);
} finally {
if(out != null ) {
out.close();
}
if(outputStream!=null){
outputStream.close();
}
}
}
/**
* @param out 输出流
* @param file 目标文件
* @return void
* @throws Exception
* @desc 生成压缩文件。 如果是文件夹,则使用递归,进行文件遍历、压缩 如果是文件,直接压缩
*/
@SneakyThrows
public static void createCompressedFile(ZipOutputStream out, File file, String dir) {
//System.out.println(file.getPath());
// 如果当前的是文件夹,则进行进一步处理
if (file.isDirectory()) {
// 得到文件列表信息
File[] files = file.listFiles();
// 将文件夹添加到下一级打包目录
try {
out.putNextEntry(new ZipEntry(dir + "/"));
} catch (IOException e) {
log.error("io流异常",e);
}
dir = dir.length() == 0 ? "" : dir + "/";
// 循环将文件夹中的文件打包
for (int i = 0; i < files.length; i++) {
createCompressedFile(out, files[i], dir + files[i].getName()); // 递归处理
}
} else { // 当前的是文件,打包处理
// 文件输入流
FileInputStream fis = null ;
try {
fis = new FileInputStream(file);
out.putNextEntry(new ZipEntry(dir));
// 进行写操作
int j = 0;
byte[] buffer = new byte[1024];
while ((j = fis.read(buffer)) > 0) {
out.write(buffer, 0, j);
}
// fis.close();
} catch (IOException e) {
log.error("io流异常",e);
} finally {
if(fis != null) {
fis.close();
}
}
}
}
}
@ApiOperation("导出数据")
@RequestMapping(value ="customerData",method = RequestMethod.GET)
public ResultBody export(){
List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
LinkedHashMap<String, Object> keyMap = new LinkedHashMap<>();
keyMap.put("id","编号");
keyMap.put("age","年龄");
keyMap.put("asset","资产");
keyMap.put("commission","佣金");
keyMap.put("createTime","创建时间");
keyMap.put("custId","用户编号");
keyMap.put("custName","姓名");
keyMap.put("sales","出版社");
keyMap.put("sex","性别");
keyMap.put("states","状态");
keyMap.put("tel","电话");
List<CustGroupRel> all = custGroupRelRepository.findAll();
for(CustGroupRel a :all){
Map<String,Object> map = new LinkedHashMap<String,Object>();
map.put("id",a.getId());
map.put("age",a.getAge());
map.put("asset",a.getAsset());
map.put("commission",a.getCommission());
map.put("createTime",a.getCreateTime());
map.put("custId",a.getCustId());
map.put("custName",a.getCustName());
map.put("sales",a.getSales());
map.put("sex",a.getSex());
map.put("states",a.getStates());
map.put("tel",a.getTel());
data.add(map);
}
// 每个文件最大数据量
int maxFileSize = 500000;
// 每个sheet页最大数据量
int maxSheetSize = 10000;
String title = "客户表信息";
long start = System.currentTimeMillis();
ExportExcelUtils.exportExcel(data,keyMap,maxFileSize,maxSheetSize,title,"客户表信息",path);
long end = System.currentTimeMillis();
System.out.println("耗时:" + (end - start) / 1000 + "秒");
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String datestr = sdf.format(date);
String fileName = datestr + "用户表";
try {
CompressedFileUtil.compressedFile(path, zip,fileName);
System.out.println("压缩文件已经生成...");
} catch (Exception e) {
System.out.println("压缩文件生成失败...");
log.error("压缩失败",e);
}
return ResultBody.success("导出成功" + path + title + " 共耗时" + (end - start) / 1000 + "秒" );
}
package com.meritdata.cloud.cosp.profilemanagement.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;
@Data
@Table(name = "cosp_index_column_config")
@Entity
@ApiModel(value="配置", description="配置")
public class CospIndex implements Serializable {
@Id
@Column(name ="id")
@ApiModelProperty(value = "id",name = "id")
private String id;
@ApiModelProperty(value = "columnCode",name = "编码")
@Column(name ="column_code")
private String columnCode;
@ApiModelProperty(value = "columnName",name = "名字")
@Column(name = "column_name")
private String columnName;
@ApiModelProperty(value = "userId",name = "用户id")
@Column(name = "user_id")
private String userId;
@ApiModelProperty(value = "sortno",name = "排序号")
@Column(name = "sortno")
private Integer sortno;
@ApiModelProperty(value = "createTime",name = "创建时间")
@Column(name = "create_date")
private Date createTime;
}
```java
package com.meritdata.cloud.cosp.profilemanagement.util;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.*;
import java.util.*;
@Slf4j
public class ExportExcelUtils {
/**
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param keyMap (key 对应源数据的map的key , value 表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
* @Title: exportExcel
* @Description: 导出Excel的方法
*/
public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, LinkedHashMap<String, Object> keyMap, List<Map<String, Object>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.WHITE.index);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 生成表头
int cellNum = 0;
for (Map.Entry<String, Object> map : keyMap.entrySet()) {
HSSFCell cell = row.createCell(cellNum);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(map.getValue().toString());
cell.setCellValue(text.toString());
cellNum++;
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (Map<String, Object> maps : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (Map.Entry<String, Object> map : keyMap.entrySet()) {
HSSFCell cell = row.createCell(cellIndex);
Object obj = maps.get(map.getKey());
if (obj != null) {
cell.setCellValue(obj.toString());
} else {
cell.setCellValue("");
}
cellIndex++;
}
index++;
}
}
}
/**
* @param data 源数据
* @param keyMap excel的表头 源数据的key ("key","序号")
* @param maxFileSize 每个文件的最大数据量 maxSheetSize 的倍数 建议100000
* @param maxSheetSize 每个sheet的条数 建议 10000
* @param sheetName sheet名
* @param fileName 文件名 不需要后缀
* @param path 生成excel的路径
* @return boolean 成功反回 true
*/
public static boolean exportExcel(List<Map<String, Object>> data, LinkedHashMap<String, Object> keyMap, int maxFileSize, int maxSheetSize, String sheetName, String fileName, String path) {
boolean result = false;
OutputStream out = null;
HSSFWorkbook workbook = null;
if (data != null && data.size() > 0) {
int zongSize = data.size();
int zongFileCount = 0;
// 判断需要分几个文件
if (zongSize % maxFileSize == 0) {
zongFileCount = zongSize / maxFileSize;
} else {
zongFileCount = zongSize / maxFileSize + 1;
}
try {
for (int i = 0; i < zongFileCount; i++) {
int fileLimit = 0;
if ((i * maxFileSize) + maxFileSize > zongSize) {
fileLimit = zongSize;
} else {
fileLimit = (i * maxFileSize) + maxFileSize;
}
int fileStart = i * maxFileSize;
File file = new File(path);
if (!file.exists()) {
file.mkdirs();
}
out = new FileOutputStream(path + File.separator + fileName + "(" + (i + 1) + ")" + ".xls");
List<Map<String, Object>> fileData = new ArrayList<Map<String, Object>>();
for (int j = fileStart; j < fileLimit; j++) {
fileData.add(data.get(j));
}
int fileDataSize = fileData.size();
int sheetCount = 0;
if (fileDataSize % maxSheetSize == 0) {
sheetCount = fileDataSize / maxSheetSize;
} else {
sheetCount = fileDataSize / maxSheetSize + 1;
}
workbook = new HSSFWorkbook();
for (int j = 0; j < sheetCount; j++) {
int sheetStart = j * maxSheetSize;
int sheetLimt = 0;
if (j * maxSheetSize + maxSheetSize > fileDataSize) {
sheetLimt = fileDataSize;
} else {
sheetLimt = j * maxSheetSize + maxSheetSize;
}
List<Map<String, Object>> sheetData = new ArrayList<Map<String, Object>>();
for (int k = sheetStart; k < sheetLimt; k++) {
sheetData.add(fileData.get(k));
}
exportExcel(workbook, j, sheetName + (j + 1), keyMap, sheetData, out);
}
workbook.write(out);
result = true;
}
} catch (FileNotFoundException e) {
result = false;
log.info("文件找不到",e);
} catch (IOException e) {
result = false;
log.info("io异常",e);
} catch (Exception e) {
result = false;
log.info("错误",e);
} finally {
try {
if (out != null) {
out.close();
}
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
result = false;
log.info("io异常",e);
}
}
}
return result;
}
}