List of usage examples for org.apache.poi.ss.usermodel Row removeCell
void removeCell(Cell cell);
From source file:cherry.goods.excel.ExcelWriter.java
License:Apache License
/** * ?<br />/*w ww .j a v a 2 s . co m*/ * ???1(1) * * @param offsetCols ????? (???) * @param record 1(1) */ public void write(int offsetCols, String... record) { Row row = currentSheet.getRow(rownum); if (row == null) { row = currentSheet.createRow(rownum); } rownum += 1; for (int i = 0; i < record.length; i++) { int colnum = i + offsetCols; Cell cell = row.getCell(colnum); if (record[i] == null) { if (cell != null) { row.removeCell(cell); } } else { if (cell == null) { cell = row.createCell(colnum); } cell.setCellValue(record[i]); } } }
From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java
License:Apache License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * /*from www . j a v a 2 s . c om*/ * Note, this method will not update any formula references. * * @param sheet * @param columnToDelete */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) { continue; } // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) { maxColumn = lastColumn; } if (lastColumn < columnToDelete) { continue; } for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) { row.removeCell(oldCell); } Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = columnToDelete; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean deleteColumn(int[] columnIndex) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Sheet sheet = null;//from w w w . j a va2 s .c om try { sheet = getSheet(); if (sheet == null) { return false; } for (int i = 0; i <= sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i); for (int j = columnIndex.length - 1; j > -1; j--) { //???? for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) { Cell tmpCell = tmpRow.getCell(k); if (null != tmpCell) { tmpRow.removeCell(tmpCell); } Cell rightCell = tmpRow.getCell(k + 1); if (null != rightCell) { HSSFRow hr = (HSSFRow) tmpRow; hr.moveCell((HSSFCell) rightCell, (short) k); } } } } m_InputStream.close(); try ( // Write the output to a file final FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } return flag; }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.SummaryReportGenerator.java
License:Apache License
/** * Creates Leveraging Sheet//from w ww . j a va 2s . c om */ private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options, Map<String, ReportWordCount> p_wordCounts) throws Exception { int rowLen = p_sheet.getPhysicalNumberOfRows(); int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells(); int wordTotalCol = colLen - 2; int row = ROWNUMBER, column = colLen - 1; int costCol; Map<String, Double> p_ratesMap = null; for (int r = 2; r < rowLen + ROWNUMBER; r++) { Row theRow = getRow(p_sheet, r); theRow.removeCell(getCell(theRow, column)); } p_sheet.removeColumnBreak(column); // Rates Columns for (int dis = column - 1; column < colLen + dis - 2; column++) { Cell cell_From = p_sheet.getRow(row).getCell(column - dis); Cell cell_To = getCell(p_sheet.getRow(row), column); cell_To.setCellValue(cell_From.getStringCellValue()); cell_To.setCellStyle(cell_From.getCellStyle()); p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis)); // Adds Rates for Match Type for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) { String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue(); String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue(); double rate = getRate(matchType, targetLocale, p_ratesMap); addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook)); } } // Cost Columns Head costCol = column; p_sheet.setColumnWidth(column, 20 * 256); Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++); cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging")); cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook)); p_sheet.setColumnWidth(column, 20 * 256); Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++); cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging")); cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook)); p_sheet.setColumnWidth(column, 15 * 256); Cell cell_Savings = getCell(getRow(p_sheet, row), column++); cell_Savings.setCellValue(bundle.getString("lb_savings")); cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook)); Cell cell_Percent = getCell(getRow(p_sheet, row), column++); cell_Percent.setCellValue("%"); cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook)); // Cost Columns Data for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) { String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1)); String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*" + getColumnName(wordTotalCol + 5) + (row + 1); String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1); String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1); Row theRow = getRow(p_sheet, row); Cell cell_Leveraging = getCell(theRow, costCol); cell_Leveraging.setCellFormula(leveragingForm); cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook)); Cell cell_NoLeveraging = getCell(theRow, costCol + 1); cell_NoLeveraging.setCellFormula(noLeveragingForm); cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook)); Cell cell_Saving = getCell(theRow, costCol + 2); cell_Saving.setCellFormula(savingForm); cell_Saving.setCellStyle(getMoneyStyle(p_workbook)); Cell cell_PercentData = getCell(theRow, costCol + 3); cell_PercentData.setCellFormula(percent); cell_PercentData.setCellStyle(getPercentStyle(p_workbook)); } if (rowLen > 1) { row = rowLen + 1; column = 1; for (; column < colLen - 1; column++) { Cell cell_Total = getCell(getRow(p_sheet, row), column); cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column)); cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook)); } for (; column < costCol; column++) { Cell cell = getCell(getRow(p_sheet, row), column); cell.setCellValue(""); cell.setCellStyle(getHeaderOrangeStyle(p_workbook)); } // Summary Cost Columns Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column); cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++)); cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook)); Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column); cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++)); cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook)); Cell cell_SumSaving = getCell(getRow(p_sheet, row), column); cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++)); cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook)); String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1); Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column); cell_AvgPercent.setCellFormula(percent); cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook)); } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void clearRange(int sheetIndex, int[] coords) { Sheet sheet = getSheet(sheetIndex);//from www.ja v a2 s . c o m for (int i = coords[0]; i <= coords[2]; i++) { Row row = sheet.getRow(i); if (row == null) continue; for (int j = coords[1]; j <= coords[3]; j++) { Cell cell = row.getCell(j); if (cell != null) row.removeCell(cell); } if (row.getLastCellNum() < 0) sheet.removeRow(row); } }
From source file:com.qihang.winter.poi.util.PoiSheetUtility.java
License:Apache License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * //from w ww . j a va 2 s. c om * Note, this method will not update any formula references. * * @param sheet * @param column */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) continue; // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) maxColumn = lastColumn; if (lastColumn < columnToDelete) continue; for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) row.removeCell(oldCell); Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = 0; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * * Note, this method will not update any formula references. * * @param sheet//w w w .j a v a2s .c o m * @param column */ private static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int iii = 0; iii < sheet.getLastRowNum() + 1; iii++) { Row row = sheet.getRow(iii); // if no row exists here; then nothing to do; next! if (row == null) { continue; } // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) { maxColumn = lastColumn; } if (lastColumn < columnToDelete) { continue; } for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) { row.removeCell(oldCell); } Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int ccc = 0; ccc < maxColumn; ccc++) { sheet.setColumnWidth(ccc, sheet.getColumnWidth(ccc + 1)); } }
From source file:lucee.runtime.poi.Excel.java
License:Open Source License
public void setValue(int rowNumber, int columnNumber, String value) throws CasterException { if (value == null) value = ""; Sheet sheet = workbook.getSheet(getExcelSheetName()); // get Row//from ww w . j av a 2 s .c o m Row row = sheet.getRow(rowNumber); if (row == null) row = sheet.createRow(rowNumber); // get Cell Cell cell = row.getCell(columnNumber); CellStyle style = null; if (cell != null) { style = cell.getCellStyle(); row.removeCell(cell); } cell = row.createCell(columnNumber); if (style != null) cell.setCellStyle(style); CreationHelper createHelper = workbook.getCreationHelper(); boolean isFormula = style != null && style.getDataFormatString().equals("@"); if (!isFormula && Decision.isNumeric(value)) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); double dbl = Caster.toDoubleValue(value); cell.setCellValue(dbl); _expandColumnWidth(sheet, Caster.toString(dbl), columnNumber); } else if (StringUtil.isEmpty("")) { cell.setCellType(Cell.CELL_TYPE_BLANK); cell.setCellValue(createHelper.createRichTextString("")); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(createHelper.createRichTextString(value)); _expandColumnWidth(sheet, value, columnNumber); } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetDeleteColumn.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; String cols;//from w w w .ja va2 s . c om /* * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(1); cols = parameters.get(0).getString(); Sheet sheet = spreadsheet.getActiveSheet(); Set<Integer> numbers = tagUtils.getNumberSet(cols); Iterator<Row> rowIT = sheet.rowIterator(); while (rowIT.hasNext()) { Row row = rowIT.next(); Iterator<Integer> columnIndx = numbers.iterator(); while (columnIndx.hasNext()) { Cell cell = row.getCell(columnIndx.next() - 1); if (cell != null) { row.removeCell(cell); } } } return cfBooleanData.TRUE; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetShiftColumns.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int start = 0, end = 0, cols = 1; /*/*from www. j a va 2 s. co m*/ * Collect up the parameters */ if (parameters.size() == 2) { spreadsheet = (cfSpreadSheetData) parameters.get(1); start = parameters.get(0).getInt() - 1; end = start; } else if (parameters.size() == 3) { spreadsheet = (cfSpreadSheetData) parameters.get(2); start = parameters.get(1).getInt() - 1; end = start; cols = parameters.get(0).getInt(); } else if (parameters.size() == 4) { spreadsheet = (cfSpreadSheetData) parameters.get(3); start = parameters.get(2).getInt() - 1; end = parameters.get(1).getInt() - 1; cols = parameters.get(0).getInt(); } /* * Validate parameters */ if (start < 0) throwException(_session, "start must be 1 or greater (" + start + ")"); if (end < 0) throwException(_session, "end must be 1 or greater (" + end + ")"); if (start > end) throwException(_session, "end must be greater that start"); Sheet sheet = spreadsheet.getActiveSheet(); Iterator<Row> rowIT = sheet.rowIterator(); while (rowIT.hasNext()) { Row row = rowIT.next(); if (cols > 0) { // Moving to the right for (int x = 0; x < (end - start) + 1; x++) { // Remove Cell that is there Cell cell = row.getCell(end + cols - x); if (cell != null) row.removeCell(cell); Cell thisCell = row.getCell(end - x); if (thisCell != null) { Cell newCell = row.createCell(end + cols - x, thisCell.getCellType()); SheetUtility.cloneCell(newCell, thisCell); row.removeCell(thisCell); } } } else { // Moving to the left for (int x = 0; x < (end - start) + 1; x++) { // Remove Cell that is there if (start + cols - x < 0) continue; Cell cell = row.getCell(start + cols - x); if (cell != null) row.removeCell(cell); Cell thisCell = row.getCell(start - x); if (thisCell != null) { Cell newCell = row.createCell(start + cols - x, thisCell.getCellType()); SheetUtility.cloneCell(newCell, thisCell); row.removeCell(thisCell); } } } } return cfBooleanData.TRUE; }