首页 > 代码库 > 使用 poi 生产 excel
使用 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();
}
}
}
使用 poi 生产 excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。