poiutil

package com.miaozhen.sitedna.poi;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.contrib.HSSFCellUtil;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class PoiUtil {
    public static void setRegionStyle(HSSFSheet sheet, Region region, HSSFCellStyle cs) {
        int toprowNum = region.getRowFrom();
        for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

    public static void colSpan(HSSFSheet sheet, short rownum) {
        short fromIndex = 0, toIndex = 0;
        HSSFRow row = sheet.getRow(rownum);
        String v = "";
        for (short i = 0; i < row.getLastCellNum(); i++) {
            HSSFCell cell = row.getCell(i);
            if (cell != null
                    && (cell.getCellType() != HSSFCell.CELL_TYPE_STRING || !cell.getStringCellValue().equals(v))) {
                if (toIndex > fromIndex) {
                    Region region = new Region(rownum, fromIndex, rownum, toIndex);
                    sheet.addMergedRegion(region);
                }
                fromIndex = i;
                v = cell.getStringCellValue();
            } else {
                toIndex = i;
            }
        }
        if (toIndex > fromIndex) {
            Region region = new Region(rownum, fromIndex, rownum, toIndex);
            sheet.addMergedRegion(region);
        }
    }

    public static void rowSpan(HSSFSheet sheet, short column) {
        short fromIndex = 0, toIndex = 0;
        String v = "";
        for (short i = 0; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = row != null ? row.getCell(column) : null;
            if (cell != null
                    && (cell.getCellType() != HSSFCell.CELL_TYPE_STRING || !cell.getStringCellValue().equals(v))) {
                if (toIndex > fromIndex) {
                    Region region = new Region(fromIndex, column, toIndex, column);
                    sheet.addMergedRegion(region);
                }
                fromIndex = i;
                v = cell.getStringCellValue();
            } else {
                toIndex = i;
            }
        }
        if (toIndex > fromIndex) {
            Region region = new Region(fromIndex, column, toIndex, column);
            sheet.addMergedRegion(region);
        }
    }

    // toColumn设置为255则表示到最后一列
    public static void colSpan(HSSFSheet sheet, int rownum, short startColumn, short toColumn) {
        short fromIndex = 0, toIndex = 0;
        HSSFRow row = sheet.getRow(rownum);
        if (toColumn > row.getLastCellNum()) {
            toColumn = row.getLastCellNum();
        }
        String v = "";
        for (short i = 0; i < toColumn; i++) {
            HSSFCell cell = row.getCell(i);
            if (cell != null
                    && (cell.getCellType() != HSSFCell.CELL_TYPE_STRING || !cell.getStringCellValue().equals(v))) {
                if (toIndex > fromIndex) {
                    Region region = new Region(rownum, fromIndex, rownum, toIndex);
                    sheet.addMergedRegion(region);
                }
                fromIndex = i;
                v = cell.getStringCellValue();
            } else {
                toIndex = i;
            }
        }
        if (toIndex > fromIndex) {
            Region region = new Region(rownum, fromIndex, rownum, toIndex);
            sheet.addMergedRegion(region);
        }
    }

    public static void rowSpan(HSSFSheet sheet, short column, int startRow, int toRow) {
        int fromIndex = 0, toIndex = 0;
        if (toRow > sheet.getLastRowNum()) {
            toRow = sheet.getLastRowNum();
        }
        String v = "";
        for (int i = startRow; i <= toRow; i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = row != null ? row.getCell(column) : null;
            if (cell != null
                    && (cell.getCellType() != HSSFCell.CELL_TYPE_STRING || !cell.getStringCellValue().equals(v))) {
                if (toIndex > fromIndex) {
                    Region region = new Region(fromIndex, column, toIndex, column);
                    sheet.addMergedRegion(region);
                }
                fromIndex = i;
                v = cell.getStringCellValue();
            } else {
                toIndex = i;
            }
        }
        if (toIndex > fromIndex) {
            Region region = new Region(fromIndex, column, toIndex, column);
            sheet.addMergedRegion(region);
        }
    }

    public static void clearSheetBody(HSSFSheet sheet, int fromrow) {
        // 清除模板中的数据
        for (int i = fromrow; i <= sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i) != null) {
                sheet.removeRow(sheet.getRow(i));
            }
        }
    }

    public static void clearSheetBody(HSSFSheet sheet, int fromRow, int toRow) {
        // 清除模板中的数据
        for (int i = fromRow; i <= toRow; i++) {
            if (sheet.getRow(i) != null) {
                sheet.removeRow(sheet.getRow(i));
            }
        }
    }

    // 清除所有的区域
    public static void clearAllRegion(HSSFSheet sheet) {
        int n = sheet.getNumMergedRegions();
        for (int i = n; i >= 0; i--) {
            sheet.removeMergedRegion(i);
        }
    }
/**
 *
 * @param sheet 区域所在的sheet
 * @param rownum 指定单元格所在行
 * @param column 指定单元格所在列   
 *  删除指定单元格所在的区域
 */
    public static void clearRegionAssign(HSSFSheet sheet, int rownum, int column) {
        int n = sheet.getNumMergedRegions();
        for (int i = n; i >= 0; i--) {
            Region region = sheet.getMergedRegionAt(i);
            if (region.getRowFrom() <= rownum && region.getRowTo() >= rownum && region.getColumnFrom() <= column
                    && region.getColumnTo() >= column) {
                sheet.removeMergedRegion(i);
                break;
            }
        }
    }

    public static void main(String[] args) {
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("输入需要操作的excel文件路径：");
            String filePath = br.readLine();
            System.out.print("输入需要操作的来源sheet名称：");
            String sourceSheetName = br.readLine();
            System.out.print("输入来源的起始行号（从0开始）：");
            int startRow = Integer.parseInt(br.readLine());
            System.out.print("输入来源的终止行号（从0开始）：");
            int endRow = Integer.parseInt(br.readLine());
            System.out.print("输入需要操作的目标sheet名称：");
            String targetSheetName = br.readLine();
            System.out.print("输入目标的行号偏移量：");
            int position = Integer.parseInt(br.readLine());

            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
            HSSFWorkbook wb = new HSSFWorkbook(fs);

            // copyRows(wb, sourceSheetName, targetSheetName, startRow, endRow, position);
            FileOutputStream fileOut = new FileOutputStream(filePath);
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            System.out.println("操作完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void clearAllComment(HSSFSheet sheet) {
        /*
         * int rowIndex = sheet.getLastRowNum(); int colIndex = sheet.getRow(0).getLastCellNum(); for(int i=0;i<=rowIndex;i++){
         * for(int j=0;j<=colIndex;j++){ HSSFComment comment = sheet.getCellComment(i, j); if(comment!=null){
         * sheet.getRow(i).getCell(j).removeCellComment(); } } }
         */
    }

    public static void copyRows(HSSFSheet sourceSheet, HSSFSheet targetSheet, 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;

        if ((pStartRow == -1) || (pEndRow == -1)) {
            return;
        }
        // sourceSheet = wb.getSheetAt(pSourceSheetId);
        // targetSheet = wb.getSheetAt(pTargetSheetId);
        // 拷贝合并的单元格
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            region = sourceSheet.getMergedRegionAt(i);
            if ((region.getRowFrom() >= pStartRow) && (region.getRowTo() <= pEndRow)) {
                int targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
                int targetRowTo = region.getRowTo() - pStartRow + pPosition;
                region.setRowFrom(targetRowFrom);
                region.setRowTo(targetRowTo);
                targetSheet.addMergedRegion(region);
            }
        }
        // 拷贝行并填充数据
        for (int i = pStartRow; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) {
                continue;
            }
            targetRow = targetSheet.createRow(i - pStartRow + pPosition);
            targetRow.setHeight(sourceRow.getHeight());
            for (short j = sourceRow.getFirstCellNum(); j < sourceRow.getPhysicalNumberOfCells(); j++) {
                sourceCell = sourceRow.getCell(j);
                if (sourceCell == null) {
                    continue;
                }
                targetCell = targetRow.createCell(j);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                int cType = sourceCell.getCellType();
                targetCell.setCellType(cType);
                switch (cType) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    // System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                    // System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    targetCell.setCellFormula(sourceCell.getCellFormula());
                    // System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                    // System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
                    // System.out.println("--------TYPE_STRING:" + targetCell.getStringCellValue());
                    break;
                }
            }
        }
    }
}