Skip to main content

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.
Labels:
Location: Bengaluru, Karnataka 560017, India

Comments

Post a Comment

Popular posts from this blog

JNDI configuration for Tomcat 9 with Oracle

In this article, I am going to place the required source code to get data from the table by using the JNDI configuration. Below are the environment details that I have configured currently. Windows - 7 Oracle - 10g Tomcat - 9 JDK - 8 Eclipse Oxygen Ojdbc6 jar required First, we need to create the Dynamic Web Project. If you don't know how to do <Click Here>. I have faced a lot of issues before getting the output like 405, No driver class to load, etc. I am using JSP & Servlets in the current explanation. Before started writing the application logic, we need to do the below configuration in the installed tomcat directory. Place OJDBC6.jar in the Tomcat LIB directory. Add this Resource under <GlobalNamingResources> in Server.xml file which is present under the conf directory in Tomcat. < Resource name = "jdbc/myoracle" global= "jdbc/myoracle" auth = "Container" type= "javax.sql.DataSource" driverClass...
Post a Comment
Read more

Prime, Fibonacci and Factorial number with example in java

Prime number, Fibonacci series and Factorial number programs are most commonly asked questions in interview. Read this article to know what is and how to write programs for prime number, fibonacci series and factorial number. Prime Number: prime number is natural number greater than 1 that has no positive divisor other than 1 and itself. A natural number greater than 1 is not a prime number, is called Composite number . For example, 7 is a prime number. Because it can divide with 1 and 7 only. Where as 8 is composite number. Since it has the divisor 2 and 4 in addition to the 1 and 8. The below example represents the finding the passing number is prime number or not. If the passing number is prime number it will print true otherwise it will print false. package com . javatbrains . practice ; public class PrimeNumber { public boolean isPrimeNumber ( int number ) { if ( number <= 1 ) return false ; // There's only one ...
1 comment
Read more

JVM, JRE and JDK in Java

JVM, JRE and JDK are the most basic common concepts to know in java. These are the basic features to understand how Java architecture works? JVM stands for Java Virtual Machine, which doesn't have any physical directories created in java installation. JRE stands for Java Runtime Environment, which creates the directory under Java installation path and also present in JDK. JDK stands for Java Development Kit, which creates the directory in Java installation path and also it has it's own JRE. Since we have already learn that Java is platform independent means if we have implemented any of the java class in one environment, it will be executed in any other environment and provides the same output. But, JVM, JRE and JDK all are platform dependent . So that, for windows, linux, unix, mac, solaris..etc has it's own JVM, JRE and JDK. One will be not compatible with other environments. While installing the Java, we might come to know a bit about JRE and JDK. But, JVM is the other...
8 comments
Read more