Java Excel Manipulation

We can use jxl library to deal with excel docs

import java.io.File;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

import jxl.CellView;
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
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 MyDemo {

 public MyDemo() {

 }

 public void writeToExcel() throws IOException, RowsExceededException,
   WriteException {
  // create a workbook
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/myDemoExcel.xls"));

  // create a new sheet inside the workbook
  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  // instantiate a new label object
  Label cellLabel = new Label(0, 1, "This is a cell");
  // now add the cell object to sheet
  sheet.addCell(cellLabel);

  // instantiate some number objects
  Number cellNumber1 = new Number(0, 2, 3.145636454);
  Number cellNumber2 = new Number(0, 3, 4500000000.0);

  sheet.addCell(cellNumber1);
  sheet.addCell(cellNumber2);

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

  System.out.println("Excel sheet created successfully!");
 }

 public void writeToExcelFormatted() throws IOException,
   RowsExceededException, WriteException {
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/Formatted DemoExcel.xls"));

  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  WritableFont arial14Font = new WritableFont(WritableFont.ARIAL, 14);

  WritableCellFormat writableCellFormat = new WritableCellFormat(
    arial14Font);
  writableCellFormat.setWrap(true);

  Label cellLabel1 = new Label(0, 1, "This is a formatted cell",
    writableCellFormat);

  Label cellLabel2 = new Label(1, 2, "This is an another formatted cell",
    writableCellFormat);

  sheet.addCell(cellLabel1);
  sheet.addCell(cellLabel2);

  // specify the format of the number
  NumberFormat myNumberFormat = new NumberFormat("#.####");
  // specify Font+number format object
  WritableCellFormat cellFormatForNumbers = new WritableCellFormat(
    arial14Font, myNumberFormat);

  // initiate number
  Number num1 = new Number(2, 1, 3.12456346534634, cellFormatForNumbers);
  Number num2 = new Number(2, 2, 343.3454823452318, cellFormatForNumbers);

  // add numbers to sheet
  sheet.addCell(num1);
  sheet.addCell(num2);

  // set auto size for column 0
  CellView cellView = sheet.getColumnView(0);
  cellView.setAutosize(true);
  sheet.setColumnView(0, cellView);

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

  System.out.println("Excel sheet created successfully!");
 }

 public void createExcelDocumentPattern() throws IOException,
   RowsExceededException, WriteException {
  WritableWorkbook workbook = Workbook.createWorkbook(new File(
    "C:/Users/Kanishka/Desktop/Formatted DemoExcel Pattern.xls"));

  WritableSheet sheet = workbook.createSheet("Sheet 01", 0);

  WritableFont arial14Font = new WritableFont(WritableFont.TIMES, 10);
  WritableFont arial12FontBold = new WritableFont(WritableFont.ARIAL, 12,
    WritableFont.BOLD, false);

  WritableCellFormat writableCellFormat = new WritableCellFormat(
    arial14Font);
  WritableCellFormat writableCellFormatHeader = new WritableCellFormat(
    arial12FontBold);

  Label cellHeaderStringCol = new Label(0, 0, "String Column",
    writableCellFormatHeader);
  Label cellHeaderNumCol1 = new Label(1, 0, "Number Column 1",
    writableCellFormatHeader);
  Label cellHeaderNumCol2 = new Label(2, 0, "Number Column 2",
    writableCellFormatHeader);
  Label cellHeaderNumCol3 = new Label(3, 0, "Number Column 3",
    writableCellFormatHeader);
  Label cellHeaderNumCol4 = new Label(4, 0, "Number Column 4",
    writableCellFormatHeader);
  Label cellHeaderNumCol5 = new Label(5, 0, "Date Column",
    writableCellFormatHeader);

  sheet.addCell(cellHeaderStringCol);
  sheet.addCell(cellHeaderNumCol1);
  sheet.addCell(cellHeaderNumCol2);
  sheet.addCell(cellHeaderNumCol3);
  sheet.addCell(cellHeaderNumCol4);
  sheet.addCell(cellHeaderNumCol5);

  NumberFormat numberFormat5DecPnts = new NumberFormat("#.#####");
  WritableCellFormat numberCellFormat = new WritableCellFormat(
    arial14Font, numberFormat5DecPnts);
  WritableCellFormat numberCellFormatIntegers = new WritableCellFormat(
    NumberFormats.INTEGER);

  for (int i = 1; i < 100; i++) {
   String rowString = "This is row " + i;
   double randNum1 = new Random(i).nextDouble() + 1;
   double randNum2 = new Random(i).nextDouble() + 10;
   double randNum3 = new Random(i).nextDouble() + 2;
   int randNum4 = new Random(i).nextInt();

   Label stringLbl = new Label(0, i, rowString, writableCellFormat);
   Number num1 = new Number(1, i, randNum1, numberCellFormat);
   Number num2 = new Number(2, i, randNum2, numberCellFormat);
   Number num3 = new Number(3, i, randNum3, numberCellFormat);
   Number num4 = new Number(4, i, randNum4, numberCellFormatIntegers);

   // sample dates
   Date now = Calendar.getInstance().getTime();
   DateFormat customDateFormat = new DateFormat("dd MMM yyyy hh:mm:ss");
   WritableCellFormat dateFormat = new WritableCellFormat(
     customDateFormat);
   DateTime dateCell = new DateTime(5, i, now, dateFormat);
   sheet.addCell(dateCell);

   sheet.addCell(stringLbl);
   sheet.addCell(num1);
   sheet.addCell(num2);
   sheet.addCell(num3);
   sheet.addCell(num4);

  }

  // set autosize for columns
  for (int i = 0; i < 6; i++) {
   CellView cv = sheet.getColumnView(i);
   cv.setAutosize(true);
   sheet.setColumnView(i, cv);
  }

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

  System.out.println("Excel document generated successfully!");

 }

}