精华内容
下载资源
问答
  • 此帮助类可以将对象通过反射的方式根据类中成员变量的声明顺序将数据映射到Excel表格中,支持设置行样式,代码如下 package com.iboxpay.jdk8.streamOp; import java.io.IOException; import java.io....

    根据Excel模板填充优先推荐阿里的EasyExcel,github地址:https://github.com/alibaba/easyexcel

    由于目前老系统poi版本过低,高版本EasyExcel不兼容,因此自己实现了根据模板填充的工具类:如下:

    package com.openplatform.system.util;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.util.CollectionUtils;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.lang.reflect.Modifier;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.function.Consumer;
    
    /**
     * 由于poi版本过低,很多工具类不能用,因此自定义低版本poi的excel导出工具类
     * @author 刘亚楼
     * @date 2020/3/28
     */
    public class ExcelExportUtils {
    
      private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtils.class);
    
      /**
       * 忽略序列化id
       */
      private static final String INGORED_FIELD = "serialVersionUID";
    
      private static final String REPLACED_REGIX = "\\{|\\}";
    
      public static <T> void fillWithTemplate(List<T> dataList, String templatePath, String dest) throws Exception {
        fillWithTemplate(dataList, templatePath, new FileOutputStream(dest));
      }
    
      /**
       * 根据Excel模板填充,忽略第一行,即头行
       * @param dataList 数据列表
       * @param templatePath 模板路径
       * @param dest 目标路径
       * @param <T> 可以是JavaBean也可以是Map<String,Object>
       * @throws Exception
       */
      public static <T> void fillWithTemplate(List<T> dataList, String templatePath, OutputStream dest) throws Exception {
        File file = new File(templatePath);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        Row dataRow = sheet.getRow(1);
    
        // 导空数据
        if (CollectionUtils.isEmpty(dataList)) {
          exportOnEmptyData(dataRow, workbook, dest);
          return;
        }
    
        export(dataList, dataRow, workbook, dest);
      }
    
      private static void exportOnEmptyData(Row dataRow, Workbook workbook, OutputStream dest) throws IOException {
        doLoop(cellIndex -> {
          Cell cell = dataRow.getCell(cellIndex);
          cell.setCellValue("");
        }, dataRow.getPhysicalNumberOfCells());
    
        workbook.write(dest);
      }
    
      private static <T> void export(List<T> dataList, Row dataRow, Workbook workbook, OutputStream dest) throws Exception {
        T firstRowData = dataList.get(0);
        boolean isMapInstance = Map.class.isAssignableFrom(firstRowData.getClass());
        Map<Integer, String> templateVariableMap = parseTemplateVariable(dataRow);
    
        if (isMapInstance) {
          fillFirstDataRow(firstRowData, dataRow, templateVariableMap);
          dataList.remove(0);
          fillRemainingDataRow(dataList, dataRow, templateVariableMap);
        } else {
          Map<String, Method> methodMap = getAllGetterMethods(dataList.get(0).getClass());
          fillFirstDataRow(firstRowData, dataRow, methodMap, templateVariableMap);
          dataList.remove(0);
          fillRemainingDataRow(dataList, dataRow, methodMap, templateVariableMap);
        }
    
        workbook.write(dest);
      }
    
      private static <T> void fillFirstDataRow(T t, Row dataRow, Map<String, Method> methodMap, Map<Integer, String> templaVariableMap) {
        doLoop(cellIndex -> {
          try {
            Method getterMethod = methodMap.get(templaVariableMap.get(cellIndex));
            setCellValueByType(dataRow.getCell(cellIndex), getterMethod.invoke(t));
          } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
          }
        }, dataRow.getPhysicalNumberOfCells());
    
      }
    
      private static <T> void fillFirstDataRow(T t, Row dataRow, Map<Integer, String> templaVariableMap) {
        Map<String, Object> result = (Map<String, Object>) t;
        doLoop(cellIndex -> {
          setCellValueByType(dataRow.getCell(cellIndex), result.get(templaVariableMap.get(cellIndex)));
        }, dataRow.getPhysicalNumberOfCells());
      }
    
      private static <T> void fillRemainingDataRow(List<T> dataList, Row dataRow, Map<Integer, String> templaVariableMap) {
        int currentRowNum = dataRow.getRowNum() + 1;
    
        for (T data : dataList) {
          Map<String, Object> result = (Map<String, Object>) data;
          Row currentRow = dataRow.getSheet().createRow(currentRowNum++);
          doLoop(cellIndex -> {
            Cell currentCell = currentRow.createCell(cellIndex);
            currentCell.setCellStyle(dataRow.getCell(cellIndex).getCellStyle());
            setCellValueByType(currentCell, result.get(templaVariableMap.get(cellIndex)));
          }, dataRow.getPhysicalNumberOfCells());
        }
    
      }
    
      private static <T> void fillRemainingDataRow(List<T> dataList, Row dataRow, Map<String, Method> methodMap,
        Map<Integer, String> templaVariableMap) {
        int currentRowNum = dataRow.getRowNum() + 1;
    
        for (T data : dataList) {
          Row currentRow = dataRow.getSheet().createRow(currentRowNum++);
          doLoop(cellIndex -> {
            try {
              Method getterMethod = methodMap.get(templaVariableMap.get(cellIndex));
              Cell currentCell = currentRow.createCell(cellIndex);
              currentCell.setCellStyle(dataRow.getCell(cellIndex).getCellStyle());
              setCellValueByType(currentCell, getterMethod.invoke(data));
            } catch (Exception e) {
              LOGGER.error(e.getMessage(), e);
            }
          }, dataRow.getPhysicalNumberOfCells());
        }
      }
    
      private static void setCellValueByType(Cell dataCell, Object obj) {
        if (obj instanceof Integer || obj instanceof Float || obj instanceof Long) {
          dataCell.setCellValue(String.valueOf(obj));
        } else if (obj instanceof Double) {
          dataCell.setCellValue((Double) obj);
        } else if (obj instanceof Boolean) {
          dataCell.setCellValue((Boolean) obj);
        } else if (obj instanceof Date) {
          dataCell.setCellValue((Date) obj);
        } else if (obj instanceof Calendar) {
          dataCell.setCellValue((Calendar) obj);
        } else {
          dataCell.setCellValue(String.valueOf(obj));
        }
      }
    
      /**
       * 反射获取运行时对象所有的get方法
       * @param clazz
       * @return
       * @throws Exception
       */
      private static Map<String, Method> getAllGetterMethods(Class<?> clazz) throws Exception {
        Map<String, Method> methodMap = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();
    
        String fieldName = null;
        String methodName = null;
        for (Field field : fields) {
          // 忽略类变量
          if (Modifier.isStatic(field.getModifiers())) {
            continue;
          }
    
          fieldName = field.getName();
          methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
          methodMap.put(fieldName, clazz.getDeclaredMethod(methodName));
        }
    
        return methodMap;
      }
    
      /**
       * 从占位符中获取模板变量,如:{variable}中的模板变量为variable
       * @param dataRow
       * @return
       */
      private static Map<Integer, String> parseTemplateVariable(Row dataRow) {
        Map<Integer, String> templateVariableMap = new HashMap<>();
    
        doLoop(cellIndex -> {
          Cell currentCell = dataRow.getCell(cellIndex);
          String cellValue = currentCell.getStringCellValue();
          if (StringUtils.isNotBlank(cellValue) && cellValue.contains("{") && cellValue.contains("}")) {
            String fieldName = cellValue.replaceAll(REPLACED_REGIX, "");
            templateVariableMap.put(cellIndex, fieldName);
          }
        }, dataRow.getPhysicalNumberOfCells());
    
        return templateVariableMap;
      }
    
      private static void doLoop(Consumer<Integer> consumer, int totalColumns) {
        for (int cellIndex = 0; cellIndex < totalColumns; cellIndex++) {
          consumer.accept(cellIndex);
        }
      }
    }
    

    拓展使用:

    package com.iboxpay.jdk8.streamOp;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    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;
    import org.apache.poi.hssf.util.HSSFColor;
    
    /**
     * <b> 导出数据的帮助类,用于将数据导出至Excel表格中,可调整导出数据的样式,如:行(分为标题行和数据行)的背景颜色,字体颜色,字体高度,
     * 字体是否加粗, 是否条纹展示数据<b>
     * 
     * @author 刘亚楼
     *
     */
    public class ExportHelper {
    
      // 列宽
      private int columWidth;
      // 标题行背景颜色
      private short headerBackground;
      // 标题行字体颜色
      private short headerFontColor;
      // 标题行字体是否加粗
      private boolean isHeaderFontBold;
      // 标题行字体高度
      private short headerFontWeight;
      // 数据行背景颜色
      private short dataRowBackground;
      // 数据行另一种背景色,用于显示条纹效果
      private short dataRowBackground2;
      // 数据行字体颜色
      private short dataRowFontColor;
      // 数据行字体是否加粗
      private boolean isDataRowFontBold;
      // 数据行字体高度
      private short dataRowFontWeight;
      // 是否给数据行加条纹
      private boolean isStriped;
    
      public ExportHelper() {
        this.columWidth = 12;
        this.headerBackground = HSSFColor.WHITE.index;
        this.headerFontColor = HSSFColor.BLACK.index;
        this.isHeaderFontBold = false;
        this.headerFontWeight = 0;
        this.dataRowBackground = HSSFColor.WHITE.index;
        this.dataRowBackground2 = HSSFColor.GREY_25_PERCENT.index;
        this.dataRowFontColor = HSSFColor.BLACK.index;
        this.isDataRowFontBold = false;
        this.dataRowFontWeight = 0;
        this.isStriped = false;
      }
    
      /**
      * 设定列宽
      * 
      * @param columWidth
      */
      public void setColumWidth(int columWidth) {
        this.columWidth = columWidth;
      }
    
      /**
      * 设置标题行字体颜色,可通过HSSFColor.colorClass.index设置
      * 
      * @param headerFontColor
      */
      public void setHeaderFontColor(short headerFontColor) {
        this.headerFontColor = headerFontColor;
      }
    
      /**
      * 设置数据行字体颜色,可通过HSSFColor.colorClass.index设置
      * 
      * @param dataRowFontColor
      */
      public void setDataRowFontColor(short dataRowFontColor) {
        this.dataRowFontColor = dataRowFontColor;
      }
    
      /**
      * 设置标题行的背景颜色,可通过HSSFColor.colorClass.index设置
      * 
      * @param headerBackground
      */
      public void setHeaderBackground(short headerBackground) {
        this.headerBackground = headerBackground;
      }
    
      /**
      * 设置数据行的背景颜色,可通过HSSFColor.colorClass.index设置
      * 
      * @param dataRowBackground
      */
      public void setDataRowBackground(short dataRowBackground) {
        this.dataRowBackground = dataRowBackground;
      }
    
      /**
      * 设置标题行字体粗度,默认为false
      * 
      * @param isHeaderFontBold
      */
      public void setHeaderFontBold(boolean isHeaderFontBold) {
        this.isHeaderFontBold = isHeaderFontBold;
      }
    
      /**
      * 设置数据行字体粗度,默认为false
      * 
      * @param isDataRowFontBold
      */
      public void setDataRowFontBold(boolean isDataRowFontBold) {
        this.isDataRowFontBold = isDataRowFontBold;
      }
    
      /**
      * 设置标题行字体高度
      * 
      * @param headerFontWeight
      */
      public void setHeaderFontWeight(short headerFontWeight) {
        this.headerFontWeight = headerFontWeight;
      }
    
      /**
      * 设置数据行字体高度
      * 
      * @param dataRowFontWeight
      */
      public void setDataRowFontWeight(short dataRowFontWeight) {
        this.dataRowFontWeight = dataRowFontWeight;
      }
    
      /**
      * 设置数据行是否条纹展示
      * 
      * @param isStriped
      */
      public void setStriped(boolean isStriped) {
        this.isStriped = isStriped;
      }
    
      /**
      * 数据行另一种背景颜色,用于显示条纹效果
      * 
      * @param dataRowBackground2
      */
      public void setDataRowBackground2(short dataRowBackground2) {
        this.dataRowBackground2 = dataRowBackground2;
      }
    
      /**
      * 将list中的对象转换为Workbook,并且将Workbook写到输出流中
      * 
      * @param sheetName
      *            表单的名字
      * @param headers
      *            标题
      * @param list
      *            包含对象的list集合
      * @param os
      *            输出流
      * @throws NoSuchMethodException
      * @throws SecurityException
      * @throws IllegalAccessException
      * @throws IllegalArgumentException
      * @throws InvocationTargetException
      * @throws IOException
      */
      public <T> void exportToExcel(String sheetName, String[] headers, List<T> list, OutputStream os)
          throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException,
          InvocationTargetException, IOException {
        // 声明一个工作簿,HSSFWorkbook为2007以上的版本
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 声明一个表格
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置默认的列宽
        sheet.setDefaultColumnWidth(columWidth);
        // 创建标题行样式
        HSSFCellStyle headerStyle =
            createRowStyle(workbook, headerBackground, headerFontColor, headerFontWeight, isHeaderFontBold);
        // 创建数据样式
        HSSFCellStyle rowStyle =
            createRowStyle(workbook, dataRowBackground, dataRowFontColor, dataRowFontWeight, isDataRowFontBold);
        HSSFCellStyle rowStyle2 = null;
        if (isStriped) {
          // 有条纹展示,创建另一种数据行样式
          rowStyle2 = createRowStyle(workbook, dataRowBackground2, dataRowFontColor, dataRowFontWeight, isDataRowFontBold);
        }
        // 为表格填充内容
        fillContentsForSheet(headers, list, sheet, headerStyle, rowStyle, rowStyle2);
        // 将表格中的内容写入输出流中
        workbook.write(os);
        os.flush();
      }
    
      /**
      * 创建行样式
      * 
      * @param workbook
      *            工作簿对象
      * @param background
      *            行背景颜色
      * @param fontColor
      *            字体颜色
      * @param fontWeight
      *            字体高度
      * @param isFontBold
      *            字体是否加粗
      * @return
      */
      private HSSFCellStyle createRowStyle(HSSFWorkbook workbook, short background, short fontColor, short fontWeight,
          boolean isFontBold) {
        // 创建样式
        HSSFCellStyle rowStyle = workbook.createCellStyle();
        // 设置填充颜色
        rowStyle.setFillForegroundColor(background);
        // 设置填充样式
        rowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        // 设置边框
        rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置水平对齐方式
        rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐方式
        rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 创建字体
        HSSFFont rowFont = workbook.createFont();
        rowFont.setColor(fontColor);
        if (fontWeight != 0) {
          rowFont.setFontHeightInPoints(fontWeight);
        }
        // 默认设为normal
        rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        if (isFontBold) {
          rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        // 把字体应用到当前的样式
        rowStyle.setFont(rowFont);
        return rowStyle;
      }
    
      // 为表格填充内容
      private <T> void fillContentsForSheet(String[] headers, List<T> list, HSSFSheet sheet, HSSFCellStyle headerStyle,
          HSSFCellStyle rowStyle, HSSFCellStyle rowStyle2)
          throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        // 是否存在标题行
        boolean isHeaderRowExisted = false;
        if (headers != null && headers.length > 0) {
          isHeaderRowExisted = true;
          // 创建标题行
          HSSFRow headerRow = sheet.createRow(0);
          for (int i = 0; i < headers.length; i++) {
            HSSFCell headerCell = headerRow.createCell(i);
            headerCell.setCellStyle(headerStyle);
            headerCell.setCellValue(headers[i]);
          }
        }
        if (list != null && list.size() > 0) {
          // 获得list中对象的运行时类
          Class<T> clazz = (Class<T>) list.get(0).getClass();
          List<Method> methodList = getAllGetMethods(clazz);
          int rowNum = 0;
          if (isHeaderRowExisted) {
            rowNum = 1;// 如果有标题行行号则从1开始,没有从0开始
          }
          HSSFCellStyle style = null;
          for (int row = rowNum, index = 0; index < list.size(); row++, index++) {
            T t = list.get(index);
            // 如果有条纹展示,奇偶行样式各不同
            if (rowStyle2 != null && index % 2 == 0) {
              style = rowStyle2;
            } else {
              style = rowStyle;
            }
            // 创建数据行
            HSSFRow dataRow = sheet.createRow(row);
            // 一个getXXX方法代表一个字段值,根据字段值生成列的个数
            for (int cell = 0; cell < methodList.size(); cell++) {
              HSSFCell dataCell = dataRow.createCell(cell);
              dataCell.setCellStyle(style);
              // 通过反射调用对应的getXXX方法
              Object obj = methodList.get(cell).invoke(t);
              // 判断数据类型,设置单元格的值
              if (obj instanceof Integer) {
                Integer value = (Integer) obj;
                dataCell.setCellValue(String.valueOf(value));
              } else if (obj instanceof Float) {
                Float value = (Float) obj;
                double doubleValue = Double.parseDouble(String.valueOf(value));
                dataCell.setCellValue(doubleValue);
              } else if (obj instanceof Long) {
                Long value = (Long) obj;
                dataCell.setCellValue(String.valueOf(value));
              } else if (obj instanceof Double) {
                Double value = (Double) obj;
                dataCell.setCellValue(value);
              } else if (obj instanceof Boolean) {
                Boolean value = (Boolean) obj;
                dataCell.setCellValue(value);
              } else if (obj instanceof Date) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                dataCell.setCellValue(sdf.format(obj));
              } else if (obj instanceof Calendar) {
                Calendar c = (Calendar) obj;
                Date date = c.getTime();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                dataCell.setCellValue(sdf.format(date));
              } else {
                String value = (String) obj;
                dataCell.setCellValue(value);
              }
            }
          }
        }
      }
    
      // 获取类中每个成员变量对应的get方法,存入集合中
      private List<Method> getAllGetMethods(Class<?> clazz) throws NoSuchMethodException, SecurityException {
        List<Method> methodList = new ArrayList<Method>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
          // 如果名字为serialVersionUID,代表字段值为序列化编号,应该跳过
          if (field.getName().equals("serialVersionUID")) {
            continue;
          }
          String fieldName = field.getName();
          String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
          Method getMethod = clazz.getDeclaredMethod(methodName);
          methodList.add(getMethod);
        }
        return methodList;
      }
    }

     

    展开全文
  • JAVA实现Excel模板填充

    千次阅读 2019-04-18 20:44:33
    需要将统计数据填充指定Excel模板中 public void writeExcelFile(LocalDate start, LocalDate end, String filename) { File file = new File("files/report_Demo.xlsx"); XSSFWorkbook workbook = null; ...

    需要将统计数据填充到指定的Excel模板中

     public void writeExcelFile(LocalDate start, LocalDate end, String filename) {
            File file = new File("files/report_Demo.xlsx");
            XSSFWorkbook workbook = null;
            try {
                FileInputStream fileInputStream = new FileInputStream(file);
                workbook = new XSSFWorkbook(fileInputStream);
            } catch (Exception e) {
            }
            //查找到指定的sheet
            XSSFSheet sheet = workbook.getSheet("抓取数据展示");
            sheet.setForceFormulaRecalculation(true);
            Map<String, List<StatInfo>> sourceCompares = getCompare(start, end);
            Map<String, String> catchMap = new HashMap<>();
            for (Map.Entry<String, List<StatInfo>> entry : sourceCompares.entrySet()) {
                String site = entry.getKey();
                int rowNum = 0;
               //判断指定数据存放到指定的行数
                switch (site) {
                    case "weixin":
                        rowNum = 23;
                        break;
                    case "article":
                        rowNum = 32;
                        break;
                    case "rss":
                        rowNum = 43;
                        break;
                    case "yidian":
                        rowNum = 54;
                        break;
                    case "weiboarticle":
                        rowNum = 64;
                        break;
                    case "toutiao":
                        rowNum = 75;
                        break;
                }
                XSSFRow row = sheet.getRow(rowNum);
                List<StatInfo> statInfos = entry.getValue();
                //按照相同的site的时间相同属性去重
                for (int i = 0; i < statInfos.size(); i++) {
                    if (catchMap.containsKey(statInfos.get(i).getSite())
                            && catchMap.get(statInfos.get(i).getSite()).equals(statInfos.get(i).getTime())) {
                        statInfos.remove(i);
                    } else {
                        catchMap.put(statInfos.get(i).getSite(), statInfos.get(i).getTime());
                    }
                }
                for (int i = 0; i < statInfos.size(); i++) {
                    //当上一周的数据填充完成了就填充本周7天数据,行数+1换到本周
                    if (i == 7) {
                        row = sheet.getRow(rowNum + 1);
                    }
                    //从第三列开始进行,并以7天为一个周期循环找列数
                    XSSFCell cell = row.getCell(3 + i % 7);
                    cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(statInfos.get(i).getCatchCount());
                }
    
            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            try {
                workbook.write(bos);
                File outfile = new File(filename);
                FileOutputStream fileOutputStream = new FileOutputStream(outfile);
                fileOutputStream.write(bos.toByteArray());
                fileOutputStream.flush();
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (bos != null) {
                        bos.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("周报数据已经完成,请检查");
        }
    

    最终的Excel表格效果:

    展开全文
  • import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*;... /**根据模板导出excel,根据ex
    package com.common.utils;
    
    import com.common.constant.FileType;
    import com.modules.wms.system.dao.DownloadFileDao;
    import com.modules.wms.system.entity.DownloadFile;
    import com.modules.wms.system.entity.SysUserToken;
    import com.modules.wms.system.service.SysUserTokenService;
    import org.apache.commons.collections4.CollectionUtils;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URL;
    import java.util.*;
    import java.util.Map.Entry;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    /**根据模板导出excel,根据excel坐标赋值,如(C1单元格)
     * @author shanph
     * @since 2020/9/27
     */
    @Service
    public class ExcelExportUtil {
    
        @Autowired
        private SysUserTokenService userTokenService;
        @Autowired
        private DownloadFileDao auDownloadFileDao;
        //模板map
        private Map<String, Workbook> tempWorkbook = new HashMap<String, Workbook>();
        //模板输入流map
        private Map<String, InputStream> tempStream = new HashMap<String, InputStream>();
    
        private static  final  String DECLARATION_FILE_PURCHASE= "document/PurchaseOrderHMTemplate.xlsx";
        /**
         * 功能:按模板向Excel中相应地方填充数据
         */
        public void writeData(String templateFilePath, Map<String, Object> dataMap, int sheetNo) throws IOException, InvalidFormatException {
            if (dataMap == null || dataMap.isEmpty()) {
                return;
            }
            //读取模板
            Workbook wbModule = getTempWorkbook(templateFilePath);
            //数据填充的sheet
            Sheet wsheet = wbModule.getSheetAt(sheetNo);
    
            for (Entry<String, Object> entry : dataMap.entrySet()) {
                String point = entry.getKey();
                Object data = entry.getValue();
    
                TempCell cell = getCell(point, data, wsheet);
                //指定坐标赋值
                setCell(cell, wsheet);
            }
    
            //设置生成excel中公式自动计算
            wsheet.setForceFormulaRecalculation(true);
        }
    
        /**
         * 功能:按模板向Excel中列表填充数据.只支持列合并
         */
        public void writeDateList(String templateFilePath, String[] heads, List<Map<Integer, Object>> datalist, int sheetNo) throws IOException, InvalidFormatException {
            if (heads == null || heads.length <= 0 || CollectionUtils.isEmpty(datalist)) {
                return;
            }
            //读取模板
            Workbook wbModule = getTempWorkbook(templateFilePath);
            //数据填充的sheet
            Sheet wsheet = wbModule.getSheetAt(sheetNo);
    
            //列表数据模板cell
            List<TempCell> tempCells = new ArrayList<TempCell>(heads.length);
            for (String point : heads) {
                TempCell tempCell = getCell(point, null, wsheet);
                //取得合并单元格位置  -1:表示不是合并单元格
                int pos = isMergedRegion(wsheet, tempCell.getRow(), tempCell.getColumn());
                if (pos > -1) {
                    CellRangeAddress range = wsheet.getMergedRegion(pos);
                    tempCell.setColumnSize(range.getLastColumn() - range.getFirstColumn());
                }
                tempCells.add(tempCell);
            }
            int num = 0;
            //赋值
            for (int i = 0; i < datalist.size(); i++) {//数据行
                Map<Integer, Object> dataMap = datalist.get(i);
                for (int j = 0; j < tempCells.size(); j++) {//列
                    TempCell tempCell = tempCells.get(j);
                    if(i==0 && num ==0 && datalist.size()-6>0) {
                        insertRow(wsheet, tempCell.getRow(), datalist.size()-6);
                    }
                    num++;
                    tempCell.setData(dataMap.get(j + 1));
                    setCell(tempCell, wsheet);
                    tempCell.setRow(tempCell.getRow() + 1);
                }
            }
            //设置生成excel中公式自动计算
            wsheet.setForceFormulaRecalculation(true);
        }
    
        public void writeDataListNew(String templateFilePath, String[] heads, String supplierInfo,String purchaseNo,List<Map<Integer, Object>> datalist, int sheetNo) throws IOException, InvalidFormatException {
            if (heads == null || heads.length <= 0 || CollectionUtils.isEmpty(datalist)) {
                return;
            }
            //读取模板
            Workbook wbModule = getTempWorkbookNew(templateFilePath);
            //数据填充的sheet
            Sheet wsheet = wbModule.getSheetAt(sheetNo);
    
            //列表数据模板cell
            List<TempCell> tempCells = new ArrayList<TempCell>(heads.length);
            for (String point : heads) {
                TempCell tempCell = getCell(point, null, wsheet);
                //取得合并单元格位置  -1:表示不是合并单元格
                int pos = isMergedRegion(wsheet, tempCell.getRow(), tempCell.getColumn());
                if (pos > -1) {
                    CellRangeAddress range = wsheet.getMergedRegion(pos);
                    tempCell.setColumnSize(range.getLastColumn() - range.getFirstColumn());
                }
                tempCells.add(tempCell);
            }
            int num = 0;
            //赋值
            for (int i = 0; i < datalist.size(); i++) {//数据行
                Map<Integer, Object> dataMap = datalist.get(i);
                for (int j = 0; j < tempCells.size(); j++) {//列
                    TempCell tempCell = tempCells.get(j);
                    if(i==0 && num ==0 && datalist.size()-6>0) {
                        insertRow(wsheet, tempCell.getRow(), datalist.size()-6);
                    }
                    num++;
                    tempCell.setData(dataMap.get(j + 1));
                    setCell(tempCell, wsheet);
                    tempCell.setRow(tempCell.getRow() + 1);
                }
            }
            //设置供应商信息
            for (int i = 0; i < wsheet.getLastRowNum(); i++) {
                if(null==wsheet.getRow(i)){
                    continue;
                }
                if(i==1&&wsheet.getRow(1).getCell(5).getCellTypeEnum().equals(CellType.STRING)){
                    if(wsheet.getRow(1).getCell(5).getStringCellValue().equals("PO#")){
                        wsheet.getRow(1).getCell(5).setCellValue("PO#"+purchaseNo);
                    }
                }
                if(wsheet.getRow(i).getCell(0).getCellTypeEnum().equals(CellType.STRING)){
                    if(wsheet.getRow(i).getCell(0).getStringCellValue().equals("To: ")){
                        wsheet.getRow(i).getCell(0).setCellValue("To: "+supplierInfo);
                        break;
                    }
                }
            }
            //设置生成excel中公式自动计算
            wsheet.setForceFormulaRecalculation(true);
        }
    
        /**
         * 功能:获取输入工作区
         */
        private Workbook getTempWorkbook(String templateFilePath) throws IOException, InvalidFormatException {
            if (!tempWorkbook.containsKey(templateFilePath)) {
                InputStream inputStream = getInputStream(templateFilePath);
                tempWorkbook.put(templateFilePath, WorkbookFactory.create(inputStream));
            }
            return tempWorkbook.get(templateFilePath);
        }
    
        /**
         * 功能:获取输入工作区
         */
        private Workbook getTempWorkbookNew(String templateFilePath) throws IOException, InvalidFormatException {
            if (!tempWorkbook.containsKey(templateFilePath)) {
                BufferedInputStream fis = new BufferedInputStream(getInputStreamNew(templateFilePath));
                tempWorkbook.put(templateFilePath, WorkbookFactory.create(fis));
            }
            return tempWorkbook.get(templateFilePath);
        }
    
        /**
         * 功能:获得模板输入流
         */
        private InputStream getInputStream(String templateFilePath) throws FileNotFoundException {
            if (!tempStream.containsKey(templateFilePath)) {
                tempStream.put(templateFilePath, new FileInputStream((templateFilePath)));
            }
            return tempStream.get(templateFilePath);
        }
    
        /**
         * 功能:获得云模板输入流
         */
        private InputStream getInputStreamNew(String templateFilePath) throws IOException {
            if (!tempStream.containsKey(templateFilePath)) {
                URL httpUrl=new URL(templateFilePath);
                tempStream.put(templateFilePath, httpUrl.openStream());
            }
            return tempStream.get(templateFilePath);
        }
    
        /**
         * 功能:获取单元格数据,样式(根据坐标:B3)
         */
        private TempCell getCell(String point, Object data, Sheet sheet) {
            TempCell tempCell = new TempCell();
    
            //得到列   字母
            String lineStr = "";
            String reg = "[A-Z]+";
            Pattern p = Pattern.compile(reg);
            Matcher m = p.matcher(point);
            while (m.find()) {
                lineStr = m.group();
            }
            //将列字母转成列号  根据ascii转换
            char[] ch = lineStr.toCharArray();
            int column = 0;
            for (int i = 0; i < ch.length; i++) {
                char c = ch[i];
                int post = ch.length - i - 1;
                int r = (int) Math.pow(10, post);
                column = column + r * ((int) c - 65);
            }
            tempCell.setColumn(column);
    
            //得到行号
            reg = "[1-9]+";
            p = Pattern.compile(reg);
            m = p.matcher(point);
            while (m.find()) {
                tempCell.setRow((Integer.parseInt(m.group()) - 1));
            }
    
            //获取模板指定单元格样式,设置到tempCell(写列表数据的时候用)
            Row rowIn = sheet.getRow(tempCell.getRow());
            if (rowIn == null) {
                rowIn = sheet.createRow(tempCell.getRow());
            }
            Cell cellIn = rowIn.getCell(tempCell.getColumn());
            if (cellIn == null) {
                cellIn = rowIn.createCell(tempCell.getColumn());
            }
            tempCell.setCellStyle(cellIn.getCellStyle());
            tempCell.setData(data);
            return tempCell;
        }
    
        /**
         * 功能:给指定坐标单元格赋值
         */
        private void setCell(TempCell tempCell, Sheet sheet) {
            if (tempCell.getColumnSize() > -1) {
                CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn() + tempCell.getColumnSize());
                setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet);
            }
            Row rowIn = sheet.getRow(tempCell.getRow());
            if (rowIn == null) {
                copyRows(tempCell.getRow() - 1, tempCell.getRow() - 1, tempCell.getRow(), sheet);//复制上一行
                rowIn = sheet.getRow(tempCell.getRow());
            }
            Cell cellIn = rowIn.getCell(tempCell.getColumn());
            if (cellIn == null) {
                cellIn = rowIn.createCell(tempCell.getColumn());
            }
            //根据data类型给cell赋值
            if (tempCell.getData() instanceof String) {
                cellIn.setCellValue((String) tempCell.getData());
            } else if (tempCell.getData() instanceof Integer) {
                cellIn.setCellValue((int) tempCell.getData());
            } else if (tempCell.getData() instanceof Double) {
                cellIn.setCellValue((double) tempCell.getData());
            } else {
                cellIn.setCellValue((String) tempCell.getData());
            }
            //样式
            if (tempCell.getCellStyle() != null && tempCell.getColumnSize() == -1) {
                cellIn.setCellStyle(tempCell.getCellStyle());
            }
        }
    
        private void setCellNew(TempCell tempCell, Sheet sheet ,String addinfo) {
            if (tempCell.getColumnSize() > -1) {
                CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn() + tempCell.getColumnSize());
                setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet);
            }
            Row rowIn = sheet.getRow(tempCell.getRow());
            if (rowIn == null) {
                copyRows(tempCell.getRow() - 1, tempCell.getRow() - 1, tempCell.getRow(), sheet);//复制上一行
                rowIn = sheet.getRow(tempCell.getRow());
            }
            Cell cellIn = rowIn.getCell(tempCell.getColumn());
            if (cellIn == null) {
                cellIn = rowIn.createCell(tempCell.getColumn());
            }
            //根据data类型给cell赋值
            if (tempCell.getData() instanceof String) {
                cellIn.setCellValue((String) tempCell.getData());
            } else if (tempCell.getData() instanceof Integer) {
                cellIn.setCellValue((int) tempCell.getData());
            } else if (tempCell.getData() instanceof Double) {
                cellIn.setCellValue((double) tempCell.getData());
            } else {
                cellIn.setCellValue((String) tempCell.getData());
            }
            //样式
            if (tempCell.getCellStyle() != null && tempCell.getColumnSize() == -1) {
                cellIn.setCellStyle(tempCell.getCellStyle());
            }
        }
    
        /**
         * 功能:写到输出流并移除资源
         */
        public void writeAndClose(String templateFilePath, OutputStream os) throws IOException, InvalidFormatException {
            if (getTempWorkbook(templateFilePath) != null) {
                getTempWorkbook(templateFilePath).write(os);
                tempWorkbook.remove(templateFilePath);
            }
            if (getInputStream(templateFilePath) != null) {
                getInputStream(templateFilePath).close();
                tempStream.remove(templateFilePath);
            }
        }
        /**
         * 功能:写到输出流并移除资源
         */
        public void writeByteAndClose(String templateFilePath, OutputStream os) throws IOException, InvalidFormatException {
            if (getTempWorkbook(templateFilePath) != null) {
                getTempWorkbook(templateFilePath).write(os);
                tempWorkbook.remove(templateFilePath);
            }
            if (getInputStreamNew(templateFilePath) != null) {
                getInputStreamNew(templateFilePath).close();
                tempStream.remove(templateFilePath);
            }
        }
    
        public void writeByteAndCloseNew(String templateFilePath, OutputStream os) throws IOException, InvalidFormatException {
            if (getTempWorkbook(templateFilePath) != null) {
                getTempWorkbook(templateFilePath).write(os);
                tempWorkbook.remove(templateFilePath);
            }
            if (getInputStreamNew(templateFilePath) != null) {
                getInputStreamNew(templateFilePath).close();
                tempStream.remove(templateFilePath);
            }
        }
    
        /**
         * 功能:判断指定的单元格是否是合并单元格
         */
        private Integer isMergedRegion(Sheet sheet, int row, int column) {
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress range = sheet.getMergedRegion(i);
                int firstColumn = range.getFirstColumn();
                int lastColumn = range.getLastColumn();
                int firstRow = range.getFirstRow();
                int lastRow = range.getLastRow();
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        return i;
                    }
                }
            }
            return -1;
        }
    
        /**
         * 功能:合并单元格
         */
        private CellRangeAddress mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
            CellRangeAddress rang = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(rang);
            return rang;
        }
    
        /**
         * 功能:设置合并单元格样式
         */
        private void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
            for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) row = sheet.createRow(i);
                for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(cs);
                }
            }
        }
    
        /**
         * 功能:insertRow
         */
        public static void insertRow(Sheet sheet,int starRow,int rows){
            System.out.println(sheet.getLastRowNum());
            sheet.shiftRows(starRow +1 , sheet.getLastRowNum(), rows,true,false);
            starRow = starRow - 1;
            for (int i = 0; i < rows; i++) {
                Row sourceRow = null;
                Row targetRow = null;
                Cell sourceCell = null;
                Cell targetCell = null;
                short m;
                starRow = starRow + 1;
                sourceRow = sheet.getRow(starRow);
                targetRow = sheet.createRow(starRow + 1);
                targetRow.setHeight(sourceRow.getHeight());
    
                for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                    sourceCell = sourceRow.getCell(m);
                    targetCell = targetRow.createCell(m);
                    targetCell.setCellStyle(sourceCell.getCellStyle());
                    if(sourceCell.getCellTypeEnum().equals(CellType.FORMULA)){
                        String newFormula = sourceCell.getCellFormula().replace(String.valueOf(starRow+1),String.valueOf(starRow+2));
                        targetCell.setCellFormula(newFormula);
                    }
                }
            }
        }
    
    
        /**
         * 功能:copy rows
         */
        private void copyRows(int startRow, int endRow, int pPosition, Sheet sheet) {
            int pStartRow = startRow - 1;
            int pEndRow = endRow - 1;
            int targetRowFrom;
            int targetRowTo;
            int columnCount;
            CellRangeAddress region = null;
            int i;
            int j;
            if (pStartRow == -1 || pEndRow == -1) {
                return;
            }
            // 拷贝合并的单元格
            for (i = 0; i < sheet.getNumMergedRegions(); i++) {
                region = sheet.getMergedRegion(i);
                if ((region.getFirstRow() >= pStartRow)
                        && (region.getLastRow() <= pEndRow)) {
                    targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
                    targetRowTo = region.getLastRow() - pStartRow + pPosition;
                    CellRangeAddress newRegion = region.copy();
                    newRegion.setFirstRow(targetRowFrom);
                    newRegion.setFirstColumn(region.getFirstColumn());
                    newRegion.setLastRow(targetRowTo);
                    newRegion.setLastColumn(region.getLastColumn());
                    sheet.addMergedRegion(newRegion);
                }
            }
            // 设置列宽
            for (i = pStartRow; i <= pEndRow; i++) {
                Row sourceRow = sheet.getRow(i);
                columnCount = sourceRow.getLastCellNum();
                if (sourceRow != null) {
                    Row newRow = sheet.createRow(pPosition - pStartRow + i);
                    newRow.setHeight(sourceRow.getHeight());
                    for (j = 0; j < columnCount; j++) {
                        Cell templateCell = sourceRow.getCell(j);
                        if (templateCell != null) {
                            Cell newCell = newRow.createCell(j);
                            copyCell(templateCell, newCell);
                        }
                    }
                }
            }
        }
    
        /**
         * 功能:copy cell,不copy值
         */
        private void copyCell(Cell srcCell, Cell distCell) {
            distCell.setCellStyle(srcCell.getCellStyle());
            if (srcCell.getCellComment() != null) {
                distCell.setCellComment(srcCell.getCellComment());
            }
            int srcCellType = srcCell.getCellType();
            distCell.setCellType(srcCellType);
        }
    
        /**
         * 描述:临时单元格数据
         */
        class TempCell {
            private int row;
            private int column;
            private CellStyle cellStyle;
            private Object data;
            //用于列表合并,表示几列合并
            private int columnSize = -1;
    
            public int getColumn() {
                return column;
            }
    
            public void setColumn(int column) {
                this.column = column;
            }
    
            public int getRow() {
                return row;
            }
    
            public void setRow(int row) {
                this.row = row;
            }
    
            public CellStyle getCellStyle() {
                return cellStyle;
            }
    
            public void setCellStyle(CellStyle cellStyle) {
                this.cellStyle = cellStyle;
            }
    
            public Object getData() {
                return data;
            }
    
            public void setData(Object data) {
                this.data = data;
            }
    
            public int getColumnSize() {
                return columnSize;
            }
    
            public void setColumnSize(int columnSize) {
                this.columnSize = columnSize;
            }
        }
    
        public void main() throws Exception {
            String templateFilePath = "C:\\Users\\Administrator.SC-202004202026\\Downloads\\Purchase Order HM Template2.xlsx";
            File file = new File("C:\\Users\\Administrator.SC-202004202026\\Downloads\\222.xlsx");
            OutputStream os = new FileOutputStream(file);
            ByteArrayOutputStream osByte = new ByteArrayOutputStream();
    
            ExcelExportUtil excel = new ExcelExportUtil();
            /*Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put("B1", "工作时间统计表");
            dataMap.put("B2", "统计时间:2020/01/01 - 2020/03/31");
            excel.writeData(templateFilePath, dataMap, 0);
            */
            List<Map<Integer, Object>> datalist = new ArrayList<Map<Integer, Object>>();
            for (int i = 0; i <10; i++) {
                Map<Integer, Object> data = new HashMap<Integer, Object>();
                data.put(1, "3/10/17");
                data.put(2, "18:50");
                data.put(3, i+1);
                data.put(4, 6);
                data.put(5,(int)data.get(3)*(int)data.get(4));
                datalist.add(data);
            }
    
            String[] heads = new String[]{"A5", "B5", "C5", "D5","E5"};
            excel.writeDateList(templateFilePath, heads, datalist, 0);
    
            //写到输出流并移除资源
            excel.writeAndClose(templateFilePath, os);
            excel.writeByteAndClose(templateFilePath,osByte);
            os.flush();
            os.close();
            System.out.println("********"+"结束1");
        }
    
        public void streamHandle(HttpServletResponse response) throws Exception {
            String templateFilePath = OssUtils.getOSSPath(DECLARATION_FILE_PURCHASE);
            ByteArrayOutputStream osByte = new ByteArrayOutputStream();
            String purchaseNo = "purchaseNo";
            ExcelExportUtil excel = new ExcelExportUtil();
            List<Map<Integer, Object>> datalist = new ArrayList<Map<Integer, Object>>();
            for (int i = 0; i <1; i++) {
                Map<Integer, Object> data = new HashMap<Integer, Object>();
                data.put(1, "1");
                data.put(2, "2");
                data.put(3, i+1);
                data.put(4, 6);
                data.put(5,(int)data.get(3)*(int)data.get(4));
                datalist.add(data);
            }
            String[] heads = new String[]{"A5", "B5", "C5", "D5","E5"};
            String supplierInfo = "666";
            excel.writeDataListNew(templateFilePath, heads,purchaseNo,supplierInfo, datalist, 0);
    
            //写到输出流并移除资源
            excel.writeByteAndCloseNew(templateFilePath,osByte);
            ByteArrayInputStream inputStream = new ByteArrayInputStream(osByte.toByteArray());
            BufferedInputStream bis = new BufferedInputStream(inputStream);
            ServletOutputStream outputStream = response.getOutputStream();
    
            byte[] outputByte = new byte[1024];
            int readTmp = 0;
                while ((readTmp = bis.read(outputByte)) != -1) {
                    outputStream.write(outputByte, 0, readTmp);
                }
                outputStream.close();
                bis.close();
            inputStream.close();
            osByte.close();
        }
    
        public void  excelHandle(String templateFilePath,List<Map<Integer, Object>> datalist,String[] heads,String supplierInfo,int sheetNo,HttpServletResponse response) throws Exception {
            ByteArrayOutputStream osByte = new ByteArrayOutputStream();
            String purchaseNo = "purchaseNo";
            writeDataListNew(templateFilePath, heads,purchaseNo, supplierInfo,datalist, sheetNo);
            writeByteAndCloseNew(templateFilePath,osByte);
    
            ByteArrayInputStream inputStream = new ByteArrayInputStream(osByte.toByteArray());
            BufferedInputStream bis = new BufferedInputStream(inputStream);
            ServletOutputStream outputStream = response.getOutputStream();
            byte[] outputByte = new byte[1024];
            int readTmp = 0;
            while ((readTmp = bis.read(outputByte)) != -1) {
                outputStream.write(outputByte, 0, readTmp);
            }
            outputStream.close();
            bis.close();
            inputStream.close();
            osByte.close();
        }
    
        @Transactional(rollbackFor = Exception.class)
        public Result cloudHandle(String templateFilePath, String cloudFileName,String supplierInfo,String purchaseNo,List<Map<Integer, Object>> datalist, String[] heads, int sheetNo, HttpServletRequest servletRequest, HttpServletResponse response) throws Exception {
            ByteArrayOutputStream osByte = new ByteArrayOutputStream();
            ByteArrayInputStream inputStream = null;
            try {
                writeDataListNew(templateFilePath, heads, supplierInfo,purchaseNo,datalist, sheetNo);
                writeByteAndCloseNew(templateFilePath,osByte);
                inputStream = new ByteArrayInputStream(osByte.toByteArray());
    
                String token = GetTokenUtils.getRequestToken(servletRequest);
                SysUserToken sysUserToken = userTokenService.queryByToken(token);
                DownloadFile downloadFile = new DownloadFile();
                downloadFile.setCreateTime(new Date());
                downloadFile.setUserId(Integer.parseInt(sysUserToken.getUserId().toString()));
                downloadFile.setFileName(cloudFileName);
                downloadFile.setFileType("purchExportTemplate");
                downloadFile.setStatus(0);
                DownloadFile downloadFileByFileName = auDownloadFileDao.querObjectByFileName(cloudFileName);
                if(null!=downloadFileByFileName){
                    auDownloadFileDao.deleteByFileName(cloudFileName);
                }
                auDownloadFileDao.save(downloadFile);
    
                Result result = OssUtils.uploadFile(FileType.XLS,inputStream);
    
                downloadFile.setCompleteTime(new Date());
                downloadFile.setStatus(1);
                String ossPath = OssUtils.getOSSPath((String) result.get("msg"));
                downloadFile.setFileUrl(ossPath);
                auDownloadFileDao.update(downloadFile);
            } finally {
                inputStream.close();
                osByte.close();
            }
            return Result.ok().put("url",cloudFileName);
        }
    
    }
    

    注意事项包引入

    <dependency>
         <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
       <version>3.17</version>
    </dependency>

     

    展开全文
  • 目录Python将Excel数据自动填写到Word需求实现 Python将Excel数据自动填写到Word 需求 需求:批量制作档案表,要从...合同docx模板(在需要插值的位置填充 {{}} 表达式) 人员数据xlsx模板 实现 代码如下 ![在这里插入

    Python将Excel数据自动填写到Word

    需求

    1. 需求:批量制作档案表,要从excel表格中将每个人的数据导入到docx档案中,重复量很大,因此可以使用python高效解决。
    2. 渲染:为了让模板内容不变动,这里使用了类似jinja2的渲染引擎,使用{{ }}插值表达式把数据插入进去。也可以使用{% %}循环,条件语法等。
    3. 模板

    合同docx模板(在需要插值的位置填充 {{}} 表达式)
    在这里插入图片描述
    人员数据xlsx模板
    在这里插入图片描述

    实现

    代码如下

    from docxtpl import DocxTemplate
    from openpyxl import load_workbook
    import os
    def replace(obj):
        if obj is None:
           obj = ''
           return obj
     # 加载要填入的数据
    wb = load_workbook(r"C:\Users\MSI2\data(1).xlsx")
    ws = wb['Sheet1']
    contexts = []
    for row in range(2, ws.max_row + 1):
        name = ws["A" + str(row)].value
        c_name = ws["B" + str(row)].value
        code = ws["C" + str(row)].value
        num = ws["D" + str(row)].value
        time = ws["E" + str(row)].value
        time = str(time)[:-9]
        money = ws["F" + str(row)].value
        address = ws["G" + str(row)].value
        replace_peo = ws["H" + str(row)].value
        
        context = {"name": name, "c_name": c_name, "code": code, "num": num, "time": time,
                   "money": money, "address": address, "replace_peo": replace_peo}
        contexts.append(context)
    contexts
    # 创建要保存的文件夹
    os.mkdir("./所有合同")
    for context in contexts:
        print(context)
        tpl = DocxTemplate(r"C:\Users\MSI2\A公司(1).docx")
        tpl.render(context)
        tpl.save("./所有合同/{}的劳动合同.docx".format(context["name"]))
    
    
    
    
    

    效果

    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • Excel】添加自动填充指定的内容 操作:文件 – 选项 – 高级 – 常规 – 编辑自定义列表 – 输入序列 – 添加 – 确定 – 确定 例子: 比如需要输入:周一,周二,周三,周四,周五,周六,周日 系统默认的...
  • 设置间隔指定行数填充颜色 隔五行填充一行 选中需要设置的表格 点击开始菜单栏里的条件格式,选择新建规则 选择规则类型:使用公式确定要设置格式的单元格 输入公式:=MOD(ROW(A1),5)=1,点击格式设置填充色 ...
  • 本程序分别演示了如何创建...如何给Excel文件中指定的Worksheet填充数据 using System; using System.Linq; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Sp
  • 数据——数据验证——设置——允许(A)——序列——来源 ...可以在同一个工作薄的同一个或者另一个工作表中设置指定内容(充当数据字典),然后点击选取就可以了。 转载于:https://www.cnblogs.c...
  • Excel中使用填充命令填充数据的方法

    千次阅读 2013-06-22 16:09:59
    会出现如图所示的子菜单,子菜单上有“向下填充”、“向右填充”、“向上填充”、“向左填充”以及“序列”等命令,excel选择不同的命令可以将内容填充至不同位置的单元格,如果选择“序列”命令,则以指定序列进行...
  • Excel文档指定位置填充的时候有一些特殊的需求,比如需要Excel文档指定的位置只能填入某一类型的数据或者某几个指定的数据。例如:有用户的需求是在Excel文件的指定单元格处插入一个日期类型的文本,所以这里只能输入...
  • 在工作中,我们经常同word、excel、ppt打交道,而excel用的应该...而今天我们来讲解一个比较简单的案例,使用openpyxl操作excel批量填充数据,并生成新的excel文件以及新的工作表,拒绝做重复的事情。 数据源:今天需要
  • EXCEL指定数字递增:如何用公式递增

    万次阅读 2017-08-29 09:41:24
    EXCEL指定数字递增:如何用公式递增呢 如果是想用公式得到1,2,3,4,...这样的顺序 方法一 比如在A1单元格写入1 在A2单元格写入公式 =A1+1 下拉填充公式   方法二 在单元格写入公式 =ROW(A1) 下拉...
  • Excel 中,下拉输入的数据时,如果序列比较少,下拉然后选择填充序列就行了。但如果让序列递增到 10000,再手动下拉就显得既耗时效率又低了,有没有什么方法可以直接下拉到指定序列呢 ?
  • 最近工作需要做一个excel和word文档的导出功能,在指定的模板上,去数据库查询填充数据其中,最后把填充好数据的一批word或者excel文件压缩成zip包,然后提供给用户下载 在此将整个操作流程做一下笔记 1.word文档模板...
  • Excel 填充到表格

    2007-10-25 09:17:56
     /// 将指定的电子表格,检索出来,填充到数据集 /// &lt;/summary&gt; /// &lt;param name="strFilePath"&gt;Excel 绝对路径&lt;/param&gt; /// &lt;param name="...
  • POI (XSSFWorkbook) excel填充图片 Java

    千次阅读 2019-09-12 18:13:57
    XSSFWorkbook xw = new XSSFWorkbook(new File("D:/test...//读取excel模板 xlsx格式 XSSFSheet sheet = xw.getSheetAt(0);//指定sheet ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); // ...
  • 在日常数据处理过程中,需要对缺失数据进行填充时,按一定逻辑规则进行处理,实现快速填充,规范数据源。此篇给大家带来多种填充数据的场景。 业务使用场景 对各种系统中导出的数据,很多时候存在数据缺失的情况...
  • 本例使用到的Excel文件为:职员信息登记表.xlsx,如下图所示: 图21-10 职员信息登记表 窗体设计如下图所示(注意:为了演示方便,已经填充了数据): 图21-11 窗体设计 在本例中还需要掌握的知识: 1、...
  • 那就是对着满屏幕“五颜六色”的数字,要求出每一行中指定颜色的单元格的数值之和。(如下图,因涉及数据敏感性,简易表示一下) 这个问题看似简单,但是如果不使用VBA真的有点无从下手的感觉(因为本小白不懂VBA...
  • 通过Python自动化办公把提取word中的表格,填充到到excel当中. 首先通过for循环提取word当中的表格的每个单元格的内容,然后指定excel,进行批量填充
  • [Excel]对自定义列表进行自动填充 1.对公司部门进行自定义填充 ##【需求】 一个公司中有多个部门,表格中未按顺序对各部门人员进行排序,希望更新“部门”列中按照用户自定义的部门顺序进行人员排序 ##【解决】 1.将...
  • number_times:表示指定文本重复显示的次数 1.星级填充 公式: =REPT("★",C6)&REPT("☆",5-C6) 2.柱状图填充 公式: =REPT("|",F6) *需设置字体为Playbill 3.柱状图填充 公式: =REPT("□",(1-B16)*100)&...
  • excel快速下拉函数到指定的位置

    千次阅读 2019-09-28 14:42:14
    excel中怎样快速下拉函数到指定的位置? 例如:设置B1=A1*2,然后把B列往下拉时,就分别得到B2+A2*2,B3=A3*2,且自动填充数值,可是,如果行数非常多,达到几万个,则要消耗很长时间,怎样能快速的拉到最后一行,...
  • 如何用python将文件写入指定excel单元格 1.需要填充的模板 文件名称:模板 所在sheet: Sheet2 具体填充位置:从A3开始 (故意避开了默认位置) 2.用于填充的数据源 3.填充需求:将李磊所在行数据填入模板-Sheet...
  • 超简单Python将指定数据插入到docx模板渲染并生成 最近有一个需求,制作劳动合同表,要从excel表格中将每个人的数据导入到docx劳动合同中,重复量很大,因此可以使用python高效解决。为了让模板内容不变动,这里使用...
  • excel表的数据情况如下:下面数据区域的左边和上边都是空,这会导致我们读取近pathon里时,结构不是我们要的,需要用到skiprow和usecols来控制我们想要读取的区域 整合: import pandas as pd from ...
  • 需求说明:某要素类的属性表,新增一个字段(ResultField,结果字段,文本类型),要填充的内容来自另外两个字段按指定格式拼接。其中一个字段是整型(本例中为OBJECTID字段),另一个为文本类型(本例中为TextField...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 383
精华内容 153
关键字:

excel指定填充