最近的训练中,遇到需要导出excel和word,通过网上找资料还是完成了的,此处帖子个人的总结。
excel和word导出需要的包:
先说excel:
// 创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
List<xmgtExcel> xmgtExcels = this.loadXmgtExcel(proId,proChatId); //准备要导出excel文件的内容列表
if(xmgtExcels!=null&&xmgtExcels.size()>0){
for(int i=0;i<xmgtExcels.size();i++){
xmgtExcel xmgt = xmgtExcels.get(i);
// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("项目沟通表"+i);
//接下来就是往sheet表中写入数据,excel表单元格下标从0开始
//获取行
Row row = sheet.createRow(0);
//获取单元格
cell = row.createCell(0);
//向单元格中写入数据
cell.setCellValue(“data”);
//合并单元格
/*
* 设定合并单元格区域范围 CellRangeAddress(firstRow,lastRow,firstCol,lastCol )
* firstRow 0-based
* lastRow 0-based
* firstCol 0-based
* lastCol 0-based
*/
CellRangeAddress cra_1=new CellRangeAddress(0, 0, 0, 2);
sheet.addMergedRegion(cra_1);
//设置单元格样式
【参考】:http://blog.csdn.net/liyong199012/article/details/17962139
}
return wb;
}
word的导出和excel差不多:
List<xmgtExcel> ls = this.loadXmgtExcel(proId, proChatId);
if(ls!=null&&ls.size()>0){
//建一个word文档
XWPFDocument document = new XWPFDocument();
for(int i=0;i<ls.size();i++){
//取出要显示的沟通信息
xmgtExcel xmgt = ls.get(i);
XWPFParagraph p1 = document.createParagraph();
// 设置字体对齐方式
p1.setAlignment(ParagraphAlignment.CENTER);
p1.setPageBreak(true);
// 第一页要使用p1所定义的属性
XWPFRun r1 = p1.createRun();
// 设置字体是否加粗
r1.setBold(true);
r1.setFontSize(20);
// 设置使用何种字体
r1.setFontFamily("Courier");
// 设置上下两行之间的间距
r1.setTextPosition(20);
r1.setText("项目沟通记录"+(i+1));
//获取沟通参与的人数
int person_size = 0;
if(xmgt.getPerson()!=null){
person_size = xmgt.getPerson().size();
}
//1.生成表格
XWPFTable table1 = document.createTable(8+person_size+1, 3);
setTableWidth(table1, "8000");
//2.向表格填充数据
int rowNum =0;
//获取table的第一行
XWPFTableRow row = table1.getRow(rowNum++);
row.setHeight(380);
//获取第一行的第一列
XWPFTableCell cell = row.getCell(0);
//设置值
cell.setText("沟通事项主题:"+xmgt.getTheme());
.
.
.
//3.合并单元格
//参与人 跨行合并
this.mergeCellsVertically(table1,0,7,7+person_size);
//跨列合并
for(int row_num=0;row_num<7;row_num++){
this.mergeCellsHorizontal(table1, row_num, 0, 2);
}
this.mergeCellsHorizontal(table1, 7+person_size+1, 0, 2);
}
return document;
/**
* @Description: 跨列合并
*/
public void mergeCellsHorizontal(XWPFTable table, int row, int fromCell, int toCell) {
for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {
XWPFTableCell cell = table.getRow(row).getCell(cellIndex);
if ( cellIndex == fromCell ) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
}
}
}
/**
* @Description: 跨行合并
* @see http://stackoverflow.com/questions/24907541/row-span-with-xwpftable
*/
public void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) {
for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) {
XWPFTableCell cell = table.getRow(rowIndex).getCell(col);
if ( rowIndex == fromRow ) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
}
}
}