Some time ago Java processing Excel files are using JXL library, later found that this library does not support XLSX format, and can not handle a large amount of data, found the LIBRARY POI

1: What is Apache POI

The Apache POI project is tasked with creating and maintaining Java apis based on the Office Open XML standard (OOXML) and Microsoft’s OLE2 Composite Document Format (OLE2) to handle various file formats. In short, you can use Java to read and write Microsoft Excel files. In addition, you can use Java to read and write Microsoft Word and Microsoft PowerPoint files

1.1 Apache POI Environment

From poi.apache.org/download.ht… Download the latest POI library and unzip it to get the JARS we need

1.2 Creating a SHEET

The first step to create a sheet is to create a workbook object (XSSFWorkbook). The second step is to create a sheet using the XSSFWorkbook object

XSSFWorkbook workbook = new XSSFWorkbook(); Create a spreadsheet XSSFSheet spreadsheet = workbook.createsheet ("Sheet Name");Copy the code

XSSFWorkbook: Supports both. XLS and. XLSX formats. Supports Office 2007 or later. You can read and write to Excel, it’s compatible with.xls, it doesn’t support higher versions of office so what we’re going to do is we’re going to use XSSFWorkbook to manipulate tables, and see how do we save data to cells

public class WriteSheetTest {

 private static final String PATH = "WriteSheet.xlsx"; Public static void main(String[] args) throws Exception {FileInputStream fis = new FileInputStream(PATH); XSSFWorkbook workbook = new XSSFWorkbook(); // Create a spreadsheet XSSFSheet sheet = workbook.createsheet ("mySheet"); // XSSFRow row;for(int r = 0 ; r<3; Row = sheet.createrow (r); row = sheet.createrow (r);for(int c = 0; c<3; C ++) {//2. Create a Cell Cell = row.createcell (c); cell.setCellValue(r+c); } } FileOutputStream fos = new FileOutputStream(PATH); workbook.write(fos); workbook.close(); }}Copy the code


1.3 Reading a SHEET

public class Readsheet 
{
   static XSSFRow row;

public static void main(String[] args) throws Exception 
   {
      FileInputStream fis = new FileInputStream(
      new File("WriteSheet.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet = workbook.getsheet ("mySheet"); // int size = sheet.getLastRowNum(); // System.out.println(size); // int s= sheet.getPhysicalNumberOfRows(); // System.out.println(s); Iterator<Row> rowIterator = sheet.rowiterator ();while(rowIterator.hasNext()) {
           row = (XSSFRow) rowIterator.next();
           Iterator<Cell> cellIterator = row.cellIterator();
           while(cellIterator.hasNext()) {
               Cell cell = cellIterator.next();
               switch(cell.getCellTypeEnum() ) {
               case NUMERIC:
                  double val = cell.getNumericCellValue();
                  System.out.print(val+"\t\t");
                  break ;
               case STRING:
                   String str = cell.getStringCellValue();
                   System.out.print(str+"\t\t"); } } System.out.println(); } fis.close(); }}Copy the code

0.0 1.0 2.0 1.0 2.0 3.0 2.0 3.0 4.0 It is necessary to judge the data type before reading the cell value, otherwise the data type error will be reported.