Shulou(Shulou.com)06/02 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.