主要接口 | 主要实现类 | 对应excel中的东西 |
Workbook | HSSFWorkbook | 一个excel,或工作簿 |
Sheet | HSSFSheet | 一个excel工作页,或者说Sheet页 |
Row | HSSFRow | 一行 |
Cell | HSSFCell | 一个单元格 |
CellStyle | HSSFCellStyle | 单元格样式 |
Font | HSSFFont | 字体 |
Color | HSSFColor | 颜色 |
Picture | HSSFPic ture | 图片 |
PrintSetup | HSSFPrintSetup | 打印 |
PoI生成一个Excel文件的基本步骤:
- 创建项目,导入jar包
poi-3.10-FINAL-20140208.jar
- 创建一个xls文件
Workbook excel=new HSSFWorkbook();
- 生成标题 , 并设置
//标题数据
String[] titles={"编号","姓名","报销额","年份","部门"};
//创建标题行
Row row=sheet.createRow(0);
for (int i = 0; i < titles.length; i++) {
//创建 单元格
Cell cell = row.createCell(i);
//设置单元格数据
cell.setCellValue(titles[i]);
} - 生成内容
//创建内容行
for (int i = 0; i < datas.size(); i++) {
WorkData data = datas.get(i);
Row contentRow=sheet.createRow(i+1);//行是i+1:因为标题占了一行
Cell cell = contentRow.createCell(0);
cell.setCellValue(data.getSn());//...更行单元格
} - 美化设置样式
CellStyle style=wb.createCellStyle(); //利用 文件对象创建
//对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
//背景色
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//字体
Font font=wb.createFont(); //利用 文件对象创建
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLUE.index);
style.setFont(font);
//边框
style.setBorderBottom(CellStyle.BORDER_DOUBLE);
style.setBorderTop(CellStyle.BORDER_DOUBLE);
style.setBorderLeft(CellStyle.BORDER_DOUBLE);
style.setBorderRight(CellStyle.BORDER_DOUBLE); - 写出到os流
//创建输出流
ByteArrayOutputStream os=new ByteArrayOutputStream();
excel.write(os);
完成以上步骤,将流写入到文件就以了
daopackage cn.xm.testpoi.entity;
import java.util.Date;
public class WorkData {
private String sn;
private String name;
private Double money;
private Date date;
private String dept;
public WorkData(){}
public WorkData(String sn, String name, Double money, Date date, String dept) {
super();
this.sn = sn;
this.name = name;
this.money = money;
this.date = date;
this.dept = dept;
}
... 省略getter setter
}
service (重点)package cn.xm.testpoi.dao;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;
import cn.xm.testpoi.entity.WorkData;
public class WorkDataDao {
/**
* 生成 一些工作数据
*
* @return
*/
public List<WorkData> list() {
Random random = new Random();// 员工名数组
String[] names = { "梦琪", "忆柳", "之桃", "慕青", "问兰", "尔岚", "元香", "初夏",
"沛菡", "傲珊", "曼文", "乐菱", "痴珊", "恨玉", "惜文", "香寒", "新柔", "语蓉",
"海安", "夜蓉", "涵柏", "水桃", "醉蓝", "春儿", "语琴", "从彤", "傲晴", "语兰",
"又菱", "碧彤", "元霜", "怜梦", "紫寒", "妙彤", "曼易", "南莲", "紫翠", "雨寒",
"易烟", "如萱", "若南", "寻真", "晓亦", "向珊", "慕灵", "以蕊", "寻雁", "映易",
"雪柳", "孤岚", "笑霜", "海云", "凝天", "沛珊", "寒云", "冰旋", "宛儿", "绿真",
"盼儿", "晓霜", "碧凡", "夏菡", "曼香", "若烟", "半梦", "雅绿", "冰蓝", "灵槐",
"平安", "书翠", "翠风", "香巧", "代云", "梦曼", "幼翠", "友巧", "听寒", "梦柏",
"醉易", "访旋", "亦玉", "凌萱", "访卉", "怀亦", "笑蓝", "春翠", "靖柏", "夜蕾",
"冰夏", "梦松", "书雪", "乐枫", "念薇", "靖雁", "寻春", "恨山", "从寒", "忆香",
"觅波", "静曼", "凡旋", "以亦", "念露", "芷蕾", "千兰", "新波", "代真", "新蕾",
"雁玉", "冷卉", "紫山", "千琴", "恨天", "傲芙", "盼山", "怀蝶", "冰兰", "山柏",
"翠萱", "恨松", "问旋", "从南", "白易", "问筠", "如霜", "半芹", "丹珍", "冰彤",
"亦寒", "寒雁", "怜云", "寻文", "乐丹", "翠柔", "谷山", "之瑶", "冰露", "尔珍",
"谷雪", "乐萱", "涵菡", "海莲", "傲蕾", "青槐", "冬儿", "易梦", "惜雪", "宛海",
"之柔", "夏青", "亦瑶", "妙菡", "春竹", "痴梦", "紫蓝", "晓巧", "幻柏", "元风",
"冰枫", "访蕊", "南春", "芷蕊", "凡蕾", "凡柔", "安蕾", "天荷", "含玉", "书兰",
"雅琴", "书瑶", "春雁", "从安", "夏槐", "念芹", "怀萍" };
//部门数组
String[] depts = { "信息部", "实践部", "组织部", "信管部", "信工部" };
List<WorkData> list = new ArrayList<WorkData>();
for (int i = 0; i < names.length; i++) {
list.add(new WorkData("sn" + i, names[i],
random.nextInt(10000) + 0D,
new Date(System.currentTimeMillis()
- random.nextInt(7)
* 24
* 60
* 60 * 100),
depts[random.nextInt(depts.length)]));
}
return list;
}
}
actionpackage cn.xm.testpoi.service;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import cn.xm.testpoi.dao.WorkDataDao;
import cn.xm.testpoi.entity.WorkData;
public class WorkDataService {
private WorkDataDao wdd = new WorkDataDao();
private Font contentFont;
public InputStream getStream() throws IOException {
List<WorkData> datas = wdd.list();
// 创建 一个 excel文件
Workbook excel = new HSSFWorkbook();
// 创建一个工作页
Sheet sheet = excel.createSheet("一周报销统计");
// 标题数据
String[] titles = { "编号", "姓名", "报销额", "年份", "部门" };
// 创建标题行
Row row = sheet.createRow(0);
for (int i = 0; i < titles.length; i++) {
// 创建 单元格
Cell cell = row.createCell(i);
// 设置单元格数据
cell.setCellValue(titles[i]);
// 设置单元格样式
cell.setCellStyle(getTitleStyle(excel));
}
// 创建内容行
for (int i = 0; i < datas.size(); i++) {
WorkData data = datas.get(i);
Row contentRow = sheet.createRow(i + 1);
Cell cell0 = contentRow.createCell(0);
cell0.setCellValue(data.getSn());
Cell cell1 = contentRow.createCell(1);
cell1.setCellValue(data.getName());
Cell cell2 = contentRow.createCell(2);
cell2.setCellValue(data.getMoney());
Cell cell3 = contentRow.createCell(3);
cell3.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(data
.getDate()));
Cell cell4 = contentRow.createCell(4);
cell4.setCellValue(data.getDept());
// 加样式
cell0.setCellStyle(getContentStyle(excel, i));
cell1.setCellStyle(getContentStyle(excel, i));
cell2.setCellStyle(getContentStyle(excel, i));
cell3.setCellStyle(getContentStyle(excel, i));
cell4.setCellStyle(getContentStyle(excel, i));
}
// 创建输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
excel.write(os);
// 将输出流转换成输入流
return new ByteArrayInputStream(os.toByteArray());
}
/**
* 标题样式
*
* @param wb
* @return
*/
private CellStyle getTitleStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
// 对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
// 背景色
style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 字体
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLUE.index);
style.setFont(font);
// 边框
style.setBorderBottom(CellStyle.BORDER_DOUBLE);
style.setBorderTop(CellStyle.BORDER_DOUBLE);
style.setBorderLeft(CellStyle.BORDER_DOUBLE);
style.setBorderRight(CellStyle.BORDER_DOUBLE);
return style;
}
/**
* 内容样式
*
* @param wb
* @param rowIndex
* @return
*/
private CellStyle getContentStyle(Workbook wb, int rowIndex) {
CellStyle style = wb.createCellStyle();
// 对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
// 背景色(隔行变色)
if (rowIndex % 2 == 0) {
style.setFillForegroundColor(HSSFColor.GOLD.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
} else {
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
// 字体
if (contentFont == null) { // 字体对象不能过多,过多时excel打开会出错(excel限制字体对象数量)
contentFont = wb.createFont();
}
contentFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLUE.index);
style.setFont(contentFont);
// 边框
style.setBorderBottom(CellStyle.BORDER_DOUBLE);
style.setBorderTop(CellStyle.BORDER_DOUBLE);
style.setBorderLeft(CellStyle.BORDER_DOUBLE);
style.setBorderRight(CellStyle.BORDER_DOUBLE);
return style;
}
}
struts.xmlpackage cn.xm.testpoi.action;
import java.io.IOException;
import java.io.InputStream;
import cn.xm.testpoi.service.WorkDataService;
public class WorkTableAction {
private WorkDataService wds=new WorkDataService();
private InputStream workTable;
private String fileName;
/**
* 下载方法
* @return
*/
public String workTable(){
try {
setWorkTable(wds.getStream());
fileName="workData-"+System.currentTimeMillis()+".xls";
} catch (IOException e) {
e.printStackTrace();
}
return "success";
}
public InputStream getWorkTable() {
return workTable;
}
public void setWorkTable(InputStream workTable) {
this.workTable = workTable;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="de" namespace="/" extends="struts-default" >
<action name="getFile" class="cn.xm.testpoi.action.WorkTableAction" method="workTable" >
<result name="success" type="stream" >
<param name="inputName">workTable</param>
<param name="contentDisposition">attachment;filename="${fileName}"</param>
<param name="contentType">application/vnd.ms-excel</param>
</result>
</action>
</package>
</struts>