java实现_杂项题Misc-Girlfriends account-xlsx，（基于jxl.Workbook）

题目excel文件
链接：https://pan.baidu.com/s/1WWZyt5HXSjoOsuir6uuRqQ
提取码：bkff

创建金额转换成阿拉伯数字的类

package com.lh.util;

import java.math.BigDecimal;

public class ChineseChangeToNumber {
    /**
     * 中文简体
     */
    public static final String[] RMB_NUMBERS = new String[] {"一","二","三","四","五","六","七","八","九","零"};
    /**
     * 中文繁体
     */
    public static final String[] BIG_RMB_NUMBERS = new String[] {"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"};
    /**
     * 与汉字相应的转化的数字
     */
    public static final Long[] TO_ARABIC_NUMBERS = new Long[] {1L,2L,3L,4L,5L,6L,7L,8L,9L,0L};
    /**
     * 人民币单位关键词  简写（大写数字倍数，一定要由大到小）
     */
    public static final String[] RMB_UNIT=new String[] {"亿","万","千","百","十","元","角","分","厘"};
    /**
     * 繁体
     */
    public static final String[] BIG_RMB_UNIT=new String[] {"億","萬","仟","佰","拾","圆","角","分","厘"};
    /**
     * 与人民币单位关键词对应的基数
     */
    public static final BigDecimal[] TO_CARDINAL_NUMBERS = new BigDecimal[] {
            new BigDecimal(100000000L), new BigDecimal(10000L),new BigDecimal(1000L),
            new BigDecimal(100L), BigDecimal.TEN, BigDecimal.ONE, new BigDecimal("0.1"),
            new BigDecimal("0.01"),new BigDecimal("0.001")
    };

    /**
     * 大写转化为小写的过程操作
     */
    public static BigDecimal ChineseToNumber(String money) {
        BigDecimal number = getDigitalNum(money);
        //return Double.parseDouble(number.toString());
        //return number.toString();
        return new BigDecimal(number.toString());
    }
    /**
     * 辅助类，处理中文数字转换成阿拉伯数字，利用递归算法
     * @param money
     * @return
     */
    public static BigDecimal getDigitalNum(String money) {
        BigDecimal result = BigDecimal.ZERO;
        if((money==null || money.trim().length()<=0)) {
            return result;
        }
        //匹配大写金额的单位
        for(int i = 0;i<RMB_UNIT.length;i++) {
            //查找字符中的简繁单位
            int index = money.lastIndexOf(RMB_UNIT[i]) == -1 ? money.lastIndexOf(BIG_RMB_UNIT[i])
                    : money.lastIndexOf(RMB_UNIT[i]);
            if(index>=0) {
                String pre_money = money.substring(0,index);//截取当前单位前面的中文字符串
                money = money.substring(index+1);//截取当前单位后面的字符串，进行下一次迭代比较
                if((pre_money==null || pre_money.length()<=0) && TO_CARDINAL_NUMBERS[i].intValue()==10) {
                    //处理拾开头的特殊字符例如拾、十
                    result = result.add(TO_CARDINAL_NUMBERS[i]);
                }else {
                    //对当前单位截取的前面的字符递归处理
                    result = result.add(getDigitalNum(pre_money).multiply(TO_CARDINAL_NUMBERS[i]));
                }
            }
        }
        //如果不带单位直接阿拉伯数字匹配替换
        if(money!=null && money.length()>0) {
            result = result.add(getArabicNumByBig(money));
        }
        return result;
    }
    /**
     * 辅助类中文数字转为对应阿拉伯数字
     * @param money
     * @return
     */
    public static BigDecimal getArabicNumByBig(String big) {
        BigDecimal result = BigDecimal.ZERO;
        for(int j = 0;j<RMB_NUMBERS.length;j++) {
            big = big.replaceAll(RMB_NUMBERS[j], TO_ARABIC_NUMBERS[j].toString());//中文小写替换
            big = big.replaceAll(BIG_RMB_NUMBERS[j], TO_ARABIC_NUMBERS[j].toString());//中文大写替换
        }
        try {
            result = new BigDecimal(big);
        } catch (Exception e) {
            result = BigDecimal.ZERO;
        }
        return result;
    }

}

创建主类testflag实现

package com.lh.util;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;

public class testflag {
    public static void main(String[] args) throws IOException, BiffException, WriteException, InterruptedException {
        //获取表格
        Workbook workbook = Workbook.getWorkbook(new File("D://Misc-Girlfriends account-xls.xls"));
        Sheet sheet = workbook.getSheet(0);//获取第一个表
        int rows = sheet.getRows();//获取一共有多少行数据
        //创建表格----------------
        WritableWorkbook book = Workbook.createWorkbook(new File("D://信息.xls"));
        WritableSheet sheets = book.createSheet("信息", 0);
        //创建表格----------------
        //创建第一行的标题
        jxl.write.WritableFont font = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);//创建字体
        jxl.write.WritableCellFormat cellFormat = new jxl.write.WritableCellFormat(font);
        Label l1= new Label(0, 0, "单件金额",cellFormat);//金额
        Label l2 = new Label(1, 0, "件数",cellFormat);//件数
        sheets.addCell(l1);
        sheets.addCell(l2);

        for (int j = 1; j < rows; j++) {
            Cell[] cells = sheet.getRow(j);//得到第几行
            String price = cells[0].getContents();//第几行的第几列（价格）
            String piece = cells[1].getContents();//（件数）

            BigDecimal price1 = ChineseChangeToNumber.ChineseToNumber(price);//转换第一个价格
            BigDecimal piece1 = ChineseChangeToNumber.ChineseToNumber(piece);//转换件数
            BigDecimal multiply = price1.multiply(piece1);//计算总价

            Label label = new Label(0, j, price);//金额
            Label label1 = new Label(1, j, piece);//件数
            Label label2 = new Label(2, j, multiply.toString());//总价格


            sheets.addCell(label);//添加元素
            sheets.addCell(label1);
            sheets.addCell(label2);

        }
        book.write();
        book.close();
        workbook.close();
    }
}

创建完成会自动生成信息表格。