List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:com.vodafone.poms.ii.helpers.SiteLoader.java
public static List<Sites> readFile(InputStream fis) { List<Sites> sites = new ArrayList<>(); Sites site = null;/*w w w.j ava 2 s . c o m*/ try { XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); int numberOfSheets = myWorkBook.getNumberOfSheets(); XSSFSheet sheet = null; System.out.println(numberOfSheets); for (int i = 0; i < numberOfSheets; i++) { sheet = myWorkBook.getSheetAt(i); if (sheet.getRow(0) != null && sheet.getRow(0).getCell(0) != null) { if (sheet.getRow(0).getCell(0).getStringCellValue().toLowerCase().contains("site")) { //<editor-fold defaultstate="collapsed" desc="Process Sheet"> Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { site = new Sites(); site.setSitePhysical(getCellValue(row.getCell(0))); site.setGfRt(getCellValue(row.getCell(1))); sites.add(site); } } //</editor-fold> } } } } catch (FileNotFoundException ex) { Logger.getLogger(SiteLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(SiteLoader.class.getName()).log(Level.SEVERE, null, ex); } return sites; }
From source file:com.xidu.framework.common.view.documenthandler.AbstractExcel2007DocumentHandler.java
License:Open Source License
/** * Convenient method to obtain the cell in the given sheet, row and column. * <p>Creates the row and the cell if they still doesn't already exist. * Thus, the column can be passed as an int, the method making the needed downcasts. * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0) * @param row thr row number/*from ww w .ja va 2s . c o m*/ * @param col the column number * @return the HSSFCell */ protected XSSFCell getCell(XSSFSheet sheet, int row, int col) { XSSFRow sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } XSSFCell cell = sheetRow.getCell(col); if (cell == null) { cell = sheetRow.createCell(col); } return cell; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.DdlReaderImpl.java
License:Apache License
/** * ?DBSheet// w ww. j a v a 2 s.c o m * @throws CodeGenException */ @Override protected DdlModel onReader(XSSFSheet sheet) throws CodeGenException { DdlModel model = super.onReader(sheet, DdlModel.class); XSSFRow row = null; // ?? row = sheet.getRow(1); model.setNameSpace(this.getCellStringValue(row.getCell(2))); model.setAuthor(this.getCellStringValue(row.getCell(4))); model.setVersion(this.getCellStringValue(row.getCell(6))); model.setDescription(this.getCellStringValue(row.getCell(8))); // model.setIndex(this.getCellStringValue(row.getCell(8))); // model.setForeign(this.getCellStringValue(row.getCell(12))); row = sheet.getRow(2); model.setName(this.getCellStringValue(row.getCell(2))); model.setResponsibility(this.getCellStringValue(row.getCell(4))); model.setRenewDate(this.getCellStringValue(row.getCell(6))); // ? model.setDetail(this.readerTable(sheet)); return model; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.DmlReaderImpl.java
License:Apache License
/** * ?DBSheet//from w w w. j a v a2 s . c o m * @throws CodeGenException */ @Override protected DmlModel onReader(XSSFSheet sheet) throws CodeGenException { DmlModel model = super.onReader(sheet, DmlModel.class); XSSFRow row = null; // ?? row = sheet.getRow(1); model.setNameSpace(this.getCellStringValue(row.getCell(2))); model.setAuthor(this.getCellStringValue(row.getCell(4))); model.setVersion(this.getCellStringValue(row.getCell(6))); model.setDescription(this.getCellStringValue(row.getCell(8))); row = sheet.getRow(2); model.setName(this.getCellStringValue(row.getCell(2))); model.setResponsibility(this.getCellStringValue(row.getCell(4))); model.setRenewDate(this.getCellStringValue(row.getCell(6))); // ? model.setDmlData(this.readerTable(sheet)); return model; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.HashMapReaderImpl.java
License:Apache License
/** * ?Sheet/* w ww .ja va 2s. c om*/ */ @Override protected HashMap<String, String> onReader(XSSFSheet sheet) { HashMap<String, String> result = new HashMap<String, String>(); for (int row = this.startRowNo; row < sheet.getLastRowNum(); row++) { XSSFRow xssfRow = sheet.getRow(row); String key = this.getCellStringValue(xssfRow.getCell(this.startColNo)); // key?Map if (xssfRow.getCell(this.startColNo).getCellType() == XSSFCell.CELL_TYPE_BLANK || StringUtility.isNullOrEmpty(key)) { continue; } String value = this.getCellStringValue(xssfRow.getCell(this.startColNo + 1)); result.put(key, value); } return result; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.TableReaderImpl.java
License:Apache License
/** * ?DBSheet/* w ww .ja va 2 s .c om*/ * @throws CodeGenException */ @Override protected TableModel onReader(XSSFSheet sheet) throws CodeGenException { TableModel model = super.onReader(sheet, TableModel.class); // KEY String[] keys = null; List<String> listKey = new ArrayList<String>(); Map<String, String> curValue = null; // ? for (int rowNo = this.startRowNo; rowNo <= sheet.getLastRowNum(); rowNo++) { XSSFRow row = sheet.getRow(rowNo); // KEY if (rowNo == this.startRowNo) { keys = new String[row.getLastCellNum()]; } else { curValue = new HashMap<String, String>(); } // ? ?? boolean allBlank = true; // ? if (row == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d)", sheet.getSheetName(), rowNo)); } for (int colNo = this.startColNo; colNo < row.getLastCellNum(); colNo++) { XSSFCell cell = row.getCell(colNo); if (cell == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d), col(%d)", sheet.getSheetName(), rowNo, colNo)); } if (cell.getCellType() != XSSFCell.CELL_TYPE_BLANK && cell.getCellType() != XSSFCell.CELL_TYPE_ERROR) { allBlank = false; } String value = this.getCellStringValue(cell); // KEY if (rowNo == this.startRowNo) { if (StringUtility.isNullOrEmpty(value) || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK || "-".equals(value)) { // / continue; } keys[colNo] = value; listKey.add(value); } else { if (colNo >= keys.length) continue; // String key = keys[colNo]; if (!StringUtility.isNullOrEmpty(key)) { curValue.put(keys[colNo], value); } } } // ? if (!allBlank && curValue != null) { model.insert(curValue); } } // model.setColumns((String[]) listKey.toArray(new String[0])); return model; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSXWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }/*from w ww.ja v a 2 s. c o m*/ for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSX(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }// ww w . j av a2 s.c o m for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<Map<String, String>> readXLSX(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; }//from w w w. j a va 2 s . c om for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); XSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; }
From source file:comparator.Comparator.java
public static void delta_MVC_MTC() throws IOException { //Get the input files //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx")); //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx")); FileInputStream mtcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mtcFile2 = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mvcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx")); //Prepare the output file //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8")); Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"), "UTF-8")); csvW.write('\ufeff'); csvW.write("Expand Project;"); csvW.write("\n\n"); //Get the workbook instance for XLS file XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile); XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2); XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile); //Output/* w w w . j ava 2s .c o m*/ csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;"); csvW.write("\n"); csvW.write("********************;"); csvW.write("\n"); csvW.write("Set name;"); csvW.write("\n"); csvW.write("MTC mismatches;List of the codes missing in MVC"); csvW.write("\n"); csvW.write("MVC mismatches;List of the codes missing in MTC"); csvW.write("\n"); csvW.write("********************;"); XSSFSheet mtcSheet; XSSFSheet mtcSheet2; Iterator<Row> mtcRowIterator; Iterator<Row> mtcRowIterator2; Iterator<Row> mvcRowIterator; Iterator<Cell> mtcCellIterator; Iterator<Cell> mvcCellIterator; int mtcCol; int mvcCol; boolean mtcColFound; boolean mvcColFound; ArrayList mtcCodes; ArrayList mvcCodes; ArrayList mtcEnglishNames; ArrayList mvcEnglishNames; ArrayList englishNamesdifferences; Row mtcRow; Row mtcRow2; Row mvcRow; Row mvcRow2; Row newRow; Cell newCell; CellStyle myStyle; String mtcSplit[]; String mvcSplit[]; String mtcSheetName; String mvcSheetName; //Get the sheet from the MTC workbook for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) { mtcSheet = mtcWorkbook.getSheetAt(i); mtcSheet2 = mtcWorkbook2.getSheetAt(i); //Get iterator to all the rows in current MTC sheet mtcRowIterator = mtcSheet.iterator(); mtcRowIterator2 = mtcSheet2.iterator(); //Get the sheet from the MVC workbook for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) { XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j); //Get iterator to all the rows in current MVC sheet mvcRowIterator = mvcSheet.iterator(); //Get the name of MTC sheet and MVC sheet, compare them if they contain data //MTC data files are called "VSX_sheetName" //MVC data files are called "epSOSsheetName" mtcSplit = mtcSheet.getSheetName().split("_"); mvcSplit = mvcSheet.getSheetName().split("SOS"); mtcSheetName = mtcSplit[mtcSplit.length - 1]; mvcSheetName = mvcSplit[mvcSplit.length - 1]; //And process the file matching or throw out the file that has no equivalent if (mtcSheetName.equals(mvcSheetName)) { mtcCol = 0; mvcCol = 0; mtcColFound = false; mvcColFound = false; mtcCodes = new ArrayList(); mvcCodes = new ArrayList(); mtcEnglishNames = new ArrayList(); mvcEnglishNames = new ArrayList(); englishNamesdifferences = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (mtcRowIterator.hasNext()) { mtcRow = mtcRowIterator.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim()); mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()); } } //In MVC while (mvcRowIterator.hasNext()) { mvcRow = mvcRowIterator.next(); mvcRow2 = mvcRow; if (mvcColFound == false) { mvcCellIterator = mvcRow.cellIterator(); while (mvcCellIterator.hasNext()) { Cell mvcCell = mvcCellIterator.next(); if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code") || mvcCell.getStringCellValue().equals("Code"))) { mvcCol = mvcCell.getColumnIndex(); mvcColFound = true; break; } } } else { mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim()); mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim()); } } //Processing colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences); //Output //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {} csvW.write("\n\n"); csvW.write(mtcSheetName + ";"); csvW.write("\n"); csvW.write("MTC mismatches;"); for (int a = 0; a < mtcCodes.size(); a++) { csvW.write(mtcCodes.get(a) + ";"); } csvW.write("\n"); csvW.write("MVC mismatches\n"); for (int b = 0; b < mvcCodes.size(); b++) { csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n"); } csvW.write("english names differences\n"); if (!englishNamesdifferences.isEmpty()) { csvW.write("code;MTC 2.0;MVC 2.0.1\n"); for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1) + ";" + englishNamesdifferences.get(c + 2) + "\n"); } } /* work on currents MTC2.0 sheet */ mtcColFound = false; mtcCol = 0; List<Integer> delRows = new ArrayList(); //recreate iterator to all the rows in current MTC sheet while (mtcRowIterator2.hasNext()) { mtcRow = mtcRowIterator2.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); for (int a = 0; a < mtcCodes.size(); a++) { if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) { // delete row corresponding to useless code delRows.add(mtcRow.getRowNum()); break; } } if (!englishNamesdifferences.isEmpty()) { for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim() .equals(englishNamesdifferences.get(c + 1))) { mtcRow2.getCell(mtcCol + 1) .setCellValue(englishNamesdifferences.get(c + 2).toString()); break; } } } } } for (int d = delRows.size() - 1; d >= 0; d--) { mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1); } myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle(); for (int b = 0; b < mvcCodes.size(); b++) { newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1); for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) { newCell = newRow.createCell(bb); newCell.setCellStyle(myStyle); if (bb == mtcCol) { newCell.setCellValue(mvcCodes.get(b).toString()); } else if (bb == mtcCol + 1) { newCell.setCellValue(mvcEnglishNames.get(b).toString()); } } } } } } //close InputStream mtcFile.close(); mtcFile2.close(); mvcFile.close(); //close OutputStream csvW.close(); //Open FileOutputStream to write updates FileOutputStream output_file = new FileOutputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx")); //write changes mtcWorkbook2.write(output_file); //close the stream output_file.close(); }