一、應用場景
列表數據Excel表格導出
需要用到的工具類:ExportExcelUtils
package com.purete.agent.config.sys.poi; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; /** * poi導出 * @author Administrator * */ public class ExportExcelUtils { private String title; // 導出表格的表名 private String[] rowName;// 導出表格的列名 private List<Object[]> dataList = new ArrayList<Object[]>(); // 對象數組的List集合 private HttpServletResponse response; // 傳入要導入的數據 public ExportExcelUtils(String title,String[] rowName,List<Object[]> dataList,HttpServletResponse response){ this.title=title; this.rowName=rowName; this.dataList=dataList; this.response = response; } // 導出數據 public void exportData(){ try { HSSFWorkbook workbook =new HSSFWorkbook(); // 創建一個excel對象 HSSFSheet sheet =workbook.createSheet(title); // 創建表格 // 產生表格標題行 HSSFRow rowm =sheet.createRow(0); // 行 HSSFCell cellTiltle =rowm.createCell(0); // 單元格 // sheet樣式定義 HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); // 頭樣式 HSSFCellStyle style = this.getStyle(workbook); // 單元格樣式 /** * 參數說明 * 從0開始 第一行 第一列 都是從角標0開始 * 行 列 行列 (0,0,0,5) 合併第一行 第一列 到第一行 第六列 * 起始行,起始列,結束行,結束列 * * new Region() 這個方法使過時的 */ // 合併第一行的所有列 sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (rowName.length-1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(title); int columnNum = rowName.length; // 表格列的長度 HSSFRow rowRowName = sheet.createRow(1); // 在第二行創建行 HSSFCellStyle cells =workbook.createCellStyle(); cells.setBottomBorderColor(HSSFColor.BLACK.index); rowRowName.setRowStyle(cells); // 循環 將列名放進去 for (int i = 0; i < columnNum; i++) { HSSFCell cellRowName = rowRowName.createCell((int)i); cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 單元格類型 HSSFRichTextString text = new HSSFRichTextString(rowName[i]); // 得到列的值 cellRowName.setCellValue(text); // 設置列的值 cellRowName.setCellStyle(columnTopStyle); // 樣式 } // 將查詢到的數據設置到對應的單元格中 for (int i = 0; i < dataList.size(); i++) { Object[] obj = dataList.get(i);//遍歷每個對象 HSSFRow row = sheet.createRow(i+2);//創建所需的行數 for (int j = 0; j < obj.length; j++) { HSSFCell cell = null; //設置單元格的數據類型 if(j==0){ // 第一列設置爲序號 cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(i+1); }else{ cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); if(!"".equals(obj[j]) && obj[j] != null){ cell.setCellValue(obj[j].toString()); //設置單元格的值 }else{ cell.setCellValue(" "); } } cell.setCellStyle(style); // 樣式 } } // 讓列寬隨着導出的列長自動適應 sheet.autoSizeColumn((short)0); //調整第一列寬度 sheet.autoSizeColumn((short)1); //調整第二列寬度 sheet.autoSizeColumn((short)2); //調整第三列寬度 sheet.autoSizeColumn((short)3); //調整第四列寬度 sheet.autoSizeColumn((short)4); //調整第五列寬度 sheet.autoSizeColumn((short)5); //調整第六列寬度 if(workbook !=null){ try { // excel 表文件名 String fileName = title + ".xls"; String fileName11 = URLEncoder.encode(fileName,"UTF-8"); String fileName12 = java.net.URLDecoder.decode(fileName11, "UTF-8"); String headStr = "attachment; filename=\"" + fileName12 + "\""; // System.out.println(headStr); response.setContentType("APPLICATION/OCTET-STREAM"); // response.setHeader("Content-Disposition", headStr); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName12.getBytes("gb2312"), "ISO8859-1" )); OutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); } } public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 設置字體 HSSFFont font = workbook.createFont(); //設置字體大小 font.setFontHeightInPoints((short)11); //字體加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //設置字體名字 font.setFontName("Courier New"); //設置樣式; HSSFCellStyle style = workbook.createCellStyle(); //設置底邊框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設置底邊框顏色; style.setBottomBorderColor(HSSFColor.BLACK.index); //設置左邊框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //設置左邊框顏色; style.setLeftBorderColor(HSSFColor.BLACK.index); //設置右邊框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //設置右邊框顏色; style.setRightBorderColor(HSSFColor.BLACK.index); //設置頂邊框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //設置頂邊框顏色; style.setTopBorderColor(HSSFColor.BLACK.index); //在樣式用應用設置的字體; style.setFont(font); //設置自動換行; style.setWrapText(false); //設置水平對齊的樣式爲居中對齊; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //設置垂直對齊的樣式爲居中對齊; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } public HSSFCellStyle getStyle(HSSFWorkbook workbook) { // 設置字體 HSSFFont font = workbook.createFont(); //設置字體大小 //font.setFontHeightInPoints((short)10); //字體加粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //設置字體名字 font.setFontName("Courier New"); //設置樣式; HSSFCellStyle style = workbook.createCellStyle(); //設置底邊框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //設置底邊框顏色; style.setBottomBorderColor(HSSFColor.BLACK.index); //設置左邊框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //設置左邊框顏色; style.setLeftBorderColor(HSSFColor.BLACK.index); //設置右邊框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //設置右邊框顏色; style.setRightBorderColor(HSSFColor.BLACK.index); //設置頂邊框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //設置頂邊框顏色; style.setTopBorderColor(HSSFColor.BLACK.index); //在樣式用應用設置的字體; style.setFont(font); //設置自動換行; style.setWrapText(false); //設置水平對齊的樣式爲居中對齊; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //設置垂直對齊的樣式爲居中對齊; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } public static String dataFormat (Date Date){ SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String format = simpleDateFormat.format(new Date()); return format; } }
後臺接口:
//設備使用信息導出 @RequestMapping(value="/exportinfo",method = { RequestMethod.GET,RequestMethod.POST}) @ResponseBody public void exportinfo(HttpServletRequest request,HttpServletResponse response,Long id,Map<String, Object> map) { map.put("id",id); List<DeviceUsageEntity> wordsList = deviceUsageService.queryListdetails(map); if (wordsList.size() != 0) { try { String title =wordsList.get(0).getDeviceInfo().getName()+"使用詳情"; String[] rowsName = new String[] {"序號","設備id", "會員暱稱", "使用時長", "開始使用時間", "結束使用時間"}; List<Object[]> dataList = new ArrayList<Object[]>(); Object[] objs = null; for (int i = 0; i < wordsList.size(); i++) { objs = new Object[rowsName.length]; objs[0] = " "+i+ " "; objs[1] = wordsList.get(i).getDeviceInfo().getUuid(); objs[2] = " "+wordsList.get(i).getDeviceUser().getNickname()+ " "; objs[3]= wordsList.get(i).getDuration()==null ? " " : " "+wordsList.get(i).getDuration()+"分鐘"+ " " ; objs[4] = ExportExcelUtils.dataFormat(wordsList.get(i).getStartTime()); objs[5] = ExportExcelUtils.dataFormat(wordsList.get(i).getEndTime()); dataList.add(objs); } ExportExcelUtils ex = new ExportExcelUtils(title, rowsName, dataList, response); ex.exportData(); } catch (Exception e) { e.printStackTrace(); } } }
效果圖: