Java Create Excel sample (JXL usage) _java

Using this API non-Windows operating systems can also work with Excel data tables through a pure Java application. Because it is written in Java, we can use the JSP, servlet to invoke the API to implement the access to Excel datasheet in the Web application.

Copy Code code as follows:

Package com.yonyou.test;

Import Java.io.File;
Import java.io.IOException;

Import JXL. workbook;
Import jxl.format.Alignment;
Import Jxl.format.Border;
Import Jxl.format.BorderLineStyle;
Import Jxl.format.Colour;
Import Jxl.format.UnderlineStyle;
Import Jxl.write.Label;
Import Jxl.write.NumberFormats;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableFont;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
Import jxl.write.WriteException;
Import jxl.write.biff.RowsExceededException;

public class Jxltable {

Private final static jxltable jxltable = new jxltable ();

public static jxltable getinstance () {
return jxltable;
}

Public jxltable () {
}

public boolean createtable (string header, string[] body, string filePath) {
Boolean createflag = true;
Writableworkbook Book;
try {
To generate an Excel file from a path
Book = Workbook.createworkbook (New File (FilePath));
Create a sheet named "table"
Writablesheet sheet = book.createsheet ("table", 0);
Set No column width
Sheet.setcolumnview (1, 5);
Remove grid lines from the entire sheet
Sheet.getsettings (). Setshowgridlines (false);
Label Templabel = null;
Table header Output
string[] Headerarr = Header.split (",");
int headerlen = Headerarr.length;
Loop Write Header Content
for (int i = 0; i < Headerlen; i++) {
Templabel = new Label (1 + i, 1, headerarr[i],
Getheadercellstyle ());
Sheet.addcell (Templabel);
}
Table Body Output
int bodylen = Body.length;
Looping through the contents of the table body
for (int j = 0; J < Bodylen; J + +) {
string[] Bodytemparr = Body[j].split (",");
for (int k = 0; k < bodytemparr.length; k++) {
Writablecellformat Tempcellformat = null;
Tempcellformat = Getbodycellstyle ();
if (Tempcellformat!= null) {
if (k = = 0 | | | k = = (bodytemparr.length-1)) {
Tempcellformat.setalignment (Alignment.centre);
}
}
Templabel = new Label (1 + k, 2 + J, Bodytemparr[k],
Tempcellformat);
Sheet.addcell (Templabel);
}
}
Book.write ();
Book.close ();
catch (IOException e) {
Createflag = false;
SYSTEM.OUT.PRINTLN ("Excel creation failed!") ");
E.printstacktrace ();
catch (Rowsexceededexception e) {
Createflag = false;
SYSTEM.OUT.PRINTLN ("Excel Unit settings creation failed!") ");
E.printstacktrace ();
catch (WriteException e) {
Createflag = false;
SYSTEM.OUT.PRINTLN ("Excel write Failed!") ");
E.printstacktrace ();
}

return createflag;
}

Public Writablecellformat Getheadercellstyle () {
Writablefont font = new Writablefont (Writablefont.createfont ("XXFarEastFont-Arial"), 10,
Writablefont.bold, False, Underlinestyle.no_underline);
Writablecellformat Headerformat = new Writablecellformat (
Numberformats.text);
try {
Add font settings
Headerformat.setfont (font);
Set cell background color: yellow Header
Headerformat.setbackground (Colour.yellow);
Set table Header table border style
The entire table line is thick, black
Headerformat.setborder (Border.all, Borderlinestyle.thick,
Colour.black);
Table header content horizontally centered
Headerformat.setalignment (Alignment.centre);
catch (WriteException e) {
System.out.println (table header cell style setting failed!) ");
}
return headerformat;
}

Public Writablecellformat Getbodycellstyle () {
Writablefont font = new Writablefont (Writablefont.createfont ("XXFarEastFont-Arial"), 10,
Writablefont.no_bold, False, Underlinestyle.no_underline);
Writablecellformat BodyFormat = new Writablecellformat (font);
try {
Set cell background color: Table body is white
Bodyformat.setbackground (Colour.white);
Set table Header table border style
The entire table line is thin, black
BodyFormat
. SetBorder (Border.all, Borderlinestyle.thin, Colour.black);
catch (WriteException e) {
SYSTEM.OUT.PRINTLN (the table body cell style setting failed!) ");
}
return BodyFormat;
}

public static void Main (string[] args) {
String Header = "NO, name, sex, age";
string[] BODY = new STRING[4];
Body[0] = "1, Ouyang Fung, Male, 68";
BODY[1] = "2, yellow Pharmacist, male, 67";
BODY[2] = "3, Hong Seven male, male, 70";
BODY[3] = "4, Guo Jing, male, 32";
String FilePath = "E:/test.xls";
Jxltable TESTJXL = Jxltable.getinstance ();
Boolean flag = testjxl.createtable (header, body, filePath);
if (flag) {
SYSTEM.OUT.PRINTLN ("Table creation is successful!!") ");
}
}
}

Step-by-Step Explanation:

Reading Excel files

To read an Excel file, you should first create a wordbook:

Copy Code code as follows:

Workbook wb=workbook.getworkbook (file file);
Workbook Wb=workbook.getworkbook (InputStream is);

Then you can get the worksheet for it:

Copy Code code as follows:

Sheet[] Sheets=wb.getsheets (); Get all Worksheets
Sheet sheet=wb.getsheet (0); Represents getting the first worksheet.

You can finally get the values for some of the cells on a worksheet:

Copy Code code as follows:

Cell Cell=sheet.getcell (0,0); Get the first row of data in the first column. The first parameter is a column
String value=cell.getcontents (); Gets the value of the cell as a string
String Type=cell.gettype (); Gets the data type of the cell.

To close the workbook workflow :
When you have finished processing Excel spreadsheet data, be sure to use the close () method to turn off previously created objects to free up memory space in the process of reading the data table, which is especially important when reading large amounts of data. Refer to the following code fragment:

Copy Code code as follows:

Wb.close ();

Note: You will not be able to close until you have completed an operation on this Excel, and the call to sheet S=wb.getsheet (0) is null after it is closed.

the common methods are :

Methods provided by the workbook class
1. int Getnumberofsheets ()
Get the number of worksheets (Sheet) in the Workbook (workbook), example:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
int sheets = Rwb.getnumberofsheets ();

2. sheet[] Getsheets ()
Returns an array of sheet (Sheet) objects in the workbook (workbook), example:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
sheet[] sheets = rwb.getsheets ();

Methods provided by the sheet interface

1. String GetName ()
Get the name of the sheet, example:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
JXL. Sheet rs = rwb.getsheet (0);
String sheetname = Rs.getname ();

2. int GetColumns ()
Gets the total number of columns contained in the sheet table, example:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
JXL. Sheet rs = rwb.getsheet (0);
int rscolumns = Rs.getcolumns ();

3. cell[] GetColumn (int column)
Gets all the cells in a column and returns an array of cell objects, examples:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
JXL. Sheet rs = rwb.getsheet (0);
cell[] cell = rs.getcolumn (0);

4. int GetRows ()
Gets the total number of rows that are included in the sheet table, example:

Copy Code code as follows:

JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
JXL. Sheet rs = rwb.getsheet (0);
int rsrows = Rs.getrows ();

5. cell[] GetRow (int row)