The applications depends on a bunch of Jar file such as
- Apache commons fileupload
- Apache commons IO
- Apache POI
- Apache XML Beans
- Google JSON library Gson
- dom4j
Application Starting point - index.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | < html > < head > < title >Java Excel File Upload and Parsing</ title > < link rel = "stylesheet" type = "text/css" href = "extjs/resources/css/ext-all.css" > < style type = "text/css" > .upload-icon { background: url('extjs/icons/fam/image_add.png') no-repeat 0 0 !important; } </ style > < script type = "text/javascript" src = "extjs/ext-all-debug.js" ></ script > < script type = "text/javascript" src = "app.js" ></ script > </ head > < body > < div id = "fileUpload" > </ div > </ body > </ html > |
Application JavaScript file - apps.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Ext.Loader.setConfig({ enabled: true }); Ext.application({ name: 'ExcelApp' , appFolder: 'app' , controllers: [ 'FileUpload' ], launch: function () { Ext.widget( 'fileuploadform' ); } }); |
Application Excel file Upload Form View - MyForm.js
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 | Ext.define( 'ExcelApp.view.MyForm' , { extend: 'Ext.form.Panel' , alias : 'widget.fileuploadform' , width: 500, frame: true , title: 'Order File Upload Form' , bodyPadding: '10 10 0' , renderTo: 'fileUpload' , defaults: { anchor: '100%' , allowBlank: false , msgTarget: 'side' , labelWidth: 75 }, items: [{ xtype: 'textfield' , name: 'filename' , fieldLabel: 'File Name' },{ xtype: 'filefield' , id: 'myFile' , emptyText: 'Select a File to Upload' , fieldLabel: 'Select a File' , name: 'fileSelected' , buttonText: '' , buttonConfig: { iconCls: 'upload-icon' } }], buttons: [{ text: 'Upload' , action: 'uploadFile' }, { text: 'Reset Form' , handler: function () { this .up( 'form' ).getForm().reset(); } }] }); |
ExtJs application controller - FileUpload.js
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 | Ext.define( 'ExcelApp.controller.FileUpload' , { extend : 'Ext.app.Controller' , //define the views views : [ 'MyForm' ], refs : [{ ref : 'myForm' , selector : 'fileuploadform' }], //special method that is called when your application boots init : function () { //control function makes it easy to listen to events on //your view classes and take some action with a handler function this .control({ //when the viewport is rendered 'viewport > panel' : { render : this .onPanelRendered }, //when you click Upload file button 'fileuploadform button[action=uploadFile]' : { click : this .uploadFile } }); }, onPanelRendered : function () { //just a console log to show when the panel is rendered console.log( 'The panel was rendered' ); }, uploadFile : function (button) { //just a console log to show when the file Upload starts console.log( 'File Upload in progress' ); var form = button.up( 'form' ).getForm(); if (form.isValid()){ form.submit({ url: 'ExcelOrderFileUpload' , waitMsg: 'Uploading your file...' , scope: this , success: function (form, action){ // server responded with success = true response = Ext.decode(action.response.responseText); if (response.success){ this .processResponse(response); form.reset(); } }, failure: function (form, action){ if (action.failureType === CONNECT_FAILURE) { Ext.Msg.alert( 'Error' , 'Status:' +action.response.status+ ': ' + action.response.statusText); } if (action.failureType === SERVER_INVALID){ // server responded with success = false Ext.Msg.alert( 'Invalid' , action.result.errormsg); } } }); } }, processResponse : function (response){ responseMessage = response.message; Ext.MessageBox.show({ closable: false , width:450, title: '<b>Excel Order File Upload Successful!</b>' , msg: responseMessage, buttons: Ext.MessageBox.OK, fn: this .showResult, icon: Ext.MessageBox.INFO }); }, showResult : function (btn){ console.log( 'You clicked the ' + btn + ' button' ); } }); |
Java Servlet for File Upload and Excel file parsing - ExcelOrderFileUpload
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 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | package com.as400samplecode; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.PrintWriter; import java.util.Iterator; import java.util.List; import javax.servlet.ServletConfig; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileUploadException; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.commons.io.FilenameUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.google.gson.JsonObject; public class ExcelOrderFileUpload extends HttpServlet { private static final long serialVersionUID = 1L; private static final String TMP_DIR_PATH = "/MyTempFiles" ; private File tmpDir; private static final String DESTINATION_DIR_PATH = "/MySavedFiles" ; private File destinationDir; public ExcelOrderFileUpload() { super (); } public void init(ServletConfig config) throws ServletException { super .init(config); String realPath = getServletContext().getRealPath(TMP_DIR_PATH); tmpDir = new File(realPath); if (!tmpDir.isDirectory()) { throw new ServletException(TMP_DIR_PATH + " is not a directory" ); } realPath = getServletContext().getRealPath(DESTINATION_DIR_PATH); destinationDir = new File(realPath); if (!destinationDir.isDirectory()) { throw new ServletException(DESTINATION_DIR_PATH+ " is not a directory" ); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //PrintWriter to send the JSON response back PrintWriter out = response.getWriter(); //set content type and header attributes response.setContentType( "text/html" ); response.setHeader( "Cache-control" , "no-cache, no-store" ); response.setHeader( "Pragma" , "no-cache" ); response.setHeader( "Expires" , "-1" ); DiskFileItemFactory fileItemFactory = new DiskFileItemFactory (); //Set the size threshold, above which content will be stored on disk. fileItemFactory.setSizeThreshold( 1 * 1024 * 1024 ); //1 MB //Set the temporary directory to store the uploaded files of size above threshold. fileItemFactory.setRepository(tmpDir); ServletFileUpload uploadHandler = new ServletFileUpload(fileItemFactory); JsonObject myObj = new JsonObject(); String fileName = null ; String fullName = null ; File file = null ; try { //Parse the request List items = uploadHandler.parseRequest(request); Iterator iterator = items.iterator(); while (iterator.hasNext()) { FileItem item = (FileItem) iterator.next(); //Handle Form Fields if (item.isFormField()) { System.out.println( "Field Name = " + item.getFieldName() + ", Value = " + item.getString()); if (item.getFieldName().trim().equalsIgnoreCase( "filename" )){ fileName = item.getString().trim(); } } //Handle Uploaded files. else { System.out.println( "Field Name = " + item.getFieldName()+ ", File Name = " + item.getName()+ ", Content type = " +item.getContentType()+ ", File Size = " +item.getSize()); fullName = item.getName().trim(); //Write file to the ultimate location. file = new File(destinationDir,item.getName()); item.write(file); } } int count = 0 ; String extension = FilenameUtils.getExtension(fullName); if (extension.trim().equalsIgnoreCase( "xlsx" )){ count = processExcelFile(file); } else if (extension.trim().equalsIgnoreCase( "xls" )){ //process your binary excel file } if (extension.trim().equalsIgnoreCase( "csv" )){ //process your CSV file } myObj.addProperty( "success" , true ); myObj.addProperty( "message" , count + " item(s) were processed for file " + fileName); out.println(myObj.toString()); } catch (FileUploadException ex) { log( "Error encountered while parsing the request" ,ex); myObj.addProperty( "success" , false ); out.println(myObj.toString()); } catch (Exception ex) { log( "Error encountered while uploading file" ,ex); myObj.addProperty( "success" , false ); out.println(myObj.toString()); } out.close(); } private int processExcelFile(File file){ int count = 0 ; try { // Creating Input Stream FileInputStream myInput = new FileInputStream(file); // Create a workbook using the File System XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput); // Get the first sheet from workbook XSSFSheet mySheet = myWorkBook.getSheetAt( 0 ); /** We now need something to iterate through the cells.**/ Iterator<Row> rowIter = mySheet.rowIterator(); while (rowIter.hasNext()){ XSSFRow myRow = (XSSFRow) rowIter.next(); Iterator<Cell> cellIter = myRow.cellIterator(); while (cellIter.hasNext()){ XSSFCell myCell = (XSSFCell) cellIter.next(); //get cell index System.out.println( "Cell column index: " + myCell.getColumnIndex()); //get cell type System.out.println( "Cell Type: " + myCell.getCellType()); //get cell value switch (myCell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC : System.out.println( "Cell Value: " + myCell.getNumericCellValue()); break ; case XSSFCell.CELL_TYPE_STRING: System.out.println( "Cell Value: " + myCell.getStringCellValue()); break ; default : System.out.println( "Cell Value: " + myCell.getRawValue()); break ; } System.out.println( "---" ); if (myCell.getColumnIndex() == 0 && !myCell.getStringCellValue().trim().equals( "" ) && !myCell.getStringCellValue().trim().equals( "Item Number" )){ count++; } } } } catch (Exception e){ e.printStackTrace(); } return count; } } |
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.