Recently, the teacher assigned a task, with Java to Excel suffix named xlsx file for a simple increase, delete, change, check operation; Although it is a simple procedure, can as just contact me, or some bumps. But fortunately it was done, a simple summary.
First import a Poi.jar online there are a lot of this resources can be downloaded
Xssfsheet Sheet=null;
Xssfworkbook Book=null;
One: Check (find the Excel table at the local specified location, output in the console)
public void Print_excel () {
//Gets the number of rows in the Excel table
int lastrownumber = Sheet.getlastrownum ();
String ret= "";
Get data for
(a=0;a<lastrownumber;a++) {
xssfrow row=sheet.getrow (a);
Gets the number of columns in the Excel table
int lastcellnum=row.getlastcellnum ();
for (b=0;b<lastcellnum;b++) {
Xssfcell cell =row.getcell (b);
Determine the type returned by the cell and assign it to the RET
ret=excel_operation.getexcelcellvalue (cell);
System.out.print (ret+ "");
}
System.out.println ();
}
}
Two: Change (Modify the contents of a cell in an Excel table)
public void Set_excelcell (int i,int j,string str) {
//Get the information for the row
Xssfrow Row=sheet.getrow (i-1);
Gets the information of the column
Xssfcell cell =row.getcell (j-1);
Gets the contents of the modified cell
string string = Excel_operation.getexcelcellvalue (cell);
The contents of the modified cell are str
cell.setcellvalue (str);
System.out.println ("+string+" has been changed to "+str)";
}
Three: Add (Insert a line 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 No. 0 , line No. 0 is for definition!
"); The IF (RowIndex > Sheet.getlastrownum () + 1) {throw new IllegalArgumentException ("can only be inserted after the last row.
"); 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 inserting the last line//not doing any processing} else {//moving down a sheet.shiftrows (rowind
Ex, 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 (deletes the contents of the specified line)
Deletes a row of data (in an Excel table, the row is starting from 0) public
void Delete (int rowIndex) {
//deletes the last row
if (RowIndex = = Sheet.getlastrow Num ()) {
Sheet.removerow (Sheet.getrow (Sheet.getlastrownum ()));
The deletion is not the last line
} else {
sheet.shiftrows (RowIndex + 1, sheet.getlastrownum (),-1, true, false);
Sheet.removerow (Sheet.getrow (Sheet.getlastrownum () + 1));
}
}
V: To determine the return type (because the contents of the Excel table are different, have character type, have integer type, etc., you must judge its type in order to output)
private static string Getexcelcellvalue (Xssfcell cell) {string ret= "";
try {//When the return value type is NULL returns the space 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 return value is a numeric type} else if (cell.getcelltype () = = xssfcell.cell_type_numeric) {ret = "+ Cell.getnumericcel
LValue ();
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 a Boolean type} else if (cell.getcelltype () = = Xssfcell.cell_type_boolean) {ret = "+ Cell.getbooleancellva
Lue ();
When the return value's type is empty} else if (cell.getcelltype () = = Xssfcell.cell_type_blank) {ret = "";
} catch (Exception ex) {ex.printstacktrace ();
ret = "";
return ret;
}