gen_xls.java

关于生成EXCEL文件的封装类

package com.zero.genexcel;

/**
 * 创建人: 零度
 * 创建日期: 2005-10-24
 * 目 的: 生成测试Excle报表
 */
import java.io.*;
import java.sql.*;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.*;

public class gen_xls
{
	private String outExcelFile = ""; //由报表标题和报表文件名称组合成
	private String errorMessage = null; //操作中所产生的错误信息
	private int resultCount = 0; //报表记录数，默认为没有记录数
	
	public gen_xls()
	{}

	public String getOutExcelFileName()
	{
		return this.outExcelFile;
	}
	
	public void genReport( String path, String filename, String title ) 
	{
		this.outExcelFile = "测试Excel" + "@" + filename;
		
		this.createExcelFile( path, filename, title );
	}

	/**
	* 根据数据生成excel文件
	* @param crsRecorder CachedRowSet  根据数据集来生成excel
	* @param path String  excel文件所要存放的路径
	* @param fileName String  excel文件的名称
	* @param title String  报表的标题
	*/
	private void createExcelFile( String path, String fileName,	String title)
	{
		HSSFWorkbook wb = new HSSFWorkbook();
		
		HSSFSheet sheet = wb.createSheet( "Sheet1" );
		int rowCounter = 0;  //第几行

		HSSFCell cellTitle = sheet.createRow(rowCounter).createCell((short)0); //标题单元格
		cellTitle.setEncoding(HSSFCell.ENCODING_UTF_16);
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short)20);
		font.setFontName("华文行楷");
		
		HSSFCellStyle style =  wb.createCellStyle();
		style.setFont(font);
		cellTitle.setCellStyle(style);
		cellTitle.setCellType(HSSFCell.CELL_TYPE_STRING);
		cellTitle.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cellTitle.setCellValue(title);
		sheet.addMergedRegion(new Region(rowCounter, (short)0, rowCounter, (short)10));
		
		rowCounter++; //第二行
		font.setFontHeightInPoints((short)18);
		font.setFontName("宋体");
		style.setFont(font);
		cellTitle = sheet.createRow(rowCounter).createCell((short)0);
		cellTitle.setEncoding(HSSFCell.ENCODING_UTF_16);
		cellTitle.setCellType(HSSFCell.CELL_TYPE_STRING);
		cellTitle.setCellStyle(style);
		cellTitle.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellTitle.setCellValue("普  及  程  度       (7-12周岁)");
		sheet.addMergedRegion(new Region(rowCounter, (short)0, rowCounter, (short)10));
	
		rowCounter++; //第三行
		HSSFCell cellFillUnit = sheet.createRow(rowCounter).createCell((short)0);
		cellFillUnit.setEncoding(HSSFCell.ENCODING_UTF_16);
		cellFillUnit.setCellType(HSSFCell.CELL_TYPE_STRING);
		cellFillUnit.setCellValue("县(市，区)");
		cellFillUnit.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
		sheet.addMergedRegion(new Region(rowCounter, (short)0, rowCounter, (short)8));
		
		HSSFCell cellCreateTime = sheet.createRow(rowCounter).createCell((short)9);
		cellCreateTime.setEncoding(HSSFCell.ENCODING_UTF_16);
		cellCreateTime.setCellType(HSSFCell.CELL_TYPE_STRING);
////		cellCreateTime.setCellValue("制表时间: " + ReportUtil.getReportDate());
		cellCreateTime.setCellValue("制表时间: 2005-10-24" );
		sheet.addMergedRegion(new Region(rowCounter, (short)9, rowCounter, (short)10));
		rowCounter++;  //使行数转向下一行
	
		int currentRow = createReportHeader(wb, sheet, rowCounter) + 1;
		/******
		try
		{
			crsRecorder.beforeFirst();
			HSSFCell cell = null;
			HSSFDataFormat format = wb.createDataFormat();
			int columnCounter = 0; //列数
			while (crsRecorder.next())
			{
				int dataCounter = 1; //从数据库取数据的索引
				for (int i = 0; i < 11; i++)
				{
					cell = sheet.createRow(currentRow).createCell( (short) columnCounter);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);
					if (i == 0 || i == 10)
					{
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					}
					else
					{
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					}
					if (i == 0)
					{
						cell.setCellValue(crsRecorder.getString(dataCounter));
					}
					else if (i == 5)  //入学率
					{
						double allSchoolChild = new Double(crsRecorder.getInt(2)).doubleValue();
						double inSchoolChild =  new Double(crsRecorder.getInt(4)).doubleValue();
						double result = 0;
						if (allSchoolChild != 0)
						{
							result = (double) ( (inSchoolChild / allSchoolChild) * 100);
						}
						style = wb.createCellStyle();
						style.setDataFormat(format.getFormat("#.##"));
						cell.setCellStyle(style);
						cell.setCellValue(result);
					}
					else if (i == 6) //女童入学率
					{
						double firstGirlChild = new Double(crsRecorder.getInt(3)).doubleValue();
						double secondGirlChild = new Double(crsRecorder.getInt(5)).doubleValue();
						double result = 0;
						if (firstGirlChild != 0)
						{
							result = (double) ( (secondGirlChild / firstGirlChild) * 100);
						}
						style = wb.createCellStyle();
						style.setDataFormat(format.getFormat("0.00"));
						cell.setCellStyle(style);
						cell.setCellValue(result);
					}
					else if (i == 9) //辍学率
					{
						double allChild = new Double(crsRecorder.getInt(6)).doubleValue();
						double offChild = new Double(crsRecorder.getInt(7)).doubleValue();
						double result = 0;
						if (allChild != 0)
						{
							result = (double) ( (offChild / allChild) * 100);
						}
						style = wb.createCellStyle();
						style.setDataFormat(format.getFormat("0.00"));
						cell.setCellStyle(style);
						cell.setCellValue(result);
					}
					else if (i == 10)  //备注
					{
						cell.setCellValue("");
					}
					else
					{
						cell.setCellValue(crsRecorder.getInt(dataCounter));
					}
					columnCounter++;
					if (i == 5 || i == 6 || i == 9 || i == 10)
					{
						continue;
					}
					else
					{
						dataCounter++;
					}
				
				}
				columnCounter = 0;
				currentRow++;
			}
			currentRow += 2; //在第三行写报表说明
			cell = sheet.createRow(currentRow).createCell((short)0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.getCellStyle().setWrapText(true);
			cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cell.getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			String introduction = "说明：1、丧失学习能力是指痴、呆、傻和严重残疾不能上学者；\n";
			introduction += "2、学年内系指从上学年初到下学年初；\n";
			introduction += "3、辍学学生指除正常的毕业（结业）、升级、留级、转学、死亡和按规定办理了休学手续以外，所有中途离开学校的学生\n";
			introduction += "4、(5)=(3) / (1) * 100%；5、(6)=(4) / (2) * 100%；6、(9) = (8) / (7) *100%";
			cell.setCellValue(introduction);
			sheet.addMergedRegion(new Region(currentRow, (short)0, currentRow + 4, (short)10));
		}
		catch (SQLException ex)
		{
			System.out.println("生成报表产生错误: " + ex.getMessage());
			ex.printStackTrace();
			errorMessage = "生成报表失败! " + ex.getMessage();
		}
		
		*****/
		HSSFCell cell = null;
		currentRow += 2; //在第三行写报表说明
			cell = sheet.createRow(currentRow).createCell((short)0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.getCellStyle().setWrapText(false);
			cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cell.getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			String introduction = "说明：1、丧失学习能力是指痴、呆、傻和严重残疾不能上学者；\n";
			introduction += "2、学年内系指从上学年初到下学年初；\n";
			introduction += "3、辍学学生指除正常的毕业（结业）、升级、留级、转学、死亡和按规定办理了休学手续以外，所有中途离开学校的学生\n";
			introduction += "4、(5)=(3) / (1) * 100%；5、(6)=(4) / (2) * 100%；6、(9) = (8) / (7) *100%";
			cell.setCellValue(introduction);
			sheet.addMergedRegion(new Region(currentRow, (short)0, currentRow + 4, (short)10));
		
		FileOutputStream fout = null;
		try
		{
			fout = new FileOutputStream(path + fileName);