1 Reply Latest reply: May 18, 2012 1:26 AM by 937323 RSS

    create new Excel sheet

    937323
      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 ");
           }
      }