For a long time want to study the use of Java Operations Excel method, today is OK, just a little understanding of a bit, the special summary. Use Java operation Excel, there is an open source Dongdong-jxl.jar, can download to http://sourceforge.net/projects/jexcelapi/files/.
I. Reading the contents of an Excel file
Copy Code code as follows:
/** *//** Read the contents of an Excel file
* @param file to be read
* @return
*/
public static String readexcel (file file) ... {
StringBuffer sb = new StringBuffer ();
Workbook WB = null;
Try ... {
Construct a workbook (Workbook) object
Wb=workbook.getworkbook (file);
catch (Biffexception e) ... {
E.printstacktrace ();
catch (IOException e) ... {
E.printstacktrace ();
}
if (wb==null)
return null;
After you get the workbook object, you can get the sheet (worksheet) object from it.
sheet[] Sheet = Wb.getsheets ();
if (sheet!=null&&sheet.length>0) ... {
Loop on each worksheet
for (int i=0;i<sheet.length;i++) ... {
Get the number of rows in the current worksheet
int rownum = Sheet[i].getrows ();
for (int j=0;j<rownum;j++) ... {
Get all the cells in the current row
Cell[] cells = Sheet[i].getrow (j);
if (cells!=null&&cells.length>0) ... {
Loop over each cell
for (int k=0;k<cells.length;k++) ... {
Reading the value of the current cell
String Cellvalue = cells[k].getcontents ();
Sb.append (cellvalue+ "");
}
}
Sb.append ("");
}
Sb.append ("");
}
}
Finally shut down the resource and free up memory
Wb.close ();
return sb.tostring ();
}
Two. Write Excel file
Here are a lot of formats, such as text content bold, plus some color, you can refer to the JXL API
Copy Code code as follows:
/** generates an Excel file
* @param filename of the Excel file to be generated
*/
public static void Writeexcel (String fileName) ... {
Writableworkbook WWB = null;
Try ... {
You first create a writable workbook (Workbook) object using the factory method of the Workbook class
WWB = Workbook.createworkbook (new File (FileName));
catch (IOException e) ... {
E.printstacktrace ();
}
if (wwb!=null) ... {
Create a writable worksheet
The workbook Createsheet method has two parameters, the first is the name of the worksheet, and the second is the position of the worksheet in the workbook
Writablesheet ws = Wwb.createsheet ("Sheet1", 0);
Start adding cells below
for (int i=0;i<10;i++) ... {
for (int j=0;j<5;j++) ... {
Note here that in Excel, the first parameter represents a column, and the second represents a row
Label LABELC = new label (j, I, "This is the first" + (i+1) + "row, first" + (j+1) + "column");
Try ... {
Add a generated cell to a worksheet
Ws.addcell (LABELC);
catch (Rowsexceededexception e) ... {
E.printstacktrace ();
catch (WriteException e) ... {
E.printstacktrace ();
}
}
}
Try ... {
Writing files from memory
Wwb.write ();
Turn off resources, freeing up memory
Wwb.close ();
catch (IOException e) ... {
E.printstacktrace ();
catch (WriteException e) ... {
E.printstacktrace ();
}
}
}
Note: If you want to write to an already existing Excel, you need to do the following:
Copy Code code as follows:
Writableworkbook book = null;
Try ... {
Excel gets the file
Workbook WB = Workbook.getworkbook (New File ("D:/test.xls"));
Opens a copy of a file and specifies that the data be written back to the original file
Book = Workbook.createworkbook (New File ("D:/test.xls"), WB);
Add a Worksheet
Writablesheet sheet = book.getsheet ("Sheet1");
Sheet.addcell (New Label (8,3, "3rd row, column 8th, add something"));
TODO the following sections omit
}catch (Exception e) ... {
E.printstacktrace ();
}
Three. Find out if a keyword is included in an Excel file
Copy Code code as follows:
/** search for a keyword in a file
* @param file to be searched
* @param keyWord to search for keywords
* @return
*/
public static Boolean SearchKeyword (File file,string keyWord) ... {
Boolean res = false;
Workbook WB = null;
Try ... {
Construct a workbook (Workbook) object
Wb=workbook.getworkbook (file);
catch (Biffexception e) ... {
return res;
catch (IOException e) ... {
return res;
}
if (wb==null)
return res;
After you get the workbook object, you can get the sheet (worksheet) object from it.
sheet[] Sheet = Wb.getsheets ();
Boolean breaksheet = false;
if (sheet!=null&&sheet.length>0) ... {
Loop on each worksheet
for (int i=0;i<sheet.length;i++) ... {
if (Breaksheet)
Break
Get the number of rows in the current worksheet
int rownum = Sheet[i].getrows ();
Boolean breakrow = false;
for (int j=0;j<rownum;j++) ... {
if (Breakrow)
Break
Get all the cells in the current row
Cell[] cells = Sheet[i].getrow (j);
if (cells!=null&&cells.length>0) ... {
Boolean Breakcell = false;
Loop over each cell
for (int k=0;k<cells.length;k++) ... {
if (Breakcell)
Break
Reading the value of the current cell
String Cellvalue = cells[k].getcontents ();
if (cellvalue==null)
Continue
if (Cellvalue.contains (KeyWord)) ... {
res = true;
Breakcell = true;
Breakrow = true;
Breaksheet = true;
}
}
}
}
}
}
Finally shut down the resource and free up memory
Wb.close ();
return res;
}
four. Insert a picture icon into Excel
The implementation of the Insert picture is easy, see the following code:
Copy Code code as follows:
/** *//** Insert a picture into Excel
* @param datasheet sheets to insert
* @param col picture from this column
* @param row picture starts from the row
* Number of columns @param width picture
* The number of rows @param the height picture
* @param imgfile The picture file to insert
*/
public static void Insertimg (Writablesheet datasheet, int col, int row, int width,
int height, File imgfile) ... {
Writableimage img = new Writableimage (col, row, width, height, imgfile);
Datasheet.addimage (IMG);
}
The comments of the above code are very clear, and probably no longer need to explain, we can use the following program to verify:
Copy Code code as follows:
Try ... {
Create a work thin
Writableworkbook workbook = Workbook.createworkbook (new File ("D:/test1.xls"));
Worksheets to insert
Writablesheet Imgsheet = Workbook.createsheet ("Images", 0);
Picture file to insert
File Imgfile = new file ("D:/1.png");
The picture is inserted into the first cell of the second row, which occupies six cells
Insertimg (Imgsheet,0,1,6,6,imgfile);
Workbook.write ();
Workbook.close ();
catch (IOException e) ... {
E.printstacktrace ();
catch (WriteException e) ... {
E.printstacktrace ();
}
However, JXL only supports images in PNG format, JPG and GIF formats are not supported
Five. Insert Header Footer
General header and footer are divided into three parts, left, middle and right three parts, using the following code to implement the Insert header and footer
Copy Code code as follows:
/** *//** Add a header footer to Excel
* @param datasheet sheet to be added to the header
* @param left
* @param Center
* @param right
*/
public static void SetHeader (Writablesheet datasheet,string left,string center,string right) ... {
HeaderFooter HF = new HeaderFooter ();
Hf.getleft (). append (left);
Hf.getcentre (). append (center);
Hf.getright (). append (right);
Add header
Datasheet.getsettings (). SetHeader (HF);
Add footer
Datasheet.getsettings (). Setfooter (HF);
}
We can test the method with the following code:
Copy Code code as follows:
Try ... {
Create a work thin
Writableworkbook workbook = Workbook.createworkbook (new File ("D:/test1.xls"));
Worksheets to insert
Writablesheet datasheet = Workbook.createsheet ("Add Header", 0);
Excelutils.setheader (datasheet, "ChB", "2007-03-06", "1th page, total 3 pages");
Workbook.write ();
Workbook.close ();
catch (IOException e) ... {
E.printstacktrace ();
catch (WriteException e) ... {
E.printstacktrace ();
}
