Java Import Export Excel Common operations summary and simple example

A summary of how Excel is commonly set in POI:

Operate Excel as follows

Hssfworkbook wb = new Hssfworkbook (); Create a webbook that corresponds to an Excel file

Hssfsheet sheet = Wb.createsheet (); Add a sheet, corresponding to the sheet construction method in the Excel file can have a parameter can also be Wb.createsheet ("Student table One")

Hssfrow row = Sheet.createrow ((int) 0); The first line of sheet

Hssfcell cell = Row.createcell (0); The first column of a row
Cell.setcellvalue ("School Number"); Add Value

Hssfcellstyle style = Wb.createcellstyle (); Create a Style

1. Set the background color:
Style.setfillforegroundcolor ((short) 10)///Set background color for red parameter also available (HSSFCOLOR.RED.INDEX)
Style.setfillpattern (Hssfcellstyle.solid_foreground);

2. Set the border:
Style.setborderbottom (Hssfcellstyle.border_thin); Bottom Frame
Style.setborderleft (Hssfcellstyle.border_thin);//left Border
Style.setbordertop (Hssfcellstyle.border_thin);//Top Border
Style.setborderright (Hssfcellstyle.border_thin);//Right Border

3. Set Center:
Style.setverticalalignment (Hssfcellstyle.vertical_center); Center vertically
Style.setalignment (hssfcellstyle.align_center);//Horizontal Center

4. Set the font:
Style.setfont (font);//font format

Hssffont font = Wb.createfont ();
Font.setfontname ("Blackbody");
Font.setfontheightinpoints ((short) 16);//Set Font size
Font.setboldweight (hssffont.boldweight_bold);//Bold display

5. Set the column width:
Sheet.setcolumnwidth (0, 10000); The first parameter represents a column, and the 2nd parameter represents the Width value

6. Set the line wrap:
Setborder.setwraptext (TRUE);//Set line wrapping

7. Merge Cells:
Region Region1 = new Region (0, (short) 0, 0, (short) 6); Parameter 1: Starting line number parameter 2: Starting column number parameter 3: Terminating line number parameter 4: terminating column number

8. Drop-down list (sequence):
String[] textlist = (string[]) moudlmap.get (Mkey);
Dvconstraint constraint = Dvconstraint.createexplicitlistconstraint (textlist);
Set the cell on which the data validation is loaded, four parameters are: Start row, terminating row, starting column, terminating column
Cellrangeaddresslist regions = new Cellrangeaddresslist (1,1000, Integer.parseint (mkey.tostring ()), Integer.parseInt ( Mkey.tostring ()));
Data validation Objects
Hssfdatavalidation data_validation_list = new Hssfdatavalidation (regions, constraint);
Sheet.addvalidationdata (data_validation_list);


public class Exportexcel
{
/**
* @ function: Java export Excel
*/
private static list<studenttest> Getstudenttest () throws Exception
{
List List = new ArrayList ();
SimpleDateFormat df = new SimpleDateFormat ("Yyyy-mm-dd");
Studenttest user1 = new Studenttest (1, "Zhang San", +, Df.parse ("1997-03-12"));
Studenttest user2 = new Studenttest (2, "John Doe", +, Df.parse ("1996-08-12"));
Studenttest User3 = new Studenttest (3, "Harry", +, Df.parse ("1985-11-12"));
List.add (user1);
List.add (User2);
List.add (USER3);
return list;
}
public static void Main (string[] args) throws Exception
{
The first step is to create a webbook that corresponds to an Excel file
Hssfworkbook wb = new Hssfworkbook ();
In the second step, add a sheet in WebBook that corresponds to the sheet in the Excel file
Hssfsheet sheet = wb.createsheet ("Student form One");
In the third step, add the No. 0 row of the table header to the sheet, noting that the old version of POI has a limit on the number of rows in Excel short
Hssfrow row = Sheet.createrow ((int) 0);
Fourth step, create a cell, and set the value header to center the header
Hssfcellstyle style = Wb.createcellstyle ();
Style.setalignment (Hssfcellstyle.align_center); Create a center format
Hssfcell cell = Row.createcell (0);
Cell.setcellvalue ("School Number");
Cell.setcellstyle (style);
Cell = Row.createcell (1);
Cell.setcellvalue ("name");
Cell.setcellstyle (style);
Cell = Row.createcell (2);
Cell.setcellvalue ("Age");
Cell.setcellstyle (style);
Cell = Row.createcell (3);
Cell.setcellvalue ("Birthday");
Cell.setcellstyle (style);
The fifth step, write the Entity data in the actual application of this data from the database,
List List = Exportexcel.getstudenttest ();
for (int i = 0; i < list.size (); i++)
{
row = Sheet.createrow ((int) i + 1);
Studenttest stu = (studenttest) list.get (i);
Fourth step, create the cell and set the value
Row.createcell (0). Setcellvalue ((double) Stu.getid ());
Row.createcell (1). Setcellvalue (Stu.getname ());
Row.createcell (2). Setcellvalue ((double) stu.getage ());
Cell = Row.createcell (3);
Cell.setcellvalue (New SimpleDateFormat ("Yyyy-mm-dd"). Format (stu
. Getbirth ()));
}
Sixth, save the file to the specified location
try {
String path= "F:" +file.separator;
String name= "Student";
System.out.println (path+name+ ". xls");
File Exportfile=new file (path+name+ ". xls");
FileOutputStream fout = new FileOutputStream (exportfile);
Wb.write (Fout);
Fout.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}


--------------------------------------------------------------------------------
public class Importexcel {
/**
* @ function: Java import Excel
*/
public static String FilePath = "F:/student.xls";
public static void Main (string[] args) {
try {
Create a reference to an Excel workbook file
Hssfworkbook workbook = new Hssfworkbook (new FileInputStream (FilePath));
In an Excel document, the default index for worksheet one is 0
Hssfsheet sheet = workbook.getsheetat (0);
Hssfsheet sheet = workbook.getsheet ("Student form One");
Gets the number of rows in the Excel file
int rows = Sheet.getphysicalnumberofrows ();
Get all content
String value= "";
Traversing rows
for (int i = 0; i < rows; i++) {
Read line I
Hssfrow row = Sheet.getrow (i);
if (row! = null) {
Get all the columns in the Excel file
int cells = Row.getphysicalnumberofcells ();
Traversing columns
for (int j = 0; J < cells; J + +) {
Get the value of each column
Hssfcell cell = Row.getcell (j);
if (cell! = null) {
Switch (Cell.getcelltype ()) {
Case Hssfcell.cell_type_formula:
Break
Case Hssfcell.cell_type_numeric:
Value + = Cell.getnumericcellvalue () + ",";
Break
Case hssfcell.cell_type_string:
Value + = Cell.getstringcellvalue () + ",";
Break
Default
Value + = "0";
Break
}
}
}
}
value+= "\ n";
}
System.out.println (value);

} catch (Exception e) {
E.printstacktrace ();
}
}
}

Java Import Export Excel Common operations summary and simple example