poi 读取Excel文件模板生成报表文件

采用poi实现报表功能。

支持excel中多sheet，支持单个sheet页中多表格

1.将excel模板文件中的头、底部的内容读入内存 

2.提取模板列数据、格式 

3.创建新的XSSFWorkbook对象

4.添加数据（报表内容，代码未体现）

5.将新文件写入到本地磁盘

6.所需jar：

sheet1：

sheet2：

 55行

package com.suixingpay;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.suixingpay.domain.BookDomain;
import com.suixingpay.domain.SheetDomain;
import com.suixingpay.domain.TableDomain;
/**
 * 
 * @author Fu Wei
 * @date 2012-12-18 下午6:12:33
 * @Package com.suixingpay
 * @version  V1.0
 * @Description TODO
 */
public class PoiTest {
	public static void main(String[] args) {
		// 第一个Table
		TableDomain tableDomain1 = new TableDomain();
		tableDomain1.setStartRowNum(0);
		tableDomain1.setEndRowNum(7);
		tableDomain1.setTotalRowLen(2);
		// 第二个Table
		TableDomain tableDomain2 = new TableDomain();
		tableDomain2.setStartRowNum(9);
		tableDomain2.setEndRowNum(16);
		tableDomain2.setTotalRowLen(2);

		List<TableDomain> tables = new ArrayList<TableDomain>();
		tables.add(tableDomain1);
		tables.add(tableDomain2);
 
		SheetDomain sheetDomain1 = new SheetDomain();
		sheetDomain1.setTables(tables);
		sheetDomain1.setSheetName("优惠信息情况统计报表");
		
		// 第二个sheet
		TableDomain tableDomain3 = new TableDomain();
		tableDomain3.setStartRowNum(0);
		tableDomain3.setEndRowNum(55);
		tableDomain3.setTotalRowLen(1);
		List<TableDomain> tables2 = new ArrayList<TableDomain>();
		tables2.add(tableDomain3);
  
		SheetDomain sheetDomain2 = new SheetDomain();
		sheetDomain2.setTables(tables2);
		sheetDomain2.setSheetName("测试");
		
		//添加sheet
		List<SheetDomain> sheets = new ArrayList<SheetDomain>();
		sheets.add(sheetDomain1);
		sheets.add(sheetDomain2);
		// book文件
		BookDomain bookDomain = new BookDomain();
		bookDomain.setBookName("xx.xlsx");
		bookDomain.setPath("E:\\");
		bookDomain.setSheets(sheets);

		PoiTest p = new PoiTest();
		XSSFWorkbook wk = p.createXSSFWorkBook("E:\\a.xlsx", bookDomain);
		p.writeWorkBook(wk, new File(bookDomain.getPath() + bookDomain.getBookName()));
	}

	/**
	 * 写xls文件
	 * 
	 * @param wk
	 * @param file
	 */
	public void writeWorkBook(XSSFWorkbook wk, File file) {
		try {
			OutputStream out = new FileOutputStream(file);
			wk.write(out);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置列样式
	 * 
	 * @param srcStyle
	 *            源 模板样式
	 * @param desStyle
	 *            目标样式
	 */
	public void setCellStyle(XSSFCellStyle srcStyle, XSSFCellStyle desStyle) {
		// 数据格式
		desStyle.setDataFormat(srcStyle.getDataFormat());
		desStyle.setRotation(srcStyle.getRotation());
		// 填充单元格
		desStyle.setFillPattern(srcStyle.getFillPattern());
		// 列填充前景色
		XSSFColor foregroundColor = srcStyle.getFillForegroundColorColor();
		if (foregroundColor != null) {
			desStyle.setFillForegroundColor(foregroundColor);
		}
		// 边框样式
		desStyle.setBorderBottom(srcStyle.getBorderBottom());
		desStyle.setBorderTop(srcStyle.getBorderTop());
		desStyle.setBorderLeft(srcStyle.getBorderLeft());
		desStyle.setBorderRight(srcStyle.getBorderRight());
		// 对齐方向
		desStyle.setAlignment(srcStyle.getAlignment());
		desStyle.setVerticalAlignment(srcStyle.getVerticalAlignment());
	}

	/**
	 * 设置字体
	 * 
	 * @param srcFont
	 *            源模板字体
	 * @param desFont
	 *            目标字体
	 */
	public void setFont(Font srcFont, Font desFont) {
		desFont.setColor(srcFont.getColor());
		desFont.setFontName(srcFont.getFontName());
		desFont.setItalic(srcFont.getItalic());
		desFont.setBoldweight(srcFont.getBoldweight());
		desFont.setFontHeight(srcFont.getFontHeight());
		desFont.setCharSet(srcFont.getCharSet());
	}

	/**
	 * 列拷贝
	 * 
	 * @param srcCell
	 * @param desCell
	 * @param wookBook
	 */
	public void copyCell(XSSFCell srcCell, XSSFCell desCell, XSSFWorkbook wookBook) {
		XSSFCellStyle srcStyle = srcCell.getCellStyle();
		XSSFCellStyle desStyle = wookBook.createCellStyle();
		Font srcFont = srcStyle.getFont();
		Font desFont = wookBook.createFont();
		// 设置列style
		setCellStyle(srcStyle, desStyle);
		// 设置列字体
		setFont(srcFont, desFont);
		// 添加字体
		desStyle.setFont(desFont);
		// 列添加样式
		desCell.setCellStyle(desStyle);
		// 列值拷贝
		copyValue(srcCell, desCell);
	}

	/**
	 * 列中的值拷贝
	 * 
	 * @param srcCell
	 * @param distCell
	 */
	public void copyValue(XSSFCell srcCell, XSSFCell distCell) {
		int srcCellType = srcCell.getCellType();
		distCell.setCellType(srcCellType);
		if (srcCellType == XSSFCell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
				distCell.setCellValue(srcCell.getDateCellValue());
			} else {
				distCell.setCellValue(srcCell.getNumericCellValue());
			}
		} else if (srcCellType == XSSFCell.CELL_TYPE_STRING) {
			distCell.setCellValue(srcCell.getRichStringCellValue());
		} else if (srcCellType == XSSFCell.CELL_TYPE_BLANK) {
		} else if (srcCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
			distCell.setCellValue(srcCell.getBooleanCellValue());
		} else if (srcCellType == XSSFCell.CELL_TYPE_ERROR) {
			distCell.setCellErrorValue(srcCell.getErrorCellValue());
		} else if (srcCellType == XSSFCell.CELL_TYPE_FORMULA) {
			distCell.setCellFormula(srcCell.getCellFormula());
		} else {
		}
	}

	/**
	 * 创建xls文件
	 * 
	 * @param tmpFilePath
	 *            模板文件路径
	 * @param bookDomain
	 *            模板配置对象
	 * @return
	 */
	public XSSFWorkbook createXSSFWorkBook(String tmpFilePath, BookDomain bookDomain) {
		// 目标book
		XSSFWorkbook desWworkBook = new XSSFWorkbook();
		InputStream is = null;
		try {
			is = new FileInputStream(new File(tmpFilePath));
			// 源book
			XSSFWorkbook srcWorkBook = new XSSFWorkbook(is);
			if (srcWorkBook != null) {
				for (int i = 0; i < bookDomain.getSheetNum(); i++) {
					XSSFSheet srcSheet = srcWorkBook.getSheetAt(i);
					XSSFSheet desSheet = desWworkBook.createSheet();
					List<TableDomain> tables = bookDomain.getSheets().get(i).getTables();
					for (TableDomain table : tables) {
						for (int j = table.getStartRowNum(); j < table.getEndRowNum(); j++) {
							// 提取行
							XSSFRow srcRow = srcSheet.getRow(j);
							// 创建行
							XSSFRow desRow = desSheet.createRow(j);
							int lastRowNum = srcRow.getLastCellNum();
							// 设置行高
							desRow.setHeight(srcRow.getHeight());
							for (int k = srcRow.getFirstCellNum(); k < lastRowNum; k++) {
								// 设置列宽
								desSheet.setColumnWidth(k, srcSheet.getColumnWidth(k));
								// 获取模板列数据
								XSSFCell srcCell = srcRow.getCell(k);
								// 创建新列
								XSSFCell desCell = desRow.createCell(k);
								// 拷贝列内容
								copyCell(srcCell, desCell, desWworkBook);
							}
						}
					}
					// 设置整个sheet的跨列
					int num = srcSheet.getNumMergedRegions();
					for (int n = 0; n < num; n++) {
						desSheet.addMergedRegion(srcSheet.getMergedRegion(n));
					}
					desWworkBook.setSheetName(i, bookDomain.getSheets().get(i).getSheetName());
				}
			}
			is.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {

			e.printStackTrace();
		}
		return desWworkBook;
	}
}

package com.suixingpay.domain;

import java.util.List;

/**
 * 
 * 
 * @author Fu Wei
 * @date 2012-12-17 下午3:25:48
 * @Package com.suixingpay.domain
 * @version V1.0
 * @Description TODO
 */
public class BookDomain {

	private String bookName;
	private String path;
	// sheet 集合
	private List<SheetDomain> sheets ;

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public String getPath() {
		return path;
	}

	public void setPath(String path) {
		this.path = path;
	}

	public List<SheetDomain> getSheets() {
		return sheets;
	}

	public void setSheets(List<SheetDomain> sheets) {
		this.sheets = sheets;
	}

	private BookDomain(String bookName, String path, List<SheetDomain> sheets) {
		super();
		this.bookName = bookName;
		this.path = path;
		this.sheets = sheets;
	}

	public BookDomain() {
		super();
	}
	/**
	 * 获取sheet页数
	 * @return
	 */
	public int getSheetNum(){
		return sheets == null ? 0 : sheets.size();
	}
}

package com.suixingpay.domain;

import java.util.List;

/**
 * sheet 实体
 * @author Fu Wei
 * @date 2012-12-17 下午3:20:24
 * @Package com.suixingpay.domain
 * @version  V1.0
 * @Description TODO
 */
public class SheetDomain {
	
	private String sheetName = "";
	
	private List<TableDomain> tables;

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public List<TableDomain> getTables() {
		return tables;
	}

	public void setTables(List<TableDomain> tables) {
		this.tables = tables;
	}

	private SheetDomain(String sheetName, List<TableDomain> tables) {
		super();
		this.sheetName = sheetName;
		this.tables = tables;
	}

	public SheetDomain() {
		super();
	}
}

package com.suixingpay.domain;

/**
 * 每个sheet页中的 表的格式
 * 
 * @author Fu Wei
 * @date 2012-12-17 下午2:37:33
 * @Package com.suixingpay.domain
 * @version V1.0
 * @Description TODO
 */
public class TableDomain {
	// 起始行数
	private int startRowNum = 0;
	// 结束下标
	private int endRowNum = 0;
	// 总结行长度
	private int totalRowLen = 0;

	public int getStartRowNum() {
		return startRowNum;
	}

	public void setStartRowNum(int startRowNum) {
		this.startRowNum = startRowNum;
	}

	public int getEndRowNum() {
		return endRowNum;
	}

	public void setEndRowNum(int endRowNum) {
		this.endRowNum = endRowNum;
	}

	public int getTotalRowLen() {
		return totalRowLen;
	}

	public void setTotalRowLen(int totalRowLen) {
		this.totalRowLen = totalRowLen;
	}

	private TableDomain(int startRowNum, int endRowNum, int totalRowLen) {
		super();
		this.startRowNum = startRowNum;
		this.endRowNum = endRowNum;
		this.totalRowLen = totalRowLen;
	}

	public TableDomain() {
		super();
	}
}