poi导出word表格的操作讲解

poi导出word表格的操作讲解

一、效果如下

二、js代码

function export_word(){ //导出word

var url = "czzsca/exportWord.do";

this.export(url);

}

function export(url){

var currentyear = $("#mainYear").val() * 1;

var key_columns = ['xh',"d_name","d_unit","d_number","d_estimate","d_lastyear","jnjh","d_remarks","d_depart","d_executeunit"];

var value_columns = ['序号','项目名称','单位','数量','总概算','至'+(currentyear-1)+'年完成',currentyear+'年计划','附注','责任部门','计划执行单位'];

window.location.href= url+"?key_columns="+key_columns+"&value_columns="+value_columns+"&title="+currentyear+"年 更新改造计划 正式计划草案";

}

三、controller代码

（controller代码比较长，主要是格式化每一行的数据，主要是往工具类里传List>参数，泛型的List是每一行，String是每一列）

//导出word

@RequestMapping("/exportWord.do")

@ResponseBody

public ReturnMsg exportWord(String[] key_columns,String[] value_columns,String title,HttpServletResponse response){

ReturnMsg rm = new ReturnMsg();

try {

response.setCharacterEncoding("UTF-8");

response.setContentType("application/msexcle");

response.setHeader("content-disposition", "attachment;filename="+new String("正式计划草案".getBytes("gb2312"),"ISO8859-1")+".doc");

List> content = new ArrayList>();

List> head = new ArrayList>();

String jnjhzh = new BigDecimal(this.map.get("jnjhzh")==null||"".equals(this.map.get("jnjhzh")+"")?"0":this.map.get("jnjhzh")+"").setScale(1, RoundingMode.UP)+"";

String trimpro = new BigDecimal(this.map.get("tzjh")==null||"".equals(this.map.get("tzjh")+"")?"0":this.map.get("tzjh")+"").setScale(1, RoundingMode.UP)+"";

String temp = new BigDecimal(trimpro).subtract(new BigDecimal(jnjhzh))+"";

if("0".equals(temp)) {

temp = "";

}

head = ExportUtil.getCzzscaList(jnjhzh, trimpro, temp);

Map dounit = new HashMap();

dounit.put("dwlx", 0);

dounit.put("user_type", 1);

List

Map zrbm = new HashMap();

zrbm.put("dwlx", 1);

zrbm.put("user_type", 1);

List

List

for (Map map: this.list) {

List row = new ArrayList();

for (int i = 0; i < key_columns.length; i++) {

System.out.print(map.get(key_columns[i])+",");

Object Otzjh = map.get("tzjh")!=null?map.get("tzjh"):0;

Object Ojnjh = map.get("jnjh")!=null?map.get("jnjh"):0;

BigDecimal tzjh = null;

BigDecimal jnjh = null;

if ("tzjh".equals(key_columns[i])||"jnjh".equals(key_columns[i])||"d_lnorde".equals(key_columns[i])) {

if(Otzjh instanceof BigDecimal) {

tzjh = (BigDecimal)Otzjh;

}else {

tzjh = new BigDecimal(Otzjh+"");

}

if(Ojnjh instanceof BigDecimal) {

jnjh = (BigDecimal)Ojnjh;

}else {

jnjh = new BigDecimal(Ojnjh+"");

}

}

if ("d_lnorde".equals(key_columns[i])) {

if(map.get("flag")!=null&&"2".equals((map.get("flag")+""))) {

row.add("");

}else {

row.add(tzjh.subtract(jnjh).setScale(1, RoundingMode.UP)+""); //不为整数则四舍五入

}

}else if ("xh".equals(key_columns[i])&&map.get("xh")!=null) {

if(map.get("xh").toString().indexOf("、")>-1) {

row.add(map.get("xh").toString().replace("、", "").trim());

}else {

row.add(map.get("xh")+""); //不为整数则四舍五入

}

}else if("tzjh".equals(key_columns[i])&&map.get("tzjh")!=null) {

if(ExportUtil.isNumeric(tzjh+"")) { //判断相减是否为整数

row.add(tzjh+"");

}else {

String stzjh = tzjh.setScale(1, RoundingMode.UP)+"";

row.add("0".equals(stzjh.charAt(stzjh.indexOf(".")+1)+"")?stzjh.replace(".0", ""):stzjh);//不为整数则四舍五入

}

}else if("d_estimate".equals(key_columns[i])&&map.get("d_estimate")!=null) {

if("0".equals(map.get("d_estimate")+"")) {

row.add("");

}else {

row.add(map.get("d_estimate")+"");

}

}else if("d_number".equals(key_columns[i])&&map.get("d_number")!=null) {

if("0".equals(map.get("d_number")+"")) {

row.add("");

}else {

row.add(map.get("d_number")+"");

}

}else if("jnjh".equals(key_columns[i])&&map.get("jnjh")!=null) {

if(ExportUtil.isNumeric(jnjh+"")) { //判断相减是否为整数

row.add(jnjh+"");

}else {

String sjnjh = jnjh.setScale(1, RoundingMode.UP)+"";

row.add("0".equals(sjnjh.charAt(sjnjh.indexOf(".")+1)+"")?sjnjh.replace(".0", ""):sjnjh); //不为整数则四舍五入

}

}else if("d_pro_type".equals(key_columns[i])&&map.get("d_pro_type")!=null) {

for (Map m : zmtype) {

if((map.get("d_pro_type")+"").equals(m.get("TYPE_ID")+"")){

row.add(m.get("NUM")+"");

break;

}

}

}else if("d_depart".equals(key_columns[i])&&map.get("d_depart")!=null) {

String d_depart = map.get("d_depart")+"";

String depart = "";

for (Map m : d_zrbm) {

if((d_depart).indexOf(",")>-1) {

for (String string : d_depart.split(",")) {

if((string).equals(m.get("ID")+"")) {

depart += ","+m.get("UNITNAME")+"";

break;

}

}

}else {

if((d_depart).equals(m.get("ID")+"")) {

depart = ","+m.get("UNITNAME")+"";

break;

}

}

}

if (StringUtils.isNotBlank(depart)) {

depart = depart.substring(1, depart.length()).replace(",", "\r ");

row.add(depart);

}else {

row.add("");

}

}else if("d_executeunit".equals(key_columns[i])&&map.get("d_executeunit")!=null) {

boolean flag;

for (Map m : d_executeunit) {

flag = false;

String executeunit = map.get("d_executeunit")+"";

if((executeunit).indexOf(",")>-1) {

for (String string : executeunit.split(",")) {

if((string).equals(m.get("ID")+"")) {

row.add((m.get("UNITNAME")+"").trim());

flag = true;

break;

}

}

if (flag) {

break;

}

}else {

if((executeunit).equals(m.get("ID")+"")) {

row.add((m.get("UNITNAME")+"").trim());

break;

}

}

}

}else {

if(map.get(key_columns[i])==null) {

row.add("");

}else {

row.add((map.get(key_columns[i])+"").trim());

}

}

}

content.add(row);

}

head.addAll(content);

int[] colWidths = new int[] { 600, 3000, 500, 500, 800, 800 ,800,3000,800,1500};

XWPFDocument document = ExportUtil.exportWord(title,value_columns, head,colWidths,1,7);

OutputStream out = response.getOutputStream();

document.write(out);

out.close();

rm.setCode("1");

rm.setMsg("导出成功");

} catch (Exception e) {

e.printStackTrace();

rm.setCode("0");

rm.setMsg("导出失败，请刷新重试。");

}

return rm;

}

四、工具类代码

/**

*

* @Date 2018年7月19日 上午11:19:04

* @Description 导出word

* @Fcunction exportWord

* @param title

* @param value_columns

* @param list

* @return XWPFDocument

*

*/

public static XWPFDocument exportWord(String title,String[] value_columns, List> list,int[] colWidths,int one,int two) {

XWPFDocument doc= new XWPFDocument();

CTDocument1 document = doc.getDocument();

CTBody body = document.getBody();

if(!body.isSetSectPr()){

body.addNewSectPr();

}

CTSectPr section = body.getSectPr();

if(!section.isSetPgSz()){

section.addNewPgSz();

}

CTPageSz pageSize = section.getPgSz();

pageSize.setW(BigInteger.valueOf(15840));

pageSize.setH(BigInteger.valueOf(12240));

pageSize.setOrient(STPageOrientation.LANDSCAPE);

//添加标题

XWPFParagraph titleParagraph = doc.createParagraph();

//设置段落居中

titleParagraph.setAlignment(ParagraphAlignment.CENTER);

XWPFRun titleParagraphRun = titleParagraph.createRun();

titleParagraphRun.setText(title);

titleParagraphRun.setColor("000000");

titleParagraphRun.setFontSize(20);

//表格

XWPFTable ComTable = doc.createTable();

//设置指定宽度

CTTbl ttbl = ComTable.getCTTbl();

CTTblGrid tblGrid = ttbl.addNewTblGrid();

for (int i : colWidths) {

CTTblGridCol gridCol = tblGrid.addNewGridCol();

gridCol.setW(new BigInteger(i+""));

}

//表头

XWPFTableRow rowHead = ComTable.getRow(0);

XWPFParagraph cellParagraph = rowHead.getCell(0).getParagraphs().get(0);

cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中

XWPFRun cellParagraphRun = cellParagraph.createRun();

cellParagraphRun.setFontSize(10); //设置表头单元格字号

cellParagraphRun.setBold(true); //设置表头单元格加粗

cellParagraphRun.setText(value_columns[0]);

for (int i = 1; i < value_columns.length; i++) {

if(value_columns[i].indexOf("增减")>-1) {

cellParagraph = rowHead.addNewTableCell().getParagraphs().get(0);

cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中

cellParagraphRun = cellParagraph.createRun();

cellParagraphRun.setFontSize(10); //设置表头单元格居中

cellParagraphRun.setBold(true);

cellParagraphRun.setText("增减(+ / -)");

}else {

cellParagraph = rowHead.addNewTableCell().getParagraphs().get(0);

cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中

cellParagraphRun = cellParagraph.createRun();

cellParagraphRun.setFontSize(10); //设置表头单元格居中

cellParagraphRun.setBold(true); //设置表头单元格加粗

cellParagraphRun.setText(value_columns[i]);

}

}

int rows = list.size();

//表格内容

for (int i = 0; i < rows; i++) {

XWPFTableRow rowsContent = ComTable.createRow();

for (int j = 0; j < list.get(i).size(); j++) {

XWPFParagraph cellParagraphC = rowsContent.getCell(j).getParagraphs().get(0);

if(j!=one&&j!=two) {

cellParagraphC.setAlignment(ParagraphAlignment.CENTER); //设置表格内容居中

}

XWPFRun cellParagraphRunC = cellParagraphC.createRun();

cellParagraphRunC.setFontSize(10); //设置表格内容字号

cellParagraphRunC.setText(list.get(i).get(j)+""); //单元格段落加载内容

}

}

if(rows==0) {

for (int i = 0; i < value_columns.length; i++) {

XWPFTableCell cell = ComTable.getRow(0).getCell(i);

cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中

}

}else {

//设置居中

for (int i = 0; i <= rows; i++) {

for (int j = 0; j < list.get(0).size(); j++) {

XWPFTableCell cell = ComTable.getRow(i).getCell(j);

cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中

}

}

}

return doc;

}

/**

*

* @Date 2018年7月19日 上午11:19:21

* @Description 判断是否是整数

* @Fcunction isNumeric

* @param str

* @return boolean

*

*/

public static boolean isNumeric(String str) {

if(str.indexOf(".")>0) {

return false;

}

return true;

}

/**

*

* @Date 2018年7月19日 上午11:44:48

* @Description 正式计划草案获取前两行

* @Fcunction getCzzscaList

* @param jnjhzh

* @param trimpro

* @param temp

* @return List>

*

*/

public static List> getCzzscaList(String jnjhzh,String trimpro,String temp) {

List> list = new ArrayList>();

List l1 = new ArrayList(); //固定第一行

l1.add("");

l1.add("年度总计");

l1.add("");

l1.add("");

l1.add("");

l1.add("");

l1.add(jnjhzh);

l1.add("");

l1.add("");

l1.add("");

list.add(l1);

List l2 = new ArrayList(); //固定第二行

l2.add("");

l2.add("搜索总计");

l2.add("");

l2.add("");

l2.add("");

l2.add("");

l2.add("");

// l2.add(trimpro+"\r100%");

l2.add("");

l2.add("");

l2.add("");

list.add(l2);

return list;

}

五、详解：

1.设置纸张大小

（1）创建完XWPFDocument后，默认为A4纸张，如需调整纸张大小，这里我是要用A3大小使用如下代码

XWPFDocument doc= new XWPFDocument();

CTDocument1 document = doc.getDocument();

CTBody body = document.getBody();

if(!body.isSetSectPr()){

body.addNewSectPr();

}

CTSectPr section = body.getSectPr();

if(!section.isSetPgSz()){

section.addNewPgSz();

}

CTPageSz pageSize = section.getPgSz();

pageSize.setW(BigInteger.valueOf(15840));

pageSize.setH(BigInteger.valueOf(12240));

pageSize.setOrient(STPageOrientation.LANDSCAPE);

（2）默认A4纸张大小的话只用XWPFDocument

XWPFDocument doc= new XWPFDocument();

（3）使用CTPageSz类需要导入ooxml-schemas的jar包（所有需要的jar包maven依赖在底部）

2.单元格列宽度

（1）每一列宽度相等自适应，不需单独设置每一列宽度的话使用如下代码

//表格

XWPFTable ComTable = doc.createTable();

//表格自适应宽度

CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();

comTableWidth.setType(STTblWidth.DXA);

comTableWidth.setW(BigInteger.valueOf(9072));

（2）单独设置每列宽度

//表格

XWPFTable ComTable = doc.createTable();

//设置指定宽度

CTTbl ttbl = ComTable.getCTTbl();

CTTblGrid tblGrid = ttbl.addNewTblGrid();

int[] colWidths = new int[] { 600, 3000, 500, 500, 800, 800 ,800,3000,800,1500};

for (int i : colWidths) {

CTTblGridCol gridCol = tblGrid.addNewGridCol();

gridCol.setW(new BigInteger(i+""));

}

（3）使用CTTblGrid类需要导入poi-ooxml-schemas的jar包（所有需要的jar包maven依赖在底部）

3.单元格内字体样式

（1）不需要设置单元格字体

for (int i = 0; i < rows; i++) {

XWPFTableRow rowsContent = ComTable.createRow();

for (int j = 0; j < list.get(i).size(); j++) {

rowsContent.getCell(j).setText(list.get(i).get(j)+""); //用单元格加载内容

}

}

（2）设置单元格内字体字号、是否加粗

for (int i = 0; i < rows; i++) {

XWPFTableRow rowsContent = ComTable.createRow();

for (int j = 0; j < list.get(i).size(); j++) {

XWPFParagraph cellParagraphC = rowsContent.getCell(j).getParagraphs().get(0);

if(j!=1&&j!=7) {

cellParagraphC.setAlignment(ParagraphAlignment.CENTER); //设置表格内容居中

}

XWPFRun cellParagraphRunC = cellParagraphC.createRun();

cellParagraphRunC.setFontSize(10); //设置表格内容字号

cellParagraphRun.setBold(true); //设置表头单元格加粗

cellParagraphRunC.setText(list.get(i).get(j)+""); //单元格段落加载内容

}

}

4.单元格居中（水平居中、垂直居中）

（1）使用单元格加载内容

for (int i = 0; i < value_columns.length; i++) {

XWPFTableCell cell = ComTable.getRow(0).getCell(i);

cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中

CTTc cttc = cell.getCTTc();

CTP ctp = cttc.getPList().get(0);

CTPPr ctppr = ctp.getPPr();

if (ctppr == null) {

ctppr = ctp.addNewPPr();

}

CTJc ctjc = ctppr.getJc();

if (ctjc == null) {

ctjc = ctppr.addNewJc();

}

ctjc.setVal(STJc.CENTER); //水平居中

}

（2）使用单元格的段落加载内容，只用调用垂直居中的方法

for (int i = 0; i < value_columns.length; i++) {

XWPFTableCell cell = ComTable.getRow(0).getCell(i);

cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中

}

水平居中在3-(2)里调用了段落的方法居中过了

cellParagraphC.setAlignment(ParagraphAlignment.CENTER);

5.单元格合并

（1）跨列合并单元格

public static 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 ) {

cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);

} else {

cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);

}

}

}

（2）跨行合并单元格

public static 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 ) {

cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);

} else {

cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);

}

}

}

6.单元格内换行，单元格内使用\r换行没用，可以用addBreak方法

for (int i = 0; i < list_vPostdutyNorm_parm.size(); i++) {

row = ComTable.createRow();

index++;

for (int j = 0; j < list_vPostdutyNorm_parm.get(i).size(); j++) {

if (j == 3 || j == 4) {

cellParagraph.setAlignment(ParagraphAlignment.LEFT); //设置表头单元格居中

} else {

cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中

}

String content = list_vPostdutyNorm_parm.get(i).get(j);

cellParagraph = row.getCell(j).getParagraphs().get(0);

cellParagraphRun = cellParagraph.createRun();

cellParagraphRun.setFontSize(10); //设置表头单元格字号

cellParagraphRun.setBold(false); //设置表头单元格加粗

if (content.indexOf("\r") > -1) {

String[] text = content.split("\r");

for (int k = 0; k < text.length; k++) {

if (k == 0) {

cellParagraphRun.setText(text[k]);

}else {

cellParagraphRun.addBreak();

cellParagraphRun.setText(text[k]);

}

}

}else {

cellParagraphRun.setText(content);

}

}

}

六、使用到的Maven依赖

org.apache.xmlbeans

xmlbeans

2.6.0

org.apache.poi

poi-ooxml

3.9

org.apache.poi

ooxml-schemas

1.1

org.apache.poi

poi-ooxml-schemas

3.16

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。