Java Examples for jxl.format.Alignment

public WritableCellFormat getDateCellFormat() throws WriteException {
    if (dateCellFormat == null) {
        DateFormat customDateFormat = new DateFormat(ProjectConfig.getProperty("format.date"));
        dateCellFormat = new WritableCellFormat(customDateFormat);
        dateCellFormat.setFont(new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK));
        dateCellFormat.setAlignment(jxl.format.Alignment.CENTRE);
    }
    return dateCellFormat;
}

/**
     * It creates excel file and download it.
     *
     * @author minikim
     * @param request http servlet request.
     * @param response http servlet response.
     * @exception ServletException when it has servlet error.
     * @exception IOException when it has IO error.
     *
     */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String[] htmldata = request.getParameterValues("htmldata");
    String[] sheetName = request.getParameterValues("sheetname");
    String currentMenu = request.getParameter("currentMenu");
    String sectionMenu = request.getParameter("sectionMenu");
    String appkey = request.getParameter("appkey");
    if (sheetName == null) {
        sheetName = new String[1];
        sheetName[0] = currentMenu;
    }
    // variable that create excel files
    WritableWorkbook workbook = null;
    // set filename.
    String filename = "Fingraph_" + appkey + "_" + sectionMenu + "_" + currentMenu + "_" + DateTimeUtil.getTodayFormatString("yyyyMMdd_HHmmss") + ".xls";
    try {
        // set filepath.
        workbook = Workbook.createWorkbook(new File(uploadTempPath + filename));
        for (int k = 0; k < htmldata.length; k++) {
            // create first excel's sheet.
            WritableSheet sheet = workbook.createSheet(sheetName[k], k);
            sheet = workbook.getSheet(k);
            // Set style of the excel.
            WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false);
            // Create style object to use on the rows and columns.
            // Column Style
            jxl.write.WritableCellFormat ColFormat = new WritableCellFormat(TitleFont);
            // Column Style 2
            jxl.write.WritableCellFormat ColFormatTop = new WritableCellFormat(TitleFont);
            jxl.write.WritableCellFormat RowFormat = new // Row Style
            WritableCellFormat();
            // Row Style Center
            jxl.write.WritableCellFormat RowFormatCenter = new WritableCellFormat();
            // insert comma at every unit of thousand.
            jxl.write.NumberFormat moneytype1 = new NumberFormat("###,##0");
            jxl.write.NumberFormat moneytype2 = new NumberFormat("###,##0.00");
            jxl.write.WritableCellFormat format_integer1 = new WritableCellFormat(moneytype1);
            jxl.write.WritableCellFormat format_integer2 = new WritableCellFormat(moneytype2);
            // Set detail of rows and columns (Border,Align,Background...)
            ColFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            ColFormat.setAlignment(jxl.format.Alignment.CENTRE);
            ColFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            ColFormat.setBackground(jxl.format.Colour.ICE_BLUE);
            ColFormatTop.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            ColFormatTop.setAlignment(jxl.format.Alignment.CENTRE);
            ColFormatTop.setVerticalAlignment(VerticalAlignment.CENTRE);
            ColFormatTop.setBackground(jxl.format.Colour.ORANGE);
            RowFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            RowFormat.setAlignment(jxl.format.Alignment.LEFT);
            RowFormat.isNumber();
            format_integer1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            format_integer1.setAlignment(jxl.format.Alignment.RIGHT);
            format_integer2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            format_integer2.setAlignment(jxl.format.Alignment.RIGHT);
            RowFormatCenter.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            RowFormatCenter.setAlignment(jxl.format.Alignment.CENTRE);
            Document doc = Jsoup.parse(URLDecoder.decode(htmldata[k], "UTF-8"));
            Elements trs = doc.getElementsByTag("tr");
            jxl.write.Number num = null;
            int first = 1;
            int i = 0;
            int j = 0;
            for (Element tr : trs) {
                Elements tds = null;
                if (first == 1) {
                    first = 0;
                    tds = tr.getElementsByTag("th");
                    if (tds != null) {
                        for (Element td : tds) {
                            sheet.addCell(new Label(j, i, td.text(), ColFormat));
                            sheet.setColumnView(j, Integer.parseInt(td.attr("width")) / 2);
                            j++;
                        }
                    }
                } else {
                    tds = tr.getElementsByTag("td");
                    if (tds != null) {
                        for (Element td : tds) {
                            if ("numFormat".equals(td.attr("class"))) {
                                num = new jxl.write.Number(j, i, Double.parseDouble(td.text()), format_integer1);
                                sheet.addCell(num);
                            } else if ("doubleFormat".equals(td.attr("class"))) {
                                num = new jxl.write.Number(j, i, Double.parseDouble(td.text()), format_integer2);
                                sheet.addCell(num);
                            } else {
                                sheet.addCell(new Label(j, i, td.text(), RowFormat));
                            }
                            j++;
                        }
                    }
                }
                i++;
                j = 0;
            }
        }
        // write to excel file.
        workbook.write();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (workbook != null)
                workbook.close();
        } catch (Exception ignored) {
        }
    }
    // create file.
    File f = new File(uploadTempPath + filename);
    // set for download excel file.
    String contentType = request.getContentType();
    response.setContentType("x-msdownload");
    if (contentType == null) {
        if (request.getHeader("user-agent").indexOf("MSIE 5.5") != -1)
            response.setContentType("doesn/matter;");
        else
            response.setContentType("application/octet-stream");
    } else {
        response.setContentType(contentType);
    }
    response.setHeader("Content-Transfer-Encoding:", "binary");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ";");
    response.setHeader("Content-Length", "" + f.length());
    response.setHeader("Pragma", "no-cache;");
    response.setHeader("Expires", "-1;");
    byte b[] = new byte[1024];
    BufferedInputStream fin = new BufferedInputStream(new FileInputStream(f));
    BufferedOutputStream outs = new BufferedOutputStream(response.getOutputStream());
    try {
        int read = 0;
        while ((read = fin.read(b)) != -1) {
            outs.write(b, 0, read);
        }
    } catch (Exception e) {
    } finally {
        if (outs != null)
            outs.close();
        if (fin != null)
            fin.close();
    }
    try {
        if (f.exists())
            f.delete();
    } catch (Exception e) {
    }
}

public static void doExport_Excel(String fileName, String[] titleList, List list, HttpServletResponse response) {
    /*
			if(list== null || list.size()<=0  ){
				//System.out.println("print");
				try {
					
					response.setContentType("text/html;charset=utf-8");
					response.getWriter().write("<script>alert(\"记录不存在\");</script>");
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		 */
    Date aaa = new Date();
    SimpleDateFormat aSimpleDateFormat = new java.text.SimpleDateFormat("yyyy年MM月dd日hh时mm分");
    SimpleDateFormat aSimpleDateFormat1 = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");
    String todayStr = df.format(new Date());
    String today = aSimpleDateFormat.format(aaa);
    String today1 = aSimpleDateFormat1.format(aaa);
    try {
        OutputStream os = response.getOutputStream();
        String localFileName = fileName;
        //处理中文文件名的问题 
        fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
        //处理中文文件名的问题
        fileName = new String(fileName.getBytes("UTF-8"), "GBK");
        response.setContentType("application/vnd.ms-excel;");
        response.setHeader("Content-disposition", "attachment; filename=\"" + fileName + "_" + todayStr + ".xls\"");
        // 开始写入excel
        // 加标题
        // 标题字体
        jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.NO_BOLD, true);
        jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        // 字段字体
        jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);
        jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);
        wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);
        wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        // 结果字体
        jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        WritableWorkbook wbook = Workbook.createWorkbook(os);
        // 写sheet名称
        WritableSheet wsheet = wbook.createSheet(localFileName, 0);
        int i = 3;
        for (int m = 0; m < titleList.length; m++) {
            wsheet.setColumnView(m, 30);
        }
        // 加入字段名
        for (int n = 0; n < titleList.length; n++) {
            wsheet.addCell(new jxl.write.Label(n, 3, titleList[n], wcfFC1));
        }
        // 加入标题
        wsheet.mergeCells(0, 0, i - 1, 0);
        wsheet.addCell(new Label(0, 0, localFileName, wcfFC));
        // 加入打印时间
        wsheet.addCell(new Label(i - 2, 1, "打印日期:"));
        wsheet.addCell(new Label(i - 1, 1, today));
        // 写入流中
        StringBuilder XML = new StringBuilder();
        int row = 0;
        int m = 0;
        long revctime = 0;
        long sendtime = 0;
        int compare = 0;
        String str = "";
        for (int r = 0; r < list.size(); r++) {
            Object[] obj = (Object[]) list.get(r);
            for (int x = 0; x < titleList.length; x++) {
                wsheet.addCell(new jxl.write.Label(x, row + 4, obj[x] == null ? " " : obj[x].toString(), wcfFC1));
            }
            row++;
        }
        wbook.write();
        wbook.close();
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static WritableCellFormat getAlignment(int stepValue, WritableCellFormat format) throws WriteException {
    if (stepValue != ExcelOutputMeta.FONT_ALIGNMENT_LEFT) {
        switch(stepValue) {
            case ExcelOutputMeta.FONT_ALIGNMENT_RIGHT:
                format.setAlignment(jxl.format.Alignment.RIGHT);
                break;
            case ExcelOutputMeta.FONT_ALIGNMENT_CENTER:
                format.setAlignment(jxl.format.Alignment.CENTRE);
                break;
            case ExcelOutputMeta.FONT_ALIGNMENT_FILL:
                format.setAlignment(jxl.format.Alignment.FILL);
                break;
            case ExcelOutputMeta.FONT_ALIGNMENT_GENERAL:
                format.setAlignment(jxl.format.Alignment.GENERAL);
                break;
            case ExcelOutputMeta.FONT_ALIGNMENT_JUSTIFY:
                format.setAlignment(jxl.format.Alignment.JUSTIFY);
                break;
            default:
                break;
        }
    }
    return format;
}

@Override
protected Integer doInBackground(String... params) {
    try {
        WritableCellFormat format = ZzFormatCreator.getInstance().createCellFont(WritableFont.ARIAL).setAlignment(Alignment.CENTRE, VerticalAlignment.CENTRE).setFontSize(14).setFontColor(Colour.ROSE).getCellFormat();
        ZzExcelCreator.getInstance().openExcel(new File(PATH + fileName + ".xls")).openSheet(0).fillNumber(Integer.parseInt(col), Integer.parseInt(row), Double.parseDouble(str), format).close();
        return 1;
    } catch (IOExceptionWriteException | BiffException |  e) {
        e.printStackTrace();
        return 0;
    }
}

@SuppressWarnings("unchecked")
public static File writeExcel(File file, List<Map> datas, ExcelCellMeta[] columns) throws Exception {
    jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(file);
    jxl.write.WritableSheet ws = wwb.createSheet("sheet1", 0);
    jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
    jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
    wcfFC.setBackground(Colour.GRAY_25);
    wcfFC.setAlignment(Alignment.CENTRE);
    try {
        for (int i = 0; i < columns.length; i++) {
            jxl.write.Label label = new jxl.write.Label(i, 0, columns[i].getContent(), wcfFC);
            ws.addCell(label);
            ws.setColumnView(i, columns[i].getWidth());
        }
        int index = 1;
        for (Map data : datas) {
            for (int i = 0; i < columns.length; i++) {
                Object o = data.get(columns[i].getName());
                if (o == null) {
                    Blank blank = new Blank(i, index);
                    ws.addCell(blank);
                } else if (o instanceof String) {
                    String content = o.toString();
                    Label label = new Label(i, index, content);
                    ws.addCell(label);
                } else if (o instanceof Integer) {
                    Number number = new Number(i, index, (Integer) o);
                    ws.addCell(number);
                } else if (o instanceof Double) {
                    Number number = new Number(i, index, (Double) o);
                    ws.addCell(number);
                } else if (o instanceof Long) {
                    Number number = new Number(i, index, (Long) o);
                    ws.addCell(number);
                } else if (o instanceof Float) {
                    Number number = new Number(i, index, (Float) o);
                    ws.addCell(number);
                }
            }
            index++;
        }
        wwb.write();
    } finally {
        wwb.close();
    }
    return file;
}

public void toExportExcel(OutputStream os) throws RowsExceededException, WriteException, IOException {
    //建立excel文件
    jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os);
    //sheet名称
    jxl.write.WritableSheet wsheet = wbook.createSheet("第一页", 0);
    WritableCellFormat cellFormatNumber = new WritableCellFormat();
    cellFormatNumber.setAlignment(Alignment.RIGHT);
    if (myStatisticData != null) {
        this.statisticData = myStatisticData;
    }
    if (myFormatDouble != null) {
        this.formatDouble = myFormatDouble;
    }
    double dataSum = getAllDataSum();
    //针对行处理
    Set sRowKeyTemp = getRowKeySet();
    Set sRowKey = new RmSequenceSet();
    for (int i = 0; rowKeyFieldAll != null && i < rowKeyFieldAll.length; i++) {
        sRowKey.add(rowKeyFieldAll[i]);
    }
    for (Iterator itSRowKeyTemp = sRowKeyTemp.iterator(); itSRowKeyTemp.hasNext(); ) {
        String tempStr = itSRowKeyTemp.next().toString();
        if (!sRowKey.contains(tempStr)) {
            sRowKey.add(tempStr);
        }
    }
    //针对列处理
    Set sColumnKeyTemp = getColumnKeySet();
    Set sColumnKey = new RmSequenceSet();
    for (int i = 0; columnKeyFieldAll != null && i < columnKeyFieldAll.length; i++) {
        sColumnKey.add(columnKeyFieldAll[i]);
    }
    for (Iterator itSColumnKeyTemp = sColumnKeyTemp.iterator(); itSColumnKeyTemp.hasNext(); ) {
        String tempStr = itSColumnKeyTemp.next().toString();
        if (!sColumnKey.contains(tempStr)) {
            sColumnKey.add(tempStr);
        }
    }
    //开始处理输出html代码
    int rowIndex = 0;
    int columnIndex = 0;
    wsheet.addCell(new Label(columnIndex++, rowIndex, rowColumnKeyFieldDisplay));
    for (Iterator itSColumnKey = sColumnKey.iterator(); itSColumnKey.hasNext(); ) {
        //循环列
        String tempColumnValue = String.valueOf(itSColumnKey.next());
        wsheet.addCell(new Label(columnIndex++, rowIndex, tempColumnValue));
    }
    wsheet.addCell(new Label(columnIndex++, rowIndex, "合计"));
    wsheet.addCell(new Label(columnIndex++, rowIndex, "所占份额"));
    //开始行循环
    for (Iterator itSRowKey = sRowKey.iterator(); itSRowKey.hasNext(); ) {
        //循环列
        rowIndex++;
        columnIndex = 0;
        String tempRowValue = String.valueOf(itSRowKey.next());
        wsheet.addCell(new Label(columnIndex++, rowIndex, tempRowValue));
        for (Iterator itSColumnKey = sColumnKey.iterator(); itSColumnKey.hasNext(); ) {
            //循环列
            String tempColumnValue = String.valueOf(itSColumnKey.next());
            double thisSum = getDataSum(tempRowValue, tempColumnValue);
            wsheet.addCell(new Label(columnIndex++, rowIndex, formatDouble.formatDouble(thisSum), cellFormatNumber));
        }
        wsheet.addCell(new Label(columnIndex++, rowIndex, formatDouble.formatDouble(getRowDataSum(tempRowValue)), cellFormatNumber));
        wsheet.addCell(new Label(columnIndex++, rowIndex, RmStringHelper.getPercentage(getRowDataSum(tempRowValue), dataSum), cellFormatNumber));
    }
    //合计行
    rowIndex++;
    columnIndex = 0;
    wsheet.addCell(new Label(columnIndex++, rowIndex, "合计"));
    for (Iterator itSColumnKey = sColumnKey.iterator(); itSColumnKey.hasNext(); ) {
        //循环列
        String tempColumnValue = String.valueOf(itSColumnKey.next());
        wsheet.addCell(new Label(columnIndex++, rowIndex, formatDouble.formatDouble(getColumnDataSum(tempColumnValue)), cellFormatNumber));
    }
    wsheet.addCell(new Label(columnIndex++, rowIndex, formatDouble.formatDouble(dataSum), cellFormatNumber));
    wsheet.addCell(new Label(columnIndex++, rowIndex, ""));
    //所占份额行
    rowIndex++;
    columnIndex = 0;
    wsheet.addCell(new Label(columnIndex++, rowIndex, "所占份额"));
    for (Iterator itSColumnKey = sColumnKey.iterator(); itSColumnKey.hasNext(); ) {
        //循环列
        String tempColumnValue = String.valueOf(itSColumnKey.next());
        wsheet.addCell(new Label(columnIndex++, rowIndex, RmStringHelper.getPercentage(getColumnDataSum(tempColumnValue), dataSum), cellFormatNumber));
    }
    wsheet.addCell(new Label(columnIndex++, rowIndex, ""));
    wsheet.addCell(new Label(columnIndex++, rowIndex, "100%", cellFormatNumber));
    wbook.write();
    if (wbook != null) {
        wbook.close();
    }
}

/**
     * In order to set text to bold, red, or green, we need to create cell
     * formats for each style.
     */
private void createCellFormats() {
    // Insert a dummy empty cell, so we can obtain its cell. This allows to
    // start with a default cell format.
    Label cell = new Label(0, 0, " ");
    CellFormat cellFormat = cell.getCellFormat();
    try {
        // Center all cells.
        mDefaultFormat = new WritableCellFormat(cellFormat);
        mDefaultFormat.setAlignment(Alignment.CENTRE);
        // Create the bold format
        final WritableFont boldFont = new WritableFont(cellFormat.getFont());
        mBoldFormat = new WritableCellFormat(mDefaultFormat);
        boldFont.setBoldStyle(WritableFont.BOLD);
        mBoldFormat.setFont(boldFont);
        mBoldFormat.setWrap(true);
        mBoldFormat.setAlignment(Alignment.CENTRE);
        // Create the red format
        mRedFormat = new WritableCellFormat(mDefaultFormat);
        final WritableFont redFont = new WritableFont(cellFormat.getFont());
        redFont.setColour(Colour.RED);
        mRedFormat.setFont(redFont);
        // Create the green format
        mGreenFormat = new WritableCellFormat(mDefaultFormat);
        final WritableFont greenFont = new WritableFont(cellFormat.getFont());
        greenFont.setColour(Colour.GREEN);
        mGreenFormat.setFont(greenFont);
        // Create the amber format
        mAmberFormat = new WritableCellFormat(mDefaultFormat);
        final WritableFont amberFont = new WritableFont(cellFormat.getFont());
        amberFont.setColour(Colour.LIGHT_ORANGE);
        mAmberFormat.setFont(amberFont);
    } catch (WriteException e) {
        Log.e(TAG, "createCellFormats Could not create cell formats", e);
    }
}

private void createLayoutDefaults() throws WriteException {
    WritableFont times10 = new WritableFont(WritableFont.TIMES, 10);
    times = new WritableCellFormat(times10);
    times.setWrap(false);
    WritableFont times10Bold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false);
    timesBold = new WritableCellFormat(times10Bold);
    timesBold.setWrap(false);
    timesBold_AlignmentRight = new WritableCellFormat(times10Bold);
    timesBold_AlignmentRight.setWrap(false);
    timesBold_AlignmentRight.setAlignment(Alignment.RIGHT);
    CellView cv = new CellView();
    cv.setFormat(times);
    cv.setFormat(timesBold);
    cv.setAutosize(false);
}

/**
    * Creates a JExcelAPI representation of an alignment
    * 
    * @param alignment The requested alignment
    * @return The alignment representation
    * @see <a
    *      href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/jxl/format/Alignment.html">Alignment</a>
    */
public static Alignment createAlignment(String alignment) {
    if (log.isTraceEnabled()) {
        log.trace("Creating alignment for #0", alignment);
    }
    try {
        return alignment == null ? Alignment.LEFT : (Alignment) getConstant(ALIGNMENT_CLASS_NAME, alignment.toUpperCase());
    } catch (NoSuchFieldException e) {
        String message = Interpolator.instance().interpolate("Alignment {0} not supported, try {1}", alignment, getValidConstantsSuggestion(ALIGNMENT_CLASS_NAME));
        throw new ExcelWorkbookException(message, e);
    }
}

/**
     * Generate a Microsoft Excel file in byte array from the given issue list,
     * using JXL.
     */
public static byte[] excelFrom(List<Issue> issueList) throws WriteException, IOException {
    WritableWorkbook workbook;
    WritableSheet sheet;
    WritableFont wf1 = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD, false, UnderlineStyle.SINGLE, Colour.BLUE_GREY, ScriptStyle.NORMAL_SCRIPT);
    WritableCellFormat cf1 = new WritableCellFormat(wf1);
    cf1.setBorder(Border.ALL, BorderLineStyle.DOUBLE);
    cf1.setAlignment(Alignment.CENTRE);
    WritableFont wf2 = new WritableFont(WritableFont.TAHOMA, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.NORMAL_SCRIPT);
    WritableCellFormat cf2 = new WritableCellFormat(wf2);
    cf2.setShrinkToFit(true);
    cf2.setBorder(Border.ALL, BorderLineStyle.THIN);
    cf2.setAlignment(Alignment.CENTRE);
    DateFormat valueFormatDate = new DateFormat("yyyy-MM-dd HH:mm:ss");
    WritableCellFormat cfDate = new WritableCellFormat(valueFormatDate);
    cfDate.setFont(wf2);
    cfDate.setShrinkToFit(true);
    cfDate.setBorder(Border.ALL, BorderLineStyle.THIN);
    cfDate.setAlignment(Alignment.CENTRE);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook = Workbook.createWorkbook(bos);
    sheet = workbook.createSheet(String.valueOf(JodaDateUtil.today().getTime()), 0);
    String[] labalArr = { "ID", "STATE", "TITLE", "ASSIGNEE", "DATE" };
    for (int i = 0; i < labalArr.length; i++) {
        sheet.addCell(new jxl.write.Label(i, 0, labalArr[i], cf1));
        sheet.setColumnView(i, 20);
    }
    for (int i = 1; i < issueList.size() + 1; i++) {
        Issue issue = issueList.get(i - 1);
        int colcnt = 0;
        sheet.addCell(new jxl.write.Label(colcnt++, i, issue.id.toString(), cf2));
        sheet.addCell(new jxl.write.Label(colcnt++, i, issue.state.toString(), cf2));
        sheet.addCell(new jxl.write.Label(colcnt++, i, issue.title, cf2));
        sheet.addCell(new jxl.write.Label(colcnt++, i, getAssigneeName(issue.assignee), cf2));
        sheet.addCell(new jxl.write.DateTime(colcnt++, i, issue.createdDate, cfDate));
    }
    workbook.write();
    try {
        workbook.close();
    } catch (WriteException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return bos.toByteArray();
}

@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model, WritableWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
    Map<String, List<Hour>> hourMap = (Map<String, List<Hour>>) model.get("hourMap");
    mapByType = (Map<Integer, Map<Integer, List<Hour>>>) model.get("mapByType");
    assignments = (Map<Integer, Assignment>) model.get("Assignments");
    usersRates = (Map<Integer, Integer>) model.get("UsersRates");
    for (Map.Entry<Integer, Integer> kv : usersRates.entrySet()) {
        System.out.println();
    }
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=timesheet.xls");
    // created a sheet in the workbook
    sheet = workbook.createSheet("mysheet", 0);
    WritableFont times16fontbold = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);
    WritableCellFormat times16formatbold = new WritableCellFormat(times16fontbold);
    WritableFont times10font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, true);
    WritableCellFormat times10fontformatleft = new WritableCellFormat(times10font);
    times10fontformatleft.setAlignment(Alignment.LEFT);
    WritableCellFormat times10fontformatcentre = new WritableCellFormat(times10font);
    times10fontformatcentre.setAlignment(Alignment.CENTRE);
    WritableCellFormat times10fontformatright = new WritableCellFormat(times10font);
    times10fontformatright.setAlignment(Alignment.RIGHT);
    WritableFont times10fontbold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, true);
    WritableCellFormat times10fontformatboldleft = new WritableCellFormat(times10fontbold);
    times10fontformatboldleft.setAlignment(Alignment.LEFT);
    WritableCellFormat times10fontformatboldcentre = new WritableCellFormat(times10fontbold);
    times10fontformatboldcentre.setAlignment(Alignment.CENTRE);
    WritableCellFormat times10fontformatboldright = new WritableCellFormat(times10fontbold);
    times10fontformatboldright.setAlignment(Alignment.RIGHT);
    WritableFont tahoma10fontbold = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD, true);
    WritableCellFormat tahoma10fontformatboldleft = new WritableCellFormat(tahoma10fontbold);
    tahoma10fontformatboldleft.setAlignment(Alignment.LEFT);
    WritableCellFormat tahoma10fontformatboldcentre = new WritableCellFormat(tahoma10fontbold);
    tahoma10fontformatboldcentre.setAlignment(Alignment.CENTRE);
    WritableCellFormat tahoma10fontformatboldright = new WritableCellFormat(tahoma10fontbold);
    tahoma10fontformatboldright.setAlignment(Alignment.RIGHT);
    WritableFont tahoma10font = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD, true);
    WritableCellFormat tahoma10fontformatleft = new WritableCellFormat(tahoma10font);
    tahoma10fontformatleft.setAlignment(Alignment.LEFT);
    WritableCellFormat tahoma10fontformatcentre = new WritableCellFormat(tahoma10font);
    tahoma10fontformatcentre.setAlignment(Alignment.CENTRE);
    WritableCellFormat tahoma10fontformatright = new WritableCellFormat(tahoma10font);
    tahoma10fontformatright.setAlignment(Alignment.RIGHT);
    WritableFont tahoma10fontred = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD, true);
    tahoma10fontred.setColour(Colour.RED);
    WritableCellFormat tahoma10fontformatleftred = new WritableCellFormat(tahoma10fontred);
    tahoma10fontformatleftred.setAlignment(Alignment.LEFT);
    WritableCellFormat tahoma10fontformatcentrered = new WritableCellFormat(tahoma10fontred);
    tahoma10fontformatcentrered.setAlignment(Alignment.CENTRE);
    WritableCellFormat tahoma10fontformatrightred = new WritableCellFormat(tahoma10fontred);
    tahoma10fontformatrightred.setAlignment(Alignment.RIGHT);
    WritableFont courier16fontbold = new WritableFont(WritableFont.COURIER, 16, WritableFont.BOLD, true);
    WritableCellFormat courier10formatbold = new WritableCellFormat(courier16fontbold);
    courier10formatbold.setAlignment(Alignment.RIGHT);
    sheet.setColumnView(0, 35);
    sheet.setColumnView(1, 35);
    sheet.setColumnView(2, 35);
    sheet.setColumnView(3, 35);
    sheet.setColumnView(4, 35);
    sheet.setColumnView(5, 35);
    sheet.setColumnView(6, 35);
    sheet.setColumnView(7, 35);
    Label approwedBy = new Label(2, 1, "APPROVED By", courier10formatbold);
    Label dmName = new Label(3, 2, "name of department manager");
    Label dmSign = new Label(2, 4, "Sign:", courier10formatbold);
    Label date = new Label(2, 5, "Date:", courier10formatbold);
    sheet.addCell(approwedBy);
    sheet.addCell(dmName);
    sheet.addCell(dmSign);
    sheet.addCell(date);
    WritableFont departmentNameFont = new WritableFont(WritableFont.COURIER, 14, WritableFont.BOLD, true);
    WritableCellFormat departmentNameCellFormat = new WritableCellFormat(departmentNameFont);
    departmentNameCellFormat.setAlignment(Alignment.CENTRE);
    Label departmentName = new Label(0, 8, "Department name:", departmentNameCellFormat);
    sheet.addCell(departmentName);
    sheet.mergeCells(0, 8, 3, 8);
    Label userReportLabel = new Label(0, 9, "Assignment Report by Department users", departmentNameCellFormat);
    sheet.addCell(userReportLabel);
    sheet.mergeCells(0, 9, 3, 9);
    sheet.mergeCells(1, 10, 2, 10);
    Label userNameLabel = new Label(0, 10, "Name", times10fontformatboldleft);
    Label assignmentNameLabel = new Label(1, 10, "Assignment", times10fontformatboldcentre);
    Label hoursLabel = new Label(3, 10, "Hours", times10fontformatboldleft);
    sheet.addCell(userNameLabel);
    sheet.addCell(assignmentNameLabel);
    sheet.addCell(hoursLabel);
    i = 11;
    Integer totalSum = 0;
    for (Map.Entry<String, List<Hour>> kv : hourMap.entrySet()) {
        if (kv.getValue().size() != 0) {
            Integer sum = 0;
            for (Hour h : kv.getValue()) {
                sheet.mergeCells(1, i, 2, i);
                userNameLabel = new Label(0, i, kv.getKey(), tahoma10fontformatleft);
                assignmentNameLabel = new Label(1, i, h.getAssignment().getName(), tahoma10fontformatcentre);
                Number hourLabel = new Number(3, i, h.getHour(), tahoma10fontformatright);
                sheet.addCell(userNameLabel);
                sheet.addCell(assignmentNameLabel);
                sheet.addCell(hourLabel);
                sum = sum + h.getHour();
                totalSum = totalSum + h.getHour();
                i++;
            }
            sheet.mergeCells(1, i, 2, i);
            assignmentNameLabel = new Label(1, i, "Sum", tahoma10fontformatright);
            Number sumCell = new Number(3, i, sum, tahoma10fontformatcentre);
            sheet.addCell(assignmentNameLabel);
            sheet.addCell(sumCell);
            i++;
        } else {
            sheet.mergeCells(1, i, 2, i);
            userNameLabel = new Label(0, i, kv.getKey(), tahoma10fontformatleftred);
            sheet.addCell(userNameLabel);
            assignmentNameLabel = new Label(1, i, "Sum", tahoma10fontformatrightred);
            Number sumCell = new Number(3, i, 0, tahoma10fontformatcentrered);
            sheet.addCell(assignmentNameLabel);
            sheet.addCell(sumCell);
            i++;
        }
    }
    WritableFont tahoma16fontGrey = new WritableFont(WritableFont.TAHOMA, 16, WritableFont.BOLD, true);
    WritableCellFormat tahoma16fontGreyformat = new WritableCellFormat(tahoma16fontGrey);
    tahoma16fontGreyformat.setBackground(Colour.GREY_40_PERCENT);
    tahoma16fontGreyformat.setAlignment(Alignment.CENTRE);
    assignmentNameLabel = new Label(0, i, "Total Sum", tahoma16fontGreyformat);
    Number sumCell = new Number(3, i, totalSum, tahoma16fontGreyformat);
    sheet.addCell(assignmentNameLabel);
    sheet.addCell(sumCell);
    sheet.mergeCells(0, i, 2, i);
    i++;
    i++;
    i++;
    Label allHoursLabel = new Label(0, i, "All Hours / Add Assignments:", departmentNameCellFormat);
    sheet.addCell(allHoursLabel);
    sheet.mergeCells(0, i, 3, i);
    i++;
    for (int step = 0; step <= 3; step++) {
        i++;
        if (mapByType.get(step) != null) {
            doByTypes(step);
        }
    }
}

/*
public Colour analiseColour(Color color){
	Vector clrs = new Vector();
	if(colours==null) colours = initColours();
	for(int i=0;i<colours.length;i++){
		try{
			Colour_TMP colour = new Colour_TMP(
				colours[i].getName(),
				colours[i].getR(),
				colours[i].getG(),
				colours[i].getB()
			);
			colour.setLength(color.getRed(),color.getGreen(),color.getBlue());
			clrs.add(colour);
		}catch(Exception e){
			e.toString();
		}
	}
	clrs = new util_sort().sort(clrs,"length");
	Colour result = null;
	try{
		Class cl = Colour.class;
		result = (Colour)cl.getField(((Colour_TMP)clrs.get(0)).getName()).get(cl);
	}catch(Exception e){
	}
	return result;
}
*/
public Alignment analiseAlign(String align) {
    if (alignCache == null)
        alignCache = new HashMap();
    Alignment alignment = (Alignment) alignCache.get(align);
    if (alignment == null) {
        if (align == null || align.equals(""))
            alignment = Alignment.GENERAL;
        if (align.equalsIgnoreCase("LEFT"))
            alignment = Alignment.LEFT;
        if (align.equalsIgnoreCase("CENTER"))
            alignment = Alignment.CENTRE;
        if (align.equalsIgnoreCase("RIGHT"))
            alignment = Alignment.RIGHT;
        if (align.equalsIgnoreCase("FILL"))
            alignment = Alignment.FILL;
        if (align.equalsIgnoreCase("JUSTIFY"))
            alignment = Alignment.JUSTIFY;
        if (alignment == null)
            alignment = Alignment.GENERAL;
        alignCache.put(align, alignment);
    }
    return alignment;
}

/**
   * Adds cells to the specified sheet which test the various label formatting
   * styles, such as different fonts, different sizes and bold, underline etc.
   * 
   * @param s1 
   */
private void writeLabelFormatSheet(WritableSheet s1) throws WriteException {
    s1.setColumnView(0, 60);
    Label lr = new Label(0, 0, "Arial Fonts");
    s1.addCell(lr);
    lr = new Label(1, 0, "10pt");
    s1.addCell(lr);
    lr = new Label(2, 0, "Normal");
    s1.addCell(lr);
    lr = new Label(3, 0, "12pt");
    s1.addCell(lr);
    WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
    WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
    arial12format.setWrap(true);
    lr = new Label(4, 0, "Normal", arial12format);
    s1.addCell(lr);
    WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    WritableCellFormat arial10BoldFormat = new WritableCellFormat(arial10ptBold);
    lr = new Label(2, 2, "BOLD", arial10BoldFormat);
    s1.addCell(lr);
    WritableFont arial12ptBold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
    WritableCellFormat arial12BoldFormat = new WritableCellFormat(arial12ptBold);
    lr = new Label(4, 2, "BOLD", arial12BoldFormat);
    s1.addCell(lr);
    WritableFont arial10ptItalic = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
    WritableCellFormat arial10ItalicFormat = new WritableCellFormat(arial10ptItalic);
    lr = new Label(2, 4, "Italic", arial10ItalicFormat);
    s1.addCell(lr);
    WritableFont arial12ptItalic = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
    WritableCellFormat arial12ptItalicFormat = new WritableCellFormat(arial12ptItalic);
    lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
    s1.addCell(lr);
    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    WritableCellFormat times10format = new WritableCellFormat(times10pt);
    lr = new Label(0, 7, "Times Fonts", times10format);
    s1.addCell(lr);
    lr = new Label(1, 7, "10pt", times10format);
    s1.addCell(lr);
    lr = new Label(2, 7, "Normal", times10format);
    s1.addCell(lr);
    lr = new Label(3, 7, "12pt", times10format);
    s1.addCell(lr);
    WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
    WritableCellFormat times12format = new WritableCellFormat(times12pt);
    lr = new Label(4, 7, "Normal", times12format);
    s1.addCell(lr);
    WritableFont times10ptBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
    WritableCellFormat times10BoldFormat = new WritableCellFormat(times10ptBold);
    lr = new Label(2, 9, "BOLD", times10BoldFormat);
    s1.addCell(lr);
    WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
    WritableCellFormat times12BoldFormat = new WritableCellFormat(times12ptBold);
    lr = new Label(4, 9, "BOLD", times12BoldFormat);
    s1.addCell(lr);
    // The underline styles
    s1.setColumnView(6, 22);
    s1.setColumnView(7, 22);
    s1.setColumnView(8, 22);
    s1.setColumnView(9, 22);
    lr = new Label(0, 11, "Underlining");
    s1.addCell(lr);
    WritableFont arial10ptUnderline = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE);
    WritableCellFormat arialUnderline = new WritableCellFormat(arial10ptUnderline);
    lr = new Label(6, 11, "Underline", arialUnderline);
    s1.addCell(lr);
    WritableFont arial10ptDoubleUnderline = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE);
    WritableCellFormat arialDoubleUnderline = new WritableCellFormat(arial10ptDoubleUnderline);
    lr = new Label(7, 11, "Double Underline", arialDoubleUnderline);
    s1.addCell(lr);
    WritableFont arial10ptSingleAcc = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE_ACCOUNTING);
    WritableCellFormat arialSingleAcc = new WritableCellFormat(arial10ptSingleAcc);
    lr = new Label(8, 11, "Single Accounting Underline", arialSingleAcc);
    s1.addCell(lr);
    WritableFont arial10ptDoubleAcc = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE_ACCOUNTING);
    WritableCellFormat arialDoubleAcc = new WritableCellFormat(arial10ptDoubleAcc);
    lr = new Label(9, 11, "Double Accounting Underline", arialDoubleAcc);
    s1.addCell(lr);
    WritableFont times14ptBoldUnderline = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
    WritableCellFormat timesBoldUnderline = new WritableCellFormat(times14ptBoldUnderline);
    lr = new Label(6, 12, "Times 14 Bold Underline", timesBoldUnderline);
    s1.addCell(lr);
    WritableFont arial18ptBoldItalicUnderline = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, true, UnderlineStyle.SINGLE);
    WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat(arial18ptBoldItalicUnderline);
    lr = new Label(6, 13, "Arial 18 Bold Italic Underline", arialBoldItalicUnderline);
    s1.addCell(lr);
    lr = new Label(0, 15, "Script styles");
    s1.addCell(lr);
    WritableFont superscript = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUPERSCRIPT);
    WritableCellFormat superscriptFormat = new WritableCellFormat(superscript);
    lr = new Label(1, 15, "superscript", superscriptFormat);
    s1.addCell(lr);
    WritableFont subscript = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUBSCRIPT);
    WritableCellFormat subscriptFormat = new WritableCellFormat(subscript);
    lr = new Label(2, 15, "subscript", subscriptFormat);
    s1.addCell(lr);
    lr = new Label(0, 17, "Colours");
    s1.addCell(lr);
    WritableFont red = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
    WritableCellFormat redFormat = new WritableCellFormat(red);
    lr = new Label(2, 17, "Red", redFormat);
    s1.addCell(lr);
    WritableFont blue = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
    WritableCellFormat blueFormat = new WritableCellFormat(blue);
    lr = new Label(2, 18, "Blue", blueFormat);
    s1.addCell(lr);
    WritableFont lime = new WritableFont(WritableFont.ARIAL);
    lime.setColour(Colour.LIME);
    WritableCellFormat limeFormat = new WritableCellFormat(lime);
    limeFormat.setWrap(true);
    lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
    s1.addCell(lr);
    WritableCellFormat greyBackground = new WritableCellFormat();
    greyBackground.setWrap(true);
    greyBackground.setBackground(Colour.GRAY_50);
    lr = new Label(2, 19, "Grey background", greyBackground);
    s1.addCell(lr);
    WritableFont yellow = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.YELLOW);
    WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
    yellowOnBlue.setWrap(true);
    yellowOnBlue.setBackground(Colour.BLUE);
    lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
    s1.addCell(lr);
    WritableCellFormat yellowOnBlack = new WritableCellFormat(yellow);
    yellowOnBlack.setWrap(true);
    yellowOnBlack.setBackground(Colour.PALETTE_BLACK);
    lr = new Label(3, 20, "Black background, yellow foreground", yellowOnBlack);
    s1.addCell(lr);
    lr = new Label(0, 22, "Null label");
    s1.addCell(lr);
    lr = new Label(2, 22, null);
    s1.addCell(lr);
    lr = new Label(0, 24, "A very long label, more than 255 characters\012" + "Rejoice O shores\012" + "Sing O bells\012" + "But I with mournful tread\012" + "Walk the deck my captain lies\012" + "Fallen cold and dead\012" + "Summer surprised, coming over the Starnbergersee\012" + "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" + "Rejoice O shores\012" + "Sing O bells\012" + "But I with mournful tread\012" + "Walk the deck my captain lies\012" + "Fallen cold and dead\012" + "Summer surprised, coming over the Starnbergersee\012" + "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" + "Rejoice O shores\012" + "Sing O bells\012" + "But I with mournful tread\012" + "Walk the deck my captain lies\012" + "Fallen cold and dead\012" + "Summer surprised, coming over the Starnbergersee\012" + "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" + "Rejoice O shores\012" + "Sing O bells\012" + "But I with mournful tread\012" + "Walk the deck my captain lies\012" + "Fallen cold and dead\012" + "Summer surprised, coming over the Starnbergersee\012" + "With a shower of rain. We stopped in the Colonnade\012" + "And sat and drank coffee an talked for an hour\012", arial12format);
    s1.addCell(lr);
    WritableCellFormat vertical = new WritableCellFormat();
    vertical.setOrientation(Orientation.VERTICAL);
    lr = new Label(0, 26, "Vertical orientation", vertical);
    s1.addCell(lr);
    WritableCellFormat plus_90 = new WritableCellFormat();
    plus_90.setOrientation(Orientation.PLUS_90);
    lr = new Label(1, 26, "Plus 90", plus_90);
    s1.addCell(lr);
    WritableCellFormat minus_90 = new WritableCellFormat();
    minus_90.setOrientation(Orientation.MINUS_90);
    lr = new Label(2, 26, "Minus 90", minus_90);
    s1.addCell(lr);
    lr = new Label(0, 28, "Modified row height");
    s1.addCell(lr);
    s1.setRowView(28, 24 * 20);
    lr = new Label(0, 29, "Collapsed row");
    s1.addCell(lr);
    s1.setRowView(29, true);
    // Write hyperlinks
    try {
        Label l = new Label(0, 30, "Hyperlink to home page");
        s1.addCell(l);
        URL url = new URL("http://www.andykhan.com/jexcelapi");
        WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
        s1.addHyperlink(wh);
        // The below hyperlink clashes with above
        WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
        s1.addHyperlink(wh2);
        l = new Label(4, 2, "File hyperlink to documentation");
        s1.addCell(l);
        File file = new File("../jexcelapi/docs/index.html");
        wh = new WritableHyperlink(0, 32, 8, 32, file, "JExcelApi Documentation");
        s1.addHyperlink(wh);
        // Add a hyperlink to another cell on this sheet
        wh = new WritableHyperlink(0, 34, 8, 34, "Link to another cell", s1, 0, 180, 1, 181);
        s1.addHyperlink(wh);
        file = new File("\\\\localhost\\file.txt");
        wh = new WritableHyperlink(0, 36, 8, 36, file);
        s1.addHyperlink(wh);
        // Add a very long hyperlink
        url = new URL("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086" + "/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
        wh = new WritableHyperlink(0, 38, 0, 38, url);
        s1.addHyperlink(wh);
    } catch (MalformedURLException e) {
        System.err.println(e.toString());
    }
    // Write out some merged cells
    Label l = new Label(5, 35, "Merged cells", timesBoldUnderline);
    s1.mergeCells(5, 35, 8, 37);
    s1.addCell(l);
    l = new Label(5, 38, "More merged cells");
    s1.addCell(l);
    Range r = s1.mergeCells(5, 38, 8, 41);
    s1.insertRow(40);
    s1.removeRow(39);
    s1.unmergeCells(r);
    // Merge cells and centre across them
    WritableCellFormat wcf = new WritableCellFormat();
    wcf.setAlignment(Alignment.CENTRE);
    l = new Label(5, 42, "Centred across merged cells", wcf);
    s1.addCell(l);
    s1.mergeCells(5, 42, 10, 42);
    wcf = new WritableCellFormat();
    wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
    wcf.setBackground(Colour.GRAY_25);
    l = new Label(3, 44, "Merged with border", wcf);
    s1.addCell(l);
    s1.mergeCells(3, 44, 4, 46);
    // Clash some ranges - the second range will not be added
    // Also merge some cells with two data items in the - the second data
    // item will not be merged
    /*
    l = new Label(5, 16, "merged cells");
    s1.addCell(l);

    Label l5 = new Label(7, 17, "this label won't appear");
    s1.addCell(l5);
    s1.mergeCells(5, 16, 8, 18);    

    s1.mergeCells(5, 19, 6, 24);
    s1.mergeCells(6, 18, 10, 19);
    */
    WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
    WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
    l = new Label(0, 49, "Courier fonts", courier10pt);
    s1.addCell(l);
    WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
    WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
    l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
    s1.addCell(l);
    WritableFont.FontName wingdingsFont = WritableFont.createFont("Wingdings 2");
    WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
    WritableCellFormat wingdings210pt = new WritableCellFormat(wingdings210ptFont);
    l = new Label(0, 51, "Bespoke Windgdings 2", wingdings210pt);
    s1.addCell(l);
    WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
    shrinkToFit.setShrinkToFit(true);
    l = new Label(3, 53, "Shrunk to fit", shrinkToFit);
    s1.addCell(l);
    l = new Label(3, 55, "Some long wrapped text in a merged cell", arial12format);
    s1.addCell(l);
    s1.mergeCells(3, 55, 4, 55);
    l = new Label(0, 57, "A cell with a comment");
    WritableCellFeatures cellFeatures = new WritableCellFeatures();
    cellFeatures.setComment("the cell comment");
    l.setCellFeatures(cellFeatures);
    s1.addCell(l);
    l = new Label(0, 59, "A cell with a long comment");
    cellFeatures = new WritableCellFeatures();
    cellFeatures.setComment("a very long cell comment indeed that won't " + "fit inside a standard comment box, so a " + "larger comment box is used instead", 5, 6);
    l.setCellFeatures(cellFeatures);
    s1.addCell(l);
    WritableCellFormat indented = new WritableCellFormat(times12pt);
    indented.setIndentation(4);
    l = new Label(0, 61, "Some indented text", indented);
    s1.addCell(l);
    l = new Label(0, 63, "Data validation:  list");
    s1.addCell(l);
    Blank b = new Blank(1, 63);
    cellFeatures = new WritableCellFeatures();
    ArrayList al = new ArrayList();
    al.add("bagpuss");
    al.add("clangers");
    al.add("ivor the engine");
    al.add("noggin the nog");
    cellFeatures.setDataValidationList(al);
    b.setCellFeatures(cellFeatures);
    s1.addCell(b);
    l = new Label(0, 64, "Data validation:  number > 4.5");
    s1.addCell(l);
    b = new Blank(1, 64);
    cellFeatures = new WritableCellFeatures();
    cellFeatures.setNumberValidation(4.5, WritableCellFeatures.GREATER_THAN);
    b.setCellFeatures(cellFeatures);
    s1.addCell(b);
    l = new Label(0, 65, "Data validation:  named range");
    s1.addCell(l);
    l = new Label(4, 65, "tiger");
    s1.addCell(l);
    l = new Label(5, 65, "sword");
    s1.addCell(l);
    l = new Label(6, 65, "honour");
    s1.addCell(l);
    l = new Label(7, 65, "company");
    s1.addCell(l);
    l = new Label(8, 65, "victory");
    s1.addCell(l);
    l = new Label(9, 65, "fortress");
    s1.addCell(l);
    b = new Blank(1, 65);
    cellFeatures = new WritableCellFeatures();
    cellFeatures.setDataValidationRange("validation_range");
    b.setCellFeatures(cellFeatures);
    s1.addCell(b);
    // Set the row grouping
    s1.setRowGroup(39, 45, false);
    // s1.setRowGroup(72, 74, true);
    l = new Label(0, 66, "Block of cells B67-F71 with data validation");
    s1.addCell(l);
    al = new ArrayList();
    al.add("Achilles");
    al.add("Agamemnon");
    al.add("Hector");
    al.add("Odysseus");
    al.add("Patroclus");
    al.add("Nestor");
    b = new Blank(1, 66);
    cellFeatures = new WritableCellFeatures();
    cellFeatures.setDataValidationList(al);
    b.setCellFeatures(cellFeatures);
    s1.addCell(b);
    s1.applySharedDataValidation(b, 4, 4);
    cellFeatures = new WritableCellFeatures();
    cellFeatures.setDataValidationRange("");
    l = new Label(0, 71, "Read only cell using empty data validation");
    l.setCellFeatures(cellFeatures);
    s1.addCell(l);
    // Set the row grouping
    s1.setRowGroup(39, 45, false);
// s1.setRowGroup(72, 74, true);
}

private void formatSettingsPage(WritableSheet a_objSheet) throws RowsExceededException, WriteException {
    // spalte 1
    CellView t_objView = new CellView();
    t_objView.setSize(10000);
    WritableFont t_objFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    WritableCellFormat t_objFormat = new WritableCellFormat(t_objFont);
    t_objFormat.setAlignment(Alignment.LEFT);
    t_objView.setFormat(t_objFormat);
    a_objSheet.setColumnView(0, t_objView);
    // spalte 2
    t_objView = new CellView();
    t_objView.setSize(6000);
    t_objFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
    t_objFormat = new WritableCellFormat(t_objFont);
    t_objFormat.setAlignment(Alignment.LEFT);
    t_objView.setFormat(t_objFormat);
    a_objSheet.setColumnView(1, t_objView);
    // spalte 3
    t_objView = new CellView();
    t_objView.setSize(4000);
    t_objFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
    t_objFormat = new WritableCellFormat(t_objFont);
    t_objFormat.setAlignment(Alignment.LEFT);
    t_objView.setFormat(t_objFormat);
    a_objSheet.setColumnView(2, t_objView);
    // spalte 4
    t_objView = new CellView();
    t_objView.setSize(3000);
    t_objFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
    t_objFormat = new WritableCellFormat(t_objFont);
    t_objFormat.setAlignment(Alignment.LEFT);
    t_objView.setFormat(t_objFormat);
    a_objSheet.setColumnView(3, t_objView);
    // headline
    a_objSheet.mergeCells(0, 0, 3, 0);
}

private int getHorizontalAlignment(TextAlignHolder alignment) {
    switch(alignment.horizontalAlignment) {
        case RIGHT:
            return Alignment.RIGHT.getValue();
        case CENTER:
            return Alignment.CENTRE.getValue();
        case JUSTIFIED:
            return Alignment.JUSTIFY.getValue();
        case LEFT:
        default:
            return Alignment.LEFT.getValue();
    }
}

/**
    * Creates a JExcelAPI representation of an alignment
    * 
    * @param alignment The requested alignment
    * @return The alignment representation
    * @see <a
    *      href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/jxl/format/Alignment.html">Alignment</a>
    */
public static Alignment createAlignment(String alignment) {
    if (log.isTraceEnabled()) {
        log.trace("Creating alignment for #0", alignment);
    }
    try {
        return alignment == null ? Alignment.LEFT : (Alignment) getConstant(ALIGNMENT_CLASS_NAME, alignment.toUpperCase());
    } catch (NoSuchFieldException e) {
        String message = Interpolator.instance().interpolate("Alignment {0} not supported, try {1}", alignment, getValidConstantsSuggestion(ALIGNMENT_CLASS_NAME));
        throw new ExcelWorkbookException(message, e);
    }
}

/**
    * Creates a JExcelAPI representation of an alignment
    * 
    * @param alignment The requested alignment
    * @return The alignment representation
    * @see <a
    *      href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/jxl/format/Alignment.html">Alignment</a>
    */
public static Alignment createAlignment(String alignment) {
    if (log.isTraceEnabled()) {
        log.trace("Creating alignment for #0", alignment);
    }
    try {
        return alignment == null ? Alignment.LEFT : (Alignment) getConstant(ALIGNMENT_CLASS_NAME, alignment.toUpperCase());
    } catch (NoSuchFieldException e) {
        String message = Interpolator.instance().interpolate("Alignment {0} not supported, try {1}", alignment, getValidConstantsSuggestion(ALIGNMENT_CLASS_NAME));
        throw new ExcelWorkbookException(message, e);
    }
}

/**
    * Creates a JExcelAPI representation of an alignment
    * 
    * @param alignment The requested alignment
    * @return The alignment representation
    * @see <a
    *      href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/jxl/format/Alignment.html">Alignment</a>
    */
public static Alignment createAlignment(String alignment) {
    if (log.isTraceEnabled()) {
        log.trace("Creating alignment for #0", alignment);
    }
    try {
        return alignment == null ? Alignment.LEFT : (Alignment) getConstant(ALIGNMENT_CLASS_NAME, alignment.toUpperCase());
    } catch (NoSuchFieldException e) {
        String message = Interpolator.instance().interpolate("Alignment {0} not supported, try {1}", alignment, getValidConstantsSuggestion(ALIGNMENT_CLASS_NAME));
        throw new ExcelWorkbookException(message, e);
    }
}

/**
    * Creates a JExcelAPI representation of an alignment
    * 
    * @param alignment The requested alignment
    * @return The alignment representation
    * @see <a
    *      href="http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/jxl/format/Alignment.html">Alignment</a>
    */
public static Alignment createAlignment(String alignment) {
    if (log.isTraceEnabled()) {
        log.trace("Creating alignment for #0", alignment);
    }
    try {
        return alignment == null ? Alignment.LEFT : (Alignment) getConstant(ALIGNMENT_CLASS_NAME, alignment.toUpperCase());
    } catch (NoSuchFieldException e) {
        String message = Interpolator.instance().interpolate("Alignment {0} not supported, try {1}", alignment, getValidConstantsSuggestion(ALIGNMENT_CLASS_NAME));
        throw new ExcelWorkbookException(message, e);
    }
}

public Alignment discover(eu.ggnet.lucidcalc.CFormat.HorizontalAlignment horizontalAlignment) {
    switch(horizontalAlignment) {
        case CENTER:
            return Alignment.CENTRE;
        case LEFT:
            return Alignment.LEFT;
        case RIGHT:
            return Alignment.RIGHT;
    }
    return Alignment.GENERAL;
}