poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Beardsley <markbrd...@tiscali.co.uk>
Subject Re: How do i set the Horizontal Freeze on a worksheet
Date Sun, 16 Jan 2011 15:12:06 GMT

Rain tosayd means I have had the chance to play around with the most basic of
the two createFreezePane() methods that are defined on the Sheet interface.
This method accepts two parameters, both of type int, to determine how many
columns and how many rows are frozen. In the example below (look into the
setFreezePane() method, it contains just the one command!), I am passing
zero to the first parameter and one to the second, This will result in a
freeze pane where the top most row - row 1 - is locked or frozen so that the
column headers remain fixed if the user scrolls the pages contents up and
down. If you want to lock two ros, than simply pass 2, three rows then pass
three, etc. To lock or freeze columns over to the left hand side of the
worksheet, all you need to do is pass a value other tna zero to the first
parameter; passing 1 will freeze colum A, passing two will freeze columns A
and B, etc.

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.text.DateFormat;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;

/**
 *
 * @author win user
 */
public class TestFreezePane {

    private FileOutputStream fos = null;
    private CellStyle[] cellStyles = null;
    private CellStyle headerStyle = null;

    private static final String[] COLUMN_HEADERS = {
        "Customer Name.", "Customer Since.",
        "Yearly Spend.", "Spend YTD.", "Type."};
    private static final String[] CUSTOMER_NAMES = {
        "Boots The Chemist", "Erikson Telecommunications",
        "Imperial Tobacco", "Rolls Royce Aero Engines",
        "Rolls Royce Marine Power"};
    private static final String[] FROM_DATES = {
        "10/10/1997 00:00:00", "12/4/2001 00:00:00",
        "23/5/1998 00:00:00", "1/6/1995 00:00:00", "1/6/1995 00:00:00"};
    private static final double[] ANNUAL_SPENDS = {
        123456.89, 23456.87, 123674.94, 45673.82, 675298.87};
    private static final double[] SPENDS_YTD = {
        1456.89, 246.87, 13674.94, 453.82, 75298.87};
    private static final String[] CUST_TYPES = {"Manufacturer",
"Distributor"};

    public TestFreezePane(String workbookname) throws FileNotFoundException
{
        File file = new File(workbookname);
        this.fos = new FileOutputStream(file);
    }

    public void createWorkbook() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Freezepane Test.");
        this.createStyles(workbook);
        this.createHeaders(sheet);
        this.populateColumns(sheet);
        this.setFreezePane(sheet);
        this.saveWorkbook(workbook);
    }

    private void createHeaders(Sheet sheet) {
        int index = 0;
        Cell cell = null;
        Row headerRow = sheet.createRow(0);
        for(String header : COLUMN_HEADERS) {
            cell = headerRow.createCell(index);
            cell.setCellValue(COLUMN_HEADERS[index++]);
            cell.setCellStyle(this.headerStyle);
        }
    }

    private void createStyles(Workbook workbook) {
        CreationHelper creaHelper = workbook.getCreationHelper();
        DataFormat dataFormat = creaHelper.createDataFormat();
        CellStyle cellStyle = null;
        this.headerStyle = workbook.createCellStyle();
        this.headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        this.cellStyles = new CellStyle[COLUMN_HEADERS.length];
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        this.cellStyles[0] = cellStyle;
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyle.setDataFormat(dataFormat.getFormat("DD/MM/YYYY"));
        this.cellStyles[1] = cellStyle;
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
       
cellStyle.setDataFormat(dataFormat.getFormat("£#,##0.00;[Red]£#,##0.00"));
        this.cellStyles[2] = cellStyle;
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
       
cellStyle.setDataFormat(dataFormat.getFormat("£#,##0.00;[Red]£#,##0.00"));
        this.cellStyles[3] = cellStyle;
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        this.cellStyles[4] = cellStyle;
    }

    private void populateColumns(Sheet sheet) {
        Row row = null;
        Cell cell = null;
        DateFormat dateFormat = DateFormat.getInstance();
        int rowIndex = 1;
        for(String customerName : CUSTOMER_NAMES) {
            row = sheet.createRow(rowIndex);
            for(int i = 0; i < COLUMN_HEADERS.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(this.cellStyles[i]);
                switch(i) {
                    case(0):
                        cell.setCellValue(customerName);
                        break;
                    case(1):
                        try {
                           
cell.setCellValue(dateFormat.parse(FROM_DATES[rowIndex - 1]));
                        }
                        catch(java.text.ParseException pEX) {
                            cell.setCellValue(new java.util.Date());
                        }
                        break;
                    case(2):
                        cell.setCellValue(ANNUAL_SPENDS[rowIndex - 1]);
                        break;
                    case(3):
                        cell.setCellValue(SPENDS_YTD[rowIndex - 1]);
                        break;
                    case(4):
                        if((rowIndex % 2) == 0) {
                            cell.setCellValue(CUST_TYPES[0]);
                        }
                        else {
                            cell.setCellValue(CUST_TYPES[1]);
                        }
                        break;
                }
            }
            rowIndex++;
        }
        for(int i = 0; i < COLUMN_HEADERS.length; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    private void setFreezePane(Sheet sheet) {
        sheet.createFreezePane(0, 1);
    }

    private void saveWorkbook(Workbook workbook) throws IOException {
        try {
            workbook.write(this.fos);
        }
        finally {
            if(this.fos != null) {
                this.fos.close();
                this.fos = null;
            }
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            TestFreezePane tf = new TestFreezePane("C:/temp/Freeze Pane
Test.xlsx");
            tf.createWorkbook();
        }
        catch(Exception ex) {
            System.out.println("Caught an: " + ex.getClass().getName());
            System.out.println("Message: " + ex.getMessage());
            System.out.println("Stacktrace follows:.....");
            ex.printStackTrace(System.out);
        }
    }
}
-- 
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-do-i-set-the-Horizontal-Freeze-on-a-worksheet-tp3340918p3343383.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message