• 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

SpringRowmapper

 
Ranch Hand
Posts: 112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,
My requirement to work on a project was to generate an .xlsx sheet dynamically with the data from DB using POI jar.
few thngs to be noted are :
1.Regardless of number of columns that are going to be added to the table in future, the java code should not be modified and should be able capable enough to genrate the .xlsx sheet .
2..xlsx sheet should also be able to generate multiple sheets .(mutiple sheets in workbook)

My Plan:
1.Use springRowmapper as we are using spring, which will loop through the result set metadata.
2. I will have one column in DB which will help us to segregate and helps to generate mutiple sheets.
3.Code in Rowmapper itself will generate .xlsx sheet using POI API.


Please suggest me if my idea makes sense? Thank you!!
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think it works. I have done something similar in the past with one enhancement:-

Use an excel sheet as a template to drive your code. Your user should be able to create a template excel file with multiple sheets. Each sheet should have a SQL statement on the first row, header on the second row, and column names on the third row. Your program can open the template, and for each sheet,
a) read the SQL statement, execute it,
b) read the second row, and put the headers in the output sheet,
c) read the columns named from third row. Now iterate over your result set and put the data from the columns specified in the template into your output sheet

If you like, you can allow your users to put excel formulas and values in the template too. You will have to find a way that you can identify which cells have column names. Maybe column named can be surrounded by ${....} You just copy the cells that don;t have column names

Needless to say, you should give this to only super-trusted users, because you don't want your users to run just about any SQL on your database. We used this to create a test framework for our testers. They excel template told our framework what the input and expected output was, and we used to pull data from the database into the sheet to compare actual output with expected output.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic