Difference between revisions of "SimpleGraph-Excel"
Jump to navigation
Jump to search
(26 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | =SimpleGraphModule= | ||
+ | |||
{{SimpleGraphModule | {{SimpleGraphModule | ||
|name=Excel | |name=Excel | ||
+ | |logo=File:Microsoft Excel 2013 logo.svg | ||
+ | |modulename=excel | ||
+ | |systemname=ExcelSystem | ||
+ | |url=https://en.wikipedia.org/wiki/Microsoft_Excel | ||
+ | |apiname=Apache POI XSSF/HSSF | ||
+ | |apiurl=https://poi.apache.org/components/spreadsheet/quick-guide.html | ||
|documentation=makes Microsoft Excel workbooks accessible via the Apache POI API | |documentation=makes Microsoft Excel workbooks accessible via the Apache POI API | ||
|storemode=property | |storemode=property | ||
|viewmode=hidden | |viewmode=hidden | ||
}} | }} | ||
+ | |||
+ | = Mental Model = | ||
+ | == Mapping rules == | ||
+ | # Each Vertex type (by label) is converted to an Excel sheet | ||
+ | # Each Edge type (by label) is converted to an Excel sheet | ||
+ | # Each vertex is converted to a row | ||
+ | # Each vertex property is converted to a cell | ||
+ | # Each edge is converted to a row | ||
+ | # Each edge property is converted to a cell | ||
+ | # The header row for Vertices has a column for each property key | ||
+ | # The header for for Edges has a column for each property key + an in and and out column | ||
+ | # The in and out header columns for edges have the label of the corresponding vertex in parentheses | ||
+ | == UML diagram == | ||
+ | <uml> | ||
+ | |||
+ | hide circle | ||
+ | package Excel { | ||
+ | class Workbook { | ||
+ | name | ||
+ | } | ||
+ | class Sheet { | ||
+ | name | ||
+ | } | ||
+ | class Row { | ||
+ | rownumber | ||
+ | } | ||
+ | class Cell { | ||
+ | columnnumber | ||
+ | value | ||
+ | } | ||
+ | Workbook "1" -> "n" Sheet | ||
+ | Sheet "1" -> "title 1" Row | ||
+ | Sheet "1" -> "n" Row | ||
+ | Row "1" -> "n" Cell | ||
+ | } | ||
+ | package TinkerPop { | ||
+ | class Property { | ||
+ | name | ||
+ | value | ||
+ | } | ||
+ | class Vertex { | ||
+ | id | ||
+ | label | ||
+ | } | ||
+ | class Edge { | ||
+ | id | ||
+ | label | ||
+ | } | ||
+ | Edge -> "1 in" Vertex | ||
+ | Edge -> "1 out" Vertex | ||
+ | Vertex -> "n properties" Property | ||
+ | Edge -> "n properties" Property | ||
+ | Edges -> "n edges" Edge | ||
+ | Vertices -> "n Vertices" Vertex | ||
+ | } | ||
+ | Cell - Property | ||
+ | Edge - Sheet | ||
+ | Vertex - Sheet | ||
+ | </uml> | ||
+ | |||
+ | = Examples = | ||
+ | == Modern == | ||
+ | <uml> | ||
+ | hide circle | ||
+ | package Modern { | ||
+ | note top of person: 4 | ||
+ | class person { | ||
+ | name | ||
+ | age | ||
+ | } | ||
+ | note top of software: 2 | ||
+ | class software { | ||
+ | name | ||
+ | lang | ||
+ | } | ||
+ | } | ||
+ | |||
+ | person --> person: knows | ||
+ | note on link: 2 | ||
+ | |||
+ | person --> software: created | ||
+ | note on link: 4 | ||
+ | </uml> | ||
+ | [[File:modern.xlsx]] | ||
+ | |||
+ | == AirRoutes == | ||
+ | The UML diagram and excel table is not fully consistent with the graph. The graph can have contains edges between airport and country. | ||
+ | |||
+ | <uml> | ||
+ | hide circle | ||
+ | package AirRoutes { | ||
+ | note top of airport: 3374 | ||
+ | class airport { | ||
+ | code | ||
+ | longest | ||
+ | city | ||
+ | elev | ||
+ | icao | ||
+ | lon | ||
+ | type | ||
+ | region | ||
+ | runways | ||
+ | lat | ||
+ | desc | ||
+ | country | ||
+ | } | ||
+ | note top of version: 1 | ||
+ | class version { | ||
+ | code | ||
+ | type | ||
+ | desc | ||
+ | } | ||
+ | note top of country: 237 | ||
+ | class country { | ||
+ | desc | ||
+ | code | ||
+ | type | ||
+ | } | ||
+ | note top of continent: 7 | ||
+ | class continent { | ||
+ | code | ||
+ | type | ||
+ | desc | ||
+ | } | ||
+ | } | ||
+ | |||
+ | airport --> airport: route | ||
+ | note on link: 43400 | ||
+ | |||
+ | continent --> airport: contains | ||
+ | note on link: 6748 | ||
+ | </uml> | ||
+ | [[File:air-routes.xlsx]] | ||
+ | <source lang='java'> | ||
+ | @Test | ||
+ | public void testCreateExcelAirRoutes() throws Exception { | ||
+ | ExcelSystem es = new ExcelSystem(); | ||
+ | Graph graph = TestTinkerPop3.getAirRoutes(); | ||
+ | GraphTraversalSource g = graph.traversal(); | ||
+ | // es.setDebug(true); | ||
+ | Workbook wb = es.createWorkBook(g); | ||
+ | assertEquals(6, wb.getNumberOfSheets()); | ||
+ | es.save(wb, testAirRouteFileName); | ||
+ | } | ||
+ | </source> | ||
+ | |||
+ | == Railway == | ||
+ | <uml> | ||
+ | hide circle | ||
+ | package Railway { | ||
+ | class City { | ||
+ | name | ||
+ | lat | ||
+ | lon | ||
+ | } | ||
+ | |||
+ | class Station { | ||
+ | name | ||
+ | lat | ||
+ | lon | ||
+ | } | ||
+ | class Route { | ||
+ | linenumber | ||
+ | } | ||
+ | |||
+ | class Section { | ||
+ | id | ||
+ | } | ||
+ | Station "1" - "city 1" City | ||
+ | Route "1" - "to 1" City | ||
+ | Route "1" - "from 1" City | ||
+ | Route "1" - "sections n" Section | ||
+ | Station "1" - "sections n" Section | ||
+ | } | ||
+ | </uml> | ||
+ | |||
+ | = Links = | ||
+ | * https://stackoverflow.com/questions/tagged/poi | ||
+ | * https://stackoverflow.com/questions/tagged/poi-hssf | ||
+ | * https://stackoverflow.com/questions/tagged/apache-poi | ||
+ | [[Category:frontend]] |
Latest revision as of 09:06, 30 October 2018
SimpleGraphModule
SimpleGraph Excel module
The SimpleGraph Excel module makes Microsoft Excel workbooks accessible via the Apache POI API see Apache POI XSSF/HSSF.
Sources
- System: ExcelSystem.java
- JUnit-Test: TestExcelSystem.java
Mental Model
Mapping rules
- Each Vertex type (by label) is converted to an Excel sheet
- Each Edge type (by label) is converted to an Excel sheet
- Each vertex is converted to a row
- Each vertex property is converted to a cell
- Each edge is converted to a row
- Each edge property is converted to a cell
- The header row for Vertices has a column for each property key
- The header for for Edges has a column for each property key + an in and and out column
- The in and out header columns for edges have the label of the corresponding vertex in parentheses
UML diagram
![](/images/wiki/diagrams/Diagrams_92b66461de3fc8e93885f18720eb9a68.png)
Examples
Modern
![](/images/wiki/diagrams/Diagrams_46997f81f7ff356d0b8619277cba0523.png)
AirRoutes
The UML diagram and excel table is not fully consistent with the graph. The graph can have contains edges between airport and country.
![](/images/wiki/diagrams/Diagrams_7a3a6332d64acda27c02947ce2c74159.png)
@Test
public void testCreateExcelAirRoutes() throws Exception {
ExcelSystem es = new ExcelSystem();
Graph graph = TestTinkerPop3.getAirRoutes();
GraphTraversalSource g = graph.traversal();
// es.setDebug(true);
Workbook wb = es.createWorkBook(g);
assertEquals(6, wb.getNumberOfSheets());
es.save(wb, testAirRouteFileName);
}
Railway
![](/images/wiki/diagrams/Diagrams_12b12d67adede988c930d229deb09cbe.png)