Apache fileupload upload and jexcelapi Parsing

Source: Internet
Author: User
Recently I encountered a problem reading Excel data, so I spent some time searching for open-source tools.
To parse Excel files, the first thing to do is upload files. We used to upload files using smartupload In the project. However, this project seems to have stopped development. So here I use Apache commons fileupload, it can be found in the http://jakarta.apache.org/commons/fileupload. Currently, the latest version of this project is 1.1.1 and there are a large number of sample programs on the Internet. However, it was found that most of the methods were not recommended in the new version, so I read back the API and the official example.

Let's take a look at how to upload files. servlet is very simple. Here I limit the maximum upload volume to 1 MB and read it directly into the memory without caching temporary files on the disk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

ImportJava. Io. ioexception;ImportJava. Io. printwriter;ImportJava. Io. file;ImportJava.net. Uri;ImportJava.net. url;ImportJavax. servlet. servletexception;ImportJavax. servlet. http. httpservlet;ImportJavax. servlet. http. httpservletrequest;ImportJavax. servlet. http. httpservletresponse;ImportJava. util. List;ImportOrg. Apache. commons. fileupload. requestcontext;ImportOrg. Apache. commons. fileupload. servlet. servletrequestcontext;ImportOrg. Apache. commons. fileupload. servlet. servletfileupload;ImportOrg. Apache. commons. fileupload. disk. diskfileitemfactory;ImportOrg. Apache. commons. fileupload. fileitem;Public   ClassUploadservletExtendsHttpservlet {/*** constructor of the object .*/PublicUploadservlet (){Super();}/*** Destruction of the servlet .*/Public   VoidDestroy (){Super. Destroy ();}Public   VoidDoget (httpservletrequest request, httpservletresponse response)ThrowsServletexception, ioexception {}/*** Upload File ** @ Param Request * @ Param response * @ throws servletexception * @ throws ioexception */Public   VoidDopost (httpservletrequest request, httpservletresponse response)ThrowsServletexception, ioexception {response. setcontenttype ("text/html"); response. setcharacterencoding ("GBK"); printwriter out = response. getwriter (); out. println ("<HTML>"); out. println ("NewServletrequestcontext (request); // determines whether multipart content is contained.If(Servletfileupload. ismultipartcontent (requestcontext) {// create a disk-based file factory diskfileitemfactory factory =NewDiskfileitemfactory (); // sets the limit size of the directly stored file. If the limit is exceeded, a temporary file is written to save memory. The default value is 1024 bytes factory. setsizethreshold (1024*1024). // you can create an upload processor to process multiple uploaded files uploaded from a single HTML file. Servletfileupload upload =NewServletfileupload (factory); // maximum file size that can be uploaded upload. setsizemax (1024*1024); // process upload list items =Null;Try{Items = upload. parserequest (requestcontext); // cyclically differentiate the submitted form fields.For(IntI = 0; I <items. Size (); I ++) {fileitem Fi = (fileitem) items. Get (I); // if it is not the form content, extract the multipart.If(! Fi. isformfield () {// Upload File Path and file, extension. String sourcepath = Fi. getname (); string [] sourcepaths = sourcepath. split ("//"); // obtain the actual file name string filename = sourcepaths [sourcepaths. length-1]; // create a file to be written. File uploadedfile =NewFile (NewUri (URL. tostring () + filename); // write fi. Write (uploadedfile); Out. println (filename + "uploaded successfully. ");}}}Catch(Exception e) {out. println ("Upload Failed. Please check whether the size of the uploaded file exceeds 1 MB and ensure that the file is not occupied by other programs during upload. "); Out. println ("<br> cause:" + E. tostring (); E. printstacktrace () ;}} out. println ("</body>"); out. println ("Public   VoidInit ()ThrowsServletexception {}}

Enctype = "multipart/form-Data" must be added to the form"
<Form ID = "frm_reqnew" name = "frm_reqnew" enctype = "multipart/form-Data" Action = "$! Actionpath/deliver/deliverreqcreate.shtml "method =" Post ">
Bytes -------------------------------------------------------------------------------------------------------------------
The above program demonstrates how to upload files to the server. The main purpose of this article is not only to upload files, but also to perform Excel parsing and extract useful content. There are a lot of open source Excel parser, here I chose jexcelapi, can be found in the http://jexcelapi.sourceforge.net, is said to be developed by Koreans, the latest version is 2.6.2. Why didn't I select poi? The reason is that it has not been updated for a long time. I always like the latest things, such as Adobe's pdf reader. I downloaded 8.0, but the result is not as good as 6.0. :(

The following program modified the direct upload and made some adjustments. it canceled the file storage and directly parsed it by reading the input stream. It is assumed that the agreed Excel file contains five columns and N rows, with the first row title information.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

ImportJava. Io. ioexception;ImportJava. Io. printwriter;ImportJavax. servlet. servletexception;ImportJavax. servlet. http. httpservlet;ImportJavax. servlet. http. httpservletrequest;ImportJavax. servlet. http. httpservletresponse;ImportJava. util. List;ImportOrg. Apache. commons. fileupload. requestcontext;ImportOrg. Apache. commons. fileupload. servlet. servletrequestcontext;ImportOrg. Apache. commons. fileupload. servlet. servletfileupload;ImportOrg. Apache. commons. fileupload. disk. diskfileitemfactory;ImportOrg. Apache. commons. fileupload. fileitem;ImportJxl. workbook;ImportJxl. sheet;ImportJxl. cell;Public   ClassUploadservletExtendsHttpservlet {/*** constructor of the object .*/PublicUploadservlet (){Super();}/*** Destruction of the servlet .*/Public   VoidDestroy (){Super. Destroy ();}Public   VoidDoget (httpservletrequest request, httpservletresponse response)ThrowsServletexception, ioexception {}/*** Upload File ** @ Param Request * @ Param response * @ throws servletexception * @ throws ioexception */Public   VoidDopost (httpservletrequest request, httpservletresponse response)ThrowsServletexception, ioexception {response. setcontenttype ("text/html"); response. setcharacterencoding ("GBK"); printwriter out = response. getwriter (); out. println ("<HTML>"); out. println ("Null; // Obtain the content required by the fileupload component from the HTTP servlet requestcontext =NewServletrequestcontext (request); // determines whether multipart content is contained. If not, no processing is performed.If(Servletfileupload. ismultipartcontent (requestcontext) {// create a disk-based file factory diskfileitemfactory factory =NewDiskfileitemfactory (); // sets the limit size of the directly stored file. If the limit is exceeded, a temporary file is written to save memory. The default value is 1024 bytes factory. setsizethreshold (1024*1024). // you can create an upload processor to process multiple uploaded files uploaded from a single HTML file. Servletfileupload upload =NewServletfileupload (factory); // maximum file size that can be uploaded upload. setsizemax (1024*1024 );Try{// Process upload list items =Null; Items = upload. parserequest (requestcontext); // looping is required because the form field information is submitted.For(IntI = 0; I <items. Size (); I ++) {fileitem Fi = (fileitem) items. Get (I); // if it is not the form content, extract the multipart.If(! Fi. isformfield () {fileitem = Fi; // only upload a single file at a timeBreak;}} Out. println (parseexcel (fileitem ));}Catch(Exception e) {out. println ("Upload Failed! Check whether the uploaded file is in Excel format, complete, and 1 MB in size. "); Out. println ("<br> cause:" + E. tostring (); E. printstacktrace () ;}} out. println ("</body>"); out. println ("PrivateString parseexcel (fileitem FI)ThrowsException {// declare workbook =Null;Try{Workbook = Workbook. getworkbook (Fi. getinputstream (); sheet = Workbook. getsheet (0); // total number of rowsIntCount = sheet. getrows (); // retrieve the title string a1 = sheet. getcell (0, 0 ). getcontents (); string a2 = sheet. getcell (1, 0 ). getcontents (); string a3 = sheet. getcell (2, 0 ). getcontents (); string A4 = sheet. getcell (3, 0 ). getcontents (); string A5 = sheet. getcell (4, 0 ). getcontents (); // retrieves the contentFor(IntI = 1; I <count; I ++) {Cell [] cells = sheet. getrow (I); system. out. println (cells [0]. getcontents () + cells [1]. getcontents () + cells [2]. getcontents () + cells [3]. getcontents () + cells [4]. getcontents ());}Return"Upload successful. ";}Catch(Exception e ){ThrowE ;}Finally{If(Workbook! =Null) {Workbook. Close () ;}}/ *** initialization of the servlet. ** @ throws servletexception */Public   VoidInit ()ThrowsServletexception {}}

Jexcelapi is easy to use and can be converted to Java data types, such as int and double, based on the data types in Excel. For details, refer to its development guide. Of course, this example also provides the method of constructing an Excel file and then downloading it. If you encounter it later, you must continue to improve it.
Bytes ------------------------------------------------------------------------------------------------------------------
Generate EXCEL and download
The Code is as follows. In servlet, I/O exceptions are not captured and are directly thrown by the get or POST method. Of course, if it is more rigorous, it can be closed in finally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

// Set the output format and header information response. setcontenttype ("application/X-msdownload; charset = GBK"); string filename =NewString ("supplier 2.16.xls ". getbytes ("GBK"), "iso_8859_1"); response. setheader ("content-disposition", "attachment; filename =" + filename); // virtual data string materialname = ""; // material name string size = "200 × 300"; // specification string unit = ""; // unit string qty = "2 "; // Number of string bands = "do not know the brand"; // material brand string Company = "a factory in Chengdu"; // manufacturer name string memo = "reliable quality "; // remarks string price = "20.30"; // price string repdate = "2007-04-11"; // list of quote time <string []> List =NewArraylist <string []> ();For(IntI = 10; I> 0; I --) {string [] Output = {materialname, size, unit, qty + I, band, company, memo, price, repdate }; list. add (output);} // output stream bytearrayoutputstream baos =NewBytearrayoutputstream (); // construct the work zone writableworkbook workbook = workbook. createworkbook (baos); // construct sheet writablesheet sheet = workbook. createsheet ("quote list", 0); // construct the bold title Font writablefont blodfont =NewWritablefont (writablefont. tahoma, 10, writablefont. Bold,False); Writablecellformat blodformat =NewWritablecellformat (blodfont); label Label =Null;Try{// Label =NewLabel (0, 0, "material name", blodformat); sheet. addcell (Label); label =NewLabel (1, 0, "type", blodformat); sheet. addcell (Label); label =NewLabel (2, 0, "unit", blodformat); sheet. addcell (Label); label =NewLabel (3, 0, "quantity", blodformat); sheet. addcell (Label); label =NewLabel (4, 0, "material brand", blodformat); sheet. addcell (Label); label =NewLabel (5, 0, "manufacturer name", blodformat); sheet. addcell (Label); label =NewLabel (6, 0, "Remarks", blodformat); sheet. addcell (Label); label =NewLabel (7, 0, "price", blodformat); sheet. addcell (Label); label =NewLabel (8, 0, "", blodformat); sheet. addcell (Label); // output business dataFor(IntI = 1; I <= List. Size (); I ++) {string [] Output = list. Get (I-1); label =NewLabel (0, I, output [0]); sheet. addcell (Label); label =NewLabel (1, I, output [1]); sheet. addcell (Label); label =NewLabel (2, I, output [2]); sheet. addcell (Label); label =NewLabel (3, I, output [3]); sheet. addcell (Label); label =NewLabel (4, I, output [4]); sheet. addcell (Label); label =NewLabel (5, I, output [5]); sheet. addcell (Label); label =NewLabel (6, I, output [6]); sheet. addcell (Label); label =NewLabel (7, I, output [7]); sheet. addcell (Label); label =NewLabel (8, I, repdate); sheet. addcell (Label);} // write to the file workbook. write (); workbook. close (); // return the file stream outputstream OS = response to the browser. getoutputstream (); OS. write (baos. tobytearray (); OS. flush (); OS. close (); baos. close ();}Catch(Rowsexceededexception e) {e. printstacktrace ();}Catch(Writeexception e) {e. printstacktrace ();}}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.