天天看點

poi實作導出excel,并且設定背景顔色

先準備poi需要的坐标:

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.13</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.13</version>
            </dependency>
           

以下是核心代碼:(每一行注釋都很清晰哦~)

@Controller
@RequestMapping(value = "/report")
public class ReportController {

    /**
     * 導出excel報表
     */
    @GetMapping(value = "/excel")
    public void excel( HttpServletResponse response) throws Exception {
        List<TestDataVo> list = datalist();
        HSSFWorkbook workbook = null ;
        BufferedOutputStream bufferedOutPut = null;
        try {
            workbook = new HSSFWorkbook();
            // 建立頁
            HSSFSheet sheet = workbook.createSheet("Sheet1");
            //設定列寬
            sheet.setColumnWidth(0, 256*35);
            sheet.setColumnWidth(1, 256*35);
            sheet.setColumnWidth(2, 256*35);
            sheet.setColumnWidth(3, 256*35);
            sheet.setColumnWidth(4, 256*35);

            // 建立行
            HSSFRow firstrow = sheet.createRow(0);
            // 建立列
            HSSFCell cell0 = firstrow.createCell(0);
            cell0.setCellStyle(getColumnTopStyle(workbook));
            cell0.setCellValue("時間");
            // 建立列
            HSSFCell cell1 = firstrow.createCell(1);
            cell1.setCellStyle(getColumnTopStyle(workbook));
            cell1.setCellValue("姓名");
            // 建立列
            HSSFCell cell2 = firstrow.createCell(2);
            cell2.setCellStyle(getColumnTopStyle(workbook));
            cell2.setCellValue("單号");
            // 建立列
            HSSFCell cell3 = firstrow.createCell(3);
            cell3.setCellStyle(getColumnTopStyle(workbook));
            cell3.setCellValue("位址");
            // 建立列
            HSSFCell cell4 = firstrow.createCell(4);
            cell4.setCellStyle(getColumnTopStyle(workbook));
            cell4.setCellValue("用途");

            for (TestDataVo testDataVo : list) {
                HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
                //設定單元格的值,并且設定樣式
                HSSFCell cell00 = row.createCell(0);
                cell00.setCellStyle(getStyle(workbook));
                cell00.setCellValue(testDataVo.getDate());
                //設定單元格的值,并且設定樣式
                HSSFCell cell01 = row.createCell(1);
                cell01.setCellStyle(getStyle(workbook));
                cell01.setCellValue(testDataVo.getName());
                //設定單元格的值,并且設定樣式
                HSSFCell cell02 = row.createCell(2);
                cell02.setCellStyle(getStyle(workbook));
                cell02.setCellValue(testDataVo.getNumbers());
                //設定單元格的值,并且設定樣式
                HSSFCell cell03 = row.createCell(3);
                cell03.setCellStyle(getStyle(workbook));
                cell03.setCellValue(testDataVo.getAddress());
                //設定單元格的值,并且設定樣式
                HSSFCell cell04 = row.createCell(4);
                cell04.setCellStyle(getStyle(workbook));
                cell04.setCellValue(testDataVo.getPurpose());
            }

            String filename = "導出資料.xls";
            response.setHeader("Content-Disposition",
                    "attachment;filename*=UTF-8''" + URLEncoder.encode(filename, "UTF-8"));
            OutputStream outputStream = response.getOutputStream();
            getColumnTopStyle(workbook);
            bufferedOutPut = new BufferedOutputStream(outputStream);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
        } finally {
            if (bufferedOutPut != null)
                bufferedOutPut.close();
            if(workbook != null)
                workbook.close();
        }
    }


           

下面是給excel設定表格+背景顔色

/*
     * 列資料資訊單元格樣式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 設定字型
        HSSFFont font = workbook.createFont();
        //設定字型大小
        font.setFontHeightInPoints((short)12);
        //字型加粗
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //設定字型名字
        font.setFontName("宋體");
        //設定樣式;
        HSSFCellStyle style = workbook.createCellStyle();
        //設定背景顔色;
        style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
        //solid 填充  foreground  前景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //設定底邊框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //設定底邊框顔色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //設定左邊框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //設定左邊框顔色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //設定右邊框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //設定右邊框顔色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //設定頂邊框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //設定頂邊框顔色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在樣式用應用設定的字型;
        style.setFont(font);
        //設定自動換行;
        style.setWrapText(false);
        //設定水準對齊的樣式為居中對齊;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //設定垂直對齊的樣式為居中對齊;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    /*
     * 列頭單元格樣式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 設定字型
        HSSFFont font = workbook.createFont();
        //設定字型大小
        font.setFontHeightInPoints((short)14);
        //字型加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //設定字型名字
        font.setFontName("黑體");
        //設定樣式;
        HSSFCellStyle style = workbook.createCellStyle();
        //設定背景顔色;
        style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
        //solid 填充  foreground  前景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //設定底邊框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //設定底邊框顔色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //設定左邊框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //設定左邊框顔色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //設定右邊框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //設定右邊框顔色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //設定頂邊框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //設定頂邊框顔色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在樣式用應用設定的字型;
        style.setFont(font);
        //設定自動換行;
        style.setWrapText(false);
        //設定水準對齊的樣式為居中對齊;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //設定垂直對齊的樣式為居中對齊;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }
}

           

以下就是效果圖,大家可以根據自己喜歡的顔色進行調整。

poi實作導出excel,并且設定背景顔色
poi實作導出excel,并且設定背景顔色

如果大家不知道有哪些顔色

請參考:https://blog.csdn.net/qq_41234832/article/details/103061884

如果此文章對您有幫助,拜托留個贊再走哦~

繼續閱讀