Java中用Apache POI生成excel和word文档
概述：
近期在做项目的过程中遇到了excel的数据导出和word的图文表报告的导出功能。最后决定用Apache POI来完毕该项功能。本文就项目实现过程中的一些思路与代码与大家共享。同一时候。也作为自己的一个总结。以备后用。
功能：
1、从数据库查询数据导出为excel；
2、导出word的包含，内容有文字。图片，表格等。
效果：
导出excel
导出word
实现代码：
1、导出excel
package beans.excel;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class CreateSimpleExcelToDisk
{
/**
* @功能：手工构建一个简单格式的Excel
*/
private static List<Student> getStudent() throws Exception
{
List<Student> list = new ArrayList<Student>();
SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
Student user1 = new Student(1, "张三", 16, df.parse("1997-03-12"));
Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));
Student user3 = new Student(3, "王五", 26, df.parse("1985-11-12"));
list.add(user1);
list.add(user2);
list.add(user3);
return list;
}
@SuppressWarnings("deprecation")
public static void main(String[] args) throws Exception
{
// 第一步，创建一个webbook，相应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步。在webbook中加入一个sheet,相应Excel文件里的sheet
HSSFSheet sheet = wb.createSheet("学生表一");
// 第三步，在sheet中加入表头第0行,注意老版本号poi对Excel的行数列数有限制short
HSSFCellStyle headStyle = wb.createCellStyle();
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 11);// 字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
headStyle.setFont(f);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
headStyle.setBorderBottom((short) 1);
headStyle.setBorderRight((short) 1);
headStyle.setFillBackgroundColor((short) 20);
HSSFRow row = sheet.createRow((int) 0);
// 第四步。创建单元格。并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setBorderBottom((short) 1);
style.setBorderRight((short) 1);
String[] header = new String[]{"学号","姓名","年龄","生日"};
HSSFCell cell = null;
for(int i=0;i<header.length;i++){
cell = row.createCell((short) i);
cell.setCellValue(header[i]);
cell.setCellStyle(headStyle);
sheet.setColumnWidth(i, 5000);
}
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
List<Student> list = CreateSimpleExcelToDisk.getStudent();
for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 1);
Student stu = (Student) list.get(i);
// 第四步，创建单元格，并设置值
cell = row.createCell((short) 0);
cell.setCellValue(stu.getId());
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue(stu.getName());
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue(stu.getAge());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth()));
cell.setCellStyle(style);
}
// 第六步，将文件存到指定位置
try
{
FileOutputStream fout = new FileOutputStream("D:/students.xls");
wb.write(fout);
fout.close();
System.out.println("输出成功。");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}当中，student类的代码例如以下：
package beans.excel;
import java.util.Date;
public class Student {
private int id;
private String name;
private int age;
private Date birth;
public Student()
{
}
public Student(int id, String name, int age, Date birth)
{
this.id = id;
this.name = name;
this.age = age;
this.birth = birth;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
public Date getBirth()
{
return birth;
}
public void setBirth(Date birth)
{
this.birth = birth;
}
}2、导出word报告
package beans.excel;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import org.apache.poi.util.Units;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
public class ExportDocTest {
public static void main(String[] args) throws Exception {
XWPFDocument doc = new XWPFDocument();
XWPFParagraph para;
XWPFRun run;
//加入文本
String content =" 额尔古纳河在1689年的《中俄尼布楚条约》中成为中国和俄罗斯的界河。额尔古纳河上游称海拉尔河。源于大兴安岭西側，西流至阿该巴图山脚， 折而北行始称额尔古纳河。额尔古纳河在黑龙江省漠河县以西的内蒙古自治区额尔古纳右旗的恩和哈达附近与流经俄罗斯境内的石勒喀河汇合后始称黑龙江。沿额尔古纳河沿岸地区土地肥沃。森林茂密，水草丰美， 鱼类品种非常多，动植物资源丰富。宜农宜木。是人类理想的天堂。";
para = doc.createParagraph();
para.setAlignment(ParagraphAlignment.LEFT);//设置左对齐
run = para.createRun();
run.setFontFamily("仿宋");
run.setFontSize(13);
run.setText(content);
doc.createParagraph();
//加入图片
String[] imgs = {"D:\\bar.png","D:\\pie.png"};
for(int i=0;i<imgs.length;i++){
para = doc.createParagraph();
para.setAlignment(ParagraphAlignment.CENTER);//设置左对齐
run = para.createRun();
InputStream input = new FileInputStream(imgs[i]);
run.addPicture(input, XWPFDocument.PICTURE_TYPE_JPEG,
imgs[i], Units.toEMU(350), Units.toEMU(170));
para = doc.createParagraph();
para.setAlignment(ParagraphAlignment.CENTER);//设置左对齐
run = para.createRun();
run.setFontFamily("仿宋");
run.setFontSize(11);
run.setText(imgs[i]);
}
doc.createParagraph();
//加入表格
XWPFTable table = doc.createTable(2,3);
table.setCellMargins(3, 5, 3, 5);
// table.addNewCol();//加入新列
// table.createRow();//加入新行
String[] title = new String[]{"境内河流","境外河流","合计"};
String[] value = new String[]{"1","2","3"};
XWPFTableRow row;
XWPFTableCell cell;
CTTcPr cellPr;
for(int j=0;j<2;j++){
row = table.getRow(j);
row.setHeight(400);
for(int i=0;i<title.length;i++){
cell = row.getCell(i);
cellPr = cell.getCTTc().addNewTcPr();
cellPr.addNewTcW().setW(BigInteger.valueOf(3000));
para = cell.getParagraphs().get(0);
para.setAlignment(ParagraphAlignment.CENTER);
run = para.createRun();
run.setFontFamily("仿宋");
run.setFontSize(11);
if(j==0){//标题
run.setBold(true);
run.setText(title[i]);
}
else{
run.setText(value[i]);
}
}
}
String path = "D:\\test.doc";
OutputStream os = new FileOutputStream(path);
doc.write(os);
if(os!=null){
try{
os.close();
System.out.println("文件已输出！");
}
catch(IOException e){
e.printStackTrace();
}
}
}
}
3、word模板替换
word模板
替换后效果
代码：
package beans.excel;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
public class ExportWordTest {
public static void main(String[] args) throws Exception, IOException {
Map<String, Object> map=new HashMap<String, Object>();
String sum = "额尔古纳河在1689年的《中俄尼布楚条约》中成为中国和俄罗斯的界河。额尔古纳河上游称海拉尔河，源于大兴安岭西側，西流至阿该巴图山脚， 折而北行始称额尔古纳河。额尔古纳河在黑龙江省漠河县以西的内蒙古自治区额尔古纳右旗的恩和哈达附近与流经俄罗斯境内的石勒喀河汇合后始称黑龙江。
沿额尔古纳河沿岸地区土地肥沃。森林茂密，水草丰美， 鱼类品种非常多，动植物资源丰富。宜农宜木。是人类理想的天堂。
";
map.put("basin", "额尔古纳河流域");
map.put("sum", sum);
map.put("jnhl", "1");
map.put("jwhl", "1");
map.put("jnhp", "1");
map.put("jwhp", "1");
map.put("jnsk", "1");
map.put("jwsk", "1");
map.put("hj", "6");
//注意biyezheng_moban.doc文档位置,此例中为应用根文件夹
XWPFDocument doc=new ExportWordTest().replaceDoc("D:\\word_temp.docx", map);
try {
OutputStream os = new FileOutputStream("D:\\tttt.doc");
doc.write(os);
os.close();
System.out.println("输出成功！");
}
catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取word模板并替换变量
* @param srcPath
* @param map
* @return
*/
public XWPFDocument replaceDoc(String srcPath, Map<String, Object> param) {
try {
// 读取word模板
InputStream fis = new FileInputStream(srcPath);
XWPFDocument doc = new XWPFDocument(fis);
//处理段落
List<XWPFParagraph> paragraphList = doc.getParagraphs();
processParagraph(paragraphList,doc,param);
//处理表格
Iterator<XWPFTable> it = doc.getTablesIterator();
while (it.hasNext()) {
XWPFTable table = it.next();
List<XWPFTableRow> rows = table.getRows();
for (XWPFTableRow row : rows) {
List<XWPFTableCell> cells = row.getTableCells();
for (XWPFTableCell cell : cells) {
List<XWPFParagraph> paragraphListTable = cell.getParagraphs();
processParagraph(paragraphListTable, doc, param);
}
}
}
return doc;
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}
public void processParagraph(List<XWPFParagraph> paragraphList,
XWPFDocument doc,Map<String, Object> param){
if(paragraphList != null && paragraphList.size() > 0){
for(XWPFParagraph paragraph:paragraphList){
List<XWPFRun> runs = paragraph.getRuns();
for (XWPFRun run : runs) {
String text = run.getText(0);
if(text != null){
boolean isSetText = false;
for (Entry<String, Object> entry : param.entrySet()) {
String key = entry.getKey();
if(text.indexOf(key) != -1){
isSetText = true;
Object value = entry.getValue();
if (value instanceof String) {//文本替换
text = text.replace(key, value.toString());
System.out.println(text);
}
else{
text = text.replace(key, "");
}
}
}
if(isSetText){
run.setText(text,0);
}
}
}
}
}
}
}
POI相关jar包和API下载地址：http://pan.baidu.com/s/1eQ6fE8a
