poi实现操作生成word表格和操作word中的图表数据

最近因为有根据数据操作文档的需求，所以使用poi来实现了功能
其实有好几种方式来实现这一系列对office操作的需求，可供选择的有easypoi，poi-tl （poi-tl）
对于poi，有一个博主已经开发的很完善并将代码提到了开源平台上poi操作

写入word表格

1.1首先要将写入的数据整理好，将其封装在一个类中：

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


public class GetElsxInfoAction{
    private Date exportStartTime;

    private Date exportEndTime;

    private String dredgeTime = "2021-07-19 00:00:00";

    private ExportInfoToWord exportInfoToWord;
    
    private String myDeptId = "";

    public void execute() throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String endTime = sdf.format(exportEndTime);
        String startTime = sdf.format(exportStartTime);

        String[] staticsticName = {"总人数", "今日人数", "总登录人数", "今日登录人数",
                "其他登录人数"};
        String[] deptUserNumsName = null;
        String[] postsNumsName = null;

        List<String> list = new ArrayList<>();
        List<String> deptUserNumsList = new ArrayList<>();
        List<String> postsNumsList = new ArrayList<>();

        List<GatewayBucketsModel> gatewayBuckets = new ArrayList<>();

        //总开通人数
        BigInteger allRegistCount = (BigInteger) getAllRegistCount().get(0);

        //今日开通人数
        BigInteger todayRegistCount = (BigInteger) getTodayRegistCount(startTime, endTime).get(0);

        list.add(String.valueOf(allRegistCount));
        list.add(String.valueOf(todayRegistCount));

        //总登录人数
        BigInteger currentDayVisitedCount = (BigInteger) getUsersTotal(dredgeTime).get(0);
        list.add(String.valueOf(currentDayVisitedCount));

        //今日总登录人数
        BigInteger allDayVisitedCount = (BigInteger) getUsersByFuncId(startTime, endTime).get(0);
        list.add(String.valueOf(allDayVisitedCount));

        //获取今日其他人数
        List todayOtherDeptNums = getTodayOtherDeptNums(startTime, endTime, myDeptId);
        if (ListUtil.isLstEmpty(todayOtherDeptNums)) {
            list.add("0");
        } else {
            list.add(todayOtherDeptNums.size() + "");
        }

        //今日登录分布情况
        List deptList = getDeptUserNumsList(startTime, endTime);

        if (!ListUtil.isLstEmpty(deptList)) {
            deptUserNumsName = new String[deptList.size()];
            for (int i = 0; i < deptList.size(); i++) {
                Object[] result = (Object[]) deptList.get(i);
                String deptName = (String) result[1];
                String userNum = String.valueOf(result[2]);
                deptUserNumsName[i] = deptName;
                deptUserNumsList.add(userNum);
            }
        }

        ExportWordMode exportWordMode = new ExportWordMode();
        exportWordMode.setStaticsticName(staticsticName);
        exportWordMode.setList(list);
        exportWordMode.setSmsgcenterFailNum(null);
        exportWordMode.setGatewayBuckets(gatewayBuckets);
        exportWordMode.setDeptUserNumsName(deptUserNumsName);
        exportWordMode.setDeptUserNumsList(deptUserNumsList);
        exportWordMode.setPostsNumsName(postsNumsName);
        exportWordMode.setPostsNumsList(postsNumsList);

        exportInfoToWord = new ExportInfoToWord();
        exportInfoToWord.exportWord(exportWordMode, exportStartTime, exportEndTime);
    }

    /**
     * 计算减去x天的时间
     *
     * @param date 时间
     * @param days 相减的天数
     * @return
     */
    public static Date subDayTime(Date date, Integer days) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.SECOND, -days * 24 * 3600);
        return calendar.getTime();
    }

    private void addPeakInfoToList(List<String> list, List peak, int i) {
        StringBuffer sb = new StringBuffer();
        Object[] o = (Object[]) peak.get(i);
        String time = ((String) o[0]).replace("\"", "");
        sb.append(String.format("%02d", Integer.parseInt(time))).append("时").append("--").append(String.format("%02d", Integer.parseInt(time) + 1)).append("时").append(": ").append(String.valueOf((BigInteger) o[1]).replaceAll("\"", "")).append("人");
        list.add(sb.toString());
    }
}

1.2 其次将数据写入word，写成表格

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

public class ExportInfoToWord {

    /**
     * 这里定义的变量是对应的表格中的键和值
     */
    private String[] staticsticName;
    private String[] staticsName2;
    private String[] postNames;
    private List<String> list;
    private List<String> list2;
    private List<String> postsNums;

    public void exportWord(ExportWordMode exportWordMode, Date exportStartTime, Date exportEndTime) {

        staticsticName = exportWordMode.getStaticsticName();
        list = exportWordMode.getList();

        staticsName2 = exportWordMode.getDeptUserNumsName();
        list2 = exportWordMode.getDeptUserNumsList();

        postNames = exportWordMode.getPostsNumsName();
        postsNums = exportWordMode.getPostsNumsList();
        // 文档生成方法
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日HH时mm分");
        String fileName;
        fileName = sdf.format(exportStartTime) + "--" + sdf.format(exportEndTime);
        XWPFDocument doc = new XWPFDocument();


        XWPFHelper.addCustomHeadingStyle(doc, "heading 1", 0);
        XWPFHelper.addCustomHeadingStyle(doc, "heading 2", 1);
        XWPFHelper.addCustomHeadingStyle(doc, "heading 3", 2);


        XWPFParagraph p1 = doc.createParagraph(); // 创建段落
        p1.setAlignment(ParagraphAlignment.CENTER);//样式居中
        XWPFRun r1 = p1.createRun(); // 创建段落文本
        r1.setText("文件名"); // 设置文本
        r1.setBold(true);
        r1.setFontSize(20);
        r1.addBreak();
        XWPFParagraph second = doc.createParagraph(); // 创建段落
        second.setAlignment(ParagraphAlignment.CENTER);//样式居中
        XWPFRun secondr2 = second.createRun(); // 创建段落文本

        secondr2.setText(sdf.format(exportStartTime) + "--" + sdf.format(exportEndTime)); // 设置文本

        FileOutputStream out = null; // 创建输出流
        try {
            // 向word文档中添加内容
            XWPFHelper.createParagraph(doc, "1、总体情况", "heading 1", true);
            XWPFTable generalConditionTable = XWPFHelperTable.createTable(doc, list.size()+1, 3, "generalConditionTable");
            XWPFHelperTable.setTableCellStyle(generalConditionTable);
            XWPFHelperTable.setTableWidthAndHAlign(generalConditionTable, "9070", STJc.CENTER);
            // 获取到刚刚插入的行
            XWPFTableRow row1 = generalConditionTable.getRow(0);
            XWPFHelperTable.setFirstTableRowColor(row1, 3, "E7E6E6");
            // 设置第一行单元格内容
            XWPFParagraph serial = row1.getCell(0).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(serial, "序号", ParagraphAlignment.CENTER);
            XWPFParagraph statisContent = row1.getCell(1).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(statisContent, "统计内容", ParagraphAlignment.CENTER);
            XWPFParagraph data = row1.getCell(2).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(data, "数据", ParagraphAlignment.CENTER);

            // 插入内容
            XWPFHelperTable.setTableContent(generalConditionTable, staticsticName, list);

            doc.setTable(0, generalConditionTable);

            //插入第二个表格
            XWPFHelper.createParagraph(doc, "2、人员分布情况", "heading 1", true);
            XWPFTable secondTable = XWPFHelperTable.createTable(doc, list2.size() + 1, 3, "secondTable");
            XWPFHelperTable.setTableCellStyle(secondTable);
            XWPFHelperTable.setTableWidthAndHAlign(secondTable, "9070", STJc.CENTER);
            //获取到刚刚插入的行
            XWPFTableRow row2 = secondTable.getRow(0);
            XWPFHelperTable.setFirstTableRowColor(row2, 3, "E7E6E6");
            //设置第一行单元格内容
            XWPFParagraph serial2 = row2.getCell(0).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(serial2, "序号", ParagraphAlignment.CENTER);
            XWPFParagraph staticsContent2 = row2.getCell(1).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(staticsContent2, "部门名称", ParagraphAlignment.CENTER);
            XWPFParagraph data2 = row2.getCell(2).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(data2, "登录人数", ParagraphAlignment.CENTER);
            //插入内容
            XWPFHelperTable.setTableContent(secondTable,staticsName2,list2);
            doc.setTable(1, secondTable);

            //插入第三个表格
            XWPFHelper.createParagraph(doc, "3、其他情况", "heading 1", true);
            XWPFTable thirdTable = XWPFHelperTable.createTable(doc, postsNums.size() + 1, 3, "thirdTable");
            XWPFHelperTable.setTableCellStyle(thirdTable);
            XWPFHelperTable.setTableWidthAndHAlign(thirdTable, "9070", STJc.CENTER);
            //获取到刚刚插入的行
            XWPFTableRow row3 = thirdTable.getRow(0);
            XWPFHelperTable.setFirstTableRowColor(row3, 3, "E7E6E6");
            //设置第一行单元格内容
            XWPFParagraph serial3 = row3.getCell(0).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(serial3, "序号", ParagraphAlignment.CENTER);
            XWPFParagraph staticsContent3 = row3.getCell(1).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(staticsContent3, "统计内容", ParagraphAlignment.CENTER);
            XWPFParagraph data3 = row3.getCell(2).getParagraphs().get(0);
            XWPFHelperTable.setTableCellParagraphContent(data3, "数据", ParagraphAlignment.CENTER);
            //插入内容
            XWPFHelperTable.setTableContent(thirdTable,postNames,postsNums);
            doc.setTable(2, thirdTable);


            setStreamFile(fileName, doc);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {

                    e.printStackTrace();
                }
            }
        }
    }

    private static void setStreamFile(String fileName, XWPFDocument doc) {
        fileName = "文件名("+fileName+").docx";
        FileOutputStream fout = null;
        String path = getClassLoader().getResource("").getPath();
        try {
            fout = new FileOutputStream(new File(path, fileName));
            doc.write(fout);
            fout.flush();
            InputStream in = new FileInputStream(new File(path, fileName));
            File newFile = new File(fileName);
            FileUtils.copyInputStreamToFile(in, newFile);
            SlwContext.setStreamData(new StreamData(newFile, StreamData.MOD_DOWNLOAD, fileName));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

2 在word表格中动态修改图表（柱状图）数据

2.1 同样先整理数据

	public static Map<String, String> getWeekDate() {
        Map<String, String> map = new HashMap();
        Calendar cal = Calendar.getInstance();
        // 设置一个星期的第一天，按中国的习惯一个星期的第一天是星期一
        cal.setFirstDayOfWeek(Calendar.MONDAY);
        // 获得当前日期是一个星期的第几天
        int dayWeek = cal.get(Calendar.DAY_OF_WEEK);
        if (dayWeek == 1) {
            dayWeek = 8;
        }

        cal.add(Calendar.DATE, cal.getFirstDayOfWeek() - dayWeek);// 根据日历的规则，给当前日期减去星期几与一个星期第一天的差值
        Date mondayDate = cal.getTime();
        String weekBegin = sdf.format(mondayDate);

        cal.add(Calendar.DATE, 4 + cal.getFirstDayOfWeek());
        Date sundayDate = cal.getTime();
        String weekEnd = sdf.format(sundayDate);

        map.put("mondayDate", weekBegin + " 00:00:00");//2021-08-05
        map.put("sundayDate", weekEnd + " 23:59:59");

        map.put("mondayTime", sdf2.format(mondayDate));//2021年08月05日
        map.put("sundayTime", sdf2.format(sundayDate));
        return map;
    }
    
	private static void export() {
        /*
         * 创建一个Map对象，将Word文档需要的数据都保存到该Map对象中
         */
        Map<String, Object> dataMap = new HashMap<>();


        Map<String, String> weekDate = getWeekDate();
        String mondayDate = weekDate.get("mondayDate");//周一
        name += mondayDate.substring(0, 10).replace("-", "");//文档名称+日期
        String sundayDate = weekDate.get("sundayDate");//周日
        String mondayTime = weekDate.get("mondayTime");//年月日格式
        String sundayTime = weekDate.get("sundayTime");//年月日格式
        String time = mondayTime + "—" + sundayTime;
        dataMap.put("exportTime", time);
		//中间数据省略，根据自己最后要读取的excel来设置
        System.out.println("导出成功！");
        return;
    }

2.2 将数据写入excel

public class PoiToExcel {
    public static void toExcel(List<List<String>> statistics, String path) {
        XSSFWorkbook wb = null;//创建XSSFWorkbook对象，针对xlsx使用XSSFWorkbook
        try {
            wb = new XSSFWorkbook(new FileInputStream(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet1 = null;
        if (wb != null) {
            sheet1 = wb.getSheet("Sheet1");
        }else {
            System.out.println("未找到sheet");
            System.exit(0);
        }

        //写一个循环根据数据的数量来创建表格
        for (int i = 0; i < statistics.size(); i++) {
            if (i == 0) {
                continue;
            }
            //为了使excel保留之前格式，这里选择了替换数据的方式
            XSSFRow row = null;
            //sheet.getLastRowNum()
            if (i < sheet1.getPhysicalNumberOfRows()) {
                row = sheet1.getRow(i);

                for (int j = 0; j < statistics.get(i).size(); j++) {
                    if (j != 0) {
                        row.getCell(j).setCellValue(Integer.parseInt(statistics.get(i).get(j)));
                    } else {
                        row.getCell(j).setCellValue(String.valueOf(statistics.get(i).get(j)));
                    }
                }
            }else {
                row = sheet1.createRow(i);
                for (int j = 0; j < statistics.get(i).size(); j++) {
                    if(i != 0 && j!= 0){
                        row.createCell(j).setCellValue(Integer.parseInt(statistics.get(i).get(j)));
                    } else {
                        row.createCell(j).setCellValue(String.valueOf(statistics.get(i).get(j)));
                    }
                }
            }


        }

        //输出Excel文件

        OutputStream output = null;
        try {
            output = new FileOutputStream(path);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        try {
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        List<List<String>> statistics = new ArrayList<>();
        statistics.add(Arrays.asList("XXX", "8"));
        statistics.add(Arrays.asList("XXXXX", "100"));


        toExcel(statistics, "C:\\poi-demo\\src\\main\\output\\result.xlxs");
    }
}