Java uses JXL to export/Generate EXCEL1

/**     * Export and export heating market revenue, cost, Profit schedule     * @author jia-g-y     *    /Public String exporexcel (String str) {

String Str=servletactioncontext.getservletcontext (). Getrealpath ("\\statics\\attachment\\excel\\");
String path = str+ "\\user.xls";

try {list<wholesaleagreementmaterialexportexceltemplate> List = agreementdao.selectallwholedetails ();// Open the file Writableworkbook book = workbook.createworkbook (new file);//Generate a worksheet named "first page", and parameter 0 indicates that            is the first page writablesheet sheet = book.createsheet ("First page", 0);            sheetsettings ss = Sheet.getsettings ();  Ss.sethorizontalfreeze (2);  Set column Freeze Ss.setverticalfreeze (2);            Set line freezes first 2 lines Writablefont font1 =new writablefont (Writablefont.createfont ("Microsoft Jas"), Writablefont.bold);            Writablefont font2 =new Writablefont (Writablefont.createfont ("Microsoft Jas Black"), 9, writablefont.no_bold);            Writablecellformat WCF = new Writablecellformat (FONT1);            Writablecellformat wcf2 = new Writablecellformat (FONT2); Writablecellformat WCF3 = new Writablecellformat (font2);//set style, Font//Wcf2.setbackground (Colour.lig            Ht_orange); Wcf.setalignment (Alignment.cenTRE);  Parallel centering Wcf.setverticalalignment (verticalalignment.centre);  Vertical Center wcf3.setalignment (alignment.centre);  Parallel centering Wcf3.setverticalalignment (verticalalignment.centre);            Vertical Center Wcf3.setbackground (colour.light_orange);  Wcf2.setalignment (Alignment.centre);  Parallel centering Wcf2.setverticalalignment (verticalalignment.centre); Vertical centering Sheet.mergecells (1, 0, 13, 0);  Merge cells//the named cell position in the construction of the Label object is the first column in the first row (0,0)//And the cell contents are test Label Titlelabel = new label (1            , 0, "heating market revenue, cost, Profit Schedule", WCF);//Add a defined cell to the worksheet Sheet.addcell (Titlelabel); Sheet.setrowview (1, 500);            Set the height of the first row 20121111 int[] headerarrhight = {13,10,30,20,20,25,7,10,15,20,13,15,15,30}; String headerarr[] = {"Year", "month", "Dealer", "Contract number", "Product", "Specification", "Quantity", "Unit Price (Yuan)", "collection Amount (Yuan)", "collection not including tax price (Yuan)", "Cost (yuan)", "gross profit (yuan)", "Gross margin", "            Remarks "};              for (int i = 0; i < headerarr.length; i++) {  Sheet.addcell (New Label (I, 1, HEADERARR[I],WCF));            Sheet.setcolumnview (i, headerarrhight[i]);            } DecimalFormat df = new DecimalFormat ("#.00");            int conut = 2; for (int i = 0; i < list.size (); i++) {//Loops a list inside the data into Excel Sheet.addcell (new Label (0, Conut, Lis                T.get (i). Getfromdate (). substring (0, 4) + "year", Wcf2));                Sheet.addcell (New Label (1, Conut, List.get (i). Getfromdate (). SUBSTRING (4, 6) + "month", WCF2));                Sheet.addcell (New Label (2, Conut, List.get (i). Getcustomername (), wcf2));                Sheet.addcell (New Label (3, Conut, List.get (i). Getagreementcode (), wcf2));                Sheet.addcell (New Label (4, Conut, List.get (i). Getbrandname (), wcf2));                Sheet.addcell (New Label (5, Conut, List.get (i). GetType (), wcf2)); Sheet.addcell (New Label (6, Conut, List.get (i). getquantity (). substring (0, List.get (i). Getquantity (). IndexOf (".")), Wcf2));                Sheet.addcell (New Label (7, Conut, List.get (i). Getunivalent (), wcf2)); Sheet.addcell (New Label (8, Conut, List.get (i) getreceiptamount () + "", wcf2));//Collection Amount Sheet.addcell (New Lab El (9, Conut, Df.format (List.get (i). Getreceiptnotax ()), WCF2));//receipts are not included in the tax price Sheet.addcell (new Label, con UT, List.get (i). Getcost () + "", wcf2);//Cost Sheet.addcell (new Label, Conut, Df.format (List.get (i). Getrec Eiptnotax (). Add (List.get (i). Getcost (). Multiply (new BigDecimal ( -1))), wcf2);//gross margin BigDecimal BigDecimal =                List.get (i). Getreceiptnotax (). Add (List.get (i). Getcost (). Multiply (New BigDecimal (-1)));                Double bigDecimal2 = Bigdecimal.doublevalue ()/list.get (i). Getreceiptnotax (). Doublevalue ();                Sheet.addcell (New Label, Conut, (Df.format (bigdecimal2*100)) + "%", wcf2));                Sheet.addcell (New Label (Conut, List.get (i). Getremark (), wcf2)); Sheet.setrowview (Conut, 370);            Sets the height of the first row conut++;            } sheet.setrowview (List.size () + 2, 370);            Sheet.setrowview (List.size () + 3, 370);            Sheet.setrowview (List.size () + 4, 370);                         Sheet.setrowview (List.size () + 5, 370); Double sumz = 0.00; Total receipts Double SumT = 0.00; Collection does not include tax price double SUMC = 0.00; Cost Double SumM = 0.00; Gross for (int i = 0; i < list.size (); i++) {sumz + = List.get (i). Getreceiptamount (). Doubleva                Lue ();                SumT + = List.get (i). Getreceiptnotax (). Doublevalue ();                SUMC + = List.get (i). Getcost (). Doublevalue ();            SumM + = List.get (i). Getreceiptnotax (). Add (List.get (i). Getcost (). Multiply (new BigDecimal ( -1))). Doublevalue ();            } Sheet.addcell (New Label (0, List.size () + 3, "total:", WCF));            Sheet.addcell (New Label (8, List.size () + 3, Df.format (SUMZ), WCF)); Sheet.addcell (New Label (9, List.size () + 3, Df.format (SumT), WCF));            Sheet.addcell (New Label (List.size () + 3, Df.format (SUMC), WCF));            Sheet.addcell (New Label (one, list.size () + 3, Df.format (SumM), WCF));             Sheet.addcell (New Label (List.size () + 5, "Export time:" + new Date (). toLocaleString (), WCF3));            Write data and close file Book.write ();            Book.close ();        return path;    } catch (Exception e) {} return path; }

JXL is a Java Operation Excel tool, in the open source world, there are two sets of more influential APIs to use, one is a poi, one is jexcelapi. Where the function is relatively weak relative to the POI. However, JEXCELAPI support for Chinese is very good, the API is pure Java, and does not rely on the Windows system, even if running under Linux, it can also correctly handle Excel files. It is also important to note that this set of APIs has limited support for graphics and charts, and only the PNG format is recognized.

Use the following:

Build the Environment

Unpack the downloaded file, get Jxl.jar, put it in Classpath, and the installation is complete.

Basic operations

First, create a file

To generate an Excel file named "Test.xls", where the first worksheet is named "first page" with the following general effect:

Package

test;//Generate Excel Class import java.io.File; Import JXL. Workbook; Import Jxl.write.Label; Import Jxl.write.WritableSheet; Import Jxl.write.WritableWorkbook; public class Createexcel {public static void main (String args[]) {try {//Open file Writableworkbook book = Workbook.createw Orkbook (New File ("Test.xls")); Generate a worksheet named "first page", parameter 0 indicates this is the first page writablesheet sheet = book.createsheet ("First page", 0); The named cell position in the Label object's construction is the first column in the first row (0,0)//And the cell contents are test label label = new label (0, 0, "test"); Add a defined cell to the worksheet Sheet.addcell (label); Jxl.write.Number number = new Jxl.write.Number (1, 0, 555.12541); Sheet.addcell (number); Write data and close file Book.write (); Book.close (); } catch (Exception e) {System.out.println (e);}} }

After the compilation is executed, an Excel file is generated.

Second, read the file

Take the Excel file we just created as an example, do a simple read operation, the program code is as follows:

Package

test;//Read the Excel class import java.io.File; Import JXL. Cell; Import JXL. Sheet; Import JXL. Workbook; public class Readexcel {public static void main (String args[]) {try {Workbook book = Workbook.getworkbook (New File (" Test.xls ")); Get the first sheet object Sheet Sheet = book.getsheet (0); Get the first row of cells cell cell1 = Sheet.getcell (0, 0); String result = Cell1.getcontents (); SYSTEM.OUT.PRINTLN (result); Book.close (); } catch (Exception e) {System.out.println (e);}} }

Program execution Result: test

Iii. Modification of documents

With JEXCELAPI, you can modify an existing Excel file, and when you modify an Excel file, other actions are the same as creating Excel, except that you open the file differently. The following example adds a worksheet to the Excel file that we have generated:

Package

test; import java.io.File; Import JXL. Workbook; Import Jxl.write.Label; Import Jxl.write.WritableSheet; Import Jxl.write.WritableWorkbook; public class Updateexcel {public static void main (String args[]) {try {//Excel get file Workbook wb = Workbook.getworkbook ( New File ("Test.xls")); Open a copy of a file, and specify that the data is written back to the original file Writableworkbook book = workbook.createworkbook (New file ("Test.xls"), WB); Add a worksheet Writablesheet sheet = book.createsheet ("Second page", 1); Sheet.addcell (New Label (0, 0, "test data for the second page")); Book.write (); Book.close (); } catch (Exception e) {System.out.println (e);}} }

Iv. Data formatting

There are no complex data types involved in Excel, and the ability to handle strings, numbers, and dates is a good fit for general applications.

String formatting

The formatting of strings involves elements such as fonts, weights, and font sizes, which are primarily responsible for the Writablefont and Writablecellformat classes. Suppose we use the following statement when we generate a cell containing a string, and for the convenience of the narrative, we add a number to each line of the command:

Writablefont font1 =new writablefont (Writablefont.times, writablefont.bold); ①writablecellformat format1 = new Writablecellformat (FONT1); ②label label = new label (0, 0, "Data 4 test", FORMAT1) ③

Where ① specifies the string format: times, font size 16, bold display. Writablefont has a very rich

constructors, for different situations, have a detailed list of Jexcelapi's Java-doc, which are no longer listed here.

The ② code uses the Writablecellformat class, which is very important, which allows you to specify a variety of cells

property, there are more descriptions in subsequent cell formatting.

The ③ uses the constructor of the label class, specifying that the string is given that format.

In the Writablecellformat class, there is also an important way to specify the alignment of the data, such as for the above example, you can specify:

//The horizontal alignment is specified as center format1.setalignment (jxl.format.Alignment.CENTRE), or vertical alignment is specified as center format1.setverticalalignment ( Jxl.format.VerticalAlignment.CENTRE);

V. Cell operations

An important part of Excel is the operation of cells, such as row height, column width, cell merging, and so on, fortunately JEXCELAPI provides these support. These operations are relatively straightforward and only the relevant APIs are described below.

1. Merging cells

writablesheet.mergecells (int m, int n, int p, int q),//function is all merged from (M,n) to (P,Q) cells, for example: writablesheet sheet = book.cre Atesheet ("first page", 0); Merges all cells Sheet.mergecells (0, 0, 5, 0) in the first row of the first column to the first row of the sixth column;

A merge can be either horizontal or vertical. Merged cells cannot be merged again, otherwise an exception is triggered.

2, Row height and column width

Writablesheet.setrowview (int i, int height);//The function is to specify the height of line i+1, such as://The height of the first row is set to 0, Sheet.setrowview (200); Writablesheet.setcolumnview (int i, int width); The function is to specify the width of column i+1, such as://To set the width of the first column to Sheet.setcolumnview (0, 30);

JEXCELAPI also has other features, such as inserting images, which are not introduced here and can be explored by the reader. Where: If you are reading an Excel, you need to know how many rows and how many columns it has, such as the following:

Workbook book = Workbook.getworkbook (New File ("Test 1.xls")); Get the first sheet object Sheet Sheet = book.getsheet (0); Get the first column of the first row of cells int columnum = Sheet.getcolumns (); Gets the number of columns int rownum = Sheet.getrows (); Get the number of rows System.out.println (Columnum); System.out.println (rownum); for (int i = 0; i < rownum; i + +)//loop for read/write {for (int j = 0; J < Columnum; J + +) {Cell cell1 = Sheet.getcell (J, I); String result = Cell1.getcontents (); System.out.print (result);

Java uses JXL to export/Generate EXCEL1