Java implementation to manipulate excel spreadsheets
- 2020-04-01 04:15:29
- OfStack
Recently, the teacher assigned a task to simply add, delete, change and check excel files with the suffix XLSX in Java. Although it is a simple procedure, but as a new contact for me or some bumps and turns. But the good news is that we've done it.
First, I imported a poi.jar. There are many resources available for download on the web
XSSFSheet sheet = null;
XSSFWorkbook book = null;
A: check (find the excel sheet in the specified location locally and output it in the console)
public void print_excel(){
//Gets the number of rows in an excel spreadsheet
int lastrownumber = sheet.getLastRowNum();
String ret=" ";
//To get the data
for(a=0;a<lastrownumber;a++){
XSSFRow row=sheet.getRow(a);
//Gets the number of columns in an excel spreadsheet
int lastcellnum=row.getLastCellNum();
for(b=0;b<lastcellnum;b++){
XSSFCell cell =row.getCell(b);
//Determines the type returned by the cell and assigns the value to ret
ret=excel_operation.getExcelCellValue(cell);
System.out.print(ret+" ");
}
System.out.println();
}
}
Change 2: (modify the contents of a cell in the excel spreadsheet)
public void set_excelcell(int i,int j,String str){
//Get row information
XSSFRow row=sheet.getRow(i-1);
//Gets the column information
XSSFCell cell =row.getCell(j-1);
//Gets the contents of the cell being modified
String string = excel_operation.getExcelCellValue(cell);
//Change the contents of the cell to STR
cell.setCellValue(str);
System.out.println(" Has been to "+string+" Instead of "+str);
}
Three: increasing (insert a line in the excel spreadsheet to the specified location)
public void insert(int rowIndex, String[] objs) {
if(rowIndex == 0) {
throw new IllegalArgumentException(" You can't cut in 0 Ok, the first 0 Rows are defined! ");
}
if(rowIndex > sheet.getLastRowNum() + 1) {
throw new IllegalArgumentException(" At most, it can only be inserted after the last row. ");
}
int referRowIndex = -1; //Refer to the line number.
if(sheet.getPhysicalNumberOfRows() <= 1) {
referRowIndex = rowIndex - 1;
} else {
referRowIndex = rowIndex - 1;
if(rowIndex == sheet.getLastRowNum() + 1) { //Insert the last row
//Don't do anything
} else {
//I'm going to move down a little bit
sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false);
}
}
Row targetRow = sheet.createRow(rowIndex);
Row referRow = sheet.getRow(referRowIndex); //The reference line
Cell targetCell, referCell;
for (int i = 0; i < objs.length; i++) {
targetCell = targetRow.createCell(i);
referCell = referRow.getCell(i);
targetCell.setCellStyle(referCell.getCellStyle());
targetCell.setCellType(referCell.getCellType());
targetCell.setCellValue(objs[i]);//Set the value
}
}
Four: delete (deletes the contents of the specified row)
//Delete a row (in Excel, rows count from 0)
public void delete(int rowIndex) {
//The last line was deleted
if(rowIndex == sheet.getLastRowNum()) {
sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
//Not the last line
} else {
sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1, true, false);
sheet.removeRow(sheet.getRow(sheet.getLastRowNum() + 1));
}
}
Five: determining the return type (because the contents of excel sheet are different, there are characters, integers, etc., the type must be determined to output)
private static String getExcelCellValue(XSSFCell cell) {
String ret=" ";
try {
//Returns a space when the type of the return value is null
if (cell == null) {
ret = " ";
//When the return value is of type string
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
ret = cell.getStringCellValue();
//When the return value is of type numeric
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
ret = "" + cell.getNumericCellValue();
//When the type of the return value is the expression type
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
ret = cell.getCellFormula();
//When the type of the return value is an exception type
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_ERROR) {
ret = " " + cell.getErrorCellValue();
//When the return value is of type Boolean
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
ret = " " + cell.getBooleanCellValue();
//When the type of the return value is null
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
ret = " ";
}
} catch (Exception ex) {
ex.printStackTrace();
ret = " ";
}
return ret;
}