Excel File for referenceHello ppl I used the following code to read the excel sheet. can you help me formatting the output data in an object in such a way that I should get the below mentioned json structure when I convert the object to json using some api's? I have also attached the excel sheet img for ref. i.e output structure should be similar to Map(List(Map(Map))) and also please refer the json
我使用了下面的代码来读取Excel表格。您能帮助我在对象中格式化输出数据,使我在使用一些api将对象转换为json时能够得到下面提到的json结构吗?我还附上了excel表格img供参考。e输出结构应该与Map(List(Map(Map)))相似,也请参考json
public class readingexcel {
public void readXLSXFile(String fileName) {
InputStream XlsxFileToRead = null;
XSSFWorkbook workbook = null;
try {
XlsxFileToRead = new FileInputStream(fileName);
//Getting the workbook instance for xlsx file
workbook = new XSSFWorkbook(XlsxFileToRead);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//getting the first sheet from the workbook using sheet name.
// We can also pass the index of the sheet which starts from '0'.
XSSFSheet sheet = workbook.getSheet("Sheet1");
XSSFRow row;
XSSFCell cell;
//Iterating all the rows in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
row = (XSSFRow) rows.next();
//Iterating all the cells of the current row
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
cell = (XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
System.out.print(cell.getStringCellValue() + " ");
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue() + " ");
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue() + " ");
} else { // //Here if require, we can also add below methods to
// read the cell content
// XSSFCell.CELL_TYPE_BLANK
// XSSFCell.CELL_TYPE_FORMULA
// XSSFCell.CELL_TYPE_ERROR
}
}
System.out.println();
try {
XlsxFileToRead.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
readingexcel readXlsx = new readingexcel();
readXlsx.readXLSXFile("stack.xlsx");
}
}
This is the json struture (please ignore the syntax)
这是json结构(请忽略语法)
Sheet1
{
Month
[
{
BL :
{
"A":42,
"B":19,
"C":20,
"D":11,
"E":22,
"F":44,
"G":76,
"H":12
}
},
{
JAN :
{
"A":6,
"B":4,
"C":2,
"D":1,
"E":8,
"F":7,
"G":2,
"H":5
}
}
FEB :
{
"A":5,
"B":6,
"C":8,
"D":9,
"E":7,
"F":6,
"G":4,
"H":2
}
.
.
.
DEC :
{
"A":4,
"B":2,
"C":1,
"D":9,
"E":8,
"F":7,
"G":4,
"H":3
}
}
}
]
WEEK
[
{
BL:
{
"I":42,
"J":19,
"K":20
}
WK-01:
{
"I":6,
"J":4
"K":2
}
WK-02:
{
"I":5,
"J":6,
"K":8
}
.
.
.
WK-52:
{
"I":1,
"J":4,
"K":6
}
}
]
1 个解决方案
#1
0
Here is the complete code to parse the file into a Map<String, Map<String, List<Map<String, Map<String, Integer>>>>>
as required (See suggested improvements at the end)
下面是将文件解析为Map
-
This can parse all the sheets you have in your XLS file, as long as they all have the same general table structure.
这可以解析XLS文件中所有的表,只要它们具有相同的通用表结构。
-
The table positions are automatically found (because their name is on the top left corner), you just have to provide the name of the table to the
extractTable()
method.将自动找到表的位置(因为它们的名称在左上角),您只需向extractTable()方法提供表的名称。
Code
代码
public class ExcelReader {
public static void main(String[] args) {
String jsonString = new ExcelReader().fileToJson("stack.xlsx");
System.out.print(jsonString);
}
// Parses the file into a JSON string
private String fileToJson(String filename) {
List<XSSFSheet> sheets = getSheets(filename);
Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> workbookMap = parseSheets(sheets);
return toJsonString(workbookMap);
}
// Make a list of XSSFSheets out of the file
private List<XSSFSheet> getSheets(String fileName) {
List<XSSFSheet> sheets = new ArrayList<>();
try(InputStream XlsxFileToRead = new FileInputStream(fileName)) {
//Getting the workbook instance for xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(XlsxFileToRead);
//Getting all the sheets
for (int i=0; i<workbook.getNumberOfSheets(); i++) {
sheets.add(workbook.getSheetAt(i));
}
} catch (IOException e) {
e.printStackTrace();
}
return sheets;
}
// Parse a list of sheets into the desired structure
private Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> parseSheets(List<XSSFSheet> xsshSheets) {
Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> workbookMap = new HashMap<>();
for(XSSFSheet xssfSheet : xsshSheets) {
String name = xssfSheet.getSheetName();
Map<String, List<Map<String, Map<String, Integer>>>> sheetMap = parseSheet(xssfSheet);
workbookMap.put(name, sheetMap);
}
return workbookMap;
}
// Parses a sheet into the desired structure
private Map<String, List<Map<String, Map<String, Integer>>>> parseSheet(XSSFSheet xsshSheet) {
List<Map<String, Map<String, Integer>>> months = extractTable(xsshSheet, "Month");
List<Map<String, Map<String, Integer>>> weeks = extractTable(xsshSheet, "Week");
List<Map<String, Map<String, Integer>>> days = extractTable(xsshSheet, "Daily");
Map<String, List<Map<String, Map<String, Integer>>>> sheet = new HashMap<>();
sheet.put("MONTH", months);
sheet.put("WEEK", weeks);
sheet.put("DAILY", days);
return sheet;
}
// Extracts a table
private List<Map<String, Map<String, Integer>>> extractTable(XSSFSheet sheet, String tableName) {
Cell tablePosition = findTablePosition(sheet, tableName);
if(tablePosition!=null)
return extractTable(sheet, tablePosition);
else return null;
}
// Finds the position of the cell with the given text
private Cell findTablePosition(XSSFSheet sheet, String tableName) {
for (Row row : sheet) {
for (Cell cell : row) {
if(cell.getCellTypeEnum() == CellType.STRING && tableName.equals(cell.getStringCellValue())) {
return cell;
}
}
}
return null;
}
// Extract a table
private List<Map<String, Map<String, Integer>>> extractTable(XSSFSheet sheet, Cell tablePosition) {
List<Map<String, Map<String, Integer>>> table = new ArrayList<>();
int headerRow = tablePosition.getRowIndex();
int valuesHeaderColumn = tablePosition.getColumnIndex();
// read all columns and rows until finding an empty one
int row = headerRow + 1;
int col = valuesHeaderColumn + 1;
// read all columns starting at col
while(true) {
Map<String, Map<String, Integer>> tableColumn = new HashMap<>();
Cell headerCell = sheet.getRow(headerRow).getCell(col);
if(headerCell == null)
break;
String columnName = headerCell.getStringCellValue().toUpperCase();
if("".equals(columnName))
break;
// read all rows starting at row
Map<String, Integer> values = new HashMap<>();
while(true) {
Row valueHeaderRow = sheet.getRow(row);
if(valueHeaderRow == null)
break;
Cell valueHeaderCell = valueHeaderRow.getCell(valuesHeaderColumn);
String valueHeader = valueHeaderCell.getStringCellValue();
if("".equals(valueHeader))
break;
Cell valueCell = sheet.getRow(row).getCell(col);
Integer value = (int)valueCell.getNumericCellValue();
values.put(valueHeader, value);
row++;
}
tableColumn.put(columnName, values);
// Add the columns map to the list
table.add(tableColumn);
col++;
row = headerRow + 1;
}
return table;
}
// Create the JSON string using Jackson ObjectMapper
private String toJsonString(Object o) {
ObjectMapper objectMapper = new ObjectMapper();
String jsonString = null;
try {
jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(o);
} catch(JsonProcessingException e) {
e.printStackTrace();
}
return jsonString;
}
}
Generated JSON (using Jackson's ObjectMapper
):
生成JSON(使用Jackson的ObjectMapper):
{
"Sheet2" : {
"MONTH" : [ {
"BL" : {
"A" : 42,
"B" : 19,
"C" : 20,
"D" : 11,
"E" : 22,
"F" : 44,
"G" : 76,
"H" : 12
}
}, {
"JAN" : {
"A" : 4,
"B" : 4,
"C" : 2,
"D" : 1,
"E" : 8,
"F" : 7,
"G" : 2,
"H" : 5
}
}, {
"FEB" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"MAR" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
}, {
"APR" : {
"A" : 5,
"B" : 6,
"C" : 6,
"D" : 4,
"E" : 3,
"F" : 2,
"G" : 1,
"H" : 3
}
}, {
"MAY" : {
"A" : 5,
"B" : 3,
"C" : 2,
"D" : 1,
"E" : 6,
"F" : 7,
"G" : 6,
"H" : 5
}
}, {
"JUN" : {
"A" : 6,
"B" : 7,
"C" : 8,
"D" : 6,
"E" : 4,
"F" : 3,
"G" : 2,
"H" : 1
}
}, {
"JUL" : {
"A" : 4,
"B" : 6,
"C" : 3,
"D" : 4,
"E" : 5,
"F" : 7,
"G" : 9,
"H" : 8
}
}, {
"AUG" : {
"A" : 1,
"B" : 5,
"C" : 3,
"D" : 6,
"E" : 7,
"F" : 8,
"G" : 1,
"H" : 2
}
}, {
"SEP" : {
"A" : 4,
"B" : 5,
"C" : 4,
"D" : 3,
"E" : 5,
"F" : 2,
"G" : 4,
"H" : 3
}
}, {
"OCT" : {
"A" : 1,
"B" : 2,
"C" : 4,
"D" : 5,
"E" : 7,
"F" : 9,
"G" : 8,
"H" : 7
}
}, {
"NOV" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"DEC" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
} ],
"DAILY" : [ {
"BL" : {
"L" : 42,
"M" : 19,
"N" : 20
}
}, {
"D-01" : {
"L" : 6,
"M" : 4,
"N" : 2
}
}, {
"D-02" : {
"L" : 5,
"M" : 6,
"N" : 8
}
}, {
"D-03" : {
"L" : 4,
"M" : 2,
"N" : 1
}
}, {
"D-04" : {
"L" : 5,
"M" : 6,
"N" : 6
}
}, {
"D-05" : {
"L" : 5,
"M" : 3,
"N" : 2
}
}, {
"D-06" : {
"L" : 6,
"M" : 7,
"N" : 8
}
}, {
"D-07" : {
"L" : 4,
"M" : 6,
"N" : 3
}
} ],
"WEEK" : [ {
"BL" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-01" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-02" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-03" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-04" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-05" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-06" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-07" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-08" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-09" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-10" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-11" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-12" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-13" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-14" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-15" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-16" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-17" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-18" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-19" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-20" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-21" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-22" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-23" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-24" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-25" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-26" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-27" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-28" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-29" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-30" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-31" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-32" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-33" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-34" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-35" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-36" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-37" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-38" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-39" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-40" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-41" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-42" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-43" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-44" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-45" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-46" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-47" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-48" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-49" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-50" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-51" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-52" : {
"I" : 1,
"J" : 4,
"K" : 6
}
} ]
},
"Sheet1" : {
"MONTH" : [ {
"BL" : {
"A" : 42,
"B" : 19,
"C" : 20,
"D" : 11,
"E" : 22,
"F" : 44,
"G" : 76,
"H" : 12
}
}, {
"JAN" : {
"A" : 4,
"B" : 4,
"C" : 2,
"D" : 1,
"E" : 8,
"F" : 7,
"G" : 2,
"H" : 5
}
}, {
"FEB" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"MAR" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
}, {
"APR" : {
"A" : 5,
"B" : 6,
"C" : 6,
"D" : 4,
"E" : 3,
"F" : 2,
"G" : 1,
"H" : 3
}
}, {
"MAY" : {
"A" : 5,
"B" : 3,
"C" : 2,
"D" : 1,
"E" : 6,
"F" : 7,
"G" : 6,
"H" : 5
}
}, {
"JUN" : {
"A" : 6,
"B" : 7,
"C" : 8,
"D" : 6,
"E" : 4,
"F" : 3,
"G" : 2,
"H" : 1
}
}, {
"JUL" : {
"A" : 4,
"B" : 6,
"C" : 3,
"D" : 4,
"E" : 5,
"F" : 7,
"G" : 9,
"H" : 8
}
}, {
"AUG" : {
"A" : 1,
"B" : 5,
"C" : 3,
"D" : 6,
"E" : 7,
"F" : 8,
"G" : 1,
"H" : 2
}
}, {
"SEP" : {
"A" : 4,
"B" : 5,
"C" : 4,
"D" : 3,
"E" : 5,
"F" : 2,
"G" : 4,
"H" : 3
}
}, {
"OCT" : {
"A" : 1,
"B" : 2,
"C" : 4,
"D" : 5,
"E" : 7,
"F" : 9,
"G" : 8,
"H" : 7
}
}, {
"NOV" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"DEC" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
} ],
"DAILY" : null,
"WEEK" : [ {
"BL" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-01" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-02" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-03" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-04" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-05" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-06" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-07" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-08" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-09" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-10" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-11" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-12" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-13" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-14" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-15" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-16" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-17" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-18" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-19" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-20" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-21" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-22" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-23" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-24" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-25" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-26" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-27" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-28" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-29" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-30" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-31" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-32" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-33" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-34" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-35" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-36" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-37" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-38" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-39" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-40" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-41" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-42" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-43" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-44" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-45" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-46" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-47" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-48" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-49" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-50" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-51" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-52" : {
"I" : 1,
"J" : 4,
"K" : 6
}
} ]
}
}
Suggested improvements
建议改进
You required all the structure to be Maps and Lists, however I think it would be lot nicer to have POJOs for Workbook
, Sheet
, Table
. It would simplify the notation a great deal and could be written into JSON the same way using the ObjectMapper
.
您要求所有的结构都是映射和列表,但是我认为如果有工作簿、表和表的pojo就更好了。它可以简化符号,并且可以用ObjectMapper以同样的方式写入JSON。
#1
0
Here is the complete code to parse the file into a Map<String, Map<String, List<Map<String, Map<String, Integer>>>>>
as required (See suggested improvements at the end)
下面是将文件解析为Map
-
This can parse all the sheets you have in your XLS file, as long as they all have the same general table structure.
这可以解析XLS文件中所有的表,只要它们具有相同的通用表结构。
-
The table positions are automatically found (because their name is on the top left corner), you just have to provide the name of the table to the
extractTable()
method.将自动找到表的位置(因为它们的名称在左上角),您只需向extractTable()方法提供表的名称。
Code
代码
public class ExcelReader {
public static void main(String[] args) {
String jsonString = new ExcelReader().fileToJson("stack.xlsx");
System.out.print(jsonString);
}
// Parses the file into a JSON string
private String fileToJson(String filename) {
List<XSSFSheet> sheets = getSheets(filename);
Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> workbookMap = parseSheets(sheets);
return toJsonString(workbookMap);
}
// Make a list of XSSFSheets out of the file
private List<XSSFSheet> getSheets(String fileName) {
List<XSSFSheet> sheets = new ArrayList<>();
try(InputStream XlsxFileToRead = new FileInputStream(fileName)) {
//Getting the workbook instance for xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(XlsxFileToRead);
//Getting all the sheets
for (int i=0; i<workbook.getNumberOfSheets(); i++) {
sheets.add(workbook.getSheetAt(i));
}
} catch (IOException e) {
e.printStackTrace();
}
return sheets;
}
// Parse a list of sheets into the desired structure
private Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> parseSheets(List<XSSFSheet> xsshSheets) {
Map<String, Map<String, List<Map<String, Map<String, Integer>>>>> workbookMap = new HashMap<>();
for(XSSFSheet xssfSheet : xsshSheets) {
String name = xssfSheet.getSheetName();
Map<String, List<Map<String, Map<String, Integer>>>> sheetMap = parseSheet(xssfSheet);
workbookMap.put(name, sheetMap);
}
return workbookMap;
}
// Parses a sheet into the desired structure
private Map<String, List<Map<String, Map<String, Integer>>>> parseSheet(XSSFSheet xsshSheet) {
List<Map<String, Map<String, Integer>>> months = extractTable(xsshSheet, "Month");
List<Map<String, Map<String, Integer>>> weeks = extractTable(xsshSheet, "Week");
List<Map<String, Map<String, Integer>>> days = extractTable(xsshSheet, "Daily");
Map<String, List<Map<String, Map<String, Integer>>>> sheet = new HashMap<>();
sheet.put("MONTH", months);
sheet.put("WEEK", weeks);
sheet.put("DAILY", days);
return sheet;
}
// Extracts a table
private List<Map<String, Map<String, Integer>>> extractTable(XSSFSheet sheet, String tableName) {
Cell tablePosition = findTablePosition(sheet, tableName);
if(tablePosition!=null)
return extractTable(sheet, tablePosition);
else return null;
}
// Finds the position of the cell with the given text
private Cell findTablePosition(XSSFSheet sheet, String tableName) {
for (Row row : sheet) {
for (Cell cell : row) {
if(cell.getCellTypeEnum() == CellType.STRING && tableName.equals(cell.getStringCellValue())) {
return cell;
}
}
}
return null;
}
// Extract a table
private List<Map<String, Map<String, Integer>>> extractTable(XSSFSheet sheet, Cell tablePosition) {
List<Map<String, Map<String, Integer>>> table = new ArrayList<>();
int headerRow = tablePosition.getRowIndex();
int valuesHeaderColumn = tablePosition.getColumnIndex();
// read all columns and rows until finding an empty one
int row = headerRow + 1;
int col = valuesHeaderColumn + 1;
// read all columns starting at col
while(true) {
Map<String, Map<String, Integer>> tableColumn = new HashMap<>();
Cell headerCell = sheet.getRow(headerRow).getCell(col);
if(headerCell == null)
break;
String columnName = headerCell.getStringCellValue().toUpperCase();
if("".equals(columnName))
break;
// read all rows starting at row
Map<String, Integer> values = new HashMap<>();
while(true) {
Row valueHeaderRow = sheet.getRow(row);
if(valueHeaderRow == null)
break;
Cell valueHeaderCell = valueHeaderRow.getCell(valuesHeaderColumn);
String valueHeader = valueHeaderCell.getStringCellValue();
if("".equals(valueHeader))
break;
Cell valueCell = sheet.getRow(row).getCell(col);
Integer value = (int)valueCell.getNumericCellValue();
values.put(valueHeader, value);
row++;
}
tableColumn.put(columnName, values);
// Add the columns map to the list
table.add(tableColumn);
col++;
row = headerRow + 1;
}
return table;
}
// Create the JSON string using Jackson ObjectMapper
private String toJsonString(Object o) {
ObjectMapper objectMapper = new ObjectMapper();
String jsonString = null;
try {
jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(o);
} catch(JsonProcessingException e) {
e.printStackTrace();
}
return jsonString;
}
}
Generated JSON (using Jackson's ObjectMapper
):
生成JSON(使用Jackson的ObjectMapper):
{
"Sheet2" : {
"MONTH" : [ {
"BL" : {
"A" : 42,
"B" : 19,
"C" : 20,
"D" : 11,
"E" : 22,
"F" : 44,
"G" : 76,
"H" : 12
}
}, {
"JAN" : {
"A" : 4,
"B" : 4,
"C" : 2,
"D" : 1,
"E" : 8,
"F" : 7,
"G" : 2,
"H" : 5
}
}, {
"FEB" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"MAR" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
}, {
"APR" : {
"A" : 5,
"B" : 6,
"C" : 6,
"D" : 4,
"E" : 3,
"F" : 2,
"G" : 1,
"H" : 3
}
}, {
"MAY" : {
"A" : 5,
"B" : 3,
"C" : 2,
"D" : 1,
"E" : 6,
"F" : 7,
"G" : 6,
"H" : 5
}
}, {
"JUN" : {
"A" : 6,
"B" : 7,
"C" : 8,
"D" : 6,
"E" : 4,
"F" : 3,
"G" : 2,
"H" : 1
}
}, {
"JUL" : {
"A" : 4,
"B" : 6,
"C" : 3,
"D" : 4,
"E" : 5,
"F" : 7,
"G" : 9,
"H" : 8
}
}, {
"AUG" : {
"A" : 1,
"B" : 5,
"C" : 3,
"D" : 6,
"E" : 7,
"F" : 8,
"G" : 1,
"H" : 2
}
}, {
"SEP" : {
"A" : 4,
"B" : 5,
"C" : 4,
"D" : 3,
"E" : 5,
"F" : 2,
"G" : 4,
"H" : 3
}
}, {
"OCT" : {
"A" : 1,
"B" : 2,
"C" : 4,
"D" : 5,
"E" : 7,
"F" : 9,
"G" : 8,
"H" : 7
}
}, {
"NOV" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"DEC" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
} ],
"DAILY" : [ {
"BL" : {
"L" : 42,
"M" : 19,
"N" : 20
}
}, {
"D-01" : {
"L" : 6,
"M" : 4,
"N" : 2
}
}, {
"D-02" : {
"L" : 5,
"M" : 6,
"N" : 8
}
}, {
"D-03" : {
"L" : 4,
"M" : 2,
"N" : 1
}
}, {
"D-04" : {
"L" : 5,
"M" : 6,
"N" : 6
}
}, {
"D-05" : {
"L" : 5,
"M" : 3,
"N" : 2
}
}, {
"D-06" : {
"L" : 6,
"M" : 7,
"N" : 8
}
}, {
"D-07" : {
"L" : 4,
"M" : 6,
"N" : 3
}
} ],
"WEEK" : [ {
"BL" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-01" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-02" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-03" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-04" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-05" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-06" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-07" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-08" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-09" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-10" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-11" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-12" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-13" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-14" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-15" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-16" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-17" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-18" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-19" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-20" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-21" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-22" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-23" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-24" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-25" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-26" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-27" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-28" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-29" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-30" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-31" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-32" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-33" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-34" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-35" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-36" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-37" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-38" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-39" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-40" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-41" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-42" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-43" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-44" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-45" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-46" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-47" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-48" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-49" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-50" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-51" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-52" : {
"I" : 1,
"J" : 4,
"K" : 6
}
} ]
},
"Sheet1" : {
"MONTH" : [ {
"BL" : {
"A" : 42,
"B" : 19,
"C" : 20,
"D" : 11,
"E" : 22,
"F" : 44,
"G" : 76,
"H" : 12
}
}, {
"JAN" : {
"A" : 4,
"B" : 4,
"C" : 2,
"D" : 1,
"E" : 8,
"F" : 7,
"G" : 2,
"H" : 5
}
}, {
"FEB" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"MAR" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
}, {
"APR" : {
"A" : 5,
"B" : 6,
"C" : 6,
"D" : 4,
"E" : 3,
"F" : 2,
"G" : 1,
"H" : 3
}
}, {
"MAY" : {
"A" : 5,
"B" : 3,
"C" : 2,
"D" : 1,
"E" : 6,
"F" : 7,
"G" : 6,
"H" : 5
}
}, {
"JUN" : {
"A" : 6,
"B" : 7,
"C" : 8,
"D" : 6,
"E" : 4,
"F" : 3,
"G" : 2,
"H" : 1
}
}, {
"JUL" : {
"A" : 4,
"B" : 6,
"C" : 3,
"D" : 4,
"E" : 5,
"F" : 7,
"G" : 9,
"H" : 8
}
}, {
"AUG" : {
"A" : 1,
"B" : 5,
"C" : 3,
"D" : 6,
"E" : 7,
"F" : 8,
"G" : 1,
"H" : 2
}
}, {
"SEP" : {
"A" : 4,
"B" : 5,
"C" : 4,
"D" : 3,
"E" : 5,
"F" : 2,
"G" : 4,
"H" : 3
}
}, {
"OCT" : {
"A" : 1,
"B" : 2,
"C" : 4,
"D" : 5,
"E" : 7,
"F" : 9,
"G" : 8,
"H" : 7
}
}, {
"NOV" : {
"A" : 5,
"B" : 6,
"C" : 8,
"D" : 9,
"E" : 7,
"F" : 6,
"G" : 4,
"H" : 2
}
}, {
"DEC" : {
"A" : 4,
"B" : 2,
"C" : 1,
"D" : 9,
"E" : 8,
"F" : 7,
"G" : 4,
"H" : 3
}
} ],
"DAILY" : null,
"WEEK" : [ {
"BL" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-01" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-02" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-03" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-04" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-05" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-06" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-07" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-08" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-09" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-10" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-11" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-12" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-13" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-14" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-15" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-16" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-17" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-18" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-19" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-20" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-21" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-22" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-23" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-24" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-25" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-26" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-27" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-28" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-29" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-30" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-31" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-32" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-33" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-34" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-35" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-36" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-37" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-38" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-39" : {
"I" : 42,
"J" : 19,
"K" : 20
}
}, {
"WK-40" : {
"I" : 6,
"J" : 4,
"K" : 2
}
}, {
"WK-41" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-42" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-43" : {
"I" : 5,
"J" : 6,
"K" : 6
}
}, {
"WK-44" : {
"I" : 5,
"J" : 3,
"K" : 2
}
}, {
"WK-45" : {
"I" : 6,
"J" : 7,
"K" : 8
}
}, {
"WK-46" : {
"I" : 4,
"J" : 6,
"K" : 3
}
}, {
"WK-47" : {
"I" : 1,
"J" : 5,
"K" : 3
}
}, {
"WK-48" : {
"I" : 4,
"J" : 5,
"K" : 4
}
}, {
"WK-49" : {
"I" : 1,
"J" : 2,
"K" : 4
}
}, {
"WK-50" : {
"I" : 5,
"J" : 6,
"K" : 8
}
}, {
"WK-51" : {
"I" : 4,
"J" : 2,
"K" : 1
}
}, {
"WK-52" : {
"I" : 1,
"J" : 4,
"K" : 6
}
} ]
}
}
Suggested improvements
建议改进
You required all the structure to be Maps and Lists, however I think it would be lot nicer to have POJOs for Workbook
, Sheet
, Table
. It would simplify the notation a great deal and could be written into JSON the same way using the ObjectMapper
.
您要求所有的结构都是映射和列表,但是我认为如果有工作簿、表和表的pojo就更好了。它可以简化符号,并且可以用ObjectMapper以同样的方式写入JSON。