public ExcelDataProvider(String classname, String methodname) { try { int dotNum = classname.indexOf("."); if (dotNum > 0) { classname = classname.substring(classname.lastIndexOf(".") + 1, classname.length()); } String path = "data/" + classname + ".xls"; InputStream inputStream = new FileInputStream(path); book = Workbook.getWorkbook(inputStream); // 方法名即为sheet名 sheet = book.getSheet(methodname); // sheet = book.getSheet(0); rowNum = sheet.getRows(); Cell[] cell = sheet.getRow(0); columnNum = cell.length; columnnName = new String[cell.length]; for (int i = 0; i < cell.length; i++) { columnnName[i] = cell[i].getContents().toString(); } this.currentRowNo++; } catch (Exception e) { e.printStackTrace(); Assert.fail("unable to read Excel data"); } }
public static List<ConsoleVo> readExcel(String fileName) { List<ConsoleVo> list = new ArrayList<ConsoleVo>(); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet[] sheets = book.getSheets(); for (Sheet s : sheets) { for (int i = 1; i < s.getRows(); i++) { ConsoleVo m = new ConsoleVo(); Cell[] cells = s.getRow(i); if (cells != null) { System.out.println(cells.length); m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents())); m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents())); m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents())); m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents())); list.add(m); } else { System.out.print("excel格式错误导入数据失败!"); return null; } } } book.close(); return list; } catch (Exception e) { e.printStackTrace(); } return null; }
public static void main(String args[]) { try { // 构建Workbook对象, 只读Workbook对象 // 直接从本地文件创建Workbook // 从输入流创建Workbook System.out.println("start load file-------------------------"); InputStream is = new FileInputStream("E:/account.xls"); // 创建输入 jxl.Workbook rwb = Workbook.getWorkbook(is); Sheet rs = rwb.getSheet(0); // 读取第一个sheet int colNum = rs.getColumns(); // 列数 int rowNum = rs.getRows(); // 行数 // System.out.println(colNum+" "+rowNum); // 创建account bean ApplicationContext ctx = new ClassPathXmlApplicationContext( new String[] { "spring/applicationContext-model-account.xml", "spring/applicationContext.xml" }); AccountService g = (AccountService) ctx.getBean("accountService"); for (int i = 1; i < rowNum; i++) { String no = rs.getCell(0, i).getContents(); String pass = rs.getCell(1, i).getContents(); String name = rs.getCell(2, i).getContents(); String maj = rs.getCell(3, i).getContents(); String grade = rs.getCell(4, i).getContents(); String cla = rs.getCell(5, i).getContents(); String idn = rs.getCell(6, i).getContents(); String email = rs.getCell(7, i).getContents(); String phone = rs.getCell(8, i).getContents(); String status = rs.getCell(9, i).getContents(); String role = rs.getCell(10, i).getContents(); ModelAccount ma = new ModelAccount(); ma.setAccountNo(no); ma.setAccountPassword(pass); ma.setAccountRealName(name); ma.setAccountMajority(maj); ma.setAccountGrade(grade); ma.setAccountClass(cla); ma.setAccountIdentification(idn); ma.setAccountEmail(email); ma.setAccountMobilePhone(phone); ma.setAccountStatus(0); ModelRoles ac = new ModelRoles(); ac.setRole_id(role); ma.setAccountRole(ac); g.saveOrUpdate(ma); // ma.setAccount_role(account_role) // System.out.println(no); } } catch (Exception e) { e.printStackTrace(); } }
public void createExcel(String file) { String filename = createPath(file); System.out.println(filename); /* try { WorkbookSettings ws = new WorkbookSettings(); ws.setLocale(new Locale("sv", "SE")); WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws); WritableSheet s = workbook.createSheet("Sheet1", 0); //writeDataSheet(s); workbook.write(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); }**/ try { File newFile = new File(filename); Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newFile), "UTF8")); // BufferedWrite output = new BufferedWriter(new OutputStreamReader(new // FileInputS§tream(filename), "iso-8859-1")); // FileWriter fw = new FileWriter(newFile.getAbsoluteFile()); // BufferedWriter bw = new BufferedWriter(fw); // bw.write("Artikel;Antal/st;Pris/st;Total\n"); out.append("Artikel;Antal/st;Pris/st;Total\n"); for (Data d : dataList) { out.append(d.toString()); out.append("\n"); // bw.write(d.toString()); // bw.write("\n"); } // bw.close(); out.flush(); out.close(); } catch (UnsupportedEncodingException unsuppEn) { unsuppEn.printStackTrace(); } catch (IOException ioE) { ioE.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } }
// DESDE AQUI AGREGADO POR GHUCHET private void completarMovimiento(MMovement move) { // Preparar el movimiento move.setDocAction(X_M_Movement.DOCACTION_Prepare); DocumentEngine.processIt(move, X_M_Movement.DOCACTION_Prepare); move.save(); commit(); move.load(get_TrxName()); if (move.getDocStatus().equals("IN")) { ADialog.error(EnvConstants.WINDOW_INFO, new Container(), "NoLines"); } else { // Aca debo actualizar las cantidades aprobadas String sql = "SELECT QtyRequired, XX_ApprovedQty FROM M_MOVEMENTLINE WHERE M_MOVEMENT_ID = " + move.getM_Movement_ID(); PreparedStatement ps_1 = DB.prepareStatement( sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, null); ResultSet rs_1 = null; // System.out.println(sql); int qty_required = 0; try { rs_1 = ps_1.executeQuery(); while (rs_1.next()) { qty_required = rs_1.getInt("QtyRequired"); rs_1.updateInt("XX_ApprovedQty", qty_required); rs_1.updateRow(); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs_1.close(); ps_1.close(); } catch (SQLException e) { e.printStackTrace(); } } move.setXX_Status("PE"); move.setXX_RequestDate(move.getUpdated()); move.save(); // Se envian correos dependiendo del tipo de documento enviarCorreoATienda(move, Env.getCtx().getContextAsInt("#XX_L_MT_PTRANSFERAPPROVAL_ID")); } }
public boolean hasNext() { if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) { try { book.close(); } catch (Exception e) { e.printStackTrace(); } return false; } else { // sheet下一行内容为空判定结束 if ((sheet.getRow(currentRowNo))[0].getContents().equals("")) return false; return true; } }
public SingleNameReport(WritableWorkbook workbook, Statement new_statement, String[] arguments) { String main_tsn = ""; String main_rank = ""; String main_kingdom = ""; row = 0; try { statement = new_statement; func = new AdditionalFunctions(statement); copy = workbook; kingdom = arguments[0]; hrank = arguments[1]; lrank = arguments[2]; scientificName = arguments[3]; date_from = arguments[4]; date_to = arguments[5]; user = arguments[6]; String temp = ""; temp = "SELECT tsn,rank_id,kingdom_id from Tree where scientificName='" + scientificName + "'"; System.out.println(temp); result = statement.executeQuery(temp); metadata = result.getMetaData(); int numberOfRows = 0; int hrankVal = Integer.parseInt(hrank); int main_rankVal = 0; if (result.last()) numberOfRows = result.getRow(); result.first(); if (numberOfRows > 0 && metadata.getColumnCount() > 0) { main_tsn = result.getString(1); main_rank = result.getString(2); main_rankVal = Integer.parseInt(main_rank); main_kingdom = result.getString(3); } if (HasChildren(main_tsn) == true) FindChildInformation(main_tsn, arguments, func); FileInFrontPage frontPage = new FileInFrontPage(copy, statement, arguments); copy.write(); copy.close(); } catch (Exception e) { e.printStackTrace(); System.exit(1); } } // end of constructor
// public method to extract and write the Publication information public static void WritePublication(String publicationId, int rowNum) { WritableSheet sheet2 = copy.getSheet(1); try { String query = "SELECT address,author,publicationTitle,chapter,edition,editor,month,day,note,number,publisher," + "series,title,volume,year,isbn,issn,pages FROM Publication WHERE id=" + publicationId; System.out.println(query); ResultSet result_query = statement.executeQuery(query); ResultSetMetaData metadata_query = result_query.getMetaData(); int numberOfRows = 0; if (result_query.last()) numberOfRows = result_query.getRow(); result_query.first(); if (numberOfRows > 0 && metadata_query.getColumnCount() > 0) { String aut_edi = result_query.getString(2); System.out.println("autor is " + aut_edi); if (result_query.getString(6).compareTo("") != 0) aut_edi = aut_edi + " / " + result_query.getString(6) + ",eds."; Label label = new Label(7, rowNum, aut_edi); sheet2.addCell(label); label = new Label(8, rowNum, result_query.getString(13)); sheet2.addCell(label); String publication_name = result_query.getString(3); if (result_query.getString(12).compareTo("") != 0) publication_name = publication_name + ", ser. " + result_query.getString(12); if (result_query.getString(14).compareTo("0") != 0) publication_name = publication_name + ", vol. " + result_query.getString(14); if (result_query.getString(10).compareTo("0") != 0) publication_name = publication_name + ",no. " + result_query.getString(10); label = new Label(9, rowNum, publication_name); sheet2.addCell(label); label = new Label(12, rowNum, result_query.getString(11)); sheet2.addCell(label); label = new Label(13, rowNum, result_query.getString(1)); sheet2.addCell(label); label = new Label(15, rowNum, result_query.getString(9)); sheet2.addCell(label); label = new Label(16, rowNum, result_query.getString(16)); sheet2.addCell(label); label = new Label(17, rowNum, result_query.getString(17)); sheet2.addCell(label); label = new Label(14, rowNum, result_query.getString(18)); sheet2.addCell(label); String date = result_query.getString(15); String month = "Jan"; System.out.println("month is " + result_query.getString(7)); if (result_query.getString(7).compareTo("") != 0) month = result_query.getString(7); month = func.ConvertMonth(month); date = month + "/" + date; String day = "01"; if (result_query.getString(8).compareTo("0") != 0) day = result_query.getString(8); if (day.length() == 1) day = "0" + day; date = day + "/" + date; label = new Label(10, rowNum, date); sheet2.addCell(label); label = new Label(11, rowNum, date); sheet2.addCell(label); } } catch (Exception e) { e.printStackTrace(); System.exit(1); } } // end of WritePublication
// public method to find child information public void FindChildInformation(String tsn, String[] arguments, AdditionalFunctions func) { WritableSheet sheet2 = copy.getSheet(1); Label label = null; String children[] = null; String temp = "", current_tsn = "", dateCreated = ""; int dateCheck = 1; temp = "SELECT distinct tsn from Tree where parent_tsn='" + tsn + "'"; try { result = statement.executeQuery(temp); metadata = result.getMetaData(); int numberOfRows = 0; if (result.last()) numberOfRows = result.getRow(); if (numberOfRows > 0 && metadata.getColumnCount() > 0) { result.first(); children = new String[numberOfRows]; for (int j = 0; j < numberOfRows; j++) { children[j] = result.getString(1); result.next(); } } for (int j = 0; j < numberOfRows; j++) { int current_tsnId = Integer.parseInt(children[j]); if (current_tsnId >= 999000000) { temp = "SELECT distinct Tree.tsn,unit_name1,unit_name2,unit_name3,unit_name4,scientificName,`usage`," + "taxon_author,parent_tsn,rank_name, pages, publicationId, userId, dateCreated, nameType FROM Tree," + " BaseObject, TaxonConcepts, TaxonAuthors,TaxonUnitTypes" + " WHERE Tree.tsn=TaxonConcepts.tsn AND Tree.tsn='" + current_tsnId + "' AND " + "BaseObject.id=TaxonConcepts.id AND TaxonAuthors.taxon_author_id=Tree.taxon_author_id AND " + "Tree.rank_id=TaxonUnitTypes.rank_id"; System.out.println(temp); result = statement.executeQuery(temp); metadata = result.getMetaData(); int RowNumber = 0; if (result.last()) RowNumber = result.getRow(); if (RowNumber > 0 && metadata.getColumnCount() > 0) { current_tsn = result.getString(1); dateCreated = result.getString(14); DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); Date d1 = df.parse(dateCreated); Date d2 = null; if (date_from.compareTo("all") != 0) { d2 = df.parse(date_from); if (d2.before(d1) || d2.equals(d1)) dateCheck = 1; else dateCheck = 0; } if (date_to.compareTo("today") != 0) { d2 = df.parse(date_to); if (d1.before(d2) || d1.equals(d2)) dateCheck = 1; else dateCheck = 0; } if (result.getString(7).compareTo("public") == 0 && result.getString(15).compareTo("Regular scientific name") == 0 && result.getString(12).compareTo("") != 0 && dateCheck == 1) { String scientificName = result.getString(6); String taxon_author = result.getString(8); String publicationId = result.getString(12); for (int k = 0; k < 4; k++) { if (result.getString(k + 2).compareTo("null") != 0) { label = new Label(k, row + 1, result.getString(k + 2)); sheet2.addCell(label); } } label = new Label(4, row + 1, taxon_author); sheet2.addCell(label); label = new Label(5, row + 1, result.getString(10)); sheet2.addCell(label); String parent_tsn = result.getString(9); String userId = result.getString(13); WritePublication(publicationId, row + 1); label = new Label(18, row + 1, func.GetSubmitter(userId)); sheet2.addCell(label); label = new Label(6, row + 1, func.GetParent(parent_tsn)); sheet2.addCell(label); row = row + 1; } } } continue; } for (int j = 0; j < numberOfRows; j++) { if (HasChildren(children[j]) == true) FindChildInformation(children[j], arguments, func); else continue; } } catch (Exception e) { e.printStackTrace(); System.exit(1); } } // end of FindChildInformation
/** Envia correos al personal de tienda en caso de la aprobación de un traspaso */ private void enviarCorreoATienda(MMovement movimiento, int plantillaDeCorreo) { MWarehouse almaSalida = new MWarehouse(getCtx(), movimiento.getM_WarehouseFrom_ID(), null); MWarehouse almaLleada = new MWarehouse(getCtx(), movimiento.getM_WarehouseTo_ID(), null); X_XX_VMR_Department departamento = new X_XX_VMR_Department(getCtx(), movimiento.getXX_VMR_Department_ID(), null); // Mensaje debe indicar departamento, origen, destino y traspaso String mensaje = Msg.getMsg( getCtx(), "XX_PTransferApproval", new String[] { movimiento.getDocumentNo(), departamento.getValue() + "-" + departamento.getName(), almaSalida.getValue() + "-" + almaSalida.getName(), almaLleada.getValue() + "-" + almaLleada.getName(), movimiento.getXX_StatusName() }); // Al Gerente de Tienda // Selecciono el o los gerentes de Tienda String SQL = "SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' " + " AND C_BPARTNER_ID IN " + "(" + "SELECT C_BPARTNER_ID " + "FROM C_BPARTNER WHERE isActive='Y' " + "AND C_JOB_ID =" + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_STOREMAN_ID") + " " + "AND (M_WAREHOUSE_ID = " + almaLleada.get_ID() + " " + "OR M_WAREHOUSE_ID = " + almaSalida.get_ID() + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")" + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")"; Vector<Integer> storeManagers = new Vector<Integer>(); try { PreparedStatement pstmt = DB.prepareStatement(SQL, null); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { storeManagers.add(rs.getInt("AD_USER_ID")); } rs.close(); pstmt.close(); } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } // Envio correos a los gerentes Utilities f = null; for (int i = 0; i < storeManagers.size(); i++) { f = new Utilities( Env.getCtx(), null, plantillaDeCorreo, mensaje, -1, Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"), -1, storeManagers.get(i), null); try { f.ejecutarMail(); } catch (Exception e) { e.printStackTrace(); } f = null; } // ********************************* // Selecciono los asesores de almacen SQL = "SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' " + "AND C_BPARTNER_ID IN " + "(" + "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' " + "AND C_JOB_ID =" + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_DEPASE_ID") + " " + "AND (M_WAREHOUSE_ID = " + almaLleada.get_ID() + " " + "OR M_WAREHOUSE_ID = " + almaSalida.get_ID() + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")" + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")"; Vector<Integer> warehouseAsessors = new Vector<Integer>(); try { PreparedStatement pstmt = DB.prepareStatement(SQL, null); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { warehouseAsessors.add(rs.getInt("AD_USER_ID")); } rs.close(); pstmt.close(); } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } // Envio correos a los asesores Utilities m = null; for (int i = 0; i < warehouseAsessors.size(); i++) { m = new Utilities( Env.getCtx(), null, plantillaDeCorreo, mensaje, -1, Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"), -1, warehouseAsessors.get(i), null); try { m.ejecutarMail(); } catch (Exception e) { e.printStackTrace(); } m = null; } // ********************************* // Selecciono los Gerentes de Area ADMIN y MERCA, Asesor de Inventario SQL = "SELECT AD_USER_ID FROM AD_USER WHERE ISACTIVE='Y' " + "AND C_BPARTNER_ID IN " + "(" + "SELECT C_BPARTNER_ID FROM C_BPARTNER WHERE isActive='Y' " + "AND C_JOB_ID IN (" + " " + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_ADMINMANAG_ID") + "," + " " + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_GAMERC_ID") + "," + " " + Env.getCtx().getContextAsInt("#XX_L_JOBPOSITION_INVASSES_ID") + ") " + "AND (M_WAREHOUSE_ID = " + almaLleada.get_ID() + " " + "OR M_WAREHOUSE_ID = " + almaSalida.get_ID() + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")" + ") " + "AND AD_Client_ID IN (0," + Env.getCtx().getAD_Client_ID() + ")"; Vector<Integer> managerArea = new Vector<Integer>(); try { PreparedStatement pstmt = DB.prepareStatement(SQL, null); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { managerArea.add(rs.getInt("AD_USER_ID")); } rs.close(); pstmt.close(); } catch (Exception a) { log.log(Level.SEVERE, SQL, a); } // Envio correos a gerentes de area Utilities u = null; for (int i = 0; i < managerArea.size(); i++) { u = new Utilities( Env.getCtx(), null, plantillaDeCorreo, mensaje, -1, Env.getCtx().getContextAsInt("#XX_L_USERFROMMAIL_ID"), -1, managerArea.get(i), null); try { u.ejecutarMail(); } catch (Exception e) { e.printStackTrace(); } u = null; } }