wood burning stoves 2.0*
The moose likes Java in General and the fly likes populating read values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "populating read values " Watch "populating read values " New topic
Author

populating read values

sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230



I am using above code to read contents of attached cccc.XLSX as below

22,33,44,"aa",6/6/20 15:49,"77-SS_001","DONE"

23,34,45,"aa",6/6/20 15:49,"77-SS_002","NoDONE".


I would like to read only first two columns as below in to some object
22 33
23 34

so that i can query database in later steps

How can I achieve it.
Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance



[Thumbnail for xlsx file look.JPG]

dennis deems
Ranch Hand

Joined: Mar 12, 2011
Posts: 808
Golly, that's a lot of code.
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

There are at least two ways of viewing XLSX files via the POI tools. The first is as a SAX-like event series. This is the code you have above (I think). In this case you want to identify which 2 columns you care about, store them and ignore anything that comes in from other columns. The second approach is to fill the entire document into memory and navigate it like a DOM model. That way you can navigate directly to the Rows and Columns you want, take the values and ignore the rest.

So you need to go to the POI resources and find out how to:
1) Read what column input is coming from. After that you can determine to either keep or ignore it
- or -
2) Load the entire file in as a DOM object. After that navigate to the columns you want to keep


Steve
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
>>This is the code you have above (I think).
correct
>> In this case you want to identify which 2 columns you care about, store them and ignore anything that comes in from other columns.
I need to get first two columns. How can I get first two column values to store them to send to some database query later. Please advise
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

sai rama krishna wrote:>>This is the code you have above (I think).
correct
>> In this case you want to identify which 2 columns you care about, store them and ignore anything that comes in from other columns.
I need to get first two columns. How can I get first two column values to store them to send to some database query later. Please advise


You said that already, and I told you how. I am not going to give you code to cut and paste, because I don't think you have (up to this point) shown any effort in solving your problems in any of your previous threads. Please look at the documentation for POI, see how you are supposed to identify what column you are working on, give it a try, post the code you come up with here if you have further questions.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
sure
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230

I have identifiied row index(0,1), as well as column index (0,1)

while(cells.hasNext())

{

XSSFCell cell = (XSSFCell) cells.next();
String Value=null;
if(cell.getCellType() == cell.CELL_TYPE_STRING) {
Value=cell.getStringCellValue();

System.out.println("string values-->"+Value);
}
else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
//int Value2=(Integer) null;
double Value2=cell.getNumericCellValue();
System.out.println("numbercal values--->"+Value2);

int Value3=cell.getColumnIndex();
System.out.println("col index--->"+Value3);
int Value4=cell.getRowIndex();
System.out.println("Row index--->"+Value4);
}

number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE



I need to print

22 33(row index 0 and column index 0.1)
and
23 34(row index 1 and column index 0,1)

I was not sure how to print this kind of 2 dimentional cell value. I could not find corresponding method in API yet. Please advise
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

I was not sure how to print this kind of 2 dimentional cell value.

So exactly what part are you having problems with. The code shows you can print to the screen and that you can figure out which column you are on. Do you:

1) Not know how to print one value to the screen without adding a new line?
2) Not know how to print a new line only if you read the last column you want to print?
3) Not know how to not print when you don't want to?

Note that all three cases are solved by simple if statements. To get all 3 requires probably something like if() {} else if() {} else {}
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
i do not know how to print cell value(34 in the attached excel screenshot i put in this question) from xlsx sheet with a position of

row index=1
and
column index=1

by specifying both the indexes. Please advise
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

In the first post you are using a SAX parser to view the data. SAX parsers are event based, and always sequential. So you can't skip right to a particular index. You have to go through each value, keeping the ones you want and throwing away the ones you don't. I am not exactly sure what that second bit of code you posted above it. It doesn't come from the SAX type code you posted at the top, so god-knows-what types and methods you have available.

If you really want to randomly access the Cells, you need to use the 'in memory model' or what is referred to as 'Document Object Model' in XML. To do this, don't use any of the classes in the org.apache.poi.***.eventusermodel tree, instead use only ones in the org.apache.poi.***.usermodel package tree. I mentioned in another thread of yours which Factory in which specific package is a good place to start. Look through what is available there, then try to work your way from a Workbook (the file) into a Cell.

[edit]
I should note: the SAX model is capable of doing what you want as long as you just want to read the data. Previous information I gave you in this thread will be applicable. The DOM model is capable of random access, so it closer matches the 'print cell value...from xlsx sheet...by specifying both the indexes' request. But DOM can be slow and takes a lot of memory.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38007
    
  22
Not a “beginning” topic. Moving discussion.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
when i try to read using getRow()


XSSFRow x= sheet1.getRow(0);
System.out.println(" row content 0---->"+ x);

XSSFRow x1= sheet1.getRow(0);
System.out.println(" row content 1---->"+ x1);
int number=sheet1.getLastRowNum();

getting results as below

row content 0----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:c r="A1" s="1">
<main:v>22</main:v>
</main:c>
<main:c r="B1" s="1">
<main:v>33</main:v>
</main:c>
<main:c r="C1" s="1">
<main:v>44</main:v>
</main:c>
<main:c r="D1" s="1" t="s">
<main:v>0</main:v>
</main:c>
<main:c r="E1" s="2">
<main:v>43988.659085648149</main:v>
</main:c>
<main:c r="F1" s="1" t="s">
<main:v>1</main:v>
</main:c>
<main:c r="G1" s="1" t="s">
<main:v>2</main:v>
</main:c>
</xml-fragment>
row content 1----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:c r="A1" s="1">
<main:v>22</main:v>
</main:c>
<main:c r="B1" s="1">
<main:v>33</main:v>
</main:c>
<main:c r="C1" s="1">
<main:v>44</main:v>
</main:c>
<main:c r="D1" s="1" t="s">
<main:v>0</main:v>
</main:c>
<main:c r="E1" s="2">
<main:v>43988.659085648149</main:v>
</main:c>
<main:c r="F1" s="1" t="s">
<main:v>1</main:v>
</main:c>
<main:c r="G1" s="1" t="s">
<main:v>2</main:v>
</main:c>
</xml-fragment>
number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE


some reason for both rownum 0 and 1 it is still printing same results ie row 0 results. Please advise


Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

sai rama krishna wrote:when i try to read using getRow()



some reason for both rownum 0 and 1 it is still printing same results ie row 0 results. Please advise


You never get row 1. Where do you think you get row 1?
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
on the console i expected to print. But it did not printed rownum 1. It printed rownum0 both times. Please advise
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
i see I made mistake I gave 0 at both places whike calling getROW() method. Row content coming fine but now i have issue with cell content. I am getting 22 both times. I supposed to get 22 and then 23



XSSFRow x= sheet1.getRow(0);
System.out.println(" row content 0---->"+ x);
XSSFCell y = x.getCell(0);
System.out.println(" cell content 0---->"+ y);

XSSFRow x1= sheet1.getRow(1);
System.out.println(" row content 1---->"+ x1);
XSSFCell y1 = x1.getCell(0);
System.out.println(" cell content 1****---->"+ y);




console output



row content 0----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:c r="A1" s="1">
<main:v>22</main:v>
</main:c>
<main:c r="B1" s="1">
<main:v>33</main:v>
</main:c>
<main:c r="C1" s="1">
<main:v>44</main:v>
</main:c>
<main:c r="D1" s="1" t="s">
<main:v>0</main:v>
</main:c>
<main:c r="E1" s="2">
<main:v>43988.659085648149</main:v>
</main:c>
<main:c r="F1" s="1" t="s">
<main:v>1</main:v>
</main:c>
<main:c r="G1" s="1" t="s">
<main:v>2</main:v>
</main:c>
</xml-fragment>
cell content 0---->22.0
row content 1----><xml-fragment r="2" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:c r="A2" s="1">
<main:v>23</main:v>
</main:c>
<main:c r="B2" s="1">
<main:v>34</main:v>
</main:c>
<main:c r="C2" s="1">
<main:v>45</main:v>
</main:c>
<main:c r="D2" s="1" t="s">
<main:v>0</main:v>
</main:c>
<main:c r="E2" s="2">
<main:v>43988.659085648149</main:v>
</main:c>
<main:c r="F2" s="1" t="s">
<main:v>3</main:v>
</main:c>
<main:c r="G2" s="1" t="s">
<main:v>4</main:v>
</main:c>
</xml-fragment>
cell content 1****---->22.0
number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE

sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
I made other mistake

XSSFRow x= sheet1.getRow(0);
System.out.println(" row content 0---->"+ x);
XSSFCell y = x.getCell(0);
System.out.println(" cell content 0---->"+ y);

XSSFRow x1= sheet1.getRow(1);
System.out.println(" row content 1---->"+ x1);
XSSFCell y1 = x1.getCell(0);
System.out.println(" cell content 1****---->"+ y1);


above code printed 22,23 both as well
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

There ya go. You are well on your way now. You show that you can read the API to learn what functions are available, try things out, and interpret mistakes. That is how you get things done.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 230
thank you very much. I got it now.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: populating read values
 
Similar Threads
reading excel xlsx files to convert into CSV format
apache POI excel 2007 reading examples
Read a .xls,.xlsx file format using XSSF
read .xlsx file and generate csv file
populate the query values from reading .xlsx file