table 导入导出Excel
1、导出excel
1 public void actionExportExcel_actionPerformed(ActionEvent e) 2 throws Exception { 3 // TODO Auto-generated method stub 4 //MsgBox.showInfo(""+this.tblMain.get); 5 String filePath = null; 6 7 KDFileChooser fileChooser = new KDFileChooser(); 8 fileChooser.setDialogTitle("公司下属成本中心设置导出"); 9 fileChooser.setFileFilter(new ExcelFile()); 10 fileChooser.setAcceptAllFileFilterUsed(false); 11 12 if (fileChooser.showSaveDialog(this) == 0) { 13 filePath = fileChooser.getSelectedFile().getPath(); 14 if (!filePath.toLowerCase().endsWith(".xls")) { 15 filePath = filePath + ".xls"; 16 } 17 } else { 18 SysUtil.abort(); 19 } 20 21 if (filePath == null) { 22 SysUtil.abort(); 23 } 24 25 KDSBook kdsBook = new KDSBook("001"); 26 KDSSheet kdsSheet = new KDSSheet(kdsBook, "001"); 27 //kdsSheet.getSheetStyleAttributes().setFont(new Font(EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemResource", "font"), 0, 9)); 28 kdsSheet.getSheetStyleAttributes().setHorizontalAlign(Styles.HorizontalAlignment.LEFT); 29 kdsSheet.getSheetStyleAttributes().setNumberFormat("@"); 30 kdsSheet.setCellValue(0, 0, "公司编码");//company.number 31 kdsSheet.setCellValue(0, 1, "公司名称");//company.name 32 kdsSheet.setCellValue(0, 2, "成本中心编码");//entrys.costCenterNumber.number 33 kdsSheet.setCellValue(0, 3, "成本中心名称");//entrys.costCenterName 34 35 IRow row = null; 36 Map<String ,Set<Integer>> m = new HashMap<String ,Set<Integer>>() ; 37 int i = 0; for (int n = this.tblMain.getRowCount(); i < n; i++) { 38 row = this.tblMain.getRow(i); 39 40 if ((row.getCell("company.number").getValue() != null) && (row.getCell("entrys.costCenterNumber.number").getValue() != null)) 41 { 42 kdsSheet.setCellValue(i + 1, 0, row.getCell("company.number").getValue().toString()); 43 kdsSheet.setCellValue(i + 1, 1, row.getCell("company.name").getValue().toString()); 44 kdsSheet.setCellValue(i + 1, 2, row.getCell("entrys.costCenterNumber.number").getValue().toString()); 45 kdsSheet.setCellValue(i + 1, 3, row.getCell("entrys.costCenterName").getValue().toString()); 46 if(i>0&&kdsSheet.getCell(i+1,0,false).getValue().toString().equals(kdsSheet.getCell(i,0,false).getValue().toString())){ 47 Set<Integer> rowIndexs = m.get(kdsSheet.getCell(i+1,0,false).getValue().toString()); 48 if(rowIndexs ==null){ 49 rowIndexs = new HashSet<Integer>(); 50 rowIndexs.add(i+1); 51 rowIndexs.add(i); 52 m.put(kdsSheet.getCell(i+1,0,false).getValue().toString(), rowIndexs); 53 } 54 rowIndexs.add(i+1); 55 /* if(kdsSheet.getCell(i, 0, false).isMerged()){ 56 logger.info(kdsSheet.getCell(i, 0, false).getMergeBlock()); 57 kdsSheet.getMerges().addMerge(i+1, 0, i-1, 0); 58 kdsSheet.getMerges().addMerge(i+1, 0, i, 0); 59 }else{ 60 kdsSheet.getMerges().addMerge(i+1, 0, i, 0); 61 } 62 logger.info(kdsSheet.getCell(i+1, 0, false).isMerged());*/ 63 } 64 } else { 65 MsgBox.showInfo(this, MessageFormat.format(EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemResource", "errorValue"), new Object[] { new Integer(i + 1) })); 66 return; 67 } 68 } 69 for(Set<Integer> set :m.values()){ 70 int start = 0; 71 for(Integer index:set){ 72 if(start ==0) 73 start = index; 74 if(index > start){ 75 kdsSheet.getMerges().addMerge(start, 0,index , 0); 76 kdsSheet.getMerges().addMerge(start, 1,index , 1); 77 } 78 } 79 } 80 kdsBook.addSheet(null, kdsSheet); 81 82 ExportManager export = new ExportManager(); 83 try { 84 export.exportToExcel(kdsBook, filePath); 85 } catch (RuntimeException e1) { 86 MsgBox.showWarning(this, EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemImportResource", "fileIsNotOpen")); 87 } 88 }
View Code
2.导入excel
public void actionImportExcel_actionPerformed(ActionEvent e) throws Exception { OrgUnitInfo orgUnit = SysContext.getSysContext().getCurrentOrgUnit(); UserInfo userInfo= getUserInfo(); String filePath = null; boolean isNull = true; boolean isTooLong = false; KDFileChooser fileChooser = new KDFileChooser(); fileChooser.setDialogTitle("公司下属成本中心设置导入"); fileChooser.setFileFilter(new ExcelFile()); fileChooser.setAcceptAllFileFilterUsed(false); if (fileChooser.showOpenDialog(this) == 0) { filePath = fileChooser.getSelectedFile().getPath(); } else { SysUtil.abort(); } if (filePath == null) { SysUtil.abort(); } KDSBook kdsBook = null; try { kdsBook = POIXlsReader.parse(filePath); } catch (Exception e1) { MsgBox.showError(this, "文件已损坏！"); SysUtil.abort(); } if (kdsBook == null) { MsgBox.showError(this, "文件不可解析！"); SysUtil.abort(); } Map<String,Set<String>> m = new HashMap<String,Set<String>>(); CostCenterSetingCollection collection = new CostCenterSetingCollection(); KDSSheet sheet = kdsBook.getSheet(new Integer(0)); int rowCount = sheet.getRowCount(); StringBuilder sb = new StringBuilder(); String companyNum = ""; String companyName=""; String costcenterNum = ""; String costCenterName = ""; ICompanyOrgUnit icpf = CompanyOrgUnitFactory.getRemoteInstance(); ICostCenterOrgUnit iccf = CostCenterOrgUnitFactory.getRemoteInstance(); ICostCenterSeting ics =CostCenterSetingFactory.getRemoteInstance(); try { for (int i = 1; i < rowCount; i++) { companyNum = sheet.getCell(i, 0, false).getValue() == null ? "" : sheet.getCell(i, 0, false).getValue().toString().trim(); companyName = sheet.getCell(i, 1, false).getValue() == null ? "" : sheet.getCell(i, 1, false).getValue().toString().trim(); costcenterNum = sheet.getCell(i, 2, false).getValue() == null ? "" : sheet.getCell(i, 2, false).getValue().toString().trim(); costCenterName = sheet.getCell(i, 3, false).getValue() == null ? null : sheet.getCell(i, 3, false).getValue().toString().trim(); /*if ((companyName.trim().equals("")) && (costcenterNum.trim().equals(""))) { MsgBox.showInfo(this, MessageFormat.format(EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemResource", "errorLine"), new Object[] { new Integer(i + 1) })); isNull = true; break; }*/ if ((companyNum.trim().equals("")) || (costcenterNum.trim().equals(""))) { //MsgBox.showInfo(this, MessageFormat.format(EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemResource", "errorLine"), new Object[] { new Integer(i + 1) })); sb.append("文件第"+(i+1)+"行数据不合法！\n"); isNull = true; break; } if ((!isTooLong) && ((costcenterNum.trim().length() > 80) || (companyNum.trim().length() > 80))) { isTooLong = true; sb.append("文件第"+(i+1)+"行数据不合法！\n"); isNull = true; } else { Set<String> entryNums = m.get(companyNum); if(entryNums == null){ entryNums = new HashSet<String>(); m.put(companyNum, entryNums); } entryNums.add(costcenterNum); //setingInfo.setNumber(num); //BgLocaleHelper.setLocalesProperty(bgItemInfo, "name", name, LocaleUtils.getDefaultLocale()); isNull = false; } } } catch (Exception ee) { MsgBox.showWarning(this, "解析出错！"); SysUtil.abort(); } for(String comNum:m.keySet()){ CostCenterSetingInfo setingInfo = new CostCenterSetingInfo(); setingInfo.setId(BOSUuid.create(setingInfo.getBOSType())); setingInfo.setCU(orgUnit.getCU()); setingInfo.setCreator(userInfo); setingInfo.setCreateTime(new Timestamp(new Date().getTime())); setingInfo.setLastUpdateUser(userInfo); setingInfo.setLastUpdateTime(new Timestamp(new Date().getTime())); EntityViewInfo viewInfo = new EntityViewInfo(); FilterInfo filter = new FilterInfo(); filter.getFilterItems().add(new FilterItemInfo("number", comNum, CompareType.EQUALS)); viewInfo.setFilter(filter); CompanyOrgUnitCollection cc = icpf.getCompanyOrgUnitCollection(viewInfo); if(cc ==null||cc.size()==0){ sb.append("不存在编码为"+comNum+"的财务组织，已自动忽略该条记录的导入！\n"); continue; } if(checkCc(cc)){ sb.append("编码为"+comNum+"的财务组织已设置，已自动忽略该条记录的导入！\n"); continue; } setingInfo.setCompany(cc.get(0)); CostCenterSetingEntryCollection entryCollection =setingInfo.getEntrys(); for(String num :m.get(comNum)){ filter = new FilterInfo(); filter.getFilterItems().add(new FilterItemInfo("number", num, CompareType.EQUALS)); viewInfo.setFilter(filter); CostCenterOrgUnitCollection ccc = iccf.getCostCenterOrgUnitCollection(viewInfo); if(ccc ==null||ccc.size()==0){ sb.append("不存在编码为"+num+"的成本中心，已自动忽略该条记录的导入！\n"); continue; } if(checkCsc(ccc)){ sb.append("编码为"+num+"的的成本中心已设置，已自动忽略该条记录的导入！\n"); continue ; } CostCenterSetingEntryInfo info = new CostCenterSetingEntryInfo(); info.setCostCenterNumber(ccc.get(0)); info.setCostCenterName(ccc.get(0).getName()); entryCollection.add(info); } if(entryCollection.size()==0){ sb.append("编码为"+comNum+"的财务组织不存在有效的成本中心，已自动忽略该条记录的导入！\n"); continue ; } collection.add(setingInfo); } if (isTooLong) { //MsgBox.showWarning(this, EASResource.getString("com.kingdee.eas.ma.budget.client.BgItemImportResource", "tooLongNumAndName")); } if (!isNull) { CoreBaseCollection cbc = new CoreBaseCollection(); for(int i=0;i<collection.size();i++) cbc.add(collection.get(i)); ics.addnewBatchData(cbc); actionRefresh_actionPerformed(e); } if(sb.length()>1){ MsgBox.showInfo(sb.toString()); } }
View Code
版权声明：本文为cyhj原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。