Guest User

Untitled

a guest
Feb 25th, 2018
103
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.89 KB | None | 0 0
  1. <controls>
  2. <mc:AlternateContent>
  3. ...
  4. </mc:AlternateContent>
  5. </controls>
  6.  
  7. import java.io.*;
  8.  
  9. import org.apache.poi.ss.usermodel.*;
  10. import org.apache.poi.xssf.usermodel.*;
  11.  
  12. import org.apache.poi.POIXMLDocumentPart;
  13.  
  14. import org.apache.poi.util.Units;
  15.  
  16. import org.apache.xmlbeans.XmlCursor;
  17. import org.apache.xmlbeans.XmlObject;
  18.  
  19. import javax.xml.namespace.QName;
  20.  
  21. class ReadExcelXSSFControls {
  22.  
  23. public ReadExcelXSSFControls() throws Exception {
  24. XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("ExcelWithControls.xlsx"));
  25.  
  26. Sheet sheet = wb.getSheetAt(0);
  27. for (Row row : sheet) {
  28. for (int c = 0; c < 2; c++) {
  29. Cell cell = row.getCell(c);
  30. if (row.getRowNum() == 0) {
  31. System.out.print(cell + "t");
  32. } else {
  33. if (c == 0) {
  34. System.out.print(cell + "t");
  35. } else if (c == 1) {
  36. if (cell == null) cell = row.createCell(c);
  37. Control contol = getControlAt((XSSFCell)cell);
  38. System.out.print(contol);
  39. }
  40. }
  41. }
  42. System.out.println();
  43. }
  44.  
  45. wb.close();
  46. }
  47.  
  48. private Control getControlAt(XSSFCell cell) throws Exception {
  49. XSSFSheet sheet = cell.getSheet();
  50. Row row = cell.getRow();
  51. int r = row.getRowNum();
  52. int c = cell.getColumnIndex();
  53.  
  54. int drheight = (int)Math.round(sheet.getDefaultRowHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
  55. int rheight = (int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
  56. row = null;
  57. if(r > 0) row = sheet.getRow(r-1);
  58. int rheightbefore = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;
  59. row = sheet.getRow(r+1);
  60. int rheightafter = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;
  61.  
  62. String name = null;
  63. String objectType = null;
  64. String checked = null;
  65.  
  66. XmlObject xmlDrawing = null;
  67. for (POIXMLDocumentPart part : sheet.getRelations()) {
  68. if ("application/vnd.openxmlformats-officedocument.vmlDrawing".equals(part.getPackagePart().getContentType())) {
  69. xmlDrawing = XmlObject.Factory.parse(part.getPackagePart().getInputStream());
  70. break;
  71. }
  72. }
  73. XmlCursor xmlcursor = null;
  74. if (xmlDrawing != null) {
  75. xmlcursor= xmlDrawing.newCursor();
  76. QName qnameClientData = new QName("urn:schemas-microsoft-com:office:excel", "ClientData", "x");
  77. QName qnameAnchor = new QName("urn:schemas-microsoft-com:office:excel", "Anchor", "x");
  78. boolean controlFound = false;
  79. while (xmlcursor.hasNextToken()) {
  80. XmlCursor.TokenType tokentype = xmlcursor.toNextToken();
  81. if (tokentype.isStart()) {
  82. if (qnameClientData.equals(xmlcursor.getName())) {
  83. controlFound = true;
  84. XmlObject clientdata = xmlcursor.getObject();
  85. XmlObject[] xmlchecked = clientdata.selectPath("declare namespace x='urn:schemas-microsoft-com:office:excel' x:Checked");
  86. if (xmlchecked.length > 0) {
  87. checked = "Checked";
  88. } else {
  89. checked = "Not checked";
  90. }
  91. while (xmlcursor.hasNextToken()) {
  92. tokentype = xmlcursor.toNextToken();
  93. if (tokentype.isAttr()) {
  94. if (new QName("ObjectType").equals(xmlcursor.getName())) {
  95. objectType = xmlcursor.getTextValue();
  96. name = objectType + " in row " + (r+1);
  97. }
  98. } else {
  99. break;
  100. }
  101. }
  102. } else if (qnameAnchor.equals(xmlcursor.getName()) && controlFound) {
  103. controlFound = false;
  104. String anchorContent = xmlcursor.getTextValue().trim();
  105. String[] anchorparts = anchorContent.split(",");
  106. int fromCol = Integer.parseInt(anchorparts[0].trim());
  107. int fromColDx = Integer.parseInt(anchorparts[1].trim());
  108. int fromRow = Integer.parseInt(anchorparts[2].trim());
  109. int fromRowDy = Integer.parseInt(anchorparts[3].trim());
  110. int toCol = Integer.parseInt(anchorparts[4].trim());
  111. int toColDx = Integer.parseInt(anchorparts[5].trim());
  112. int toRow = Integer.parseInt(anchorparts[6].trim());
  113. int toRowDy = Integer.parseInt(anchorparts[7].trim());
  114.  
  115. if (fromCol == c /*needs only starting into the column*/
  116. && (fromRow == r || (fromRow == r-1 && fromRowDy > rheightbefore/2f))
  117. && (toRow == r || (toRow == r+1 && toRowDy < rheightafter/2f))) {
  118. //System.out.print(fromCol + ":" +fromColDx + ":" + fromRow + ":" + fromRowDy + ":" + toCol + ":" + toColDx + ":" + toRow + ":" + toRowDy);
  119. break;
  120. }
  121. }
  122. }
  123. }
  124. }
  125.  
  126. if (xmlcursor!=null && xmlcursor.hasNextToken())
  127. return new Control(name, objectType, checked, r, c);
  128.  
  129. return new Control("Not found", "unknown", "undefined", r, c);
  130. }
  131.  
  132. public static void main(String[] args) throws Exception {
  133. ReadExcelXSSFControls o = new ReadExcelXSSFControls();
  134. }
  135.  
  136. private class Control {
  137. private String name;
  138. private String objectType;
  139. private String checked;
  140. private int row;
  141. private int col;
  142. public Control(String name, String objectType, String checked, int row, int col) {
  143. this.name = name;
  144. this.objectType = objectType;
  145. this.checked = checked;
  146. this.row = row;
  147. this.col= col;
  148. }
  149. public String getName() {
  150. return this.name;
  151. }
  152. public String getObjectType() {
  153. return this.objectType;
  154. }
  155. public String getChecked() {
  156. return this.checked;
  157. }
  158. public int getRow() {
  159. return this.row;
  160. }
  161. public int getCol() {
  162. return this.col;
  163. }
  164. public String toString() {
  165. return this.name + ":r/c:" +row+ "/" + col + ":" + this.checked;
  166. }
  167. }
  168. }
  169.  
  170. axel@arichter:~/Dokumente/JAVA/poi/poi-3.17$ java -cp .:./*:./lib/*:./ooxml-lib/* ReadExcelXSSFControls
  171. Product Status
  172. a Checkbox in row 2:r/c:1/1:Checked
  173. b Not found:r/c:2/1:undefined
  174. c Checkbox in row 4:r/c:3/1:Not checked
  175. d Checkbox in row 5:r/c:4/1:Checked
  176. e Radio in row 6:r/c:5/1:Checked
  177. f Not found:r/c:6/1:undefined
  178. g Not found:r/c:7/1:undefined
  179. e Checkbox in row 9:r/c:8/1:Checked
  180. f Not found:r/c:9/1:undefined
  181. h Radio in row 11:r/c:10/1:Not checked
  182. ActiveX Pict in row 14:r/c:13/1:Not checked
Add Comment
Please, Sign In to add comment