excelutils.java

本文论述了一个前台笔记本销售系统的开发过程

package com.set.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * <p>
 * Title: 代码生成框架
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2005
 * </p>
 * <p>
 * Company:
 * </p>
 * 
 * @author zhifeng
 * @version 1.0
 */

public class ExcelUtils {
	ArrayList fields = new ArrayList();

	int fieldLoop = 0;

	public ExcelUtils() {
	}

	private String getProperty(Object bean, Method[] method, String methodName) {
		String property = "";
		for (int i = 0; i < method.length; i++) {
			try {
				if (method[i].getName().startsWith("get")) {
					if ((methodName.toLowerCase().trim()).equals(method[i]
							.getName().substring(3).toLowerCase().trim()))
						property = (String) method[i].invoke(bean, null);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return property;
	}

	private void setSheet(WritableSheet sheet, HashMap params, List gvos)
			throws Exception {

		int cols, rows;
		cols = sheet.getColumns();
		rows = sheet.getRows();
		CellFormat wcfF = null;
		Cell cell;
		Label labelCF;
		String content = "";

		if (null == gvos)
			throw new Exception("error.arguments");
		this.fieldLoop = gvos.size();

		ArrayList fields = new ArrayList();

		boolean firstField = true;
		for (int j = 0; j < rows; j++) {
			for (int i = 0; i < cols; i++) {
				cell = sheet.getCell(i, j);
				if (null != cell && !"".equals(cell.getContents().trim())) {
					String tmp = cell.getContents().trim();
					wcfF = cell.getCellFormat();
					if (tmp.startsWith("$F{")) {
						if (firstField == true) {
							firstField = false;
							// 取得参数列表
							fields = this.evalFields(sheet, j, i);
							ExcelRptMgr.fillFields(fields, gvos, sheet);
							j += gvos.size();
							rows += gvos.size();

						}

					} else if (tmp.startsWith("$P{")) {
						String propertity = this.getParameterName(tmp);
						content = String.valueOf(params.get(propertity));
						labelCF = new Label(i, j, content, wcfF);
						sheet.addCell(labelCF);
					}
				}
			}
		}

	}

	private void setSheet(WritableSheet sheet, Object bean, Method[] method)
			throws Exception {
		int cols, rows;
		cols = sheet.getColumns();
		rows = sheet.getRows();
		CellFormat wcfF;
		Cell cell;
		Label labelCF;
		String content;
		for (int i = 0; i < cols; i++) {
			for (int j = 0; j < rows; j++) {
				cell = sheet.getCell(i, j);
				if (null != cell && cell.getContents().trim().startsWith("$")) {
					wcfF = cell.getCellFormat();
					content = this.getProperty(bean, method, cell.getContents()
							.trim().substring(1));
					labelCF = new Label(i, j, content, wcfF);
					sheet.addCell(labelCF);
				}
			}
		}

	}

	public String createExcelFromTemplate(String templatePath, Object[] bean,
			Class[] beanClass, String fileName) throws Exception {

		Workbook wb = Workbook.getWorkbook(new FileInputStream(templatePath));
		WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(
				fileName), wb);

		int numberOfSheets = wb.getNumberOfSheets();
		WritableSheet sheet;
		Method method[] = null;
		for (int i = 0; i < numberOfSheets; i++) {
			sheet = wwb.getSheet(i);
			method = beanClass[i].getDeclaredMethods();
			this.setSheet(sheet, bean[i], method);
		}

		wwb.write();
		wwb.close();
		wb.close();

		return fileName;
	}

	public void createExcelFromTemplate(String templatePath, String fileName,
			HashMap params, List gvos, HttpServletResponse response)
			throws Exception {

		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=\""
				+ fileName + ".XLS\"");

		Workbook wb = Workbook.getWorkbook(new FileInputStream(templatePath));
		// WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(
		// fileName), wb);
		WritableWorkbook wwb = Workbook.createWorkbook(response
				.getOutputStream(), wb);

		int numberOfSheets = wb.getNumberOfSheets();
		WritableSheet sheet;
		for (int i = 0; i < numberOfSheets; i++) {
			sheet = wwb.getSheet(i);
			this.setSheet(sheet, params, gvos);
		}

		wwb.write();
		wwb.close();
		wb.close();

	}

	private String getParameterName(String name) {
		String res = "";
		if (null != name && name.length() > 4) {
			res = name.substring(3, name.length() - 1);
		}

		return res;
	}

	private ArrayList evalFields(WritableSheet sheet, int row, int column) {
		int cols = sheet.getColumns();
		Cell cell;

		int j = row;
		for (int i = column; i < cols; i++) {
			cell = sheet.getCell(i, j);
			if (null != cell) {
				String tmp = cell.getContents().trim();
				if (tmp.startsWith("$F{")) {
					String propertity = this.getParameterName(tmp);
					fields.add(new ExcelField(String.valueOf(row), String
							.valueOf(i), propertity));
				}
			}
		}
		return fields;
	}
}