快捷導(dǎo)航

Java編寫通用的導(dǎo)出任何對(duì)象列表數(shù)據(jù)到excel的工具類

更新時(shí)間：2024年12月26日 09:39:00 作者：B1nnnn丶

在工作中經(jīng)常會(huì)遇到列表數(shù)據(jù)的導(dǎo)出,每次需要的時(shí)候都要去開發(fā)一次,且數(shù)據(jù)不斷在變化,所以本文將利用Java編寫一個(gè)工具類可以導(dǎo)出任何對(duì)象列表數(shù)據(jù)到excel,希望對(duì)大家有所幫助

在工作中經(jīng)常會(huì)遇到列表數(shù)據(jù)的導(dǎo)出，每次需要的時(shí)候都要去開發(fā)一次，且數(shù)據(jù)不斷在變化，于是就有了下述的工具類，可傳入各種實(shí)體對(duì)象的List，最終以指定格式導(dǎo)出excel，廢話不多說(shuō)，上代碼~

1.控制層代碼

    @PostMapping("/test")
    public void test(HttpServletResponse response) throws Exception {
        
        //查詢?nèi)我鈹?shù)據(jù)列表
        List<ErrorLog> list = errorLogService.selectListByInfo();
        
        //導(dǎo)出操作
        CommonExcelUtils.exportDynamicsData(response, list, "日志", "日志數(shù)據(jù)");
    }

此處的list可以是任意數(shù)據(jù)，也可以是任意SQL組裝的list數(shù)據(jù)，標(biāo)題會(huì)以sql的別名為準(zhǔn).

2.CommonExcelUtils工具類

    /**
     * 動(dòng)態(tài)列表導(dǎo)出
     * @param response
     * @param list        數(shù)據(jù)list
     * @param sheetName    頁(yè)簽名稱，也是總標(biāo)題的名稱
     * @param fileName    導(dǎo)出文件名
     */
    @SuppressWarnings("all")
    public static String exportDynamicsData(HttpServletResponse response,
            List list, String sheetName, String fileName) throws IOException {
        
        //將list數(shù)據(jù)轉(zhuǎn)成指定類型
        List<LinkedHashMap<String, Object>> data = CommonBeanUtils.convertListToMap(list);
        
        List<List> rows = new ArrayList<>();//excel導(dǎo)出整體數(shù)據(jù)
        List<String> titles = new ArrayList<>();//excel導(dǎo)出標(biāo)題（首行）
        List<String> title = new ArrayList<>();
        title.add(sheetName);
        rows.add(title);
        
        //組裝標(biāo)題
         LinkedHashMap<String,Object> m = (LinkedHashMap<String,Object>) data.get(0);
         Set<String> keySet = m.keySet();
          for (String t : keySet) {
              titles.add(t);
        }
        rows.add(titles);
        
        //組裝數(shù)據(jù)
        for (LinkedHashMap<String,Object> info : data) {
            List d = new ArrayList<>();
            Set<Entry<String, Object>> entrySet = info.entrySet();
            for (Entry<String, Object> da : entrySet) {
                d.add(da.getValue());
            }
            rows.add(d);
        }
        fileName = fileName +"-"+ DateUtils.parseDateToStr("yyMMdd", new Date()) +".xlsx";//導(dǎo)出文件名稱
        
        //聲明一個(gè)工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
          XSSFSheet sheet = workbook.createSheet(sheetName);
          sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()-1));//合并第一行的單元格，作標(biāo)題
          sheet.setDefaultColumnWidth(14);        //設(shè)置表格列寬度
          
          //導(dǎo)出操作
          ExcelUtil.exportExcel(response, rows, workbook, sheet, fileName);
          
          return fileName;
    }

3.CommonBeanUtils工具類

    /**
     * List轉(zhuǎn)Map
     * @param <T>
     * @param list
     * @return
     */
    public static <T> List<LinkedHashMap<String, Object>> convertListToMap(List<T> list) {
        return list.stream()
            .map(CommonBeanUtils::objectToMap)
            .collect(Collectors.toList());
    }
 
    /**
     * object 轉(zhuǎn) Map
     * @param <T>
     * @param object
     * @return
     */
    private static <T> LinkedHashMap<String, Object> objectToMap(T object) {
        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        for (Field field : object.getClass().getDeclaredFields()) {
            field.setAccessible(true);
            try {
                map.put(field.getName(), field.get(object));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

4.ExcelUtil工具類

import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
public class ExcelUtil<T>
{
 
    /**
     * 自定義導(dǎo)出
     * @param response
     * @param excelData
     * @param sheetName
     * @param fileName
     * @param columnWidth
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response,List<List> excelData,XSSFWorkbook workbook, XSSFSheet sheet,String fileName) throws IOException {                
        //設(shè)置單元格字體
        XSSFFont fontName = workbook.createFont();
        fontName.setFontName("宋體");
        //寫入List<List<String>>中的數(shù)據(jù)
        int rowIndex = 0;
        int rowFlag = -2;
        XSSFCellStyle createTitleCellStyle = createTitleCellStyle(workbook);
        XSSFCellStyle createTableCellStyle = createTableCellStyle(workbook);
        XSSFCellStyle rightRowCellStyle = createRightRowCellStyle(workbook);
        XSSFCellStyle leftRowCellStyle = createLeftRowCellStyle(workbook);
       
        for(List data : excelData){
            if(rowFlag == 8) rowFlag = 0;
            rowFlag++;
            //創(chuàng)建一個(gè)row行，然后自增1
            XSSFRow row = sheet.createRow(rowIndex++);
            if(rowIndex==1) row.setHeight((short)(20*30));
            
            //遍歷添加本行數(shù)據(jù)
            for (int i = 0; i < data.size(); i++) {
                //創(chuàng)建一個(gè)單元格
                XSSFCell cell = row.createCell(i);
                //value單元格值
                Object value = data.get(i);
                
                //設(shè)置第一個(gè)行標(biāo)題的樣式
                if(i==0 && rowIndex==1) {
                       cell.setCellStyle(createTitleCellStyle);
                   }
                //設(shè)置表頭樣式
                if(rowIndex==2) {
                    cell.setCellStyle(createTableCellStyle);
                }
                if(rowIndex>2) {
                    //如果是數(shù)字類型，則字體向右對(duì)齊
                    if(value instanceof BigDecimal || value instanceof Integer) {
                        row.getCell(i).setCellStyle(rightRowCellStyle);
                    }else {
                        row.getCell(i).setCellStyle(leftRowCellStyle);
                    }
                }
                //將內(nèi)容對(duì)象的文字內(nèi)容寫入到單元格中（單獨(dú)處理數(shù)值類型）
                if(value instanceof BigDecimal) {
                    BigDecimal v = (BigDecimal)value;
                    cell.setCellValue(v.doubleValue());
                }else {
                    cell.setCellValue(String.valueOf(value));
                }
            }
        }
        //準(zhǔn)備將Excel的輸出流通過(guò)response輸出到頁(yè)面下載
        //八進(jìn)制輸出流
        response.setContentType("application/octet-stream");
        //設(shè)置導(dǎo)出Excel的名稱
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        //刷新緩沖
        response.flushBuffer();
        //workbook將Excel寫入到response的輸出流中，供頁(yè)面下載該Excel文件
        workbook.write(response.getOutputStream());
 
        //關(guān)閉workbook
        workbook.close();
    }
    
    /**
     * 設(shè)置標(biāo)題單元樣式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle  cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 20);
        font.setFontName(HSSFFont.FONT_ARIAL);// 設(shè)置標(biāo)題字體
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 設(shè)置列標(biāo)題樣式
        
        XSSFColor color = new XSSFColor();
        //根據(jù)你需要的rgb值獲取byte數(shù)組
        color.setRGB(intToByteArray(getIntFromColor(255,231,228)));
        //設(shè)置自定義背景顏色
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //設(shè)置字體水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //設(shè)置字體垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        //設(shè)置邊框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下邊框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左邊框
        cellStyle.setBorderTop(BorderStyle.THIN);//上邊框
        cellStyle.setBorderRight(BorderStyle.THIN);//右邊框
        
        return cellStyle;
    }
    
    /**
     * 設(shè)置表頭單元樣式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTableCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName(HSSFFont.FONT_ARIAL);// 設(shè)置標(biāo)題字體
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 設(shè)置列標(biāo)題樣式
        
        XSSFColor color = new XSSFColor();
        //根據(jù)你需要的rgb值獲取byte數(shù)組
        color.setRGB(intToByteArray(getIntFromColor(251,241,227)));
        //設(shè)置自定義背景顏色
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //設(shè)置字體水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        
        //設(shè)置邊框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下邊框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左邊框
        cellStyle.setBorderTop(BorderStyle.THIN);//上邊框
        cellStyle.setBorderRight(BorderStyle.THIN);//右邊框
        
        return cellStyle;
    }
    
    /**
     * 設(shè)置內(nèi)容單元樣式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createRowCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
 
        XSSFColor color = new XSSFColor();
        //根據(jù)你需要的rgb值獲取byte數(shù)組
        color.setRGB(intToByteArray(getIntFromColor(220,220,220)));
        //設(shè)置自定義背景顏色
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //設(shè)置邊框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下邊框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左邊框
        cellStyle.setBorderTop(BorderStyle.THIN);//上邊框
        cellStyle.setBorderRight(BorderStyle.THIN);//右邊框
        XSSFColor borderColor = new XSSFColor();
        //設(shè)置字體水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //設(shè)置字體垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //根據(jù)你需要的rgb值獲取byte數(shù)組
        borderColor.setRGB(intToByteArray(getIntFromColor(181,181,181)));
        cellStyle.setLeftBorderColor(borderColor);
        cellStyle.setRightBorderColor(borderColor);
        cellStyle.setBottomBorderColor(borderColor);
        cellStyle.setTopBorderColor(borderColor);
        
        return cellStyle;
    }
    
    /**
     * 設(shè)置內(nèi)容單元樣式(字體靠右對(duì)齊)
     * 數(shù)字類型
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createRightRowCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle  cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle = workbook.createCellStyle();
        
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        cellStyle.setFont(font);
        
        //設(shè)置字體水平居中
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        //設(shè)置字體垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
    
    /**
     * 設(shè)置內(nèi)容單元樣式(字體靠左對(duì)齊)
     * 文本類型
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createLeftRowCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //設(shè)置字體位置
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        //設(shè)置字體垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }
    
    /**
     * rgb轉(zhuǎn)int
     */
     private static int getIntFromColor(int Red, int Green, int Blue){
         Red = (Red << 16) & 0x00FF0000;
         Green = (Green << 8) & 0x0000FF00;
         Blue = Blue & 0x000000FF;
         return 0xFF000000 | Red | Green | Blue;
     }
     
     /**
      * int轉(zhuǎn)byte[]
      */
     public static byte[] intToByteArray(int i) {
         byte[] result = new byte[4];
         result[0] = (byte)((i >> 24) & 0xFF);
         result[1] = (byte)((i >> 16) & 0xFF);
         result[2] = (byte)((i >> 8) & 0xFF);
         result[3] = (byte)(i & 0xFF);
         return result;
     }
}

最終導(dǎo)出效果：