[Java]Spring/SpringBoot生成Excel文件并提供下载

2021-02-02 1759点热度 0人点赞 1条评论

在Spring或者SpringBoot程序里,生成Excel文件比较方便,Spring已经通过org.springframework.web.servlet.view.document.AbstractXlsxView提供了对Excel的支持,我们只需要实现它就可以。

首先引入apache poi包,修改pom.xml:
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>
新建ExcelView extends AbstractXlsxView
package com.terrynow.util;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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.util.CellRangeAddress;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @date 2021-2-2 11:10
 * @description
 */
public class ExcelView extends AbstractXlsxView {

    //这里的Key用在HashMap里的key,携带value数据,在Controller里作为Map<String, Object> model传给ModelAndView,
    //然后在下面的方法buildExcelDocument里取出来,组装成excel
    //所以可以根据自己的需要,自定义一些key,来自定义你的Excel
    public static final String KEY_EXCEL_FILE_NAME = "KEY_EXCEL_FILE_NAME";
    public static final String KEY_EXCEL_SHEET_NAME = "KEY_EXCEL_SHEET_NAME";
    public static final String KEY_EXCEL_TITLE = "KEY_EXCEL_FILE_TITLE";
    //表头,数据是一个String数组
    public static final String KEY_EXCEL_HEADERS = "KEY_EXCEL_HEADERS";
    //Excel的主体数据,value是一个List<String[]>
    public static final String KEY_EXCEL_DATA = "KEY_EXCEL_DATA";
    public static final String KEY_EXCEL_BORDER = "KEY_EXCEL_DATA_BORDER";

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                                      HttpServletRequest httpServletRequest, HttpServletResponse response) {
        String fileName = (String) model.get(KEY_EXCEL_FILE_NAME);
        String sheetName = (String) model.get(KEY_EXCEL_SHEET_NAME);
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = fileName;
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xlsx\"; charset=utf-8");

        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("黑体");//默认好像宋体,粗体不太明显?
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font);

        boolean border = model.get(KEY_EXCEL_BORDER) != null;
        if (border) {
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
        }

        CellStyle borderStyle = workbook.createCellStyle();
        if (border) {
            borderStyle.setBorderBottom(BorderStyle.THIN);
            borderStyle.setBorderLeft(BorderStyle.THIN);
            borderStyle.setBorderRight(BorderStyle.THIN);
            borderStyle.setBorderTop(BorderStyle.THIN);
        }

        String[] header = (String[]) model.get(KEY_EXCEL_HEADERS);
        List<String[]> data = (List<String[]>) model.get(KEY_EXCEL_DATA);

        // create excel xls sheet
        Sheet sheet = workbook.createSheet(sheetName);

        int rowNumber = 0;

        String title = (String) model.get(KEY_EXCEL_TITLE);
        if (!StringUtils.isEmpty(title)) {
            Font fontForTitle = workbook.createFont();
//            fontForTitle.setBold(true);
            fontForTitle.setFontHeightInPoints((short) 24);
            CellStyle cellStyleForTitle = workbook.createCellStyle();
            cellStyleForTitle.setAlignment(HorizontalAlignment.LEFT);
            cellStyleForTitle.setFont(fontForTitle);
            cellStyleForTitle.setAlignment(HorizontalAlignment.CENTER);

            Row titleRow = sheet.createRow(rowNumber++);
            Cell cell = titleRow.createCell(0);
            cell.setCellStyle(cellStyleForTitle);
            cell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));
        }

        // create header row
        Row headerRow = sheet.createRow(rowNumber++);
        for (int i = 0; i < header.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(header[i]);
        }

        // Create data cells
        for (String[] datum : data) {
            Row courseRow = sheet.createRow(rowNumber++);
            for (int i = 0; i < datum.length; i++) {
                Cell cell = courseRow.createCell(i);
                cell.setCellStyle(borderStyle);
                cell.setCellValue(datum[i]);
            }
        }

        // 如果有title的,用row0做调整,否则row1做调整
        autoSizeColumns(workbook, 0, StringUtils.isEmpty(title) ? 0 : 1);

        // 每一列都是文本格式
        // see https://stackoverflow.com/questions/6510012/apache-poi-for-excel-setting-the-cell-type-to-text-for-an-entire-column
        DataFormat fmt = workbook.createDataFormat();
        CellStyle textStyle = workbook.createCellStyle();
        textStyle.setDataFormat(fmt.getFormat("@"));
        for (int i = 0; i < header.length; i++) {
            sheet.setDefaultColumnStyle(i, textStyle);
        }
    }

    //用这个实现每一列宽度自动调整
    private void autoSizeColumns(Workbook workbook, int targetSheetNumber, int targetRow) {
//        int numberOfSheets = workbook.getNumberOfSheets();
//        for (int i = 0; i < numberOfSheets; i++) {
//            Sheet sheet = workbook.getSheetAt(i);
        Sheet sheet = workbook.getSheetAt(targetSheetNumber);
        if (sheet.getPhysicalNumberOfRows() > targetRow) {
            Row row = sheet.getRow(targetRow);
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                sheet.autoSizeColumn(columnIndex);
            }
        }
//        }
    }
}
最后在Controller里
@Controller
public class BomController {

    @RequestMapping("/xxx.xlsx")
        public ModelAndView listPipesExcel() {
            HashMap<String, Object> model = new HashMap<>();

            map.put(ExcelView.KEY_EXCEL_FILE_NAME, "文件名称");
            map.put(ExcelView.KEY_EXCEL_SHEET_NAME, "工作表名称");

            String[] header = new String[]{"编号", "名称", "备注"};
            map.put(ExcelView.KEY_EXCEL_HEADERS, header);

            List<String[]> data = new ArrayList();
            data2.add(new String[]{"1","名称1","备注1"});
            data2.add(new String[]{"2","名称2","备注2"});
            map.put(ExcelView.KEY_EXCEL_DATA, data);

            ExcelView viewExcel = new ExcelView();
            return new ModelAndView(viewExcel, model);
    }

}

如何生成普通 Excel 文件,请参考:https://blog.terrynow.com/2021/02/03/generate-excel-file-using-apache-poi/

如何合并单元格,详见 https://blog.terrynow.com/2021/02/11/apache-poi-merge-cells/

admin

这个人很懒,什么都没留下

文章评论

  • unussyKig

    Cialis Cialis Scaduto Da 2 Anni Ultimately renal failure is a complication of proliferative glomerulonephritis. v m sec with cm area v m sec Ulrvrf Epigenetic therapeutics that have been developed with the purpose to exert epigenetic effects Substance Chemical class Target Epigenetic consequences Indications Refs Epigenetic therapeutics Suberoylanilide hydroxamic acid SAHA vorinostat Hydroxamic acids HDACs Histone acetylation Advanced cutaneous T cell lymphoma CTCL FDAapproved PXD belinostat Hepatocellular carcinoma Phase III ITF givinostat Hematological malignancies Phase II Systemiconset juvenile idiopathic arthritis SOJIA Phase I Depsipeptide Romidepsin Cyclic peptides Advanced cutaneous T cell lymphoma CTCL and peripheral T cell lymphoma FDAapproved MS entinostat Benzamides Solid tumors Phase III Azacytidine AzaCR Vidaza Nucleoside analogs DNMTs DNA hypomethylation Myeloid malignancies FDAapproved Aza deoxycytidine AzaCdR Decitabine Epigenetic effects of drugs with structural similarities to epigenetic factors are highly likely as shown for valproic acid which besides exerting channel blocking actions is also a HDAC inhibitor Amygez buying cialis generic Degnaf

    2022-04-15
  • 您需要 登录 之后才可以评论