Java 在 Excel 中查找合并的单元格，拆分单元格并将其写入新电子表格？

Find merged cell in Excel, split the cells and write those in new spreadsheet?

提问by Java_User

I have been given a Assignment that I need to Split the data of a Spreadsheet and Write it into the new Spreadsheet. The Conditions are, Given Spreadsheet may have multiple numbers of Merged Cells and I need to find those Merged cells and write those Data in a New SpreadSheet. ie, the data or cells between one merged cell till to another Merged cell must be written in another Spreadsheet.

我收到了一项作业，需要拆分电子表格的数据并将其写入新的电子表格。条件是，给定的电子表格可能有多个合并的单元格，我需要找到这些合并的单元格并将这些数据写入新的电子表格。即，一个合并单元格到另一个合并单元格之间的数据或单元格必须写入另一个电子表格。

My Code of Effort is given below,

我的工作守则如下，

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class CopyTest {

public static void main(String[] args) throws IOException {
    CopyTest excel = new CopyTest();
    excel.process("D:\B3.xls");
 }

public void process(String fileName) throws IOException {
    BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileName));
    HSSFWorkbook workbook = new HSSFWorkbook(bis);
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    HSSFSheet sheet = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    HSSFSheet mySheet = null;
    HSSFRow myRow = null;
    HSSFCell myCell = null;
    int sheets = workbook.getNumberOfSheets();
    int fCell = 0;
    int lCell = 0;
    int fRow = 0;
    int lRow = 0;

    for (int iSheet = 0; iSheet < sheets; iSheet++) {
        sheet = workbook.getSheetAt(iSheet);
        if (sheet != null) {
            mySheet = myWorkBook.createSheet(sheet.getSheetName());

            fRow = sheet.getFirstRowNum();
            System.out.println("First Row at"+fRow);
            lRow = sheet.getLastRowNum();
            for (int iRow = fRow; iRow <= lRow; iRow++) {
                row = sheet.getRow(iRow);
                myRow = mySheet.createRow(iRow);



                if (row != null) {
                    fCell = row.getFirstCellNum();
                    lCell = row.getLastCellNum();
                    for (int iCell = fCell; iCell < lCell; iCell++) {
                        //if (mySheet.getMergedRegionAt(index)!=null)
                       System.out.println("Finding next merged Cells");
                       cell = row.getCell(iCell);
                        myCell = myRow.createCell(iCell);
                        if (cell != null) {
                            myCell.setCellType(cell.getCellType());

                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_BLANK:
                                myCell.setCellValue("");
                                break;

                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                myCell.setCellValue(cell.getBooleanCellValue());
                                break;

                            case HSSFCell.CELL_TYPE_ERROR:
                                myCell.setCellErrorValue(cell.getErrorCellValue());
                                break;

                            case HSSFCell.CELL_TYPE_FORMULA:
                                myCell.setCellFormula(cell.getCellFormula());
                                break;

                            case HSSFCell.CELL_TYPE_NUMERIC:
                                myCell.setCellValue(cell.getNumericCellValue());
                                break;

                            case HSSFCell.CELL_TYPE_STRING:
                                myCell.setCellValue(cell.getStringCellValue());
                                break;
                            default:
                                myCell.setCellFormula(cell.getCellFormula());
                               // System.out.println("Reading Cell value\t"+myCell);
                            }System.out.println("Reading Cell value\t"+myCell);
                        }
                    }
                }
            }
        }

    }  

    bis.close();
    BufferedOutputStream bos = new BufferedOutputStream(
            new FileOutputStream("D:\Result Excel1.xls", true));
    myWorkBook.write(bos);
    bos.close();

 }}

With this Code, I have Achieved cloning the spreadsheet into another new Sheet. Here, I am failing to find the merged Cell, getMergedCellRegionAt() helps me and returns merged cell region like A:4 D:12 like that. how do I proceed with this. Kindly Help me, your small effort is appreciated. Thanks in advance.

使用此代码，我已实现将电子表格克隆到另一个新工作表中。在这里，我找不到合并的单元格， getMergedCellRegionAt() 帮助我并返回合并的单元格区域，如 A:4 D:12 那样。我该如何处理。请帮助我，感谢您的小努力。提前致谢。

采纳答案by rgettman

According to the Javadocs for HSSFSheet, getMergedCellRegionAt()was deprecated in 2008 because the Regionit returns was also deprecated, in favor of CellRangeAddress. It suggests that you should use getMergedRegion(int)instead, which returns a CellRangeAddress.

根据Javadocs forHSSFSheet，getMergedCellRegionAt()在 2008 年被弃用，因为Region它返回的CellRangeAddress. 它建议您应该改用它getMergedRegion(int)，它返回一个CellRangeAddress.

The merged region data is not stored directly with the cells themselves, but with the Sheetobject. So you do not need to loop through rows and cells looking for whether they are part of a merged region; you just need to loop through the list of merged regions on the sheet, then add the merged region to your new sheet with addMergedRegion(CellRangeAddress).

合并的区域数据不直接与单元格本身一起存储，而是与Sheet对象一起存储。所以你不需要遍历行和单元格来寻找它们是否是合并区域的一部分；您只需要遍历工作表上合并区域的列表，然后将合并区域添加到新工作表中addMergedRegion(CellRangeAddress)。

for (int i = 0; i < sheet.getNumMergedRegions(); i++)
{
    CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
    // Just add it to the sheet on the new workbook.
    mySheet.addMergedRegion(mergedRegion);
}

These methods on HSSFSheetare in the Sheetinterface, so they will work with any Excel workbook that Apache POI supports, .xls (HSSF) or .xlsx (XSSF).

这些方法HSSFSheet位于Sheet界面中，因此它们将适用于 Apache POI 支持的任何 Excel 工作簿，.xls (HSSF) 或 .xlsx (XSSF)。

回答by Sorter

The merged cells have their value in the first cell.

合并的单元格在第一个单元格中具有它们的值。

The following method returns the value of the region provided the first cell's row and column in the merged region

以下方法返回提供合并区域中第一个单元格的行和列的区域的值

String getMergedRegionStringValue(HSSFSheet sheet, int firstRow, int firstColumn){
   for(int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);

        int colIndex = region.getFirstColumn();
        int rowNum = region.getFirstRow();
        //check first cell of the region
        if(rowNum == firstRow && colIndex == firstColumn){  
            return sheet.getRow(rowNum).getCell(colIndex).getStringCellValue();
        }
    }
}

回答by winklerrr

rgettmans answer is completely correct.

rgettmans 的回答是完全正确的。

Java 8

爪哇 8

I just wanted to add a solution for Java 8 with streams:

我只想为带有流的 Java 8 添加一个解决方案：

Sheet oldSheet, newSheet;
IntStream.range(0, oldSheet.getNumMergedRegions())
           .mapToObj(oldSheet::getMergedRegion)
           .forEach(newSheet::addMergedRegion);