导出excel

╰+攻爆jí腚メ 2022-08-23 00:52 246阅读 0赞

将Jqgrid表格中的数据进行下载导出
    
    
    excel的sheet列的表头，分别对应三个表单
    
    
    		List<String> sheet1 = new LinkedList<String>();
    		sheet1.add("1");
    		sheet1.add("2");
    		sheet1.add("3");
    		sheet1.add("4");
    		sheet1.add("5");
    		sheet1.add("6");
    		sheet1.add("7");
    
    
    		List<String> sheet2 = new LinkedList<String>();
    		sheet2.add("1");
    		sheet2.add("2");
    		sheet2.add("3");
    		sheet2.add("4");
    		
    		List<String> sheet3 = new LinkedList<String>();
    		sheet3.add("1");
    		sheet3.add("2");
    		sheet3.add("3");
    		sheet3.add("4");
    		
    		//将3个的表的列的名字分别添加进来
    		List<List<String>> excelTitle = new LinkedList<List<String>>();
    		excelTitle.add(sheet1);
    		excelTitle.add(sheet2);
    		excelTitle.add(sheet3);
    
    
    		String sheet1Name = "第一个表单的名字";
    		String sheet2Name = "第二个表单的名字";
    		String sheet3Name = "第三个表单的名字";
    		
    		List<String> sheetName = new LinkedList<String>();
    		sheetName.add(sheet1Name);
    		sheetName.add(sheet2Name);
    		sheetName.add(sheet3Name);
    		
    		//用来存放所有的sheet1、sheet2和sheet3的数据的容器
    		List<List<String[]>> exportDataList = new LinkedList<List<String[]>>();
    		
    		//用来接受接受的数据
    		List<String[]> sheetDataList1 = new LinkedList<String[]>();
    		List<String[]> sheetDataList2 = new LinkedList<String[]>();
    		List<String[]> sheetDataList3 = new LinkedList<String[]>();
    					
    					
    		//获取三个表单的中的数据也是这样，分别和列名中1、2、3等等对应起来
    		String[] data = new String[10];
    		data[0] = "数据一";
    		data[1] = "数据二";
    		data[2] = "数据三";
    		data[3] = "数据四";
    		data[4] = "数据五";
    		data[5] = "数据六";
    		data[6] = "数据七";
    		sheetDataList1.add(data);
    		
    		data[0] = "数据一";
    		data[1] = "数据二";
    		data[2] = "数据三";
    		data[3] = "数据四";
    		sheetDataList2.add(data);
    		
    		data[0] = "数据一";
    		data[1] = "数据二";
    		data[2] = "数据三";
    		data[3] = "数据四";
    		sheetDataList3.add(data);
    		
    		exportDataList.add(sheetDataList1);
    		exportDataList.add(sheetDataList2);
    		exportDataList.add(sheetDataList3);
    		
    		
    		public static Map<String, String> exportExcel(String excelName, List<List<String>> excelTitle,
                List<List<String[]>> excelDatalist, List<String> sheetName, String rootPath,
                String storeFolder) {
            Map<String, String> exportedExcelPathMap = null;
            try {
                SimpleDateFormat df = new SimpleDateFormat("MMdd_HHmm");
                rootPath = URLDecoder.decode(rootPath + storeFolder + File.separator, "UTF-8");
                String fileName = null;
                if (StringUtils.isNotBlank(excelName)) {
                    fileName = URLDecoder.decode(excelName + ".xls","UTF-8");
                } else {
                    fileName = URLDecoder.decode(df.format(new Date()) + ".xls", "UTF-8");
                }
                File outputDir = new File(rootPath);
                if (!outputDir.exists()) {
                    outputDir.mkdirs();
                }
                WritableWorkbook wwb;
                File outputFile = new File(rootPath + fileName);
                if(outputFile.exists())
                	outputFile.delete();//刪除之前已经存在的相同文件名的文件
                wwb = Workbook.createWorkbook(outputFile);
                for (int sheetNumber = 0; sheetNumber < sheetName.size(); ++sheetNumber) {
                    WritableSheet sheet = wwb.createSheet(sheetName.get(sheetNumber), sheetNumber);
                    Label label;
                    WritableCellFormat textFormat = new WritableCellFormat(NumberFormats.TEXT);
                    textFormat.setAlignment(Alignment.CENTRE);
                    CellView cv = new CellView();
                    cv.setFormat(textFormat);
                    cv.setAutosize(true);
                    for (int i = 0; i < excelTitle.get(sheetNumber).size(); i++) {
                        sheet.setColumnView(i, cv);
                        label = new Label(i, 0, excelTitle.get(sheetNumber).get(i), textFormat);
                        sheet.addCell(label);
    
    
                    }
                    if (excelDatalist.size() > 0) {
                        for (int i = 0; i < excelDatalist.get(sheetNumber).size(); i++) {
                            String[] row = excelDatalist.get(sheetNumber).get(i);
                            int rowLength = row.length;
                            for (int j = 0; j < rowLength; j++) {
                                if (row[j] == null)
                                    continue;
                                sheet.setColumnView(i, cv);
                                label = new Label(j, i + 1, row[j], textFormat);
                                sheet.addCell(label);
                            }
                        }
                    }
                }
                wwb.write();
                wwb.close();
                exportedExcelPathMap = new HashMap<String, String>();
                exportedExcelPathMap.put("filepath", rootPath);
                exportedExcelPathMap.put("filename", fileName);
                return exportedExcelPathMap;
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (RowsExceededException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            } finally {
    
    
            }
            return exportedExcelPathMap;
        }