1. The jar that needs to be imported
1.commons-collections4-4.1.jar
2.poi-3.17-beta1.jar
3.poi-ooxml-3.17-beta1.jar
4.poi-ooxml-schemas-3.17-beta1.jar
5.xmlbeans-2.6.0.jar
2. Main API
1.import org.apache.poi.ss.usermodel.Workbook, corresponding to Excel document;
2.import org.apache.poi.hssf.usermodel.HSSFWorkbook, corresponding to the Excel document in xls format;
3.import org.apache.poi.xssf.usermodel.XSSFWorkbook, corresponding to the Excel document in xlsx format;
4.import org.apache.poi.ss.usermodel.Sheet, corresponding to a sheet in the Excel document;
5.import org.apache.poi.ss.usermodel.Row, corresponding to a line in a sheet;
6.import org.apache.poi.ss.usermodel.Cell, corresponding to a cell.
3. The code is as follows
package poi;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class Testpoi { public static void main(String[] args) { Workbook wb =null; Sheet sheet = null; Row row = null; List<Map<String,String>> list = null; String cellData = null; String filePath = "D://test.xlsx"; String columns[] = {"name","age","score"}; wb = readExcel(filePath); if(wb != null){ //Use to store data in the table list = new ArrayList<Map<String,String>>(); //Get the first sheet sheet = wb.getSheetAt(0); //Get the maximum number of rows int rownum = sheet.getPhysicalNumberOfRows(); //Get the first row row = sheet.getRow(0); //Get the maximum number of columns int colnum = row.getPhysicalNumberOfCells(); for (int i = 1; i<rownum; i++) { Map<String,String> map = new LinkedHashMap<String,String>(); row = sheet.getRow(i); if(row !=null){ for (int j=0;j<colnum;j++){ cellData = (String) getCellFormatValue(row.getCell(j)); map.put(columns[j], cellData); } }else{ break; } list.add(map); } } //Tranquility through the parsed list for (Map<String,String> map : list) { for (Entry<String,String> entry : map.entrySet()) { System.out.print(entry.getKey()+":"+entry.getValue()+","); } System.out.println(); } } //Read excel public static Workbook readExcel(String filePath){ Workbook wb = null; if(filePath==null){ return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is = null; try { is = new FileInputStream(filePath); if(".xls".equals(extString)){ return wb = new HSSFWorkbook(is); }else if(".xlsx".equals(extString)){ return wb = new XSSFWorkbook(is); }else{ return wb = null; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return wb; } public static Object getCellFormatValue(Cell cell){ Object cellValue = null; if(cell!=null){ //Judge cell type switch(cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC:{ cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA:{ //Judge whether the cell is in the date format if(DateUtil.isCellDateFormatted(cell)){ //Convert to date format YYYY-mm-dd cellValue = cell.getDateCellValue(); }else{ //Numerical cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:{ cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } }else{ cellValue = ""; } return cellValue; }}4. Operation results
Before running the code, make sure there is a test.xlsx document on disk D, otherwise the report file will not be found; the header of the table in the Excel document must correspond to String columns[] = {"name","age","score"} in the code.
Summarize
The above is the method of java parsing Excel (two formats of xls and xlsx) introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!