Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <controls>
- <mc:AlternateContent>
- ...
- </mc:AlternateContent>
- </controls>
- import java.io.*;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.xssf.usermodel.*;
- import org.apache.poi.POIXMLDocumentPart;
- import org.apache.poi.util.Units;
- import org.apache.xmlbeans.XmlCursor;
- import org.apache.xmlbeans.XmlObject;
- import javax.xml.namespace.QName;
- class ReadExcelXSSFControls {
- public ReadExcelXSSFControls() throws Exception {
- XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("ExcelWithControls.xlsx"));
- Sheet sheet = wb.getSheetAt(0);
- for (Row row : sheet) {
- for (int c = 0; c < 2; c++) {
- Cell cell = row.getCell(c);
- if (row.getRowNum() == 0) {
- System.out.print(cell + "t");
- } else {
- if (c == 0) {
- System.out.print(cell + "t");
- } else if (c == 1) {
- if (cell == null) cell = row.createCell(c);
- Control contol = getControlAt((XSSFCell)cell);
- System.out.print(contol);
- }
- }
- }
- System.out.println();
- }
- wb.close();
- }
- private Control getControlAt(XSSFCell cell) throws Exception {
- XSSFSheet sheet = cell.getSheet();
- Row row = cell.getRow();
- int r = row.getRowNum();
- int c = cell.getColumnIndex();
- int drheight = (int)Math.round(sheet.getDefaultRowHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
- int rheight = (int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
- row = null;
- if(r > 0) row = sheet.getRow(r-1);
- int rheightbefore = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;
- row = sheet.getRow(r+1);
- int rheightafter = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;
- String name = null;
- String objectType = null;
- String checked = null;
- XmlObject xmlDrawing = null;
- for (POIXMLDocumentPart part : sheet.getRelations()) {
- if ("application/vnd.openxmlformats-officedocument.vmlDrawing".equals(part.getPackagePart().getContentType())) {
- xmlDrawing = XmlObject.Factory.parse(part.getPackagePart().getInputStream());
- break;
- }
- }
- XmlCursor xmlcursor = null;
- if (xmlDrawing != null) {
- xmlcursor= xmlDrawing.newCursor();
- QName qnameClientData = new QName("urn:schemas-microsoft-com:office:excel", "ClientData", "x");
- QName qnameAnchor = new QName("urn:schemas-microsoft-com:office:excel", "Anchor", "x");
- boolean controlFound = false;
- while (xmlcursor.hasNextToken()) {
- XmlCursor.TokenType tokentype = xmlcursor.toNextToken();
- if (tokentype.isStart()) {
- if (qnameClientData.equals(xmlcursor.getName())) {
- controlFound = true;
- XmlObject clientdata = xmlcursor.getObject();
- XmlObject[] xmlchecked = clientdata.selectPath("declare namespace x='urn:schemas-microsoft-com:office:excel' x:Checked");
- if (xmlchecked.length > 0) {
- checked = "Checked";
- } else {
- checked = "Not checked";
- }
- while (xmlcursor.hasNextToken()) {
- tokentype = xmlcursor.toNextToken();
- if (tokentype.isAttr()) {
- if (new QName("ObjectType").equals(xmlcursor.getName())) {
- objectType = xmlcursor.getTextValue();
- name = objectType + " in row " + (r+1);
- }
- } else {
- break;
- }
- }
- } else if (qnameAnchor.equals(xmlcursor.getName()) && controlFound) {
- controlFound = false;
- String anchorContent = xmlcursor.getTextValue().trim();
- String[] anchorparts = anchorContent.split(",");
- int fromCol = Integer.parseInt(anchorparts[0].trim());
- int fromColDx = Integer.parseInt(anchorparts[1].trim());
- int fromRow = Integer.parseInt(anchorparts[2].trim());
- int fromRowDy = Integer.parseInt(anchorparts[3].trim());
- int toCol = Integer.parseInt(anchorparts[4].trim());
- int toColDx = Integer.parseInt(anchorparts[5].trim());
- int toRow = Integer.parseInt(anchorparts[6].trim());
- int toRowDy = Integer.parseInt(anchorparts[7].trim());
- if (fromCol == c /*needs only starting into the column*/
- && (fromRow == r || (fromRow == r-1 && fromRowDy > rheightbefore/2f))
- && (toRow == r || (toRow == r+1 && toRowDy < rheightafter/2f))) {
- //System.out.print(fromCol + ":" +fromColDx + ":" + fromRow + ":" + fromRowDy + ":" + toCol + ":" + toColDx + ":" + toRow + ":" + toRowDy);
- break;
- }
- }
- }
- }
- }
- if (xmlcursor!=null && xmlcursor.hasNextToken())
- return new Control(name, objectType, checked, r, c);
- return new Control("Not found", "unknown", "undefined", r, c);
- }
- public static void main(String[] args) throws Exception {
- ReadExcelXSSFControls o = new ReadExcelXSSFControls();
- }
- private class Control {
- private String name;
- private String objectType;
- private String checked;
- private int row;
- private int col;
- public Control(String name, String objectType, String checked, int row, int col) {
- this.name = name;
- this.objectType = objectType;
- this.checked = checked;
- this.row = row;
- this.col= col;
- }
- public String getName() {
- return this.name;
- }
- public String getObjectType() {
- return this.objectType;
- }
- public String getChecked() {
- return this.checked;
- }
- public int getRow() {
- return this.row;
- }
- public int getCol() {
- return this.col;
- }
- public String toString() {
- return this.name + ":r/c:" +row+ "/" + col + ":" + this.checked;
- }
- }
- }
- axel@arichter:~/Dokumente/JAVA/poi/poi-3.17$ java -cp .:./*:./lib/*:./ooxml-lib/* ReadExcelXSSFControls
- Product Status
- a Checkbox in row 2:r/c:1/1:Checked
- b Not found:r/c:2/1:undefined
- c Checkbox in row 4:r/c:3/1:Not checked
- d Checkbox in row 5:r/c:4/1:Checked
- e Radio in row 6:r/c:5/1:Checked
- f Not found:r/c:6/1:undefined
- g Not found:r/c:7/1:undefined
- e Checkbox in row 9:r/c:8/1:Checked
- f Not found:r/c:9/1:undefined
- h Radio in row 11:r/c:10/1:Not checked
- ActiveX Pict in row 14:r/c:13/1:Not checked
Add Comment
Please, Sign In to add comment