How to create excel sheet with right-to-left alignment using JXL

Question

I wonder if i can set the direction of the excel sheet to right-to-left ... I am using JXL Api.

thanks in advance

The bounty is about right to left and not right alignment

Answer 1

Currently JXL does not support this option, but I have a good solution that will work if you are willing to edit and re-build JXL.

Right-to-left options are saved and defined in a section of the file called WINDOW2 record. You can see all the options defined in the part here in section 5.110 WINDOW2. Under section 5.110.2 Option Flags, you can see the mask value for the option flag and the right-to-left option:

6 | 0040H | 0 = Columns from left to right | 1 = Columns from right to left

JXL create This part of the excel file in this class - Window2Record.

In the constructor method you can see that some of the values are configurable and some are hard-coded:

  public Window2Record(SheetSettings settings)
  {
    super(Type.WINDOW2);

    int options = 0;

    options |= 0x0; // display formula values, not formulas

    if (settings.getShowGridLines())
    {
      options |= 0x02;
    }

    options |= 0x04; // display row and column headings

    options |= 0x0; // panes should be not frozen

    if (settings.getDisplayZeroValues())
    {
      options |= 0x10;
    }

    options |= 0x20; // default header

    options |= 0x80; // display outline symbols

    // Handle the freeze panes
    if (settings.getHorizontalFreeze() != 0 ||
        settings.getVerticalFreeze() != 0)
    {
      options |= 0x08;
      options |= 0x100;
    }
...

As you can see options "display outline symbols" is hard coded to be true, since it's mask (0x80) always added to the options flag, and DisplayZeroValues is configurable by the values of the given SheetSettings object (that has getter and setters...)

If you are willing to rebuild the project, you can either hard code your right-to-left settings by adding the line:

options |= 0x40; // Columns from right to left

to this constructor, or if you want it configurable, add a new parameter to the SheetSettings (and a getter and a setter for it) and in the Window2Record add the right if clause for it.

Answer 2

This can be done manually (from Microsoft Office Support)

The Right-to-left option in the Excel Options dialog box orients new worksheets from right to left. The setting does not apply to the worksheet currently displayed. You can have worksheets oriented right to left and left to right in the same workbook.

1. Click the Microsoft Office Button, and then click Excel Options. Where is the Excel Options button?
2. Click International.
3. Under Right-to-left and Default direction, click Right-to-left, and then click OK.
4. Insert a new worksheet, or open a new workbook.
5. In the Windows taskbar (or on the Language bar, if it is visible), click the Language icon , and then click the name of the right-to-left language that you want to use.
6. If you need to change the direction of the text, click the Right-to-left button on the Data tab in the Font Alignment group.

It seems JXL api doesn't support this feature. You can have a look at Apache Poi (javadoc) or TeamDev's JExcel (javadoc) which is more appropriate for this kind of requirement. Implementation will be similar to these:

Poi way:

XSSFSheet sheet = workbook.createSheet();
sheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setRightToLeft(true);

Via Jexcel:

 final Application application = ...;
        application.getOleMessageLoop().doInvokeAndWait(new Runnable() {
            public void run() {
                _Application app = application.getPeer();
                app.setDefaultSheetDirection(new Int32(LocaleID.LOCALE_USER_DEFAULT), new Int32(Constants.xlRTL));
            }
        });

About poi: 1, 2, 3.

Btw, if you try the use setExcelRegionalSettings(IL) or setExcelRegionalSettings(IL-JM) is not going to work because these are the only supported countries by JXL:

public static final jxl.biff.CountryCode USA;
public static final jxl.biff.CountryCode CANADA;
public static final jxl.biff.CountryCode GREECE;
public static final jxl.biff.CountryCode NETHERLANDS;
public static final jxl.biff.CountryCode BELGIUM;
public static final jxl.biff.CountryCode FRANCE;
public static final jxl.biff.CountryCode SPAIN;
public static final jxl.biff.CountryCode ITALY;
public static final jxl.biff.CountryCode SWITZERLAND;
public static final jxl.biff.CountryCode UK;
public static final jxl.biff.CountryCode DENMARK;
public static final jxl.biff.CountryCode SWEDEN;
public static final jxl.biff.CountryCode NORWAY;
public static final jxl.biff.CountryCode GERMANY;
public static final jxl.biff.CountryCode PHILIPPINES;
public static final jxl.biff.CountryCode CHINA;
public static final jxl.biff.CountryCode INDIA;
public static final jxl.biff.CountryCode UNKNOWN;

Answer 3

To set right-to-left direction during Spreadsheet creation:

• Manually create an xls template file with right-to-left text direction

• When you wish to create a new spreadsheet in Jexcel:

  • open the template as a worksheet

  • invoke the worksheet method to create a new worksheet as a copy of the worksheet template. Here's the method in Workbook class:

    public static WritableWorkbook createWorkbook(java.io.File file,
                                                  Workbook in)
                                    throws java.io.IOException
    

    Creates a writable workbook with the given filename as a copy of the workbook passed in. Once created, the contents of the writable workbook may be modified

To change direction after creation:

• you can manually create an excel macro called AUTO_OPEN that will run whenever the spreadsheet is open:

      Application.DefaultSheetDirection = xlRTL
      ActiveSheet.DisplayRightToLeft = True
  

• or you can do all your processing with JXL, close the file and then run a VBscript (interfacing with microsoft.office.interop.excel.dll):

   Set xl = CreateObject("Excel.application")
   xl.Application.Workbooks.Open "yourworkbookpath\yourworkbook.xls"
   xl.DefaultSheetDirection = xlRTL
  

  You can execute a script from java via Process