This discussion is archived
1 Reply Latest reply: May 17, 2012 11:26 PM by 937323 RSS

create new Excel sheet

937323 Newbie
Currently Being Moderated
I want to merge 2 rows of first column and also merge the columns of first row need some help.

Eg Code:

package com.hcl.mcmp.ui.ECPObjectConfiguration;

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

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
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 WriteExcel {

     private WritableCellFormat timesBoldUnderline;
     private WritableCellFormat times;
     private String inputFile;
     
public void setOutputFile(String inputFile) {
     this.inputFile = inputFile;
     }

     public void write() throws IOException, WriteException {
          File file = new File(inputFile);
          WorkbookSettings wbSettings = new WorkbookSettings();

          wbSettings.setLocale(new Locale("en", "EN"));

          WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
          workbook.createSheet("Source", 0);
          workbook.createSheet("Target", 1);
          WritableSheet excelSheet = workbook.getSheet(0);
          createLabel(excelSheet);
          createContent(excelSheet);

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

     private void createLabel(WritableSheet sheet)
               throws WriteException {
          // Lets create a times font
          WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
          // Define the cell format
          times = new WritableCellFormat(times10pt);
          // Lets automatically wrap the cells
          times.setWrap(true);

          // Create create a bold font with unterlines
          WritableFont times10ptBoldUnderline = new WritableFont(
                    WritableFont.TIMES, 10, WritableFont.BOLD, false,
                    UnderlineStyle.SINGLE);
          timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
          // Lets automatically wrap the cells
          timesBoldUnderline.setWrap(true);

          CellView cv = new CellView();
          cv.setFormat(times);
          cv.setFormat(timesBoldUnderline);

          // Write a few headers
          addCaption(sheet, 0, 0, "Header 1");
          addCaption(sheet, 3, 0, "This is another header");
          addCaption(sheet, 1, 1, "This is sub header");
          addCaption(sheet, 2, 1, "This is sub header");
          addCaption(sheet, 3, 1, "This is sub header");
          addCaption(sheet, 4, 1, "This is sub header");
          addCaption(sheet, 5, 1, "This is sub header");

     }

     private void createContent(WritableSheet sheet) throws WriteException,
               RowsExceededException {
          // Write a few number
          for (int i = 2; i < 10; i++) {
               // First column
               addNumber(sheet, 1, i, i + 10);
               // Second column
               addNumber(sheet, 2, i, i * i);
               addNumber(sheet, 3, i, i * i);
               addNumber(sheet, 4, i, i * i);
               addNumber(sheet, 5, i, i * i);
          }
          
     }

     private void addCaption(WritableSheet sheet, int column, int row, String s)
               throws RowsExceededException, WriteException {
          Label label;
          label = new Label(column, row, s, timesBoldUnderline);
          sheet.addCell(label);
//     sheet.mergeCells(arg0, arg1, arg2, arg3)
     }

     private void addNumber(WritableSheet sheet, int column, int row,
               Integer integer) throws WriteException, RowsExceededException {
          Number number;
          number = new Number(column, row, integer, times);
          sheet.addCell(number);
     }


     public static void main(String[] args) throws WriteException, IOException {
          WriteExcel test = new WriteExcel();
          test.setOutputFile("d:/temp/lars.xls");
          test.write();
          System.out
                    .println("Please check the result file under c:/temp/lars.xls ");
     }
}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points