JAVA工具类

下划线转驼峰

    /*** 下划线转驼峰* 字符串 "USER_NAME" 会被转换为 "userName"* 字符串 "user_name" 会被转换为 "userName"* @param underscoreStr* @return*/public static String underlineToCamelCase(String underscoreStr) {if (underscoreStr == null) {return null;}// 先全部转小写underscoreStr = underscoreStr.toLowerCase();// 首字母小写StringBuilder camelCaseStr = new StringBuilder();boolean nextIsUpperCase = false;for (char underscoreChar : underscoreStr.toCharArray()) {if ('_' == underscoreChar) {nextIsUpperCase = true;} else if (nextIsUpperCase) {camelCaseStr.append(Character.toUpperCase(underscoreChar));nextIsUpperCase = false;} else {camelCaseStr.append(underscoreChar);}}return camelCaseStr.toString();}

POI 导出Excel工具类

package com.bairong.micro.modules.util;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;/*** Excel导出工具类** @Author:Y.P* @CreateDate:2024/9/19 15:47* @Version:v1.0*/
public class ExcelExportUtil {private static Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);public static void downLoadFile(List contextlist, List<String> titleNameList,OutputStream outputStream) throws Exception {// 创建excel文件SXSSFWorkbook wb = new SXSSFWorkbook();// 将数据写入Excel中buildSheet(wb, contextlist, titleNameList);// 将数据写出去try {wb.write(outputStream);// 刷新outputStream.flush();} catch (IOException e) {log.error("", e);} finally {// 关闭流outputStream.close();}}/*** 构建Excel** @param wb 工作簿, exportDataList 导出的数据, style excel样式* @return void* @auther Y.P* @create 2024/07/24 11:44* @throws:**/public static void buildSheet(SXSSFWorkbook wb, List exportDataList, List<String> titleNameList) throws IllegalAccessException {// 创建sheetSheet sheet = wb.createSheet("sheet1");sheet.setVerticallyCenter(true);Row row = sheet.createRow(0);// 创建加粗字体XSSFFont boldFont = (XSSFFont) wb.createFont();boldFont.setBold(true);//写表头XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();titleStyle.setFont(boldFont);// 设置边框titleStyle.setBorderTop(BorderStyle.THIN);titleStyle.setBorderBottom(BorderStyle.THIN);titleStyle.setBorderLeft(BorderStyle.THIN);titleStyle.setBorderRight(BorderStyle.THIN);// 设置单元格属性titleStyle.setFillPattern(FillPatternType.NO_FILL);titleStyle.setAlignment(HorizontalAlignment.CENTER);  //水平居中titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //垂直居中// 表头写入setTitle(sheet, row, titleStyle, titleNameList);//写表头XSSFCellStyle contextStyle = (XSSFCellStyle) wb.createCellStyle();// 设置边框contextStyle.setBorderTop(BorderStyle.THIN);contextStyle.setBorderBottom(BorderStyle.THIN);contextStyle.setBorderLeft(BorderStyle.THIN);contextStyle.setBorderRight(BorderStyle.THIN);// 设置单元格属性contextStyle.setFillPattern(FillPatternType.NO_FILL);contextStyle.setAlignment(HorizontalAlignment.LEFT);  //水平居中contextStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //垂直居中//写数据setData(sheet, contextStyle, exportDataList);}/*** 书写表头** @param row 表头行, style 样式* @return void* @auther Y.P* @create 2024/07/24 11:44**/public static void setTitle(Sheet sheet, Row row, XSSFCellStyle style, List<String> titleNameList) {for (int i = 0; i < titleNameList.size(); i++) {Cell cell = row.createCell(i);String titleName = titleNameList.get(i);cell.setCellValue(titleName);cell.setCellStyle(style);// 设置每一列的宽sheet.setColumnWidth(i, 30 * 256);}}/*** 将数据写入excel表格** @param sheet sheet 页, style 样式 , exportDataList 导出的数据* @return void* @auther Y.P* @create 2024/09/19 11:44**/public static void setData(Sheet sheet, XSSFCellStyle style, List exportDataList) throws IllegalAccessException {if (exportDataList != null && !exportDataList.isEmpty()) {// 扩展模式if (exportDataList.get(0) instanceof List) {for (int i = 0; i < exportDataList.size(); i++) {List vs = (List) exportDataList.get(i);// 数据都在集合里if (vs.size() > 0) {Row row = sheet.createRow(i + 1);for (int j = 0; j < vs.size(); j++) {Cell cell = row.createCell(j);cell.setCellStyle(style);String value = vs.get(j) == null ? "" : vs.get(j).toString();setCellValue(cell, value);}}}} else {for (int i = 0; i < exportDataList.size(); i++) {Class cla = exportDataList.get(i).getClass();// 获取实体的calssField[] fields = cla.getDeclaredFields();// 获取fieldsif (fields.length > 0) {Row row = sheet.createRow(i + 1);for (int j = 0; j < fields.length; j++) {Field field = fields[j];field.setAccessible(true);// 设置可以访问私有属性Cell cell = row.createCell(j);cell.setCellStyle(style);String value = field.get(exportDataList.get(i)) == null ? "" : field.get(exportDataList.get(i)).toString();setCellValue(cell, value);}}}}}}/*** excel数据MAP转成LIST** @param list*/public static List<List> mapToList(List<LinkedHashMap> list) {List<List> titleValueList = new ArrayList<>();// 根据表头提取数据for (int i = 0; i < list.size(); i++) {ArrayList<Object> obj = new ArrayList<>();LinkedHashMap paramMap = list.get(i);paramMap.forEach((k, v) -> {obj.add(v);});titleValueList.add(obj);}return titleValueList;}public static String getCurrentDate() {Date currentTime = new Date();SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd_HHmmssSSS");String dateString = formatter.format(currentTime);return dateString;}/*** Excel 设置单元格参数** @param cell* @param value* @throws IllegalAccessException*/public static void setCellValue(Cell cell, String value) {try {cell.setCellValue(value);} catch (IllegalArgumentException e) {cell.setCellValue("内容超长，无法导出");}}/*** 如果Object对象为NULL时替换为空字符串** @param obj* @return String* @date：2014-11-19 下午08:04:17* @author：Pansq*/public static String replaceNullByObj(Object obj) {if (obj == null) {return "";} else {return String.valueOf(obj);}}
}

POI 导入Excel工具类

package com.bairong.micro.modules.util;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;/*** Excel读取工具类** @Author:Y.P* @CreateDate:2024/9/19 15:47* @Version:v1.0*/
public class ExcelReaderUtil {public interface RowHandler {void handle(Row row);}public static void readExcelFile(String filePath,boolean skipFirstRow ,RowHandler rowHandler) {try (FileInputStream inputStream = new FileInputStream(filePath);Workbook workbook = new XSSFWorkbook(inputStream)) {Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表Iterator<Row> rowIterator = sheet.iterator(); // 获取行迭代器if (skipFirstRow){rowIterator.next(); // 跳过首行}while (rowIterator.hasNext()) {Row row = rowIterator.next();rowHandler.handle(row); // 处理每一行}} catch (IOException e) {e.printStackTrace();}}public static List<String> readExcelFirstRowToList(String filePath) {ArrayList<String> fistRowList = new ArrayList<>();try (FileInputStream inputStream = new FileInputStream(filePath);Workbook workbook = new XSSFWorkbook(inputStream)) {Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表Iterator<Row> rowIterator = sheet.iterator(); // 获取行迭代器Row firstRow = rowIterator.next();int lastCellNum = firstRow.getLastCellNum();for (int i = 0; i < lastCellNum; i++) {String stringCellValue = firstRow.getCell(i).getStringCellValue();fistRowList.add(stringCellValue);}} catch (IOException e) {e.printStackTrace();}return fistRowList;}}