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