{"id":48941,"date":"2017-06-13T16:40:55","date_gmt":"2017-06-13T11:10:55","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=48941"},"modified":"2017-06-13T16:40:55","modified_gmt":"2017-06-13T11:10:55","slug":"report-generation-using-jxls-v2-2","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/report-generation-using-jxls-v2-2\/","title":{"rendered":"Report Generation Using Jxls v2"},"content":{"rendered":"<p style=\"text-align: justify\"><strong>What is Jxls?\u00a0<\/strong><\/p>\n<p style=\"text-align: justify\"><strong><span style=\"font-weight: 400\">Jxls is a Java library developed as a wrapper around existing open source projects known by the name of Apache POI and Java Excel API, to generated\u00a0<\/span><\/strong>Excel reports\u00a0<strong><span style=\"font-weight: 400\">quickly.\u00a0<\/span><\/strong><\/p>\n<p style=\"text-align: justify\"><strong>Why Jxls, if Apache POI is already there?<\/strong><\/p>\n<p style=\"text-align: justify\"><strong><span style=\"font-weight: 400\">We often come across a general scenario where we want a report to be highly presentable and crisp on information. For such data formatting and layout, one ends up with tonnes of code using low-level libraries like Apache POI or has to do it manually while generating such a\u00a0report. With Jxls one can provide all these formattings in the form of\u00a0an Excel template. Along with the layout details, this template contains instructions for data manipulation, coded in a special markup language for Excel sheets.\u00a0<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\">Apart from generating reports, it is also helpful in uploading bulk data using Excel.<\/span><\/strong><\/p>\n<p style=\"text-align: justify\"><strong>How does it work?<\/strong><\/p>\n<p style=\"text-align: justify\"><strong><span style=\"font-weight: 400\">For the sake of simplicity and existing user base, we will include dependencies for Apache POI only. Otherwise, we have an option to do the same for Java Excel API.<\/span><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><span style=\"font-weight: 400\">First of all, let us see how\u00a0the bulk upload will work.<\/span><\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li>Add these dependencies to your pom file.\n<pre><code><strong><span style=\"color: #003366\">&lt;dependency&gt;<\/span><\/strong>\r\n<strong><span style=\"color: #003366\">&lt;groupId&gt;<\/span><\/strong>org.jxls<strong><span style=\"color: #003366\">&lt;\/groupId&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;artifactId&gt;<\/strong><\/span>jxls-reader<span style=\"color: #003366\"><strong>&lt;\/artifactId&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;version&gt;<\/strong><\/span>2.0.2<strong><span style=\"color: #003366\">&lt;\/version&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;\/dependency&gt;<\/strong><\/span>\r\n <\/code><code>\r\n<span style=\"color: #003366\"><strong>&lt;dependency&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;groupId&gt;<\/strong><\/span>org.jxls<strong><span style=\"color: #003366\">&lt;\/groupId&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;artifactId&gt;<\/strong><\/span>jxls<span style=\"color: #003366\"><strong>&lt;\/artifactId&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;version&gt;<\/strong><\/span>2.2.6<strong><span style=\"color: #003366\">&lt;\/version&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;\/dependency&gt;<\/strong><\/span>\r\n <\/code><code>\r\n<span style=\"color: #003366\"><strong>&lt;dependency&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;groupId&gt;<\/strong><\/span>org.jxls<strong><span style=\"color: #003366\">&lt;\/groupId&gt;<\/span><\/strong>\r\n<strong><span style=\"color: #003366\">&lt;artifactId&gt;<\/span><\/strong>jxls-poi<span style=\"color: #003366\"><strong>&lt;\/artifactId&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;version&gt;<\/strong><\/span>1.0.5<strong><span style=\"color: #003366\">&lt;\/version&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;\/dependency&gt;<\/strong><\/span><\/code><\/pre>\n<\/li>\n<\/ul>\n<ul style=\"text-align: justify\">\n<li style=\"text-align: left\">Add XML configuration file which will help Jxls reader to map Excel data to Java class. The configuration will look like this:\n<pre><code><strong><span style=\"color: #003366\">&lt;?xml version<\/span><span style=\"color: #008000\">=\"1.0\"<\/span> <span style=\"color: #003366\">encoding<\/span><span style=\"color: #008000\">=\"ISO-8859-1\"<\/span><span style=\"color: #003366\">?&gt;<\/span><\/strong>\r\n<span style=\"color: #003366\"><strong>&lt;workbook&gt;<\/strong><\/span>\r\n <strong><span style=\"color: #003366\"> &lt;worksheet name<\/span><span style=\"color: #008000\">=\"Sheet1\"<\/span><span style=\"color: #003366\">&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">   &lt;section startRow<\/span><span style=\"color: #008000\">=\"1\"<\/span> <span style=\"color: #003366\">endRow<\/span><span style=\"color: #008000\">=\"3\"<\/span><span style=\"color: #003366\">&gt;<\/span><\/strong>\r\n <span style=\"color: #003366\"><strong>   &lt;\/section&gt;<\/strong><\/span>\r\n <strong><span style=\"color: #003366\">   &lt;loop startRow<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">endRow<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">items<\/span><span style=\"color: #008000\">=\"students\"<\/span> <span style=\"color: #003366\">var<\/span><span style=\"color: #008000\">=\"student\"<\/span> <span style=\"color: #003366\">varType<\/span><span style=\"color: #008000\">=\"ttn.example.Student\"<\/span><span style=\"color: #003366\">&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">     &lt;section startRow<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">endRow<span style=\"color: #008000\">=<\/span><\/span><span style=\"color: #008000\">\"4\"<\/span><span style=\"color: #003366\">&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<span style=\"color: #008000\">=<\/span><\/span><span style=\"color: #008000\">\"4\"<\/span> <span style=\"color: #003366\">col<span style=\"color: #008000\">=<\/span><\/span><span style=\"color: #008000\">\"0\"<\/span><span style=\"color: #003366\">&gt;<\/span>student.admissionNumber<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span>=<span style=\"color: #008000\">\"1\"<\/span>&gt;student.centerCode<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"2\"<\/span>&gt;student.admissionDate<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"3\"<\/span>&gt;student.firstName<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"4\"<\/span>&gt;student.lastName<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"5\"<\/span>&gt;student.dob<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"6\"<\/span>&gt;student.gender<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">       &lt;mapping row<\/span><span style=\"color: #008000\">=\"4\"<\/span> <span style=\"color: #003366\">col<\/span><span style=\"color: #008000\">=\"7\"<\/span><span style=\"color: #003366\">&gt;<\/span>student.nationality<span style=\"color: #003366\">&lt;\/mapping&gt;<\/span><\/strong>\r\n <span style=\"color: #003366\"><strong>     &lt;\/section&gt;<\/strong><\/span>\r\n <span style=\"color: #003366\"><strong>     &lt;loopbreakcondition&gt;<\/strong><\/span>\r\n <strong><span style=\"color: #003366\">       &lt;rowcheck offset<\/span><span style=\"color: #008000\">=\"0\"<\/span><span style=\"color: #003366\">&gt;<\/span><\/strong>\r\n <strong><span style=\"color: #003366\">         &lt;cellcheck offset<\/span><span style=\"color: #008000\">=\"0\"<\/span><span style=\"color: #003366\">&gt;&lt;\/cellcheck&gt;<\/span><\/strong>\r\n <span style=\"color: #003366\"><strong>       &lt;\/rowcheck&gt;<\/strong><\/span>\r\n <span style=\"color: #003366\"><strong>     &lt;\/loopbreakcondition&gt;<\/strong><\/span>\r\n <span style=\"color: #003366\"><strong>   &lt;\/loop&gt;<\/strong><\/span>\r\n <span style=\"color: #003366\"><strong> &lt;\/worksheet&gt;<\/strong><\/span>\r\n<span style=\"color: #003366\"><strong>&lt;\/workbook&gt;<\/strong><\/span><\/code><\/pre>\n<\/li>\n<\/ul>\n<ul style=\"text-align: justify\">\n<li>Create a Java Class Student with mentioned fields along with their getter and setter methods.<\/li>\n<\/ul>\n<ul style=\"text-align: justify\">\n<li>Code to read the uploaded Excel file.\n<pre><strong><span style=\"color: #003366\">public<\/span><\/strong> List importStudentRecords(MultipartFile file)\r\n{\r\n  Map&lt;String, ArrayList&gt; beans = <span style=\"color: #003366\"><strong>new<\/strong><\/span> HashMap&lt;&gt;();\r\n  beans.put(<span style=\"color: #008000\"><strong>\"students\"<\/strong><\/span>, new ArrayList());\r\n  <span style=\"color: #003366\"><strong>try<\/strong><\/span>\r\n  {\r\n    ReaderBuilder.buildFromXML(ExcelImportService.<span style=\"color: #003366\"><strong>class<\/strong><\/span>.getClassLoader()\r\n                 .getResourceAsStream(<span style=\"color: #008000\"><strong>\"student.xml\"<\/strong><\/span>))\r\n                 .read(new ByteArrayInputStream(file.getBytes()), beans);\r\n  }\r\n  <span style=\"color: #003366\"><strong>catch<\/strong><\/span> (Exception exp)\r\n  {\r\n    logger.error(<span style=\"color: #008000\"><strong>\"Error importing records : {}\"<\/strong><\/span>, exp);\r\n  }\r\n  <span style=\"color: #003366\"><strong>return<\/strong><\/span> beans.get(<strong><span style=\"color: #008000\">\"students\"<\/span><\/strong>);\r\n}\r\n<\/pre>\n<\/li>\n<\/ul>\n<ul style=\"text-align: justify\">\n<li>Your upload sheet will look like this.\n<pre><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-48942\" src=\"\/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-17-54-24.png\" alt=\"Screenshot from 2017-05-28 17-54-24\" width=\"824\" height=\"159\" srcset=\"\/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-17-54-24.png 824w, \/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-17-54-24-300x57.png 300w, \/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-17-54-24-624x120.png 624w\" sizes=\"(max-width: 824px) 100vw, 824px\" \/><\/pre>\n<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Now let us take a look at report generation.<\/p>\n<ul style=\"text-align: justify\">\n<li>The dependencies will remain same here. Your excel template will look like this.\n<pre><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-48944\" src=\"\/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-18-31-14_1.png\" alt=\"Screenshot from 2017-05-28 18-31-14_1\" width=\"775\" height=\"233\" srcset=\"\/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-18-31-14_1.png 775w, \/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-18-31-14_1-300x90.png 300w, \/blog\/wp-ttn-blog\/uploads\/2017\/05\/Screenshot-from-2017-05-28-18-31-14_1-624x187.png 624w\" sizes=\"(max-width: 775px) 100vw, 775px\" \/><\/pre>\n<ul>\n<li>The yellow coloured comment boxes define how the data should be parsed on the template.<\/li>\n<li>The first comment in cell A1 states the area to be parsed which is H5 here.<\/li>\n<li>The second comment in cell A5 defines an iterative instruction to print the student records.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul style=\"text-align: justify\">\n<li>Code to generate the Excel report.\n<pre>Path dirpath = Paths.get(exportDirectory);\r\nString filename = dirpath.resolve(UUID.randomUUID().toString() + <strong><span style=\"color: #008000\">\".xls\"<\/span><\/strong>).toString();\r\n<span style=\"color: #003366\"><strong>try<\/strong><\/span> (InputStream is = StudentService.<span style=\"color: #003366\"><strong>class<\/strong><\/span>.getClassLoader().getResourceAsStream(<span style=\"color: #008000\"><strong>\"student.xls\"<\/strong><\/span>))\r\n{\r\n<span style=\"color: #003366\"><strong> try<\/strong><\/span> (OutputStream os = <span style=\"color: #003366\"><strong>new<\/strong><\/span> FileOutputStream(filename))\r\n {\r\n   Context context = <span style=\"color: #003366\"><strong>new<\/strong><\/span> Context();\r\n   context.putVar(<span style=\"color: #008000\"><strong>\"students\"<\/strong><\/span>, students);\r\n   JxlsHelper.getInstance().processTemplate(is, os, context);\r\n }\r\n}\r\n<span style=\"color: #003366\"><strong>return<\/strong><\/span> filename;<\/pre>\n<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><a title=\"https:\/\/bitbucket.org\/shibizumastokar\/jxls\" href=\"https:\/\/bitbucket.org\/shibizumastokar\/jxls\"> Jxls working example<\/a><\/p>\n<p style=\"text-align: justify\">Hope this blog was instrumental in building an understanding of report\u00a0generation using Jxls, version 2 and how it can help i<strong><span style=\"font-weight: 400\">n uploading bulk data using Excel.\u00a0<\/span><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is Jxls?\u00a0 Jxls is a Java library developed as a wrapper around existing open source projects known by the name of Apache POI and Java Excel API, to generated\u00a0Excel reports\u00a0quickly.\u00a0 Why Jxls, if Apache POI is already there? We often come across a general scenario where we want a report to be highly presentable [&hellip;]<\/p>\n","protected":false},"author":981,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":85},"categories":[446],"tags":[4588,4589,4587,4603],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/48941"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/981"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=48941"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/48941\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=48941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=48941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=48941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}