?
Solved

Formatting Excel using Java

Posted on 2011-09-30
5
Medium Priority
?
772 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:Dale Logan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 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:Dale Logan
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:Dale Logan
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll

765 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