Poi拷贝Excel Sheet(解决合并单元格计数问题)_poi excel导出合并单元格计数不对,-优快云博客

本文提供了一种使用Apache POI库在Java中批量复制Excel工作表的方法，包括复制合并单元格、调整列宽以及处理单元格值。通过此方法，可以实现对多个工作表的一次性复制，并确保所有工作表的格式和内容得到正确地复制。重点在于处理合并单元格的计数问题及合并单元格的样式复制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
                            
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.Region;
                            
                            
/**
 * 可运行 成功复制sheet！！！
 * @author lch
 *
 */
public class CopyExcelSheet{
                            
    public static void main(String[] args) throws FileNotFoundException, IOException {
        String fromPath = "D://excel/caca.xls";// excel存放路径
        String toPath = "D:\\ok\\lala.xls";// 保存新EXCEL路径
        // 创建新的excel
        HSSFWorkbook wbCreat = new HSSFWorkbook();
                            
            // 打开已有的excel
            InputStream in = new FileInputStream(fromPath);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            for (int ii = 0; ii < wb.getNumberOfSheets(); ii++) {
                HSSFSheet sheet = wb.getSheetAt(ii);     
                int rowNum1 = sheet.getLastRowNum();
                HSSFRow row1 = sheet.getRow(0);
                   if(isBlankRow(row1,rowNum1,10)==true)  {                    
                   }  
                   else{    
                HSSFSheet sheetCreat = wbCreat.createSheet(wb.getSheetName(ii));
                // 复制源表中的合并单元格
                MergerRegion(sheetCreat, sheet);
                int firstRow = sheet.getFirstRowNum();
                int lastRow = sheet.getLastRowNum();
                for (int i = firstRow; i <= lastRow; i++) {
                    // 创建新建excel Sheet的行
                    HSSFRow rowCreat = sheetCreat.createRow(i);
                    // 取得源有excel Sheet的行
                    HSSFRow row = sheet.getRow(i);
               if(isBlankRow(row,rowNum1,10)==true)  {     
               }   
               else{ // 单元格式样       
                    int firstCell = row.getFirstCellNum();
                    int lastCell = row.getLastCellNum();
                    for (int j = firstCell; j < lastCell; j++) {
                        // 自动适应列宽 貌似不起作用
                       sheetCreat.autoSizeColumn(j);
                        System.out.print(row.getCell(j)+"   ");
                        HSSFCell a= rowCreat.createCell(j);
                        String strVal ="";
                        if (row.getCell(j)==null) {  
                        }else{
                             strVal = getCellValue(row.getCell(j)).toString();
                             a.setCellValue(strVal);
                        }                      
                    }
               System.out.println();     
               }
                }
            }
        }
        FileOutputStream fileOut = new FileOutputStream(toPath);
        wbCreat.write(fileOut);
        fileOut.close();
    }
                             
                                
    /**
     * 判断 行 是否为空方法
     * @param row
     * @return
     */
    public static boolean isBlankRow(HSSFRow row, int rowNum, int colNum) {
        boolean b = true;
        if (row == null) {
            b = true;
        } else {
            for (int i = 0; i < colNum; i++) {
                HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    continue;
                } else {
                    String value = getCellValue(cell).toString();
                    if (value.length()!=0) {
                        b = false;
                    }
                }
            }
        }
        return b;
    }
                                
    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     * @param sheetCreat 新创建sheet
     * @param sheet 原有的sheet
     */
    private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            Region mergedRegionAt = sheet.getMergedRegionAt(i);
            sheetCreat.addMergedRegion(mergedRegionAt);
        }
                            
    }
                            
    /**
     * 去除字符串内部空格
     */
    public static String removeInternalBlank(String s) {
        // System.out.println("bb:" + s);
        Pattern p = Pattern.compile("\\s*|\t|\r|\n");
        Matcher m = p.matcher(s);
        char str[] = s.toCharArray();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < str.length; i++) {
            if (str[i] == ' ') {
                sb.append(' ');
            } else {
                break;
            }
        }
        String after = m.replaceAll("");
        return sb.toString() + after;
    }
                                
    /**
     * POI取得Excel单元格的值
     * @param cell
     * @return
     * @throws IOException
     */
    private static Object getCellValue(HSSFCell cell) {
        Object value = "";
        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            value = cell.getRichStringCellValue().toString();
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = (Date) cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf.format(date);
            } else {
                double value_temp = (double) cell.getNumericCellValue();
                BigDecimal bd = new BigDecimal(value_temp);
                BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
                value = bd1.doubleValue();
                DecimalFormat format = new DecimalFormat("#0.###");
                value = format.format(cell.getNumericCellValue());
            }
        }
        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            value = "";
        }
        if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
            try {
                value = String.valueOf(cell.getStringCellValue());
            } catch (IllegalStateException e) {
                value = String.valueOf(cell.getNumericCellValue());
            }
        }
        return value;
    }
                                
}

可完全拷贝，电脑上能正常显示，但合并单元格计数仍有问题，本来每个合并单元格的计数为1，此方法拷贝后计数为合并数。手机端显示时合并单元格会的后面会添加空格。

修正后的方法：拷贝时把单元格的HSSFCellStyle也拷过去

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
          
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.util.CellRangeAddress;
          
          
public class PoiCopyExcelSheet {
    /**
     *测试方法
     * @param args
     */
    public static void main(String[] args){
        String fromPath = "D://excel/新版20.xls";// excel存放路径
        String toPath = "D:\\ok\\lulala.xls";// 保存新EXCEL路径
        InputStream in;
        try {
            in = new FileInputStream(fromPath);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            HSSFSheet sheet = wb.getSheetAt(1);             
            HSSFWorkbook wbCreat = new HSSFWorkbook();
            HSSFSheet newSheet=wbCreat.createSheet(sheet.getSheetName());
            copySheets( newSheet, sheet,true); 
            FileOutputStream fileOut = new FileOutputStream(toPath);
            wbCreat.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
                  
        }
              
    /**
     * 拷贝sheet
     * @param newSheet  新的sheet
     * @param sheet     原有的sheet
     * @param copyStyle
     */
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){  
        int maxColumnNum = 0;  
        Map<Integer, HSSFCellStyle> styleMap = (copyStyle)? new HashMap<Integer, HSSFCellStyle>() : null;  
               MergerRegion(newSheet,sheet);
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {  
            HSSFRow srcRow = sheet.getRow(i);  
            HSSFRow destRow = newSheet.createRow(i);  
            if (srcRow != null) {  
                copyRow(sheet, newSheet, srcRow, destRow, styleMap);  
                if (srcRow.getLastCellNum() > maxColumnNum) {  
                    maxColumnNum = srcRow.getLastCellNum();  
                }  
            }  
        }  
        for (int i = 0; i <= maxColumnNum; i++) {  
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));  
        }  
    }  
            
    /**
     * 拷贝行
     * @param srcSheet   原有的sheet
     * @param destSheet  新的sheet 
     * @param srcRow    原有行
     * @param destRow    新的行
     * @param styleMap
     */
    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {  
        destRow.setHeight(srcRow.getHeight());  
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {  
            HSSFCell oldCell = srcRow.getCell(j);  
            HSSFCell newCell = destRow.getCell(j);  
            if (oldCell != null) {  
                if (newCell == null) {  
                    newCell = destRow.createCell(j);  
                }  
                copyCell(oldCell, newCell, styleMap);  
             // MergerRegion(destSheet,srcSheet);
            }  
        }  
                    
    }  
              
              
              
    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     * @param sheetCreat 新创建sheet
     * @param sheet 原有的sheet
     */
    private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {
        int rownum=sheet.getLastRowNum();
        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            oldRanges.add(sheet.getMergedRegion(i));
        }
        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (int k = 0; k < oldRanges.size(); k++) {
            CellRangeAddress oldRange = oldRanges.get(k);
            if (oldRange.getFirstRow() >= 0 && oldRange.getLastRow() <= rownum) {
                sheetCreat.addMergedRegion(oldRange);
            }
        }
    }
              
              
    /**
     * @param oldCell   
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {  
        if(styleMap != null) {  
            if(oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()){  
                newCell.setCellStyle(oldCell.getCellStyle());  
            } else{  
                int stHashCode = oldCell.getCellStyle().hashCode();  
                HSSFCellStyle newCellStyle = styleMap.get(stHashCode);  
                if(newCellStyle == null){  
                    newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();  
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());  
                    styleMap.put(stHashCode, newCellStyle);  
                }  
                newCell.setCellStyle(newCellStyle);  
            }  
        }  
        switch(oldCell.getCellType()) {  
            case HSSFCell.CELL_TYPE_STRING:  
                newCell.setCellValue(oldCell.getStringCellValue());  
                break;  
            case HSSFCell.CELL_TYPE_NUMERIC:  
                          
                Object valuee="";
                if (HSSFDateUtil.isCellDateFormatted(oldCell)) {
                    Date date = (Date) oldCell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    valuee = sdf.format(date);
                } else {
                    double value_temp = (double) oldCell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(value_temp);
                    BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP);
                    valuee = bd1.doubleValue();
                    DecimalFormat format = new DecimalFormat("#0.###");
                    valuee = format.format(oldCell.getNumericCellValue());
                }
                newCell.setCellValue(valuee.toString());  
                          
  //            newCell.setCellValue(oldCell.getNumericCellValue());  
                break;  
            case HSSFCell.CELL_TYPE_BLANK:  
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);  
                break;  
            case HSSFCell.CELL_TYPE_BOOLEAN:  
                newCell.setCellValue(oldCell.getBooleanCellValue());  
                break;  
            case HSSFCell.CELL_TYPE_ERROR:  
                newCell.setCellErrorValue(oldCell.getErrorCellValue());  
                break;  
            case HSSFCell.CELL_TYPE_FORMULA:  
         //       newCell.setCellFormula(oldCell.getCellFormula());     
                try {
                    String value = String.valueOf(oldCell.getStringCellValue());
                    newCell.setCellValue(value); 
                } catch (IllegalStateException e) {
                    String value = String.valueOf(oldCell.getNumericCellValue());
                    newCell.setCellValue(value); 
                }
                break;  
            default:  
                break;  
        }  
                    
    }   
}