Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

Formatting Excel using Java

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
Dale Logan
Asked:
Dale Logan
  • 3
  • 2
1 Solution
 
for_yanCommented:


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
 
Dale LoganConsultantAuthor Commented:
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
 
for_yanCommented:
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
 
Dale LoganConsultantAuthor Commented:
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
 
for_yanCommented:

Great! You are always welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now