/** * Fired when the document is written to an output stream. * * @see org.apache.poi.xssf.usermodel.XSSFSheet#write(java.io.OutputStream) () */ protected void onDocumentWrite() { // check if cells in the CTRow are ordered boolean isOrdered = true; if (_row.sizeOfCArray() != _cells.size()) isOrdered = false; else { int i = 0; CTCell[] xcell = _row.getCArray(); for (XSSFCell cell : _cells.values()) { CTCell c1 = cell.getCTCell(); CTCell c2 = xcell[i++]; String r1 = c1.getR(); String r2 = c2.getR(); if (!(r1 == null ? r2 == null : r1.equals(r2))) { isOrdered = false; break; } } } if (!isOrdered) { CTCell[] cArray = new CTCell[_cells.size()]; int i = 0; for (XSSFCell c : _cells.values()) { cArray[i++] = c.getCTCell(); } _row.setCArray(cArray); } }
/** * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する * * @param workbook ワークブック * @param sheet シート * @param nRow 行 * @param nColumn 列 * @param isBold 太字フラグ * @param fontSize 文字サイズ * @param fontHeight 行高 */ public static void setCellStyleForLabel( XSSFWorkbook workbook, XSSFSheet sheet, int nRow, int nColumn, boolean isBold, short fontSize, float fontHeight) { assert sheet != null; // style設定 XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); if (isBold) { font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字 } font.setFontHeightInPoints((short) fontSize); // 文字サイズ font.setFontName(DEFAULT_FONT_NAME); style.setFont(font); // 文字太字 と 文字サイズ style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準 style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め style.setWrapText(true); // 折り返して全体を表示する // セルに罫線を描画 XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellStyle(style); row.setHeightInPoints(fontHeight); // 行高設定 }
public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx"); wb.write(fileOut); fileOut.close(); }
/** * 指定行を0で埋める * * @param sheet 編集対象シート * @param nRow 行番号 * @param nStartColumn 開始列番号 * @param nEndColumn 終了列番号 */ public static void setZero(XSSFSheet sheet, int nRow, int nStartColumn, int nEndColumn) { XSSFRow row = getRowAnyway(sheet, nRow); for (int nIndex = nStartColumn; nIndex <= nEndColumn; nIndex++) { XSSFCell cell = getCellAnyway(row, nIndex); cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((double) 0); } }
/** * 全てのシートの先頭セルにフォーカスをあてる * * @param workbook ワークブック */ public static void setFocusFistCell(XSSFWorkbook workbook) { assert workbook != null; for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) { XSSFSheet sheet = workbook.getSheetAt(nIndex); XSSFCell cell = getFirstCell(sheet); assert cell != null; cell.setAsActiveCell(); } }
/** * Construct a XSSFRow. * * @param row the xml bean containing all cell definitions for this row. * @param sheet the parent sheet. */ protected XSSFRow(CTRow row, XSSFSheet sheet) { _row = row; _sheet = sheet; _cells = new TreeMap<Integer, XSSFCell>(); for (CTCell c : row.getCArray()) { XSSFCell cell = new XSSFCell(this, c); _cells.put(cell.getColumnIndex(), cell); sheet.onReadCell(cell); } }
/** * セルの縮小して全体を表示する * * @param nRow 行データ * @return 有効列数 */ public static void setShrinkToFitForCell(XSSFWorkbook wb, XSSFSheet sheet, int nRow, int nCol) { XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow); XSSFCell cell = OoxmlUtil.getCellAnyway(row, nCol); CellStyle styletmp = cell.getCellStyle(); CellStyle newStyletmp = wb.createCellStyle(); newStyletmp.cloneStyleFrom(styletmp); newStyletmp.setWrapText(false); // 折り返して全体を表示する newStyletmp.setShrinkToFit(true); // 縮小して全体を表示する cell.setCellStyle(newStyletmp); }
/** * Remove the Cell from this row. * * @param cell the cell to remove */ public void removeCell(Cell cell) { if (cell.getRow() != this) { throw new IllegalArgumentException("Specified cell does not belong to this row"); } XSSFCell xcell = (XSSFCell) cell; if (xcell.isPartOfArrayFormulaGroup()) { xcell.notifyArrayFormulaChanging(); } _cells.remove(cell.getColumnIndex()); }
/** * セルの値を取得する * * @param row 行データ * @param nColumn 列番号 * @return セルの値 */ public static Object getData(XSSFRow row, int nColumn) { if (row != null) { XSSFCell cell = row.getCell(nColumn); if (cell != null) { if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { return cell.getNumericCellValue(); } else if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) { return cell.getStringCellValue(); } } } return null; }
/** * 列方向のセルの値を合算する * * @param sheet 編集対象シート * @param nColumn 行番号 * @param nStartRow 開始列番号 * @param nEndRow 終了列番号 * @return 合算値 */ public static int sumColumn(XSSFSheet sheet, int nColumn, int nStartRow, int nEndRow) { int sum = 0; for (int nIndex = nStartRow; nIndex <= nEndRow; nIndex++) { XSSFRow row = sheet.getRow(nIndex); assert row != null; XSSFCell cell = row.getCell(nColumn); assert cell != null; if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { sum += cell.getNumericCellValue(); } } return sum; }
/** * 罫線スタイルの<b>CellStyle</b>を生成 セル結合 * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param style 罫線style */ public static void setMerger( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, XSSFCellStyle style) { assert sheet != null; sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd)); XSSFRow row = getRowAnyway(sheet, nRowStart); XSSFCell cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(style); }
/** * 指定範囲にセルを新規作成 * * @param sheet シート * @param startRow 開始行番号 * @param endRow 終了行番号 * @param startColumn 開始列番号 * @param endColumn 終了列番号 * @param style セルスタイル */ public static void setStyle( XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn, XSSFCellStyle style) { for (int nRow = startRow; nRow <= endRow; nRow++) { XSSFRow row = getRowAnyway(sheet, nRow); for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) { XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellStyle(style); } } }
/** * 上線は太線のセル行を探す * * @param nRow 行データ * @return 有効列数 */ public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) { int nRowIndex = nRow; for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) { XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow); if (row != null) { XSSFCell cell = row.getCell(0); XSSFCellStyle styletmp = cell.getCellStyle(); short borderTopnum = styletmp.getBorderTop(); short borderBold = XSSFCellStyle.BORDER_MEDIUM; if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) { break; } } } return nRowIndex; }
/** * Use this to create new cells within the row and return it. * * @param columnIndex - the column number this cell represents * @param type - the cell's data type * @return XSSFCell a high level representation of the created cell. * @throws IllegalArgumentException if the specified cell type is invalid, columnIndex < 0 or * greater than 16384, the maximum number of columns supported by the SpreadsheetML format * (.xlsx) * @see Cell#CELL_TYPE_BLANK * @see Cell#CELL_TYPE_BOOLEAN * @see Cell#CELL_TYPE_ERROR * @see Cell#CELL_TYPE_FORMULA * @see Cell#CELL_TYPE_NUMERIC * @see Cell#CELL_TYPE_STRING */ public XSSFCell createCell(int columnIndex, int type) { CTCell ctCell; XSSFCell prev = _cells.get(columnIndex); if (prev != null) { ctCell = prev.getCTCell(); ctCell.set(CTCell.Factory.newInstance()); } else { ctCell = _row.addNewC(); } XSSFCell xcell = new XSSFCell(this, ctCell); xcell.setCellNum(columnIndex); if (type != Cell.CELL_TYPE_BLANK) { xcell.setCellType(type); } _cells.put(columnIndex, xcell); return xcell; }
/** * ハイパーリンクの設定 * * @param sheet シート * @param nRow 対象行番号 * @param nColumn 対象列番号 * @param value ハイパーリンクテキスト * @param url ハイパーリンク先URL */ public static void setHyperLink( XSSFSheet sheet, int nRow, int nColumn, String value, String url) { assert sheet != null; XSSFWorkbook workbook = sheet.getWorkbook(); CreationHelper helper = workbook.getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL); hyperlink.setAddress(url); XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellValue(value); cell.setHyperlink(hyperlink); // ハイパーリンクテキストの装飾 XSSFFont font = workbook.createFont(); XSSFCellStyle style = workbook.createCellStyle(); // font.setColor(new XSSFColor(new Color(0, 0, 255))); font.setUnderline(XSSFFont.U_SINGLE); style.setFont(font); cell.setCellStyle(style); }
/** * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param isBorder 罫線描画フラグ * @param style 罫線style */ public static void setRowDataCellStyle( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, boolean isBorder, XSSFCellStyle style) { assert sheet != null; // Range内のすべてセルに罫線を描画 for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) { XSSFRow row = getRowAnyway(sheet, rIndex); for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) { XSSFCell cell = getCellAnyway(row, cIndex); cell.setCellStyle(style); } } }
/** * Returns the cell at the given (0 based) index, with the specified {@link * org.apache.poi.ss.usermodel.Row.MissingCellPolicy} * * @return the cell at the given (0 based) index * @throws IllegalArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid * @see Row#RETURN_NULL_AND_BLANK * @see Row#RETURN_BLANK_AS_NULL * @see Row#CREATE_NULL_AS_BLANK */ public XSSFCell getCell(int cellnum, MissingCellPolicy policy) { if (cellnum < 0) throw new IllegalArgumentException("Cell index must be >= 0"); XSSFCell cell = (XSSFCell) _cells.get(cellnum); if (policy == RETURN_NULL_AND_BLANK) { return cell; } if (policy == RETURN_BLANK_AS_NULL) { if (cell == null) return cell; if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return null; } return cell; } if (policy == CREATE_NULL_AS_BLANK) { if (cell == null) { return createCell((short) cellnum, Cell.CELL_TYPE_BLANK); } return cell; } throw new IllegalArgumentException("Illegal policy " + policy + " (" + policy.id + ")"); }
/** * update cell references when shifting rows * * @param n the number of rows to move */ protected void shift(int n) { int rownum = getRowNum() + n; CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain(); int sheetId = (int) _sheet.sheet.getSheetId(); String msg = "Row[rownum=" + getRowNum() + "] contains cell(s) included in a multi-cell array formula. " + "You cannot change part of an array."; for (Cell c : this) { XSSFCell cell = (XSSFCell) c; if (cell.isPartOfArrayFormulaGroup()) { cell.notifyArrayFormulaChanging(msg); } // remove the reference in the calculation chain if (calcChain != null) calcChain.removeItem(sheetId, cell.getReference()); CTCell ctCell = cell.getCTCell(); String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString(); ctCell.setR(r); } setRowNum(rownum); }
/** * Process the specified HTTP request, and create the corresponding HTTP response (or forward to * another web component that will create it). Return an <code>ActionForward</code> instance * describing where and how control should be forwarded, or <code>null</code> if the response has * already been completed. * * @param mapping The ActionMapping used to select this instance * @param form The optional ActionForm bean for this request (if any) * @param request The HTTP request we are processing * @param response The HTTP response we are creating * @exception Exception if business logic throws an exception */ public ActionForward execute( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { // Extract attributes we will need MessageResources messages = getResources(request); // save errors ActionMessages errors = new ActionMessages(); // START check for login (security) if (!SecurityService.getInstance().checkForLogin(request.getSession(false))) { return (mapping.findForward("welcome")); } // END check for login (security) // START get id of current quote from either request, attribute, or cookie // id of quote from request String projectId = null; projectId = request.getParameter("projectViewId"); // check attribute in request if (projectId == null) { projectId = (String) request.getAttribute("projectViewId"); } // id of quote from cookie if (projectId == null) { projectId = StandardCode.getInstance().getCookie("projectViewId", request.getCookies()); } // default client to first if not in request or cookie if (projectId == null) { List results = QuoteService.getInstance().getQuoteList(); Quote1 first = (Quote1) results.get(0); projectId = String.valueOf(first.getQuote1Id()); } // projectId="6553"; Integer id = Integer.valueOf(projectId); String[] dataValue = new String[11]; Project p = ProjectService.getInstance().getSingleProject(Integer.parseInt(projectId)); // END get id of current quote from either request, attribute, or cookie // get quote to add files to // Quote1 q = QuoteService.getInstance().getSingleQuote(p.getQuotes()); Set sources = p.getSourceDocs(); // get the lin task to update // String linTaskId = StandardCode.getInstance().getCookie("quoteViewGeneralTradosUploadId", // request.getCookies()); System.out.println(sources.size()); File folder = new File("C:/log"); File[] listOfFiles = folder.listFiles(); for (int ij = 0; ij < listOfFiles.length; ij++) { if (listOfFiles[ij].isFile() && (listOfFiles[ij].getName().endsWith(".log")) || listOfFiles[ij].getName().endsWith(".xls") || listOfFiles[ij].getName().endsWith(".xlsx") || listOfFiles[ij].getName().endsWith(".xml")) { String lang = ""; System.out.println("File " + listOfFiles[ij].getName()); String myFile = listOfFiles[ij].getName(); Integer leng = myFile.length(); if (listOfFiles[ij].getName().endsWith(".log")) { lang = (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4)); } else if (listOfFiles[ij].getName().endsWith(".xlsx")) { lang = (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 7, leng - 5)); } else { lang = (String) LanguageAbs.getInstance().getAbs().get(myFile.substring(leng - 6, leng - 4)); } // List sourceLang = QuoteService.getInstance().getSourceLang1(q); for (Iterator sourceIter = sources.iterator(); sourceIter.hasNext(); ) { SourceDoc sd = (SourceDoc) sourceIter.next(); List targetLang = QuoteService.getInstance().getTargetLang(sd.getSourceDocId()); for (Iterator linTargetIter = sd.getTargetDocs().iterator(); linTargetIter.hasNext(); ) { TargetDoc td = (TargetDoc) linTargetIter.next(); if (td.getLanguage().equalsIgnoreCase(lang)) { List linTasklist = QuoteService.getInstance().getLinTask(td.getTargetDocId()); for (int k = 0; k < linTasklist.size(); k++) { LinTask lt = (LinTask) linTasklist.get(k); // get input stream // InputStream in = listOfFiles[ij].getInputStream(); if (listOfFiles[ij].getName().endsWith(".log")) { FileInputStream in = new FileInputStream(listOfFiles[ij]); // byte[] fileData = listOfFiles[ij].getFileData(); //byte array of entire file long length = listOfFiles[ij].length(); byte[] fileData = new byte[(int) length]; in.read(fileData); // read data into fileData String entireRead = new String(fileData); // the entire file as a string String[] lines = entireRead.split("\n"); // lines within the file String line = new String(""); // each line // scroll to totals int j = 0; // line numbers try { while (true) { line = lines[j++]; if (line != null && line.length() > 12 && line.substring(0, 13).equals("Analyse Total")) { break; } } // move to repetitions line j++; j++; j++; String[] parts; // each number per line String wordRep = null; String word100 = null; String word95 = null; String word85 = null; String word75 = null; String word50 = null; String wordNo = null; String wordPerfect = null; String wordContext = null; String wordTotal = null; // wordRep line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 3) { // if at the words column wordRep = parts[i]; break; } } // word100 line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 3) { // if at the words column word100 = parts[i]; break; } } // word95 line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 5) { // if at the words column word95 = parts[i]; break; } } // word85 line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 5) { // if at the words column word85 = parts[i]; break; } } // word75 line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 5) { // if at the words column word75 = parts[i]; break; } } // word50 line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 5) { // if at the words column word50 = parts[i]; break; } } // wordNo line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 4) { // if at the words column wordNo = parts[i]; break; } } // wordTotal line = lines[j++]; parts = line.split(" "); for (int i = 0, counter = 0; i < parts.length; i++) { if (parts[i].length() > 0) { // look for non-blank items and count them counter++; } if (counter == 3) { // if at the words column wordTotal = parts[i]; break; } } // END process the trados .log file // remove commas from trados values wordRep = wordRep.replaceAll(",", ""); word100 = word100.replaceAll(",", ""); word95 = word95.replaceAll(",", ""); word85 = word85.replaceAll(",", ""); word75 = word75.replaceAll(",", ""); word50 = word50.replaceAll(",", ""); wordNo = wordNo.replaceAll(",", ""); // wordPerfect=null; // wordContext=null; wordTotal = wordTotal.replaceAll(",", ""); // convert trados values from strings to numbers Integer numRep = Integer.valueOf(wordRep); Integer num100 = Integer.valueOf(word100); Integer num95 = Integer.valueOf(word95); Integer num85 = Integer.valueOf(word85); Integer num75 = Integer.valueOf(word75); Integer num50 = Integer.valueOf(word50); Integer numNo = Integer.valueOf(wordNo); Double numTotal = Double.valueOf(wordTotal); // find totals to save to lin task int numNew = num50.intValue() + numNo.intValue(); int num8599 = num95.intValue() + num85.intValue(); int numNew4 = num75.intValue() + numNew; if (lt.getTaskName().equalsIgnoreCase("Translation")) { // set new trados values for the lin task lt.setWordRep(numRep); lt.setWord100(num100); lt.setWord95(num95); lt.setWord85(num85); lt.setWord75(num75); lt.setWordNew(new Integer(numNew)); lt.setWord8599(new Integer(num8599)); lt.setWordNew4(new Double(numNew4)); lt.setWordTotal(numTotal); } else if (lt.getTaskName().equalsIgnoreCase("editing")) { lt.setWordNew4(numTotal); lt.setWordTotal(numTotal); } // upload the new trados values to db ProjectService.getInstance().updateLinTask(lt); // START get file list // get input stream in.close(); in = new FileInputStream(listOfFiles[ij]); length = listOfFiles[ij].length(); fileData = new byte[(int) length]; in.read(fileData); // read data into fileData entireRead = new String(fileData); // the entire file as a string lines = entireRead.split("\n"); // lines within the file line = new String(""); // each line } catch (Exception e) { } in.close(); } else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xls")) { POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream("C:/log/" + listOfFiles[ij].getName())); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int count = 0, i = 0; String flag = "true"; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); count = 0; Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); count++; try { if (count == 4 && flag.equalsIgnoreCase("true")) { dataValue[i++] = cell.toString(); System.out.println("cel value----------> " + cell.toString()); if (i > 10) { flag = "false"; } } } catch (Exception e) { System.out.println("Integer Value" + count++); } } } Integer numRep = Math.round(Float.parseFloat(dataValue[2])); Integer num100 = Math.round(Float.parseFloat(dataValue[4])); Integer num95 = Math.round(Float.parseFloat(dataValue[5])); Integer num85 = Math.round(Float.parseFloat(dataValue[6])); Integer num75 = Math.round(Float.parseFloat(dataValue[7])); Integer num50 = Math.round(Float.parseFloat(dataValue[8])); Integer numNo = Math.round(Float.parseFloat(dataValue[9])); Integer numPerfect = Math.round(Float.parseFloat(dataValue[1])); Integer numContext = Math.round(Float.parseFloat(dataValue[3])); Double numTotal = Double.valueOf(dataValue[10]); // numRep = Integer.parseInt(dataValue[1]); int numNew = num50.intValue() + numNo.intValue(); int num8599 = num95.intValue() + num85.intValue(); int numNew4 = num75.intValue() + numNew; if (lt.getTaskName().equalsIgnoreCase("Translation")) { // set new trados values for the lin task lt.setWordRep(numRep); lt.setWord100(num100); lt.setWord95(num95); lt.setWord85(num85); lt.setWord75(num75); lt.setWordNew(new Integer(numNew)); lt.setWord8599(new Integer(num8599)); lt.setWordNew4(new Double(numNew4)); lt.setWordContext(numContext); lt.setWordPerfect(numPerfect); lt.setWordTotal(numTotal); } else if (lt.getTaskName().equalsIgnoreCase("editing")) { lt.setWordNew4(numTotal); lt.setWordTotal(numTotal); } // upload the new trados values to db ProjectService.getInstance().updateLinTask(lt); } else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xlsx")) { // POIFSFileSystem fs = new // POIFSFileSystem(new FileInputStream("C:/log/" + listOfFiles[ij].getName())); // File file = new File("C:/log/" + // listOfFiles[ij].getName()); // OPCPackage pkg = OPCPackage.open(new // FileInputStream(file.getAbsolutePath())); // XSSFWorkbook wb = new XSSFWorkbook(pkg); InputStream fs = new FileInputStream("C:/log/" + listOfFiles[ij].getName()); XSSFWorkbook wb = new XSSFWorkbook(fs); // XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; int count = 0, i = 0; String flag = "true"; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); count = 0; Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); count++; try { if (count == 4 && flag.equalsIgnoreCase("true")) { dataValue[i++] = cell.toString(); System.out.println("cel value----------> " + cell.toString()); if (i > 10) { flag = "false"; } } } catch (Exception e) { System.out.println("Integer Value" + count++); } } } Integer numRep = Math.round(Float.parseFloat(dataValue[2])); Integer num100 = Math.round(Float.parseFloat(dataValue[4])); Integer num95 = Math.round(Float.parseFloat(dataValue[5])); Integer num85 = Math.round(Float.parseFloat(dataValue[6])); Integer num75 = Math.round(Float.parseFloat(dataValue[7])); Integer num50 = Math.round(Float.parseFloat(dataValue[8])); Integer numNo = Math.round(Float.parseFloat(dataValue[9])); Double numTotal = Double.valueOf(dataValue[10]); // numRep = Integer.parseInt(dataValue[1]); int numNew = num50.intValue() + numNo.intValue(); int num8599 = num95.intValue() + num85.intValue(); int numNew4 = num75.intValue() + numNew; if (lt.getTaskName().equalsIgnoreCase("Translation")) { // set new trados values for the lin task lt.setWordRep(numRep); lt.setWord100(num100); lt.setWord95(num95); lt.setWord85(num85); lt.setWord75(num75); lt.setWordNew(new Integer(numNew)); lt.setWord8599(new Integer(num8599)); lt.setWordNew4(new Double(numNew4)); lt.setWordTotal(numTotal); } else if (lt.getTaskName().equalsIgnoreCase("editing")) { lt.setWordNew4(numTotal); lt.setWordTotal(numTotal); } // upload the new trados values to db ProjectService.getInstance().updateLinTask(lt); } else if (listOfFiles[ij].isFile() && listOfFiles[ij].getName().endsWith(".xml")) { InputStream in = new FileInputStream("C:/log/" + listOfFiles[ij].getName()); System.setProperty( "javax.xml.parsers.DocumentBuilderFactory", "com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl"); DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder dBuilder = dbFactory.newDocumentBuilder(); Document doc = dBuilder.parse(in); doc.getDocumentElement().normalize(); System.out.println("Root element :" + doc.getDocumentElement().getNodeName()); Integer numRep = 0; Integer num100 = 0; Integer num95 = 0; Integer num85 = 0; Integer num75 = 0; Integer num50 = 0; Integer numNo = 0; Integer numTotal = 0; Integer numContext = 0; Integer numPerfect = 0; NodeList batchTotal = doc.getElementsByTagName("batchTotal"); // Element eElement = (Element) batchTotal; // NodeList analyse = // eElement.getElementsByTagName("analyse"); if (batchTotal != null && batchTotal.getLength() > 0) { Node node = batchTotal.item(0); if (node.getNodeType() == Node.ELEMENT_NODE) { Element eElement = (Element) node; NodeList analyse = eElement.getElementsByTagName("analyse"); if (analyse != null && analyse.getLength() > 0) { Node node1 = analyse.item(0); if (node1.getNodeType() == Node.ELEMENT_NODE) { Element eElement1 = (Element) node1; // NodeList analyse1 = // eElement.getElementsByTagName("analyse"); eElement1.getElementsByTagName("fuzzy").item(0).getTextContent(); NodeList fuzzy = doc.getElementsByTagName("fuzzy"); for (int temp = 0; temp < fuzzy.getLength(); temp++) { Node nNode = fuzzy.item(temp); System.out.println("\nCurrent Element :" + nNode.getNodeName()); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; if (eElement2.getAttribute("min").equalsIgnoreCase("50") && eElement2.getAttribute("max").equalsIgnoreCase("74")) { num50 = Integer.parseInt(eElement2.getAttribute("words")); } if (eElement2.getAttribute("min").equalsIgnoreCase("75") && eElement2.getAttribute("max").equalsIgnoreCase("84")) { num75 = Integer.parseInt(eElement2.getAttribute("words")); } if (eElement2.getAttribute("min").equalsIgnoreCase("85") && eElement2.getAttribute("max").equalsIgnoreCase("94")) { num85 = Integer.parseInt(eElement2.getAttribute("words")); } if (eElement2.getAttribute("min").equalsIgnoreCase("95") && eElement2.getAttribute("max").equalsIgnoreCase("99")) { num95 = Integer.parseInt(eElement2.getAttribute("words")); } } } // eElement1.getElementsByTagName("new").item(0).getTextContent(); NodeList new1 = doc.getElementsByTagName("new"); for (int temp = 0; temp < new1.getLength(); temp++) { Node nNode = new1.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numNo = Integer.parseInt(eElement2.getAttribute("words")); } } eElement1.getElementsByTagName("total").item(0).getTextContent(); NodeList total = doc.getElementsByTagName("total"); for (int temp = 0; temp < total.getLength(); temp++) { Node nNode = total.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numTotal = Integer.parseInt(eElement2.getAttribute("words")); } } eElement1.getElementsByTagName("exact").item(0).getTextContent(); NodeList exact = doc.getElementsByTagName("exact"); for (int temp = 0; temp < exact.getLength(); temp++) { Node nNode = exact.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; num100 = Integer.parseInt(eElement2.getAttribute("words")); } } eElement1.getElementsByTagName("perfect").item(0).getTextContent(); NodeList perfect = doc.getElementsByTagName("perfect"); for (int temp = 0; temp < perfect.getLength(); temp++) { Node nNode = perfect.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numPerfect = Integer.parseInt(eElement2.getAttribute("words")); } } eElement1.getElementsByTagName("repeated").item(0).getTextContent(); NodeList repeated = doc.getElementsByTagName("repeated"); for (int temp = 0; temp < repeated.getLength(); temp++) { Node nNode = repeated.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numRep = Integer.parseInt(eElement2.getAttribute("words")); } } try { eElement1 .getElementsByTagName("crossFileRepeated") .item(0) .getTextContent(); NodeList crossFileRepeated = doc.getElementsByTagName("crossFileRepeated"); for (int temp = 0; temp < crossFileRepeated.getLength(); temp++) { Node nNode = crossFileRepeated.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numRep += Integer.parseInt(eElement2.getAttribute("words")); } } } catch (Exception e) { } eElement1.getElementsByTagName("inContextExact").item(0).getTextContent(); NodeList inContextExact = doc.getElementsByTagName("inContextExact"); for (int temp = 0; temp < inContextExact.getLength(); temp++) { Node nNode = inContextExact.item(temp); if (nNode.getNodeType() == Node.ELEMENT_NODE) { Element eElement2 = (Element) nNode; numContext = Integer.parseInt(eElement2.getAttribute("words")); } } } } } } int numNew = num50.intValue() + numNo.intValue(); int num8599 = num95.intValue() + num85.intValue(); int numNew4 = num75.intValue() + numNew; if (lt.getTaskName().equalsIgnoreCase("Translation")) { // set new trados values for the lin task lt.setWordRep(numRep); lt.setWord100(num100); lt.setWord95(num95); lt.setWord85(num85); lt.setWord75(num75); lt.setWordNew(new Integer(numNew)); lt.setWord8599(new Integer(num8599)); lt.setWordNew4(new Double(numNew4)); lt.setWordTotal(new Double(numTotal)); lt.setWordContext(numContext); lt.setWordPerfect(numPerfect); } else if (lt.getTaskName().equalsIgnoreCase("editing")) { lt.setWordNew(new Integer(numTotal)); lt.setWordNew4(new Double(numTotal)); lt.setWordTotal(new Double(numTotal)); } // upload the new trados values to db ProjectService.getInstance().updateLinTask(lt); } else { System.out.println("no Match"); request.setAttribute("isError", "error"); return (mapping.findForward("Error")); } } } } } } else if (listOfFiles[ij].isDirectory()) { System.out.println("Directory " + listOfFiles[ij].getName()); } } deleteFile("C:/log"); // END get file list // Forward control to the specified success URI return (mapping.findForward("Success")); }
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet overall = workbook.createSheet("Overall"); XSSFRow row = overall.createRow(0); XSSFCellStyle topStyle = workbook.createCellStyle(); topStyle.setAlignment(CellStyle.ALIGN_CENTER); XSSFCell theme = row.createCell(0); theme.setCellValue("Theme"); overall.autoSizeColumn(0); XSSFCell occurs = row.createCell(1); occurs.setCellValue("Occurrences"); overall.autoSizeColumn(1); XSSFCell prev = row.createCell(2); prev.setCellValue("Prevalence"); overall.autoSizeColumn(2); theme.setCellStyle(topStyle); occurs.setCellStyle(topStyle); prev.setCellStyle(topStyle); for (int i = 0; i < themes.size(); i++) { XSSFRow r = overall.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(themes.get(i).getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(themes.get(i).getTotalOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount)); } // This could be done in the previous loop but since we don't need // indices as much, we may as well use the cleaner for each loop for (Theme t : themes) { XSSFSheet themeSheet = workbook.createSheet(t.getName()); XSSFRow row1 = themeSheet.createRow(0); XSSFCell keyword = row1.createCell(0); keyword.setCellValue("Keyword"); keyword.setCellStyle(topStyle); XSSFCell occ = row1.createCell(1); occ.setCellValue("Occurrences"); occ.setCellStyle(topStyle); XSSFCell themePrev = row1.createCell(2); themePrev.setCellValue("Prevalence"); themePrev.setCellStyle(topStyle); for (int i = 0; i < t.getKeywords().size(); i++) { Keyword k = t.getKeywords().get(i); XSSFRow r = themeSheet.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(k.getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(k.getNumOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs())); } } FileOutputStream output = new FileOutputStream(docName); workbook.write(output); output.close(); }
/** * 罫線スタイルの<b>CellStyle</b>を生成 四角の範囲に周囲太線、中は細線を描画する * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param isBorder 罫線描画フラグ * @param styleMap 罫線style */ public static void setTableDataCellStyle( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, boolean isBorder, Map<String, XSSFCellStyle> styleMap) { assert sheet != null; // Range内のすべてセルに罫線を描画 for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) { XSSFRow row = getRowAnyway(sheet, rIndex); for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) { XSSFCell cell = getCellAnyway(row, cIndex); cell.setCellStyle(styleMap.get("normal")); } } // 初行のTopのみ太罫線 XSSFRow row = getRowAnyway(sheet, nRowStart); XSSFCell cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(styleMap.get("isTopAndLeft")); for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) { cell = getCellAnyway(row, cIndex); cell.setCellStyle(styleMap.get("isTop")); } cell = getCellAnyway(row, nColumnEnd); cell.setCellStyle(styleMap.get("isTopAndRight")); // 間の行 for (int cIndexCenter = nRowStart + 1; cIndexCenter < nRowEnd; cIndexCenter++) { row = getRowAnyway(sheet, cIndexCenter); cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(styleMap.get("isLeft")); cell = getCellAnyway(row, nColumnEnd); cell.setCellStyle(styleMap.get("isRight")); } // 最後の行のBottomのみ太罫線 XSSFRow rowEnd = getRowAnyway(sheet, nRowEnd); cell = getCellAnyway(rowEnd, nColumnStart); cell.setCellStyle(styleMap.get("isBottomAndLeft")); for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) { cell = getCellAnyway(rowEnd, cIndex); cell.setCellStyle(styleMap.get("isBottom")); } cell = getCellAnyway(rowEnd, nColumnEnd); cell.setCellStyle(styleMap.get("isBottomAndRight")); // CellRangeAddress region=new // CellRangeAddress(nRowStart,nRowEnd,nColumnStart,nColumnEnd); // short border=XSSFCellStyle.BORDER_MEDIUM;//太罫線 // RegionUtil.setBorderTop(border,region,sheet,workbook); // RegionUtil.setBorderBottom(border,region,sheet,workbook); // RegionUtil.setBorderLeft(border, region, sheet, workbook); // RegionUtil.setBorderRight(border, region, sheet, workbook); }
/** * セルに文字列を出力する * * @param cell 対象セル * @param object 出力データ * @param style セルスタイル * @param zeroValue 値が0の時に設定する値 */ private static void setData(XSSFCell cell, Object object, XSSFCellStyle style, String zeroValue) { if (style != null) { cell.setCellStyle(style); } if (object instanceof String) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue((String) object); } else if (object instanceof Integer) { Integer integer = (Integer) object; if (0 == integer) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(zeroValue); } else { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Integer) object); } } else if (object instanceof Double) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); if (Double.isNaN((Double) object)) { cell.setCellValue(zeroValue); } else { Double value = (Double) object; if (0 == value.compareTo((Double) 0.0)) { cell.setCellValue(zeroValue); } else { cell.setCellValue((Double) object); } } } }
/** * 指定セルのセルスタイルを取得 * * @param sheet シート * @param nRow 行番号 * @param nColumn 列番号 * @return <b>CellStyle</b> */ public static XSSFCellStyle getCellStyle(XSSFSheet sheet, int nRow, int nColumn) { assert sheet != null; XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); return cell.getCellStyle(); }