通过POI程序进行excel文件格式的导出

1.   需要导入相关的jar包poi-3.2.jar，poi-contrib-3.2.jar，poi-scratchpad-3.2.jar 等

2.   使用导出模板工具类：

       

package cn.ffcs.icity.module.filehandle.poi.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
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.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 cn.ffcs.icity.module.filehandle.poi.om.vo.ExcelFileHandleVO;
import cn.ffcs.smartcity.util.CollectionUtil;


/**
 * 通过POI组建进行Excel的相关文件输出
 * @author linwei
 *
 */
public class XLSExporter {

	private final static Logger log = Logger.getLogger(XLSExporter.class);
	private HSSFWorkbook workbook;
	private HSSFSheet sheet;
	private HSSFRow row; 
	private HSSFCellStyle normalStyle;
	private HSSFCellStyle headStyle;
	private HSSFCellStyle commentStyle;
	private final static String NORMAL_STR = "normal";
	private final static String COMMENT_STR = "comment";
	private final static String HEAD_STR = "head";	
	
	/**
	 * 初始化Excel
	 * 
	 * @param fileName
	 * 			导出文件名
	 */
	private XLSExporter() {
		
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet();
		sheet.setDefaultColumnWidth(15);
		sheet.setColumnWidth(2, 7000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);

		// 建立正常cell样式
		normalStyle = workbook.createCellStyle();
		// 字体
		HSSFFont normalfont = workbook.createFont();
		normalfont.setFontHeightInPoints((short) 12);
		normalfont.setFontName("宋体");
		normalStyle.setFont(normalfont);
		// 边框，对齐
		normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

		// 建立表头的cell样式
		headStyle = workbook.createCellStyle();
		headStyle.cloneStyleFrom(normalStyle);
		// 字体
		HSSFFont headFont = workbook.createFont();
		headFont.setFontHeightInPoints((short) 12);
		headFont.setFontName("宋体");
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headStyle.setFont(headFont);
		// 颜色
		headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headStyle.setFillForegroundColor(new HSSFColor.PALE_BLUE().getIndex());

		// 建立评论的cell样式
		commentStyle = workbook.createCellStyle();
		commentStyle.cloneStyleFrom(normalStyle);
		// 边框，对齐
		commentStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		// 字体
		HSSFFont commentFont = workbook.createFont();
		commentFont.setFontHeightInPoints((short) 12);
		commentFont.setFontName("宋体");
		commentFont.setItalic(true);
		commentFont.setColor(new HSSFColor.GREY_40_PERCENT().getIndex());
		commentStyle.setFont(commentFont);
	}

	/**
	 * 导出Excel文件
	 * 
	 * @param out
	 * 			OutputStream输出流
	 * @throws Exception
	 * 			Exception异常
	 */
	private void exportXLS(OutputStream out) throws Exception {
		
		try {
			workbook.write(out);
			out.flush();
			out.close();
		} catch (IOException e) {
			throw e;
		}
	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 * 			行号
	 */
	private HSSFRow createRow(int index) {
		
		this.row = this.sheet.createRow(index);
		this.row.setHeightInPoints(25);
		return this.row;
	}
	
	/**
	 * 通过行号获取固定行
	 * @param index
	 * @return
	 */
	private HSSFRow getRowByIndex(int rowIndex) {
		
		return this.sheet.getRow(rowIndex);
	}
	
	/**
	 * 通过传递行以及对应的列数量，获取CELL值
	 * @param row
	 * @param columnIndex
	 * @return
	 */
	private String getValueFromRowCell(HSSFRow row,int columnIndex) {
		
		return row.getCell(columnIndex).getStringCellValue();
	}
	
	/**
	 * 设置单元格
	 * 
	 * @param index
	 * 			列号
	 * @param value
	 * 			单元格的(字符串)填充值
	 * @param style
	 * 			单元格样式(字符串)
	 */
	@SuppressWarnings("deprecation")
	private void setCell(int index, String value, String style) {
		
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);

		if (style.equals("head")) {
			cell.setCellStyle(headStyle);
		} else if (style.equals("comment")) {
			cell.setCellStyle(commentStyle);
		} else {
			cell.setCellStyle(normalStyle);
		}
	}
	
	/**
	 * 设置单元格
	 * 
	 * @param index
	 * 			列号
	 * @param value
	 * 			单元格的(数字)填充值
	 */
	@SuppressWarnings("deprecation")
	private void setCell(int index, double value) {
		
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		cell.setCellStyle(normalStyle);
	}
	
	/**
	 * 合并单元格
	 * 
	 * @param rowIndexBegin
	 * 			行起始号
	 * @param rowIndexEnd
	 * 			行结尾号
	 * @param colIndexBegin
	 * 			列起始号
	 * @param colIndexEnd
	 * 			列结尾号
	 */
	private void mergeCell(int rowIndexBegin, int rowIndexEnd, int colIndexBegin, int colIndexEnd) {
		
		sheet.addMergedRegion( new CellRangeAddress(
				rowIndexBegin, rowIndexEnd, (short) colIndexBegin, (short) colIndexEnd) );
	}
	
	
	/**
	 * 该方法用来实现对应的文件输出
	 * @param excelFileHandleVO
	 */
	public static void outputExcel(ExcelFileHandleVO excelFileHandleVO) {
		
		int size = excelFileHandleVO.getAssisMap().size();
		
		XLSExporter e = new XLSExporter();
		e.createRow(0).setHeightInPoints(40);
		e.mergeCell(0, 0, 1, size);
		
		for(int i=1;i<=size;i++) {
			if(i == 1) {
				e.setCell(1, excelFileHandleVO.getTitle(), NORMAL_STR);
			} else {
				e.setCell(i, "",NORMAL_STR);
			}
		}

		e.createRow(1).setHeightInPoints(15);
		e.mergeCell(1, 1, 1, size);
		
		for(int i=1;i<=size;i++) {
			if(i == 1) {
				e.setCell(1, "统计时间：(" + (excelFileHandleVO.getBeginTime() == null ? "起始日期" : excelFileHandleVO.getBeginTime()) + 
						"——" + (excelFileHandleVO.getEndTime() == null ? "当前" : excelFileHandleVO.getEndTime()) + ")", COMMENT_STR);
			} else {
				e.setCell(i, "",NORMAL_STR);
			}
		}

		e.createRow(2);
		for(int i=1;i<=size;i++) {
			String a = (String)excelFileHandleVO.getAssisMap().get(String.valueOf(i));
			String value = (String)excelFileHandleVO.getColumnNameMap().get(a);
			e.setCell(i,value, HEAD_STR);
		}

		try {

			int row = 3;
			int no = 1;
			int total = 0; // 总计次数
			
			List list = excelFileHandleVO.getList();
			for(int i=0;i<list.size();i++) {
				e.createRow(row++);
				Map map = (HashMap)list.get(i);
				for(int j=1;j<=size;j++) {
					if(j == 1) {
						e.setCell(j, no);
					} else {
						e.setCell(j, (String)map.get(excelFileHandleVO.getAssisMap().get(String.valueOf(j))), NORMAL_STR);
					}
				}
				no++;
			}
			
			if(!CollectionUtil.isMapNULL(excelFileHandleVO.getTotalMap())) {
				e.createRow(row);
				for(int i=1;i<=size;i++) {
					Object o = excelFileHandleVO.getTotalMap().get(excelFileHandleVO.getAssisMap().get(String.valueOf(i)));
					if(o instanceof Integer) {
						e.setCell(i, (Integer)o);
					} else {
						e.setCell(i, (String)o, NORMAL_STR);
					}
				}
			}
			
			e.exportXLS(excelFileHandleVO.getResponse().getOutputStream());
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("in XLSExporter,e is " + ex.getMessage());
		} finally {

		}
		
	}
	
}

3.   使用例子为：

       

String fileName = "这个是测试的例子.xls";
				response.setContentType("application/vnd.ms-excel");
				response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"iso-8859-1"));
				
//				XLSExporter.outputExcel("", "2012-01-01", "2012-05-01", response.getOutputStream());	
				
				Map assisMap = new HashMap();
				assisMap.put("1", "no");
				assisMap.put("2", "test1");
				assisMap.put("3", "test2");
				
				Map columnNameMap = new HashMap();
				columnNameMap.put("no", "NO");
				columnNameMap.put("test1", "测试字段1");
				columnNameMap.put("test2", "测试字段2");
				
				List list = new ArrayList();
				Map m = new HashMap(); 
				m.put("test1", "测试value1");
				m.put("test2", "测试value2");
				list.add(m);
				m = new HashMap(); 
				m.put("test1", "测试value11");
				m.put("test2", "测试value22");
				list.add(m);
				
//				Map totalMap = new HashMap();
//				totalMap.put("no", "总计");
//				totalMap.put("test1", 123);
//				totalMap.put("test2", "450");
				
				ExcelFileHandleVO excelFileHandleVO = new ExcelFileHandleVO();
				excelFileHandleVO.setAssisMap(assisMap);
//				excelFileHandleVO.setTotalMap(totalMap);
				excelFileHandleVO.setColumnNameMap(columnNameMap);
				excelFileHandleVO.setFileName(fileName);
				excelFileHandleVO.setList(list);
				excelFileHandleVO.setTitle("标题");
				excelFileHandleVO.setBeginTime("2012-05-01 22:00:00");
				excelFileHandleVO.setEndTime("2012-07-01 22:01:00");
				excelFileHandleVO.setRequest(request);
				excelFileHandleVO.setResponse(response);
				
				excelFileHandleService.writeExcelFile(excelFileHandleVO);

    

package cn.ffcs.icity.module.filehandle.poi.service.impl;

import java.io.UnsupportedEncodingException;

import cn.ffcs.exception.ServiceException;
import cn.ffcs.icity.module.filehandle.poi.om.vo.ExcelFileHandleVO;
import cn.ffcs.icity.module.filehandle.poi.service.IExcelFileHandleService;
import cn.ffcs.icity.module.filehandle.poi.util.XLSExporter;
import cn.ffcs.smartcity.util.CollectionUtil;


/**
 * excel文件下载操作的相关服务类
 * @author linwei
 *
 */
public class ExcelFileHandleServiceImpl implements IExcelFileHandleService {

    /**
     * 通过该方法来进行excel格式文件的相关下载操作
     * 
     * @param excelFileHandleVO
     * @throws ServiceException
     */
	public void writeExcelFile(ExcelFileHandleVO excelFileHandleVO) throws ServiceException {

		//进行相关参数的校验操作
		this.checkExcelFileHandleVO(excelFileHandleVO);
		
		try {
			//进行相关的response操作
			excelFileHandleVO.getResponse().setContentType("application/vnd.ms-excel");
			excelFileHandleVO.getResponse().setHeader("Content-Disposition", "attachment;filename=" + new String(excelFileHandleVO.getFileName().getBytes("gb2312"),"iso-8859-1"));
		} catch (UnsupportedEncodingException e) {
			throw new ServiceException("in ExcelFileHandleServiceImpl.writeExcelFile, e is " + e.getMessage());
		}
		
		//进行相关的文件下载操作
		XLSExporter.outputExcel(excelFileHandleVO);
	}

	/**
	 * 进行相关操作的校验
	 * @param excelFileHandleVO
	 * @throws ServiceException
	 */
	private void checkExcelFileHandleVO(ExcelFileHandleVO excelFileHandleVO) throws ServiceException {
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO)) {
			throw new ServiceException("此时excelFileHandleVO为空，请确保excelFileHandleVO不为空。");
		}
		
		String fileName = excelFileHandleVO.getFileName();
		if(CollectionUtil.isObjectNULL(fileName)) {
			throw new ServiceException("文件名参数fileName不能为空。");
		} else {
			String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
			if(!("XLS".equals(suffix.toUpperCase()) || "XLSX".equals(suffix.toUpperCase()))) {
				throw new ServiceException("此处，文件名后缀只能为xls或者xlsx。");
			}
		}
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO.getTitle())) {
			throw new ServiceException("参数title不能为空。");
		}
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO.getBeginTime())) {
			throw new ServiceException("参数beginTime不能为空。");
		}
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO.getEndTime())) {
			throw new ServiceException("参数endTime不能为空。");
		}
		
		if(CollectionUtil.isMapNULL(excelFileHandleVO.getAssisMap())) {
			throw new ServiceException("AssisMap不能为空。");
		}
		
//		if(CollectionUtil.isMapNULL(excelFileHandleVO.getTotalMap())) {
//			throw new ServiceException("totalMap不能为空。");
//		}
		
		if(CollectionUtil.isMapNULL(excelFileHandleVO.getColumnNameMap())) {
			throw new ServiceException("columnNameMap不能为空。");
		}
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO.getRequest())) {
			throw new ServiceException("参数request不能为空。");
		}
		
		if(CollectionUtil.isObjectNULL(excelFileHandleVO.getResponse())) {
			throw new ServiceException("参数response不能为空。");
		}
		
	}
	
}