Solved

Formatting Excel using Java

Posted on 2011-09-30
5
712 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

20 Experts available now in Live!

Get 1:1 Help Now