Various Java Excel export attempts, java excel Export

Various Java Excel export attempts, java excel Export

The latest project is busy and has no time to come and talk to the new blog. Today I will share with you a small problem encountered in this project: Exporting Excel; I believe that exporting Excel is a very common feature and there are many ways. Well, let's not talk about it much. Let's talk about the various pitfalls we have encountered. I use poi export in the background.

First, let's talk about the requirement. The expected result is: "When the export conditions are met, export is normal, and a prompt box is displayed asking the user to select. If the export conditions are not met, the user is prompted not to export, you can also disable the export button while exporting data. After the export is successful, you can release the export button.

(First of all, I will talk about the ideas of the whole process and the problems and solutions I have encountered. The dry goods will be posted below)

1. Because our entire framework is used to asynchronous requests, at first I used ajax asynchronous requests to export the Excel files. The results showed that the export failed, the background program ran out, and the logs were recorded ,, there is no excel, let alone the prompt box. I checked a lot on the Internet. It is said that ajax uses the ghost stream to communicate with the background, and the browser won't recognize that you want him to download it, so this cannot be done. (Some friends also say they have tried ajax synchronization. Another pitfall ....)

2. I am going to use form submit to submit and export. I tried it and found it okay. I'm very happy, but the problem is coming again. After form is submitted, the page is actually refreshed, how can I prompt the user if the export conditions are not met? Are you redirected to a new page? This is not suitable, because the user has worked hard to enter the conditions for half a day, and then waited for a half-day background verification (involving a large amount of data). The result is that the user ran to another page and told you that the data could not be exported, if it was me, I would crash. After waiting for a long time, my grandmother had a leg and I had to re-enter the query conditions for a long time. This was intolerable .. Okay, this method won't work. Then I tried to write a script from the background to the jsp page, but I also found that the script was not executed at all, so I wondered. Finally, A friend on the Internet said to use an iframe hidden domain. If the domain does not meet the conditions, the problem will be written back in a stream. The final problem is solved (the code will be uniformly pasted below ).

3. This is perfect, but the problem has not been completely solved. There is also a problem of disabling the button. This problem is easy to think about. When you click Export, the button will be disabled, after the export is complete, a flag is passed back and the button is opened. But the problem arises. How can this flag be worn back? At first, I took it for granted the same ending scheme as the error prompt. I found that I couldn't because I exported the Excel file and used the response stream. I couldn't write the mark back in the stream mode. In this way, I cannot open the export button, and the boss does not agree. I am working overtime to find a method. Finally, I found a solution to use cookies. The specific idea is: After you click Export, disable the button, and then write a timer training cookie in js. After writing the file stream, I will put a cookie in the browser, then js will be able to find the cookie. After finding the cookie, it will first kill the timer, then kill the cookie (a bit of a heap, haha), and then release the export button.

(Although the problem has been solved, the cookie and timer training are still a little unreliable. I hope you can have a better solution. Please kindly advise. Thank you !!!)

Front-end code:

<Button class = "btn-sm btn-success" type = "submit" id = "detailEp" onclick = "return exportCheck (true ); "forbid =" yes "> <I class =" icon-arrow-right bigger-110 "> </I> export </button>
<Script >$ (function () {var timer = "" ;}); // disable the export button when you click the export button $ ("# myForm "). submit (function () {$ ("button [type = submit]", this ). attr ("disabled", "disabled"); // check whether timer = setInterval (refrashPg, 1000) is successfully exported after the export is submitted );}) // after the export is successful, the function refrashPg () {if (getCk () = "1") {clearInterval (timer) of the Export button will be released ); $ ("# detailEp "). removeAttr ("disabled");} delCk ();} // js gets cookiefunction getCk () {debuggervar ck = document. cookie. split (";"); var ckname = ""; for (var I = 0; I <ck. length; I ++) {var arr = ck [I]. split ("="); if (arr [0] = "updtstatus") {ckname = arr [1]; break ;}} return ckname ;} // Delete cookiefunction delCk () {var exp = new Date (); var name = "updtstatus"; exp. setTime (exp. getTime ()-1000); var cval = getCk (); document. cookie = name + "=" + cval + "; expires =" + exp. toGMTString ();}}

</script>

The export code in the background is the common Java POI code:

1 public void buildExcelDocument (Map <String, Object> obj, String fileName, String type, 2 HttpServletRequest request, HttpServletResponse response) 3 throws Exception {4 HSSFWorkbook workbook = new HSSFWorkbook (); 5 if ("1 ". equals (type) {6 List <Map <String, Object> contentList = (List <Map <String, Object>) obj. get ("content"); 7 HSSFSheet sheet = workbook. createSheet ("sheet1"); // The Excel version is 2003-2007 (xls). If 2010 is required, use XSSFSheet 8 Map <String, string> titleList = (Map <String, String>) obj. get ("title"); 9 // The key of the header corresponds to the key10 List of map in listMap <String> mkey = new ArrayList <String> (); 11 // value header 12 List <String> mvalue = new ArrayList <String> (); 13 Iterator <Entry <String, String> it = titleList. entrySet (). iterator (); 14 while (it. hasNext () {15 @ SuppressWarnings ("rawtypes") 16 java. util. map. entry entry = (java. util. map. entry) it. next (); 17 mkey. add (String) entry. getKey (); 18 mvalue. add (String) entry. getValue (); 19} 20 sheet. setdefacolumcolumnwidth (short) 12); 21 HSSFCell cell = null; 22 for (int I = 0; I <mvalue. size (); I ++) {23 cell = getCell (sheet, 0, I); 24 setText (cell, mvalue. get (I); 25} 26 27 for (short I = 0; I <contentList. size (); I ++) {28 HSSFRow sheetRow = sheet. createRow (I + 1); 29 Map <String, Object> entity = contentList. get (I); 30 for (int j = 0; j <mkey. size (); j ++) {31 if (entity. get (mkey. get (j) instanceof String) {32 sheetRow. createCell (j ). setCellValue (String) entity. get (mkey. get (j); 33} else if (entity. get (mkey. get (j) instanceof Double) {34 sheetRow. createCell (j ). setCellValue (Double) entity. get (mkey. get (j); 35} else if (entity. get (mkey. get (j) instanceof Date) {36 String date = (entity. get (mkey. get (j ))). toString (); 37 sheetRow. createCell (j ). setCellValue (date. substring (0, 10); 38} else if (entity. get (mkey. get (j) instanceof BigDecimal) {39 sheetRow. createCell (j ). setCellValue (entity. get (mkey. get (j ))). toString (); 40} 41} 42} 43} 44} else if ("2 ". equals (type) {45 List <String []> contentList = (List <String []>) obj. get ("content"); 46 HSSFSheet sheet = workbook. createSheet ("sheet1"); 47 String [] titleList = (String []) obj. get ("title"); 48 HSSFCell cell = null; 49 for (int I = 0; I <titleList. length; I ++) {50 cell = getCell (sheet, 0, I); 51 setText (cell, titleList [I]); 52} 53 for (short I = 0; I <contentList. size (); I ++) {54 HSSFRow sheetRow = sheet. createRow (I + 1); 55 String [] detail = contentList. get (I); 56 for (int j = 0; j <detail. length; j ++) {57 sheetRow. createCell (j ). setCellValue (String) contentList. get (I) [j]); 58} 59} 60} 61 62 // set the client Excel name 63 String filename = fileName + ". xls "; 64 // process the Chinese file name 65 filename = Chineseutil. encodeFilename (filename, request); 66 response. setContentType ("application/Vnd. ms-excel; charset = UTF-8 "); 67 response. setHeader ("Content-Disposition", "attachment; filename =" + new String (filename. getBytes ("gb2312"), "iso8859-1"); 68 OutputStream ouputStream = response. getOutputStream (); 69 workbook. write (ouputStream); 70 71 // Save the exported data to cookie72 Cookie status = new Cookie ("updtstatus", "1"); 73 status. setMaxAge (3600); // set the cookie survival time to 1 hour 74 response. addCookie (status); 75 76 ouputStream. flush (); 77 ouputStream. close (); 78}

View Code