Solved

Formatting Excel using Java

Posted on 2011-09-30
5
701 Views
Last Modified: 2012-05-12
I typically work in Access using VBA and a little bit in Excel, but never have experienced Java. I have been managing a web project for a couple of years where one of the features allows the user to export a report to Excel. The developer has created a very nice Excel export. However, we have a couple of requests by the users that he can't figure out. I am simply trying to research for him.

The requests by the users are:

1. Freeze panes to row 1-5 frozen in heading
2. Width is printing onto 2 pages.  Can we get it onto 1 page width?

Does anyone know if this can be done in Java? If so, how?

I asked him to send me the section of code that's used for the Excel export and have attached it here.

Thanks for any help.
This is the main class that renders the Excel file for One To Many:

package com.foomatrix.web.servlet.foo.view;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

import com.foomatrix.domain.Alfalfa;
import com.foomatrix.domain.Corn;
import com.foomatrix.domain.Cotton;
import com.foomatrix.domain.Crop;
import com.foomatrix.domain.Product;
import com.foomatrix.domain.Rice;
import com.foomatrix.domain.SilageCorn;
import com.foomatrix.domain.Sorghum;
import com.foomatrix.domain.Soybean;
import com.foomatrix.domain.Sunflower;
import com.foomatrix.domain.Wheat;
import com.foomatrix.domain.WinterWheat;
import com.foomatrix.domain.readonly.OneToManyResult;
import com.foomatrix.domain.support.CropVisitor;
import com.foomatrix.domain.support.Fields;

public class OneToManyExcelView extends AbstractExcelReportView {

	class ExtraColumns implements CropVisitor {

		private int col;

		private HSSFRow dataRow;

		private HSSFCellStyle dataStyle;

		private OneToManyResult result;

		private HSSFWorkbook workbook;

		public ExtraColumns(HSSFWorkbook workbook, HSSFRow dataRow,
				HSSFCellStyle dataStyle, int col, OneToManyResult result) {
			this.col = col;
			this.workbook = workbook;
			this.dataRow = dataRow;
			this.dataStyle = dataStyle;
			this.result = result;
		}

		public int getCol() {
			return col;
		}

		@Override
		public void visit(Alfalfa alfalfa) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Corn corn) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Cotton cotton) {
			if (result != null) {
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getSelectedProductAverageLoanValue(),
						DataFormat.DECIMAL4_FORMAT);
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getComparedProductAverageLoanValue(),
						DataFormat.DECIMAL4_FORMAT);
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getLoanValueDifference(),
						DataFormat.DECIMAL4_FORMAT);
			} else {
				HSSFCell dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.benchmark.average.loan.value"));
				dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.compared.average.loan.value"));
				dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.loan.value.difference"));
			}
		}

		@Override
		public void visit(Rice rice) {
			visitMoistureCrop();
		}

		@Override
		public void visit(SilageCorn silageCorn) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Sorghum sorghum) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Soybean soybean) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Sunflower sunflower) {
			visitMoistureCrop();
		}

		@Override
		public void visit(Wheat wheat) {
			visitMoistureCrop();
		}

		@Override
		public void visit(WinterWheat winterWheat) {
			visitMoistureCrop();
		}

		private void visitMoistureCrop() {
			if (result != null) {
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getMaturity(), DataFormat.NUMBER_FORMAT);
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getSelectedProductAverageMoisture(),
						DataFormat.PERCENT_DECIMAL_FORMAT);
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getComparedProductAverageMoisture(),
						DataFormat.PERCENT_DECIMAL_FORMAT);
				createDataCell(workbook, dataRow, dataStyle, col++,
						result.getMoistureDifference(),
						DataFormat.PERCENT_DECIMAL_FORMAT);
			} else {
				/* Headers only */
				HSSFCell dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.maturity.short"));
				dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.benchmark.average.moisture"));
				dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.compared.average.moisture"));
				dataCell = dataRow.createCell(col++);
				dataCell.setCellStyle(dataStyle);
				setCellValue(
						dataCell,
						getMessageSourceAccessor().getMessage(
								"columns.moisture.difference.short"));
			}
		}

	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		List<OneToManyResult> oneToManyResultList = (List) model
				.get("oneToManyResultList");
		Product benchmark = (Product) model.get("benchmark");
		Crop crop = (Crop) model.get("crop");
		Fields comparisonField = (Fields) model.get("comparisonField");

		String title = getMessageSourceAccessor().getMessage(
				"one.to.many.title");
		HSSFSheet sheet = workbook.createSheet(title);

		HSSFCellStyle dataStyle = createDataStyle(workbook);
		HSSFCellStyle centeredDataStyle = createCenteredDataStyle(workbook);
		HSSFCellStyle labelStyle = createLabelStyle(workbook);
		createFormatMap(workbook);

		int row = 0;
		int col = 0;
		int freezeColumn = col;
		int lastColumn = col + 7;
		int headerRow = row;
		row = createHeader(workbook, sheet, row, col, lastColumn, null);

		sheet.createRow(row++); /* Spacer */

		HSSFRow summaryRow = sheet.createRow(row++);
		createDataCell(workbook, summaryRow, labelStyle, col++,
				getMessageSourceAccessor().getMessage("one.to.many.benchmark"),
				null);
		createDataCell(workbook, summaryRow, dataStyle, col, benchmark
				.getBrand().getName() + " - " + benchmark.getName(), null);
		createDataCell(workbook, summaryRow, dataStyle, col + 1, null, null);
		sheet.addMergedRegion(new CellRangeAddress(summaryRow.getRowNum(),
				summaryRow.getRowNum(), col, col + 1));
		col++;
		sheet.createRow(row++); /* Spacer */

		DataFormat comparisonFormat = null;
		String comparisonColumnCode = null;
		if (Fields.PRICE_PER_ACRE.equals(comparisonField)) {
			comparisonFormat = DataFormat.DECIMAL2_FORMAT;
			comparisonColumnCode = "columns.price.per.acre.difference";
		} else {
			comparisonFormat = DataFormat.DECIMAL1_FORMAT;
			comparisonColumnCode = "columns.yield.difference";
		}

		row = createDataHeaderRow(workbook, sheet, row, crop,
				comparisonColumnCode);
		int freezeRow = row;

		sheet.createFreezePane(freezeColumn, freezeRow);

		for (OneToManyResult result : oneToManyResultList) {
			HSSFRow dataRow = sheet.createRow(row++);
			col = 0;
			createDataCell(workbook, dataRow, dataStyle, col++,
					result.getBrand(), null);
			createDataCell(workbook, dataRow, dataStyle, col++,
					result.getComparedProduct(), null);
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getTestCount(), DataFormat.NUMBER_FORMAT);
			ExtraColumns extraColumns = new ExtraColumns(workbook, dataRow,
					dataStyle, col, result);
			crop.accept(extraColumns);
			col = extraColumns.getCol();
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getWins(), DataFormat.NUMBER_FORMAT);
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getLosses(), DataFormat.NUMBER_FORMAT);
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getSelectedProductAverage(), comparisonFormat);
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getComparedProductAverage(), comparisonFormat);
			createDataCell(workbook, dataRow, centeredDataStyle, col++,
					result.getDifference(), comparisonFormat);
		}

		int firstFilterRow = row;
		int lastFilterRow = createFilterRows(workbook, sheet, labelStyle,
				dataStyle, model, row, lastColumn);
		row = lastFilterRow + 1;

		createDateStamp(workbook, sheet, dataStyle, row, model);
		// autoSizeColumns(sheet, 0, lastColumn);
		// autoSizeRows(workbook, sheet, firstFilterRow, lastFilterRow);

		sheet.getRow(headerRow).getCell(0)
				.setCellValue(new HSSFRichTextString(title));

		createFileName(response, "one.to.many.excel.file.name", crop.getName(),
				benchmark.getBrand().getName(), benchmark.getName());
	}

	private int createDataHeaderRow(HSSFWorkbook workbook, HSSFSheet sheet,
			int row, Crop crop, String comparisonColumnCode) {
		int col = 0;
		HSSFCellStyle dataHeaderStyle = createDataHeaderStyle(workbook);
		HSSFRow dataHeaderRow = sheet.createRow(row++);
		dataHeaderRow.setHeight((short) 750);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage("columns.brand"), null);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage("columns.product"), null);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage("columns.total.tests"),
				null);
		ExtraColumns extraColumns = new ExtraColumns(workbook, dataHeaderRow,
				createExtraColumnsStyle(workbook), col, null);
		crop.accept(extraColumns);
		col = extraColumns.getCol();
		createDataCell(
				workbook,
				dataHeaderRow,
				dataHeaderStyle,
				col++,
				getMessageSourceAccessor().getMessage("columns.benchmark.wins"),
				null);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage("columns.product.wins"),
				null);
		createDataCell(
				workbook,
				dataHeaderRow,
				dataHeaderStyle,
				col++,
				getMessageSourceAccessor().getMessage("columns.benchmark.mean"),
				null);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage("columns.product.mean"),
				null);
		createDataCell(workbook, dataHeaderRow, dataHeaderStyle, col++,
				getMessageSourceAccessor().getMessage(comparisonColumnCode),
				null);
		return row;
	}

	private HSSFCellStyle createExtraColumnsStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.WHITE.index);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 12);
		style.setFont(font);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFPalette palette = workbook.getCustomPalette();
		palette.setColorAtIndex(HSSFColor.PINK.index, (byte) 225, (byte) 168,
				(byte) 165);
		style.setFillForegroundColor(HSSFColor.PINK.index);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);
		return style;
	}
}

Open in new window

0
Comment
Question by:dlogan7
  • 3
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 36895075


http://poi.apache.org/spreadsheet/quick-guide.html


This is the example how to freeze panes with POI:
------------------------------------
Splits and freeze panes

There are two types of panes you can create; freeze panes and split panes.

A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism:

sheet1.createFreezePane( 3, 2, 3, 2 );

The first two parameters are the columns and rows you wish to split by. The second two parameters indicate the cells that are visible in the bottom right quadrant.

Split pains appear differently. The split area is divided into four separate work area's. The split occurs at the pixel level and the user is able to adjust the split by dragging it to a new position.

Split panes are created with the following call:

sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );

The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel. The second parameter is the y position of the split. Again in 1/20th of a point.

The last parameter indicates which pane currently has the focus. This will be one of Sheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.

    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    Sheet sheet2 = wb.createSheet("second sheet");
    Sheet sheet3 = wb.createSheet("third sheet");
    Sheet sheet4 = wb.createSheet("fourth sheet");

    // Freeze just one row
    sheet1.createFreezePane( 0, 1, 0, 1 );
    // Freeze just one column
    sheet2.createFreezePane( 1, 0, 1, 0 );
    // Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
    sheet3.createFreezePane( 2, 2 );
    // Create a split with the lower left side being the active quadrant
    sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
-----------------------------------------------------

Maybe zoom could help with printing?
---------------------------------------------

Set the zoom magnification

The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator.

    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("new sheet");
    sheet1.setZoom(3,4);   // 75 percent magnification
                   
   -------------------------------------------------------
                 
0
 

Author Comment

by:dlogan7
ID: 36895760
Zoom only changes the appearance on the screen. Never thought about asking it this way: what I'm looking for is the command to do this:  .FitToPagesWide = 1

Better yet, is there a list of commands for POI? Duh, just saw your link. Will check it out.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36895771
It looks there is something appropriate:
(look at the saem link above)

Fit Sheet to One Page

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    ps.setFitHeight((short)1);
    ps.setFitWidth((short)1);


    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                   
0
 

Author Comment

by:dlogan7
ID: 36895944
Yep. I bet this one line does itL   ps.setFitWidth((short)1);

Not setting the Height will allow longer pages to continue to the next page. Thanks a bunch.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36895948

Great! You are always welcome.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now