How to read and update dynamic excel file programatically using apache poi 3.5 and java?

Question

Now i want to read any excel file that are given by the user dynamically and also the extract the data in the tabular form and also update the data the cells...

so, how to capture the column headers and row headers in the excel tabular form and also modify the particular cell value....

See my excel file may like this:

Excel file data

Production Tracking System                              
Production Report                               
Location                    Number of centers           Note:
Manager Name                    Number of emp           
Manager No                              
Project Name                                
Production Plan         Status of the Production                    
Center Name Date    Target  Status  Items Completed Defects Pending Estimated date of completion    Comments

worker_mdi_1    10/19/2010  0   0   0   0   0   10/19/2010  Nil
Total   10/19/2010  0   0   0   0   0   10/19/2010  Nil

and also append the new data at the last row like Total row. How to do this?

Excel uses an internal mechanism to recognize whether a row is a Header - some clues here excel.tips.net/Pages/…. so this wont be available as an API in POI. Ask on the mailing list to confirm or a better solution. — JoseK
– JoseK, Commented Nov 11, 2010 at 8:02

Andreas Dolk · Accepted Answer · 2010-11-11 07:47:28Z

1

I've never seen a function to annotate worksheet cells as "header". Excel itself can detect row and column header but I'm sure this is based on some sort of pattern recognition. It does some analysis on the cell content and "guesses" if a cell contains a row or column header entry or a value.

Maybe you're lucky and the header values are fixed, so that you can scan the worksheet areas for those known values in rows and cells.

(Old part of the answer, based on a misunderstanding)

As far as I understand your question, you'd like to now how to use apache POI. Reading an excel file and reading/modifying worksheet content are basic tasks. Apache has some howtos, manuals and examples on their page. Look at the "General Use" section on the howto page, they have an example for reading an writing an excel file.

edited Nov 11, 2010 at 7:47

answered Nov 11, 2010 at 7:08

Andreas Dolk

115k20 gold badges185 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saravanan Over a year ago

Hi Andreas, i know very well poi3.5. i have used last six months...we are using excel sheet for storing data instead of using DB.Early i have one static excel sheet.so, i know where is the col_header is start and also row_header is start.I mean i know the row address.But now user gives his own excel sheet every time, i don't know where the col_header and row_header is start.....so, i want to capture that information including row_header,col_header values...How i do it... Please give me a solution....

Andreas Dolk Over a year ago

@Saravanan - ah, sorry, misunderstood your question.

Saravanan Over a year ago

For Example, User Selecting any excel file that containing col_header,Row_header. Col_header like Name,Date,target,status.Row_header is Saravanan,Keerthi,John,Total.Here i don't know where the col_header and row_header starts... how to identify the row_header,col_header cell address programatically...This is my problem

Collectives™ on Stack Overflow

How to read and update dynamic excel file programatically using apache poi 3.5 and java?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related