Java Operations Excel table

Recently, the teacher assigned a task, using Java to Excel suffix named xlsx file for simple increase, delete, change, check operation; Although it is a simple program, but as I have just come into contact with some bumps. But fortunately it was finished, a simple summary.

First imported a Poi.jar online with a lot of this resource can be downloaded

Xssfsheet Sheet=null;
Xssfworkbook Book=null;

A: Check (find the Excel table at the local specified location, in the console output)

public void Print_excel () {

Get the number of rows in an Excel table
int lastrownumber = Sheet.getlastrownum ();
String ret= "";
Get Data
for (a=0;a<lastrownumber;a++) {
Xssfrow Row=sheet.getrow (a);
Get the number of columns in an 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 a value to RET
Ret=excel_operation.getexcelcellvalue (cell);
System.out.print (ret+ "");
}
System.out.println ();
}
}

Second: Change (Modify the contents of a cell in an Excel table)

public void Set_excelcell (int i,int j,string str) {
Get information about a row
Xssfrow Row=sheet.getrow (i-1);
Get information about a column
Xssfcell cell =row.getcell (j-1);
Get the contents of a modified cell
String string = Excel_operation.getexcelcellvalue (cell);
Modify the contents of a cell to be str
Cell.setcellvalue (str);
System.out.println ("+string+" has been changed to "+str");
}

Three: Add (insert a row into the Excel table to the specified location)

　　

public void Insert (int rowIndex, string[] objs) {
if (RowIndex = = 0) {
throw new IllegalArgumentException ("cannot be inserted in line No. 0, line No. 0 is used to define!") ");
}
if (RowIndex > Sheet.getlastrownum () + 1) {
throw new IllegalArgumentException ("can be inserted at most 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 the last line inserted
Do not do any processing
} else {

Move 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.getcell (i);

Targetcell.setcellstyle (Refercell.getcellstyle ());
Targetcell.setcelltype (Refercell.getcelltype ());

Targetcell.setcellvalue (Objs[i]);//Set Value
}
}

IV: Delete (remove the contents of the specified line)

Delete a row of data (in an Excel table, the row is from 0)
public void Delete (int rowIndex) {

The last line is deleted
if (RowIndex = = Sheet.getlastrownum ()) {
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: Determine the return type (because the contents of the Excel table are different, have character type, have integer type, etc., must be judged by its type to be 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 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 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.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;
}

Java Operations Excel table