Spring 使用POI导出Excel并生成下拉选项

秒速五厘米 2022-05-23 11:50 501阅读 0赞

在开发中我们经常会用到生成excel，下面是spring项目前后端分离导出excel工具类，接口中直接引用即可。

首先引入poi-jar包：

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

ExcelPoiUtil：

import java.util.List;
    
    import org.apache.poi.hssf.usermodel.DVConstraint;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataValidation;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellRangeAddressList;
    
    public class ExcelPoiUtil {
    
    	/**
    	 * 
    	 * @param fileName 文件名称
    	 * @param sheetName sheet页名称
    	 * @param titleList 列名
    	 * @param titleCodeList 列名对应的code
    	 * @param parpamtsList 可选择参数列(需与列名一比一)
    	 */
    	public static HSSFWorkbook createExcel(String fileName,String sheetName,List<String> titleList,List<String> titleCodeList,List<List<String>> parpamtsList) {
    		 HSSFWorkbook wb = new HSSFWorkbook();
    		 
    		 HSSFSheet sheet = setSheetBaseInfoExcel(sheetName,36,30,wb);
    		 
    		 //设置前三行冻结
    	     sheet.createFreezePane(0,3,0,3);
    	     //此处是设置 第一行 合并单元格的个数(标题行)
    	     sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (titleList.size()-1)));
    	     
    	     // 产生表格标题行
    	    HSSFRow rowm = sheet.createRow(0);
    	    HSSFCell cellTiltle = rowm.createCell(0);
    	    HSSFCellStyle columnTopStyle = getColumnTopStyle(wb);//获取列头样式对象
    	    cellTiltle.setCellStyle(columnTopStyle);
    	    cellTiltle.setCellValue(sheetName);
    	    rowm.setHeightInPoints(25);
    	    
    	    //隐藏第二行，并填写code值
    	    HSSFRow row_titleCode = sheet.createRow(1);
    	    for(int i=0;i<titleCodeList.size();i++) {
    	    	HSSFCell cellTiltleCode = row_titleCode.createCell(i);
    	    	cellTiltleCode.setCellValue(titleCodeList.get(i));
    	    }
    	    row_titleCode.setZeroHeight(true);
    	    
    	    //设置第三行标题 标题列
    	    HSSFRow row_titleName = sheet.createRow(2);
    	    HSSFCellStyle style = getStyle(wb);
            for (int i=0;i<titleList.size();i++){
                HSSFCell cell_titleName = row_titleName.createCell(i);
                cell_titleName.setCellValue(titleList.get(i));
                cell_titleName.setCellStyle(style);
            }
            
            //设置默认下拉内容
            if(parpamtsList!=null && parpamtsList.size()>0) {
            	for(int i=0;i<parpamtsList.size();i++) {
            		if(parpamtsList.get(i)==null || parpamtsList.get(i).size()<1) {
            			continue;
            		}
            		//设置下拉控制的范围
            		CellRangeAddressList regions = new CellRangeAddressList(3, 999, i, i);
            		// 生成下拉框内容
            		String[] strings = new String[parpamtsList.get(i).size()];
            		parpamtsList.get(i).toArray(strings);
            		DVConstraint constraint = DVConstraint.createExplicitListConstraint(strings);
            		// 绑定下拉框和作用区域
            		HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
            		// 对sheet页生效
            		sheet.addValidationData(data_validation);
            	}
            }
            
            return wb;
    	    
    	}
    	
    	 /**设计excel定义列数、列宽和标头信息*/
        private static HSSFSheet setSheetBaseInfoExcel(String excelName,int columWith,int rowHight,HSSFWorkbook wb){
            HSSFSheet sheet = wb.createSheet(excelName);
            sheet.setDefaultColumnWidth(columWith);
            sheet.setDefaultRowHeightInPoints(rowHight);
    //        String columWiths[] =  columWithMsg.split(",");
    //        for (int i=0;i<columWiths.length;i++){
    //            sheet.setColumnWidth(i,Integer.valueOf(columWiths[i])*512);
    //        }
            return sheet;
        }
        
        /*
         * 列表首页的大title样式
         */
        private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
    
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short)16);
            //字体加粗
    //        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            HSSFCellStyle style = workbook.createCellStyle();
            //设置底边框;
    //        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);
            //设置背景颜色
    //        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            return style;
        }
        
        /*
         * 标题 列的样式
         */
        private static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short)12);
            //字体加粗
    //        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            HSSFCellStyle style = workbook.createCellStyle();
            //设置底边框;
    //        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);
            //设置背景颜色
    //        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
            return style;
        }
    }