Java 调用Poi 生成费用报销单Excel

置顶 HLucky_17

已于 2022-07-11 16:06:22 修改

阅读量760

点赞数 1

分类专栏： excel 文章标签： java

于 2022-06-10 16:02:49 首次发布

本文链接：https://blog.csdn.net/qq_35243979/article/details/125223065

版权

excel 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

整个代码有点多，并没有做优化。目前只是为了功能实现。可以直接复制，创建main方法运行

默认一张A4纸，可以存放2个报销单模板。

如果只想一张A4纸，存放一个报销单。可以从分割线以下代码删除掉。其对应的代码也要全部删除掉。

分割线的作用是用来，打印之后裁剪用的。

代码里可填写的值，目前填写的是模板值。

应该自己封装一个json对象，然后传入到该方法中，进行json数据解析。

并一一填充到具体的列中。

添加条形二维码：Java 调用Poi 生成费用报销单Excel_02_HLucky_17的博客-CSDN博客

代码下载链接：Java调用Poi生成费用报销单Excel-Java文档类资源-CSDN下载

打印预览效果：

pom.xml

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

代码区：

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.CellRangeAddress;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.RegionUtil;

public class ExcelUtils {

   /**
   * 报销单excel模板
   */
   public static void reimbursementExcel() {
       try {
           // 创建工作薄对象
           HSSFWorkbook wb = new HSSFWorkbook();
           // 创建工作表对象
           HSSFSheet sheet = wb.createSheet();

           // A4 纸上半部分

           // 设置列的宽度

           // 设置列宽度
           sheet.setColumnWidth(0, 256 * 6);
           sheet.setColumnWidth(1, 256 * 6);
           sheet.setColumnWidth(2, 256 * 3);
           sheet.setColumnWidth(3, 256 * 3);
           sheet.setColumnWidth(4, 256 * 3);
           sheet.setColumnWidth(5, 256 * 3);
           sheet.setColumnWidth(6, 256 * 3);
           sheet.setColumnWidth(7, 256 * 3);
           sheet.setColumnWidth(8, 256 * 3);
           sheet.setColumnWidth(9, 256 * 3);
           sheet.setColumnWidth(10, 256 * 3);
           sheet.setColumnWidth(11, 256 * 3);
           sheet.setColumnWidth(12, 256 * 3);
           sheet.setColumnWidth(13, 256 * 3);
           sheet.setColumnWidth(14, 256 * 3);
           sheet.setColumnWidth(15, 256 * 3);
           sheet.setColumnWidth(16, 256 * 3);
           sheet.setColumnWidth(17, 256 * 3);
           sheet.setColumnWidth(18, 256 * 3);
           sheet.setColumnWidth(19, 256 * 3);
           sheet.setColumnWidth(20, 256 * 4);
           sheet.setColumnWidth(21, 256 * 4);
           sheet.setColumnWidth(22, 256 * 4);
           sheet.setColumnWidth(23, 256 * 3);
           sheet.setColumnWidth(24, 256 * 3);
           sheet.setColumnWidth(25, 256 * 4);
           sheet.setColumnWidth(26, 256 * 4);
           sheet.setColumnWidth(27, 256 * 4);

           // 设置该行高度
           HSSFRow row = null;

           // 第一张报销单
           row = sheet.createRow(0);
           row.setHeight((short) 600);
           row = sheet.createRow(1);
           row.setHeight((short) 50);
           row = sheet.createRow(2);
           row.setHeight((short) 200);
           row = sheet.createRow(3);
           row.setHeight((short) 600);
           row = sheet.createRow(4);
           row.setHeight((short) 300);
           row = sheet.createRow(5);
           row.setHeight((short) 300);
           row = sheet.createRow(6);
           row.setHeight((short) 600);
           row = sheet.createRow(7);
           row.setHeight((short) 600);
           row = sheet.createRow(8);
           row.setHeight((short) 600);
           row = sheet.createRow(9);
           row.setHeight((short) 600);
           row = sheet.createRow(10);
           row.setHeight((short) 600);
           row = sheet.createRow(11);
           row.setHeight((short) 600);
           row = sheet.createRow(12);
           row.setHeight((short) 600);
           row = sheet.createRow(13);
           row.setHeight((short) 600);

           // 第十六行折叠中间线

           row = sheet.createRow(14);
           row.setHeight((short) 600);
           row = sheet.createRow(15);
           row.setHeight((short) 400);
           row = sheet.createRow(16);
           row.setHeight((short) 600);


           // 第二张报销单
           row = sheet.createRow(17);
           row.setHeight((short) 600);
           row = sheet.createRow(18);
           row.setHeight((short) 50);
           row = sheet.createRow(19);
           row.setHeight((short) 200);
           row = sheet.createRow(20);
           row.setHeight((short) 600);
           row = sheet.createRow(21);
           row.setHeight((short) 300);
           row = sheet.createRow(22);
           row.setHeight((short) 300);
           row = sheet.createRow(23);
           row.setHeight((short) 600);
           row = sheet.createRow(24);
           row.setHeight((short) 600);
           row = sheet.createRow(25);
           row.setHeight((short) 600);
           row = sheet.createRow(26);
           row.setHeight((short) 600);
           row = sheet.createRow(27);
           row.setHeight((short) 600);
           row = sheet.createRow(28);
           row.setHeight((short) 600);
           row = sheet.createRow(29);
           row.setHeight((short) 600);
           row = sheet.createRow(30);
           row.setHeight((short) 600);
           row = sheet.createRow(30);
           row.setHeight((short) 600);

HSSFCell cell = row.createCell(0);

           // 第一行
           // 合并第一行从第一行开始第一行结束第零列开始第二十七列结束
           CellRangeAddress cellRangeAddress40 = new CellRangeAddress(0, 0, 0, 27);
           sheet.addMergedRegion(cellRangeAddress40);

row = sheet.createRow(0);
row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size20.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("费用报销单"));

           // 第二行
           CellRangeAddress cellRangeAddress41 = new CellRangeAddress(1, 1, 10, 17);
           sheet.addMergedRegion(cellRangeAddress41);

            // 第三行 (修改：要求年月日居中)
           // 报销部门
           CellRangeAddress cellRangeAddress33 = new CellRangeAddress(3, 3, 0, 9);
           sheet.addMergedRegion(cellRangeAddress33);

           // 填写区域(报销部门)
//           CellRangeAddress cellRangeAddress34 = new CellRangeAddress(3, 3, 3, 10);
//           sheet.addMergedRegion(cellRangeAddress34);

           // 填写区域(年份)
           CellRangeAddress cellRangeAddress35 = new CellRangeAddress(3, 3, 10, 11);
           sheet.addMergedRegion(cellRangeAddress35);

           // 填写区域(月份)
           CellRangeAddress cellRangeAddress36 = new CellRangeAddress(3, 3, 13, 13);
           sheet.addMergedRegion(cellRangeAddress36);

           // 填写区域(日)
           CellRangeAddress cellRangeAddress37 = new CellRangeAddress(3, 3, 14, 14);
           sheet.addMergedRegion(cellRangeAddress37);

           // 日填
           CellRangeAddress cellRangeAddress38 = new CellRangeAddress(3, 3, 16, 17);
           sheet.addMergedRegion(cellRangeAddress38);

           // 单据及附件共
           CellRangeAddress cellRangeAddress39 = new CellRangeAddress(3, 3, 20, 25);
           sheet.addMergedRegion(cellRangeAddress39);

row = sheet.createRow(3);
row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("报销部门:${depart}"));

           cell.setCellValue(new HSSFRichTextString(cell.getRichStringCellValue().toString().replace("${depart}", "这里应该填")));

           // 这是填写的值
//           cell = row.createCell(3);
//           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
//                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
//           cell.setCellValue(new HSSFRichTextString("这里应该填写一个部门"));

           // 这是填写的值
           cell = row.createCell(10);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("2022"));

           cell = row.createCell(12);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("年"));

           // 这是填写的值
           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("06"));

           cell = row.createCell(14);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("月"));

           // 这是填写的值
           cell = row.createCell(15);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("31"));

           cell = row.createCell(16);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("日填"));

           cell = row.createCell(20);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("单据及附件共"));

           // 填写的页码值
           cell = row.createCell(26);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, true, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("99"));

           cell = row.createCell(27);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("页"));

           // 第四行
           // 发生日期
           CellRangeAddress cellRangeAddress1 = new CellRangeAddress(4, 4, 0, 1);
           sheet.addMergedRegion(cellRangeAddress1);

           // 用途
           CellRangeAddress cellRangeAddress2 = new CellRangeAddress(4, 5, 2, 12);
           sheet.addMergedRegion(cellRangeAddress2);

           // 金额
           CellRangeAddress cellRangeAddress3 = new CellRangeAddress(4, 5, 13, 17);
           sheet.addMergedRegion(cellRangeAddress3);

           // 备注
           CellRangeAddress cellRangeAddress4 = new CellRangeAddress(4, 8, 18, 19);
           sheet.addMergedRegion(cellRangeAddress4);

           // 签字区域
           CellRangeAddress cellRangeAddress32 = new CellRangeAddress(4, 8, 20, 27);
           sheet.addMergedRegion(cellRangeAddress32);

row = sheet.createRow(4);

           cell = row.createCell(2);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("用途"));

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("金额（元）"));

           // 这里填写备注的内容
           cell = row.createCell(20);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, true));
           cell.setCellValue(new HSSFRichTextString("这里是备注的内容,这里是备注的内容,这里是备注的内容,这里是备注的内容,这里是备注的内容"));

           // 第五行
           CellRangeAddress cellRangeAddress6 = new CellRangeAddress(5, 5, 2, 12);
           sheet.addMergedRegion(cellRangeAddress6);

CellRangeAddress cellRangeAddress7 = new CellRangeAddress(5, 5, 13, 17);
sheet.addMergedRegion(cellRangeAddress7);

row = sheet.createRow(5);
row.setHeight((short) 300);

           // 第六行
           CellRangeAddress cellRangeAddress8 = new CellRangeAddress(6, 6, 2, 12);
           sheet.addMergedRegion(cellRangeAddress8);

CellRangeAddress cellRangeAddress9 = new CellRangeAddress(6, 6, 13, 17);
sheet.addMergedRegion(cellRangeAddress9);

row = sheet.createRow(6);
row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, true, false, false));
           cell.setCellValue(new HSSFRichTextString("08"));

           cell = row.createCell(1);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, true, false, false));
           cell.setCellValue(new HSSFRichTextString("06"));

           cell = row.createCell(2);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, true, false, false));
           cell.setCellValue(new HSSFRichTextString("测试用途"));

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, true, false, false));
           cell.setCellValue(new HSSFRichTextString(formatTosepara(123)));

String p1 = cell.getRichStringCellValue().toString();

           // 第七行
           CellRangeAddress cellRangeAddress10 = new CellRangeAddress(7, 7, 2, 12);
           sheet.addMergedRegion(cellRangeAddress10);

CellRangeAddress cellRangeAddress11 = new CellRangeAddress(7, 7, 13, 17);
sheet.addMergedRegion(cellRangeAddress11);

row = sheet.createRow(7);
row.setHeight((short) 600);

String p2 = cell.getRichStringCellValue().toString();

           // 第八行
           CellRangeAddress cellRangeAddress12 = new CellRangeAddress(8, 8, 2, 12);
           sheet.addMergedRegion(cellRangeAddress12);

CellRangeAddress cellRangeAddress13 = new CellRangeAddress(8, 8, 13, 17);
sheet.addMergedRegion(cellRangeAddress13);

row = sheet.createRow(8);
row.setHeight((short) 600);

String p3 = cell.getRichStringCellValue().toString();

           // 第九行
           CellRangeAddress cellRangeAddress14 = new CellRangeAddress(9, 9, 2, 12);
           sheet.addMergedRegion(cellRangeAddress14);

CellRangeAddress cellRangeAddress15 = new CellRangeAddress(9, 9, 13, 17);
sheet.addMergedRegion(cellRangeAddress15);

           // 部门审核
           CellRangeAddress cellRangeAddress5 = new CellRangeAddress(9, 11, 18, 19);
           sheet.addMergedRegion(cellRangeAddress5);

           // 签字区域
           CellRangeAddress cellRangeAddress29 = new CellRangeAddress(9, 11, 20, 22);
           sheet.addMergedRegion(cellRangeAddress29);

           // 领导审批
           CellRangeAddress cellRangeAddress30 = new CellRangeAddress(9, 11, 23, 24);
           sheet.addMergedRegion(cellRangeAddress30);

           // 签字区域
           CellRangeAddress cellRangeAddress31 = new CellRangeAddress(9, 11, 25, 27);
           sheet.addMergedRegion(cellRangeAddress31);

row = sheet.createRow(9);
row.setHeight((short) 600);

String p4 = cell.getRichStringCellValue().toString();

           // 第十行
           CellRangeAddress cellRangeAddress16 = new CellRangeAddress(10, 10, 2, 12);
           sheet.addMergedRegion(cellRangeAddress16);

CellRangeAddress cellRangeAddress17 = new CellRangeAddress(10, 10, 13, 17);
sheet.addMergedRegion(cellRangeAddress17);

row = sheet.createRow(10);
row.setHeight((short) 600);

String p5 = cell.getRichStringCellValue().toString();

           // 第十一行
           CellRangeAddress cellRangeAddress18 = new CellRangeAddress(11, 11, 0, 12);
           sheet.addMergedRegion(cellRangeAddress18);

CellRangeAddress cellRangeAddress19 = new CellRangeAddress(11, 11, 13, 17);
sheet.addMergedRegion(cellRangeAddress19);

row = sheet.createRow(11);
row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("合计"));

           // 计算合计
           String p_all = priceUtils(p1, p2, "+");

           p_all = priceUtils(p_all, p3, "+");
           p_all = priceUtils(p_all, p4, "+");
           p_all = priceUtils(p_all, p5, "+");

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("¥:"+p_all));

//           cell = row.createCell(14);
//           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
//                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, false, false, false));

// cell.setCellValue(new HSSFRichTextString(p_all));

           // 第十二行
           CellRangeAddress cellRangeAddress20 = new CellRangeAddress(12, 12, 0, 1);
           sheet.addMergedRegion(cellRangeAddress20);

row = sheet.createRow(12);
row.setHeight((short) 600);

List<String> price = getCharacters(cell.getRichStringCellValue().toString());

           // 金额大写： ${7} 拾 ${6} 万 ${5} 仟 ${4} 佰 ${3} 拾 ${2} 元 ${1} 角 ${0} 分
           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("金额大写："));

           cell = row.createCell(2);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 7 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 7 ? price.get(price.size() - 8) : "⊗"));

           cell = row.createCell(3);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("拾"));

           cell = row.createCell(4);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 6 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 6 ? price.get(price.size() - 7) : "⊗"));

           cell = row.createCell(5);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("万"));

           cell = row.createCell(6);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 5 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 5 ? price.get(price.size() - 6) : "⊗"));

           cell = row.createCell(7);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("仟"));

           cell = row.createCell(8);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 4 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 4 ? price.get(price.size() - 5) : "⊗"));

           cell = row.createCell(9);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("佰"));

           cell = row.createCell(10);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 3 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 3 ? price.get(price.size() - 4) : "⊗"));

           cell = row.createCell(11);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("拾"));

           cell = row.createCell(12);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 2 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 2 ? price.get(price.size() - 3) : "⊗"));

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("元"));

           cell = row.createCell(14);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 1 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 1 ? price.get(price.size() - 2) : "⊗"));

           cell = row.createCell(15);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("角"));

           cell = row.createCell(16);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price.size() > 0 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price.size() > 0 ? price.get(price.size() - 1) : "⊗"));

           // 报销人签章
           CellRangeAddress cellRangeAddress21 = new CellRangeAddress(12, 12, 18, 22);
           sheet.addMergedRegion(cellRangeAddress21);

           // 签字区域
           CellRangeAddress cellRangeAddress22 = new CellRangeAddress(12, 12, 23, 27);
           sheet.addMergedRegion(cellRangeAddress22);

           // 第十三行
           // 会计主管
           CellRangeAddress cellRangeAddress23 = new CellRangeAddress(13, 13, 0, 1);
           sheet.addMergedRegion(cellRangeAddress23);

           // 签字区域
           CellRangeAddress cellRangeAddress24 = new CellRangeAddress(13, 13, 2, 8);
           sheet.addMergedRegion(cellRangeAddress24);

           // 会计
           CellRangeAddress cellRangeAddress25 = new CellRangeAddress(13, 13, 9, 11);
           sheet.addMergedRegion(cellRangeAddress25);

           // 签字区域
           CellRangeAddress cellRangeAddress26 = new CellRangeAddress(13, 13, 12, 17);
           sheet.addMergedRegion(cellRangeAddress26);

           // 出纳
           CellRangeAddress cellRangeAddress27 = new CellRangeAddress(13, 13, 18, 20);
           sheet.addMergedRegion(cellRangeAddress27);

           // 签字区域
           CellRangeAddress cellRangeAddress28 = new CellRangeAddress(13, 13, 21, 27);
           sheet.addMergedRegion(cellRangeAddress28);

row = sheet.createRow(13);
row.setHeight((short) 600);

           // 第一列
           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("会计主管："));

           // 第二列
           cell = row.createCell(9);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("会计："));

           setRegionBorder(1, cellRangeAddress1, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress2, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress3, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress4, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress5, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress6, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress7, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress8, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress9, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress10, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress11, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress12, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress13, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress14, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress15, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress16, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress17, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress18, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress19, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress20, sheet, wb, true, true, true, false);
           setRegionBorder(1, cellRangeAddress21, sheet, wb, true, true, false, false);
           setRegionBorder(1, cellRangeAddress22, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress29, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress30, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress31, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress32, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress33, sheet, wb, false, true, false, false);
//           setRegionBorder(1, cellRangeAddress34, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress35, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress36, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress37, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress38, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress39, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress40, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress41, sheet, wb, true, true, false, false);

           // 中间线
           // 第十六行

           CellRangeAddress cellRangeAddress50 = new CellRangeAddress(14, 14, 0, 27);
           sheet.addMergedRegion(cellRangeAddress50);

           CellRangeAddress cellRangeAddress51 = new CellRangeAddress(15, 15, 0, 27);
           sheet.addMergedRegion(cellRangeAddress51);

           CellRangeAddress cellRangeAddress90 = new CellRangeAddress(16, 16, 0, 27);
           sheet.addMergedRegion(cellRangeAddress90);

           // 这是一条裁剪线
           row = sheet.createRow(15);
           row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("————————————————————————————————————————————————————————————————————————"));

           // A4 纸下半部分

           // 第一行
           // 合并第一行从第一行开始第一行结束第零列开始第二十七列结束
           CellRangeAddress cellRangeAddress52 = new CellRangeAddress(17, 17, 0, 27);
           sheet.addMergedRegion(cellRangeAddress52);

           row = sheet.createRow(17);
           row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size20.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("费用报销单"));

           // 第二行
           CellRangeAddress cellRangeAddress53 = new CellRangeAddress(18, 18, 10, 17);
           sheet.addMergedRegion(cellRangeAddress53);

// 第三行 (修改：要求年月日居中)
           // 报销部门
           CellRangeAddress cellRangeAddress54 = new CellRangeAddress(20, 20, 0, 9);
           sheet.addMergedRegion(cellRangeAddress54);

           // 填写区域(年份)
           CellRangeAddress cellRangeAddress55 = new CellRangeAddress(20, 20, 10, 11);
           sheet.addMergedRegion(cellRangeAddress55);

           // 填写区域(月份)
           CellRangeAddress cellRangeAddress56 = new CellRangeAddress(20, 20, 13, 13);
           sheet.addMergedRegion(cellRangeAddress56);

           // 填写区域(日)
           CellRangeAddress cellRangeAddress57 = new CellRangeAddress(20, 20, 14, 14);
           sheet.addMergedRegion(cellRangeAddress57);

           // 日填
           CellRangeAddress cellRangeAddress58 = new CellRangeAddress(20, 20, 16, 17);
           sheet.addMergedRegion(cellRangeAddress58);

           // 单据及附件共
           CellRangeAddress cellRangeAddress59 = new CellRangeAddress(20, 20, 20, 25);
           sheet.addMergedRegion(cellRangeAddress59);

           row = sheet.createRow(20);
           row.setHeight((short) 600);

           // 第四行
           // 发生日期
           CellRangeAddress cellRangeAddress60 = new CellRangeAddress(21, 21, 0, 1);
           sheet.addMergedRegion(cellRangeAddress60);

           // 用途
           CellRangeAddress cellRangeAddress61 = new CellRangeAddress(21, 22, 2, 12);
           sheet.addMergedRegion(cellRangeAddress61);

           // 金额
           CellRangeAddress cellRangeAddress62 = new CellRangeAddress(21, 22, 13, 17);
           sheet.addMergedRegion(cellRangeAddress62);

           // 备注
           CellRangeAddress cellRangeAddress63 = new CellRangeAddress(21, 25, 18, 19);
           sheet.addMergedRegion(cellRangeAddress63);

           // 签字区域
           CellRangeAddress cellRangeAddress64 = new CellRangeAddress(21, 25, 20, 27);
           sheet.addMergedRegion(cellRangeAddress64);

row = sheet.createRow(21);

           cell = row.createCell(1);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, false, true, true, false));
           cell.setCellValue(new HSSFRichTextString("日"));


           // 第六行
           CellRangeAddress cellRangeAddress65 = new CellRangeAddress(23, 23, 2, 12);
           sheet.addMergedRegion(cellRangeAddress65);

CellRangeAddress cellRangeAddress66 = new CellRangeAddress(23, 23, 13, 17);
sheet.addMergedRegion(cellRangeAddress66);

           row = sheet.createRow(23);
           row.setHeight((short) 600);


           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, true, false, false));
           cell.setCellValue(new HSSFRichTextString("08"));

           String p_t_1 = cell.getRichStringCellValue().toString();


           // 第七行
           CellRangeAddress cellRangeAddress67 = new CellRangeAddress(24, 24, 2, 12);
           sheet.addMergedRegion(cellRangeAddress67);

CellRangeAddress cellRangeAddress68 = new CellRangeAddress(24, 24, 13, 17);
sheet.addMergedRegion(cellRangeAddress68);

row = sheet.createRow(24);
row.setHeight((short) 600);

           String p_t_2 = cell.getRichStringCellValue().toString();

           // 第八行
           CellRangeAddress cellRangeAddress69 = new CellRangeAddress(25, 25, 2, 12);
           sheet.addMergedRegion(cellRangeAddress69);

CellRangeAddress cellRangeAddress70 = new CellRangeAddress(25, 25, 13, 17);
sheet.addMergedRegion(cellRangeAddress70);

row = sheet.createRow(25);
row.setHeight((short) 600);

           String p_t_3 = cell.getRichStringCellValue().toString();


           // 第九行
           CellRangeAddress cellRangeAddress71 = new CellRangeAddress(26, 26, 2, 12);
           sheet.addMergedRegion(cellRangeAddress71);

CellRangeAddress cellRangeAddress72 = new CellRangeAddress(26, 26, 13, 17);
sheet.addMergedRegion(cellRangeAddress72);

           // 部门审核
           CellRangeAddress cellRangeAddress73 = new CellRangeAddress(26, 28, 18, 19);
           sheet.addMergedRegion(cellRangeAddress73);

           // 签字区域
           CellRangeAddress cellRangeAddress74 = new CellRangeAddress(26, 28, 20, 22);
           sheet.addMergedRegion(cellRangeAddress74);

           // 领导审批
           CellRangeAddress cellRangeAddress81 = new CellRangeAddress(26, 28, 23, 24);
           sheet.addMergedRegion(cellRangeAddress81);

           // 签字区域
           CellRangeAddress cellRangeAddress82 = new CellRangeAddress(26, 28, 25, 27);
           sheet.addMergedRegion(cellRangeAddress82);

           row = sheet.createRow(26);
           row.setHeight((short) 600);

           String p_t_4 = cell.getRichStringCellValue().toString();

           cell = row.createCell(18);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, true, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("部门审核"));

           cell = row.createCell(23);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, true, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("领导审批"));


           // 第十行
           CellRangeAddress cellRangeAddress83 = new CellRangeAddress(27, 27, 2, 12);
           sheet.addMergedRegion(cellRangeAddress83);

           CellRangeAddress cellRangeAddress84 = new CellRangeAddress(27, 27, 13, 17);
           sheet.addMergedRegion(cellRangeAddress84);

           row = sheet.createRow(27);
           row.setHeight((short) 600);

           String p_t_5 = cell.getRichStringCellValue().toString();


           // 第十一行
           CellRangeAddress cellRangeAddress85 = new CellRangeAddress(28, 28, 0, 12);
           sheet.addMergedRegion(cellRangeAddress85);

           CellRangeAddress cellRangeAddress86 = new CellRangeAddress(28, 28, 13, 17);
           sheet.addMergedRegion(cellRangeAddress86);

           row = sheet.createRow(28);
           row.setHeight((short) 600);

           cell = row.createCell(0);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, false, false, false, false, false, false));
           cell.setCellValue(new HSSFRichTextString("合计"));

           // 计算合计
           String p_t_all = priceUtils(p_t_1, p_t_2, "+");

           p_t_all = priceUtils(p_t_all, p_t_3, "+");
           p_t_all = priceUtils(p_t_all, p_t_4, "+");
           p_t_all = priceUtils(p_t_all, p_t_5, "+");

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size12.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("¥:"+p_t_all));


           List<String> price_t = getCharacters(cell.getRichStringCellValue().toString());

           // 第十二行
           CellRangeAddress cellRangeAddress87 = new CellRangeAddress(29, 29, 0, 1);
           sheet.addMergedRegion(cellRangeAddress87);

row = sheet.createRow(29);
row.setHeight((short) 600);

           cell = row.createCell(2);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 7 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 7 ? price_t.get(price_t.size() - 8) : "⊗"));

           cell = row.createCell(4);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 6 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 6 ? price_t.get(price_t.size() - 7) : "⊗"));

           cell = row.createCell(6);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 5 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 5 ? price_t.get(price_t.size() - 6) : "⊗"));

           cell = row.createCell(8);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 4 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 4 ? price_t.get(price_t.size() - 5) : "⊗"));

           cell = row.createCell(10);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 3 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 3 ? price_t.get(price_t.size() - 4) : "⊗"));

           cell = row.createCell(12);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 2 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 2 ? price_t.get(price_t.size() - 3) : "⊗"));

           cell = row.createCell(13);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString("元"));

           cell = row.createCell(14);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 1 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 1 ? price_t.get(price_t.size() - 2) : "⊗"));

           cell = row.createCell(16);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb,
                   price_t.size() > 0 ? fontSize.name_size12.getValue() : fontSize.name_size18.getValue(),
                   HSSFCellStyle.ALIGN_CENTER, false, false, false, false, true, true, true, false, false, false));
           cell.setCellValue(new HSSFRichTextString(price_t.size() > 0 ? price_t.get(price_t.size() - 1) : "⊗"));

           cell = row.createCell(17);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_RIGHT, false, false, false, false, true, true, true, false, true, false));
           cell.setCellValue(new HSSFRichTextString("分"));


           // 报销人签章
           CellRangeAddress cellRangeAddress88 = new CellRangeAddress(29, 29, 18, 22);
           sheet.addMergedRegion(cellRangeAddress88);

           cell = row.createCell(18);
           cell.setCellType(HSSFCell.CELL_TYPE_STRING);
           cell.setCellStyle(setStyle(fontName.name_kt.getValue(), wb, fontSize.name_size14.getValue(),
                   HSSFCellStyle.ALIGN_LEFT, false, false, false, false, true, true, true, false, true, false));
           cell.setCellValue(new HSSFRichTextString("报销人签章："));


           // 签字区域
           CellRangeAddress cellRangeAddress89 = new CellRangeAddress(29, 29, 23, 27);
           sheet.addMergedRegion(cellRangeAddress89);



           // 第十三行
           // 会计主管
           CellRangeAddress cellRangeAddress75 = new CellRangeAddress(30, 30, 0, 1);
           sheet.addMergedRegion(cellRangeAddress75);

           // 签字区域
           CellRangeAddress cellRangeAddress76 = new CellRangeAddress(30, 30, 2, 8);
           sheet.addMergedRegion(cellRangeAddress76);

           // 会计
           CellRangeAddress cellRangeAddress77 = new CellRangeAddress(30, 30, 9, 11);
           sheet.addMergedRegion(cellRangeAddress77);

           // 签字区域
           CellRangeAddress cellRangeAddress78 = new CellRangeAddress(30, 30, 12, 17);
           sheet.addMergedRegion(cellRangeAddress78);

           // 出纳
           CellRangeAddress cellRangeAddress79 = new CellRangeAddress(30, 30, 18, 20);
           sheet.addMergedRegion(cellRangeAddress79);

           // 签字区域
           CellRangeAddress cellRangeAddress80 = new CellRangeAddress(30, 30, 21, 27);
           sheet.addMergedRegion(cellRangeAddress80);

row = sheet.createRow(30);
row.setHeight((short) 600);

           setRegionBorder(1, cellRangeAddress50, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress51, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress52, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress53, sheet, wb, true, true, false, false);
           setRegionBorder(1, cellRangeAddress54, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress55, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress56, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress57, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress58, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress59, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress60, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress61, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress62, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress63, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress64, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress65, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress66, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress67, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress68, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress69, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress70, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress71, sheet, wb, true, true, true, false);
           setRegionBorder(1, cellRangeAddress72, sheet, wb, true, true, false, false);
           setRegionBorder(1, cellRangeAddress73, sheet, wb, true, true, true, true);
           setRegionBorder(1, cellRangeAddress74, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress81, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress82, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress83, sheet, wb, true, true, false, true);
           setRegionBorder(1, cellRangeAddress84, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress85, sheet, wb, false, true, true, true);
           setRegionBorder(1, cellRangeAddress86, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress87, sheet, wb, false, true, true, false);
           setRegionBorder(1, cellRangeAddress88, sheet, wb, false, true, false, false);
           setRegionBorder(1, cellRangeAddress89, sheet, wb, false, true, false, true);
           setRegionBorder(1, cellRangeAddress90, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress75, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress76, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress77, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress78, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress79, sheet, wb, false, false, false, false);
           setRegionBorder(1, cellRangeAddress80, sheet, wb, false, false, false, false);

           // 打印设置
           PrintSetup ps = sheet.getPrintSetup();
           // 印刷方向、true：横向、false：縦向
           ps.setLandscape(true); // 横向
           ps.setLandscape(false); // 縦向

// 设置用紙
ps.setPaperSize(PrintSetup.A4_PAPERSIZE);

           sheet.setMargin(Sheet.LeftMargin, 0.5f);// (左)
           sheet.setMargin(Sheet.RightMargin, 0.2f);// (右)
           sheet.setMargin(Sheet.BottomMargin, 1.4f);// (下)
           sheet.setMargin(Sheet.TopMargin, 1.4f);// (上)

// 启用“适合页面”打印选项的标志。(默认选择的是“将工作表调整为一页”)
sheet.setFitToPage(true);

// 设置sheet的Name

wb.setSheetName(0, "费用报销单");

           // 文档输出
           String path = "C:\\excel\\" + String.valueOf(LocalDate.now()).substring(0, 10) + "\\";
           File dest = new File(path);
           if (!dest.isDirectory() && !dest.exists())
               dest.mkdirs();

           FileOutputStream out = new FileOutputStream(path + System.currentTimeMillis() + "费用报销单.xls");
           wb.write(out);
           out.close();

       } catch (FileNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       } catch (Exception e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   }

   /**
   * 单元格样式设置
   *
   * @param fortName 字体名称
   * @param wb
   * @param height 高度
   * @param center HSSFCellStyle.ALIGN_CENTER
   * @param flag true:竖排显示
   * @param bold true:加粗
   * @param single true:下划线
   * @param dsingle true:双下划线
   * @param border true:边框
   * @param top true:上
   * @param bottom 下
   * @param left 左
   * @param right 右
   * @param warp true:自动换行
   * @return
   * @throws Exception
   */
   private static HSSFCellStyle setStyle(String fortName, HSSFWorkbook wb, short height, short center, boolean flag,
           boolean bold, boolean single, boolean dsingle, boolean border, boolean top, boolean bottom, boolean left,
           boolean right, boolean warp) throws Exception {

       // 设置单元格类型
       HSSFFont font = wb.createFont();
       font.setFontName(fortName);
       font.setFontHeightInPoints(height);
       font.setBoldweight((bold) ? HSSFFont.BOLDWEIGHT_BOLD : HSSFFont.BOLDWEIGHT_NORMAL);
       if (single)
           font.setUnderline(HSSFFont.U_SINGLE);

if (dsingle)
font.setUnderline(HSSFFont.U_DOUBLE);

       HSSFCellStyle cellStyle = wb.createCellStyle();
       cellStyle.setFont(font);
       cellStyle.setAlignment(center);
       cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
       // 设置自动换行
       if (warp)
           cellStyle.setWrapText(true);

       // 竖排显示
       if (flag)
           cellStyle.setRotation((short) 0xff);

       if (border) {
           if (top)
               cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
           if (bottom)
               cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
           if (left)
               cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
           if (right)
               cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
       }

return cellStyle;
}

   /**
   *
   * @param border
   * @param region
   * @param sheet
   * @param wb
   * @param top 上
   * @param bottom 下
   * @param left 左
   * @param right 右
   */
   public static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet, Workbook wb, boolean top,
           boolean bottom, boolean left, boolean right) {
       if (top)
           RegionUtil.setBorderTop(border, region, sheet, wb); // 上边框
       if (bottom)
           RegionUtil.setBorderBottom(border, region, sheet, wb); // 下边框
       if (left)
           RegionUtil.setBorderLeft(border, region, sheet, wb); // 左边框
       if (right)
           RegionUtil.setBorderRight(border, region, sheet, wb); // 右边框
   }

   /**
   * 文本下划线设置
   *
   * @param detail
   * @param font
   * @return
   */
   private static HSSFRichTextString UnderLineIndex(StringBuffer detail, HSSFFont font) {

HSSFRichTextString richString = new HSSFRichTextString(detail.toString());

// 下划线的起始位置结束位置
richString.applyFont(6, detail.length() - 1, font);

return richString;
}

   public static HSSFFont getFont(HSSFWorkbook wb) {
       HSSFFont font = wb.createFont();
       // 下划线
       font.setUnderline(HSSFFont.U_SINGLE);
       font.setFontName(fontName.name_kt.getValue());
       font.setFontHeightInPoints(fontSize.name_size14.getValue());

return font;
}

   /**
   * @param args
   *
   */
   private static final String[] data = new String[] { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
   private static final String[] dataIndex = new String[] { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };

   public static void main(String[] args) {
       reimbursementExcel();
   }

   /**
   * 将金额转换成汉字
   *
   * @param strPrice 0.25
   * @return [零, 贰, 伍]
   */
   private static List<String> getCharacters(String strPrice) {

       // 说明没有小数点
       if (strPrice.indexOf(".") == -1)
           strPrice += "-00";
       else
           strPrice = strPrice.replace(".", "-");

int bi = strPrice.indexOf("-");
int si = strPrice.length() - (bi + 1);

if (si == 1)
strPrice += "0";

bi = strPrice.indexOf("-");
si = strPrice.length() - (bi + 1);

String[] price_split = strPrice.split("-");

String[] array_before = intArray(price_split[0]);
String[] array_after = intArray(price_split[1]);

String[] capitalize0 = new String[array_before.length];
String[] capitalize1 = new String[array_after.length];

List<String> listAll = new ArrayList<>();

for (int i = 0; i < array_before.length; i++) {

for (int j = 0; j < dataIndex.length; j++) {

               if (array_before[i].equals(dataIndex[j]))
                   listAll.add(data[Integer.parseInt(dataIndex[j])]);
           }
       }

for (int i = 0; i < array_after.length; i++) {

for (int j = 0; j < dataIndex.length; j++) {

               if (array_after[i].equals(dataIndex[j]))
                   listAll.add(data[Integer.parseInt(dataIndex[j])]);
           }
       }

return listAll;
}

   /**
   * 将str转成数组
   *
   * @param num
   * @return
   */
   private static String[] intArray(String num) {

// 新建一个数组用来保存num每一位的数字
String[] intArray = new String[num.length()];

for (int i = 0; i < num.length(); i++) {

           // 遍历str将每一位数字添加如intArray
           Character ch = num.charAt(i);
           intArray[i] = String.valueOf(ch.toString());
       }

return intArray;
}

   /**
   * 价格相加
   *
   * @return
   */
   public static String priceUtils(String priceAll, String price, String util) {
       double newPrice = 0d;

       priceAll = priceAll.replace("¥:", "");
       price = price.replace("¥:", "");

BigDecimal bd1 = new BigDecimal(priceAll);
BigDecimal bd2 = new BigDecimal(price);

       if ("+".equals(util))
           newPrice = bd1.add(bd2).doubleValue();
       else
           newPrice = bd1.subtract(bd2).doubleValue();

return String.valueOf(newPrice);
}

   /**
   * 价格加逗号，
   *
   * @param data 整型
   * @return
   */
   private static String formatTosepara(float data) {
       return "¥:" + new DecimalFormat("#,###.00").format(data);
   }
}

/**
*    字体
* @author think
*
*/
enum fontName {
   name_kt(1, "楷体");

   private Integer key;
   private String value;
   /**
   * 枚举必须使用私有化
   * @param key
   * @param value
   */
   private fontName(Integer key,String value) {
       this.key = key;
       this.value = value;
   }

   /** 获取key值 */
   public Integer getKey() {
       return key;
   }
   public void setKey(Integer key) {
       this.key = key;
   }

   /** 获取对应的key的描述 */
   public String getValue() {
       return value;
   }
   public void setValue(String value) {
       this.value = value;
   }
}
/**
*    字体大小
* @author think
*
*/
enum fontSize {
   name_size20(1, (short)20),
   name_size18(1, (short)18),
   name_size16(1, (short)16),
   name_size14(1, (short)14),
   name_size12(1, (short)12);

   private Integer key;
   private short value;
   /**
   * 枚举必须使用私有化
   * @param key
   * @param value
   */
   private fontSize(Integer key,short value) {
       this.key = key;
       this.value = value;
   }

   /** 获取key值 */
   public Integer getKey() {
       return key;
   }
   public void setKey(Integer key) {
       this.key = key;
   }

   /** 获取对应的key的描述 */
   public short getValue() {
       return value;
   }
   public void setValue(short value) {
       this.value = value;
   }
}