• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

populating read values

 
Ranch Hand
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator



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
xlsx-file-look.JPG
[Thumbnail for xlsx-file-look.JPG]
cccc.xlsx file looks as follows
 
Ranch Hand
Posts: 808
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Golly, that's a lot of code.
 
Bartender
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
sai rama krishna
Ranch Hand
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
>>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
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sure
 
sai rama krishna
Ranch Hand
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not a “beginning” topic. Moving discussion.
 
sai rama krishna
Ranch Hand
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 930
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you very much. I got it now.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic