List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:org.gaixie.micrite.car.service.impl.DealWithCar.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { getPartner1();/*from w ww. j a v a2s .c o m*/ getPartner2(); getPartner3(); getPartner4(); getPartner5(); getPartner6(); getPartner7(); // TODO Auto-generated method stub if (log.isInfoEnabled()) { if (src == null) log.info("upload file is null."); } HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); int colSize = row.getLastCellNum(); int rowSize = sheet.getLastRowNum(); if (log.isInfoEnabled()) log.info("this is colSize=" + rowSize); try { if (colSize == COLS_1_2) {// for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else if (colSize == COLS_3) {// paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save3(row); } } else if (colSize > COLS_3) {// if (sheet.getRow(sheet.getFirstRowNum()) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2).getStringCellValue() != null && PAISE_TITLE.equals(sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) .getStringCellValue().trim())) { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); if (row.getCell(PAISE_COL_3) != null && row.getCell(PAISE_COL_3).getStringCellValue() != null) { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast( row.getCell(PAISE_COL_3).getStringCellValue(), paiSe); } else { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); } save3(row); } } } else { log.info("does not deal with colum size=" + colSize); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); log.info(e); } return IDealWith.OK; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from w w w . j av a 2 s . c o m newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel.//from ww w . j av a 2 s .co m * * @param <T> the generic type * @param type the type * @param in the in * @return the list */ @SuppressWarnings("rawtypes") public <T> List<T> importExcel(Class<T> type, InputStream in) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } List<T> list = new ArrayList<T>(); Map<String, Method> mapping = new LinkedHashMap<String, Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { Method setMethod = ReflectionUtils.setValueMethod(field, type); mapping.put(column.value(), setMethod); if (column.converter() != TypeHandler.class) { converters.put(setMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(setMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } T temp = null; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ?Sheet List<Method> methods = new ArrayList<Method>(); for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // ? if (rowNum == 0) { for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { String title = hssfRow.getCell(cellNum).getStringCellValue(); Method me = mapping.get(title); if (me == null) { continue; } methods.add(me); } continue; } temp = type.newInstance(); for (int cellNum = 0; cellNum < methods.size(); cellNum++) { HSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } Method m = methods.get(cellNum); TypeHandler handler = converters.get(m.getName()); if (handler == null) { continue; } String value = xh.getStringCellValue(); if (StringUtils.isEmpty(value)) { continue; } Object val = null; try { val = handler.stringToType(value); } catch (Exception e) { throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1) + "" + (cellNum + 1) + "" + value + "??"); } methods.get(cellNum).invoke(temp, val); } list.add(temp); } } } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } return list; }
From source file:org.hsh.bfr.db.imports.GeneralXLSImporter.java
License:Open Source License
public String doImport(final String filename, final JProgressBar progress, final boolean showResults) { Runnable runnable = new Runnable() { public void run() { try { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Importiere Excel Datei..."); progress.setMinimum(0); }/*from w w w .ja v a 2 s . c om*/ InputStream is = null; System.out.println(filename); if (filename.startsWith("http://")) { URL url = new URL(filename); URLConnection uc = url.openConnection(); is = uc.getInputStream(); } else if (filename.startsWith("/org/hsh/bfr/db/res/")) { is = this.getClass().getResourceAsStream(filename); } else { is = new FileInputStream(filename); } POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet; HSSFRow row; int numSuccess = 0; int numFailed = 0; String unusedFields = ""; for (int i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); String tableName = sheet.getSheetName(); MyTable myT = DBKernel.myDBi.getTable(tableName); if (myT != null) { int numRows = sheet.getLastRowNum(); if (progress != null) { progress.setMaximum(numRows); progress.setValue(0); } row = sheet.getRow(0); String sql1 = ""; String sql2 = ""; String sql3 = ""; Vector<String> codeSql1 = new Vector<>(); Vector<String> codeSql2 = new Vector<>(); LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>(); int numCols = row.getLastCellNum(); String[] fieldNames = new String[numCols]; String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT); String[] ffieldTypes = new String[numCols]; MyTable[] myForeignTables = new MyTable[numCols]; String[] kzS = new String[numCols]; String[] dbFieldnames = new String[numCols]; int lfdCol = 0; Hashtable<String, String> dbFieldNames = new Hashtable<>(); for (int j = 0; j < numCols; j++) { String fieldName = row.getCell(j).getStringCellValue(); fieldNames[j] = fieldName; int ffe; String dbFieldName = getDBFieldName(fieldName, myT, takecareofID); if (dbFieldName != null) { String ft = getForeignTable(dbFieldName, myT); if (ft != null && ft.equals("DoubleKennzahlen")) { kzS[j] = getKZ(fieldName, dbFieldName); dbFieldnames[j] = dbFieldName; } else if (!dbFieldNames.containsKey(dbFieldName)) { dbFieldNames.put(dbFieldName, dbFieldName); sql1 += DBKernel.delimitL(dbFieldName) + ","; sql2 += "?,"; sql3 += DBKernel.delimitL(dbFieldName) + "=?,"; lfdCol++; } fieldTypes[j] = getType(dbFieldName, myT, takecareofID); } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) { if (!foreignTables.containsKey(myT.getForeignFields()[ffe])) foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>()); ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false); foreignTables.get(myT.getForeignFields()[ffe]).add(j); myForeignTables[j] = myT.getForeignFields()[ffe]; } else if (DBKernel.showHierarchic(tableName) && fieldName.toLowerCase().endsWith("-code")) { codeSql1.add(DBKernel.delimitL("CodeSystem") + "," + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis")); codeSql2.add("'" + fieldName.substring(0, fieldName.length() - "-code".length()) + "',?,?"); } else if (!fieldName.equalsIgnoreCase("id")) { unusedFields += "," + fieldName; } } if (sql1.length() > 0 && sql2.length() > 0) { String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " (" + sql1.substring(0, sql1.length() - 1) + ") VALUES (" + sql2.substring(0, sql2.length() - 1) + ")"; PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int idCol = lfdCol + 1; sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET " + sql3.substring(0, sql3.length() - 1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql); PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()]; boolean doCode[] = new boolean[codeSql1.size()]; int codesI; for (codesI = 0; codesI < codeSql1.size(); codesI++) { sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName)) + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI) + ")"; psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql); } LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>(); LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>(); for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) { Vector<Integer> vs = entry.getValue(); String ssql1 = "", ssql2 = "", ssql3 = ""; for (int ii = 0; ii < vs.size(); ii++) { ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]); ssql2 += ",?"; ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?"; } if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) { sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename()) + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1) + ")"; psForeign.put(entry.getKey(), DBKernel.getDBConnection() .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)); sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename()) + " SET " + ssql3.substring(1) + " WHERE " + DBKernel.delimitL("ID") + "=?"; psForeignUpdate.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql)); } } LinkedHashMap<Object, String> hashBL = null; Iterator<Row> rows = sheet.rowIterator(); int lfd = 0; while (rows.hasNext()) { row = (HSSFRow) rows.next(); boolean setID = false; Integer lastID = null; ps.clearParameters(); psUpdate.clearParameters(); for (codesI = 0; codesI < codeSql1.size(); codesI++) { psCodes[codesI].clearParameters(); doCode[codesI] = false; } LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>(); for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate.entrySet()) { MyTable myT1 = entry.getKey(); psForeign.get(myT1).clearParameters(); psForeignUpdate.get(myT1).clearParameters(); lfdColsForeign.put(myT1, 0); } if (row.getRowNum() > 0) { lfdCol = 0; codesI = 0; Object[] kzVal = new Object[numCols]; for (int j = 0; j < numCols; j++) { if (fieldTypes[j] != null) { /* * if (fieldNames[j].equals( * "Bundesland")) { hashBL = * DBKernel * .myDBi.getHashMap("County"); * } else { hashBL = null; } */ lfdCol++; if (fieldTypes[j].startsWith("VARCHAR(") || fieldTypes[j].startsWith("CHAR(") || fieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL); else if (fieldTypes[j].equals("BOOLEAN")) manageBoolean(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("INTEGER")) manageInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("BIGINT")) manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(ps, psUpdate, lfdCol, row.getCell(j)); else if (fieldTypes[j].equals("DOUBLE")) { if (kzS[j] != null) { lfdCol--; //System.err.println(dbFieldnames[j] + "\t" + kzS[j]); if (DBKernel.kzIsString(kzS[j])) kzVal[j] = manageString(null, null, lfdCol, row.getCell(j)); else if (DBKernel.kzIsBoolean(kzS[j])) kzVal[j] = manageBoolean(null, null, lfdCol, row.getCell(j)); else kzVal[j] = manageDouble(null, null, lfdCol, row.getCell(j)); } else { manageDouble(ps, psUpdate, lfdCol, row.getCell(j)); } } else System.err .println("Wasn hier los? Undefinierter Feldtyp???? ->\t" + fieldNames[j]); } else if (myForeignTables[j] != null && ffieldTypes[j] != null) { lfdColsForeign.put(myForeignTables[j], lfdColsForeign.get(myForeignTables[j]) + 1); if (ffieldTypes[j].startsWith("VARCHAR(") || ffieldTypes[j].startsWith("CHAR(") || ffieldTypes[j].startsWith("BLOB(") && !tableName.equals("DateiSpeicher")) manageString(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j), hashBL); else if (ffieldTypes[j].equals("BOOLEAN")) manageBoolean(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("INTEGER")) manageInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("BIGINT")) manageBigInteger(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (fieldTypes[j].equals("DATE")) manageDate(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); else if (ffieldTypes[j].equals("DOUBLE")) { manageDouble(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j)); } else System.err.println(fieldNames[j] + " Feldtype????"); } else if (fieldNames[j].equals("ID")) { lastID = manageInteger(null, null, 0, row.getCell(j)); if (lastID != null) { if (DBKernel.hasID(tableName, lastID.intValue())) { psUpdate.setInt(idCol, lastID.intValue()); setID = true; } } } else if (DBKernel.showHierarchic(tableName) && fieldNames[j].toLowerCase().endsWith("-code")) { String code = manageString(psCodes[codesI], null, 1, row.getCell(j)); if (code != null && code.length() > 0) doCode[codesI] = true; codesI++; } else { //System.out.println(fieldNames[j]); } } try { if (setID) { psUpdate.execute(); } else { if (ps.executeUpdate() > 0) {// execute() lastID = DBKernel.getLastInsertedID(ps); } else { System.err.println("W"); } } numSuccess++; if (lastID != null) { for (int j = 0; j < numCols; j++) { if (dbFieldnames[j] != null && kzVal[j] != null) { DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j], lastID, null, kzS[j], kzVal[j]); } } for (codesI = 0; codesI < codeSql1.size(); codesI++) { if (doCode[codesI]) { psCodes[codesI].setInt(2, lastID); try { psCodes[codesI].execute(); numSuccess++; } catch (SQLException e1) { numFailed++; System.err.println(psCodes[codesI]); } } } for (Map.Entry<MyTable, PreparedStatement> entry : psForeign .entrySet()) { MyTable myT1 = entry.getKey(); MyTable[] foreignTs = myT.getForeignFields(); for (int ii = 0; ii < foreignTs.length; ii++) { if (foreignTs[ii] != null && foreignTs[ii].equals(myT1)) { if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT int lID = DBKernel .getLastInsertedID(psForeign.get(myT1)); // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt DBKernel.sendRequest("UPDATE " + DBKernel.delimitL(tableName) + " SET " + DBKernel.delimitL(myT.getFieldNames()[ii]) + "=" + lID + " WHERE " + DBKernel.delimitL("ID") + "=" + lastID, false); } break; } } } /* * for (int j=0;j<numCols;j++) { * if (myForeignTables[j] != * null && ffieldTypes[j] != * null) { MyTable[] foreignTs = * myT.getForeignFields(); for * (int * ii=0;ii<foreignTs.length; * ii++) { if (foreignTs[ii] != * null && foreignTs[ii].equals( * myForeignTables[j])) { if * (psForeign * .get(myForeignTables * [j]).executeUpdate() > 0) { * // INSERT int lID = * DBKernel.getLastInsertedID * (psForeign * .get(myForeignTables[j])); * DBKernel * .sendRequest("UPDATE " + * DBKernel.delimitL(tableName) * + " SET " + * DBKernel.delimitL( * myT.getFieldNames()[ii]) + * "=" + lID + " WHERE " + * DBKernel.delimitL("ID") + "=" * + lastID, false); } break; } * } } } */ } } catch (Exception e1) { numFailed++; MyLogger.handleMessage(ps.toString()); MyLogger.handleException(e1); } } if (progress != null) { lfd++; progress.setValue(lfd); } } } myT.doMNs(); if (progress != null) { // Refreshen: MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable(); if (myDB.getActualTable() != null) { String actTablename = myDB.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myDB.setTable(myDB.getActualTable()); } } MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree(); if (myTR.getActualTable() != null) { String actTablename = myTR.getActualTable().getTablename(); if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) { myTR.setTable(myTR.getActualTable()); } } } } else { System.err.println(tableName + " nicht in DB???"); } } if (progress != null) { progress.setVisible(false); } String log = numSuccess + " erfolgreiche Importe.\n"; log += numFailed + " fehlgeschlagene Importe.\n"; if (unusedFields.length() > 0) log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n"; if (showResults) { InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null); ib.setVisible(true); } else { System.out.println("GeneralXLSImporter (" + filename + "):\n" + log); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); try { thread.join(); } catch (InterruptedException e) { MyLogger.handleException(e); } return ""; }
From source file:org.jxstar.report.util.XlsToHtml.java
public String parserXls(String fileName) throws ReportException { ///*from w w w.j a v a 2s.co m*/ HSSFWorkbook hssfWB = ReportXlsUtil.readWorkBook(fileName); if (hssfWB == null) {//"?{0}??" throw new ReportException(JsMessage.getValue("xlstohtml.hint01"), fileName); } HSSFSheet sheet = hssfWB.getSheetAt(0); //???0 int lastRowNum = sheet.getLastRowNum(); if (lastRowNum == 0) { _log.showDebug("xls file row num is 0!!"); return ""; } //?xls?tablehtml StringBuilder sbTable = new StringBuilder(); sbTable.append("<table id='" + PARSERTABLEID + "' class='xls_table' >\n"); //? List<Integer> lsemp = FactoryUtil.newList(); //?1 int hasnum = 0, tableColNum = 0; //??1IE1? //table-layout:fixed;??????1?? List<Integer> lswidth = FactoryUtil.newList(); //?? for (int i = 0; i <= lastRowNum; i++) { HSSFRow row = sheet.getRow(i); if (row == null) {//?? lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } //??? int lastCellNum = row.getLastCellNum(); //cells-1? if (lastCellNum <= 0) { lsemp.add(i); sbTable.append("<tr height='22px' >\n{EMPTY_LINE}</tr>\n"); continue; } else { //? if (hasnum == 0) tableColNum = lastCellNum + EMPTY_COLNUM; hasnum++; } //tr sbTable.append("<tr height='" + getHeightPixel(row.getHeightInPoints()) + "px' >\n"); //_log.showDebug("row=" + i + "; nums=" + cells); for (int j = 0; j < tableColNum; j++) { HSSFCell cell = null; if (j < lastCellNum) cell = row.getCell(j); //?ID String tdid = i + "," + j; // if (cell == null) { String ls = ""; if (hasnum == 1) { //?30px int width = (j < lastCellNum) ? 10 : 30; ls = " style='width:" + width + "px;'"; lswidth.add(width); } sbTable.append("\t<td id='" + tdid + "' class='xls_emp'" + ls + " > </td>\n"); continue; } //td? String style = getCellStyle(cell); //???cssName String cssName = getTdCss(style); //?td String value = getCellValue(cell); if (value == null || value.length() == 0) { value = " "; } else { value = value.replaceAll("\\s", " "); } //1????? if (hasnum == 1) { // int colw = getWidthPixel(sheet.getColumnWidth(j)); lswidth.add(colw); //td sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' style='width:" + colw + "px;' >" + value + "</td>\n"); } else { sbTable.append("\t<td id='" + tdid + "' class='" + cssName + "' >" + value + "</td>\n"); } } sbTable.append("</tr>\n"); } //_log.showDebug(sbTable.toString()); //?? for (int i = 1; i <= EMPTY_ROWNUM; i++) { lsemp.add(lastRowNum + i); //tr sbTable.append("<tr height='22px' >\n"); sbTable.append("{EMPTY_LINE}"); sbTable.append("</tr>\n"); } //cells-1? if (tableColNum > 0 && lsemp.size() > 0) { sbTable = fillEmptyLine(sbTable, tableColNum, lsemp, lswidth); lsemp.clear(); } sbTable.append("</table>\n"); //?html StringBuilder sbHtml = new StringBuilder(); sbHtml.append("<html>\n<body style='margin:1px;'>\n"); sbHtml.append(getCssStyle()); sbHtml.append(sbTable); //??? sbHtml.append(mergedRegion(sheet)); //html sbHtml.append("</body>\n</html>\n"); //_log.showDebug(sbHtml.toString()); return sbHtml.toString(); }
From source file:org.kitodo.production.plugin.importer.massimport.PicaMassImport.java
License:Open Source License
private List<Record> getRecordsForXLS(InputStream xls) throws IOException { List<Record> records = new ArrayList<>(); HSSFWorkbook wb = new HSSFWorkbook(xls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet // loop over all rows for (int j = 0; j <= sheet.getLastRowNum(); j++) { // loop over all cells HSSFRow row = sheet.getRow(j); if (Objects.nonNull(row)) { for (int i = 0; i < row.getLastCellNum(); i++) { HSSFCell cell = row.getCell(i); // changing all cell types to String if (Objects.nonNull(cell)) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); Record record = changeCellTypeToString(cell, i, j); if (Objects.nonNull(record)) { records.add(record); }//from www . j av a 2 s. c o m } } } } return records; }
From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java
License:Open Source License
@Override public int getNumberOfLastColumn(int row) { HSSFRow r = sheet.getRow(row); if (r != null) { return r.getLastCellNum() - 1; }//from www. java 2 s .c o m return 0; }
From source file:org.mili.core.text.MockFactory.java
License:Apache License
/** * Assert workbook.// w w w. ja v a2s .c o m * * @param workbook the workbook */ public static void assertWorkbook(HSSFWorkbook workbook) { HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row = sheet.getRow(0); assertEquals("unknown", workbook.getSheetName(0)); assertEquals(1, sheet.getLastRowNum()); assertEquals(11, row.getLastCellNum()); assertEquals("byte", row.getCell((short) 0).getStringCellValue()); assertEquals("short", row.getCell((short) 1).getStringCellValue()); assertEquals("integer", row.getCell((short) 2).getStringCellValue()); assertEquals("char", row.getCell((short) 3).getStringCellValue()); assertEquals("long", row.getCell((short) 4).getStringCellValue()); assertEquals("float", row.getCell((short) 5).getStringCellValue()); assertEquals("double", row.getCell((short) 6).getStringCellValue()); assertEquals("boolean", row.getCell((short) 7).getStringCellValue()); assertEquals("string", row.getCell((short) 8).getStringCellValue()); assertEquals("foo", row.getCell((short) 9).getStringCellValue()); assertEquals("date", row.getCell((short) 10).getStringCellValue()); assertEquals("null", row.getCell((short) 11).getStringCellValue()); row = sheet.getRow(1); // assertEquals("1", row.getCell((short) 0).getStringCellValue()); // assertEquals("1", row.getCell((short) 1).getStringCellValue()); // assertEquals("1", row.getCell((short) 2).getStringCellValue()); assertEquals("c", row.getCell((short) 3).getStringCellValue()); // assertEquals("1", row.getCell((short) 4).getStringCellValue()); assertEquals(1.0, row.getCell((short) 5).getNumericCellValue(), 0.0); // assertEquals(1.0, row.getCell((short) 6).getNumericCellValue(), 0.0); assertEquals("true", row.getCell((short) 7).getStringCellValue()); assertEquals("abbas", row.getCell((short) 8).getStringCellValue()); assertEquals("Foo", row.getCell((short) 9).getStringCellValue()); assertEquals("Thu Feb 01 00:00:00 CET 3900", row.getCell((short) 10).getStringCellValue()); assertEquals("", row.getCell((short) 11).getStringCellValue()); }
From source file:org.mili.core.text.MockFactory.java
License:Apache License
/** * Assert empty workbook./*w w w. j a va2s . c om*/ * * @param workbook the workbook */ public static void assertEmptyWorkbook(HSSFWorkbook workbook) { HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row = sheet.getRow(0); assertEquals("unknown", workbook.getSheetName(0)); assertEquals(0, sheet.getLastRowNum()); assertEquals(0, row.getLastCellNum()); assertEquals("Keine Daten vorhanden !", row.getCell((short) 0).getStringCellValue()); }
From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java
License:Apache License
public static ExcelMetadata instance(InputStream stream) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta);//from ww w . j av a 2 s . c om HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); // Empty rows are returned as null if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); // Undefined cells are returned as null if (cell == null) { continue; } /* * Builds a string of body content from all string, numeric, * and formula values in the body of each worksheet. * * This code currently duplicates the POI 3.1 ExcelExtractor behavior of * combining the body text from all worksheets into a single string. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { // Filter out row delimiter characters from comment String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }