jexcel - transpose question

Question

I am trying to work with JExcel API and had the following question.

I have queried my database tables for 3 columns: id, time, value. I am adding them into different arrays as objects. I want to output them into an excel sheet as follows:

 id      9:10   9:11   9:12

1       value  value  value

2       value  value  value

3       value  value  value

Where 1, 2 and 3 are the id's. The value is the value of that id at that time. I am not getting a clue how I could have all the input sorted as above as I need to do it for further analysis.

Any suggestions?

Does it necessarily need to be fullworthy Excel file? Wouldn't just CSV suffice? CSV is easy to generate using Java and perfectly supported by Excel. You could even benefit of the DB's builtin CSV generators. — BalusC, Aug 13 '10 at 14:55
CSV is cool. But still could you tell me how to go about it?? I still am having no clue :( In the end I just need to generate an excel report after some major sorting of the data. also need to store the data somewhere so that I could sort it. I am using arraylists to store the data which seems majorly inefficient as the data I am processing is over 100000 records! Any better way to do it?? — JJunior, Aug 13 '10 at 15:07
Use SQL, not Java. The DB is tremendously much more efficient in the sorting job. Just write SQL so that the DB returns the results exactly the way you want, if need be in flavor of a CSV file. — BalusC, Aug 13 '10 at 19:31

score 0 · Answer 1 · answered Apr 24 '13 at 14:50

This sort of issue can be resolved reasonably easily.

First, ask yourself, what would I do if I were to transpose them by hand?

I'm assuming here your data in the DB looks liek this:

id          1           2           3
09:10       valueA1     valueA2     valueA3
09:11       valueB1     valueB2     valueB3
09:12       valueC2     valueC2     valueC3

What you need to do, is swap your rows into columns. id <->id, 1<->09:00, 2<->09:11....valueA1<->valueA1, valueB1<->valueA2.

So, if we were going over this with nested for loops, where we read in a cell C, at C_ij we write out into C_ji.

I hope this enough to show you what to do.

jexcel - transpose question

1 Answers1