List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:data.services.ColorGroupService.java
public void updateFromXml(File fl) { try {/*from w ww.j a v a2s. c om*/ FileInputStream fi = new FileInputStream(fl); try { HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next(); Long id = StringAdapter.toLong(StringAdapter.HSSFSellValue(row.getCell(0))); /*String uid=StringAdapter.HSSFSellValue(row.getCell(3)); String val=StringAdapter.HSSFSellValue(row.getCell(4)); String pers=StringAdapter.HSSFSellValue(row.getCell(5)); String rad=StringAdapter.HSSFSellValue(row.getCell(6));*/ String uid = row.getCell(3).getStringCellValue(); String val = row.getCell(4).getStringCellValue(); String pers = row.getCell(5).getStringCellValue(); String rad = row.getCell(6).getStringCellValue(); ColorGroup cl = colorGroupDao.find(id); if (cl != null) { cl.setUid(uid); cl.setParamValue(StringAdapter.toDouble(val)); cl.setParamValue(StringAdapter.toDouble(pers)); cl.setRadical(rad); if (validate(cl)) { colorGroupDao.update(cl); } } } workbook.close(); } catch (Exception e) { addError(StringAdapter.getStackTraceException(e)); } fi.close(); } catch (Exception e) { addError(" xml"); addError(e.getMessage()); } }
From source file:data.services.ColorService.java
public void updateFromXml(File fl) { try {//from ww w. j a v a 2 s .co m FileInputStream fi = new FileInputStream(fl); try { HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next(); Long id = StringAdapter.toLong(StringAdapter.HSSFSellValue(row.getCell(0))); /*String uid=StringAdapter.HSSFSellValue(row.getCell(3)); String val=StringAdapter.HSSFSellValue(row.getCell(4)); String pers=StringAdapter.HSSFSellValue(row.getCell(5)); String rad=StringAdapter.HSSFSellValue(row.getCell(6));*/ Cell uidc = row.getCell(3); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell valc = row.getCell(4); Double val = (double) 0; if (valc != null) { String valstr = StringAdapter.HSSFSellValue(valc).replace(",", ".").trim(); val = StringAdapter.toDouble(valstr); } Cell percc = row.getCell(5); Long perc = (long) 0; if (percc != null) { String percstr = StringAdapter.HSSFSellValue(percc); if (percstr.contains(".")) { int point = percstr.indexOf("."); percstr = percstr.substring(0, point); } if (!percstr.equals("")) { perc = StringAdapter.toLong(percstr); } } Cell radc = row.getCell(6); String rad = ""; if (radc != null) { rad = StringAdapter.HSSFSellValue(radc).trim(); if (rad.contains(".")) { int point = rad.indexOf("."); rad = rad.substring(0, point); } if (rad.trim().equals("0")) { rad = ""; } else { rad = rad.replace(" ", ""); } } Cell ac = row.getCell(7); String a = "0"; if (ac != null) { a = StringAdapter.HSSFSellValue(ac); if (a.contains(".")) { int point = a.indexOf("."); a = a.substring(0, point); } if (a.equals("")) { a = "0"; } } Cell vc = row.getCell(8); String v = "0"; if (vc != null) { v = StringAdapter.HSSFSellValue(vc); if (v.contains(".")) { int point = v.indexOf("."); v = v.substring(0, point); } if (v.equals("")) { v = "0"; } } Cell kc = row.getCell(9); String k = "0"; if (kc != null) { k = StringAdapter.HSSFSellValue(ac); if (k.contains(".")) { int point = k.indexOf("."); k = k.substring(0, point); } if (k.equals("")) { k = "0"; } } Color cl = colorDao.find(id); if (cl != null) { cl.setUid(uid); cl.setParamValue(val); cl.setPercentValue(perc); cl.setRadical(rad); cl.setAudial(Integer.valueOf(a)); cl.setVisual(Integer.valueOf(v)); cl.setKinestet(Integer.valueOf(k)); if (validate(cl)) { colorDao.update(cl); } } } workbook.close(); } catch (Exception e) { addError(StringAdapter.getStackTraceException(e)); } fi.close(); } catch (Exception e) { addError(" xml"); addError(e.getMessage()); } }
From source file:data.services.FeatureService.java
public void updateFromXml(File fl) { try {// w ww . ja v a 2 s. c o m FileInputStream fi = new FileInputStream(fl); int rownumber = 1; HSSFWorkbook workbook = new HSSFWorkbook(fi); try { HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { rownumber++; Row row = it.next(); Cell idc = row.getCell(0); Long id = null; if (idc != null) { id = StringAdapter.toLong(StringAdapter.HSSFSellValue(row.getCell(0))); } if (id != null) { Cell uidc = row.getCell(5); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell valc = row.getCell(6); Double val = (double) 0; if (valc != null) { String valstr = StringAdapter.HSSFSellValue(valc); val = StringAdapter.toDouble(valstr); } Cell percc = row.getCell(7); Long perc = (long) 0; if (percc != null) { String percstr = StringAdapter.HSSFSellValue(percc); if (percstr.contains(".")) { int point = percstr.indexOf("."); percstr = percstr.substring(0, point); } if (!percstr.equals("")) { perc = StringAdapter.toLong(percstr); } } Cell radc = row.getCell(8); String rad = ""; if (radc != null) { rad = StringAdapter.HSSFSellValue(radc).trim(); if (rad.contains(".")) { int point = rad.indexOf("."); rad = rad.substring(0, point); } if (rad.trim().equals("0")) { rad = ""; } else { rad = rad.replace(" ", ""); } } Cell ac = row.getCell(9); String a = "0"; if (ac != null) { a = StringAdapter.HSSFSellValue(ac); if (a.contains(".")) { int point = a.indexOf("."); a = a.substring(0, point); } if (a.equals("")) { a = "0"; } } Cell vc = row.getCell(10); String v = "0"; if (vc != null) { v = StringAdapter.HSSFSellValue(vc); if (v.contains(".")) { int point = v.indexOf("."); v = v.substring(0, point); } if (v.equals("")) { v = "0"; } } Cell kc = row.getCell(11); String k = "0"; if (kc != null) { k = StringAdapter.HSSFSellValue(kc); if (k.contains(".")) { int point = k.indexOf("."); k = k.substring(0, point); } if (k.equals("")) { k = "0"; } } Feature cl = featureDao.find(id); if (cl != null) { cl.setUid(uid); cl.setParamValue(val); cl.setPercentValue(perc); cl.setRadical(rad); cl.setAudial(Integer.valueOf(a)); cl.setVisual(Integer.valueOf(v)); cl.setKinestet(Integer.valueOf(k)); if (validate(cl, " " + rownumber + "; ")) { featureDao.update(cl); } } } } } catch (NumberFormatException e) { addError(" " + rownumber + "; " + StringAdapter.getStackTraceException(e)); } workbook.close(); fi.close(); } catch (IOException e) { addError(" xml"); addError(e.getMessage()); } }
From source file:data.services.FreeOptionService.java
public void updateFromXml(File fl) { try {//from w ww .j a v a 2 s . c om FileInputStream fi = new FileInputStream(fl); int i = 1; String listName = "? ."; int s = 39191091; List<FreeOption> foForSave = new ArrayList(); List<FreeOption> foForUpd = new ArrayList(); try { HSSFWorkbook workbook = new HSSFWorkbook(fi); int sheetNumber = workbook.getNumberOfSheets(); while (i < sheetNumber) { HSSFSheet sheet = workbook.getSheetAt(i); i++; listName = sheet.getSheetName(); Iterator<Row> it = sheet.iterator(); Car car = new Car(); s = 0; while (it.hasNext()) { Row row = it.next(); s++; Cell idCell = row.getCell(0); if (idCell.getCellType() == Cell.CELL_TYPE_STRING) { String nameCell = idCell.getStringCellValue().trim(); if (nameCell.equals("CAR_ID")) { row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); //throw new Exception(" carIdstr="+carIdstr+"; carId="+carId+"; "); } else if (nameCell.equals("OPTION_ID")) { while (it.hasNext()) { row = it.next(); s++; Cell optIdCell = row.getCell(0); if (optIdCell != null) { if (optIdCell.getCellType() == Cell.CELL_TYPE_STRING) { String optIdstr = optIdCell.getStringCellValue().trim(); if (optIdstr.equals("CAR_ID")) { /*it.remove(); break;*/ row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); it.next(); s++; } else { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("4!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } //addError(": ? , ."); } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); //Long optId = StringAdapter.toLong(StringAdapter.getString(optIdCell.getNumericCellValue())); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("3!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_BLANK) { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("2!"); if (validate(fo)) { foForSave.add(fo); } } } } else { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("1! rad="+fo.getRadical()+"; a="+fo.getAudial()+"; v="+fo.getVisual()+"; perc="+fo.getPercentValue()+"; val="+fo.getParamValue()+"; price="+fo.getPrice()+"; "); if (validate(fo)) { foForSave.add(fo); } } } } } } } } workbook.close(); } catch (Exception e) { addError(": ?:" + i + ", " + listName + ", ?:" + s + ", " + StringAdapter.getStackTraceException(e)); } fi.close(); for (FreeOption fo : foForSave) { freeOptionDao.save(fo); } for (FreeOption fo : foForUpd) { freeOptionDao.update(fo); } } catch (Exception e) { addError(" xml"); addError(e.getMessage()); } }
From source file:data.services.PropertyNameService.java
public void updateFromXml(File fl) throws Exception { try {/*from ww w .j a v a 2 s . c o m*/ FileInputStream fi = new FileInputStream(fl); int i = 0; int m = 0; try { HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { Row row = it.next(); Long cpid = StringAdapter.toLong(StringAdapter.HSSFSellValue(row.getCell(0))); Cell pnValc = row.getCell(3); String pnVal = ""; if (pnValc != null) { pnVal = StringAdapter.HSSFSellValue(pnValc).trim(); } Cell radc = row.getCell(4); String rad = ""; if (radc != null) { rad = StringAdapter.HSSFSellValue(radc); if (rad.contains(".")) { int point = rad.indexOf("."); rad = rad.substring(0, point); } if (rad.trim().equals("0")) { rad = ""; } else { rad = rad.replace(" ", ""); } } Cell percc = row.getCell(5); Long perc = (long) 0; if (percc != null) { String percstr = StringAdapter.HSSFSellValue(percc); if (percstr.contains(".")) { int point = percstr.indexOf("."); percstr = percstr.substring(0, point); } if (!percstr.equals("")) { perc = StringAdapter.toLong(percstr); } } Cell valc = row.getCell(6); Double val = (double) 0; if (valc != null) { String valstr = StringAdapter.HSSFSellValue(valc).replace(",", ".").trim(); val = StringAdapter.toDouble(valstr); } Cell audc = row.getCell(7); Long a = (long) 0; if (audc != null) { String supstr = StringAdapter.HSSFSellValue(audc); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } a = Long.valueOf(supstr); } Cell visc = row.getCell(8); Long v = (long) 0; if (visc != null) { String supstr = StringAdapter.HSSFSellValue(visc); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } v = Long.valueOf(supstr); } Cell kinc = row.getCell(9); Long k = (long) 0; if (kinc != null) { String supstr = StringAdapter.HSSFSellValue(kinc); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } k = Long.valueOf(supstr); } /*Cell aCell = row.getCell(7); Long a=(long)0; if(aCell!=null){ if(aCell.getCellType()==Cell.CELL_TYPE_NUMERIC){ a=StringAdapter.toLong(StringAdapter.getString(aCell.getNumericCellValue())); }else if(aCell.getCellType()==Cell.CELL_TYPE_STRING){ a=StringAdapter.toLong(aCell.getStringCellValue().trim()); } } Cell vCell = row.getCell(8); Long v=(long)0; if(vCell!=null){ if(vCell.getCellType()==Cell.CELL_TYPE_NUMERIC){ v=StringAdapter.toLong(StringAdapter.getString(vCell.getNumericCellValue())); }else if(vCell.getCellType()==Cell.CELL_TYPE_STRING){ v=StringAdapter.toLong(vCell.getStringCellValue().trim()); } } Cell kCell = row.getCell(9); Long k=(long)0; if(kCell!=null){ if(kCell.getCellType()==Cell.CELL_TYPE_NUMERIC){ k=StringAdapter.toLong(StringAdapter.getString(kCell.getNumericCellValue())); }else if(kCell.getCellType()==Cell.CELL_TYPE_STRING){ k=StringAdapter.toLong(kCell.getStringCellValue().trim()); } }*/ CarProperty cp = carPropertyDao.find(cpid); PropertyName suppn = new PropertyName(); suppn.setPropertyNameValue(pnVal); suppn.setCarProperty(cp); List<PropertyName> pnlist = propertyNameDao.findByPropertyAndPnValue(suppn); if (!pnlist.isEmpty()) { for (PropertyName pn : pnlist) { if (!(pn.getPercentValue().equals(perc) && pn.getParamValue().equals(val) && pn.getRadical().equals(rad) && pn.getAudial().equals(a) && pn.getVisual().equals(v) && pn.getKinestet().equals(k))) { pn.setPercentValue(perc); pn.setParamValue(val); pn.setRadical(rad); pn.setVisual(v); pn.setAudial(a); pn.setKinestet(k); if (validate(pn)) { propertyNameDao.update(pn); } i++; } m++; } } //} } workbook.close(); addError(" " + i + " ?; ? ?: " + m + "; "); } catch (Exception e) { //addError(StringAdapter.getStackTraceException(e) + "str:"+i+"; "); throw new Exception(StringAdapter.getStackTraceException(e) + "str:" + i + "; "); } fi.close(); } catch (Exception e) { //addError(" xml"); //addError(e.getMessage()); throw new Exception(StringAdapter.getStackTraceException(e)); } }
From source file:data.services.SceneService.java
public void updateFromXml(File fl, Long sceneId) throws Exception { try {/* w ww . java2s . co m*/ Scene scene = sceneDao.find(sceneId); if (scene != null) { FileInputStream fi = new FileInputStream(fl); int i = 0; int m = 0; try { List<ValueRange> newVrList = new ArrayList(); HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { Row row = it.next(); Cell uidc = row.getCell(0); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc).trim(); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell aminCell = row.getCell(2); Long amin = (long) 0; if (aminCell != null) { String supstr = StringAdapter.HSSFSellValue(aminCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amin = StringAdapter.toLong(supstr); } } Cell minCell = row.getCell(3); Long min = (long) 0; if (minCell != null) { String supstr = StringAdapter.HSSFSellValue(minCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { min = StringAdapter.toLong(supstr); } } Cell maxCell = row.getCell(4); Long max = (long) 0; if (maxCell != null) { String supstr = StringAdapter.HSSFSellValue(maxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { max = StringAdapter.toLong(supstr); } } Cell amaxCell = row.getCell(5); Long amax = (long) 0; if (amaxCell != null) { String supstr = StringAdapter.HSSFSellValue(amaxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amax = StringAdapter.toLong(supstr); } } ValueRange vr = new ValueRange(); vr.setUid(uid); vr.setAmin(amin); vr.setValueMin(min); vr.setValueMax(max); vr.setAmax(amax); vr.setScene(scene); newVrList.add(vr); } List<ValueRange> oldVrList = scene.getValueRanges(); for (ValueRange vr : oldVrList) { valueRangeService.delete(vr.getId()); } for (ValueRange vr : newVrList) { valueRangeService.create(vr); } workbook.close(); } catch (Exception e) { //addError(StringAdapter.getStackTraceException(e) + "str:"+i+"; "); throw new Exception(StringAdapter.getStackTraceException(e) + "str:" + i + "; "); } fi.close(); } } catch (Exception e) { //addError(" xml"); //addError(e.getMessage()); throw new Exception(StringAdapter.getStackTraceException(e)); } }
From source file:data.services.SequenceService.java
public void updateFromXml(File fl, Long seqId) throws Exception { try {//from ww w. j a v a 2s .c o m Sequence seq = sequenceDao.find(seqId); if (seq != null) { FileInputStream fi = new FileInputStream(fl); int i = 0; int m = 1; try { List<SequenceValueRange> newSVrList = new ArrayList(); HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { m++; Row row = it.next(); Cell uidc = row.getCell(0); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc).replace(" ", "").trim(); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell aminCell = row.getCell(1); Long amin = null; if (aminCell != null) { String supstr = StringAdapter.HSSFSellValue(aminCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amin = StringAdapter.toLong(supstr); } } Cell minCell = row.getCell(2); Long min = null; if (minCell != null) { String supstr = StringAdapter.HSSFSellValue(minCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { min = StringAdapter.toLong(supstr); } } Cell maxCell = row.getCell(3); Long max = null; if (maxCell != null) { String supstr = StringAdapter.HSSFSellValue(maxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { max = StringAdapter.toLong(supstr); } } Cell amaxCell = row.getCell(4); Long amax = null; if (amaxCell != null) { String supstr = StringAdapter.HSSFSellValue(amaxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amax = StringAdapter.toLong(supstr); } } BaseParam bp = baseParamService.getBaseParam(uid); if (bp != null) { SequenceValueRange vr = new SequenceValueRange(); vr.setUid(uid); vr.setName(bp.getName()); vr.setAmin(amin); vr.setValueMin(min); vr.setValueMax(max); vr.setAmax(amax); vr.setSequence(seq); if (validate(vr, "??? , ? " + m + "; ")) { newSVrList.add(vr); } } } List<SequenceValueRange> oldSVrList = seq.getSequenceValueRanges(); for (SequenceValueRange vr : oldSVrList) { sequenceValueRangeService.delete(vr.getId()); } for (SequenceValueRange vr : newSVrList) { sequenceValueRangeService.create(vr); } workbook.close(); } catch (Exception e) { //addError(StringAdapter.getStackTraceException(e) + "str:"+i+"; "); throw new Exception(StringAdapter.getStackTraceException(e) + "str: " + m + "; "); } fi.close(); } } catch (Exception e) { //addError(" xml"); //addError(e.getMessage()); throw new Exception(StringAdapter.getStackTraceException(e)); } }
From source file:database_lab1.ExcelToMySQL.java
public void ExcelFileReader(String filename) throws IOException, SQLException { FileInputStream fis = null;/*from ww w.j a v a 2 s . com*/ Scanner sc = new Scanner(System.in); try { fis = new FileInputStream(filename); String queryValue = (new File(filename).getName().replaceAll("(.xls)", "")); System.out.print("Please input the schema name:"); String schemaName = sc.next(); System.out.print("Please input the database username:"); String userName = sc.next(); System.out.print("Please input the database password:"); String password = sc.next(); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); //variable that will use to find the first row int firstrow = 0, maxColumn = 0; //iterator for the row values while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector<String> cellStoreVector = new Vector<String>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String cellvalue; //determines if cell value is string or numaric if (myCell.getCellType() == 1) cellvalue = myCell.getStringCellValue(); else cellvalue = (int) myCell.getNumericCellValue() + ""; //adds the value in the vector cellStoreVector.addElement(cellvalue); //prints out the cell valuse System.out.print(cellvalue + " "); } System.out.println(); //if the row is not the first row then, //insert the data in database if (firstrow != 0) { dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/" + schemaName, userName, password); //sql comment String insert = "INSERT INTO " + queryValue + " VALUES (?,?,?,?,?,?);"; PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert); for (int i = 1; i <= cellStoreVector.size(); i++) { ps.setString(i, cellStoreVector.get(i - 1)); } //executing the sql command ps.execute(); } firstrow++; } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } }
From source file:database_lab1.studentExcel.java
public void studentExcelFile(String filename) throws IOException, SQLException { FileInputStream fis = null;// ww w . j ava2 s.co m try { fis = new FileInputStream(filename); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); int firstrow = 0; while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector<String> cellStoreVector = new Vector<String>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String cellvalue; if (myCell.getCellType() == 1) cellvalue = myCell.getStringCellValue(); else cellvalue = (int) myCell.getNumericCellValue() + ""; cellStoreVector.addElement(cellvalue); System.out.print(cellvalue + " "); } System.out.println(); if (firstrow != 0) { dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/database_lab1", "root", "password"); String insert = "INSERT INTO student VALUES (?,?,?,?,?,?);"; PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert); ps.setString(1, cellStoreVector.get(0)); ps.setString(2, cellStoreVector.get(1)); ps.setString(3, cellStoreVector.get(2)); ps.setString(4, cellStoreVector.get(3)); ps.setString(5, cellStoreVector.get(4)); ps.setString(6, cellStoreVector.get(5)); Boolean rs = ps.execute(); } firstrow++; } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } }
From source file:ddf.metrics.reporting.internal.rest.MetricsEndpointTest.java
License:Open Source License
@Test public void testGetMetricsReportAsXls() throws Exception { // Create RRD file that Metrics Endpoint will detect int dateOffset = 900; // 15 minutes in seconds createRrdFile(dateOffset, "uptime"); UriInfo uriInfo = createUriInfo();//from ww w .j a va 2 s . c om // Get the metrics data from the endpoint MetricsEndpoint endpoint = getEndpoint(); endpoint.setMetricsDir(TEST_DIR); Response response = endpoint.getMetricsReport("xls", null, null, Integer.toString(dateOffset), "minute", uriInfo); cleanupRrd(); MultivaluedMap<String, Object> headers = response.getHeaders(); assertTrue(headers.getFirst("Content-Disposition").toString().contains("attachment; filename=")); InputStream is = (InputStream) response.getEntity(); assertThat(is, not(nullValue())); HSSFWorkbook wb = new HSSFWorkbook(is); assertThat(wb.getNumberOfSheets(), equalTo(1)); HSSFSheet sheet = wb.getSheetAt(0); assertThat(sheet, not(nullValue())); }