XSSF Apache POI XSSFCellStyle

Question

Rotating column heading using HSSFCellStyle using method setRotation working fine as below program ---

 public static void main(String[] args)throws Exception 
{
  HSSFWorkbook workbook = new HSSFWorkbook(); 
  HSSFSheet spreadsheet = workbook.createSheet(
  "Text direction");
  HSSFRow row = spreadsheet.createRow(2);
  HSSFCellStyle myStyle = workbook.createCellStyle();
  myStyle.setRotation((short) 0);
  HSSFCell cell = row.createCell(1);
  cell.setCellValue("0D angle");
  cell.setCellStyle(myStyle);

  myStyle=workbook.createCellStyle();
  myStyle.setRotation((short) 90);
  cell = row.createCell(3);
  cell.setCellValue("30D angle");
  cell.setCellStyle(myStyle);

  myStyle=workbook.createCellStyle();
  myStyle.setRotation((short) -90);
  cell = row.createCell(5);
  cell.setCellValue("90D angle");
  cell.setCellStyle(myStyle);

  FileOutputStream out = new FileOutputStream(
  new File("textdirection.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println( 
  "textdirection.xlsx written successfully");
}

but same code writen using XSSF output file column header not rotating. below code using XSSF--

public static void main(String[] args)throws Exception 
{
  XSSFWorkbook workbook = new XSSFWorkbook(); 
  XSSFSheet spreadsheet = workbook.createSheet(
  "Text direction");
  XSSFRow row = spreadsheet.createRow(2);
  XSSFCellStyle myStyle = workbook.createCellStyle();
  myStyle.setRotation((short) 0);
  XSSFCell cell = row.createCell(1);
  cell.setCellValue("0D angle");
  cell.setCellStyle(myStyle);

  myStyle=workbook.createCellStyle();
  myStyle.setRotation((short) 180);
  cell = row.createCell(3);
  cell.setCellValue("30D angle");
  cell.setCellStyle(myStyle);

  myStyle=workbook.createCellStyle();
  myStyle.setRotation((short) -180);
  cell = row.createCell(5);
  cell.setCellValue("90D angle");
  cell.setCellStyle(myStyle);

  FileOutputStream out = new FileOutputStream(
  new File("textdirection.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println( 
  "textdirection.xlsx written successfully");
}

so can any one give me hint on this.

Thanks.

Answer 1

It's all about formats for XLS and XLSX sheets and workbooks - they are different.

Here's part of JavaDoc on setRotation() method:

public void setRotation(short rotation)

Set the degree of rotation for the text in the cell

Expressed in degrees. Values range from 0 to 180. The first letter of the text is considered the center-point of the arc. For 0 - 90, the value represents degrees above horizon. For 91-180 the degrees below the horizon is calculated as: [degrees below horizon] = 90 - textRotation.

Note: HSSF uses values from -90 to 90 degrees, whereas XSSF uses values from 0 to 180 degrees. The implementations of this method will map between these two value-ranges accordingly, however the corresponding getter is returning values in the range mandated by the current type of Excel file-format that this CellStyle is applied to.

So, here's the correct example of what you're willing to do:

package com.github.xsavikx.apachepoitest;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;


public class ApachePOITest {
    public static void main(String[] args) throws Exception {
        XSSF();
        HSSF();
    }

    private static void XSSF() throws IOException {
        String filename = "textdirection_xssf.xlsx";
        try (XSSFWorkbook workbook = new XSSFWorkbook();
             FileOutputStream out = new FileOutputStream(new File(filename));) {

            XSSFSheet spreadsheet = workbook.createSheet(
                "Text direction");
            XSSFRow row = spreadsheet.createRow(2);
            XSSFCellStyle myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) 0);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("0D angle");
            cell.setCellStyle(myStyle);

            myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) 90);
            cell = row.createCell(3);
            cell.setCellValue("30D angle");
            cell.setCellStyle(myStyle);

            myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) 180);
            cell = row.createCell(5);
            cell.setCellValue("90D angle");
            cell.setCellStyle(myStyle);
            workbook.write(out);
            System.out.println(String.format("%s written successfully", filename));
        }
    }

    private static void HSSF() throws IOException {
        String filename = "textdirection_hssf.xls";
        try (HSSFWorkbook workbook = new HSSFWorkbook();
             FileOutputStream out = new FileOutputStream(new File(filename));) {
            HSSFSheet spreadsheet = workbook.createSheet(
                "Text direction");
            HSSFRow row = spreadsheet.createRow(2);
            HSSFCellStyle myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) 0);
            HSSFCell cell = row.createCell(1);
            cell.setCellValue("0D angle");
            cell.setCellStyle(myStyle);

            myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) 90);
            cell = row.createCell(3);
            cell.setCellValue("30D angle");
            cell.setCellStyle(myStyle);

            myStyle = workbook.createCellStyle();
            myStyle.setRotation((short) -90);
            cell = row.createCell(5);
            cell.setCellValue("90D angle");
            cell.setCellStyle(myStyle);


            workbook.write(out);
            System.out.println(String.format("%s written successfully", filename));
        }
    }

}