精华内容
下载资源
问答
  • java excel poi合并单元格
  • POI 合并单元格 及 注意事项
  • ava poi合并单元格后内容无法显示

    ava poi操作合并单元格后写入内容无法显示的原因是设置值的单元格不正确,并不是poi的bug。调试好多次成功。废话不多说,代码解释。

    假设我们合并第三行的1-5列并设置为a

    //获取行
    Row row = sheet.getRow(2);
    //不存在创建行
    if(null==row) sheet.createRow(2);
    //先创建显示cell,显示cell是合并单元格的第一个cell,这点非常重要
    Cell valueCell = row.createCell(0);
    valueCell.setValue("a");
    //创建其他被合并的单元格,示例,可以循环创建
    Cell mCell = row.createCell(1);
    mCell.setValue("");
    mCell = row.createCell(2);
    mCell.setValue("");
    mCell = row.createCell(3);
    mCell.setValue("");
     mCell = row.createCell(4);
    mCell.setValue("");
    
    //合并单元格
    sheet.addMergedRegion(new CellRangeAddress(2,2,0,4));
    
    
    展开全文
  • POI 合并单元格

    千次阅读 2017-09-29 10:48:38
    1. 合并单元格概述合并单元格CellRangeAddress就是将几个相邻的单元格合并为一个单元格,即使没有创建过行和单元格,也 可以创建合并单元格,因为单元格信息是单独存储的 /** * Creates new cell range. base 0 *...

    1. 合并单元格概述

    合并单元格CellRangeAddress就是将几个相邻的单元格合并为一个单元格,即使没有创建过行和单元格,也 可以创建合并单元格,因为单元格信息是单独存储的

        /**
         * Creates new cell range. base 0
         * 
         * @param firstRow Index of first row
         * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
         * @param firstCol Index of first column
         * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
         */
        public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

    这里写图片描述

    2. 创建合并单元格

    创建合并单元格很简单:

     1. 创建一个合并单元格region
     2. 将region添加到工作表Sheet中
    CellRangeAddress region  = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    CellRangeAddress region  = new CellRangeAddress("A1:E10");
    sheet.addMergedRegion(region)

    3. 合并单元格内容

    合并单元格的内容需要通过设置合并区域左上角单元格的内容确定,设置合并区域内其他单元格是不起效的
    上图中需要设置A1单元格内容才是设置合并的那远哥的内容

    Cell cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("合并单元格");

    4. 实例

    package hssf.sheet.cell;
    
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellUtil;
    import org.apache.poi.ss.util.RegionUtil;
    import org.apache.poi.ss.util.WorkbookUtil;
    
    /**
     * 合并单元格
     * 1.创建一个合并单元格
     * 2.设置合并单元格内容
     * 3.设置合并单元格边框样式
     * */
    public class ExportRegionCell {
        public static void main(String[] args) throws Exception {
            File file = new File("C:\\Users\\Administrator\\Desktop\\test.xls");
            if (file.exists()) {
                file.delete();
            }
            BufferedOutputStream out = null;
            try {
                out = new BufferedOutputStream(new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test.xls"));
                exportExcel(out);
            } finally {
                out.close();
            }
        }
    
        private static void exportExcel(BufferedOutputStream out) throws Exception {
            Workbook workbook = new HSSFWorkbook();
    //      Workbook workbook = new XSSFWorkbook();
            String safeSheetName = WorkbookUtil.createSafeSheetName("合并单元格");
            Sheet sheet = workbook.createSheet(safeSheetName);
    
            // 1.创建一个合并单元格
    //      CellRangeAddress region = new CellRangeAddress(0, 9, 0, 4);
            CellRangeAddress region = CellRangeAddress.valueOf("A1:E10");
            sheet.addMergedRegion(region);
    
            // 2.设置合并单元格内容
            Cell cell = sheet.createRow(0).createCell(0);
            cell.setCellValue("合并单元格");
    
            // 设置单元格内容水平垂直居中
            CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
            CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
    
            // 3.设置合并单元格边框
            setBorderStyle(sheet, region);
    
            workbook.write(out);
        }
    
        /**
         * 设置合并单元格边框 - 线条
         * */
        private static void setBorderStyle(Sheet sheet, CellRangeAddress region) {
            // 合并单元格左边框样式
            RegionUtil.setBorderLeft(BorderStyle.THICK, region, sheet);
            RegionUtil.setLeftBorderColor(IndexedColors.LIGHT_BLUE.getIndex(), region, sheet);
    
            // 合并单元格上边框样式
            RegionUtil.setBorderTop(BorderStyle.THICK, region, sheet);
            RegionUtil.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex(), region, sheet);
    
            // 合并单元格右边框样式
            RegionUtil.setBorderRight(BorderStyle.THICK, region, sheet);
            RegionUtil.setRightBorderColor(IndexedColors.LIGHT_BLUE.getIndex(), region, sheet);
    
            // 合并单元格下边框样式
            RegionUtil.setBorderBottom(BorderStyle.THICK, region, sheet);
            RegionUtil.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex(), region, sheet);
        }
    }

    截图:
    这里写图片描述

    展开全文
  • PoI合并单元格

    2019-01-11 10:07:18
    //合并行 region=new CellRangeAddress(2, list.size()+1, 0, 0); sheet.addMergedRegion(region); region=new CellRangeAddress(list.size()+2,2*list.size()+1 , 0, 0); sheet.addMergedRegion(region); ...
    public static void main(String[] args){
        String filePath="D:\\桌面\\sapmple.xls";//文件路径
        String name = "采购申请列表";
        String[] columnNames = {"采购理由","备注","类型","资产名称","型号"};
        PurchaseApplication purchaseApplication = new PurchaseApplication();
        List<PurchaseApplicationDetail> list = new ArrayList<>();
        PurchaseApplicationDetail purchaseApplicationDetails = new PurchaseApplicationDetail();
        purchaseApplicationDetails.setTypeName("测试1");
        purchaseApplicationDetails.setGoodsName("测试1");
        purchaseApplicationDetails.setSpecifications("测试1");
        list.add(purchaseApplicationDetails);
        PurchaseApplicationDetail purchaseApplicationDetails2 = new PurchaseApplicationDetail();
        purchaseApplicationDetails2.setTypeName("测试2");
        purchaseApplicationDetails2.setGoodsName("测试2");
        purchaseApplicationDetails2.setSpecifications("测试2");
        list.add(purchaseApplicationDetails2);
        PurchaseApplicationDetail purchaseApplicationDetails3 = new PurchaseApplicationDetail();
        purchaseApplicationDetails3.setTypeName("测试3");
        purchaseApplicationDetails3.setGoodsName("测试3");
        purchaseApplicationDetails3.setSpecifications("测试3");
        list.add(purchaseApplicationDetails3);
        purchaseApplication.setReasons("测试");
        purchaseApplication.setRemarks("测试");
        purchaseApplication.setPurchaseApplicationDetails(list);
        try {
            export(filePath,name,columnNames,purchaseApplication);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    
    
    public static  void export(String filePath, String name, String[] columnNames, PurchaseApplication purchaseApplication) throws IOException{
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
        HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
        HSSFCellStyle style=workbook.createCellStyle();
        HSSFRow row=sheet.createRow(0);
        HSSFCell cell=row.createCell(0);
        cell.setCellValue(name);
        Font f = workbook.createFont();
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);
        style.setFont(f);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFRow row1=sheet.createRow(1);
        for (int i = 0; i <columnNames.length ; i++) {
            row1.createCell(i).setCellValue(columnNames[i]);
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
            cell.setCellStyle(style);
        }
        HSSFRow row2=sheet.createRow(2);
        row2.createCell(0).setCellValue(purchaseApplication.getReasons());
        row2.createCell(1).setCellValue(purchaseApplication.getRemarks());
        List<PurchaseApplicationDetail> list = purchaseApplication.getPurchaseApplicationDetails();
        for(int i =0;i<list.size();i++){
            if(i !=0){
                row2=sheet.createRow(i+2);
            }
            row2.createCell(2).setCellValue(list.get(i).getTypeName());
            row2.createCell(3).setCellValue(list.get(i).getGoodsName());
            row2.createCell(4).setCellValue(list.get(i).getSpecifications());
        }
        CellRangeAddress region=new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(region);
        //合并行
        region=new CellRangeAddress(2, list.size()+1, 0, 0);
        sheet.addMergedRegion(region);
        region=new CellRangeAddress(list.size()+2,2*list.size()+1 , 0, 0);
        sheet.addMergedRegion(region);
        region=new CellRangeAddress(2, list.size()+1, 1, 1);
        sheet.addMergedRegion(region);
    
    
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");
    }
    public static void main(String[] args){
        String filePath="D:\\桌面\\sapmple.xls";//文件路径
        String name = "采购申请列表";
        String[] columnNames = {"采购理由","备注","类型","资产名称","型号"};
        PurchaseApplication purchaseApplication = new PurchaseApplication();
        List<PurchaseApplicationDetail> list = new ArrayList<>();
        PurchaseApplicationDetail purchaseApplicationDetails = new PurchaseApplicationDetail();
        purchaseApplicationDetails.setTypeName("测试1");
        purchaseApplicationDetails.setGoodsName("测试1");
        purchaseApplicationDetails.setSpecifications("测试1");
        list.add(purchaseApplicationDetails);
        PurchaseApplicationDetail purchaseApplicationDetails2 = new PurchaseApplicationDetail();
        purchaseApplicationDetails2.setTypeName("测试2");
        purchaseApplicationDetails2.setGoodsName("测试2");
        purchaseApplicationDetails2.setSpecifications("测试2");
        list.add(purchaseApplicationDetails2);
        PurchaseApplicationDetail purchaseApplicationDetails3 = new PurchaseApplicationDetail();
        purchaseApplicationDetails3.setTypeName("测试3");
        purchaseApplicationDetails3.setGoodsName("测试3");
        purchaseApplicationDetails3.setSpecifications("测试3");
        list.add(purchaseApplicationDetails3);
        purchaseApplication.setReasons("测试");
        purchaseApplication.setRemarks("测试");
        purchaseApplication.setPurchaseApplicationDetails(list);
        try {
            export(filePath,name,columnNames,purchaseApplication);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    
    
    public static  void export(String filePath, String name, String[] columnNames, PurchaseApplication purchaseApplication) throws IOException{
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
        HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
        HSSFCellStyle style=workbook.createCellStyle();
        HSSFRow row=sheet.createRow(0);
        HSSFCell cell=row.createCell(0);
        cell.setCellValue(name);
        Font f = workbook.createFont();
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);
        style.setFont(f);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFRow row1=sheet.createRow(1);
        for (int i = 0; i <columnNames.length ; i++) {
            row1.createCell(i).setCellValue(columnNames[i]);
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
            cell.setCellStyle(style);
        }
        HSSFRow row2=sheet.createRow(2);
        row2.createCell(0).setCellValue(purchaseApplication.getReasons());
        row2.createCell(1).setCellValue(purchaseApplication.getRemarks());
        List<PurchaseApplicationDetail> list = purchaseApplication.getPurchaseApplicationDetails();
        for(int i =0;i<list.size();i++){
            if(i !=0){
                row2=sheet.createRow(i+2);
            }
            row2.createCell(2).setCellValue(list.get(i).getTypeName());
            row2.createCell(3).setCellValue(list.get(i).getGoodsName());
            row2.createCell(4).setCellValue(list.get(i).getSpecifications());
        }
        CellRangeAddress region=new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(region);
        //合并行
        region=new CellRangeAddress(2, list.size()+1, 0, 0);
        sheet.addMergedRegion(region);
        region=new CellRangeAddress(list.size()+2,2*list.size()+1 , 0, 0);
        sheet.addMergedRegion(region);
        region=new CellRangeAddress(2, list.size()+1, 1, 1);
        sheet.addMergedRegion(region);
    
    
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");
    }
    展开全文
  • 单元格合并之后,同行有未合并单元格,前者边框会显示不全。 当把未合并单元格向下移动一行后,边框显示完全。 怎么解?求指点!
  • 它的意思是,在进行列宽自适应时,要不要考虑计算已经进行合并单元格里面的数据,不设置的话默认是false。 区别展示 不进行设置或设置为false: sheet.autoSizeColumn(i); 可以看到流程实例和发起时间这两列,...
    1. POI设置列宽自适应的方法
      public void autoSizeColumn(int column, boolean useMergedCells);
      
      可以看到除了第一个列标参数外,还有第二个useMergedCells的布尔值参数。它的意思是,在进行列宽自适应时,要不要考虑计算已经进行合并的单元格里面的数据,不设置的话默认是false。
    2. 区别展示
      不进行设置或设置为false:
      sheet.autoSizeColumn(i);
      在这里插入图片描述
      可以看到流程实例和发起时间这两列,由于进行了单元格合并,并没有根据里面的数据内容进行自适应,十分拥挤;
      设置为true:
      sheet.autoSizeColumn(i, true);在这里插入图片描述
      可以看到已经进行了列宽自适应。希望对你有所帮助!
    展开全文
  • POI合并单元格 package com.yk.ln.util; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi....
  • poi合并单元格后居中

    千次阅读 2020-04-16 10:12:49
    //设置style ICellStyle cellstyle = workbook.CreateCellStyle(); cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.Alignment = HorizontalAlignment....//合并操作 sheet.AddMergedRegion(ne...
  • 使用POI导出Excel文件 private XSSFWorkbook writeExcel(List<SupplyKcPlan> list) { if(CollectionUtils.isEmpty(list)){ return new XSSFWorkbook(); } XSSFWorkbook wb = new XSSFWorkbook(); ...
  • EXCEL POI 合并单元格解析 1、EXCEL2007的合并单元格的XML格式 <mergeCells count="2"> <mergeCell ref="A1:A2"/> <mergeCell ref="B3:B4"/> </mergeCells> mergeCells 里面包含了所有...
  • POI合并单元格,赋值

    千次阅读 2019-07-12 09:22:37
    Poi 地址:https://blog.csdn.net/qq_29870555/article/details/80493612 public void exportFive() { HSSFWorkbook workbook = new HSSFWorkbook(); /** ***************** 样式定义开始 ****************** */ /...
  • poi 合并单元格添加边框Excel spreadsheets can be hard to read at a glance, thanks to dense information and the grid formatting. Spending a little time on your spreadsheet to make it easier to read is a...
  • 我这里用的方法可能比较笨,就是合并后其间的每个单元格都设置同样的单元格样式,便可以解决这个问题了,如果大家有更简单更好的方法,可以告诉一下,一起学习。 //定义Excel文档对象 XSSFWorkbook xssfWorkbook =...
  • POI合并单元格后边框显示问题

    千次阅读 2019-12-03 17:57:50
    POI合并单元格后边框显示问题 最近在做导出Excel报表业务. 牵扯到一个复杂表头… 首先我分成3层来为表头数据装配样式. 但是两大行中因为牵扯到一个合并问题,没有附上边框, 之后也百度了前辈们总结的经验, 附上代码:...
  • Apache POI 合并单元格

    2019-02-14 11:22:55
    package ... import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.us...
  • //注意再合并单元格的时候, sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 3, 3)); //如果不能确定rowIndex 和 rowIndex + surveySearches.size()-1 是否相同,那么...
  • 所有单元格的样式都是带边框的,但是合并之后,图中的红框标识区域样式并没有 样式源码如下: public HSSFCellStyle getBorderCellStyle(HSSFWorkbook hWorkbook) { HSSFCellStyle cellStyle = hWorkbook....
  • java poi 合并单元格

    千次阅读 2017-06-02 16:23:55
    poi导出excel的,动态合并单元格
  • poi获取合并单元格

    2015-08-11 11:29:46
    poi获取合并单元格,Java 对excel 文档处理。
  • poi合并单元格的处理

    千次阅读 2018-07-23 14:31:41
    1、获取真实的单元格行数 2、判断是否有合并行 package util; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import org....
  • POI合并单元格

    2015-08-06 13:20:25
    合并单元格------------------------------ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));// 开始行,结束行,开始列,结束列 sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); ...
  • Poi合并单元格后居中显示,大家帮忙看下我写的有什么问题,为什么居中显示不了,谢谢啦
  • 但是如果你是通过POI 来进行合并单元格操作的,上面代为改为 row.createCell(2).setCellValue("222"); row.createCell(3).setCellValue("333"); row.createCell(4).setCellValue("444"); row.createCell(5)....
  • poi获取exccel有合并单元格的数据,存入List
  • 本文使用java-poi实现, 上下合并之后,居然出现了两个93.3分,点进去看又确实只有一个93.3,复制出来也是正常的 源码如下: CellRangeAddress range = new CellRangeAddress(rowIndex, toRowIndex, column...
  • * @Description: 创建日期行(需合并单元格) */ protected void createTableDateRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum, String operator) { // 合并单元格 CellRangeAddress dateRange ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,610
精华内容 3,044
关键字:

poi合并单元格