StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODelete Excel Blank Columns permanently using Java
text
Body
copied!<p>My Excel Sheet look like this:</p> <pre><code>Column Index: [0] [1] [2] [3] Column Name : Name Password Address XYZ 111 Delhi ABC 222 Chennai </code></pre> <p>Here column at index[2] whose cell type is BLANK.I want to delete the columns of CELL_TYPE_BLANK.</p> <p>I could able to find columns and their index numbers which are CELL_TYPE_BLANK, but I don't find the method to delete those columns permanently from Excel sheet.How to code to meet my requirement?</p> <p>My output Excel sheet should look like below after deleting CELL_TYPE_BLANK columns.</p> <pre><code>Column Index: [0] [1] [2] Column Name : Name Password Address XYZ 111 Delhi ABC 222 Chennai import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.usermodel.Workbook; public class DeleteColumn { public static void main(String[] args) throws InvalidFormatException, FileNotFoundException, IOException { Workbook wb = WorkbookFactory.create(new FileInputStream("Delete_Columns.xls")); Sheet sheet = wb.getSheetAt(0); Cell cell; Column column; Row row= sheet.getRow(0); int numXolumns = row.getLastCellNum(); for(int col=0; col< numXolumns; col++) { cell = row.getCell(col); int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_BLANK) { System.out.println("[" + cell.getRowIndex() + ", "+ cell.getColumnIndex() + "] = BLANK CELL"+ cell.toString()); int Delete_column_index=cell.getColumnIndex(); } } FileOutputStream fileOut = new FileOutputStream("Output_Excel.xls"); wb.write(fileOut); fileOut.close(); } } </code></pre> <p>The code I implemented to delete the rows is</p> <pre><code>public static void removeRow() { try { int getLastCell=row.getLastCellNum()-1; int lastIndex = sheet.getLastRowNum(); for (int i=0; i<=lastIndex; i++) { row=sheet.getRow(i); if(row.getCell(getLastCell)!=null && ((row.getCell(getLastCell).toString().equalsIgnoreCase("Not valid for this App Type".trim()))|| (row.getCell(getLastCell).toString().equalsIgnoreCase("Not Applicable".trim())))) { sheet.shiftRows(i+1, lastIndex, -1); i--; } } } catch(NullPointerException e) { e.printStackTrace(); } catch(Exception e) { e.printStackTrace(); } } </code></pre> <p>How to delete the columns?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload