Recently, the teacher assigned a task to use Java to simply add, delete, modify and check files with excel suffix xlsx. Although it is a simple program, it is still a bit bumpy as a newcomer. Fortunately, it was completed and made a simple summary.
First, I imported a poi.jar. There are many resources available for download on the Internet.
XSSFSheet sheet=null;
XSSFWorkbook book=null;
1: Check (find the excel table in the local specified location, output it in the console)
public void print_excel(){ //Get the number of rows of the excel table int lastrownumber = sheet.getLastRowNum(); String ret=" "; //Get the data for(a=0;a<lastrownumber;a++){ XSSFRow row=sheet .getRow(a); //Get the number of columns in the excel table int lastcellnum=row.getLastCellNum(); for(b=0;b<lastcellnum;b++){ XSSFCell cell =row.getCell(b); //Judge cel l The returned type and assign it to ret ret=excel_operation.getExcelCellValue(cell); System.out.print(ret+" "); } System.out.println(); } }2: Change (modify the content of a cell in the excel table)
public void set_excelcell(int i,int j,String str){ //Get row information XSSFRow row=sheet.getRow(i-1); //Get column information XSSFCell cell =row.getCell(j-1); //Get the content of the modified cell String string = excel_operation.getExcelCellValue(cell); //Change the content of the modified cell to str cell.setCellValue(str); System.out.println("Acknowledged"+string+" Change to "+str);}Three: Add (insert a row of contents in the excel table to the specified position)
public void insert(int rowIndex, String[] objs) { if(rowIndex == 0) { throw new IllegalArgumentException("Cannot be inserted in line 0, line 0 is used to define!"); } if(rowIndex > sheet.getLastRowNum() + 1) { throw new IllegalArgumentException("It can only be inserted after the last line at most."); } int referRowIndex = -1; //Reference line number of the line. if(sheet.getPhysicalNumberOfRows() <= 1) { referRowIndex = rowIndex - 1; } else { referRowIndex = rowIndex - 1; if(rowIndex == sheet.getLastRow Num() + 1) { // is to insert the last line//No Do any processing} else { //Move one down one sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false); } } Row targetRow = sheet.createRow(rowIndex); Row referRow = sheet.getRow (referRowIndex); // Reference line Cell targetCell, referCell; for (int i = 0; i < objs.length; i++) { targetCell = targetRow.createCell(i); referCell = referRow.ge tCell(i); targetCell.setCellStyle (referCell.getCellStyle()); targetCell.setCellType(referCell.getCellType()); targetCell.setCellValue(objs[i]);// Set value} }Four: Delete (delete the content of the specified line)
// Delete a row of data (in Excel table, rows are calculated from 0) public void delete(int rowIndex) { // The last row that is deleted is if(rowIndex == sheet.getLastRowNum()) { sheet.removeRow(sheet. getRow(sheet.getLastRowNum())); //It is not the last line that is deleted} else { sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1, true, false); sheet.remov eRow(sheet.getRow( sheet.getLastRowNum() + 1)); } }5: Determine the return type (because the content in the excel table is different, including character type, integer type, etc., it is necessary to judge its type before outputting)
private static String getExcelCellValue(XSSFCell cell) { String ret=" "; try { //Return space if (cell == null) { ret = " "; //When the type of return value is character String type} else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { ret = cell.getStringCellValue(); //When the type of return value is a numeric type} else if (cell.getCellType( ) == XSSFCell.CELL_TYPE_NUMERIC ) { ret = "" + cell.getNumericCellValue(); //When the type of the return value is expression type} else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { ret = cell.getCellF ormula(); // When the type of the return value is an exception type} else if (cell.getCellType() == XSSFCell.CELL_TYPE_ERROR) { ret = " " + cell.getErrorCellValue(); //When the type of the return value is a boolean type} else if (cell : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : .getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { ret = " " + cell.getBooleanCellValue(); //When the type of return value is empty} else if (cell.getCellType() == XSSFCel l.CELL_TYPE_BLANK) { ret = " "; } } catch (Exception ex) { ex.printStackTrace(); ret = " "; } return ret;}