Swapping between two columns values

[Gunjan Jadon]

I want to ask how do we perform swapping between two columns values in excel using java. Please tell me. Thanks in advance.

[praveen kumaar]

Do you have tried anything so far?
you can make use of Apache POI--->this is a java API for Microsoft documents.
take a look at Quick Guide for better understanding of API.on the left side of the website,it has its index you can see plenty of options there,choose as you like.
You may have question in your mind how really will you implement this api in your code or how will you swap the value.take a piece of paper,try to build some logic,think about it-if you have to swap the value directly in excel,how would you have done.then try to correlate the previously done things with coding steps or as a pseudo code.
i hope my suggestion will help you in getting your solution!

Kind Regards,
Praveen.

[Gunjan Jadon]

Iterator < Row > rowIterator1 = sh1.iterator();
while (rowIterator1.hasNext()) {
    Row row = rowIterator1.next();
    Cell temp;
    Cell c1 = row.getCell(0);
    Cell c2 = row.getCell(1);
    temp = c1;
    c1 = c2;
    c2 = temp;
    System.out.println("Date:hdgjs" + c1);
    System.out.println("Date:" + c2);
}

[Gunjan Jadon]

I used this code. I got swapping values but I want to put these values in excel sheet also. How can I do that. Please Help me. Thanks in advance.

[praveen kumaar]

Hi gunjan.you have incorrectly used code tags.next time whenever you use it first thing is to do a selection of your code and then hit a code button.before hitting a submit button please review your post,it will make you know how exactly it looks after posting.

kind regards
Praveen.

[Gunjan Jadon]

Ok Praveen, I got it. Next time I will try my best.

[Gunjan Jadon]

Anyone please help me to solve the problem of this question. It's very urgent.

[praveen kumaar]

Gunjan Jadon wrote:I used this code. I got swapping values but I want to put these values in excel sheet also. How can I do that. Please Help me. Thanks in advance.

read the "reading and writing" lesson in the quick guide link-i have provided it above.there you will learn how can you read and write to the excel file.read the lessons carefully accordingly with your requirement.everything is explained in it,if you stuck at some part don't shy to ask here.

Kind regards,
Praveen.

[praveen kumaar]

Their is a heading "Reading and Re-writing".try to understand how exactly they are reading and then rewriting the same excel file,it's not that complex,you can easily understand that.

[Gunjan Jadon]

Hi Praveen, I want to ask you that when I print c1 then it returns c2 value and when I print c2 then it return c1 value. c1 and c2 are cells. I am unable to put these values in excel sheet.

[Gunjan Jadon]

Praveen, are you getting my point?

[praveen kumaar]

yes i got your point,do you have read what i suggest in my previous post.show me your full code or what have you tried so far...

[Gunjan Jadon]

if (file1.exists()) {
    FileInputStream fis1 = new FileInputStream(file1);
    XSSFWorkbook workbook = new XSSFWorkbook(fis1);
    XSSFSheet sh1 = workbook.getSheetAt(0);

    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 temp;
                Cell cl1 = row.getCell(0);
                Cell cl2 = row.getCell(1);
                temp = cl1;
                cl1 = cl2;
                cl2 = temp;
                cl1.setCellValue(cl2.getStringCellValue());
                cl2.setCellValue(cl1.getStringCellValue());

            } else {
                Cell temp;
                Cell cl1 = row.getCell(0);
                Cell cl2 = row.getCell(1);
                temp = cl1;
                cl1 = cl2;
                cl2 = temp;
                cl2.toString();
                cl1.setCellValue(cl1.getStringCellValue());
                cl2.setCellValue(cl2.getStringCellValue());
            }

        }
    }
}

[Gunjan Jadon]

See this

[praveen kumaar]

again you didn't use code tags-see your code should be b/w 2 square brackets-
["code=java] your code goes here [/code"]
i have used inverted commas for visibility of syntax of code tag,neglect it.

so you  are done with swapping,all you need now is to just write these values to excel sheet,is it?

[praveen kumaar]

for writting do as below:

try {
        FileOutputStream fileout=new FileOutputstream(file1); // i am assuming you need to write the data in the same excel file.
 
        workbook.write(fileout); // it would write the new workbook to the required file.
}
finally{
 fis1.close() ; // close the FileInputStream.
 fileout.close() ; //close the FileOutputStream.
}

try this in your code and let me know when you are done.

Kind regards,
Praveen.

[Gunjan Jadon]

I tried this but it gives an error.
java.lang.IllegalStateException: Cannot get a text value from a numeric cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1015)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:399)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:351)
at b2b_final.B2B_Final.cpu52_54(B2B_Final.java:359)
at b2b_final.B2B_Final.main(B2B_Final.java:912)

one column is system name and other one is date column.
In first row, values are system name and date which are string and after first row all values are numeric.

[praveen kumaar]

Then use setCellType(CellType celltype) method of class-"org.apache.poi.hssf.usermodel.HSSFCell".
you can take a look at an example how this method is used here-setCellType-Example.
notice how they sets the celltype before writing in it.

Kind Regards,
Praveen.

[Gunjan Jadon]

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);
System.out.println("Before" + temp);

cl1.setCellValue(temp1.getStringCellValue());
cl2.setCellValue(temp.getStringCellValue());
row.getCell(1).setCellStyle(cellStyle1);

I used this code but I got wrong output. I got 24 jan and 29 jan 1900 instead of 1-30 sept 2016 in date column.

[Gunjan Jadon]

see this

[Gunjan Jadon]

I want to swap these two column system name and date. when I used that code I got this type of output.

[praveen kumaar]

First of all take a look at this--->how to use code-tags.

[Gunjan Jadon]

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 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);
                }
            }
        }
    }
}

[Gunjan Jadon]

praveen kumaar wrote:First of all take a look at this--->how to use code-tags.

Is it correct format?

[praveen kumaar]

Gunjan Jadon wrote:Is it correct format?

Yes the format is correct though your code need some indentation and comments,it would make your code readable.comment will let us know what you are doing or like to do.keep these things in mind for the next time.

now come to your problem..
as a programmer,i have already mentioned,you need to have some strategies or kind of pseudo code before moving towards coding.you are getting these problems because:
--->you have not written your strategy i.e how really you are going to solve this problem.
--->you are not thinking about-how really would you solve this problem if asked to solve on excel directly.you may be thinking that it is really easy to do so on excel,perhaps it is,and even more easy through coding.
--->the most important things,you have not read the API carefully.for ex at line-21:

cl1.setCellType(Cell.CELL_TYPE_STRING);

do you know what are you doing here,you are trying to set the cell type(type of the value in a cell) of given cell to string though it is containing a numerical value and consequently you are getting a exception.

so before moving towards coding be assured that you have done all these things.and believe me these things will let you enjoy your work even better.

like if i were at your place i would have written this before coding:-

1.open the excel workbook--->use the Input stream and XSSFworkbook(or HSSFWorkbook) class.
2.open the required sheet---->use workbook.getSheetAt(index) method
3.declare the iterator<Row>,you will get through sheet.iterator() methods,it will let you to iterate on rows.
4.now check for the cells you need to swap,just think about a single row for simplicity,the iterator will do the rest work.
----Strategy for swapping---
5.their is a DataFormatter class in the apache poi API,it will let you read the value in your cell as a string without changing the cell type.formatCellValue(Cell c) is the required method in this class.read the API for extra info.
6.use 2 String variable to store the values of the swapping cell,you will get it from above method.
7.store the cellstyle of the swapping cells in some CellStyle variable(use cell.getCellStyle()).because after setting the new cell values,along with swapping values you will also need to swap the formatting
8.set the new cell values in cell,you don't need to use any temporary cell as you have done...
.
.
.//rest is your job.
.
.

as you are working on this project for a long time .i am providing you a very big hint.
Hint:

DataFormatter df = new DataFormatter();//instantiate DataFormatter class for reading the cell without changing the cell type

String s1=df.formatCellValue(c1);//store cell value as string

String s2 = df.formatCellValue(c2);//store cell value as string

c1.setCellValue(s2);//perform swapping

c2.setCellValue(s1);//perform swapping

c1.setCellStyle(cs2);//perform swapping on formatting

c2.setCellStyle(cs1);//perform swapping on formatting.

hope this time you will get a success!

Kind Regards,
Praveen.

[praveen kumaar]

I will request some staff member to delete the duplicate post and  add code tags wherever necessary here,i have not keys for moderating the same.

[Gunjan Jadon]

I used this code. When I print cl1 cell then it prints correct output but in excel sheet it is returned incorrect output.

XSSFCellStyle cellStyle1 = workbook11.createCellStyle();// Create cellstyle
CreationHelper createHelper1 = workbook11.getCreationHelper();
cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("d-mmm"));// set cellstyle
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);
                                    String s1=df.formatCellValue(cl0);//store cell value as string
                                    String s2 =df.formatCellValue(cl1);//store cell value as string
                                    cl1.setCellValue(s1);//perform swapping
                                    System.out.println(cl1);//print date
                                    cl1.setCellStyle(cellStyle1);//perform swapping on formatting
                                    cl0.setCellValue(s2);//perform swapping
                 }
            }
     }
}
 

[praveen kumaar]

i am mentioning some part of your code.did you not think that the marked lines is kind of unnecessary thing in your code.

1.XSSFCellStyle cellStyle1 = workbook11.createCellStyle();// Create cellstyle
2.CreationHelper createHelper1 = workbook11.getCreationHelper();
3.cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("d-mmm"));// set cellstyle
4.try {
       if (file11.exists()) {
       5.String dt = sh1.getRow(0).getCell(1).getStringCellValue();
       6.if (!dt.equalsIgnoreCase("Date")) {

are you not comfortable with this approach..

InputStream is = new FileInputStream("C:/Users/Admin/...xls");
	Workbook wb = new HSSFWorkbook(is);
	Sheet sheet = wb.getSheetAt(0);
	Iterator<Row> it = sheet.iterator();
	while(it.hasNext()) {
	Row r=it.next();
	Cell c1 = r.getCell(0);
	Cell c2 = r.getCell(1);
	CellStyle cs1= c1.getCellStyle();
	CellStyle cs2= c2.getCellStyle();
	DataFormatter df = new DataFormatter();
	String s1=df.formatCellValue(c1);
	String s2 = df.formatCellValue(c2);
    c1.setCellValue(s2);
	c2.setCellValue(s1);
    c1.setCellStyle(cs2);
	c2.setCellStyle(cs1);
//extra lines goes here...

[Gunjan Jadon]

praveen kumaar wrote:i am mentioning some part of your code.did you not think that the marked lines is kind of unnecessary thing in your code.

1.XSSFCellStyle cellStyle1 = workbook11.createCellStyle();// Create cellstyle
2.CreationHelper createHelper1 = workbook11.getCreationHelper();
3.cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("d-mmm"));// set cellstyle
4.try {
       if (file11.exists()) {
       5.String dt = sh1.getRow(0).getCell(1).getStringCellValue();
       6.if (!dt.equalsIgnoreCase("Date")) {

are you not comfortable with this approach..

InputStream is = new FileInputStream("C:/Users/Admin/...xls");
	Workbook wb = new HSSFWorkbook(is);
	Sheet sheet = wb.getSheetAt(0);
	Iterator<Row> it = sheet.iterator();
	while(it.hasNext()) {
	Row r=it.next();
	Cell c1 = r.getCell(0);
	Cell c2 = r.getCell(1);
	CellStyle cs1= c1.getCellStyle();
	CellStyle cs2= c2.getCellStyle();
	DataFormatter df = new DataFormatter();
	String s1=df.formatCellValue(c1);
	String s2 = df.formatCellValue(c2);
    c1.setCellValue(s2);
	c2.setCellValue(s1);
    c1.setCellStyle(cs2);
	c2.setCellStyle(cs1);
//extra lines goes here...

Hello Praveen, My problem is solved using your code. thank you so much.