Excel导出

设置ContentType & 中文文件名

设置Content-Type为：application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

给文件设置中文名称，为了防止中文乱码，需要给对名称进行url编码，并且添加Header:Access-Control-Expose-Headers、Content-disposition、download-filename。如下：

 /**
     * 下载文件名重新编码
     *
     * @param response 响应对象
     * @param realFileName 真实文件名
     */
    public static void setAttachmentResponseHeader(HttpServletResponse response, String realFileName) throws UnsupportedEncodingException
    {
        String percentEncodedFileName = percentEncode(realFileName);

        StringBuilder contentDispositionValue = new StringBuilder();
        contentDispositionValue.append("attachment; filename=")
                .append(percentEncodedFileName)
                .append(";")
                .append("filename*=")
                .append("utf-8''")
                .append(percentEncodedFileName);

        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue.toString());
        response.setHeader("download-filename", percentEncodedFileName);
    }

    /**
     * 百分号编码工具方法
     *
     * @param s 需要百分号编码的字符串
     * @return 百分号编码后的字符串
     */
    public static String percentEncode(String s) throws UnsupportedEncodingException
    {
        String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());
        return encode.replaceAll("\\+", "%20");
    }

完整代码如下：

public void exportMonitorDataType(HttpServletResponse response, @RequestBody CorrectDataTypeExportDTO correctDataTypeExportDTO) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String excelName = "待确认数据类型的点位";
        try {
            FileUtils.setAttachmentResponseHeader(response, excelName);
        } catch (UnsupportedEncodingException e) {
            LOGGER.error("exportCorrectDatatype set excel name occur exception:{}", ExceptionUtils.getStackTrace(e));
        }
        //
        Workbook workbook = new XSSFWorkbook();

        try {
            workbook = correctDataTypeService.exportMonitors(correctDataTypeExportDTO.dto2Bo(), workbook);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            LOGGER.error("exportCorrectDatatype occur exception:{}", ExceptionUtils.getStackTrace(e));
        } finally {
            IOUtils.closeQuietly(workbook);
        }
    }

XSSFWorkbook 导出Excel

导出excel，提炼出3个主要步骤：

• 创建Title的style
• 填充Title
• 创建Data的style

public interface ExportExcelService<T> {
    public CellStyle createTitleStyle(Workbook wb);

    public void fillTitleRowCell(Sheet sheet,CellStyle titleStyle,T sourceTitleData);

    public CellStyle createDataStyle(Workbook wb);
}

接口的实现如下：

/**
     * 创建excel的标题部分的格式
     *
     * @param wb
     * @return
     */
    @Override
    public CellStyle createTitleStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);

        // 这里，是否需要设置border，待定
        return style;
    }

    @Override
    public void fillTitleRowCell(Sheet sheet, CellStyle titleStyle, PlainTitleContent sourceTitleData) {
        Iterator<String> iterator = sourceTitleData.iteratorContent();
        int columnIndex = 0;
        Row row = sheet.createRow(0);
        row.setHeightInPoints(30);
        while (iterator.hasNext()) {
            String title = iterator.next();
            Cell cell = row.createCell(columnIndex);
            cell.setCellValue(title);
            cell.setCellStyle(titleStyle);
            columnIndex++;
        }
    }

    @Override
    public CellStyle createDataStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);

        DataFormat dataFormat = wb.createDataFormat();
        style.setDataFormat(dataFormat.getFormat("@"));

        return style;
    }

针对，如果id是一串很长数字，为了防止excel将其理解为科学计数，需要设置DataFormat 。其中@表示文本格式。

其余的格式，参考：https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html

代码中参考：

// org.apache.poi.ss.usermodel.BuiltinFormats

public final class BuiltinFormats {
	/**
	 * The first user-defined number format starts at 164.
	 */
	public static final int FIRST_USER_DEFINED_FORMAT_INDEX = 164;

	private final static String[] _formats = {
        "General",
        "0",
        "0.00",
        "#,##0",
        "#,##0.00",
        "\"$\"#,##0_);(\"$\"#,##0)",
        "\"$\"#,##0_);[Red](\"$\"#,##0)",
        "\"$\"#,##0.00_);(\"$\"#,##0.00)",
        "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)",
        "0%",
        "0.00%",
        "0.00E+00",
        "# ?/?",
        "# ??/??",
        "m/d/yy",
        "d-mmm-yy",
        "d-mmm",
        "mmm-yy",
        "h:mm AM/PM",
        "h:mm:ss AM/PM",
        "h:mm",
        "h:mm:ss",
        "m/d/yy h:mm",

        // 0x17 - 0x24 reserved for international and undocumented
        // TODO - one junit relies on these values which seems incorrect
        "reserved-0x17",
        "reserved-0x18",
        "reserved-0x19",
        "reserved-0x1A",
        "reserved-0x1B",
        "reserved-0x1C",
        "reserved-0x1D",
        "reserved-0x1E",
        "reserved-0x1F",
        "reserved-0x20",
        "reserved-0x21",
        "reserved-0x22",
        "reserved-0x23",
        "reserved-0x24",
        
        "#,##0_);(#,##0)",
        "#,##0_);[Red](#,##0)",
        "#,##0.00_);(#,##0.00)",
        "#,##0.00_);[Red](#,##0.00)",
		"_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)",
        "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)",
        "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)",
        "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)",
        "mm:ss",
        "[h]:mm:ss",
        "mm:ss.0",
        "##0.0E+0",
        "@"
	};
 }

有了上面的基础，下面，我们来看下，业务代码是怎么写的？

@Override
    public Workbook exportMonitors(CorrectDataTypeExportBO correctDataTypeExportBO, Workbook workbook) {
        Sheet sheet = workbook.createSheet();
        // title
        CellStyle titleStyle = createTitleStyle(workbook);
        PlainTitleContent titleContentList = new PlainTitleContent();
        titleContentList.nextContent("Id").nextContent("TagName").nextContent("DataType");
        fillTitleRowCell(sheet, titleStyle, titleContentList);
        // 查询点位
        TDeviceMonitor tDeviceMonitor = new TDeviceMonitor();
        tDeviceMonitor.setIds(correctDataTypeExportBO.getMonIds());
        tDeviceMonitor.setDeleted(Constants.DELETED_0);
        tDeviceMonitor.setTenantId(Optional.ofNullable(SecurityUtils.getLoginUser()).map(LoginUser::getUser).map(SysUser::getTenantId).orElse(null));
        List<TDeviceMonitor> tDeviceMonitorList = deviceMonitorService.simpleSelectTDeviceMonitorDTOList(tDeviceMonitor);
        if (CollectionUtils.isEmpty(tDeviceMonitorList)) {
            return workbook;
        }
        int dataRowIndex = 1;

        // data
        CellStyle dataStyle = createDataStyle(workbook);
        CreationHelper createHelper = workbook.getCreationHelper();
        for (TDeviceMonitor deviceMonitor : tDeviceMonitorList) {
            Row row = sheet.createRow(dataRowIndex);
            Cell firstColumnCell = row.createCell(0);
            firstColumnCell.setCellValue(createHelper.createRichTextString(deviceMonitor.getId()));
            firstColumnCell.setCellStyle(dataStyle);

            Cell secondColumnCell = row.createCell(1);
            secondColumnCell.setCellValue(deviceMonitor.getName());
            secondColumnCell.setCellStyle(dataStyle);

            Integer dataType = deviceMonitor.getDataType();
            if (Objects.nonNull(dataType)) {
                DataTypeEnum dataTypeEnum = DataTypeEnum.fromVal(dataType);
                ExportDataTypeEnum exportDataTypeEnum = ExportDataTypeEnum.transform(dataTypeEnum);
                Cell thirdColumnCell = row.createCell(2);
                thirdColumnCell.setCellValue(exportDataTypeEnum.getShowVal());
                thirdColumnCell.setCellStyle(dataStyle);
            }
            dataRowIndex++;
        }

        // validation
        int validFirstRow = 1;
        int validLastRow = validFirstRow + tDeviceMonitorList.size();
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        String[] explicitList = ExportDataTypeEnum.showAllVals().stream().toArray(String[]::new);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
                dvHelper.createExplicitListConstraint(explicitList);
        CellRangeAddressList addressList = new CellRangeAddressList(validFirstRow, validLastRow, 2, 2);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(
                dvConstraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
        // column width
        for (int i = 0; i < 3; i++) {
            sheet.autoSizeColumn(i);
        }
        return workbook;
    }

其中，我们需要注意的是validation部分。

XSSFDataValidationConstraint : 这个指定了单元格中可以填写哪些指定的值
CellRangeAddressList : 这个指定了哪些单元格区域使用这个validation规则

setSuppressDropDownArrow(true)： 这个表示启用下拉列表
setShowErrorBox(true)： 这个表示如果填写的值，不是指定值，会弹出错误提示

另外，上面涉及到的其他类如下：

public class PlainTitleContent {
    private LinkedList<String> contents ;

    public PlainTitleContent() {
        this.contents = new LinkedList<>();
    }

    public LinkedList<String> getContents() {
        return contents;
    }

    public void setContents(LinkedList<String> contents) {
        this.contents = contents;
    }

    public PlainTitleContent nextContent(String contentVal) {
        contents.add(contentVal);
        return this;
    }

    public Iterator<String> iteratorContent() {
        Iterator<String> iterator = contents.iterator();
        return iterator;
    }

}

设置列宽和行高

列宽有2种设置方式：自行调整 和 指定列宽。

如果想让excel自动根据内容，自行设置合适的列宽，如下：

// 写数据


	 // column width
        for (int i = 0; i < 3; i++) {
            sheet.autoSizeColumn(i);
        }

如果想手动设置列宽，如下：

    /**
     * Set the width (in units of 1/256th of a character width)
     * <p>
     * The maximum column width for an individual cell is 255 characters.
     * This value represents the number of characters that can be displayed
     * in a cell that is formatted with the standard font.
     * </p>
     *
     * @param columnIndex - the column to set (0-based)
     * @param width - the width in units of 1/256th of a character width
     */
    @Override
    public void setColumnWidth(int columnIndex, int width)
    {
        _sh.setColumnWidth(columnIndex,width);
    }

// 针对最后一列，宽带再增加一点
        double bestWidth = sheet.getColumnWidth(2) * 1.2;
        sheet.setColumnWidth(2, new Double(Math.ceil(bestWidth)).intValue());

与列宽不同，行高只能手动设置，如下：

Row row = sheet.createRow(0);
row.setHeightInPoints(30);

SXSSFWorkbook导出Excel

SXSSFWorkbook 和 XSSFWorkbook，大部分都相同。只有一些细微的区别

下面介绍下，在调整列宽时，SXSSFWorkbook的用法。

因为SXSSFWorkbook生成的Excel，一部分数据已经存到磁盘中了，内存中无法访问，所以调整列宽时，如果直接使用sheet.autoSizeColumn(i);是不可行的。

需要使用如下方式：

// 先跟踪
sheet.trackColumnForAutoSizing(0);
sheet.trackColumnForAutoSizing(1);
sheet.trackColumnForAutoSizing(2);
        
// 再写数据


// 最后调整列宽
 sheet.autoSizeColumn(0);
 sheet.autoSizeColumn(1);
 sheet.autoSizeColumn(2);