java poi 讀取模版excel寫入資料並保持原本單元格樣式

浮笙芸芸發表於2024-12-09
JavaExcel
public Result<Object> testreport(@RequestParam Map<String, String> params,HttpServletResponse response) throws Exception{
        String filename="wenjian.xlsx";
        String inFilePath="C:\\Users\\xnn\\Desktop\\附件一：高登商業生活廣場用電安全分析報告.xlsx";

        Result info;
        info=appletService.getRiskReportEnterprise(params);
        Map<String,Object> data = (Map<String, Object>) info.getData();

        List<Map<String, Object>> rows=new ArrayList<>();
        InputStream in = new FileInputStream(inFilePath);
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);
        in.close();
        int rowNum = 1;//模板第一行是固定標題，從第二行開始插入資料
        sheet.setForceFormulaRecalculation(true);//強制執行excel中函式
        // 建立一個單元格，設定其內的資料格式為字串，並填充內容，其餘單元格類同
        for (Map<String, Object> row : rows) {
            // 獲取並設定該行每一單元格的資訊，該行單元格的索引從 0 開始
            int cellIndex = 1;//從第二個單元格設定值，因為第一個單元格是序號函式row()-1
            XSSFRow newRow = sheet.getRow(rowNum);
            if(newRow==null){
                newRow = sheet.createRow(rowNum);
            }
            XSSFColor color = new XSSFColor();
            color.setRGB(intToByteArray(getIntFromColor(68,84,106)));
            XSSFCellStyle style= wb.createCellStyle();
            style.setFillForegroundColor(color);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            XSSFCell c2 = newRow.getCell(cellIndex++);
            if (c2 != null) {
                XSSFCellStyle originalStyle = c2.getCellStyle();
                c2.setCellValue(row.get("appNumber")!=null?(String)row.get("appNumber"):null);
                c2.setCellStyle(originalStyle);
            } else {
                System.out.println("空！！！！！！！！！！！！！！！！！！！！！！");
                c2 = newRow.createCell(cellIndex++, CellType.STRING);
                XSSFCellStyle originalStyle = c2.getCellStyle();
                c2.setCellValue(row.get("appNumber")!=null?(String)row.get("appNumber"):null);
                c2.setCellStyle(originalStyle);
            }
            XSSFCell c3 = newRow.createCell(cellIndex++, CellType.STRING);
//            originalStyle = c3.getCellStyle();
            c3.setCellValue(row.get("receiptTitle")!=null?(String)row.get("receiptTitle"):null);
            c3.setCellStyle(style);
            rowNum++;
        }

        try {
            response.setContentType("application/vnd.ms-excel");
            filename = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(filename));
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.close();
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * rgb轉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轉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;
    }

遇到需求一開始是想著自己填rgb顏色，但是發現還要設定字型顏色什麼的比較麻煩，不如直接讀取原本的檔案的樣式，注意要用get獲取原本單元格再獲取樣式，create建立單元格的話，樣式是空的就獲取不到了。

相關文章