import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import com.google.common.collect.Lists;
public class ExeclExportUtils {
public static void execute(String[] cellTitle, List<String[]> cellData, File filePath) {
SXSSFWorkbook workBook = null;
FileOutputStream outStream = null;
try {
// 声明一个工作薄
workBook = new SXSSFWorkbook();
// 标题样式
CellStyle cellTitleStyle = workBook.createCellStyle();
// 设置水平对齐的样式为居中对齐;
cellTitleStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
cellTitleStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
// 设置背景颜色
cellTitleStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
// solid 填充 foreground 前景色
cellTitleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 边框
cellTitleStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellTitleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellTitleStyle.setBorderBottom(IndexedColors.BLACK.getIndex());
// 标题字体样式
Font cellTitleFont = workBook.createFont();
cellTitleFont.setColor(HSSFColor.WHITE.index); // 将字体设置为“白色”
cellTitleFont.setFontHeightInPoints((short) 12); // 将字体大小设置为12px
cellTitleFont.setFontName("华文细黑"); // 将“华文细黑”字体应用到当前单元格上
cellTitleStyle.setFont(cellTitleFont);
// 数据 - 根据数据大小拆分多页
int sheetNum = 50000;
List<List<String[]>> listData = Lists.partition(cellData, sheetNum);
for (int i = 0; i < listData.size(); i++) {
Sheet sheetDetail = workBook.createSheet();
workBook.setSheetName(i, ""+i);
// 创建表格标题行 第一行
Row titleRow = sheetDetail.createRow(0);
titleRow.setHeightInPoints(20);
for (int j = 0; j < cellTitle.length; j++) {
Cell cell = titleRow.createCell(j);
cell.setCellStyle(cellTitleStyle);
cell.setCellValue(cellTitle[j]);
}
// 插入导出的数据
List<String[]> detailDataList = listData.get(i);
for (int j = 0; j < detailDataList.size(); j++) {
Row row = sheetDetail.createRow(j + 1);
for (int k = 0; k < cellTitle.length; k++) {
String valStr = detailDataList.get(j)[k];
if (valStr.trim().length() == 0 || "null".equals(valStr)) {
valStr = "";
}
row.createCell(k).setCellValue(valStr);
}
}
// 调整宽度
for (int j = 0; j < cellTitle.length; j++) {
sheetDetail.setColumnWidth(j, cellTitle[j].length() * 1000);
}
// 固定第一行标题
sheetDetail.createFreezePane(1, 1, 1, 1);
}
// 导出
System.out.println("生成文件路径:" + filePath.getAbsolutePath());
// 文件输出流
outStream = new FileOutputStream(filePath);
workBook.write(outStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != outStream) {
try {
outStream.flush();
outStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != workBook) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>26.0-jre</version>
</dependency>