JXL生成EXCEL时单元格设置为数值却展示货币或自定义的问题处理

前言：

以下内容为个人见解，勿喷，欢迎有更好解决方法的大佬指点

问题起源：

最近项目中有一个这样的需求：根据后台数据生成Excel后转存到服务器上供用户进行下载。这本来是一个比较常见的需求，不存在什么难点，但是客户要求部分数值列在Excel中展示为“数值”而非“常规”，这其实也没什么问题，但是在实现的过程中却遇到了一些坑。

问题复现：

最初实现，整体代码见文章结尾，此时设置数值格式的代码为：

//设定数字格式
//数值 保留两位小数 千分位展示
NumberFormat numberFont = new NumberFormat("#,##0.00_ ");

"#,##0.00_ "中为预期的数值展示样式，以两位小数、千分位数值型展示，但是实际生成的EXCEL中的样式却是以两位小数、千分位货币型展示。

那么问题是出在哪里？要排查这个问题，我们首先要知道设置数字样式的来源，这个样式并不是凭空编造的，而是从Excel的相关样式直接复制出来的，步骤如下：

1.首先在某个数字型单元格上右键设置单元格格式，选择数字-数值，选择小数位数，勾选千分位分割，选择数字的展示样式；

 2.此时先不要点击确定，选择数字-自定义，此时类型中的一串公式“#,##0.00_ ”即为我们需要的格式，复制出来即可。

 

 那么，理论上，我们使用JXL生成Excel时，使用这个样式的单元格的数字格式应为”数值“：

 但是，实际上得到的却是”货币“，如下：

 

问题排查： 

这是为什么呢？我们来看一下此时这个单元格的数字格式公式是什么。从下图可以看到，我们设置的是”#,##0.00_ “，而得到的却是”#,##0.00“，显而易见我们设置的公式由于某种原因”_ “（下划线和空格）都被干掉了。那么，接下来只需解决这个，让他不被干掉就行了。

 既然出问题的地方是格式公式，那么我们从这里开始debug看看吧（针对版本为2.6.12）。

 可以看到，在 jxl.write.biff.NumberFormatRecord 的第16行执行后，公式中的下划线和空格被干掉了。

问题处理：

找到了原因，怎么解决呢？重写这个方法？算了，先看看有没有原生的方法能用吧，然后发现了这个方法，jxl.write.NumberFormat类下的NumberFormat(String format, NonValidatingFormat dummy)，可以看到该方法只是将公式中的"E0"换为"E+0"，正合我用。（至于参数中的NonValidatingFormat dummy，好像没啥用吧，有懂的大佬还请不吝赐教）。

于是，之前的使用公式的代码改造成了这样：

//设定数字格式
//数值 保留两位小数 千分位展示
NumberFormat numberFont = new NumberFormat("#,##0.00_ ", null);

 修改后的效果已经达到预期了，如下：

注意：

上述方法只是针对设置数值公式却展示为货币的处理方式，而且只是针对简单的数值公式，比较复杂的可能不适用（如”0.00_ ;[红色]-0.00 “、”#,##0.00_);[红色](#,##0.00)“）

源码：

JXL生成EXCEL代码

package com.codelin.demo.utils;


import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.*;
import jxl.format.VerticalAlignment;
import jxl.write.Number;
import jxl.write.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.Boolean;
import java.util.ArrayList;
import java.util.List;

/**
 * @author CodeLin
 * @date 2022/4/8 21:50
 */
public class ExcelUtil {

    static final String excelFilePath = "D:\\excel\\测试.xls";

    public static void main(String[] args) throws Exception {
        //内容
        List<Account> content = new ArrayList<>();
        content.add(new Account("甲", 10897.21));
        content.add(new Account("乙", 210897.31));
        content.add(new Account("丙", 310897.41));
        content.add(new Account("丁", 410897.51));
        content.add(new Account("戊", 510897.61));
        content.add(new Account("己", 60897.71));
        content.add(new Account("庚", 70897.01));
        content.add(new Account("辛", 80897.11));
        generateExcel(content);
    }

    /**
     * 生成excel文件
     *
     * @param content
     * @throws Exception
     */
    private static void generateExcel(List<Account> content) throws Exception {
        //创建一个文件
        File file = createFile(excelFilePath);
        //创建一个输出流，读取文件
        OutputStream outputStream = new FileOutputStream(file);
        //创建一个工作簿
        WritableWorkbook writableWorkbook = Workbook.createWorkbook(outputStream);
        //创建第一个工作表  第一个参数为：工作表名称，第二个参数：第几个工作表，从0开始
        WritableSheet writableSheet = writableWorkbook.createSheet("账户信息", 0);
        //写入数据
        writeWorksheet(writableSheet, content);
        //关闭流
        writableWorkbook.write();
        writableWorkbook.close();
        outputStream.close();
    }

    /**
     *
     * @param writableSheet
     * @param content
     * @throws Exception
     */
    private static void writeWorksheet(WritableSheet writableSheet, List<Account> content) throws Exception {
        //添加表头
        //获取表头单元格格式  居中 黑色18号宋 灰色背景
        WritableCellFormat titleWcf = setWritableCellFormat(getWritableFontTitle(), Alignment.CENTRE, VerticalAlignment.CENTRE, false, Colour.GRAY_25);
        writableSheet.addCell(getLabel(0, 0, "账户名称", titleWcf));
        writableSheet.addCell(getLabel(1, 0, "账户余额", titleWcf));
        // 设置行的高度
        writableSheet.setRowView(0, 600);

        //添加内容
        //获取单元格格式  左对齐，红色9号字体
        WritableCellFormat textWcf = setWritableCellFormat(getWritableFont(Colour.BLACK), Alignment.LEFT, VerticalAlignment.CENTRE, false, null);
        WritableFont numberWritableFont = getWritableFont(Colour.BLACK);
        //设定数字格式
        //数值 保留两位小数 千分位展示
        NumberFormat numberFont = new NumberFormat("#,##0.00_ ", null);
        //单元格宽度  第几列，宽度
        writableSheet.setColumnView(0, 30);
        writableSheet.setColumnView(1, 30);
        for (int i = 0; i < content.size(); i++) {
            Account account = content.get(i);
            writableSheet.addCell(getLabel(0, i + 1, account.getAccountName(), textWcf));
            WritableCellFormat writableCellFormat = new WritableCellFormat(numberWritableFont, numberFont);
            //设置边框 四周 细线条 黑色
            writableCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            writableSheet.addCell(new Number(1, i + 1, account.accountBal, writableCellFormat));
        }
    }

    /**
     * 获取Label
     *
     * @param col  列
     * @param row  行
     * @param cont 内容
     * @param wcf  格式
     * @return Label
     */
    private static Label getLabel(Integer col, Integer row, String cont, CellFormat wcf) {
        return new Label(col, row, cont, wcf);
    }

    /**
     * 文本字体格式
     * 宋体，字号12，非粗体，非斜体，无下划线，字体颜色自定义
     *
     * @return WritableFont
     */
    private static WritableFont getWritableFont(Colour colour) {
        return new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, colour);
    }

    /**
     * 获取表头样式
     * 字体格式
     * 宋体，字号18，粗体，非斜体，无下划线，字体颜色黑色
     *
     * @return WritableFont
     */
    private static WritableFont getWritableFontTitle() {
        WritableFont writableFont = new WritableFont(WritableFont.createFont("宋体"), 18, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        return writableFont;
    }

    /**
     * 设置居中单元格格式
     *
     * @param wf
     * @param alignment         水平对齐样式
     * @param verticalAlignment 垂直对齐样式
     * @param wrap              是否换行
     * @param background        背景色
     * @return
     * @throws WriteException
     */
    private static WritableCellFormat setWritableCellFormat(WritableFont wf, Alignment alignment, VerticalAlignment verticalAlignment, Boolean wrap, Colour background) throws WriteException {
        WritableCellFormat wcf = new WritableCellFormat(wf);
        if (alignment != null) {
            // 水平居中
            wcf.setAlignment(alignment);
        }
        if (verticalAlignment != null) {
            //垂直居中
            wcf.setVerticalAlignment(verticalAlignment);
        }
        //设置边框 四周 细线条 黑色
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        //关闭自动换行
        wcf.setWrap(wrap == null ? false : wrap);
        if (background != null) {
            //设置背景色为灰色
            wcf.setBackground(background);
        }
        return wcf;
    }

    /**
     * 创建文件
     *
     * @param path 路径
     * @return File
     * @throws IOException 异常
     */
    private static File createFile(String path) throws IOException {
        File file = new File(path);
        //判断目录是否存在/不存在就创建
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        //文件存在则先删除
        if (file.exists()) {
            file.delete();
        }
        //创建文件
        if (!file.createNewFile()) {
            throw new IOException("创建文件失败");
        }
        return file;
    }
}

class Account {
    String accountName;
    Double accountBal;

    public String getAccountName() {
        return accountName;
    }

    public void setAccountName(String accountName) {
        this.accountName = accountName;
    }

    public Double getAccountBal() {
        return accountBal;
    }

    public void setAccountBal(Double accountBal) {
        this.accountBal = accountBal;
    }

    Account(String accountName, Double accountBal) {
        this.accountName = accountName;
        this.accountBal = accountBal;
    }

    @Override
    public String toString() {
        return "Account{" +
                "accountName='" + accountName + '\'' +
                ", accountBal=" + accountBal +
                '}';
    }
}