Home>

The teacher recently assigned a task,Simple increase the file named excel suffix xlsx with java,Delete, modify, and check operations;although it is a simple procedure,But as a new contact, I still have some bumps.Fortunately, it's done,Make a simple summary.

First 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 at the specified local location and output it in the console)

public void print_excel () {
///Get the number of rows in the excel table
Int lastrownumber=sheet.getlastrownum ();
String ret="";
  //retrieve 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 the type returned by the cell and assign it to ret
Ret=excel_operation.getexcelcellvalue (cell);
System.out.print (ret + "");
}
System.out.println ();
}
}

Two:change(Modify the content of a cell in the excel table)

public void set_excelcell (int i, int j, string str) {
//Get line 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);
//Modify the content of the cell to str
Cell.setcellvalue (str);
System.out.println ("Changed" + string + "to" + str);
}

Three:increase(Insert a row 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 ("Can only be inserted after the last line.
");
}
Int referrowindex=-1;//The line number of the reference line.
If (sheet.getphysicalnumberofrows ()<= 1) {
Referrowindex=rowindex-1;
} Else {
Referrowindex=rowindex-1;
If (rowindex == sheet.getlastrownum () + 1) {//is insert the last row
//do nothing
} Else {
//move down
Sheet.shiftrows (rowindex, sheet.getlastrownum (), 1, true, false);
}
}
Row targetrow=sheet.createrow (rowindex);
Row referrow=sheet.getrow (referrowindex);//reference row
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 value
}
}

Four:delete(delete the contents of the specified line)

//delete a row of data (excel table, rows are counted from 0)
Public void delete (int rowindex) {
//delete the last line
If (rowindex == sheet.getlastrownum ()) {
Sheet.removerow (sheet.getrow (sheet.getlastrownum ()));
//Delete is not the last line
} Else {
Sheet.shiftrows (rowindex + 1, sheet.getlastrownum (), -1, true, false);
Sheet.removerow (sheet.getrow (sheet.getlastrownum () + 1));
}
}

Five:determine the return type(Because the content in the excel table is different,There are characters,There are integers, etc.You must judge its type to output)

private static string getexcelcellvalue (xssfcell cell) {
String ret="";
Try {
//returns a space when the type of the return value is empty
If (cell == null) {
Ret="";
//When the type of the return value is a string type
} Else if (cell.getcelltype () == xssfcell.cell_type_string) {
Ret=cell.getstringcellvalue ();
//When the type of the returned value is a numeric type
} Else if (cell.getcelltype () == xssfcell.cell_type_numeric) {
Ret="" + cell.getnumericcellvalue ();
//When the type of the return value is an 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 type of the return value is Boolean
} Else if (cell.getcelltype () == xssfcell.cell_type_boolean) {
Ret="" + cell.getbooleancellvalue ();
//When the type of the return value is empty
} Else if (cell.getcelltype () == xssfcell.cell_type_blank) {
Ret="";
}
} Catch (exception ex) {
Ex.printstacktrace ();
Ret="";
}
Return ret;
}
  • Previous PHP Lottery Algorithm Program Code Sharing
  • Next Tutorial for writing GUI dialogs in Java