网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
${ poi.version }
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
${ poi.version }
org.apache.commons
commons-compress
${commons.compress.version}
工具类
import dbox.tbox.lang.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Stream;
/**
-
@Author ping
-
@Date 2023/12/4 17:04
-
excel工具类 用于service服务的导出,不使用export模式
*/
public class ExcelUtil {
// 读取 Excel 文件并将其映射为具体的 Java 对象
public static List readExcel(String filePath, Class clazz) throws Exception {
List objects = new ArrayList<>();
try (FileInputStream fis = new FileInputStream(filePath);
Workbook workbook = new XSSFWorkbook(fis)) {Sheet sheet = workbook.getSheetAt(0); // 选择工作表 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<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;
}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<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并写入数据的逻辑
-
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
加,创建新的SXSSFWorkbook并写入数据的逻辑
[外链图片转存中…(img-8dPcu7XL-1715660060888)]
[外链图片转存中…(img-VxKaBULs-1715660060888)]
[外链图片转存中…(img-lXiRHMol-1715660060888)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新