poi实现操作生成word表格和操作word中的图表数据
写入word表格1.1首先要将写入的数据整理好，将其封装在一个类中：1.2 其次将数据写入word，写成表格2 在word表格中动态修改图表（柱状图）数据2.1 同样先整理数据2.2 将数据写入excel最近因为有根据数据操作文档的需求，所以使用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 = "-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 parator;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");//-08-05map.put("sundayDate", weekEnd + " 23:59:59");map.put("mondayTime", sdf2.format(mondayDate));//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使用XSSFWorkbooktry {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");}}
