您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)Java實現(xiàn)數(shù)據(jù)庫中查詢出數(shù)據(jù)轉(zhuǎn)存成excel表的方法,小編覺得挺實用的,因此分享給大家做個參考,希望大家閱讀完這篇文章后可以有所收獲。
注意日期格式如果是以String類型的方式存到數(shù)據(jù)庫的導(dǎo)出時要轉(zhuǎn)換一次,直接導(dǎo)出格式不對
因為導(dǎo)出excel表格用的是get方式傳參,所以如果需要對導(dǎo)出的數(shù)據(jù)用中文模糊查詢,此時 用get傳參會出現(xiàn)中文亂碼
解決辦法:
前端對需要傳的中文參數(shù)進(jìn)行一次編碼 URLEncoder.encode(傳參,“utf-8”);
后臺需要再次解碼:URLDecoder.decode(接收的參數(shù),“utf-8”);
@RequestMapping(value = "outPutExcel", method = RequestMethod.GET) @ResponseBody public void outPutExcel( HttpServletResponse response,String officeid, String sonid,String nameorphone,String beginTime, String endTime,String option) { String nString = ""; try { if (nameorphone != null && nameorphone != "") { //對前端傳的參數(shù)解碼 nString = URLDecoder.decode(nameorphone,"UTF-8"); } } catch (UnsupportedEncodingException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } response.reset(); //設(shè)置瀏覽器下載的格式,并以當(dāng)前時間的毫秒數(shù)命名 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); response.setContentType("application/msexcel"); List<PurchaseSum> list = purchaseService.selectPCSum(officeid, sonid, nString, beginTime, endTime, option); if (list == null && list.isEmpty()) { throw new NullPointerException("導(dǎo)出數(shù)據(jù)源為空"); } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet0"); HSSFRow rows; HSSFCell cells; //設(shè)置表格第一行的列名 // 獲得表格第一行 rows = sheet.createRow(0); // 根據(jù)需要給第一行每一列設(shè)置標(biāo)題 cells = rows.createCell(0); cells.setCellValue("客戶姓名"); cells = rows.createCell(1); cells.setCellValue("客戶電話"); cells = rows.createCell(2); cells.setCellValue("下單日期"); cells = rows.createCell(3); cells.setCellValue("訂單號"); cells = rows.createCell(4); cells.setCellValue("所屬分公司"); cells = rows.createCell(5); cells.setCellValue("簽單人"); cells = rows.createCell(6); cells.setCellValue("品名"); cells = rows.createCell(7); cells.setCellValue("型號"); cells = rows.createCell(8); cells.setCellValue("顏色"); cells = rows.createCell(9); cells.setCellValue("尺寸"); cells = rows.createCell(10); cells.setCellValue("材質(zhì)"); cells = rows.createCell(11); cells.setCellValue("已采購數(shù)量(件)"); cells = rows.createCell(12); cells.setCellValue("采購單價"); cells = rows.createCell(13); cells.setCellValue("采購總價"); cells = rows.createCell(14); cells.setCellValue("已出庫(件)"); //循環(huán)數(shù)據(jù)庫查出來的數(shù)據(jù)集,對應(yīng)每一列賦值 //此處list.size()本不應(yīng)該-1,因為同事在list集合里追加了另一條數(shù)據(jù),導(dǎo)致報錯故將其去除 for (int i = 0; i < list.size()-1; i++) { rows = sheet.createRow(i + 1); cells = rows.createCell(0); cells.setCellValue(list.get(i).getCustomerName()); cells = rows.createCell(1); cells.setCellValue(list.get(i).getPhone()); //對日期格式進(jìn)行轉(zhuǎn)換 cells = rows.createCell(2); String dateString = list.get(i).getPlaceOrderTime().toString(); Date date = null; try { date = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK).parse(dateString); } catch (ParseException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cells.setCellValue(sdf.format(date)); cells = rows.createCell(3); cells.setCellValue(list.get(i).getOrderNumber()); cells = rows.createCell(4); cells.setCellValue(list.get(i).getOfficeName()); cells = rows.createCell(5); cells.setCellValue(list.get(i).getUsername()); cells = rows.createCell(6); cells.setCellValue(list.get(i).getProductName()); cells = rows.createCell(7); cells.setCellValue(list.get(i).getType()); cells = rows.createCell(8); cells.setCellValue(list.get(i).getColor()); cells = rows.createCell(9); cells.setCellValue(list.get(i).getSize()); cells = rows.createCell(10); cells.setCellValue(list.get(i).getTexture()); cells = rows.createCell(11); cells.setCellValue(list.get(i).getPurchasedNumber()); cells = rows.createCell(12); cells.setCellValue(list.get(i).getPurchaseprice()); cells = rows.createCell(13); cells.setCellValue(list.get(i).getPurchasePriceSun()); cells = rows.createCell(14); cells.setCellValue(list.get(i).getOutlibraryNumber()); } try { OutputStream oStream = response.getOutputStream(); wb.write(oStream); oStream.flush(); } catch (FileNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
關(guān)于Java實現(xiàn)數(shù)據(jù)庫中查詢出數(shù)據(jù)轉(zhuǎn)存成excel表的方法就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,可以學(xué)到更多知識。如果覺得文章不錯,可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。