性能爆炸！SXSSFWorkbook原文件上追加写入&amp；分页导出

for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
T obj = clazz.getDeclaredConstructor().newInstance();

for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String fieldName = sheet.getRow(0).getCell(j).getStringCellValue();
Field field = clazz.getDeclaredField(fieldName);
field.setAccessible(true);
//跳过静态属性
if (Modifier.isStatic(field.getModifiers())){
continue;
}
//单元格为空的情况
if (cell == null){
continue;
}
if (cell.getCellType() == CellType.NUMERIC) {
double numericValue = cell.getNumericCellValue();
if (field.getType() == BigDecimal.class){
field.set(obj, BigDecimal.valueOf(numericValue));
}else if (field.getType() == Long.class){
field.set(obj, (long) numericValue);
}else if (field.getType() == Integer.class){
field.set(obj, (int) numericValue);
}else {
field.set(obj, numericValue); // 设置为Double类型
}
} else if (cell.getCellType() == CellType.STRING) {
if (field.getType() == Date.class){
field.set(obj, DateUtils.strToDate(cell.getStringCellValue(),“yyyy-MM-dd HH:mm:ss”));
}else if (field.getType() == String.class){
field.set(obj, cell.getStringCellValue());
}
}
}
objects.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return objects;
}

/**

• 创建表头 MAP 格式为 filed，表头名
• @param objects
• @param filePath
• @param titleMap
• @param append
• @param
• @throws IOException
  */
  public static void writeExcel(List objects, String filePath, Map<String, String> titleMap, boolean append) throws IOException {
  // 确保工作簿以指定模式打开
  Workbook workbook;
  if (append) {
  try (FileInputStream fis = new FileInputStream(filePath)) {
  workbook = WorkbookFactory.create(fis);
  }
  } else {
  workbook = new XSSFWorkbook();
  }

// 获取或创建Sheet
Sheet sheet = workbook.getSheet(“Sheet1”);
if (sheet == null) {
sheet = workbook.createSheet(“Sheet1”);
}

// 创建表头
List titles = new ArrayList<>();
titleMap.forEach((k, v) -> titles.add(v));
if (!append) {
Row headerRow = sheet.createRow(0);
for (int i = 0; i < titles.size(); i++) {
headerRow.createCell(i).setCellValue(titles.get(i));
}
}

// 写入数据
int lastRowNum = sheet.getPhysicalNumberOfRows();
int currentRow = lastRowNum == 0 ? 0 : lastRowNum + 1;
for (T object : objects) {
Row row = sheet.createRow(currentRow++);
for (String fieldName : titleMap.keySet()) {
Cell cell = row.createCell(row.getLastCellNum());
cell.setCellValue((String) getCellValue(object, fieldName));
}
}

// 写入文件
try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
workbook.write(outputStream);
}
}

/**

• 只需要列名–不创建表头
• @param objects
• @param filePath
• @param titleList
• @param append
• @param
• @throws IOException
  */
  public static void writeExcel(List objects, String filePath, List titleList, boolean append) throws IOException {
  // 确保工作簿以指定模式打开
  Workbook workbook;
  if (append) {
  try (FileInputStream fis = new FileInputStream(filePath)) {
  workbook = WorkbookFactory.create(fis);
  }
  } else {
  workbook = new XSSFWorkbook();
  }

// 获取或创建Sheet
Sheet sheet = workbook.getSheet(“Sheet1”);
if (sheet == null) {
sheet = workbook.createSheet(“Sheet1”);
}

// 写入数据
int lastRowNum = sheet.getPhysicalNumberOfRows();
int currentRow = lastRowNum == 0 ? 0 : lastRowNum + 1;

for (T object : objects) {
Row row = sheet.createRow(currentRow++);
for (String fieldName : titleList) {
Cell cell = row.createCell(row.getLastCellNum());
cell.setCellValue((String) getCellValue(object, fieldName));
}
}

// 写入文件
try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
workbook.write(outputStream);
}
}

public static void writeExcel(List<List> objects, String filePath, boolean append,List rigthList) throws IOException {
// 确保工作簿以指定模式打开
Workbook workbook;
if (append) {
try (FileInputStream fis = new FileInputStream(filePath)) {
workbook = WorkbookFactory.create(fis);
}
} else {
workbook = new XSSFWorkbook();
}

// 获取或创建Sheet
Sheet sheet = workbook.getSheet(“Sheet1”);
if (sheet == null) {
sheet = workbook.createSheet(“Sheet1”);
}

// 写入数据
int lastRowNum = sheet.getPhysicalNumberOfRows();
int currentRow = lastRowNum == 0 ? 0 : lastRowNum;

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cellStyle.setWrapText(true);

for (List objectList : objects) {
Row row = sheet.createRow(currentRow++);
for (int i =0;i<objectList.size();i++) {
Cell cell = row.createCell(i);
//右对齐
if (rigthList.contains(i)){

cell.setCellStyle(cellStyle);
}
if (objectList.get(i) == null) {
cell.setCellValue(“”);
} else {
cell.setCellValue(objectList.get(i));
}
}
}

// 写入文件
try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
workbook.write(outputStream);
} finally {
// 确保工作簿被关闭以释放资源
if (workbook != null) {
workbook.close();
}
}
}

private static Object getCellValue(T object, String columnName) {
// 这里业务对象有对应属性，且属性名与columnNam匹配
Field field = getDeclaredField(object.getClass(), columnName);
try {
Object value = field.get(object);
if (value instanceof Number) {
return ((Number) value).doubleValue();
} else if (value instanceof String) {
return value;
} else if (value instanceof Date){
return DateUtils.dateToStr((Date)value,“yyyy-MM-dd HH:mm:ss”);
}
return “”;
} catch (IllegalAccessException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}

private static Field getDeclaredField(Class<?> clazz, String fieldName) {
try {
return clazz.getDeclaredField(fieldName);
} catch (NoSuchFieldException e) {
// 递归查找父类
Field parentField = getDeclaredField(clazz.getSuperclass(), fieldName);
if (parentField != null) {
return parentField;
}
}
return null;
}

public static void writeExcelBySXSSF(List<List> newData,String filePath, boolean append,List rigthList) throws IOException {
if (append) {
// 打开现有的Excel文件
FileInputStream fis = new FileInputStream(filePath);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);

// 创建SXSSFWorkbook实例
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1024);

// 获取原有工作簿的工作表
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); // 假设数据在第一个工作表
SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet(xssfSheet.getSheetName());

// 复制原有工作表的数据和样式到SXSSFWorkbook
copySheet(xssfSheet, sxssfSheet);

CellStyle cellStyle = sxssfWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cellStyle.setWrapText(true);

// 追加新数据到SXSSFWorkbook的工作表
int rowIndex = sxssfSheet.getLastRowNum() + 1;
for (List row : newData) {
Row sxssfRow = sxssfSheet.createRow(rowIndex++);
for (int i = 0; i < row.size(); i++) {
Cell cell = sxssfRow.createCell(i);
cell.setCellValue(row.get(i));
if (rigthList != null && rigthList.contains(i)){
cell.setCellStyle(cellStyle);
}
}
}

// 将SXSSFWorkbook写入文件，覆盖原有文件
FileOutputStream os = new FileOutputStream(filePath);
sxssfWorkbook.write(os);
os.close();

// 关闭工作簿和文件输入流
sxssfWorkbook.close();
xssfWorkbook.close();
fis.close();
} else {
// 如果不追加，创建新的SXSSFWorkbook并写入数据的逻辑
}
}

private static void copySheet(XSSFSheet xssfSheet, SXSSFSheet sxssfSheet) {
for (int rowIndex = 0; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++) {
XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
Row sxssfRow = sxssfSheet.createRow(rowIndex);
for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum(); cellIndex++) {
Cell xssfCell = xssfRow.getCell(cellIndex);
Cell sxssfCell = sxssfRow.createCell(cellIndex);
if (xssfCell != null) {
// 复制单元格的值
sxssfCell.setCellValue(xssfCell.toString());
// 复制单元格的样式
/*CellStyle xssfCellStyle = xssfCell.getCellStyle();
if (xssfCellStyle != null) {
sxssfCell.setCellStyle(xssfCellStyle);
// 复制其他样式属性，如字体、边框等

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数大数据工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上大数据开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加VX：vip204888 （备注大数据获取）

g-kfmCzmFu-1712579979500)]
[外链图片转存中…(img-FFoK7qcN-1712579979500)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上大数据开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加VX：vip204888 （备注大数据获取）
[外链图片转存中…(img-pMiVLoea-1712579979501)]