md_YOS/src/custom/restcontroller/sale/cashbill/PayExportExcel.java

package restcontroller.sale.cashbill;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.awt.Color;

public class PayExportExcel {
	 /**
     * 设置标题单元样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle1(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.RED.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorGrey = new XSSFColor(new Color(217, 225, 242));
        cellStyle.setFillForegroundColor(colorGrey);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    }
    /**
     * 设置中文提示信息样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle2(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorBlue = new XSSFColor(new Color(48, 84, 150));
        cellStyle.setFillForegroundColor(colorBlue);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    }
    /**
     * 设置中文提示信息样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle3(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorBlue = new XSSFColor(new Color(48, 84, 150));
        cellStyle.setFillForegroundColor(colorBlue);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居左
        return cellStyle;
    }

    /**
     * 设置正文单元样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createBodyCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
		font.setFontName("微软雅黑");// 设置标题字体
       // font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
        cellStyle.setFont(font);
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    } 
     /**
     * 2022-07-14 17:41:39
               * 设置表格宽度（导入模板）
     * **/
    public static void setBatchDetailSheetColumn1(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 5500);// 设置 经销商编码 宽度
        sheet.setColumnWidth((short) 1, (short) 5500);// 设置 账户名称 宽度
        sheet.setColumnWidth((short) 2, (short) 5500);// 设置 金额 宽度
        sheet.setColumnWidth((short) 3, (short) 5500);// 设置 分类 宽度
        sheet.setColumnWidth((short) 4, (short) 5500);// 设置 分类明细 宽度
        sheet.setColumnWidth((short) 5, (short) 5500);// 设置 单据日期 宽度
        sheet.setColumnWidth((short) 6, (short) 5500);// 设置 单据日期 宽度
        sheet.setColumnWidth((short) 7, (short) 11000);// 设置 备注 宽度
    }
    
    
    /**
     * 2022-07-14 17:41:39
              * 设置表格宽度(返回错误Excel的样式)
     * **/
    public static void setBatchDetailSheetColumn2(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 5500);// 设置 经销商编码 宽度
        sheet.setColumnWidth((short) 1, (short) 5500);// 设置 账户名称 宽度
        sheet.setColumnWidth((short) 2, (short) 5500);// 设置 金额 宽度
        sheet.setColumnWidth((short) 3, (short) 5500);// 设置 分类 宽度
        sheet.setColumnWidth((short) 4, (short) 5500);// 设置 分类明细 宽度
        sheet.setColumnWidth((short) 5, (short) 5500);// 设置 单据日期 宽度
        sheet.setColumnWidth((short) 6, (short) 11000);// 设置 备注 宽度
        sheet.setColumnWidth((short) 7, (short) 8000);// 设置 错误信息 宽度
        sheet.setColumnWidth((short) 8, (short) 8000);// 设置 错误信息 宽度
    }
    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * cellStyle1 中文提示信息样式
     * cellStyle2 标题提示信息样式
     * cellStyle3 正文提示信息样式
     * **/
    public static void batchDetail(XSSFSheet sheet, XSSFCellStyle cellStyle1, XSSFCellStyle cellStyle2, XSSFCellStyle cellStyle3, XSSFCellStyle cellStyle4,XSSFWorkbook workbook) {
//        HSSFCellStyle bcs = ExportExcel.createTitleCellStyle1(workbook);
//        bcs.setBorderBottom(BorderStyle.THIN); //下边框
//        bcs.setBorderLeft(BorderStyle.THIN);//左边框
//        bcs.setBorderTop(BorderStyle.THIN);//上边框
//        bcs.setBorderRight(BorderStyle.THIN);//右边框
//        bcs.setWrapText(true);


        XSSFRow row = null;
        XSSFCell cell = null;

        cellStyle1.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle1.setWrapText(true);
        
        cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle2.setWrapText(true);

        DataFormat format = workbook.createDataFormat();
        cellStyle1.setDataFormat(format.getFormat("@"));
        cellStyle2.setDataFormat(format.getFormat("@"));
        cellStyle3.setDataFormat(format.getFormat("@"));
        cellStyle4.setDataFormat(format.getFormat("@"));

   	 /*第一行*/
       sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

       /*第一行塞值*/
       row = sheet.createRow(0);
       cell = row.createCell(0);// ID
       cell.setCellStyle(cellStyle4);
       cell.setCellValue("支出凭证导入");
       /*第二行*/
       sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
       /*第二行塞值*/
       row = sheet.createRow(1);
       cell = row.createCell(0);// ID
       cell.setCellStyle(cellStyle1);
       cell.setCellValue("注意：①请按照以下示例填写支出凭证；②支出凭证最多不超过5000行；③支出凭证导入前，请记得删除示例行！");
       /*第三行塞值*/

        row = sheet.createRow(2);
       cell = row.createCell(0);
       cell.setCellStyle(cellStyle2);
       cell.setCellValue("经销商编码（必填）");

       cell = row.createCell(1);
       cell.setCellStyle(cellStyle2);
       cell.setCellValue("账户名称（必填）");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("支出金额（必填）");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("优惠金额");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("分类（必填）");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("分类明细");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("单据日期");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("备注");
       

        /*第四行塞值*/
        row = sheet.createRow(3);

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("0000");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("现金账户");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("0");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("货款");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("分类明细");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("2024-07-01");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("备注");
        
    }
    
    
    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * **/
    public static void batchDetailErr(XSSFSheet sheet, XSSFCellStyle cellStyle1,XSSFCellStyle cellStyle2,XSSFCellStyle cellStyle3,XSSFWorkbook workbook) {
//        HSSFCellStyle bcs = ExportExcel.createTitleCellStyle1(workbook);
//        bcs.setBorderBottom(BorderStyle.THIN); //下边框
//        bcs.setBorderLeft(BorderStyle.THIN);//左边框
//        bcs.setBorderTop(BorderStyle.THIN);//上边框
//        bcs.setBorderRight(BorderStyle.THIN);//右边框
//        bcs.setWrapText(true);


        XSSFRow row = null;
        XSSFCell cell = null;

        cellStyle1.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle1.setWrapText(true);
        
        

      	 /*第一行*/
          sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

          /*第一行塞值*/
          row = sheet.createRow(0);
          cell = row.createCell(0);// ID
          cell.setCellStyle(cellStyle3);
          cell.setCellValue("支出凭证导入");
          /*第二行*/
          sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
          /*第二行塞值*/
          row = sheet.createRow(1);
          cell = row.createCell(0);// ID
          cell.setCellStyle(cellStyle1);
          cell.setCellValue("注意：①请按照以下示例填写支出凭证；②支出凭证最多不超过5000行；③支出凭证导入前，请记得删除示例行！");
          /*第三行塞值*/
          row = sheet.createRow(2);

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("经销商编码（必填）");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("账户名称（必填）");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("支出金额（必填）");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("优惠金额");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("分类（必填）");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue(" 分类明细");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue(" 单据日期");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("备注");

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle2);
          cell.setCellValue("错误信息");         
      
    }
}