package com.zjtzsw.wb.modules.zzdClick.remote;
import com.alibaba.fastjson.JSONArray;
import com.zjtzsw.yth.core.validator.Assert;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import javax.activation.MimetypesFileTypeMap;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLDecoder;
import java.util.UUID;
/**
* <p>
*
* </p>
*
* @author zzl
* @since 2022/7/19 14:08
*/
@Component
@Slf4j
public class ExcelRemote {
@Value("${zzdclick.file.path}")
private String path;
public void exportAll(JSONArray jsonArray, HttpServletResponse response, HttpServletRequest request) {
OutputStream os = null;
FileInputStream fis = null;
File file = null;
String[] tableHead = {"企业名称", "统一信用代码", "工伤保险参保人数","其中省外参保人数"};
try {
HSSFWorkbook wb = new HSSFWorkbook();
if (jsonArray.size() < 1) {
Assert.ythException("无数据!");
}
String targetFilePath = path + UUID.randomUUID().toString() + ".xls";
String safeName = WorkbookUtil.createSafeSheetName("规上企业数据");
HSSFSheet sheet = wb.createSheet(safeName);
//设置列宽
sheet.setColumnWidth(0, 8 * 1000);
sheet.setColumnWidth(1, 8 * 1000);
sheet.setColumnWidth(2, 8 * 1000);
sheet.setColumnWidth(3, 8 * 1000);
//表头字体
HSSFFont fontTableHeader = wb.createFont();
fontTableHeader.setBold(true);
fontTableHeader.setFontHeight((short) (4 * 50));
fontTableHeader.setFontName("宋体");
//单元格字体1
HSSFFont fontTable = wb.createFont();
fontTable.setBold(false);
fontTable.setFontHeight((short) (4 * 50));
fontTable.setFontName("宋体");
//表头样式
HSSFCellStyle cellTitleStyle = wb.createCellStyle();
cellTitleStyle.setAlignment(HorizontalAlignment.CENTER);
cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//单元格边框
cellTitleStyle.setBorderTop(BorderStyle.THIN);
cellTitleStyle.setBorderBottom(BorderStyle.THIN);
cellTitleStyle.setBorderLeft(BorderStyle.THIN);
cellTitleStyle.setBorderRight(BorderStyle.THIN);
//单元格样式1
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//单元格边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//设置表头
HSSFCell cell;
//设置标题
HSSFRow row = sheet.createRow(0);
//设置表头
for (int j = 0; j < tableHead.length; j++) {
cell = row.createCell(j);
cell.setCellValue(tableHead[j]);//表头
cell.setCellStyle(cellTitleStyle);
row.setHeight((short) (5 * 100));
}
for (int i = 1; i < jsonArray.size() + 1; i++) {
cellStyle.setFont(fontTable);
//第一行
HSSFRow rowCell = sheet.createRow(i);
//设置行高
rowCell.setHeight((short) (4 * 100));
cell = rowCell.createCell(0);
cell.setCellValue((String) jsonArray.getJSONObject(i - 1).get("aab069"));
cell.setCellStyle(cellStyle);
cell = rowCell.createCell(1);
cell.setCellValue((String) jsonArray.getJSONObject(i - 1).get("aaz010"));
cell.setCellStyle(cellStyle);
cell = rowCell.createCell(2);
cell.setCellValue(jsonArray.getJSONObject(i - 1).get("people0").toString());
cell.setCellStyle(cellStyle);
cell = rowCell.createCell(3);
cell.setCellValue(jsonArray.getJSONObject(i - 1).get("people1").toString());
cell.setCellStyle(cellStyle);
}
//合并单元格
String last = "";
int k = 0;
for (int i = 1; i < jsonArray.size() + 1; i++) {
if (i == 1) {
last = (String) jsonArray.getJSONObject(i - 1).get("people0");
} else {
if (last.equals((String) jsonArray.getJSONObject(i - 1).get("people0"))) {
k += 1;
} else {
sheet.addMergedRegion(new CellRangeAddress(i - (k + 1), i - 1, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(i-(k+1), i-1,3 ,3));
last = (String) jsonArray.getJSONObject(i - 1).get("people0");
k = 0;
}
if (i == jsonArray.size()) {
sheet.addMergedRegion(new CellRangeAddress(i - (k + 1) + 1, i, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(i-(k+1)+1, i,3 ,3));
}
}
}
FileOutputStream fos = new FileOutputStream(targetFilePath);
wb.write(fos);
fos.close();
//文件下载前端
file = new File(targetFilePath);
if (file.exists()) {
fis = new FileInputStream(file);
byte[] bytes = new byte[fis.available()];
fis.read(bytes);
response.setContentType(new MimetypesFileTypeMap().getContentType(file));
response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(
URLDecoder.decode("规上企业数据.xls", "UTF-8")
.getBytes("gb2312"), "ISO8859-1"));
os = response.getOutputStream();
os.write(bytes);
} else {
Assert.ythException("导出失败");
}
} catch (Exception e) {
e.printStackTrace();
Assert.ythException("导出失败");
} finally {
try {
if (os != null) {
os.flush();
os.close();
}
if (fis != null) {
fis.close();
}
if (file != null && file.exists()) {
file.delete();
}
} catch (Exception e) {
}
}
}
}
合并单元格这个格式不能作为公共实例。
网友评论