Enlightensoft's Blog

Helping in your each step

  • Categories

  • Authors

Export to .xls using Core JAVA

Posted by Pankil Patel on June 22, 2012

Required JAR: jxl-2.6.12.jar

File: XlsCreator.java

package co.cc.enlightensoft.xls;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class XlsCreator {
private static WritableSheet s;
private static WritableCellFormat cf;
private static WritableCellFormat cfd;
private static File file;
public static int NUMBER_OF_TIME_QUERY_EXICUTION = 2;

public static WritableWorkbook createFile() throws IOException,
WriteException {
String filename = “QueryResponceTime.xls”;
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale(“en”, “EN”));
XlsCreator.file = new File(filename);
WritableWorkbook workbook = Workbook
.createWorkbook(XlsCreator.file, ws);
XlsCreator.s = workbook.createSheet(“Sheet1”, 0);
// XlsCreator.summary = workbook.createSheet(“Output”, 1);

/* Format the Font */
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD);
WritableFont wfd = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD);

XlsCreator.cf = new WritableCellFormat(wf);
XlsCreator.cfd = new WritableCellFormat(wfd);

XlsCreator.cf.setWrap(true);
XlsCreator.cfd.setWrap(false);
XlsCreator.cf.setBackground(Colour.AQUA);
XlsCreator.cf.setBorder(Border.ALL, BorderLineStyle.THICK);
XlsCreator.cfd.setBorder(Border.ALL, BorderLineStyle.MEDIUM);

XlsCreator.s.setColumnView(0, 30);
XlsCreator.s.setColumnView(1, 30);
XlsCreator.s.setColumnView(2, 30);

createHeader();
return workbook;
}

public static void closeFile(WritableWorkbook workbook) throws IOException,
WriteException {
workbook.write();
workbook.close();
}

public static void createHeader() throws RowsExceededException,
WriteException {
/* Creates Label and writes date to one cell of sheet */
Label oid = new Label(0, 0, “OID”, XlsCreator.cf);
Label totalTime = new Label(1, 0, “Total Time (Milli Second)”,
XlsCreator.cf);
Label queryTime = new Label(2, 0, “Query Time (Milli Second)”,
XlsCreator.cf);
XlsCreator.s.addCell(oid);
XlsCreator.s.addCell(totalTime);
XlsCreator.s.addCell(queryTime);
}

public static void addRow(long oid, long totalTime, long queryTime,
int index) throws RowsExceededException, WriteException {
Number c1_data = new Number(0, index, oid, cfd);
Number c2_data = new Number(1, index, totalTime, cfd);
Number c3_data = new Number(2, index, queryTime, cfd);
XlsCreator.s.addCell(c1_data);
XlsCreator.s.addCell(c2_data);
XlsCreator.s.addCell(c3_data);
}

public static void main(String[] args) throws WriteException, IOException {
WritableWorkbook wb = XlsCreator.createFile();
addRow(123, 456, 001, 1);
addRow(52468, 123, 010, 2);
addSummary(3);
XlsCreator.closeFile(wb);
System.out.println(“.xls file is created… \n Location: ”
+ XlsCreator.file.getAbsoluteFile());
}

public static void addSummary(int index) throws RowsExceededException,
WriteException {
String avg = “AVERAGE”;
String avgTotalTime = “AVERAGE(B2:B”
+ (NUMBER_OF_TIME_QUERY_EXICUTION + 1) + “)”;
String avgQueryTime = “AVERAGE(C2:C”
+ (NUMBER_OF_TIME_QUERY_EXICUTION + 1) + “)”;

// Create label for average
Label formulaLabel = new Label(0, index, avg, cf);
XlsCreator.s.addCell(formulaLabel);

// Create a formula for average
Formula formulaAvgTotalTime = new Formula(1, index, avgTotalTime, cf);
XlsCreator.s.addCell(formulaAvgTotalTime);

// Create a formula for average
Formula formulaAvgQueryTime = new Formula(2, index, avgQueryTime, cf);
XlsCreator.s.addCell(formulaAvgQueryTime);

String min = “MIN”;
String minTotalTime = “MIN(B2:B” + (NUMBER_OF_TIME_QUERY_EXICUTION + 1)
+ “)”;
String minQueryTime = “MIN(C2:C” + (NUMBER_OF_TIME_QUERY_EXICUTION + 1)
+ “)”;

// Create label for average
Label minLabel = new Label(0, index + 1, min, cf);
XlsCreator.s.addCell(minLabel);

// Create a formula for average
Formula formulaMinTotalTime = new Formula(1, index + 1, minTotalTime,
cf);
XlsCreator.s.addCell(formulaMinTotalTime);

// Create a formula for average
Formula formulaMinQueryTime = new Formula(2, index + 1, minQueryTime,
cf);
XlsCreator.s.addCell(formulaMinQueryTime);

String max = “MAX”;
String maxTotalTime = “MAX(B2:B” + (NUMBER_OF_TIME_QUERY_EXICUTION + 1)
+ “)”;
String maxQueryTime = “MAX(C2:C” + (NUMBER_OF_TIME_QUERY_EXICUTION + 1)
+ “)”;

// Create label for average
Label maxLabel = new Label(0, index + 2, max, cf);
XlsCreator.s.addCell(maxLabel);

// Create a formula for average
Formula formulaMaxTotalTime = new Formula(1, index + 2, maxTotalTime,
cf);
XlsCreator.s.addCell(formulaMaxTotalTime);

// Create a formula for average
Formula formulaMaxQueryTime = new Formula(2, index + 2, maxQueryTime,
cf);
XlsCreator.s.addCell(formulaMaxQueryTime);

}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: