ExcelUtil 工具类

最新推荐文章于 2024-07-28 09:45:40 发布

丁丁的码农日志

最新推荐文章于 2024-07-28 09:45:40 发布

阅读量461

点赞数

文章标签： java 算法 c++

本文链接：https://blog.csdn.net/weixin_44479146/article/details/128534384

版权

public class ExcelUtil {
    /**
     intPosition：复制到指定的行数
     **/
    public static void copyRows(XSSFWorkbook wb, XSSFSheet sourceSheet,
                                XSSFSheet targetSheet, int intStartRow, int intEndRow, int intPosition) {

        // EXECL中的行是从1开始的,而POI中是从0开始的,所以这里要减1.

        int pStartRow = intStartRow - 1;
        int pEndRow = intEndRow - 1;
        int pPosition = intPosition - 1;
        XSSFRow sourceRow = null;
        XSSFRow targetRow = null;
        XSSFCell sourceCell = null;
        XSSFCell targetCell = null;
//			 Sheet sourceSheet = null;
//			Sheet targetSheet = null;
        CellRangeAddress region = null;
        CellType cType;
        int i;
        int j;
        int targetRowFrom;
        int targetRowTo;


        if ((pStartRow == -1) || (pEndRow == -1)) {

            return;

        }

//			 sourceSheet = wb.getSheetAt(pSourceSheet);
//			 targetSheet = wb.getSheetAt(pTargetSheet);

        //拷贝合并的单元格
        for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {

            region = sourceSheet.getMergedRegion(i);
            if ((region.getFirstRow() >= pStartRow)&&(region.getLastRow() <= pEndRow)) {

                targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
                targetRowTo = region.getLastRow() - pStartRow + pPosition;
//					 region.setFirstRow(targetRowFrom);
//					 region.setLastRow(targetRowTo);
                CellRangeAddress targetregion = new CellRangeAddress(targetRowFrom,targetRowTo,region.getFirstColumn(),region.getLastColumn());
                targetSheet.addMergedRegion(targetregion);
            }
        }

        //设置列宽
        for (i = pStartRow; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
                    targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
                    targetSheet.setColumnHidden(j, false);
                }
                break;
            }
        }

        //拷贝行并填充数据
        for (; 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);
                switch (cType) {
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                    case ERROR:
                        targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                        break;
                    case FORMULA:
                        targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
                        break;
                    case _NONE:
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case STRING:
                        targetCell.setCellValue(sourceCell.getRichStringCellValue());
                        break;
                    case BLANK:
                        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();
    }
}