Java handles a little practice of excel in the accumulation

Java handles a little practice of excel in the accumulation

Tong Liang Date: 2008-11-06

1 Document Description

Purpose: The main purpose of this document is to share with you how to work with Excel in Java, part of the code from the Enterprise project management system V2.0 Source, part of the copy online.

Background: Colleagues working with the Windows operating system are certainly not unfamiliar with Excel (spreadsheets), but it is not easy to manipulate Excel files using the Java language. With the growing popularity of Web applications, the need to manipulate Excel files over the Web is becoming more and more intense.

2 Requirements

This section has nothing to do with technology, and colleagues who are not interested can skip directly to the next chapter.

The reason I want to separate the "requirements" is because by fulfilling this need, I have learned not only about technology, but also about the way of thinking and working. The customer's original requirement is that the data in the Web system is entered in tabular form, and then the data can be calculated and processed (the formula is configured in some of the tables). When received this demand, we without further ado directly with the JS drawing table, JS to achieve the calculation of the formula. As a result of the customer constantly in accordance with the features of Excel in the implementation of our, we finally overwhelmed, to know that the need for Unicom is actually the full function of Excel. Behind closed doors we all knew that a few years ago there have been similar weboffice middleware. The main reason we have such a mistake is the limitation of thought and the rigidity of the way of working, so it is a lesson for me to see the document colleague lesson. After we quoted the weboffice2003 this middleware, we only use this middleware to open excel in the JSP page, and then send the contents of Excel in his encapsulated submission to action, and then we in action, Bo, The DAO that handles this Excel, and how Excel is handled is what I'm going to say next.

3 Technical Implementation

To learn Java processing Excel, first of all we need to understand JXL this package, we quoted all the methods are from this package, my introduction is mainly around the package of classes and methods to expand.

3.1 Read Excel

3.1.1 Reading local Excel files

The first step in reading an Excel data table is to create a workbook (term: workbook).

InputStream is = new FileInputStream (SOURCEFILE[W1]);

Workbook WB = Workbook.getworkbook (IS);

The Workbook object is created so that we can access the Excel Sheet (term: worksheet).

Sheet rs = Rwb.getsheet (0[w2]);

Similar to this, you can also take the cell object, as well as the cell's corresponding property information. You can view the API documentation for JXL.

In addition, when you have finished processing Excel spreadsheet data, be sure to use the close () method to close the previously created object to free up memory space used in reading the data table, which is especially important when reading large amounts of data. Wb.close ();

3.1.2 Reading a database file

Similar to reading local Excel, the difference is in the acquisition of the input stream. General Excel stores the database in BLOB type, so we want to read the Blob field when querying, get the input stream.

The code is as follows:

InputStream instream = vfield[w3]. Getbinarystream ();

3.2 Writing Excel files

3.2.1 Creating an Excel File

3.2.1.1 How to create

1. Write to local file

Writableworkbook WWB = Workbook.createworkbook (new File (TARGETFILE[W4]));

2. Write directly to the output stream

Bytearrayoutputstream outs = new Bytearrayoutputstream ();

Writableworkbook WB = Workbook.createworkbook (outs);

3.2.1.2 Creating steps

1. Create a writable WORKBOOK[W5] (Createworkbook).

2. Create a writable SHEET[W6] (createsheet).

3, if you want to beautify the generated Excel can create Writablefont,writablecellformat to set the color, font, etc.

4. Add a Number object,. Add a Boolean object, add a DateTime object, add a Label object, and merge cells with the MergeCells method.

5, Write Exel worksheet wb.write ();

6. Close the Excel Workbook Object Wb.close ();

See note 4.1 For detailed code

3.2.2 Modifying an Excel file

How to get 3.2.2.1

1. Local Files

InputStream is = new FileInputStream (sourcefile[w7]);

Bytearrayoutputstream outs = new Bytearrayoutputstream ();

2. Database objects

InputStream instream = Vfield[w8]. Getbinarystream ();

Bytearrayoutputstream outs = new Bytearrayoutputstream ();

3.2.2.2 Modify Steps

1. Create a writable WORKBOOK[W9].

Wwbook = Workbook.createworkbook (OutStream, This.rwbook,new workbooksettings ());

The next action, like creating Excel, is to return an output stream. You can do what you want with this output stream.

See note 4.2 For detailed code

4 Notes

4.1 Example of creating an Excel file code

1. Add a Label object

Label LABELC = new Jxl.write.Label (0, 0, "This is a Label cell");

Ws.addcell (LABELC);

Add an object with a font formatting

Jxl.write.WritableFont wf = new Jxl.write.WritableFont (Writablefont.times, Writablefont.bold, true);

Jxl.write.WritableCellFormat WCFF = new Jxl.write.WritableCellFormat (WF);

Jxl.write.Label LABELCF = new Jxl.write.Label (1, 0, "This is a Label Cell", WCFF);

Ws.addcell (LABELCF);

Add an object with font color formatting

Jxl.write.WritableFont WFC = new Jxl.write.WritableFont (writablefont.arial, ten, Writablefont.no_bold, False,

Underlinestyle.no_underline, Jxl.format.Colour.RED);

Jxl.write.WritableCellFormat WCFFC = new Jxl.write.WritableCellFormat (WFC);

Jxl.write.Label LABELCFC = new Jxl.write.Label (1, 0, "This is a Label Cell", WCFFC);

Ws.addcell (LABELCF);

2. Add a Number Object

Jxl.write.Number Labeln = new Jxl.write.Number (0, 1, 3.1415926);

Ws.addcell (Labeln);

Add a Number object with formatting

Jxl.write.NumberFormat NF = new Jxl.write.NumberFormat ("#.##");

Jxl.write.WritableCellFormat WCFN = new Jxl.write.WritableCellFormat (NF);

Jxl.write.Number labelnf = new Jxl.write.Number (1, 1, 3.1415926, WCFN);

Ws.addcell (LABELNF);

3. Adding a Boolean Object

Jxl.write.Boolean Labelb = new Jxl.write.Boolean (0, 2, false);

Ws.addcell (LABELB);

4. Adding a DateTime object

Jxl.write.DateTime Labeldt = new Jxl.write.DateTime (0, 3, New Java.util.Date ());

Ws.addcell (Labeldt);

Add a DateFormat object with formatting

Jxl.write.DateFormat df = new Jxl.write.DateFormat ("dd MM yyyy hh:mm:ss");

Jxl.write.WritableCellFormat wcfdf = new Jxl.write.WritableCellFormat (DF);

Jxl.write.DateTime LABELDTF = new Jxl.write.DateTime (1, 3, New Java.util.Date (), WCFDF);

Ws.addcell (LABELDTF);

4.2 Example of modifying Excel file code

/**

* <p>description: Initialize template info </p>

* <p>remark: </p>

* @return

* @throws serviceexception

*/

Public Accessoryvo doinit (String initextid,string departmentcode) throws Serviceexception

{

Accessoryvo Accessoryvo = new Accessoryvo ();

/* reinitialization, Get plan template number by initialization number */

String Templateextid = Initdao.gettemplateidbyinitid (Initextid);

String Rpsno = Initdao.getrpsno (Templateextid);

Filloutvo filloutvo = new Filloutvo ();

Filloutvo.setrpsno (RPSNO);

Filloutvo.setdepartmentcode (Departmentcode);

List list= Initdao.gethistorydata (FILLOUTVO);

Initdao.updatefilloutstatus ("5", Initextid);

/* Get initialization template */

InputStream ins = initdao.getexceltemplate (Templateextid);

/* Get empty attachment output stream */

Bytearrayoutputstream outs = new Bytearrayoutputstream ();

Createexcel (ins,outs,list);

Accessoryvo.setaccessorybody (Outs.tobytearray ());

Initdao.updaterpstatus ("5", Rpsno);

Try

{

if (outs!=null)

{

Outs.close ();

}

if (ins!=null)

{

Ins.close ();

}

}

catch (IOException E)

{

TODO auto-generated Catch block

E.printstacktrace ();

}

return ACCESSORYVO;

}

/**

* <p>description: Generate excel</p> with initialized data

* <p>remark: </p>

* @return

* @throws serviceexception

*/

Public String createexcel (inputstream ins,outputstream outs,list List)

Throws Serviceexception

{

Changeworkbookbystream (ins,outs);

Try

{

Writableworkbook book = null;

book = This.wwbook;

Set format

Jxl.write.NumberFormat NF = new Jxl.write.NumberFormat ("#,###");

Jxl.write.WritableCellFormat WCFN = new Jxl.write.WritableCellFormat (NF);

Wcfn.setborder (Border.all,borderlinestyle.thin);

Wcfn.setverticalalignment (Verticalalignment.centre);

int length = List.size ();

Writablesheet rs = null;

Rdvo vo = null;

Loop get processing sheet data

for (int j = 0;j<length;j++) {

Vo = (RDVO) list.get (j);

sheetID = Vo.getsheetid ();

Cellrow = Vo.getcellrow ();

Cellcolumn = Vo.getcellcolumn ();

RPData = nullprocessutil.nvltostring (Vo.getrpdata (), "0");

rs = (Writablesheet) book.getsheet (Integer.parseint (sheetID));

Labeltemp = new Number (Integer.parseint (cellcolumn), Integer.parseint (Cellrow), double.parsedouble (RpData), WcfN);

((Writablesheet) RS). Addcell (Labeltemp);

}

Book.write ();

Close book, release resources

Book.close ();

Rwbook.close ();

}catch (Exception e) {

ErrorMsg = E.getmessage ();

ErrorMsg + = "Error in row:" +rowtemp+ "column:" +COLTEMP;

System.out.println ("Something went wrong!!") +ERRORMSG);

System.out.println (e);

E.printstacktrace ();

return this.errormsg;

}

return this.errormsg;

}

/**

* Change the way to get workbook for stream acquisition

* @author

* @date June 2, 2008

* @return void

*/

public void Changeworkbookbystream (InputStream instream,outputstream outstream) {

try {

This.rwbook = Workbook.getworkbook (instream);

System.out.println ("This.wbook init () ok!!");

} catch (IOException e) {

E.printstacktrace ();

} catch (Biffexception e) {

E.printstacktrace ();

}

try {

This.wwbook = Workbook.createworkbook (OutStream, This.rwbook,new workbooksettings ());

} catch (IOException e) {

E.printstacktrace ();

}

}

This article is from the "breath of rain after the taste of the Soil" blog, please be sure to keep this source http://tongl.blog.51cto.com/4470985/1836856

Java handles a little practice of excel in the accumulation