JSP: how to upload excel files and insert excel files to the database. jsp: how to upload excel files

JSP: how to upload excel files and insert excel files to the database. jsp: how to upload excel files

This document describes how to upload an excel file from a JSP file and insert an excel file into a database. Share it with you for your reference. The details are as follows:

This excel file is bound to pojo. (disadvantage) The excle header must be a pojo field value.

1. html page:

<Form id = "myform" method = "post" enctype = "multipart/form-data"> <table> <tr> <td> </td> <input type = "file" name = "filepath" id = "filepath" class = "easyui-validatebox" required = true validType = "equalLength [4]" missingMessage = "file! "Value =" "/> </td> </tr> <tr align =" center "> <td colspan =" 2 "> <a id =" btn1 "class = "easyui-linkbutton" data-options = "iconCls: 'icon-OK '"style =" width: 60px "onclick =" subForm (); "> OK </a> <a id =" btn2 "class =" easyui-linkbutton "data-options =" iconCls: 'icon-cancel' "style =" width: 60px "onclick =" closeDig (); "> Cancel </a> </td> </tr> </table> </form> <script type =" text/javascript "> function subForm () {if ($ ('# myfor M '). form ('validate') {/** var filepath = $ ("# filepath "). val (); alert (filepath); $. ajax ({url: 'excleimport', typs: "post", data: {"filepath": filepath}, async: false, error: function (request) {$ ('# dg '). datagrid ('reload'); closeDig (); $. messager. alert ("Operation prompt", "Operation successful! "," Info ") ;}, success: function (data) {alert (" success ");}}); **/var filepath = $ ("# filepath "). val (); var re =/(\ +)/g; var filename = filepath. replace (re, "#"); // cut the path string. var one = filename. split ("#"); // get the last one in the array, that is, the file name var two = one [one. length-1]; // truncate the file name to obtain the suffix var three = two. split (". "); // obtain the last string intercepted, that is, the suffix var last = three [three. length-1]; // Add the suffix type to be determined var tp = "xls, xlsx"; // return the response Position of the suffix of the condition in the string var rs = tp. indexOf (last); if (rs! =-1) {$ ("# myform "). attr ("action", "excleImport"); $ ("# myform "). submit ();} else {$. messager. alert ("Operation prompt", "the file you selected is not a valid xls or xlsx file! "," Error "); return false ;}} else {$. messager. alert (" Operation prompt "," Select Upload File! "," Error ") ;}}</script>

2. java code:

@ RequestMapping ("/excleImport") public void excleImport (HttpServletRequest request) throws IOException, Exception {request. setCharacterEncoding ("UTF-8"); // set the encoding // Get disk file entries factory DiskFileItemFactory factory = new DiskFileItemFactory (); // obtain the path to which the file needs to be uploaded String path = request. getRealPath ("/upload/kaku"); File uploadDir = new File (path); if (! UploadDir. exists () {uploadDir. mkdirs ();} factory. setRepository (uploadDir); // sets the cache size. When the size of the uploaded file exceeds the cache, it is directly placed in the temporary storage room factory. setSizeThreshold (1024*1024); // high-level API File upload processing ServletFileUpload upload = new ServletFileUpload (factory ); // you can upload multiple file lists <FileItem> List = (list <FileItem>) upload. parseRequest (request); for (FileItem item: list) {// obtain the attribute name of the Form. String name = item. getFieldName (); // If the obtained form information is normal text Information if (item. isFormField () {// obtain the String entered by the user. The name is very good because the String value = item is submitted in the form. getString (); request. setAttribute (name, value) ;}// process the passed non-simple strings, such as binary images and movies. The else {/*** steps are as follows, obtain the name of the uploaded file * // obtain the path String value = item. getName (); // index to the last backslash int start = value. lastIndexOf ("\"); // intercept the name of the uploaded file. Add 1 to remove the backslash, String filename = value. substring (start + 1); // file suffix String prefix = fil Ename. substring (filename. lastIndexOf (". ") + 1); CardCenter cardCenter = new CardCenter (); request. setAttribute (name, filename); // The object is actually written to the disk. // The exception thrown by the object is caught by exception. // item. write (new File (path, filename); // manually written // OutputStream out = new FileOutputStream (new File (path, filename) provided by a third party )); inputStream in = item. getInputStream (); List <CardCenter> listFromExcel = (List <CardCenter>) ExelUtil. exportListFrom Excel (in, prefix, cardCenter); this. cardCenterService. excleImport (listFromExcel);/* int length = 0; byte [] buf = new byte [1024]; System. out. println ("Get the total size of the uploaded file:" + item. getSize (); // in. read (buf) the data read each time is stored in the buf array while (length = in. read (buf ))! =-1) {// extract data from the buf array and write it to the (output stream) disk out. write (buf, 0, length);} */in. close (); // out. close ();}}}

3. java code:

Public class ExelUtil {// The first column starts private static int start = 0; // The last column serial number private static int end = 0; public static String getSubString (String str) {return str. substring (0, str. lastIndexOf (". ");}/*** method description: export the Sheet from an Excel file to List * @ param file * @ param sheetNum * @ return * @ throws IOException * @ author * @ date 10:44:26 * @ comment */public static List <?> ExportListFromExcel (File file, String fileFormat, Object dtoobj) throws IOException {return exportListFromExcel (new FileInputStream (file), fileFormat, dtoobj);}/*** method description: export the Sheet from an Excel stream to List * @ param is * @ param extensionName * @ param sheetNum * @ return * @ throws IOException * @ author * @ date 10:44:03 * @ comment * /public static List <?> ExportListFromExcel (InputStream is, String fileFormat, Object dtoobj) throws IOException {Workbook workbook = null; if (fileFormat. equals (BizConstant. XLS) {workbook = new HSSFWorkbook (is);} else if (fileFormat. equals (BizConstant. XLSX) {workbook = new XSSFWorkbook (is);} return exportListFromExcel (workbook, dtoobj);}/*** method description: export from the specified Sheet to List * @ param workbook * @ param sheetNum * @ return * @ Uthor * @ date 10:43:46 * @ comment */private static List <Object> exportListFromExcel (Workbook workbook, Object dtoobj) {List <Object> list = new ArrayList <Object> (); String [] model = null; Sheet sheet = workbook. getSheetAt (0); // parse the formula result FormulaEvaluator evaluator = workbook. getCreationHelper (). createFormulaEvaluator (); int minRowIx = sheet. getFirstRowNum (); int maxRowIx = sheet. getLast RowNum (); for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx ++) {Object obj = null; if (rowIx = minRowIx) {start = sheet. getRow (rowIx ). getFirstCellNum (); end = sheet. getRow (rowIx ). getLastCellNum ();} Row row = sheet. getRow (rowIx); StringBuilder sb = new StringBuilder (); for (int I = start; I <end; I ++) {Cell cell = row. getCell (new Integer (I); CellValue cellValue = evaluator. evaluate (cell); if (CellValue = null) {sb. append (BizConstant. SEPARATOR + null); continue;} // After formula resolution, only the Boolean, Numeric, and String data types exist. In addition, there are Error data types. // other data types. According to the official documentation, you can ignore switch (cellValue. getCellType () {case Cell. CELL_TYPE_BOOLEAN: sb. append (BizConstant. SEPARATOR + cellValue. getBooleanValue (); break; case Cell. CELL_TYPE_NUMERIC: // The date type here is converted to the numeric type. if (DateUtil. isCellDateFormatted (cell) {sb. append (Biz Constant. SEPARATOR + cell. getDateCellValue ();} else {sb. append (BizConstant. SEPARATOR + cellValue. getNumberValue ();} break; case Cell. CELL_TYPE_STRING: sb. append (BizConstant. SEPARATOR + cellValue. getStringValue (); break; case Cell. CELL_TYPE_FORMULA: break; case Cell. CELL_TYPE_BLANK: break; case Cell. CELL_TYPE_ERROR: break; default: break;} if (rowIx = minRowIx) {String index = String. valueOf (Sb); String realmodel = index. substring (1, index. length (); model = realmodel. split (",");} else {String index = String. valueOf (sb); String realvalue = index. substring (1, index. length (); String [] value = realvalue. split (","); // field ing try {dtoobj = dtoobj. getClass (). newInstance ();} catch (InstantiationException e) {e. printStackTrace ();} catch (IllegalAccessException e) {e. printStackTrace ();} obj = ReflectUtil (dtoobj, model, value); list. add (obj) ;}} return list;}/*** method description: field ing assignment * @ param objOne * @ param listName * @ param listVales * @ return * @ author * @ date 10:53:43 * @ comment */@ SuppressWarnings ("deprecation ") private static Object reflectUtil (Object objOne, String [] listName, String [] listVales) {Field [] fields = objOne. getClass (). getDeclaredFields (); for (int I = 0; I <Fields. length; I ++) {fields [I]. setAccessible (true); for (int j = 0; j <listName. length; j ++) {if (listName [j]. equals (fields [I]. getName () {try {if (fields [I]. getType (). getName (). equals (java. lang. string. class. getName () {// String type if (listVales [j]! = Null) {fields [I]. set (objOne, listVales [j]);} else {fields [I]. set (objOne, "") ;}} else if (fields [I]. getType (). getName (). equals (java. lang. integer. class. getName () | fields [I]. getType (). getName (). equals ("int") {// Integer type if (listVales [j]! = Null) {fields [I]. set (objOne, (int) Double. parseDouble (listVales [j]);} else {fields [I]. set (objOne,-1) ;}} else if (fields [I]. getType (). getName (). equals ("Date") {// date type if (listVales [j]! = Null) {fields [I]. set (objOne, Date. parse (listVales [j]);} else if (fields [I]. getType (). getName (). equals ("Double") | fields [I]. getType (). getName (). equals ("float") {// double if (listVales [j]! = Null) {fields [I]. set (objOne, Double. parseDouble (listVales [j]);} else {fields [I]. set (objOne, 0.0) ;}} catch (IllegalArgumentException e) {e. printStackTrace ();} catch (IllegalAccessException e) {e. printStackTrace () ;}break ;}} return objOne ;}}

I hope this article will help you with JSP program design.