How to parse excel table data into JSONArray by java
This article is about how java parses excel table data into JSONArray. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
Program body:
/ * json header template * /
Public static final int HEADER_VALUE_TYPE_O = 1
/ * instance * /
Public static ExcelToJson getExcelToJson () {
Return new ExcelToJson ()
}
/ * read excel*/
Public JSONArray readExcel (File file, int headerIndex, int headType) {
List lists = new ArrayList ()
If (! fileNameFileter (file)) {
Return null
} else {
Try {
WorkbookFactory factory = new WorkbookFactory ()
Workbook workbook = factory.create (file)
Sheet sheet = workbook.getSheetAt (0)
Row headerRow = getHeaderRow (sheet, headerIndex)
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper () .createFormulaEvaluator ()
For (int r = headerIndex + 1; r < sheet.getLastRowNum () + 1; rust +) {
Row dataRow = sheet.getRow (r)
Map map = new HashMap ()
For (int h = 0; h < dataRow.getLastCellNum (); hype +) {
String key = getHeaderCellValue (headerRow, h, headType)
Object value = getCellValue (dataRow, h, formulaEvaluator)
If (! key.equals (") & &! key.equals (" null ") & & key! = null) {
Map.put (key, value)
}
}
Lists.add (map)
}
} catch (Exception e) {
E.printStackTrace ()
}
}
JSONArray jsonArray = JSONArray.fromObject (lists)
Return jsonArray
}
/ * File filtering, only forms can be processed * /
Public boolean fileNameFileter (File file) {
Boolean endsWith = false
If (file! = null) {
String fileName = file.getName ()
EndsWith = fileName.endsWith (".xls") | | fileName.endsWith (".xlsx")
}
Return endsWith
}
/ * get rows of the table * /
Public Row getHeaderRow (Sheet sheet, int index) {
Row headerRow = null
If (sheet! = null) {
HeaderRow = sheet.getRow (index)
}
Return headerRow
}
/ * get the value*/ of the header
Public String getHeaderCellValue (Row headerRow, int cellIndex, int type) {
Cell cell = headerRow.getCell (cellIndex)
String headerValue = null
If (cell! = null) {
If (HEADER_VALUE_TYPE_O = = type) {
HeaderValue = cell.getRichStringCellValue () .getString ()
}
}
Return headerValue
}
/ * get the value of the cell * /
Public Object getCellValue (Row row, int cellIndex, FormulaEvaluator formulaEvaluator) {
Cell cell = row.getCell (cellIndex)
If (cell! = null) {
Switch (cell.getCellType ()) {
/ / String
Case Cell.CELL_TYPE_STRING:
Return cell.getRichStringCellValue () .getString ()
/ / Number
Case Cell.CELL_TYPE_NUMERIC:
If (DateUtil.isCellDateFormatted (cell)) {
Return cell.getDateCellValue () .getTime ()
} else {
Return cell.getNumericCellValue ()
}
/ / boolean
Case Cell.CELL_TYPE_BOOLEAN:
Return cell.getBooleanCellValue ()
/ / formula
Case Cell.CELL_TYPE_FORMULA:
Return formulaEvaluator.evaluate (cell) .getNumberValue ()
Default:
Return null
}
}
Return null
}
Test method:
/ * Test entry * /
Public static void main (String [] args) {
File file = new File ("C:\\ a.xls")
ExcelToJson excelToJson = getExcelToJson ()
JSONArray jsonArray = excelToJson.readExcel (file, 0,1)
System.out.println (jsonArray.toString ())
}
Dependent jar packages:
Org.apache.poi
Poi
3.15
Org.apache.poi
Poi-ooxml
3.15
Net.sourceforge.jexcelapi
Jxl
2.6.12
Thank you for reading! On "java how to achieve parsing excel table data into JSONArray" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!
