精华内容
下载资源
问答
  • poi获取合并单元格

    2015-08-11 11:29:46
    poi获取合并单元格,Java excel 文档处理
  • java excel poi合并单元格
  • Java操纵POI解析Excel表,根据表头将内容储存。通过实体类set、get方便其它方法引用
  • poi合并单元格,poi合并单元格并设置内容居中,Java使用POI操作Excel合并单元格合并单元格的方法:指定 4 个参数,起始行,结束行,起始列,结束列。然后这个区域将被合并。CellRangeAddress region = new ...

    8b1e08e1cad6eccf712a01716a1e1cb3.png

    poi合并单元格,poi合并单元格并设置内容居中,Java使用POI操作Excel合并单元格

    合并单元格的方法:

    指定 4 个参数,起始行,结束行,起始列,结束列。然后这个区域将被合并。

    CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);

    sheet.addMergedRegion(region);

    合并的简单示例:

    public class TestExcel {

    public static void main(String[] args) throws IOException {

    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFCellStyle style = workbook.createCellStyle();

    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    HSSFSheet sheet = workbook.createSheet("sheet");

    HSSFRow row0 = sheet.createRow(0);

    HSSFCell cell_00 = row0.createCell(0);

    cell_00.setCellStyle(style);

    cell_00.setCellValue("日期");

    HSSFCell cell_01 = row0.createCell(1);

    cell_01.setCellStyle(style);

    cell_01.setCellValue("午别");

    HSSFRow row1 = sheet.createRow(1);

    HSSFCell cell_10 = row1.createCell(0);

    cell_10.setCellStyle(style);

    cell_10.setCellValue("20180412");

    HSSFCell cell_11 = row1.createCell(1);

    cell_11.setCellStyle(style);

    cell_11.setCellValue("上午");

    HSSFRow row2 = sheet.createRow(2);

    HSSFCell cell_21 = row2.createCell(1);

    cell_21.setCellStyle(style);

    cell_21.setCellValue("下午");

    // 合并日期占两行(4个参数,分别为起始行,结束行,起始列,结束列)

    // 行和列都是从0开始计数,且起始结束都会合并

    // 这里是合并excel中日期的两行为一行

    CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);

    sheet.addMergedRegion(region);

    File file = new File("E:\\demo.xls");

    FileOutputStream fout = new FileOutputStream(file);

    workbook.write(fout);

    fout.close();

    }

    }

    运行结果,得到的 Excel 表如下所示:

    9db1fb8aeeec308c031d190f7051bbaa.png

    当然也可以更复杂些的,如下图,需要自己计算好行与列即可

    cee5fca99b9ea2fbaec3c29d07b5f03f.png

    poi合并单元格的相关网页热门搜索词

    poi合并单元格并设置内容居中|JavaExcel合并单元格|poi读取excel合并单元格|poi设置单元格宽度自适应|xssfsheet合并单元格|Java判断excel的合并单元格|java读取excel合并单元格|单元格合并后poi合并单元格|poi设置单元格宽度|

    展开全文
  • poi读取合并单元格

    2021-02-26 20:15:13
    importjava.io.File;importjava.io....importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apa...

    importjava.io.File;importjava.io.IOException;importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.usermodel.WorkbookFactory;importorg.apache.poi.ss.util.CellRangeAddress;importorg.junit.Test;/***

    *@authorwcyong

    *

    * @date 2013-6-21*/

    public classTestReadMergeRegionExcel {

    @Testpublic voidtestReadExcel() {

    readExcelToObj(“f:\\test\\out3.xls”);

    }/*** 读取excel数据

    *@parampath*/

    private voidreadExcelToObj(String path) {

    Workbook wb= null;try{

    wb= WorkbookFactory.create(newFile(path));

    readExcel(wb,0, 0, 0);

    }catch(InvalidFormatException e) {

    e.printStackTrace();

    }catch(IOException e) {

    e.printStackTrace();

    }

    }/*** 读取excel文件

    *@paramwb

    *@paramsheetIndex sheet页下标:从0开始

    *@paramstartReadLine 开始读取的行:从0开始

    *@paramtailLine 去除最后读取的行*/

    private void readExcel(Workbook wb,int sheetIndex, int startReadLine, inttailLine) {

    Sheet sheet=wb.getSheetAt(sheetIndex);

    Row row= null;for(int i=startReadLine; i

    row=sheet.getRow(i);for(Cell c : row) {boolean isMerge =isMergedRegion(sheet, i, c.getColumnIndex());//判断是否具有合并单元格

    if(isMerge) {

    String rs=getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());

    System.out.print(rs+“ “);

    }else{

    System.out.print(c.getRichStringCellValue()+” “);

    }

    }

    System.out.println();

    }

    }/*** 获取合并单元格的值

    *@paramsheet

    *@paramrow

    *@paramcolumn

    *@return

    */

    public String getMergedRegionValue(Sheet sheet ,int row , intcolumn){int sheetMergeCount =sheet.getNumMergedRegions();for(int i = 0 ; i < sheetMergeCount ; i++){

    CellRangeAddress ca=sheet.getMergedRegion(i);int firstColumn =ca.getFirstColumn();int lastColumn =ca.getLastColumn();int firstRow =ca.getFirstRow();int lastRow =ca.getLastRow();if(row >= firstRow && row <=lastRow){if(column >= firstColumn && column <=lastColumn){

    Row fRow=sheet.getRow(firstRow);

    Cell fCell=fRow.getCell(firstColumn);returngetCellValue(fCell) ;

    }

    }

    }return null;

    }/*** 判断合并了行

    *@paramsheet

    *@paramrow

    *@paramcolumn

    *@return

    */

    private boolean isMergedRow(Sheet sheet,int row ,intcolumn) {int sheetMergeCount =sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; 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 true;

    }

    }

    }return false;

    }/*** 判断指定的单元格是否是合并单元格

    *@paramsheet

    *@paramrow 行下标

    *@paramcolumn 列下标

    *@return

    */

    private boolean isMergedRegion(Sheet sheet,int row ,intcolumn) {int sheetMergeCount =sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; 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 true;

    }

    }

    }return false;

    }/*** 判断sheet页中是否含有合并单元格

    *@paramsheet

    *@return

    */

    private booleanhasMerged(Sheet sheet) {return sheet.getNumMergedRegions() > 0 ? true : false;

    }/*** 合并单元格

    *@paramsheet

    *@paramfirstRow 开始行

    *@paramlastRow 结束行

    *@paramfirstCol 开始列

    *@paramlastCol 结束列*/

    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, intlastCol) {

    sheet.addMergedRegion(newCellRangeAddress(firstRow, lastRow, firstCol, lastCol));

    }/*** 获取单元格的值

    *@paramcell

    *@return

    */

    publicString getCellValue(Cell cell){if(cell == null) return“”;if(cell.getCellType() ==Cell.CELL_TYPE_STRING){returncell.getStringCellValue();

    }else if(cell.getCellType() ==Cell.CELL_TYPE_BOOLEAN){returnString.valueOf(cell.getBooleanCellValue());

    }else if(cell.getCellType() ==Cell.CELL_TYPE_FORMULA){returncell.getCellFormula() ;

    }else if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC){returnString.valueOf(cell.getNumericCellValue());

    }return“”;

    }

    }

    展开全文
  • poi获取exccel有合并单元格的数据,存入List
  • POI 合并单元格 及 注意事项
  • POI处理合并单元格

    千次阅读 2018-08-26 23:00:16
  • POI获取合并单元格的值

    千次阅读 2020-11-11 17:01:38
    POI获取合并单元格时,如果是遍历获取合并单元格的所有子单元格的值,office的excel都会有值,wps的excel只会第一个子单元格有值,其他子单元格都没值,即cell=null。故意凡是获取合并单元格的值,都获取第一个子...

    POI获取合并单元格时,如果是遍历获取合并单元格的所有子单元格的值,office的excel都会有值,wps的excel只会第一个子单元格有值,其他子单元格都没值,即cell=null。故意凡是获取合并单元格的值,都获取第一个子单元格的值即可 

    package com.oop.createObj;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    import java.io.FileInputStream;
    import java.util.Arrays;
    import java.util.stream.Stream;
    
    /**
     * @ProjectName: spring-boot-lantian
     * @Package: com.oop.createObj
     * @ClassName: SendFactoryTest
     * @Author: zhangs
     * @Email: 853632587@qq.com
     * @Description: 工厂模式测试
     * @Date: 2020/11/4 15:53
     * @Version: 1.0
     */
    public class SendFactoryTest {
    
        public static void main(String[] args) throws Exception {
    
            //Sender mail= SendFactory.produceMail();
            //mail.send();
    
            HSSFWorkbook wk=new HSSFWorkbook(new FileInputStream("E://订单导出.xls"));
    
            HSSFSheet sheet=wk.getSheetAt(0);
    
            int rows=sheet.getPhysicalNumberOfRows();
    
            int cells=sheet.getRow(0).getPhysicalNumberOfCells();
    
            for(int r=0;r<rows;r++){
    
                HSSFRow row=sheet.getRow(r);
                StringBuilder str=new StringBuilder();
                for(int c=0;c<cells;c++){
                    HSSFCell cell=row.getCell(c);
    
                    if(isMergedRegion(sheet,r,c)){
                        str.append(">>>"+getMergedRegionValue(sheet,r,c)+"||");
                    }else{
    
                        str.append(">>>"+row.getCell(c)+"||");
                    }
    
                }
                System.out.println(r+"str"+str);
                str.setLength(0);
    
            }
    
        }
    
        /**
         * 判断指定的单元格是否是合并单元格
         * @param sheet
         * @param row 行下标
         * @param column 列下标
         * @return
         */
        private static  boolean isMergedRegion(Sheet sheet, int row , int column) {
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; 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 true;
                    }
                }
            }
            return false;
        }
    
        /**
         * 获取合并单元格的值
         * @param sheet
         * @param row
         * @param column
         * @return
         */
        public static String getMergedRegionValue(Sheet sheet ,int row , int column){
            int sheetMergeCount = sheet.getNumMergedRegions();
    
            for(int i = 0 ; i < sheetMergeCount ; i++){
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
    
                if(row >= firstRow && row <= lastRow){
                    if(column >= firstColumn && column <= lastColumn){
                        Row fRow = sheet.getRow(firstRow);
                        Cell fCell = fRow.getCell(firstColumn);
                        return getCellValue(fCell) ;
                    }
                }
            }
    
            return null ;
        }
    
        /**
         * 获取单元格的值
         * @param cell
         * @return
         */
        public  static String getCellValue(Cell cell){
            if(cell == null) return "";
            return cell.toString();
        }
    
        public static void main1(String[] args){
    
    
            Object[][] rowName = new Object[][]{
              {"序号",2000,"NM"},
                    {"分组编号",2000,"NM"},
                    {"订单编号",6500,"NM"},
                    {"第三方订单号",6500,"CM"},
                    {"SKU",2500,"NM"},
                    {"订单金额",2500,"CM"},
                    {"运费",2500,"CM"},
                    {"商品名称",12000,"NM"},
                    {"规格",7000,"NM"},
                    {"单位",2500,"NM"},
                    {"数量",2500,"NM"},
              {"单价",2500,"NM"},
                    {"金额",2500,"NM"}, {"税率",2500,"NM"},{"税额",2500,"NM"},{"结算主体",7000,"M"},{"发票抬头",12000,"M"},{"发票类型",2500,"M"},{"客户名称",7000,"M"},{"项目名称",5000,"M"},{"采购员",5000,"M"},
              {"收件人",6500,"M"},{"详细地址",7000,"M"},{"店铺",7000,"M"},{"订单状态",2500,"CM"},{"订单标签",4500,"CM"},{"结算状态",4500,"CM"},{"发票编号",5000,"CM"},{"订单开始时间",5000,"CM"},{"审批完成时间",5000,"CM"},{"订单完成时间",5000,"CM"},
              {"下单月",5000,"CM"}, {"订单完成月",5000,"CM"},{"商品一级分类",5000,"NM"}, {"商品二级分类",5000,"NM"}, {"商品三级分类",5000,"NM"}};
    
    
            Stream.iterate(0, i -> i + 1).limit(rowName.length).forEach(i -> System.out.println(rowName[i][2]));
    
    
        }
    }
    

    展开全文
  • POI操作Excel 合并单元格 边框问题解决方法,这个方法是我亲自测试,并运用于项目中的,可以放心使用,还一并写出了很多注释,其中有写poi的bug的解决方法
  • POI实现合并单元格

    2019-09-10 15:07:23
    // 大标题合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 18); sheet.addMergedRegion(cellRangeAddress); // 生成样式 HSSFCellStyle titleStyle = workbook.createCellStyle()...
  • 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 3.12依赖的包:代码:public class POITest {public static void main(String[] args) throws Exception {File inputFile = new File("d:\\test.xlsx");InputStream is = new FileInputStream(inputFile);...
  • 利用poi进行excel相同内容合并单元格,只摘要了传入部分功能,请关注addMergedRegion方法
  • poi导出 合并单元格 自适应高度

    千次阅读 2020-10-27 11:20:38
    import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache....
  • Apache POI合并单元格

    千次阅读 2017-08-15 15:24:03
    利用Apache POI合并单元格
  • 合并合并后 //上边循环存值。 String[] lie = {"ffdwsjname","ffdwname","gktype"}; //需要合并的列,也用作取数据 for (int i = 0; i < lie.length; i++) { //循环列 Map<String,Integer>...
  • ava poi合并单元格后内容无法显示
  • 最近碰到个需要合并单元格的需求,要合并相同内容的单元格,前提是合并同一个订单的(第5列) 1.先合并第5列OrderNo的...// 已生成的excel进行合并单元格操作,先合并第5列OrderNo的列的单元格 ExcelUtil.merge...
  • poi生成excel及合并单元格示例,详见文章http://blog.csdn.net/geloin/article/details/17219885
  • ![图片说明](https://img-ask.csdn.net/upload/202008/15/1597422957_203616.png)![图片说明](https://img-ask.csdn.net/upload/202008/15/1597422963_146350.png)
  • POI 合并单元格

    千次阅读 2017-09-29 10:48:38
    1. 合并单元格概述合并单元格CellRangeAddress就是将几个相邻的单元格合并为一个单元格,即使没有创建过行和单元格,也 可以创建合并单元格,因为单元格信息是单独存储的 /** * Creates new cell range. base 0 *...
  • POI是apache提供的一个读写Excel文档的开源组件,在操作excel时常要合并单元格合并单元格的方法是:sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));public CellRangeAddress(int firstRow,int lastRow,...
  • Java POI Excel合并单元格的数据处理 最近在项目开发过程中,有个一个导入Excel文件处理合并单元格数据的需求,就自己在网上找了一个模板,自己进行二次开发来开发需求。 Excel工具类 public class ExcelUtil { ...
  • POI读取合并单元格中的excel数据

    千次阅读 2018-01-15 11:25:42
    import java.io.File;  import java.io.IOException;    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  ...import org.apache.poi.ss.usermodel.Cell;...import org.apache.poi.ss.us
  • POI Word单元格合并

    千次阅读 2020-04-08 16:58:56
    1、pom.xml <dependency> <...org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dep...
  • 下面是网上判断合并单元格的方法,它里面加粗那段代码会循环好多遍,假设我导入4000条数据,它每一行就会循环接近4000遍才会走进去,这个方法要怎么改效率才会高点,我本地测了下4000条数据这个方法全部走完就得40秒...
  • 下面例子为创建产生一个excel,合并单元格,然后为合并后的单元格添加边框 package test; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; ...
  • 使用poi导出的execl合并单元格,会出现下图问题整个单元格看似合并了,但是文字没有垂直居中,而且execl中所有的合并都会在第三行开始出现灰色分层样式合并单元格伪代码String upCompareField = ""; //上一行的对比...
  • excel poi工具类 导入 导出 合并单元格 计算公式

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,464
精华内容 3,385
关键字:

poi对合并单元格的处理