Excel (xls포맷) Form Control (Checkbox) 상태 읽기.

TeamDev 의 jExcel 라이브러리를 이용하는 방법 (상용)

JExcel Forum ›

Reading Check Boxes.

https://groups.google.com/a/teamdev.com/forum/#!topic/jexcel-forum/-HGKvLio91c

여기에 Test.xlsx 파일과 CheckboxSample.java 파일을 다운로드 받을 수 있는데, 

컴파일 하기 위해서는 http://www.teamdev.com/ 에서 배포하는 라이브러가 필요하다.

위 홈페이지에서 jExcel 이라는 라이브러리와 의존성이 있는 라이브러리가 필요한데, 

http://www.teamdev.com/jexcel 들어가서 다운로드 받아서 압축을 풀어서 lib 디렉터리에 있는 jar 파일을 모두 classpath에 추가하면 된다.

http://www.teamdev.com/jniwrapper 에서 jniwrap.dll (jniwrap64.dll) 파일도 다운로드 받아야 한다.

다운로드 받아서 PATH 로 지정된 디렉터리 안에 집어 넣어야 된다.

이렇게 다 다운로드 받아서 돌리니...

Exception in thread "main" java.lang.ExceptionInInitializerError

at com.jniwrapper.win32.jexcel.Application.<clinit>(Unknown Source)

at CheckboxSample.main(CheckboxSample.java:33)

Caused by: java.lang.RuntimeException: JNIWrapper license check failed: No valid license found.

허무하게도 이런 에러가...

jExcel의 경우 개발자 1명 당 $799 이다.

JNIWrapper의 경우 개발자 1명 당 $499 이다.

이 라이브러리들은 http://mvnrepository.com 에도 없음.

JExcelApi 를 이용하는 방법 - 모름.

Get value of unmapped checkbox in excel using JExcelApi

http://stackoverflow.com/questions/28668283/get-value-of-unmapped-checkbox-in-excel-using-jexcelapi

http://www.tech.theplayhub.com/get_value_of_unmapped_checkbox_in_excel_using_jexcelapi/

JExcelApi 는 jExcelApi 로 검색하면 jxl 라이브러리로 검색되어 나오고, 다운로드 받을 수 있다.

위 웹페이지에서는 질문만 있고, 답변이 없다.

Workbook wrk1 =  Workbook.getWorkbook(new File("D:/Test.xls"));

Sheet sheet1 = wrk1.getSheet(0);

String contents = sheet1.findLabelCell("Check Box 1").getContents();

System.out.println(contents);

jxl의 Sheet 인터페이스에서 

http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/jxl/Sheet.html

findLabelCell() 메서드나 findCell() 메서드를 사용해 봤지만, check box 를 읽어오지 못했다.

아마 Check Box 는 Cell Type 이 아닐 것이다.

Apache POI 를 이용하는 방법

Apache POI 라이브러리도 Check Box의 상태를 읽어오는 방법.

POI 사용한 버전: 3.11

private 으로 되어 있는 InternalSheet 객체를 가져오기 위해 

package org.apache.poi.hssf.usermodel;

HSSFTestHelper class를 만든다.

이 클래스는 poi 라이브러리의 test 패키지에 있는 클래스인데, maven repository 에는 test 패키지의 jar 파일을 배포하고 있지 않아서 HSSFTestHelper class 만 인터넷에서 찾아서 내 소스에 포함시켰다. (원래 패키지 정보 - org.apache.poi.hssf.usermodel 대로 경로를 만들어 놔야지 HSSFSheet 클래스의 internal 메서드인 getSheet() 메서드에 접근할 수 있다.)

getSheet() 메서드를 통해 InternalSheet 객체를 가져오고, 그것을 통해 List<RecordBase> 를 가져오고, 

다시 List<SubRecord> 를 가져와서 CommonObjectDataSubRecord 객체를 가져와서,

CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX 인지를 판별한 후,

다음 RecordBase를 가져오면서 TextObjectRecord 객체인지 확인해서 CheckBox의 Label 값을 가져올 수 있다.

그리고 나서 SubRecord 를 순회하면서 CheckBox의 Status를 가지고 있는 org.apache.poi.hssf.record.SubRecord$UnknownSubRecord 객체를 가져와서 reflection 으로 private 멤버변수의 값(check box의 상태값)을 가져온다.

Microsoft Office Excel 97-2007 Binary File Format

http://www.digitalpreservation.gov/formats/digformatspecs/Excel97-2007BinaryFileFormat(xls)Specification.pdf

xls 파일의 Binary Format 문서는 찾았고, Page 167 of 349 를 보면...

Check Box Object Fields

var fChecked 2 =0 

if the check box is not checked

=1 if the check box is checked

=2 if the check box is gray (mixed)

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.hssf.usermodel;
import org.apache.poi.ddf.*;
import org.apache.poi.hssf.model.DrawingManager2;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.record.EscherAggregate;
import org.apache.poi.hssf.record.ObjRecord;
import org.apache.poi.hssf.record.TextObjectRecord;

/**
 * Helper class for HSSF tests that aren't within the
 *  HSSF UserModel package, but need to do internal
 *  UserModel things.
 */
public class HSSFTestHelper {

    public static class MockDrawingManager extends DrawingManager2 {

        public MockDrawingManager (){
            super(null);
        }

        @Override
        public int allocateShapeId(short drawingGroupId) {
            return 1025; //Mock value
        }

        @Override
        public int allocateShapeId(short drawingGroupId, EscherDgRecord dg) {
            return 1025;
        }

        public EscherDgRecord createDgRecord()
        {
            EscherDgRecord dg = new EscherDgRecord();
            dg.setRecordId( EscherDgRecord.RECORD_ID );
            dg.setOptions( (short) (16) );
            dg.setNumShapes( 1 );
            dg.setLastMSOSPID( 1024 );
            return dg;
        }
    }
	/**
	 * Lets non UserModel tests at the low level Workbook
	 */
	public static InternalWorkbook getWorkbookForTest(HSSFWorkbook wb) {
		return wb.getWorkbook();
	}
	public static InternalSheet getSheetForTest(HSSFSheet sheet) {
		return sheet.getSheet();
	}

    public static HSSFPatriarch createTestPatriarch(HSSFSheet sheet, EscherAggregate agg){
        return new HSSFPatriarch(sheet, agg);
    }

    public static EscherAggregate getEscherAggregate(HSSFPatriarch patriarch){
        return patriarch._getBoundAggregate();
    }

    public static int allocateNewShapeId(HSSFPatriarch patriarch){
        return patriarch.newShapeId();
    }

    public static EscherOptRecord getOptRecord(HSSFShape shape){
        return shape.getOptRecord();
    }

    public static void setShapeId(HSSFShape shape, int id){
        shape.setShapeId(id);
    }

    public static EscherContainerRecord getEscherContainer(HSSFShape shape){
        return shape.getEscherContainer();
    }

    public static TextObjectRecord getTextObjRecord(HSSFSimpleShape shape){
        return shape.getTextObjectRecord();
    }

    public static ObjRecord getObjRecord(HSSFShape shape){
        return shape.getObjRecord();
    }

    public static EscherRecord getEscherAnchor(HSSFAnchor anchor){
        return anchor.getEscherAnchor();
    }
}

package com.example.excel.service;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.CommonObjectDataSubRecord;
import org.apache.poi.hssf.record.EndSubRecord;
import org.apache.poi.hssf.record.ObjRecord;
import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.record.SubRecord;
import org.apache.poi.hssf.record.TextObjectRecord;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFTestHelper;
import org.springframework.stereotype.Service;

@Service
public class ExcelService {

	public void findCellCheckbox(HSSFSheet hssfSheet) {

		HashMap<String,Boolean> checkBoxMap = new HashMap<String,Boolean>();

		InternalSheet isheet = HSSFTestHelper.getSheetForTest(hssfSheet);

		for (Iterator<RecordBase> it=isheet.getRecords().iterator(); it.hasNext(); ) {
			RecordBase rec = it.next();
			if (rec instanceof ObjRecord) {
				ObjRecord objRec = (ObjRecord)rec;

				List<SubRecord> subRecords = objRec.getSubRecords();

				// CheckBox의 Label 이름
				String checkBoxLabel = "";

				for(SubRecord sub : subRecords) {
					if (sub instanceof CommonObjectDataSubRecord) {
						CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord)sub;
						// CheckBox의 Label 이름 읽기.
						if (cmo.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX ) {
							while (it.hasNext()) {
								rec = (RecordBase)it.next();
								if (rec instanceof TextObjectRecord) {
									checkBoxLabel = ((TextObjectRecord) rec).getStr().toString();
									break;
								}
							}
						}
                        // EDIT Box 안의 Text 읽기.
						if( cmo.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_TEXT ) {
							while (it.hasNext()) {
								rec = (RecordBase)it.next();
								if (rec instanceof TextObjectRecord) {
									String text = ((TextObjectRecord) rec).getStr().toString();
									System.out.println("Text: " + text);
									break;
								}
							}
						}
					} else if ( !(sub instanceof EndSubRecord) ) {

						//System.out.println("sub: " + sub);
						// sub: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x0012 size=8 : [01, 00, 00, 00, 00, 00, 03, 00]]
						// 위 문자열에서 01 은 checked 상태, 00 은 not checked 상태, 02 는 gray(mixed) 상태이다.

						// CheckBox의 status를 가지고 있는 것은 org.apache.poi.hssf.record.UnknownSubRecord Type인데,
						// UnknownSubRecord Class는 private Class 라서 reflection 으로 private 멤버 변수의 값을 가져왔다.

						try {
							Class<?> clazz = Class.forName("org.apache.poi.hssf.record.SubRecord$UnknownSubRecord");

							Field sidField = clazz.getDeclaredField("_sid");

							// 다음 에러를 방지하기 위해 isInstance() 로 체크해준다. 
							// java.lang.IllegalArgumentException: Can not set final int field org.apache.poi.hssf.record.SubRecord$UnknownSubRecord._sid to org.apache.poi.hssf.record.GroupMarkerSubRecord
							if( clazz.isInstance(sub) ) {

								sidField.setAccessible(true);

								int _sid = (int) sidField.get(sub);

								if( _sid == 10 ) {
									Field dataField = clazz.getDeclaredField("_data");

									dataField.setAccessible(true);

									byte[] _data = (byte[]) dataField.get(sub);

									int status = _data[0];

									// CheckBox의 check 상태 조회
									if( status == 1 ) {
										checkBoxMap.put(checkBoxLabel, true); // checked
									} else {
										checkBoxMap.put(checkBoxLabel, false); // not checked
									}
								}
							}
						} catch (ClassNotFoundException | NoSuchFieldException | SecurityException | IllegalArgumentException | IllegalAccessException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}

		System.out.println("CheckBoxMap: " + checkBoxMap);
	}
}

Check Box가 있는 샘플 xls 파일

Test.xls