使用 poi 生产 excel

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

项目中要生成这样格式excel

使用poi请先下载对应的jar包到项目，请自行搜索

首先把表单抽象成java bean

我的bean 如下：

package com.poi.entity;

import java.util.ArrayList;
import java.util.List;

public class PlatformDetail {

	private List<ProductDetailTab> productDetailTabs;
	private Borrower borrower;
	private Investor investor;
	
	public PlatformDetail(){
		productDetailTabs=new ArrayList<ProductDetailTab>();
		borrower=new Borrower();
		investor=new Investor();
	}
	
	public List<ProductDetailTab> getProductDetailTabs() {
		return productDetailTabs;
	}
	public void setProductDetailTabs(List<ProductDetailTab> productDetailTabs) {
		this.productDetailTabs = productDetailTabs;
	}
	public Borrower getBorrower() {
		return borrower;
	}
	public void setBorrower(Borrower borrower) {
		this.borrower = borrower;
	}
	public Investor getInvestor() {
		return investor;
	}
	public void setInvestor(Investor investor) {
		this.investor = investor;
	}
	
	
}

package com.poi.entity;

import java.util.ArrayList;
import java.util.List;

public class ProductDetailTab {

	List<String> heads;
	private Double investSum;
	private Integer investCycle;
	private Double annualYield;
	private Double interestSum;
	List<ProductDetailItem> datas;
	
	public ProductDetailTab() {
		heads=new ArrayList<String>();
		heads.add("总额");
		heads.add("投资周期");
		heads.add("年化收益率（%）");
		heads.add("投资人姓名");
		heads.add("投资金额");
		heads.add("投资人购买日");
		heads.add("放款日期");
		heads.add("回款日期");
		heads.add("计算利息天数");
		heads.add("到期利息");
		heads.add("备注");	
		datas=new ArrayList<ProductDetailItem>();
	}


	public List<String> getHeads() {
		return heads;
	}


	public void setHeads(List<String> heads) {
		this.heads = heads;
	}


	public Double getInvestSum() {
		return investSum;
	}


	public void setInvestSum(Double investSum) {
		this.investSum = investSum;
	}


	public Integer getInvestCycle() {
		return investCycle;
	}


	public void setInvestCycle(Integer investCycle) {
		this.investCycle = investCycle;
	}


	public Double getAnnualYield() {
		return annualYield;
	}


	public void setAnnualYield(Double annualYield) {
		this.annualYield = annualYield;
	}


	public Double getInterestSum() {
		return interestSum;
	}


	public void setInterestSum(Double interestSum) {
		this.interestSum = interestSum;
	}


	public List<ProductDetailItem> getDatas() {
		return datas;
	}


	public void setDatas(List<ProductDetailItem> datas) {
		this.datas = datas;
	}
	
	
	
	
	
	
}

package com.poi.entity;

import java.util.Date;

public class ProductDetailItem {

	
	
	private String investorName;
	private Double investment;
	private Date buyDate;
	private Date loansDate;
	private Date repayDate;
	private int interestDays;
	private Double interest;
	private String remark;
	
	public String getInvestorName() {
		return investorName;
	}
	public void setInvestorName(String investorName) {
		this.investorName = investorName;
	}
	
	public Double getInvestment() {
		return investment;
	}
	public void setInvestment(Double investment) {
		this.investment = investment;
	}
	public Date getBuyDate() {
		return buyDate;
	}
	public void setBuyDate(Date buyDate) {
		this.buyDate = buyDate;
	}
	public Date getLoansDate() {
		return loansDate;
	}
	public void setLoansDate(Date loansDate) {
		this.loansDate = loansDate;
	}
	public Date getRepayDate() {
		return repayDate;
	}
	public void setRepayDate(Date repayDate) {
		this.repayDate = repayDate;
	}
	public int getInterestDays() {
		return interestDays;
	}
	public void setInterestDays(int interestDays) {
		this.interestDays = interestDays;
	}
	public Double getInterest() {
		return interest;
	}
	public void setInterest(Double interest) {
		this.interest = interest;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	
	
	
}

生成器如下：

package com.poi.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import com.poi.entity.*;

public class PlatformDetailXlsCreator {

	private HSSFWorkbook wb;
	private PlatformDetail pfd;
	private CellStyle alignCenter;
	private CellStyle alignRight;	
	private CellStyle dateStyle;
	private CellStyle topBorder;	
	private CellStyle totalTopBorder;
	private CellStyle money;	
	
	public PlatformDetailXlsCreator(PlatformDetail pfd){
		 
		this.wb= new HSSFWorkbook();
		this.pfd=pfd;
		
		alignCenter=wb.createCellStyle();//居中对齐的样式
		alignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中对齐的样式
		alignRight=wb.createCellStyle();
		alignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		dateStyle=wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();  
		dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd"));
		dateStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		topBorder=wb.createCellStyle();
		topBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
		totalTopBorder=wb.createCellStyle();
		totalTopBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
		totalTopBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		totalTopBorder.setDataFormat(format.getFormat("#,##0.00"));
		money=wb.createCellStyle();
		money.setDataFormat(format.getFormat("#,##0.00"));
		money.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		init();
		
	}
	
	public void init(){
		
		
		
		HSSFSheet sheet1 = wb.createSheet("产品明细");	    
		createSheet1(sheet1);
		
	}
	
	public void write(String path) throws IOException{
		File outPutFile=new File(path);
		if(!outPutFile.exists()){
			outPutFile.createNewFile();
		}
		
		FileOutputStream os = new FileOutputStream(outPutFile);  
	    wb.write(os);  
	    os.close(); 
	}
	//sheet1
	private void createSheet1(HSSFSheet sheet){
		
		sheet.setDefaultColumnWidth(12);
		int totalrow=0;
		List<ProductDetailTab> tabs=pfd.getProductDetailTabs();
		List<String> heads= tabs.get(0).getHeads();		
		
		//写表头
		sheet.createFreezePane(0, 1);
	    HSSFRow row0 = sheet.createRow(0); 
	    for(int i=0;i<heads.size();i++){
	    	 Cell cell= row0.createCell(i);
	    	 cell.setCellValue(heads.get(i));
	    	cell.setCellStyle(alignCenter);
	    	 
	    }
	    totalrow++;
	    
	    //写数据
		for(int i=0;i<tabs.size();i++){
			
			ProductDetailTab tab=tabs.get(i);			
			
		 	for(int i1=0;i1<tab.getDatas().size();i1++)
		    {		    	
		    	ProductDetailItem item=tab.getDatas().get(i1);
		    	HSSFRow row=sheet.createRow(totalrow);
		    	totalrow++;
		    	if(i1==0){
		    		//总额
				    Cell cell_0 = row.createCell(0);
				    cell_0.setCellValue(tab.getInvestSum());
				    cell_0.setCellStyle(alignCenter);
				    //投资周期（天）
				    Cell cell_1=row.createCell(1);
				    cell_1.setCellValue(tab.getInvestCycle()+"天");
				    cell_1.setCellStyle(alignCenter);
				    //年化收益率（%）
				    Cell cell_2=row.createCell(2);
				    cell_2.setCellValue(tab.getAnnualYield()+"%");
					cell_2.setCellStyle(alignCenter);
		    	}
		    	
		    	Cell cell_3=row.createCell(3);
		    	cell_3.setCellValue(item.getInvestorName());
		    	cell_3.setCellStyle(alignCenter);
		    	
		    	Cell cell_4=row.createCell(4);
		    	cell_4.setCellValue(item.getInvestment());
		    	cell_4.setCellStyle(money);
		    	
		    	Cell cell_5=row.createCell(5);
		    	cell_5.setCellValue(item.getBuyDate());
		    	cell_5.setCellStyle(dateStyle);
		    	
		    	Cell cell_6=row.createCell(6);
		    	cell_6.setCellValue(item.getLoansDate());
		    	cell_6.setCellStyle(dateStyle);
		    	
		    	Cell cell_7=row.createCell(7);
		    	cell_7.setCellValue(item.getRepayDate());
		    	cell_7.setCellStyle(dateStyle);
		    	
		    	Cell cell_8=row.createCell(8);
		    	cell_8.setCellValue(item.getInterestDays());
		    	cell_8.setCellStyle(alignRight);
		    	
		    	Cell cell_9=row.createCell(9);
		    	cell_9.setCellValue(item.getInterest());
		    	cell_9.setCellStyle(money);
		    	
		    	Cell cell_10=row.createCell(10);
		    	cell_10.setCellValue(item.getRemark());	
		    	
		    }
		 	HSSFRow row=sheet.createRow(totalrow);
		 	totalrow++;	 	
		 	
		 	Cell total4 =row.createCell(4);
		 	total4.setCellValue(tab.getInterestSum());
		 	total4.setCellStyle(totalTopBorder);
		 	
			Cell total5=row.createCell(9);
			total5.setCellValue(tab.getInvestSum());
			total5.setCellStyle(totalTopBorder);			
			
			row.setRowStyle(topBorder);
			
			row=sheet.createRow(totalrow);
			totalrow++;
			
//			for(int i1=0;i1<heads.size();i1++){
//				Cell cell=row.createCell(i1);
//				cell.setCellStyle(bottomBorder);
//				
//			}
			
		}    
	    
	   
	}
}

测试类：

package com.poi.test;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import com.poi.entity.PlatformDetail;
import com.poi.entity.ProductDetailItem;
import com.poi.entity.ProductDetailTab;
import com.poi.utils.PlatformDetailXlsCreator;


public class Test {

	public static void main(String[] args) throws ParseException {
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
		PlatformDetail platformDetail=new PlatformDetail();
		List<ProductDetailTab> tabs=platformDetail.getProductDetailTabs();
		ProductDetailTab tab=new ProductDetailTab();
		tab.setInvestSum(96800.00);
		tab.setInvestCycle(7);
		tab.setAnnualYield(5.45);	
		tab.setInterestSum(1012323.11);
		
		
		ProductDetailItem item=new ProductDetailItem();
		item.setInvestorName("唐一娟");
		item.setInvestment(123200.00);
		item.setBuyDate(sdf.parse("2014/9/28"));
		item.setLoansDate(sdf.parse("2014/9/28"));
		item.setRepayDate(sdf.parse("2014/9/28"));
		item.setInterestDays(7);
		item.setInterest(12121210.10);
		item.setRemark("我是备注");
		tab.getDatas().add(item);
		tab.getDatas().add(item);
		tab.getDatas().add(item);
		tab.getDatas().add(item);
		
		tabs.add(tab);
		tabs.add(tab);
		
		PlatformDetailXlsCreator creator=new PlatformDetailXlsCreator(platformDetail);
		try {
			creator.write("D:\\platformDetail.xls");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}