private static final String[] ROW_NAMES = {
"编号", "名称", "名称", "单位", "金额", "申报时间", "办结时间", "审批状态"
};
private static final String[] ROW_NAMES_EN = {
"handleNum", "matterName", "enterpriseName", "handingDept", "appliedCashAmount", "createTime", "finishTime", "handleStatus"
};
public List<Map<String, String>> getColumns() {
return ImportTemplateUtils.getColumns(ROW_NAMES_EN, ROW_NAMES);
}
public static List<Map<String, String>> getColumns(String[] ens, String[] cn) {
List<Map<String, String>> result = new ArrayList<>(2);
List<String> enList = Arrays.asList(ens);
enList.forEach(en -> {
Map<String, String> map = new HashMap<>(3);
map.put("label", cn[enList.indexOf(en)]);
map.put("value", en);
result.add(map);
});
return result;
}
public void exportExcel(Page<MatMatterEnjoyEnterpriseRecordDTO> page,
MatMatterEnjoyEnterpriseRecordDTO matMatterEnjoyEnterpriseRecordDTO,
HttpServletRequest request, HttpServletResponse response) {
String[] rowNamesEn = matMatterEnjoyEnterpriseRecordDTO.getColumns();
if (ObjectUtil.isNull(rowNamesEn)) {
rowNamesEn = new String[1];
}
String[] rowNames = new String[rowNamesEn.length];
List<String> ens = Arrays.asList(ROW_NAMES_EN2);
for (int i = 0; i < rowNamesEn.length; i++) {
if (!ens.contains(rowNamesEn[i])) {
break;
}
rowNames[i] = ROW_NAMES2[ens.indexOf(rowNamesEn[i])];
}
// 自定义标题别名,换成链表表头顺序会固定
Map<String, String> headerAlias = ExcelUtil.getFirstRow(rowNames, rowNamesEn, ExcelUtil.OUT);
// 查询结果
List<MatMatterEnjoyEnterpriseRecord> records = baseMapper.getDetailList(matMatterEnjoyEnterpriseRecordDTO);
// 封装导出对象
List<MatMatterEnjoyEnterpriseRecordPOI> poiList = new ArrayList<>();
final int[] iNum = {1};
records.forEach(
d -> {
MatMatterEnjoyEnterpriseRecordPOI poi = new MatMatterEnjoyEnterpriseRecordPOI();
poi.setIndex(iNum[0]);
poi.setHandleNum(d.getHandleNum());
poi.setEnterpriseName(d.getEnterpriseName());
poi.setAppliedCashAmount(d.getAppliedCashAmount());
poi.setMatterName(d.getMatterName());
poi.setHandingDept(d.getHandingDept());
poi.setCreateTime(CommonUtil.localDateTimeToStr(d.getCreateTime()));
poi.setFinishTime(CommonUtil.localDateTimeToStr(d.getFinishTime()));
poi.setHandleStatus(d.getHandleStatus());
poiList.add(poi);
iNum[0]++;
}
);
// 多sheet的导出,关闭流变成了专门一个方法,后续考虑整合到common
ExcelWriter writer = null;
OutputStream out = null;
try {
writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
List<ExcelStyle> excelStyleList = new ArrayList<>(2);
excelStyleList.add(new ExcelStyle(writer, headerAlias, poiList, "办件统计", 1));
ImportTemplateUtils.setStyle(excelStyleList);
// 设置返回头
ImportTemplateUtils.setResponse(response, ImportTemplateUtils.getNameWithRequest(request, "办件统计.xlsx"));
//合并单元格
//定义启始行
int index = 1;
int index2 = 1;
int index3 = 1;
List<List<Object>> rows = new LinkedList<>();
//按照受理编号分组数据汇总处理(lambda表达式,不了解可以百度)
Map<String, List<MatMatterEnjoyEnterpriseRecordPOI>> staffNoGroupMaps =
poiList.stream().collect(Collectors.groupingBy(i->i.getHandleNum(),LinkedHashMap::new,Collectors.toList()));
for (Map.Entry<String, List<MatMatterEnjoyEnterpriseRecordPOI>> listEntry : staffNoGroupMaps.entrySet()) {
List<MatMatterEnjoyEnterpriseRecordPOI> staffNoList = listEntry.getValue();
//根据数据条数设置合并单元格信息
if (staffNoList.size() == 1) {//一条数据不合并
index = index + staffNoList.size();
index2 = index2 + staffNoList.size();
index3 = index3 + staffNoList.size();
} else {
//规则编写
writer.merge(index, index + staffNoList.size() - 1, 1, 1,
null, true);//## 标题
index = index + staffNoList.size();
//按照编码进行分组
Map<String, List<MatMatterEnjoyEnterpriseRecordPOI>> terminalNumberGroupMaps =
staffNoList.stream().collect(Collectors.groupingBy(i -> i.getMatterName(), LinkedHashMap::new, Collectors.toList()));
for (Map.Entry<String, List<MatMatterEnjoyEnterpriseRecordPOI>> list2Entry : terminalNumberGroupMaps.entrySet()) {
List<MatMatterEnjoyEnterpriseRecordPOI> terminalNumberList = list2Entry.getValue();
//根据数据条数设置合并单元格信息
if (terminalNumberList.size() == 1) {//一条数据不合并
index2 = index2 + terminalNumberList.size();
} else {
//规则编写
writer.merge(index2, index2 + terminalNumberList.size() - 1, 2, 2,
null, true);
index2 = index2 + terminalNumberList.size();
}
}
//按照编码进行分组
Map<String, List<MatMatterEnjoyEnterpriseRecordPOI>> terminalNumberGroupMaps2 =
staffNoList.stream().collect(Collectors.groupingBy(i -> i.getHandingDept(), LinkedHashMap::new, Collectors.toList()));
for (Map.Entry<String, List<MatMatterEnjoyEnterpriseRecordPOI>> list2Entry : terminalNumberGroupMaps2.entrySet()) {
List<MatMatterEnjoyEnterpriseRecordPOI> terminalNumberList = list2Entry.getValue();
//根据数据条数设置合并单元格信息
if (terminalNumberList.size() == 1) {//一条数据不合并
index3 = index3 + terminalNumberList.size();
} else {
//规则编写
writer.merge(index3, index3 + terminalNumberList.size() - 1, 4, 4,
null, true);
index3 = index3 + terminalNumberList.size();
}
}
}
}
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
log.error("办件统计导出异常:" + e.getMessage(), e);
} finally {
// 关闭writer,释放内存
IoUtil.close(writer);
// 此处记得关闭输出Servlet流
IoUtil.close(out);
}
}
package com.govmade.gds.common.core.excel;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.WorkbookUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import com.google.common.collect.Maps;
import com.govmade.gds.common.core.exception.CheckedException;
import io.micrometer.core.instrument.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@SuppressWarnings("rawtypes")
public class ExcelUtil {
private ExcelAdapter adapter;
public ExcelUtil(ExcelAdapter adapter) {
super();
this.adapter = adapter;
}
private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public final static String IN = "in";
public final static String OUT = "out";
/**
* 表头别名设置
*
* @param ROW_NAMES 中文别名
* @param ROW_NAMES_EN 英文别名
* @param type 导入导出类型in导入 out导出
* @return map
*/
public static Map<String, String> getFirstRow(final String[] ROW_NAMES, final String[] ROW_NAMES_EN, String type) {
if (ROW_NAMES.length == 0 || ROW_NAMES.length != ROW_NAMES_EN.length) {
throw new IllegalArgumentException("表头中英文长度不一致");
}
Map<String, String> map;
switch (type) {
case IN:
map = Maps.newHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES[i], ROW_NAMES_EN[i]);
}
break;
case OUT:
map = Maps.newLinkedHashMap();
for (int i = 0; i < ROW_NAMES.length; i++) {
map.put(ROW_NAMES_EN[i], ROW_NAMES[i]);
}
break;
default:
throw new IllegalArgumentException("表头转换类型错误");
}
return map;
}
/**
* 通过hutool读取文件内容
*
* @param file 文件
* @param rowNames 表头信息
* @param firstRow 文件头别名信息
* @param beanType 转换的类型
* @return list
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
public static <T> List<T> readFile(MultipartFile file, String[] rowNames, Map<String, String> firstRow, Class<T> beanType) throws IOException, InvalidFormatException {
//校验文件格式及表头并获取数据
checkFile(file);
checkFirstRowWithRowName(file, rowNames, 0);
ExcelReader excelReader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream());
excelReader.setHeaderAlias(firstRow);
return excelReader.readAll(beanType);
}
/**
* 通过hutool读取文件内容
*
* @param fullPath 路径
* @param page sheet页
* @throws IOException
* @throws InvalidFormatException
*/
public void readResrcFile(String fullPath, Integer page) throws IOException, InvalidFormatException {
// 解析excel
Workbook workbook = null;
// 将Excel的各行记录放入ImpExcelBean的list里面
try {
workbook = WorkbookFactory.create(new FileInputStream(fullPath));
Sheet sheet = workbook.getSheetAt(page);// 统计excel的行数
int rowLen = sheet.getPhysicalNumberOfRows();// excel总行数,记录数=行数-1
int cellNum = sheet.getRow(0).getLastCellNum();
int startRow = adapter.getStartRow();
if (startRow >= rowLen) {
adapter.setError(true);
adapter.appendMsg("模板为空!");
return;
}
for (int i = adapter.getStartRow(); i < rowLen; i++) {
Row row = sheet.getRow(i);
// 将Excel中各行记录依次导入到ImpExcelBean的list中
if (row != null) {
String[] str = new String[cellNum];
boolean isEmpty = true;
for (int j = 0; j < cellNum; j++) {
Cell c = row.getCell(j);
if (c == null) {
str[j] = "";
continue;
}
boolean isMerge = PoiUtil.isMergedRegion(sheet, i, c.getColumnIndex());//判断是否具有合并单元格
if (isMerge) {
str[j] = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
} else {
str[j] = PoiUtil.getValue(c);
}
if (StrUtil.isNotEmpty(str[j].trim())) {
isEmpty = false;
}
}
if (!isEmpty) {
adapter.buildList(str, i);
}
}
}
} catch (Exception e) {
adapter.setError(true);
if (StringUtils.isBlank(adapter.getErrorMsg() + "")) {
adapter.appendMsg("模板错误");
}
e.printStackTrace();
}
}
/**
* 通过hutool读取文件内容
*
* @param file 文件
* @param rowNames 表头信息
* @param firstRow 文件头别名信息
* @param beanType 转换的类型
* @param sheetIndex shheet序号
* @return list
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
public static <T> List<T> readFileWithSheetIndex(MultipartFile file, String[] rowNames, Map<String, String> firstRow, Class<T> beanType, Integer sheetIndex) throws IOException, InvalidFormatException {
//校验文件格式及表头并获取数据
checkFile(file);
checkFirstRowWithRowName(file, rowNames, sheetIndex);
ExcelReader excelReader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream(), sheetIndex);
excelReader.setHeaderAlias(firstRow);
return excelReader.readAll(beanType);
}
/**
* 通过hutool导出数据
*
* @param fileName 文件名称
* @param sheetName sheet名称
* @param headerAlias 标题别名
* @param list 导出数据
* @param title 标题
* @param response 返回体
*/
public static void writeFile(String fileName, String sheetName, Map<String, String> headerAlias, Iterable<?> list, String title, HttpServletResponse response) {
ExcelWriter writer = null;
OutputStream out = null;
try {
// 通过工具类创建writer
writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
//设置样式
setStyle(writer, headerAlias, list, sheetName, title);
//设置返回头
setResponse(response, fileName);
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
} finally {
// 关闭writer,释放内存
IoUtil.close(writer);
//此处记得关闭输出Servlet流
IoUtil.close(out);
}
}
/**
* 设置返回头
*
* @param response response
* @param fileName 文件名
*/
private static void setResponse(HttpServletResponse response, String fileName) {
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//fileName是弹出下载对话框的文件名,不能为中文,中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
}
/**
* 设置样式
*
* @param writer hutool的导出类
* @param headerAlias 标题别名
* @param list 导出数据
* @param sheetName sheet名称
* @param title 标题
*/
private static void setStyle(ExcelWriter writer, Map<String, String> headerAlias, Iterable<?> list, String sheetName, String title) {
//自定义标题别名
writer.setHeaderAlias(headerAlias);
//只保留设置了别名的字段
writer.setOnlyAlias(true);
// 一次性写出内容,使用默认样式,强制输出标题,这里要放在设置宽行属性之前
writer.write(list, true);
//ignoreHead表示是否忽略头部样式
writer.getStyleSet().setFont((short) -1, (short) 11, "Courier New", true);
//设置表头和单元格格式
Font font = StyleUtil.createFont(writer.getWorkbook(), (short) -1, (short) 13, "Courier New");
//头部样式独立调整
writer.getHeadCellStyle().setFont(font);
//表头设置换行
writer.getHeadCellStyle().setWrapText(true);
//自动宽度
writer.autoSizeColumnAll();
//自动宽度有点问题,设置个默认值
int columnCount = writer.getColumnCount();
for (int i = 0; i < columnCount; i++) {
int width = writer.getSheet().getColumnWidth(i);
if (width < 1990) {
writer.setColumnWidth(i, 15);
}
if (width > 19900) {
writer.setColumnWidth(i, 30);
}
}
//设置换行
writer.getStyleSet().setWrapText();
//设置行高
//writer.setDefaultRowHeight(25);
//更改sheet名称
writer.renameSheet(sheetName);
// 合并单元格后的标题行,使用默认标题样式
if (StrUtil.isNotBlank(title)) {
writer.merge(headerAlias.size(), title);
}
}
/**
* 校验文件是否存在或合法
*
* @param file 文件
* @throws IOException io异常
*/
private static void checkFile(MultipartFile file) throws IOException {
if (null == file) {
throw new FileNotFoundException("文件不存在!");
}
String fileName = file.getOriginalFilename();
if (StrUtil.isBlank(fileName) || (!fileName.endsWith(EXCEL2003) && !fileName.endsWith(EXCEL2007))) {
throw new IOException(fileName + "不是excel文件");
}
}
/**
* 校验表头是否正确
*
* @param file 文件
* @param rowNames 表头
* @throws IOException IOException
* @throws InvalidFormatException InvalidFormatException
*/
private static void checkFirstRowWithRowName(MultipartFile file, String[] rowNames, Integer sheetIndex) throws IOException, InvalidFormatException {
//使用hutool的类,内部其实还是调用WorkbookFactory.create方法,只不过多了个关闭流的操作
Workbook workbook = WorkbookUtil.createBook(file.getInputStream(), true);
Sheet sheet = workbook.getSheetAt(sheetIndex);
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
Row row = sheet.getRow(firstRowNum);
String s;
for (int i = 0; i < rowNames.length; i++) {
s = row.getCell(i).getStringCellValue();
if (StrUtil.isBlank(s)) {
throw new CheckedException("警告!模板为空或有误!");
}
checkNameWithRowName(s, i, rowNames[i]);
}
}
/**
* 文件中的表头与正确表头对比
*
* @param name 文件表头名称
* @param i 表头序列
* @param rowName 标准表头名称
*/
private static void checkNameWithRowName(String name, int i, String rowName) {
String error = "警告!模板为空或有误!第 " + (i + 1) + " 列名错误";
if (!rowName.equals(name)) {
throw new CheckedException(error);
}
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return PoiUtil.getValue(fCell);
}
}
}
return null;
}
/**
* 导出excel
*
* @param title 导出表的标题
* @param rowsName 导出表的列名
* @param dataList 需要导出的数据
* @param fileName 生成excel文件的文件名
* @param response
*/
public static void exportExcel(String title, String[] rowsName, List<String> bigRowName, List<String> smallRowName, List<Object[]> dataList, String fileName, HttpServletResponse response) throws Exception {
OutputStream output = response.getOutputStream();
response.reset();
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + fileName);
export(title, rowsName, bigRowName, smallRowName, dataList, output);
close(output);
}
/**
* 导出数据
*
* @param title
* @param rowName
* @param dataList
* @param out
*/
private static void export(String title, String[] rowName, List<String> bigRowName, List<String> smallRowName, List<Object[]> dataList, OutputStream out) {
try {
// 创建工作簿对象
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 创建工作表
SXSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
//HSSFRow rowm = sheet.createRow(0);
//创建表格标题列
//HSSFCell cellTiltle = rowm.createCell(0);
// sheet样式定义; getColumnTopStyle(); getStyle()均为自定义方法 --在下面,可扩展
// 获取列头样式对象
CellStyle columnTopStyle = ExcelUtil.getColumnTopStyle(workbook);
// 获取单元格样式对象
CellStyle style = ExcelUtil.getStyle(workbook);
//合并表格标题行,合并列数为列名的长度,第一个0为起始行号,第二个1为终止行号,第三个0为起始列好,第四个参数为终止列号
//sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
//设置标题行样式
//cellTiltle.setCellStyle(columnTopStyle);
//设置标题行值
//cellTiltle.setCellValue(title);
int x = 0;
if (bigRowName != null && !bigRowName.isEmpty()) {
int columnNum = bigRowName.size();
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n + 4);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(bigRowName.get(n));
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
x += 1;
}
if (smallRowName != null && !smallRowName.isEmpty()) {
int columnNum = smallRowName.size();
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n + 4);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(smallRowName.get(n));
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
x += 1;
}
if (x >= 1) {
SXSSFRow row = sheet.getRow(0);
int cellNum = row.getLastCellNum();
SXSSFCell tempCell = row.createCell(cellNum);
tempCell.setCellValue("%1");
String temp = row.getCell(4).getStringCellValue();
List<Integer> mergeList = new ArrayList<>();
for (int i = 4; i < cellNum + 1; i++) {
String stringCellValue = row.getCell(i).getStringCellValue();
if (!temp.equals(stringCellValue)) {
temp = stringCellValue;
mergeList.add(i - 1);
}
}
for (int i = 0; i < mergeList.size(); i++) {
Integer merge = mergeList.get(i);
if (0 == i) {
if (merge != 4) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, merge));
}
} else {
if (merge != mergeList.get(i - 1) + 1) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, mergeList.get(i - 1) + 1, merge));
}
}
}
row.removeCell(tempCell);
}
if (x == 2) {
SXSSFRow row = sheet.getRow(1);
int cellNum = row.getLastCellNum();
SXSSFCell tempCell = row.createCell(cellNum);
tempCell.setCellValue("%1");
String temp = row.getCell(4).getStringCellValue();
List<Integer> mergeList = new ArrayList<>();
for (int i = 4; i < cellNum + 1; i++) {
String stringCellValue = row.getCell(i).getStringCellValue();
if (!temp.equals(stringCellValue)) {
temp = stringCellValue;
mergeList.add(i - 1);
}
}
for (int i = 0; i < mergeList.size(); i++) {
Integer merge = mergeList.get(i);
if (0 == i) {
if (merge != 4) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, merge));
}
} else {
if (merge != mergeList.get(i - 1) + 1) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, mergeList.get(i - 1) + 1, merge));
}
}
}
row.removeCell(tempCell);
}
int columnNum = rowName.length;
SXSSFRow rowRowName = sheet.createRow(x);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
// 创建列头对应个数的单元格
SXSSFCell cellRowName = rowRowName.createCell(n);
// 设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
// 设置列头单元格的值
cellRowName.setCellValue(text);
// 设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
if (0 != x) {
SXSSFCell cell = sheet.getRow(0).createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString("基本信息");
cell.setCellValue(text);
cell.setCellStyle(columnTopStyle);
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 3);
sheet.addMergedRegion(region);
}
// 将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
// 遍历每个对象
Object[] obj = dataList.get(i);
// 创建所需的行数
SXSSFRow row = sheet.createRow(i + 3);
for (int j = 0; j < obj.length; j++) {
// 设置单元格的数据类型
SXSSFCell cell = null;
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
// 设置单元格的值
cell.setCellValue(obj[j].toString());
} else {
cell.setCellValue("无");
}
// 设置单元格样式
cell.setCellStyle(style);
}
}
// 让列宽随着导出的列长自动适应
/* for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue()
.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}*/
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 列头单元格样式
*
* @param workbook
* @return
*/
private static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 列数据信息单元格样式
*
* @param workbook
* @return
*/
private static CellStyle getStyle(SXSSFWorkbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(true);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 关闭输出流
*
* @param os
*/
private static void close(OutputStream os) {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步