摘要：一般訂單打印紙高度毫米字母或半角字符判斷是否為全角全角符號及中文連續出現，認為是單詞組成部分的字符和間單頁的票據右端聯頁寬占用的寬度回退一行每減一次少一行實際高度的距離補足行高拉高的行內容要調整使用靠上對齊補充

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

/**
 * Created by chengxiaoqi1 on 15/9/24.
 */
public class ExcelUtils {
    static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    public final static int PAGE_HEIGHT_DEFAULT = 140;  //一般訂單打印紙高度 140毫米


//    static Pattern PAT_ASC = Pattern.compile("^[A-Za-z0-9 -./+]+$");  //字母或半角字符
//    static Pattern PAT_FULL_1 = Pattern.compile("^[u4e00-u9fa5]+$");  //判斷是否為全角
//    static Pattern PAT_FULL_2 = Pattern.compile("[^x00-xff]$");  //全角符號及中文
    static Pattern PAT_PARTNER = Pattern.compile("^[A-Za-z0-9]$");  //連續出現，excel認為是單詞組成部分的字符

    /**
     * startRow和endRow間單頁的票據右端聯
     *
     * @param outputSheet
     * @param startRow
     * @param endRow
     * @param PAGE_HEIGHT
     * @param MAX_COL
     */
    public static void noteOnRightSideOne(HSSFSheet outputSheet, int startRow, int endRow, int PAGE_HEIGHT, int MAX_COL, String ticketInfo, int needHeight) {
        int prefixHeight = (PAGE_HEIGHT - needHeight) / 2; //頁寬-占用的寬度
        startRow--; //回退一行
        while (prefixHeight > 0 && startRow < endRow) {
            startRow++;
            prefixHeight -= outputSheet.getRow(startRow).getHeight();//每減一次少一行實際高度的距離
        }
        int toRow = startRow;

        while (needHeight > 0 && toRow < endRow) {
            toRow++;
            needHeight -= outputSheet.getRow(toRow).getHeight();
        }
        //補足行高
        if (needHeight > 0) {
            short beHeight = (short) (outputSheet.getRow(endRow).getHeight() + needHeight);
            HSSFRow row = outputSheet.getRow(endRow);
            row.setHeight(beHeight);
            //拉高的行內容要調整使用靠上對齊
            for (int i = 0; i < row.getLastCellNum(); i++) {
                row.getCell(i).getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
            }
        }
        //補充不存在的單元格
        for (int i = startRow; i <= toRow; i++) {
            HSSFRow aRow = outputSheet.getRow(i);
            if (aRow == null) {
                aRow = outputSheet.createRow(i);
            }
            HSSFCell aCell = aRow.getCell(MAX_COL);
            if (aCell == null) {
                aRow.createCell(MAX_COL);
            }
        }
        HSSFCell  aCell=outputSheet.getRow(startRow).getCell(MAX_COL);
        aCell.setCellValue(ticketInfo);
        HSSFCellStyle style =  outputSheet.getWorkbook().createCellStyle();
        style.cloneStyleFrom(aCell.getCellStyle());
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setBorderLeft(HSSFCellStyle.BORDER_HAIR);
        style.setWrapText(true);
        HSSFFont font =  outputSheet.getWorkbook().createFont(); //style.getFont(outputSheet.getWorkbook());
        font.setFontHeightInPoints((short) 8);
        font.setFontName("宋體");
        style.setFont(font);
        aCell.setCellStyle(style);

        style = outputSheet.getWorkbook().createCellStyle();
        style.cloneStyleFrom(aCell.getCellStyle());
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        for (int y = startRow; y <= toRow; y++) {
            outputSheet.getRow(y).getCell(MAX_COL).setCellStyle(style);
        }
        CellRangeAddress note = new CellRangeAddress(startRow, toRow, MAX_COL, MAX_COL);
        outputSheet.addMergedRegion(note);
        outputSheet.setColumnWidth(MAX_COL, 600);
    }

    /**
     * minRow和maxRow間多頁的票據右端聯
     *
     * @param outputSheet
     * @param minRow
     * @param maxRow
     * @param PAGE_HEIGHT
     * @param MAX_COL
     */
    public static void noteOnRightSide(int minRow, int maxRow, int PAGE_HEIGHT, int MAX_COL, HSSFSheet outputSheet,String ticketInfo, int needHeight) {
        int[] breaks = outputSheet.getRowBreaks();
        boolean minAdded = false;
        boolean maxAdded = false;
        List breakLists = new ArrayList<>();
        if (breaks != null && breaks.length >= 0) {
            for (int i = 0; i < breaks.length; i++) {
                if (breaks[i] >= minRow) {
                    if (!minAdded) {
                        breakLists.add(minRow - 1);
                        minAdded = true;
                    }
                    breakLists.add(breaks[i]);
                } else if (breaks[i] >= maxRow) {
                    if (!maxAdded) {
                        breakLists.add(maxRow);
                        maxAdded = true;
                        break;
                    }
                }
            }
        }
        if (!minAdded) {
            breakLists.add(minRow - 1);
        }
        if (!maxAdded) {
            breakLists.add(maxRow);
        }

        int startBreakIndex = 0;
        while (startBreakIndex < breakLists.size() - 1) {
            noteOnRightSideOne(outputSheet, breakLists.get(startBreakIndex) + 1, breakLists.get(startBreakIndex + 1), PAGE_HEIGHT, MAX_COL,ticketInfo, needHeight);
            startBreakIndex++;
        }
    }

    /**
     * 獲取模板中的行數
     *
     * @param sourceSheet
     * @return
     */
    public static int findRowCount(HSSFSheet sourceSheet) {
        int count = 0;
        int maxRow=sourceSheet.getLastRowNum();
        while (count < maxRow) {
            if ("END".equals(sourceSheet.getRow(count).getCell(0).getStringCellValue())) {
                count--;
                break;
            }
            count++;
        }
        if(count==maxRow){
            count--;
        }
        return count;
    }

    /**
     * 獲取模板中最后頁應包含的最少的行數
     * @param sourceSheet
     * @param maxRows 模板的最大行數
     * @return 沒找到，返回 -1
     */
    public static int findLastTableMinRow(HSSFSheet sourceSheet, int maxRows) {
        int count = 0;
        int maxRow=sourceSheet.getLastRowNum();
        while (count < maxRow) {
            HSSFRow aRow=sourceSheet.getRow(count);
            if (aRow != null) {
                HSSFCell aCell=aRow.getCell(0);
                if(aCell!=null && "LST".equals(aCell.getStringCellValue())){
                    aCell.setCellValue("");
                    count--;
                    break;
                }
            }
            count++;
        }
        if(count==maxRow){
            count=1;
        }else{
            count=maxRows-count;
        }
        return count;
    }

    /**
     * 獲取模板中標識聯的列位置
     * @param sourceSheet
     * @param row
     * @return 沒找到，返回-1
     */
    public static int findMarkCol(HSSFSheet sourceSheet,int row) {
        HSSFRow aRow=sourceSheet.getRow(row);
        if(aRow==null) return -1;
        int maxCol=aRow.getLastCellNum();
        int count = 0;
        while (count < maxCol) {
            HSSFCell aCell=aRow.getCell(count);
            if(aCell!=null && "MARK_COL".equals(aCell.getStringCellValue())){
                break;
            }
            count++;
        }
        if(maxCol==count){
            count=-1;
        }
        return count;
    }


    /**
     * 復制行內內容
     *
     * @param wb
     * @param pSourceSheetName
     * @param pTargetSheetName
     * @param pStartRow
     * @param pEndRow
     * @param pPosition
     */
    public static void copyRows(HSSFWorkbook wb, String pSourceSheetName,
                                String pTargetSheetName, int pStartRow, int pEndRow, int pPosition) {
        HSSFRow sourceRow = null;
        HSSFRow targetRow = null;
        HSSFCell sourceCell = null;
        HSSFCell targetCell = null;
        HSSFSheet sourceSheet = null;
        HSSFSheet targetSheet = null;
        Region region = null;
        int cType;
        int i;
        int j;
        int targetRowFrom;
        int targetRowTo;

        if ((pStartRow == -1) || (pEndRow == -1)) {
            return;
        }
        sourceSheet = wb.getSheet(pSourceSheetName);
        targetSheet = wb.getSheet(pTargetSheetName);
        // 拷貝合并的單元格
        for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            region = sourceSheet.getMergedRegionAt(i);
            if ((region.getRowFrom() >= pStartRow)
                    && (region.getRowTo() <= pEndRow)) {
                targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
                targetRowTo = region.getRowTo() - pStartRow + pPosition;
                region.setRowFrom(targetRowFrom);
                region.setRowTo(targetRowTo);
                targetSheet.addMergedRegion(region);
            }
        }
        // 設置列寬
        if (pPosition == 0) {
            for (j = 0; j < 100; j++) {
                //log.debug("==================>>" + j + sourceSheet.getColumnWidth(j));
                targetSheet.setColumnHidden(j, false);
                targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
            }
        }
        // 拷貝行并填充數據
        for (i = pStartRow; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) {
                continue;
            }
            targetRow = targetSheet.createRow(i - pStartRow + pPosition);
            targetRow.setHeight(sourceRow.getHeight());
            for (j = sourceRow.getFirstCellNum(); j < sourceRow
                    .getPhysicalNumberOfCells(); j++) {
                sourceCell = sourceRow.getCell(j);
                if (sourceCell == null) {
                    continue;
                }
                targetCell = targetRow.createCell(j);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                cType = sourceCell.getCellType();
                targetCell.setCellType(cType);
                log.debug("width:----->"+i+","+targetSheet.getColumnWidth(2)+"");
                switch (cType) {
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        targetCell
                                .setCellErrorValue(sourceCell.getErrorCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        // parseFormula這個函數的用途在后面說明
                        targetCell.setCellFormula(parseFormula(sourceCell
                                .getCellFormula()));
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        targetCell
                                .setCellValue(sourceCell.getRichStringCellValue());
                        break;
                }

            }

        }
    }

    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result = null;
        int index;

        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index
                    + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }
        return result.toString();
    }


    /**
     * 對sheet做分頁處理，右邊加上聯標識
     * @param sheet 要處理的sheet對象
     * @param firstRow sheet中哪一行開始算本頁第一行
     * @param PAGE_HEIGHT  頁高，一般以標準每行280單位計數
     * @param lastPageMinRow  最后頁應該有的最少行數
     */
    public static void commonPageDeal(HSSFSheet sheet, int firstRow, final int PAGE_HEIGHT, int lastPageMinRow, int markCol, String ticketInfo, int needHeight){
        int aPageHeight=0;
        int lastPageBreak=firstRow-1;
        int cRowCount=sheet.getLastRowNum();
        for(int i=firstRow;i<=cRowCount;i++){
            short rowHeight=sheet.getRow(i).getHeight();
            aPageHeight += rowHeight;
            if(aPageHeight > PAGE_HEIGHT){
                lastPageBreak = i-1;
                sheet.setRowBreak(lastPageBreak);
                aPageHeight = rowHeight;
            }
        }
        //每單最后頁內容必須有指定行數的內容
        if(lastPageMinRow>1 && cRowCount-firstRow>lastPageMinRow && cRowCount-lastPageBreak0) {
            ExcelUtils.noteOnRightSide(firstRow, cRowCount, PAGE_HEIGHT, markCol, sheet, ticketInfo, needHeight);
        }
    }

    public static void styleMoneyCell(Cell moneyCell){
        if(moneyCell==null) return;
        CellStyle style=moneyCell.getCellStyle();
        style.setDataFormat((short)731);
    }


    public static void styleGoodsNameCell(Cell goodsNameCell){
        if(goodsNameCell==null) return;
        CellStyle style=goodsNameCell.getCellStyle();
        style.setShrinkToFit(true);
    }
    public static Integer getExcelCellAutoHeight(String str, float fontCountInline) {
        float contentLength = 0.00f;
        for (int i = 0; i < str.length(); i++) {
            float ff = getregex(str.substring(i, i + 1));
            contentLength = contentLength + ff;
        }
            return ((int) (contentLength / fontCountInline) + 1);//計算
    }

    public static float getregex(String charStr) {
        return charStr.getBytes().length==1?0.5f:1.0f;
//        // 判斷是否為字母或字符
//        if (PAT_ASC.matcher(charStr).matches()) {
//            return 0.5f;
//        }
        // 判斷是否為全角
//        if (PAT_FULL_1.matcher(charStr).matches()) {
//            return 1.00f;
//        }
//        //全角符號 及中文
//        if (PAT_FULL_2.matcher(charStr).matches()) {
//            return 1.00f;
//        }
//        return 0.5f;
    }

    public static void insertRow(HSSFSheet sheet, int startRow, int rows) {
        sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false);
        HSSFRow sourceRow = sheet.getRow(startRow + rows); //模板行
        HSSFRow targetRow = null;
        HSSFCell targetCell = null;
        HSSFCell sourceCell = null;
        for (int i = 0; i < rows; i++) {
            targetRow = sheet.createRow(startRow + i);
            targetRow.setHeight(sourceRow.getHeight());

            for (int m = sourceRow.getFirstCellNum(); m < sourceRow.getPhysicalNumberOfCells(); m++) {
                targetCell = targetRow.createCell(m);
                sourceCell = sourceRow.getCell(m);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                targetCell.setCellType(sourceCell.getCellType());
            }
        }
    }

    public static void getFile(String fileName,HSSFWorkbook wbModule,HttpServletResponse response,Logger logger){
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        String time = sdf.format(date);
        String filename = time + fileName;
        try {
            //頁面 download
            // 清空response
            response.reset();
            // 設置response的Header
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            ServletOutputStream sos = response.getOutputStream();
            wbModule.write(sos);
            sos.flush();
            sos.close();
        } catch (Exception e) {
            logger.error("",e);
        }
    }

    /**
     *將text在該換行處插入空格，使能自動換行
     * @param text 需要處理的文本
     * @param lineLen 一行顯示的最多中文字符數
     * @return 返回List 第一個元素：在換行處加上空格的商品名稱 第二個元素:包含的行數
     */
    public static List wrapText(String text,float lineLen){
        if(text==null || "".equals(text)){
            text=" ";
        }
        StringBuffer buffer = new StringBuffer();
        int rowCount = 0;
        float count = 0;
        boolean lastCharPart = false;
        boolean curCharPart;
        int lastPartIndex =0;
        for (int i=0;i=lineLen) { //當前字符超過一行可顯示的字符
                /**
                 * 當前字符號上一個字符同時滿足條件
                 * 添加空格處理
                 */
                if(curCharPart && lastCharPart){
                    int cj = 0; //需要補足的空格數
                    for(int j=i;j= lineLen){
                        throw new RuntimeException("超長單詞，暫不支持");
                    }
                    for(int jj=0;jj 0){
            rowCount++;
        }
        List