How to generate excel sheet by using java

To generate an excel sheet by using java, we need to download the jxl.jar. This will help us to generate xls file by using predefined classes. For downloading jxl jar click here. This link will point to Maven repository from there you need to click on download button for downloading jar file.

When jxl jar downloading will get complete, add the jar to project class path by following these steps. Right click on project --> goto properties --> select java build path --> select libraries --> click on add external jars button to browse downloaded jar path --> select jar and click on open button --> Ok. This jar file will provide few classes to design the excel sheet. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
package com.thbs.java.jxl;

import java.io.File;

//Import the JExcel API
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/*
* Created By JavaTBrains
*
* This Java class is used to create an Excel File output
* from a given String
*
*/

public class CreateExcelSheet {

   public static void main(String[] args) {
     try {

     /*
     * The assumption is that the data has the field separator as comma
     * (,) and the end separator for each record is found by the literal
     * END
     */

     // Assume the below is the input file format
     String input = "100,subba reddy,MIS,Software Engineer END 200,Nallamachu S,Java,Programmer END 300,Reddy   Nallamachu,MISTechmologies,Project Manager END";

     int counter = 0;

     WritableWorkbook workbook = Workbook.createWorkbook(new File(
"E:/LOGS/excel.xls"));
     WritableSheet sheet = workbook.createSheet("Page1", 0);

     // Set Header

     String header[] = new String[4];
     header[0] = "Emp ID";
     header[1] = "Name";
     header[2] = "Department";
     header[3] = "Designation";

     // Setting Background colour for Cells
     Colour bckcolor = Colour.LIME;
     WritableCellFormat cellFormat = new WritableCellFormat();
     cellFormat.setBackground(bckcolor);

     // Setting Colour & Font for the Text

     WritableFont font = new WritableFont(WritableFont.ARIAL);
     font.setColour(Colour.BLACK);
     cellFormat.setFont(font);

     // Write the Header to the excel file
     for (int i = 0; i < header.length; i++) {
         Label label = new Label(i, 0, header[i]);
         sheet.addCell(label);
         WritableCell cell = sheet.getWritableCell(i, 0);
         cell.setCellFormat(cellFormat);
     }

     String inputArray[] = input.split("END");

     for (int i = 0; i < inputArray.length; i++) {

         counter++;

         String fieldValuesArray[] = inputArray[i].split(",");

         for (int j = 0; j < fieldValuesArray.length; j++) {

             Label label = new Label(j, counter,
             fieldValuesArray[j].toString());
             sheet.addCell(label);
         }
     }

      workbook.write();
      workbook.close();

     } catch (Exception e) {
     /*
     * Write your logic to handle exception. For a module,
     * recommendation is to write out trace/log messages so that  error
     * tracking is easy via the adapter logs
     */
     }
   }
}

When we will execute above source code in our local, it will generates an excel sheet with few values which we added in a String class. String value will separate the line where all the END word is present. Then each line will divide by (,) to peace of words, that will treat as the column values.

Now, lets come into the excel design.
  • In this article we are using the Workbook class which will write xls file.
  • WritableWorkbook is one more class which will creates .xls file. 
  • WorkSheet class will represents the sheets inside the .xls file. 
  • Color, WritableFont and WritableCellFormat classes will help us to design cells/Font into custom format.
Location: Bengaluru, Karnataka 560017, India

Comments

Post a Comment

Popular posts from this blog

how to count the page views by using JSP

To count no.of page's are viewed by user in your application is a challenge in real time application. In this page we will see how to count the page views when user has viewed the page.           Here, we will do one example how many times user has visited each page in our application. In this post we will come to know, how to count the total application page views as well as individually each pages count.           Let us assume my web application name is "Page Counter". For creating the web application in eclipse IDE, Click here . This Page Counter contains three pages, assume that are the different pages in an application. But, there is the link between each other files for navigating from one page to another. Also it will show the total pages view count and individual page view count in all the pages. Let us see, below is my first jsp file,           You can declare the below content inside the < body > tag. Rest of the content in every page will b
Read more

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException error we got in Hibernate 4.3 to work with my MySQL database. We are using hibernate code generation tool in Eclipse and we are able to connect to the database using the Hibernate configuration file. When we try to run code in my Main class application is connecting to the database, but transaction is failing and we got the following error: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Exception in thread "main" java . lang . NoClassDefFoundError : javax / transaction / SystemException at java . lang . Class . forName0 ( Native Method ) at java . lang . Class . forName ( Unknown Source ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2248 ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2214 ) at org . hibernate . cfg . Configuration .< clinit >( Configuration . java : 184 ) at be . comp . permanenti
Read more

Multithreading in java with example

Multithreading  is one of the most important concept in core java. In this article we will learn what is multithreading? , what is the use of it? and What is the use of Synchronization and when to use it?  with detailed examples. At a time, two or more threads are accessing the same object is called as Multithreading  in Java .  First, we will create two threads for two objects. It is also possible to run two or more threads on a single class object. In this case, there is a possibility to get unreliable results. If the two threads are perform same task, then they need same object to be executed each time. For your better understanding, take an example of any reservations like, railway, movie ticket booking,etc. Let us think only one berth is available in a train and two passengers are asking for that berth. The first person has sent a request to allocate that ticket/berth to him. At the same time, the second person also sent a request to allocate that ticket/berth to him
Read more