Print series -- use jxl to generate EXCEL

Jxl installation:
The main is to put the jxl package under the classes of the WEB-INF (if you download the. jar file, put it under Lib). Don't forget to put the common folder in the jxl package under the WEB-INF.

Use of jxl:
The main function is to read and write Excel files.

Read:
This is an idea for reading. First, use an input stream (inputstream) to get the Excel file, then use the workbook in jxl to get the workbook, and use sheet to get the worksheet from the workbook, use cell to obtain a cell in the worksheet.
Inputstream-> workbook-> Sheet-> cell, the cells in the Excel file are obtained.

Code:

1. <% @ Page contenttype = "text/html; charset = gb2312" %>
2. <% @ Page import = "Java. Io. *, jxl. *, jxl. Write. *, jxl. Write. *, jxl. format. *" %>
3. String Path = "C: // excel.xls"; // Excel File URL
4. Inputstream is = new fileinputstream (PATH); // write to fileinputstream
5. Jxl. Workbook WB = Workbook. getworkbook (is); // get the working thin
6. Jxl. Sheet ST = WB. getsheet (0); // obtain the first worksheet in the workbook.
7. Cell cell = ST. getcell (); // obtain the first cell of the worksheet, that is, A1.
8. String content = cell. getcontents (); // getcontents () convert the characters in the cell into strings
9. WB. Close (); // close the workbook
10. Is. Close (); // close the input stream
    You can also use the getrows () and getcolumns () Methods of sheet to obtain the number of rows and columns for loop control and output all contents in a sheet.

We can use the getcell (x, y) method of sheet to obtain the coordinates of any cell, x, y, and excel.
For example, A1 corresponds to (0, 0), A2 corresponds to (0, 1), D3 corresponds to (3, 2). The coordinates in Excel start from a, 1, and jxl all start from 0.

Write:
The main content written to excel is the class in the jxl. Write package.
The idea is as follows:
Outputstream <-writableworkbook <-writablesheet <-label
The label indicates the cell location and content written to the sheet.
Code:

1. <% @ Page contenttype = "text/html; charset = gb2312" %>
2. <% @ Page import = "Java. Io. *, jxl. *, jxl. Write. *, jxl. Write. *, jxl. format. *" %>
3. Outputstream OS = new fileoutputstream ("C: // test.xls"); // output Excel File URL
4. Writableworkbook WWB = Workbook. createworkbook (OS); // create a writeable workbook
5. Writablesheet Ws = WWB. createsheet ("sheet1", 0); // create a writable Worksheet
6. Label labelcf = new label (0, 0, "hello"); // create the write location and content
7. WS. addcell (labelcf); // write the label to the sheet.
9. // The constructor label (int x, int y, string astring) xy of the label refers to the XY of the read, and astring is the written content.
11. Writablefont WF = new writablefont (writablefont. Times, 12, writablefont. Bold, false );
12. // Set the write font
13. Writablecellformat wcff = new writablecellformat (WF); // set cellformat
14. Label labelcf = new label (0, 0, "hello"); // create a write location, content, and format

Another constructor of label: Label (int c, int R, string cont, cellformat
(St) You can format the written content and set the font and other attributes.

Now you can write
WWB. Write ();
Close after writing
WWB. Close ();
Close the output stream.
OS. close;

OK, as long as the read and write are combined, it is more convenient to read data from N Excel files and write it into the new Excel table you want.

The following is a complete example of using jxl to generate an Excel file:
The Code is as follows:
Test. jsp

1. <% @ Taglib uri = "/WEB-INF/struts-html.tld" prefix = "html" %>
2. <HTML: HTML>
3. <HTML: button property = "button" onclick = "printall ()">
4. Download
5. </Html: button>
6. </Html: HTML>
7. <Script language = 'javascript '>
8. Function printall () {location. href = "<% = request. getcontextpath () %>/download. Do ";}
9. </SCRIPT>

Downloadaction. Java

 

2. Import org. Apache. Struts. Action .*;
3. Import javax. servlet. http .*;
4. Import java. Io. outputstream;
5. Import test. whw. Upload. excelbean;
7. Public class downloadaction extends action {
8. Public actionforward execute (actionmapping mapping,
9. Actionform form,
10. Httpservletrequest request,
11. Httpservletresponse response)
12. Throws exception {
13. Try {
14. String fname = "test"; // Excel file name
15. Outputstream OS = response. getoutputstream (); // gets the output stream
16. Response. Reset (); // clear the output stream
17. Response. setheader ("content-disposition", "attachment; filename =" + fname + ". xls ");
18. // Set the output file header
19. Response. setcontenttype ("application/MSExcel"); // defines the output type.
20. Excelbean EB = new excelbean ();
21. EB. expordexcel (OS); // call to generate the Excel file Bean
22. } Catch (exception e ){
23. System. Out. println (E );
24. }
26. Return Mapping. findforward ("display ");
27. }
28. }

Excelbean. Java

2. Package test. whw. Upload;
3. Import java. Io .*;
4. Import jxl .*;
5. Import jxl. Write .*;
6. Import jxl. format .*;
7. Import java. util .*;
8. Import java. AWT. color;
10. Public class excelbean {
11. Public excelbean (){}
12. Public String expordexcel (outputstream OS) throws exception {
13. Jxl. Write. writableworkbook wbook = Workbook. createworkbook (OS); // create an Excel file
14. String tmptitle = "test file"; // Title
15. Jxl. Write. writablesheet wsheet = wbook. createsheet ("first page", 0); // Sheet Name
16. // Set the Excel title
17. Jxl. Write. writablefont wfont = new jxl. Write. writablefont (
18. Writablefont. Arial, 16,
19. Writablefont. Bold, false, jxl. format. underlinestyle. no_underline,
20. Jxl. format. colour. Black );
21. Jxl. Write. writablecellformat wcffc = new jxl. Write. writablecellformat (
22. Wfont );
23. Jxl. Write. Label wlabel1;
24. Wlabel1 = new jxl. Write. Label (5, 0, tmptitle, wcffc );
25. Wsheet. addcell (wlabel1 );
26. Wfont = new jxl. Write. writablefont (
27. Writablefont. Arial, 14,
28. Writablefont. Bold, false, jxl. format. underlinestyle. no_underline,
29. Jxl. format. colour. Black );
30. Wcffc = new jxl. Write. writablecellformat (
31. Wfont );
32. Jxl. Write. Label wlabel;
33. Wlabel = new jxl. Write. Label (0, 0, "write content ");
34. Wsheet. addcell (wlabel );//
35. // Merge the cells mergecells (rows, columns, rows, and columns );
36. Wsheet. mergecells (3, 1, 5, 1 );
37. Wbook. Write (); // write a file
38. Wbook. Close ();
39. OS. Close ();
40. Return "success ";
41. }
42. }

Struts-config.xml

1. <? XML version = "1.0" encoding = "UTF-8"?>
2. <! Doctype Struts-config public "-// Apache Software Foundation // DTD struts configuration 1.1 //" http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd ">
3. <Struts-config>
4. <Action-mappings>
5. <Action type = "test. whw. Upload. downloadaction" Path = "/download">
6. <Forward name = "display" Path = "/display. jsp"/>
7. </Action>
8. </Action-mappings>
9. </Struts-config>
10. <! -- Display. jsp is a successful prompt page -->