采用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();
}
}