网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
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<String> 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 <T>
* @throws IOException
*/
public static <T> void writeExcel(List<T> objects, String filePath, List<String> 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<String>> objects, String filePath, boolean append,List<Integer> 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<String> 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 <T> 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<String>> newData,String filePath, boolean append,List<Integer> 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<String> 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);
// 复制其他样式属性,如字体、边框等
}*/
}
}
}
}
}
以上工具类里提供的都是一次性导出的方法。那如果说需要多次导出咋整呢。说实话,支付系统里头最头痛的就是导出大数据量,性能瓶颈第一卡在数据库资源,第二卡在poi性能。
做了一系列优化后,**还是觉得分页最靠谱。分页查询分页导出,于是就会有这种场景,第一次写完的excel,第二次读取后再追加写入。但我们又知道SXSSFWorkbook类是专门给写定制的,压根就没法直接读取原excel文件。只能由XSSFWorkbook类读取。所以TM实现起来真难啊。只能先读,后复制再写。给两个方法自行体会。**
**第一个XSSFWorkbook类追加写入**,**只是参考模式,具体参数自己玩**
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<String> 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;
}
**第二个SXSSFWorkbook类追加写入**
![img](https://img-blog.csdnimg.cn/img_convert/5b11b68b60284f68779088e5ba39d7d9.png)
![img](https://img-blog.csdnimg.cn/img_convert/50725a84950bbbe725298c9aa194c82f.png)
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**
}
第二个SXSSFWorkbook类追加写入
[外链图片转存中…(img-7KC1Mn0t-1715643211010)]
[外链图片转存中…(img-7TBcFbvX-1715643211010)]
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!