abcd abcd - 1 month ago 25
Java Question

Swapping between Columns in excel sheet using java

I am new in java. Actually I want to swap two columns in excel sheet using java. I used the code but I didn't get correct output. I add screenshot of my excel sheet. I want to swap system name and date columns.

enter image description here

I also add screenshot of my incorrect output. I got 24 and 29 jan 1900 instead of 1-30th sept 2016.

enter image description here

CellStyle cellStyle1 = workbook11.createCellStyle();
CreationHelper createHelper1 = workbook11.getCreationHelper();
cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("d-mmm"));
try {
if (file11.exists()) {
String dt = sh1.getRow(0).getCell(1).getStringCellValue();
if (!dt.equalsIgnoreCase("Date")) {
Iterator<Row> rowIterator1 = sh1.iterator();
while (rowIterator1.hasNext()) {
Row row = rowIterator1.next();
if (row.getCell(1).getStringCellValue().equalsIgnoreCase("Date")) {
Cell cl1 = row.getCell(0);
Cell cl2 = row.getCell(1);
Cell temp = row.getCell(0);
Cell temp1 = row.getCell(1);
cl1.setCellValue(temp1.getStringCellValue());
cl2.setCellValue(temp.getStringCellValue());
} else {
Cell cl1 = row.getCell(0);
Cell cl2 = row.getCell(1);
cl2.setCellType(Cell.CELL_TYPE_STRING);
cl1.setCellType(Cell.CELL_TYPE_STRING);
Cell temp = row.getCell(0);
Cell temp1 = row.getCell(1);
cl1.setCellValue(temp1.getStringCellValue());
cl2.setCellValue(temp.getStringCellValue());
row.getCell(1).setCellStyle(cellStyle1);
}
}
}
}
}


Edit:

According to xenteros' answer I've tried the following:

CellStyle cellStyle1 = workbook11.createCellStyle();
CreationHelper createHelper1 = workbook11.getCreationHelper();
cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("d-mmm"));
try {
if (file11.exists()) {

String dt = sh1.getRow(0).getCell(1).getStringCellValue();
if (!dt.equalsIgnoreCase("Date")) {
Iterator<Row> rowIterator1 = sh1.iterator();
while (rowIterator1.hasNext()) {
Row row = rowIterator1.next();
if (row.getCell(1).getStringCellValue().equalsIgnoreCase("Date")) {
Cell cl1 = row.getCell(0);
Cell cl2 = row.getCell(1);
String temp = new String(cl2.getStringCellValue());
cl2.setCellValue(cl1.getStringCellValue());
cl1.setCellValue(temp);
else {

Cell cl1 = row.getCell(0);
Cell cl2 = row.getCell(1);
cl2.setCellType(Cell.CELL_TYPE_STRING);
cl1.setCellType(Cell.CELL_TYPE_STRING);
String temp = new String(cl2.getStringCellValue());
System.out.println(temp);
java.util.Date temp2 = cl1.getDateCellValue();
cl2.setCellStyle(cellStyle1);
cl2.setCellValue(temp2);
cl1.setCellType(Cell.CELL_TYPE_STRING);
cl1.setCellValue(temp);
}
}

Answer

I got the answer of this question.

try{

 if (file11.exists()) {
 String dt = sh1.getRow(0).getCell(1).getStringCellValue();
 if (!dt.equalsIgnoreCase("Date")) {
 Iterator<Row> rowIterator1 = sh1.iterator();
 while (rowIterator1.hasNext()) {
 Row row = rowIterator1.next();                    
 DataFormatter df = new DataFormatter();//instantiate DataFormatter class for reading the cell without changing the cell type
 Cell cl0 = row.getCell(0);
 Cell cl1 = row.getCell(1);
 CellStyle cs1 = cl0.getCellStyle();
 CellStyle cs2 = cl1.getCellStyle();
 String s1 = new String(df.formatCellValue(cl0));//store cell value as string
 String s2 = new String(df.formatCellValue(cl1));//store cell value as string
 cl1.setCellValue(s1);//perform swapping
 cl0.setCellStyle(cs2);
 cl1.setCellStyle(cs1);//perform swapping on formatting
 cl0.setCellValue(s2);//perform swapping  
            }
        }
    } else {
        System.out.println("File does not exist..................");
    }
 }
catch (Exception e) {
        } finally {
           FileOutputStream out = new FileOutputStream(Report_File2);
    workbook11.write(out);
    out.close();
}