2.Java Excel操作读取合并单元格

/*** 初始化要和表列匹配的数组* @param filePath String* */void tableInit(){dataHeader = new String[]{"顺序号","层级","物料编码","物料名称","物料规格","表面处理","材质","用量","子件图号","来源","当前库存"};tableModel.addColumn(dataHeader[0]);tableModel.addColumn(dataHeader[1]);tableModel.addColumn(dataHeader[2]);tableModel.addColumn(dataHeader[3]);tableModel.addColumn(dataHeader[4]);tableModel.addColumn(dataHeader[5]);tableModel.addColumn(dataHeader[6]);tableModel.addColumn(dataHeader[7]);tableModel.addColumn(dataHeader[8]);tableModel.addColumn(dataHeader[9]);tableModel.addColumn(dataHeader[10]);table.setModel(tableModel);tableModel.setRowCount(5);}/*** 选择导入文件* @param filePath String* */void btnfile_actionPerformed(ActionEvent e){int filetype = 10;MitPDMFileChooserProxy proxy = new MitPDMFileChooserProxy(filetype);JFileChooser fc = proxy.getFileChooser();fc.setToolTipText("选择导入的文件");fc.setDialogTitle("查找文件");int returnVal = fc.showDialog(this, "打开");if(returnVal == 0){File file = fc.getSelectedFile();String strPath = file.getPath();if(strPath.indexOf(".xlsx") != -1 || strPath.indexOf(".xls") != -1){textField.setText(strPath);}else MitOptionPane.showMessageDialog(this, "不是Excel文件", "错误", 0);}}void btnImport_actionPerformed(ActionEvent e){importPath = textField.getText().trim();if((importPath == null || importPath.equals(""))){MitOptionPane.showMessageDialog(this, "请选择要导入的文件！", "数据导入", 1);return;}DlgWaitting waitDialog = new DlgWaitting("正在导入数据......", null, this);OpenWaitThread openWait = new OpenWaitThread(waitDialog);openWait.start();waitDialog.show();}void btnCancel_actionPerformed(ActionEvent e){this.dispose();}class OpenWaitThread extends Thread{DlgWaitting wait;public OpenWaitThread(DlgWaitting wait){this.wait = null;this.wait = wait;}public void run(){try{Thread.currentThread();Thread.sleep(1000L);}catch(Exception e) { }try{importData(wait);}catch(Exception e){e.printStackTrace();}}}/*** 导入数据* @param wait DlgWaitting* @throws IOException*/void importData(DlgWaitting wait) throws IOException{//要导入的数据列表ArrayList<String[]> alt = null;try {alt = getImportData(importPath, wait);}catch (Exception e) {e.printStackTrace();wait.dispose();MitOptionPane.showMessageDialog(this, "获取文件内数据出错，请检查文件", "错误", 0);return;}//如果数据为空则返回if(alt == null){wait.dispose();return;}//获取当前系统时间并进行格式化操作SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");Date date = new Date();String nowDate = sdf.format(date);//bom_054表插入设计BOM索引，或者是更新信息String selectSql054 = "select count(*) from Bom_054 where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(selectSql054).get(0))[0]) == 0){String sql54 = "insert into BOM_054(BOMNAME,BOMVAR,BOMSTATE,BOMTYPE,PARTID,PARTVAR,BOMARCHIVER,TOTALBOMBACKUP2,BOMACHIVETIME) values('"+bomName1+"','A.00','design','0','"+produCode+"','A.00','"+Message.getLoginuserid()+"','"+chName+"','"+nowDate+"')";if(!SystemSql.getInstance().executeUpdate(sql54)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}else {String sql54 = "update BOM_054 set BOMNAME = '"+bomName1 +"',BOMVAR = 'A.00',BOMMODIFYTIME ='"+nowDate+"'," +"PARTVAR='A.00',TOTALBOMBACKUP2='"+chName+"' where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"'";if(!SystemSql.getInstance().executeUpdate(sql54)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}//权限设置开始BOMNAME, BOMVER, PERSONID, PRIVILEGEIDString seSql31 = "select count(*) from Bom_031 where bomname = '"+bomName1+"' and bomver = 'A.00' and " +"partid = '"+produCode+"' and partver = 'A.00' and PERSONID = '"+Message.getLoginuserid()+"'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(seSql31).get(0))[0]) == 0){String sql31 = "insert into BOM_031(BOMNAME,BOMVER,PARTID,PARTVER,PERSONID) values('"+bomName1+"','A.00','"+produCode+"','A.00','"+Message.getLoginuserid()+"')";if(!SystemSql.getInstance().executeUpdate(sql31)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;} }String seSql34 = "select count(*) from Bom_034 where bomname = '"+bomName1+"' and bomver = 'A.00' and " +"PERSONID = '"+Message.getLoginuserid()+"' and PRIVILEGEID = '2'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(seSql34).get(0))[0]) == 0){String sql34 = "insert into BOM_034(BOMNAME,BOMVER,PERSONID,PRIVILEGEID) values('"+bomName1+"','A.00','"+Message.getLoginuserid()+"','2')";if(!SystemSql.getInstance().executeUpdate(sql34)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;} }//权限设置结束//bom_017表插入设计BOM根节点，或者是更新信息BOMNAME, SEQNUM, BOMVARString selectSql017 = "select count(*) from Bom_017 where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"' and SEQNUM = '1'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(selectSql017).get(0))[0]) == 0){String sql017 = "insert into BOM_017(FREZONBOMBACKUP3,PARENTID,FATHERPARTVAR,PARTID,ASSEMBLELEVEL,BOMNAME,PARTVAR,COUNTS,SEQNUM,BOMVAR,CHINANAME,NODETIMESTAMP) " +"values('0','root','root','"+produCode+"',1,'"+bomName1+"','A.00','"+baseCount+"','1','A.00','"+chName+"','"+nowDate+"')";//写入数据库出现错误则停止操作并提示if(!SystemSql.getInstance().executeUpdate(sql017)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}else {String sql017 = "update BOM_017 set PARENTID = 'root',FATHERPARTVAR = 'root',ASSEMBLELEVEL='1',PARTID = '"+produCode+"'" +",PARTVAR='A.00',CHINANAME='"+chName+"',COUNTS = '"+baseCount+"',FREZONBOMBACKUP3 = '0'" +" where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"' and SEQNUM = '1'";//写入数据库出现错误则停止操作并提示if(!SystemSql.getInstance().executeUpdate(sql017)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}// for(int i = 0; i<alt.size(); i++){// System.out.println("list2"+Arrays.toString(alt.get(i))+"----位置"+i +"-----长度"+alt.get(i));String[] s = (String[])alt.get(i);int row = i + 1;// //sql语句：向替代料信息表导入数据String execSql = null;String seqnum1 =s[0];String frezonup3 = "1."+s[0];String assem1 ;if (s[2].equals("1")) {frezonup3 = "1";assem1 = "2";}else {int temp = Integer.parseInt(s[2])-1;int temp2 = Integer.parseInt(s[2])+1;String toTemp2 = String.valueOf(temp2);for (int j = 0; j < temp; j++) {seqnum1= seqnum1+".1";}for (int j = 0; j < temp-1; j++) {frezonup3= frezonup3+".1";}// String temp2 = String.valueOf(temp);// seqnum1 = s[0]+"."+temp2;// frezonup3 = "1."+s[0];assem1 = "3";assem1 = toTemp2;}// //sql语句：查询数据库表中是否已有数据String countSql = "select count(*) from BOM_017 ";// //sql语句中可复用的部分PARTVARString sql = "where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"' and SEQNUM = '1."+seqnum1+"'";// String sql = "where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"' and SEQNUM = '1."+seqnum1+"' and PARENTID = '"+s[3]+"'";// // //执行语句//TODOif(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(countSql + sql).get(0))[0]) == 0){execSql = "insert into Bom_017(PARENTID,PARTID,BOMNAME,COUNTS,SEQNUM,FATHERPARTVAR,BOMVAR,ASSEMBLELEVEL,FREZONBOMBACKUP3,PARTVAR,NODETIMESTAMP) " +"values ('"+produCode+"','"+s[3]+"','"+bomName1+"','"+s[14]+"','1."+seqnum1+"','A.00','A.00','"+assem1+"','"+frezonup3+"','A.00','"+nowDate+"')";}else{execSql = "update bom_017 set COUNTS='"+s[14]+"',PARTID = '"+s[3]+"',FATHERPARTVAR=" +"'A.00',ASSEMBLELEVEL='"+assem1+"',FREZONBOMBACKUP3='"+frezonup3+"'" + sql;}//写入数据库出现错误则停止操作并提示if(!SystemSql.getInstance().executeUpdate(execSql)){wait.dispose();MitOptionPane.showMessageDialog(this, "第"+row+"行写入数据库失败，操作终止！", "错误", 0);return;}}//设计bom附加属性BOMNAME, BOMVAR, SEQNUMString sql094 = "select count(*) from BOM_094 where SEQNUM = '1' and BOMNAME = '"+bomName1+"' and BOMVAR = 'A.00'";//bom_094表插入设计BOM根节点，或者是更新信息if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(sql094).get(0))[0]) == 0){String sql94 = "insert into BOM_094(PARTID,PARTVAR,BOMNAME,BOMVAR,SEQNUM,cusid) " +"values('"+produCode+"','A.00','"+bomName1+"','A.00','1','"+custID+"')";//写入数据库出现错误则停止操作并提示if(!SystemSql.getInstance().executeUpdate(sql94)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}else {String sql94 = "update BOM_094 set cusid = '"+custID+"' where SEQNUM = '1' and BOMNAME = '"+bomName1+"' and BOMVAR = 'A.00'";//写入数据库出现错误则停止操作并提示if(!SystemSql.getInstance().executeUpdate(sql94)){wait.dispose();MitOptionPane.showMessageDialog(this, "写入数据库失败，操作终止！", "错误", 0);return;}}//导入成功记录写入系统日志FrmChangeLog_yl.setLogView("设计bom多层Excel导入", //$NON-NLS-1$"设计bom多层Excel导入成功。", //$NON-NLS-1$FrmChangeLog_yl.BOMMANAGER);wait.dispose(); MitOptionPane.showMessageDialog(this, (new StringBuilder("导入完成，日志信息位于：")).append(logPath).toString(), "数据导入", 1);}/*** 获取要导入的数据* @param fileName String* @param wait DlgWaitting* @return ArrayList* @throws Exception*/ArrayList<String[]> getImportData(String fileName, DlgWaitting wait) throws Exception{ //要返回的导入数据列表ArrayList<String[]> list2 = new ArrayList<String[]>();ArrayList<String[]> list1 = new ArrayList<String[]>();File file = new File(fileName);InputStream input = null;//Excel中对于的行数Row row;int cRow = 1;if(file.exists()){input = new FileInputStream(file);Workbook workbook = ((Workbook) (MitToolKit.isExcel(fileName) ? ((Workbook) (new HSSFWorkbook(input))) : ((Workbook) (new XSSFWorkbook(input)))));Sheet sheet = workbook.getSheetAt(0);//用来做物料-替代料重复校验的map// Map<String, ArrayList<Integer>> map1 = new HashMap<String, ArrayList<Integer>>();Map<String, ArrayList<Integer>> reNum = new HashMap<String, ArrayList<Integer>>();//读取excel全部信息for (int i = 0; i < sheet.getLastRowNum(); i++) {row = sheet.getRow(i);if (row == null)continue;List<String> result = new ArrayList<String>();for (Cell c : row) {boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());// 判断是否具有合并单元格if (isMerge) {result.add(getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()));} else {result.add(getCellValue(c));}}String[] strs1 = result.toArray(new String[result.size()]);list2.add(strs1);}if(list2.size()<10 ||list2.get(9).length != 23){wait.dispose();MitOptionPane.showMessageDialog(this, "Excel模板格式不正确，请检查", "错误", 0);return null;}//集合中获取第10个数据，下标为9String columns[] = list2.get(9);String eqColums[] = new String[11];eqColums[0] = columns[0];eqColums[1] = columns[2];eqColums[2] = columns[3];eqColums[3] = columns[4];eqColums[4] = columns[8];eqColums[5] = columns[10];eqColums[6] = columns[12];eqColums[7] = columns[14];eqColums[8] = columns[16];eqColums[9] = columns[20];eqColums[10] = columns[21];//检查Excel中的表头和模板的表头是否一致if(!Arrays.equals(dataHeader, eqColums)){wait.dispose();MitOptionPane.showMessageDialog(this, "Excel模板格式不正确，请检查", "错误", 0);return null;}else {System.out.println("格式正确！");}//要插入的根件信息（获取表首有用信息）produCode = list2.get(5)[1];chName = list2.get(5)[5];baseCount = list2.get(6)[5];bomName1 = chName+"【"+produCode+"-"+"A.00"+"】";custID = list2.get(5)[3];//过滤格式正常的数据for (String[] strs1 : list2) {if (strs1.length==23 && !strs1[0].equals("顺序号") && !strs1[0].equals("") && !strs1[2].equals("") && !strs1[3].equals("") && !strs1[14].equals("") && strs1[0] != null && strs1[2] != null && strs1[3] != null&& strs1[14] != null) {strs1[3]=strs1[3].replace(".", "");//判断物料库是否存在该物料String strSql1 = "select count(*) from BOM_027 where PARTID = '"+strs1[3]+"' and PARTVAR = 'A.00'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(strSql1).get(0))[0]) == 0){logTxt += "第"+cRow+"行：物料编号“"+strs1[3]+"”不存在或版本不正确;"+"\r\n";}else {String seqnum1 ;if (strs1[2].equals("1")) {seqnum1 = strs1[0];}else {int temp = Integer.parseInt(strs1[2])-1;String temp2 = String.valueOf(temp);seqnum1 = strs1[0]+"."+temp2;}//是否存在设计bom物料信息，存在的话是否进行覆盖String strSql2 = "select count(*) from BOM_017 where BOMVAR = 'A.00' and BOMNAME = '"+bomName1+"' and SEQNUM = '1."+seqnum1+"'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(strSql2).get(0))[0]) == 0){list1.add(strs1);}else {if (isPop) {DlgRepeat dlgRepeat = new DlgRepeat(this,strs1[3]);this.setActiveWnd(dlgRepeat);dlgRepeat.show();btnTxtGet = dlgRepeat.getBtnTxt();if (btnTxtGet.equals("是")) {list1.add(strs1);System.out.println("是");}if (btnTxtGet.equals("一律是")) {isCoverOK=true;isPop=false;}if (btnTxtGet.equals("一律否")) {isPop=false;System.out.println("一律否");}if (btnTxtGet.equals("取消")) {isPop=false;wait.dispose();MitOptionPane.showMessageDialog(this, "操作取消", 1);System.out.println("取消");return null;}}if (isCoverOK) {list1.add(strs1);//System.out.println("一律是");}}//System.out.println("list2"+Arrays.toString(strs1)+"----位置"+cRow +"-----长度"+strs1.length);}if (strs1[0]==null || strs1[2]==null || strs1[3]==null || strs1[14]==null ||strs1[0].equals("") ||strs1[2].equals("") ||strs1[3].equals("") ||strs1[14].equals("")) {logTxt += "第"+cRow+"行有不能为空的数据"+"\r\n";}if (!isDouble(strs1[14]) || Double.valueOf(strs1[14]) < 0) {logTxt += "第"+cRow+"行：用量“"+strs1[14]+"”不是正数;"+"\r\n";}////用来检验物料-设计BOM料重复的key//String key = strs1[3];////保存设计BOM料重复数据出现的行数//ArrayList<Integer> rows1 = new ArrayList<Integer>();////判断设计BOM料重复//if (map1.containsKey(key)) {//rows1 = map1.get(key);//rows1.add(cRow);// map1.put(key, rows1);// // } else {// rows1.add(cRow);// map1.put(key, rows1);//// }//用来检验物料-设计BOM料重复的序列+层次号+物料编码String key1 = strs1[0]+strs1[2];//String key1 = strs1[0]+strs1[2]+strs1[3];//保存设计BOM料重复数据出现的行数ArrayList<Integer> rows2 = new ArrayList<Integer>();//判断设计BOM料重复if (reNum.containsKey(key1)) {rows2 = reNum.get(key1);rows2.add(cRow);reNum.put(key1, rows2);} else {rows2.add(cRow);reNum.put(key1, rows2);}}cRow++;}isPop = true;////遍历判断设计BOM料是否重复// for(Entry<String, ArrayList<Integer>> entry : map1.entrySet()){// ArrayList<Integer> rows = entry.getValue();// if(rows.size() > 1){// logTxt += "数据【物料编码】:【"+entry.getKey()+"】在第"+rows.toString()+"行有重复数据;\r\n";System.out.println("数据【物料编码】:【"+entry.getKey()+"】在第"+rows.toString()+"行有重复数据;\n");// }// }//遍历判断设计BOM编号是否重复for(Entry<String, ArrayList<Integer>> entry : reNum.entrySet()){ArrayList<Integer> rows = entry.getValue();if(rows.size() > 1){//logTxt += "数据【顺序号+层次号+物料编码】:【"+entry.getKey()+"】在第"+rows.toString()+"行有重复数据;\r\n";logTxt += "数据【顺序号+层次号】:【"+entry.getKey()+"】在第"+rows.toString()+"行有重复数据;\r\n";// System.out.println("数据【顺序编号】:【"+entry.getKey()+"】在第"+rows.toString()+"行有重复顺序编号;\n");}}//导入表前部分判断分析double tranType = Double.parseDouble(baseCount);if (produCode == null || produCode.equals("")) {logTxt += "产品编码不能为空\r\n";}if (tranType < 0) {logTxt += "产品数量错误，不能为:"+tranType+"\r\n";}String strSql1 = "select count(*) from BOM_027 where PARTID = '"+produCode+"' and PARTVAR = 'A.00'";if(Integer.parseInt(((String[])SystemSql.getInstance().executeQuery(strSql1).get(0))[0]) == 0){logTxt += "产品编号：“"+produCode+"”不存在或版本不正确;"+"\r\n";}//当有错误数据时停止校验返回空数据并弹窗提示if(!logTxt.equals("")){logTxt = "设计BOM（单层）导入数据校验失败\r\n" + logTxt;createLogFile(importPath);wait.dispose();MitOptionPane.showMessageDialog(this, "数据校验失败，日志信息位于："+logPath, "错误", 0);return null;}}if(input != null){input.close();}//当数据为空时弹窗提示if(list1.size() == 0){wait.dispose();MitOptionPane.showMessageDialog(this, "空数据", "提示", 1);return null;}return list1;}/*** 生成日志文件* @param filePath String* */void createLogFile(String filePath){String path = filePath.substring(0, filePath.lastIndexOf("\\"));logPath = (new StringBuilder(String.valueOf(path))).append("\\importLog.txt").toString();File logfile = new File(logPath);try{//删除原有日志if(logfile.exists()){logfile.delete();}logfile.createNewFile();FileWriter writer = new FileWriter(logfile, true);writer.write(logTxt);writer.flush();writer.close();Desktop.getDesktop().open(logfile);}catch(Exception e){e.printStackTrace();}finally{//清空日志内容logTxt = "";}}/*** 判断是否为Double类型* @param str* @return*/public static boolean isDouble(String str) {if(str == null || str.equals("")){return false;}try {Double.valueOf(str.equals("") ? "0.00" : str);return true;} catch (Exception e) {return false;}}/*** 获取合并单元格的值*/public String getMergedRegionValue(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress ca = sheet.getMergedRegion(i);int firstColumn = ca.getFirstColumn();int lastColumn = ca.getLastColumn();int firstRow = ca.getFirstRow();int lastRow = ca.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {Row fRow = sheet.getRow(firstRow);Cell fCell = fRow.getCell(firstColumn);return getCellValue(fCell);}}}return null;}/*** 判断指定的单元格是否是合并单元格* @param row 行下标* @param column 列下标*/private static boolean isMergedRegion(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();int firstRow = range.getFirstRow();int lastRow = range.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {return true;}}}return false;}/*** 获取单元格的值*/public static String getCellValue(Cell cell) {NumberFormat nf;nf = DecimalFormat.getInstance();nf.setMaximumFractionDigits(9);if (cell == null) {return "";}if (cell.getCellType() == 1) {String str;str = cell.getStringCellValue().trim();str=str.replaceAll(" ", "");str=str.replaceAll("\\s*", "");str=str.replaceAll("(\\\r\\\n|\\\r|\\\n|\\\n\\\r)", "");return str;}if (cell.getCellType() == 4) {return String.valueOf(cell.getBooleanCellValue()).trim();}if (cell.getCellType() == 2) {return cell.getCellFormula().trim();}if (cell.getCellType() == 0) {String str;for(str = nf.format(cell.getNumericCellValue()); str.indexOf(",") > -1; str = (new StringBuilder(String.valueOf(str.substring(0, str.indexOf(","))))).append(str.substring(str.indexOf(",") + 1)).toString());return str.endsWith(".0") ? str.split("\\.")[0] : str;}if(cell.getCellType() == 3){return cell.getStringCellValue();}return "";}