The project needs to parse Excel documents to obtain data, so I found some information online, and combined with my use this time, and wrote down my experience:
1. The following dependencies are required for the maven project:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10-FINAL</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.10-FINAL</version> </dependency>
Directly go to the test class, with complete annotations in the class:
package shindo.Java;import java.io.FileInputStream;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil { public static void main(String[] args) { String path = "D://IDE//workspace-Neon//Java//src//refund.xls"; try { List<List<String>> result = new ExcelUtil().readXls(path); System.out.println(result.size()); for (int i = 0; i < result.size(); i++) { List<String> model = result.get(i); System.out.println("orderNum:" + model.get(0) + "--> orderAmount:" + model.get(1)); } } catch (Exception e) { e.printStackTrace(); } } /** * * @Title: readXls * @Description: Processing xls files* @param @param path * @param @return * @param @throws Exception Set file* @return List<List<String>> Return type* @throws * * It is not difficult to find its processing logic from the code: * 1. First use InputStream to get the io stream of the excel file* 2. Then use an in-memory excel file HSSFWorkbook type object, which represents the entire excel file. * 3. Loop for each page of this excel file* 4. Loop for each row in each page* 5. Process each cell in each row to get the value of this cell* 6. Add the result of this line to a List array* 7. Add the result of each row to the final total result* 8. After parsing, an object of type List<List<String>> is obtained* */ private List<List<String>> readXls(String path) throws Exception { InputStream is = new FileInputStream(path); // HSSFWorkbook identifies the entire excel HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<List<String>> result = new ArrayList<List<String>>(); int size = hssfWorkbook.getNumberOfSheets(); // Loop each page and process the current loop page for (int numSheet = 0; numSheet < size; numSheet++) { // HSSFSheet identifies a certain page HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // Process the current page and read each row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { // HSSFRow represents the row HSSFRow hssfRow = hssfSheet.getRow(rowNum); int minColIx = hssfRow.getFirstCellNum(); int maxColIx = hssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>(); // traversal and change rows to get processing each cell element for (int colIx = minColIx; colIx < maxColIx; colIx++) { // HSSFCell Represents cell HSSFCell cell = hssfRow.getCell(colIx); if (cell == null) { continue; } rowList.add(getStringVal(cell)); } result.add(rowList); } } return result; } /** * * @Title: readXlsx * @Description: Processing Xlsx files* @param @param path * @param @return * @param @throws Exception Setting file* @return List<List<String>> Return type* @throws */ private List<List<String>> readXlsx(String path) throws Exception { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<List<String>> result = new ArrayList<List<String>>(); // Loop every page and process the current loop page for (XSSFSheet xssfSheet : xssfWorkbook) { if (xssfSheet == null) { continue; } // Process the current page and read every row for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); int minColIx = xssfRow.getFirstCellNum(); int maxColIx = xssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>(); for (int colIx = minColIx; colIx < maxColIx; colIx++) { XSSFCell cell = xssfRow.getCell(colIx); if (cell == null) { continue; } rowList.add(cell.toString()); } result.add(rowList); } } return result; } // Existing problems/* * In fact, sometimes the data we want to get is the data in excel, but in the end it is found that the result is not ideal* If the data in your excel is a number, you will find that the corresponding one in Java has become a scientific notation method. * So when obtaining values, you need to do some special processing to ensure that you get the results you want. The online approach is to format numerical data to obtain the results you want. * The following is another method. Before this, let's take a look at the toString() method in poi: * * This method is the poi method. From the source code, we can find that the processing flow is: * 1. Get the cell type* 2. Format the data according to the type and output it. This creates a lot of things that we are not looking for*, so we make a transformation of this method. */ /*public String toString(){ switch(getCellType()){ case CELL_TYPE_BLANK: return ""; case CELL_TYPE_BOOLEAN: return getBooleanCellValue() ? "TRUE" : "FALSE"; case CELL_TYPE_ERROR: return ErrorEval.getText(getErrorCellValue()); case CELL_TYPE_FORMULA: return getCellFormula(); case CELL_TYPE_NUMERIC: if(DateUtil.isCellDateFormatted(this)){ DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy") return sdf.format(getDateCellValue()); } return getNumericCellValue() + ""; case CELL_TYPE_STRING: return getRichStringCellValue().toString(); default : return "Unknown Cell Type:" + getCellType(); } }*/ /** * The method of rebuilding the default toString() of poi is as follows* @Title: getStringVal * @Description: 1. For unfamiliar types, or empty, return ""control string* 2. If it is a number, modify the cell type to String and then return String, so that the number is not formatted* @param @param cell * @param @return Setting file* @return String Return type* @throws */ public static String getStringVal(HSSFCell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "TRUE" : "FALSE"; case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: cell.setCellType(Cell.CELL_TYPE_STRING); return cell.getStringCellValue(); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return ""; } }}Summarize
The above is all the content of this article about using poi package to read Excel document code in Java. I hope it will be helpful to everyone. Interested friends can continue to refer to other related topics on this site. If there are any shortcomings, please leave a message to point it out. Thank you friends for your support for this site!