POI 获取chekbox textbox (精准定位)

 方式1：POI   

方式2： xls

获取checkbox , 已经checkbox  的 label      (如果shape name 读取时一直为空， 用wps 打开excel , 保存后在测试)

 

 

 

 

 

依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

 

测试代码：

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataFormatter;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/***************************
 *<pre>
 * @Project Name : base-case-test-service
 * @Package      : com.sea.base.test
 * @File Name    : ReadExcelUFLCheckBox
 * @Author       : Sea
 * @Mail         : lshan523@163.com
 * @Date         : 2022/9/27 15:23
 * @Purpose      :　仅仅用于ｘls　文件
 * @History      :
 *</pre>
 ***************************/
@Data
@Slf4j
@NoArgsConstructor
public class MyExcelReadUtil {

//    HashMap<String, Object> checkBoxResultMap = new HashMap<>(); //收集解析的checkbox
//    HashMap<String, Object> activeXTextBoxMap = new HashMap<>(); //收集解析的 activeX Textbox
    HashMap<String, Object> textBoxMap = new HashMap<>(); //收集解析的 textBoxMap

    private HSSFWorkbook workbook;

    public MyExcelReadUtil(InputStream excelInput) throws IOException {
//        InputStream input = new FileInputStream("/home/sea/xx.xls");
        POIFSFileSystem fs = new POIFSFileSystem(excelInput);
        this.workbook= new HSSFWorkbook(fs);
    }

    /**
     * @param sheetIndex sheet编号　0,1,2
     * @param checkBoxResultMap  收集解析的checkbox
     * @param activeXTextBoxMap  收集解析的 activeX Textbox (name : byte[])
     */
    public  void readBoxAtSheet(int sheetIndex,HashMap<String, Object> checkBoxResultMap,HashMap<String, Object> activeXTextBoxMap){
        try {
            HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
            log.info("sheet name : " + sheet.getSheetName());
            HSSFPatriarch drawingPatriarch = sheet.getDrawingPatriarch(); //sheet.createDrawingPatriarch();
            List<HSSFShape> childrenList= drawingPatriarch.getChildren();
            int i =1;
            for(HSSFShape shape: childrenList)
            {
                String shapeName = shape.getShapeName();
                log.info("+++++++++++++++++++++  " +(i++) +"    " +shapeName+" +++++++++++++^+++++++++++++");
                if (shape instanceof HSSFTextbox) //普通文本框
                {
                    log.info("+++++++++ HSSFTextbox  ++++++++++"+shape.getShapeName());
                    HSSFTextbox textbox = (HSSFTextbox)shape;
                    HSSFRichTextString richString = textbox.getString();
                    String str = richString.getString();
                    log.info("文本框内容: " + str);
                    textBoxMap.put((shapeName+"").trim(),str);
                }

                if (shape instanceof HSSFSimpleShape) // 复选框类
                {
                    log.info("+++++++++ HSSFSimpleShape  ++++++++++"+shape.getShapeName());
                    HSSFSimpleShape simpleShape = (HSSFSimpleShape)shape;
                    getCheckBox(simpleShape,checkBoxResultMap);
                }

                if (shape instanceof HSSFObjectData) //图片已经其他插件
                {
                    log.info("+++++++++ HSSFObjectData  ++++++++++"+shape.getShapeName());
                    getActivexTextbox(shape,activeXTextBoxMap);
                }
            }
            System.err.println(activeXTextBoxMap);
            System.err.println(checkBoxResultMap);
        } catch (Exception  ex) {
            ex.printStackTrace();
        }
    }


    /**
     * @param sheet
     */
    public static void getAllData(HSSFSheet sheet)
    {
        int lastRowNum = sheet.getLastRowNum();
        DataFormatter dataFormatter = new DataFormatter();
        for(int row=0; row< lastRowNum; row++){
            HSSFRow rowLine = sheet.getRow(row);
            if(rowLine==null){continue;}
            for(int cell=0; cell<=10;cell++){
                if(rowLine.getCell(cell)!=null){
                    System.err.print(dataFormatter.formatCellValue(rowLine.getCell(cell)));
                }
                System.err.print(" ");
            }
            System.err.println(" ");
        }
    }

    /**
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getData(HSSFSheet sheet,int row, int column)
    {
        String s = new DataFormatter().formatCellValue(sheet.getRow(row-1).getCell(column-1));
        return s;
    }




    private  void getActivexTextbox(HSSFShape shape, HashMap<String, Object> activeXTextBoxMap) throws Exception
    {
        String shapeName = shape.getShapeName() + "";
        if(shapeName.contains("TextBox"))
        {
            HSSFObjectData objDataShape = (HSSFObjectData) shape;
            //保存到本地
      /*      log.info("框名　"+shapeName);
            String path =  "/home/sea/CCCCCCCCCCC/AAAA_svn_new_AAAA/base-case-test-service/src/test/resources/" +shape.getShapeName().trim()+ ".emf";
            System.err.println(path);
            FileOutputStream fileOutputStream = new FileOutputStream(path);
            fileOutputStream.write(objDataShape.getPictureData().getData());
            fileOutputStream.close();*/
            //name : byte[]
            activeXTextBoxMap.put(shapeName,objDataShape.getPictureData().getData());
//            activeXTextBoxMap.put(shapeName,path);
        }
    }


    private final static Pattern checkBoxReg = Pattern.compile("\\[sid=0x000A.+?\\[0(\\d),");
    /**
     * 获取复选框已经结果
     * @param simpleShape
     * @param checkBoxResult
     * @throws Exception
     */
    private  void getCheckBox(HSSFSimpleShape simpleShape,HashMap<String, Object> checkBoxResult) throws Exception
    {
        if((simpleShape.getShapeName()+"").contains("Check Box"))
        {
            String simpleName = simpleShape.getShapeName();
            log.info("复选框的内部名: "+ simpleName); // 复选框的内部名
            log.info("复选框的标记:  "+simpleShape.getString()); // 复选框的标记
            Method getTextObjectRecord =simpleShape.getClass().getSuperclass().getDeclaredMethod("getObjRecord", null);
            getTextObjectRecord.setAccessible(true);
            Object textObjectRecord = getTextObjectRecord.invoke(simpleShape, null);
            Matcher m = checkBoxReg.matcher(textObjectRecord.toString());
            if (m.find())
            {
                String checkBit = m.group(1);
                if (checkBit.length() == 1) {
                    boolean checked = "1".equals(checkBit);
                    System.out.println( simpleName+"  " +  checked+"-----------"+checkBit);
                    checkBoxResult.put(simpleName.trim(),checked);
                }
            }
        }
    }


    /**
     * Sea test 2022-09-20  : OK
     */
//    @Test
    public void Test2(){
        try {
            InputStream input = new FileInputStream("/home/sea/CCCCCCCCCCC/AAAA_svn_new_AAAA/base-case-test-service/src/test/resources/EXHKG_SLI_Template.xls");
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFPatriarch drawingPatriarch = sheet.getDrawingPatriarch(); //sheet.createDrawingPatriarch();
            List<HSSFShape> childrenList= drawingPatriarch.getChildren();
            HashMap<String, Object> checkBoxResultMap = new HashMap<>(); //收集解析的checkbox
            HashMap<String, Object> activeXTextBoxMap = new HashMap<>(); //收集解析的 activeX Textbox
            HashMap<String, Object> textBoxMap = new HashMap<>(); //收集解析的 textBoxMap
            int i =1;
            for(HSSFShape shape: childrenList)
            {
                String shapeName = shape.getShapeName();
                System.err.println("+++++++++++++++++++++  " +(i++) +"    " +shapeName+" +++++++++++++^+++++++++++++");
                if (shape instanceof HSSFTextbox) //普通文本框
                {
                    System.err.println("+++++++++ HSSFTextbox  ++++++++++"+shape.getShapeName());
                    HSSFTextbox textbox = (HSSFTextbox)shape;
                    HSSFRichTextString richString = textbox.getString();
                    String str = richString.getString();
                    System.out.println("文本框内容: " + str);
                    textBoxMap.put((shapeName+"").trim(),str);
                }

                if (shape instanceof HSSFSimpleShape) // 复选框类
                {
                    System.err.println("+++++++++ HSSFSimpleShape  ++++++++++"+shape.getShapeName());
                    HSSFSimpleShape simpleShape = (HSSFSimpleShape)shape;
                    System.err.println(simpleShape.getShapeName());
                    getCheckBox(simpleShape,checkBoxResultMap);
                }

                if (shape instanceof HSSFObjectData) //图片已经其他插件
                {
                    System.err.println("+++++++++ HSSFObjectData  ++++++++++"+shape.getShapeName());
                    getActivexTextbox(shape,activeXTextBoxMap);
                }
            }

            System.err.println(activeXTextBoxMap);
            System.err.println(checkBoxResultMap);


        } catch (Exception  ex) {
            ex.printStackTrace();
        }
    }
}

 

 

 

结果：[true, 男3, true, 男4, false,  男7, true, 男5, true, 男6, false, 男2, false, 男1]

 

 

 

方法2：（比较慢，不建议） 原文：https://zhuanlan.zhihu.com/p/518669270

通过Maven仓库下载导入，如下配置pom.xml： 

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>https://repo.e-iceblue.cn/repository/maven-public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

 

示例：

import com.spire.xls.*;
import com.spire.xls.core.ICheckBox;
import com.spire.xls.core.IRadioButton;
import com.spire.xls.core.ISpinnerShape;

public class GetFormControl {
 public static void main(String[] args) {
        //创建Workbook类的实例，加载Excel文档
        Workbook wb = new Workbook();
        wb.loadFromFile("AddControls.xlsx");

        //获取第1张工作表
        Worksheet sheet = wb.getWorksheets().get(0);

        //获取TextBox
        String textbox =  sheet.getTextBoxes().get(0).getText();
        System.out.println(textbox);

        //获取Radio Button
        for(int i = 0; i<sheet.getRadioButtons().getCount();i++)
        {
            IRadioButton radioButton = sheet.getRadioButtons().get(i);
            String name = radioButton.getCheckState().name();
            String text = radioButton.getText();
        boolean islocked = radioButton.isLocked();
            System.out.println(name + text + " 是否锁定："+ islocked);
        }

        //获取Combo Box控件中的选中的值（注：非列表中所有选项值）
        String value =  sheet.getComboBoxes().get(0).getSelectedValue();
        System.out.println(value);

        //获取Checkbox
        for(int z = 0;z< sheet.getCheckBoxes().getCount();z++)
        {
            ICheckBox checkBox = sheet.getCheckBoxes().get(z);
            String text = checkBox.getText();
            String name = checkBox.getCheckState().name();
            String alternativetext = checkBox.getAlternativeText();
            System.out.println(text + name + alternativetext);
        }

        //获取SpinnerShape
        for(int j  = 0;j<sheet.getSpinnerShapes().getCount();j++)
        {
            ISpinnerShape spinnerShape = sheet.getSpinnerShapes().get(j);
            String rangeAddress = spinnerShape.getLinkedCell().getRangeAddress();
            int currentValue = spinnerShape.getCurrentValue();
            System.out.println(rangeAddress + "\n" + currentValue);
        }

    }
}