精华内容
下载资源
问答
  • 使用poi实现动态合并单元格导出excel

    千次阅读 2019-08-20 13:17:21
    使用poi进行动态合并单元格, 参考博客:https://blog.csdn.net/yuan890720/article/details/52368366 根据卖家公司字段匹配导出结果样式: 代码如下 package test; import org.apache.log4j.Logger; import org....

    使用poi进行动态合并单元格,

    参考博客:https://blog.csdn.net/yuan890720/article/details/52368366

    根据卖家公司字段匹配导出结果样式:

    在这里插入图片描述

    代码如下

    package test;

    import org.apache.log4j.Logger;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFCell;

    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;

    /**

    • Created by LH on 2019/8/20 9:57
      */
      public class Test2 {

      // 日志
      private static Logger log = Logger.getLogger(Test2.class
      .getName());

      /**

      • 判断文件是否存在
      • @param path
      • @return
        */
        public static boolean checkFile(String path) {
        File file = new File(path);
        if (file.exists()) {
        return true;
        } else {
        return false;
        }
        }

      /**

      • 建立订单查询ExcelFile

      • @param excelPath

      • @return
        */
        public static boolean createExcelFile(String excelPath,List info) {

        /**

        • 新增列:买家公司交易金额
        • NorthLee

        */
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(“实际成交订单”);

        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);//字号
        font.setFontName(“宋体”);
        font.setBold(true); //粗体

        style.setFont(font);
        style.setWrapText(true);

        // 标题行
        HSSFRow row = sheet.createRow(0);
        // 行高
        row.setHeight((short) (20*20));
        // 列数
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellStyle(style);
        cell0.setCellValue(“订单号”);

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellStyle(style);
        cell1.setCellValue(“货物标题”);

        HSSFCell cell2 = row.createCell(2);
        cell2.setCellStyle(style);
        cell2.setCellValue(“支付价格”);

        HSSFCell cell3 = row.createCell(3);
        cell3.setCellStyle(style);
        cell3.setCellValue(“批付价格”);

        HSSFCell cell4 = row.createCell(4);
        cell4.setCellStyle(style);
        cell4.setCellValue(“卖家真实姓名”);

        HSSFCell cell5 = row.createCell(5);
        cell5.setCellStyle(style);
        cell5.setCellValue(“卖家公司”);

        HSSFCell cell6 = row.createCell(6);
        cell6.setCellStyle(style);
        cell6.setCellValue(“买家公司交易总额”);

        HSSFCell cell7 = row.createCell(7);
        cell7.setCellStyle(style);
        cell7.setCellValue(“买家真实姓名”);

        HSSFCell cell8 = row.createCell(8);
        cell8.setCellStyle(style);
        cell8.setCellValue(“买家公司”);

        HSSFCell cell9 = row.createCell(9);
        cell9.setCellStyle(style);
        cell9.setCellValue(“货运地址”);

        HSSFCell cell10 = row.createCell(10);
        cell10.setCellStyle(style);
        cell10.setCellValue(“买家联系方式1”);

        HSSFCell cell11 = row.createCell(11);
        cell11.setCellStyle(style);
        cell11.setCellValue(“买家联系方式2”);

        HSSFCell cell12 = row.createCell(12);
        cell12.setCellStyle(style);
        cell12.setCellValue(“货运方式”);

        HSSFCell cell13 = row.createCell(13);
        cell13.setCellStyle(style);
        cell13.setCellValue(“货运联系人”);

        HSSFCell cell14 = row.createCell(14);
        cell14.setCellStyle(style);
        cell14.setCellValue(“货运车牌号”);

        HSSFCell cell15 = row.createCell(15);
        cell15.setCellStyle(style);
        cell15.setCellValue(“货运联系方式”);

        HSSFCell cell16 = row.createCell(16);
        cell16.setCellStyle(style);
        cell16.setCellValue(“发货时间”);

        // 内容行
        if(info != null && info.size() > 0){
        HSSFCellStyle styleList = workbook.createCellStyle();
        HSSFFont fontList = workbook.createFont();
        fontList.setFontHeightInPoints((short) 12);//字号
        fontList.setFontName(“宋体”);
        font.setBold(false);//粗体

         styleList.setFont(font);
        
         /**
          * m,a在插入买家公司交易总额的数据的时候用
          *    m是统计同一个公司出现相同的次数
          *     a是相同公司出现的交易额的累加
          */
        
         int m=1;
         Double a=0.0;//不包括合并单元格的第一行的数据
         for (int i = 0; i < info.size(); i++) {
             HSSFRow rowList = sheet.createRow(i+1);
             // 行高
             rowList.setHeight((short) (20*20));
             // 列数
             HSSFCell cell0List = rowList.createCell(0);
             cell0List.setCellStyle(styleList);
             cell0List.setCellValue(info.get(i).getOutOrderNo());
        
             HSSFCell cell1List = rowList.createCell(1);
             cell1List.setCellStyle(styleList);
             cell1List.setCellValue(info.get(i).getTitle());
        
             HSSFCell cell2List = rowList.createCell(2);
             cell2List.setCellStyle(styleList);
             cell2List.setCellValue(Double.parseDouble(info.get(i).getPay()));
        
             HSSFCell cell3List = rowList.createCell(3);
             cell3List.setCellStyle(styleList);
             cell3List.setCellValue(Double.parseDouble(info.get(i).getTransfer()));
        
             HSSFCell cell4List = rowList.createCell(4);
             cell4List.setCellStyle(styleList);
             cell4List.setCellValue(info.get(i).getSaller());
        
             HSSFCell cell5List = rowList.createCell(5);
             cell5List.setCellStyle(styleList);
             cell5List.setCellValue(info.get(i).getSalCompany());
        

    // 插入新增列数据
    // 说明:由于已经合并的单元格不能进行再次合并,所以先进行判断和数据统计最后进行单元格的合并
    HSSFCell cell6List = rowList.createCell(6);
    cell6List.setCellStyle(styleList);
    CellRangeAddress region;//所要合并单元格的地址值
    CellRangeAddress region0;//所要合并单元格的地址值
    CellRangeAddress region1;//所要合并单元格的地址值
    if(i==0){
    cell6List.setCellValue(Double.parseDouble(info.get(0).getPay()));

                }else{
                    // 判断本行是否是最后一行  并且 本行的公司名是否和上个公司相同,如果相同取得支付的值,并累加
                    if((i!=info.size()-1)&&(info.get(i).getBuyCompany().equals(info.get(i-1).getBuyCompany()))){
                        a += Double.parseDouble(info.get(i).getPay());
                        m++;
                        // 判断本行是否是最后一行  并且 本行的公司名是否和上个公司相同,如果相同取得支付的值,并累加,再合并单元格并插入数据
                    }else if((i==info.size()-1)&&(info.get(i).getBuyCompany().equals(info.get(i-1).getBuyCompany()))){
    
                        Double b=Double.parseDouble(info.get(i-m).getPay());//所合并单元格的第一行数据
                        Double c=Double.parseDouble(info.get(i).getPay());//最后一行的数据
                         region0=new CellRangeAddress(i-m+1, i+1, 0, 0);//h合并单元格
                         region1=new CellRangeAddress(i-m+1, i+1, 1, 1);//h合并单元格
                        region=new CellRangeAddress(i-m+1, i+1, 6, 6);//h合并单元格
                        sheet.addMergedRegion(region);
                        sheet.addMergedRegion(region0);
                        sheet.addMergedRegion(region1);
                        //取得合并的单元格并把数据插入
                        sheet.getRow(i-m+1).getCell(6).setCellValue(a+b+c);
                        sheet.getRow(i-m+1).getCell(0).setCellValue(info.get(i-m).getOutOrderNo());
                        sheet.getRow(i-m+1).getCell(1).setCellValue(info.get(i-m).getTitle());
                    }else{
                        //判断上一行和上两行的值是否相同,如果相同就就将上他们合并,并插入值
                        if(i>1&&info.get(i-2).getBuyCompany().equals(info.get(i-1).getBuyCompany())){
                            Double b=Double.parseDouble(info.get(i-m).getPay());
                            region0=new CellRangeAddress(i-m+1, i, 0, 0);
                            region1=new CellRangeAddress(i-m+1, i, 1, 1);
                            region=new CellRangeAddress(i-m+1, i, 6, 6);
                            sheet.addMergedRegion(region);
                            sheet.addMergedRegion(region0);
                            sheet.addMergedRegion(region1);
                            sheet.getRow(i-m+1).getCell(6).setCellValue(a+b);
                            sheet.getRow(i-m+1).getCell(0).setCellValue(info.get(i-m).getOutOrderNo());
                            sheet.getRow(i-m+1).getCell(1).setCellValue(info.get(i-m).getTitle());
    
                            cell0List.setCellValue(info.get(i).getOutOrderNo());
                            cell1List.setCellValue(info.get(i).getTitle());
                            cell6List.setCellValue(Double.parseDouble(info.get(i).getPay()));
                            m=1;
                            a=0.0;
                        }else{
                            //如果不相同就直接填入值
    
                            cell6List.setCellValue(Double.parseDouble(info.get(i).getPay()));
                            cell0List.setCellValue( info.get(i).getOutOrderNo());
                            cell1List.setCellValue( info.get(i).getTitle());
                            m=1;
                        }
                    }
                }
    
                HSSFCell cell7List = rowList.createCell(7);
                cell7List.setCellStyle(styleList);
                cell7List.setCellValue(info.get(i).getBuyer());
    
                HSSFCell cell8List = rowList.createCell(8);
                cell8List.setCellStyle(styleList);
                cell8List.setCellValue(info.get(i).getBuyCompany());
    
                HSSFCell cell9List = rowList.createCell(9);
                cell9List.setCellStyle(styleList);
                cell9List.setCellValue(info.get(i).getAddress());
    
                HSSFCell cell10List = rowList.createCell(10);
                cell10List.setCellStyle(styleList);
                cell10List.setCellValue(info.get(i).getContact1());
    
                HSSFCell cell11List = rowList.createCell(11);
                cell11List.setCellStyle(styleList);
                cell11List.setCellValue(info.get(i).getContact2());
    
                HSSFCell cell12List = rowList.createCell(12);
                cell12List.setCellStyle(styleList);
                cell12List.setCellValue(info.get(i).getDeliveryMethod());
    
                HSSFCell cell13List = rowList.createCell(13);
                cell13List.setCellStyle(styleList);
                cell13List.setCellValue(info.get(i).getDeliveryPeople());
    
                HSSFCell cell14List = rowList.createCell(14);
                cell14List.setCellStyle(styleList);
                cell14List.setCellValue(info.get(i).getDeliveryCar());
    
                HSSFCell cell15List = rowList.createCell(15);
                cell15List.setCellStyle(styleList);
                cell15List.setCellValue(info.get(i).getDeliveryPhone());
    
                HSSFCell cell16List = rowList.createCell(16);
                cell16List.setCellStyle(styleList);
                cell16List.setCellValue(info.get(i).getDeliveryTime());
            }
            HSSFRow rowSum = sheet.createRow(info.size()+1);
            HSSFCell cellSum = rowSum.createCell(2);
            cellSum.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cellSum.setCellFormula("SUM(C2:C"+(info.size()+1)+")" );
        }
        // 列宽
    
    
        sheet.setColumnWidth(0, 15*280);
        sheet.setColumnWidth(1, 40*280);
        sheet.setColumnWidth(2, 15*280);
        sheet.setColumnWidth(3, 15*280);
        sheet.setColumnWidth(4, 15*280);
        sheet.setColumnWidth(5, 50*280);
        sheet.setColumnWidth(6, 25*280);
        sheet.setColumnWidth(7, 15*280);
        sheet.setColumnWidth(8, 50*280);
        sheet.setColumnWidth(9, 50*280);
        sheet.setColumnWidth(10, 15*280);
        sheet.setColumnWidth(11, 15*280);
        sheet.setColumnWidth(12, 15*280);
        sheet.setColumnWidth(13, 15*280);
        sheet.setColumnWidth(14, 15*280);
        sheet.setColumnWidth(15, 15*280);
        sheet.setColumnWidth(16, 15*280);
    
        return outputHSSFWorkbook(workbook, excelPath);
    }
    
    /**
     * 创建Excel文件
     *
     * @param wb
     * @param excelPath
     * @return
     */
    private static boolean outputHSSFWorkbook(HSSFWorkbook wb, String excelPath) {
        int index = excelPath.lastIndexOf(File.separator);
        String path = excelPath.substring(0, index);
        File file = new File(path);
        if (!file.exists()) {
            file.mkdirs();
        }
        FileOutputStream fOut = null;
        try {
            fOut = new FileOutputStream(excelPath);
            wb.write(fOut);
            fOut.flush();
            log.info("文件正在生成......");
            return true;
        } catch (FileNotFoundException e) {
            log.error("未找到要写的文件");
        } catch (IOException e) {
            log.error("写文件是IO异常");
        } finally {
            try {
                if (fOut != null)
                    fOut.close();
            } catch (Exception e) {
                log.error("关闭文件流错误");
            }
        }
        return false;
    }
    
    private Test2() {
    }
    
    
    
    
    
    public static void main(String[] args) {
        List<OrderInfoBean> info = new ArrayList<OrderInfoBean>();
        for (int i = 0; i < 10; i++) {
            OrderInfoBean order = new OrderInfoBean();
            order.setOutOrderNo("123456");
            order.setTitle("塑料");
            order.setPay("1000.120");
            order.setTransfer("10.010");
            order.setSaller("zhangsan");
            order.setSalCompany("张三的公司");
            order.setBuyer("李四");
            order.setBuyCompany("李四的公司");
            order.setAddress("beijing");
            order.setContact1("13110011002");
            order.setContact2("15610011002");
            order.setDeliveryPeople("王五");
            order.setDeliveryPhone("12345698710");
            order.setDeliveryCar("京A00001");
            order.setDeliveryTime("2016-8-18");
            order.setNumber(10);
            info.add(order);
    
        }
        for (int i = 0; i < 5; i++) {
            OrderInfoBean order1 = new OrderInfoBean();
            order1.setOutOrderNo("123456123");
            order1.setTitle("塑料");
            order1.setPay("1000.120");
            order1.setTransfer("10.010");
            order1.setSaller("zhangsan");
            order1.setSalCompany("张三的公司111111");
            order1.setBuyer("李四");
            order1.setBuyCompany("李四的公司111111");
            order1.setAddress("beijing");
            order1.setContact1("13110011002");
            order1.setContact2("15610011002");
            order1.setDeliveryPeople("王五");
            order1.setDeliveryPhone("12345698710");
            order1.setDeliveryCar("京A00001");
            order1.setDeliveryTime("2016-8-18");
            order1.setNumber(5);
            info.add(order1);
        }
    
        OrderInfoBean order1 = new OrderInfoBean();
        order1.setOutOrderNo("123456123");
        order1.setTitle("塑料");
        order1.setPay("1000.120");
        order1.setTransfer("10.010");
        order1.setSaller("zhangsan");
        order1.setSalCompany("张三的公司1111111111122");
        order1.setBuyer("李四");
        order1.setBuyCompany("李四的公司1111111111122");
        order1.setAddress("beijing");
        order1.setContact1("13110011002");
        order1.setContact2("15610011002");
        order1.setDeliveryPeople("王五");
        order1.setDeliveryPhone("12345698710");
        order1.setDeliveryCar("京A00001");
        order1.setDeliveryTime("2016-8-18");
        order1.setNumber(5);
        info.add(order1);
    
        Test2.createExcelFile("D:\\test111.xls", info);
        System.out.println("OK!");
    }
    
    static class OrderInfoBean {
        private String outOrderNo;
        private String title;
        private String pay;
        private String transfer;
        private String saller;
        private String salCompany;
        private String buyer;
        private String buyCompany;
        private String address;
        private String contact1;
        private String contact2;
        private String deliveryPeople;
        private String deliveryPhone;
        private String deliveryCar;
        private String deliveryTime;
        private String deliveryMethod;
        private Integer number;
    
        public String getOutOrderNo() {
            return outOrderNo;
        }
    
        public void setOutOrderNo(String outOrderNo) {
            this.outOrderNo = outOrderNo;
        }
    
        public String getTitle() {
            return title;
        }
    
        public void setTitle(String title) {
            this.title = title;
        }
    
        public String getPay() {
            return pay;
        }
    
        public void setPay(String pay) {
            this.pay = pay;
        }
    
        public String getTransfer() {
            return transfer;
        }
    
        public void setTransfer(String transfer) {
            this.transfer = transfer;
        }
    
        public String getSaller() {
            return saller;
        }
    
        public void setSaller(String saller) {
            this.saller = saller;
        }
    
        public String getSalCompany() {
            return salCompany;
        }
    
        public void setSalCompany(String salCompany) {
            this.salCompany = salCompany;
        }
    
        public String getBuyer() {
            return buyer;
        }
    
        public void setBuyer(String buyer) {
            this.buyer = buyer;
        }
    
        public String getBuyCompany() {
            return buyCompany;
        }
    
        public void setBuyCompany(String buyCompany) {
            this.buyCompany = buyCompany;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        public String getContact1() {
            return contact1;
        }
    
        public void setContact1(String contact1) {
            this.contact1 = contact1;
        }
    
        public String getContact2() {
            return contact2;
        }
    
        public void setContact2(String contact2) {
            this.contact2 = contact2;
        }
    
        public String getDeliveryPeople() {
            return deliveryPeople;
        }
    
        public void setDeliveryPeople(String deliveryPeople) {
            this.deliveryPeople = deliveryPeople;
        }
    
        public String getDeliveryPhone() {
            return deliveryPhone;
        }
    
        public void setDeliveryPhone(String deliveryPhone) {
            this.deliveryPhone = deliveryPhone;
        }
    
        public String getDeliveryMethod() {
            return deliveryMethod;
        }
    
        public void setDeliveryMethod(String deliveryMethod) {
            this.deliveryMethod = deliveryMethod;
        }
    
        public String getDeliveryCar() {
            return deliveryCar;
        }
    
        public void setDeliveryCar(String deliveryCar) {
            this.deliveryCar = deliveryCar;
        }
    
        public String getDeliveryTime() {
            return deliveryTime;
        }
    
        public void setDeliveryTime(String deliveryTime) {
            this.deliveryTime = deliveryTime;
        }
    
        public Integer getNumber() {
            return number;
        }
    
        public void setNumber(Integer number) {
            this.number = number;
        }
    }
    

    }

    展开全文
  • java代码,实现从数据库中查询出数据,然后通过poi实现合并单元格生成excel
  • EasyExcel导出动态合并单元格策略

    千次阅读 2020-12-22 15:49:06
    EasyExcel导出动态合并单元格策略 1.导入依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </...

    EasyExcel导出动态合并单元格策略

    1.导入依赖

       <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
    

    2.合并单元格工具类

    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    import java.util.List;
    
    public class ExcelMergeUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
    
    public ExcelMergeUtil() {
    }
    
    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }
    
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    
    }
    
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    
    }
    
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    
    }
    
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
    
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }
    
    
    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意:因为我是按照订单号确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并
        Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并,则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
    

    }

    3、导出方法(公司导出的工具类)

    public static ExcelWriter getExcelWriterMerge(HttpServletResponse response, String excelName,int mergeRowIndex,int[] mergeColumeIndex) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
    
        ExcelWriter build = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex)).build();
    
        return build;
    }
    

    4、业务代码

       try {
                //需要合并的列
                int[] mergeColumeIndex = {0,4,5,6,7,8,9,10};
                // 从那一行开始合并
                int mergeRowIndex = 1;
                //先执行合并策略
                excelWriter = ExcelUtil.getExcelWriterMerge(response, fileName, mergeRowIndex, mergeColumeIndex);
                //业务代码
                for (int i = 0 ; i < totalPage;i++){
                    selectOrderDto.setPageNum(i);
                    selectOrderDto.setPageSize(10000);
                    String sheetName = "sheet"+i;
                    PageInfo<ShowOrderByKitchen> page = selectOrderByKitchenId(selectOrderDto);
                    List<ShowOrderByKitchen> list = page.getList();
                    if (!list.isEmpty()){
                    //进行写入操作
                        WriteSheet sheetWriter = EasyExcel.writerSheet(i,sheetName).head(ShowOrderByKitchen.class).build();
                        excelWriter.write(list,sheetWriter);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                log.error("导出订单失败,{}",e);
            }finally {
                // 千万别忘记finish 会帮忙关闭流
                if (excelWriter != null) {
                    excelWriter.finish();
                }
            }
        }
    

    5、实体

    public class ShowOrderByKitchen implements Serializable {
    
    @ExcelProperty(value = "编码",index = 0)
    private String orderId;
    
    @ExcelProperty(value = "名称",index = 1)
    private String dishName;
    
    @ExcelProperty(value = "价格",index = 2)
    private BigDecimal dishPrice;
    
    @ExcelProperty(value = "数量",index = 3)
    private Integer dishNumber;
    

    }

    6、测试结果

    嗯呐
    这是根据订单号进行合并,工具类里进行两次判断,合并后就会呈现正确的格式,建议根据哪一列数据合并,就将这列数据放在excel的第一列,否则会出现空指针异常。

    展开全文
  • 根据业务需求会有导出中需要合并的功能,但是当根据数据合并时又不能使用注解来进行合并,只能自定义合并,根据官网中介绍可以使用第二种方式,官网地址https://www.yuque.com/easyexcel/doc/write#cac25459 ...

    问题

    根据业务需求会有导出中需要合并的功能,但是当根据数据合并时又不能使用注解来进行合并,只能自定义合并,根据官网中介绍可以使用第二种方式,官网地址https://www.yuque.com/easyexcel/doc/write#cac25459

    导出的格式:

     

    解决思路

    实际上是使用此类中重写 afterCellDispose() 方法;

    RowMergeStrategy类:

    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.util.List;
    import java.util.Map;
    
    public class RowMergeStrategy extends AbstractCellWriteHandler {
    
        private Map<String, List<MergeRowBean>> strategyMap;
        private Sheet sheet;
    
        public RowMergeStrategy(Map<String, List<MergeRowBean>> strategyMap) {
            this.strategyMap = strategyMap;
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            if (isHead.booleanValue()) {
                return;
            }
            merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
        }
    
        protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
            this.sheet = sheet;
            if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
                /**
                 * 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的,
                 * 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3,
                 * 但此时A2,A3已经是合并的单元格了
                 */
                for (Map.Entry<String, List<MergeRowBean>> entry : strategyMap.entrySet()) {
                    Integer columnIndex = Integer.valueOf(entry.getKey());
                    entry.getValue().forEach(rowRange -> {
                        //添加一个合并请求
                        sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getFirstRow(),
                                rowRange.getLastRow(), columnIndex, columnIndex));
                    });
                }
            }
        }
    }
    MergeRowBean类:
    
    public class MergeRowBean {
        /**
         * 起始行
         */
        private int firstRow;
        /**
         * 结束行
         */
        private int lastRow;
    
    
    
        public MergeRowBean(int firstRow, int lastRow) {
            this.firstRow = firstRow;
            this.lastRow = lastRow;
        }
    
        public int getFirstRow() {
            return firstRow;
        }
    
        public void setFirstRow(int firstRow) {
            this.firstRow = firstRow;
        }
    
        public int getLastRow() {
            return lastRow;
        }
    
        public void setLastRow(int lastRow) {
            this.lastRow = lastRow;
        }
    }

     ExcelUtil类:

    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelUtil {
    
        /**
         * 添加
         * @param excelDtoList 要导出的集合
         * @return
         */
        public static Map<String, List<MergeRowBean>> addMerStrategy(List<ThirdTypeTableExcelResponseBean> excelDtoList) {
            Map<String, List<MergeRowBean>> strategyMap = new HashMap<>();
    
            ThirdTypeTableExcelResponseBean preExcelDto = null;
            //行坐标
            int startRow = 0;
            for (int i = 0; i < excelDtoList.size(); i++) {
                ThirdTypeTableExcelResponseBean currDto = excelDtoList.get(i);
                if (preExcelDto != null) {
                    if (currDto.getFirstTypeId() != null) {
                        //当firstTypeId相同时
                        if (currDto.getFirstTypeId().equals(preExcelDto.getFirstTypeId())) {
                            fillStrategyMap(strategyMap, "0", i + startRow);
                        }
                        //当secondTypeId相同时
                        if (currDto.getSecondTypeId().equals(preExcelDto.getSecondTypeId())) {
                            fillStrategyMap(strategyMap, "1", i + startRow);
                        }
                    }
                }
                preExcelDto = currDto;
            }
    
            return strategyMap;
        }
    
        /**
         * 合并
         *
         * @param strategyMap Map
         * @param key         列
         * @param index       行坐标
         */
        private static void fillStrategyMap(Map<String, List<MergeRowBean>> strategyMap, String key, int index) {
            List<MergeRowBean> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
            boolean flag = false;
            for (MergeRowBean dto : rowRangeDtoList) {
                //分段list中是否有end索引是上一行索引的,如果有,则索引+1
                if (dto.getLastRow() == index) {
                    dto.setLastRow(index + 1);
                    flag = true;
                }
            }
            //如果没有,则新增分段
            if (!flag) {
                rowRangeDtoList.add(new MergeRowBean(index, index + 1));
            }
            strategyMap.put(key, rowRangeDtoList);
        }
    
    }

    代码中可以看到思路为比较上一个类和当前类中同属性是否相同,如果相同则合并

    导出代码:

    //要导出的集合
    List<ThirdTypeTableExcelResponseBean> thirdTypeTableExcelResponseBeans = BeanUtil.listObjToListObj(thirdTypeListByDate, ThirdTypeTableExcelResponseBean.class);
    //设置表名
            StringBuilder title = new StringBuilder();
            title.append("xxx")
                    .append(statisticsQueryRequestBean.getStartTime())
                    .append("至")
                    .append(statisticsQueryRequestBean.getEndTime())
                    .append(".xlsx");
            try {
                String encodeFileName = URLEncoder.encode(title.toString(), "utf-8");
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                response.setHeader("Content-disposition", "attachment;filename=" + encodeFileName + "");
                response.setContentType("application/octet-stream;charset=utf-8");
                response.setHeader("Access-Control-Expose-Headers", title.toString());
                response.setHeader("fileName", encodeFileName);
                //设置合并规则
                Map<String, List<MergeRowBean>> stringListMap = ExcelUtil.addMerStrategy(thirdTypeTableExcelResponseBeans);
                ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream(), xxx.class).registerWriteHandler(new RowMergeStrategy(stringListMap)).build();
                /* 这里注意 如果同一个sheet只要创建一次 */
                WriteSheet writeSheet = EasyExcelFactory.writerSheet("xxx").build();
                excelWriter.write(thirdTypeTableExcelResponseBeans, writeSheet);
                excelWriter.finish();
            } catch (Exception e) {
                log.error("导出失败 {}", e.getMessage());
                response.reset();
                response.setContentType(MediaType.APPLICATION_JSON_VALUE);
                response.setCharacterEncoding(StandardCharsets.UTF_8.name());
                Map<String, String> map = new HashMap<>(8);
                map.put("status", "failure");
                map.put("message", "导出失败" + e.getMessage());
                response.getWriter().println(JSON.toJSONString(map));
            }

    展开全文
  • poi-合并单元格导出

    2018-07-23 14:39:27
    //创建第一行 HSSFRow t=sheet.createRow(0); //合并单元格 (起始行 结尾行 起始列 结尾列) sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));  
    //创建第一行 
    HSSFRow t=sheet.createRow(0);
    //合并单元格 (起始行 结尾行 起始列 结尾列)
    sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));    

     

    展开全文
  • JAVA excel合并单元格原生poi 合并后的效果 直接上代码 (该方法为如果指定行的单元格里面的值一致则进行合并,直接粘贴使用即可) * * @param sheet * @param colIdx 合并的列 * @param startRow 起始行 * @...
  • 最近工作遇到了导出execl画表格,记录一下,省的下次再忘了,用的第三方的NPOI, 其中涉及到动态合并单元格,其中你想要动态合并的那列的数据,最好查询数据的时候排序一下,不然还得要处理跨行合并的问题,那样比较...
  • 参数 0:合并起始行 1:合并终止行 2:合并起始列 3:合并终止列 CellRangeAddress region = new CellRangeAddress(0,1,2,3); sheet.addMergedRegion(region);
  • poi导出单元格的同时合并相同内容 周一刚写了个poi打出图片的功能,然后看我刚写完这一个模块,就把其他页面导出表格的也交给我了。 页面需求就是这样 因为刚开始接触poi的我并不熟悉,所以一开始也是百度了很多...
  • Java 树形 数据 合并单元格导出Excel

    千次阅读 2015-12-11 01:29:40
    场景: 数据库 表 是 父子关系 的树形 数据 ,需要导出Excel 模板
  • } } } //接口调用示例 @ApiOperation("物流列表导出") @PostMapping("/on/line/export") public AccessResult exportOrderOnLineListDTO(HttpServletResponse response, @RequestBody OrderOnLineExcelQueryDTO ...
  • IReport动态合并单元格场景描述开发环境实现步骤合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants...
  • 数据集合导出到excel模板(未合并单元格合并单元格
  • 网上找的某个大佬的,忘了大佬地址了,就不附链接了。... * @param mergeIndex 合并单元格的列(0,1,2)代表前三列需要行合并。必须要传值,也必须从0开始传。 */ public static String createE
  • 开发中,对于导出一个excel表格这样的功能很常见,这里谈谈我所知道的相关知识 二 需求 导出某个套餐所关联的所有项目的一个Excel表格 三 HSSFWorkbook //创建一个Excel文件 HSSFWorkbookworkbook = new ...
  • 有一个需求是动态合并列,参考文章做了下调整,代码如下。 import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.skyworth.spip.bean.*; import com.skyworth.spip.common.*; ...
  • //合并单元格,第一个参数:要合并 的单元格最左上角的列号,第二个参数:要合并的单元格最左上角的行号,第三个参数:要合并的 单元格最右角的列号,第四个参数:要合并的单元格最右下角的行号 } else { sheet....
  • [img=https://img-bbs.csdn.net/upload/201307/23/1374565753_237788.png][/img]怎样在程序里面动态合并成这样,给个提示也行,谢了 [img=https://img-bbs.csdn.net/upload/201307/23/1374565836_632036.png][/...
  • 导出EXCEL工具类:(可导出合并单元格) 本博客参照修改:原文出处 package com.apply.controller.util; import com.github.pagehelper.util.StringUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; ...
  • 需求:导出机构的统计数据到表格,需要根据数据,将同一机构的机构名称列合并单元格。类似生成下面这样的表格 地方 private void writeExcel(XSSFSheet sheet, List<Export> list) { int end=0; int ...
  • //获取姓名 //创建单元格设置样式 ,流程就是创建单元格 创建样式 再设置样式 Sheet createSheet = workbook.createSheet(); HSSFCellStyle CellStyle = workbook....
  • // 调用合并单元格工具类 ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex,mergeColumeIndex); // 这里需要设置不关闭流 WriteCellStyle ...
  • antd table 表格动态合并单元格

    千次阅读 2020-04-09 11:09:56
    Ant Design of Vue表格动态合并单元格需求版本实现 需求 最近在做一个功能,需要在表格内展示数据,动态合并值相同单元格,大概是下图的样子 百度的解决方案基本都是比较上下数据是否一致来决定是否合并,当遇到...
  • 这种格式 公司和年份两列是要有动态合并功能 并且年份合并的时候不能跨公司 有没有大神帮帮忙... ``` HSSFWorkbook workbook = new HSSFWorkbook(); String[] columnNames = new String[]{"编号","公司", "年份",...
  • JSP动态合并单元格

    千次阅读 2016-12-08 10:54:05
    <c:set var="rowspanCount" value="0"></c:set>记录合并列数 --%> <c:set var="tempFrist" value="0"></c:set>记录合并开始位置 --%> <c:set var="tempEnd" value="-1"></c:set>记录合并结束位置 --%> ${list...
  • 之前在网上找的方法都得一个表格一个表格的去画,每次写一个导出都要花费很多时间。 最近又要做合并行,防止以后再去写那些重复代码, 于是我就写了个能满足大部分合并单元格导出需求的方法。
  • POI 动态合并单元格

    千次阅读 2017-01-09 17:57:26
    /***** 查询结果 根据机构ID排序的 ... * 报表导出 * * @throws Exception */ public void queryExcelActionInfo(OutputStream out, Map parameters) throws Exception { List queryActionInfo = actionC

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,446
精华内容 6,178
关键字:

动态合并单元格导出