poi合并单元格

2016-01-24 19:01:22

POI合并单元格的方法是addMergedRegion（）; /** * 合并单元格 * @param sheet 要合并单元格的excel 的sheet * @param cellLine 要合并的列 * @param startRow 要合并列的开始行


注：我这里合并的是列的相同单元格，图片效果如下：
 POI合并单元格的方法是addMergedRegion（）;




/**  
     * 合并单元格  
     * @param sheet 要合并单元格的excel 的sheet
     * @param cellLine  要合并的列  
     * @param startRow  要合并列的开始行  
     * @param endRow    要合并列的结束行  
     */  
    private static void addMergedRegion(HSSFSheet sheet, int cellLine, int startRow, int endRow,HSSFWorkbook workBook){   
           
     HSSFCellStyle style = workBook.createCellStyle(); // 样式对象    
     
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直    
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平    
        //获取第一行的数据,以便后面进行比较    
        String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();   
     
        int count = 0;
        boolean flag = false;
        for (int i = 1; i <= endRow; i++) {   
         String s_current = sheet.getRow(i).getCell(0).getStringCellValue(); 
         if(s_will.equals(s_current))
         {
         s_will = s_current;
         if(flag)
         {
         sheet.addMergedRegion(new CellRangeAddress(startRow-count,startRow,cellLine,cellLine));
         HSSFRow row = sheet.getRow(startRow-count);
         String cellValueTemp = sheet.getRow(startRow-count).getCell(0).getStringCellValue(); 
         HSSFCell cell = row.createCell(0);
         cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
                
cell.setCellStyle(style); // 样式    
         count = 0;
         flag = false;
         }
         startRow=i;
         count++;        

         }else{
         flag = true;
         s_will = s_current;
         }
//由于上面循环中合并的单元放在有下一次相同单元格的时候做的，所以最后如果几行有相同单元格则要运行下面的合并单元格。
         if(i==endRow&&count>0)
         {
         sheet.addMergedRegion(new CellRangeAddress(endRow-count,endRow,cellLine,cellLine));   
         String cellValueTemp = sheet.getRow(startRow-count).getCell(0).getStringCellValue(); 
         HSSFRow row = sheet.getRow(startRow-count);
     HSSFCell cell = row.createCell(0);
     cell.setCellValue(cellValueTemp); // 跨单元格显示的数据    
                cell.setCellStyle(style); // 样式    
         }
        }
    }
    






你只要在导出excel的方法里调用：  addMergedRegion(sheet, 0, 0, sheet.getLastRowNum(),workBook);就是合并第一列的所有相同单元格。

收起

展开全文

java

POI 合并单元格

2018-06-17 18:28:29

一、合并效果效果说明：第A 列合并行列序为：5-22第B 列合并行列序为：5-10、11-16、17-22第C 列合并行列序为：5-7、8-10、11-13、14-16、17-19、20-2二、POI 合并单元格样例代码//合并第A列的 5-22 int A = 0; ...

一、合并效果


二、POI 合并单元格样例代码
package org.linxiupan.export.excel;

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

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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiMergeTest {
	public static void main(String[] args) throws IOException{
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sht = wb.createSheet("sheet1");
		int columnSize = 6;
		for(int i=0;i<4;i++){
			XSSFRow row = sht.createRow(i);
			for(int j=0;j<columnSize;j++){
				XSSFCell cell = row.createCell(j);
				cell.setCellValue(i+":"+j);
			}
		}
		int rowPointer = 4;
		for(int i=4;i<7;i++){
			for(int j=0;j<3;j++){
				for(int k=0;k<2;k++){
					for(int l=0;l<3;l++){
						XSSFRow row = sht.createRow(rowPointer++);
						XSSFCell cell = row.createCell(0);
						cell.setCellValue(i);
						
						cell = row.createCell(1);
						cell.setCellValue(j);
						
						cell = row.createCell(2);
						cell.setCellValue(k);
						
						cell = row.createCell(3);
						cell.setCellValue(l);
						
						cell = row.createCell(4);
						cell.setCellValue((rowPointer-1)+":"+4);
						
						cell = row.createCell(5);
						cell.setCellValue((rowPointer-1)+":"+5);
					}
				}
			}
		}
		List<Integer> l1 = new ArrayList<Integer>();
		List<Integer> l2 = new ArrayList<Integer>();
		List<Integer> l3 = new ArrayList<Integer>();
		String s1 = "",s2="",s3="";
		Iterator<Row> itr = sht.iterator();
		while(itr.hasNext()){
			Row row = itr.next();
			if(row.getRowNum()<4)continue;
			String n1 = getCellValue(row.getCell(0));
			if(!n1.equals(s1)){
				l1.add(row.getRowNum());
				s1=n1;
			}
			
			String n2 = getCellValue(row.getCell(1));
			if(!n2.equals(s2)){
				l2.add(row.getRowNum());
				s2=n2;
			}
			
			String n3 = getCellValue(row.getCell(2));
			if(!n3.equals(s3)){
				l3.add(row.getRowNum());
				s3=n3;
			}
			
		}
		l1.add(sht.getLastRowNum()+1);
		l2.add(sht.getLastRowNum()+1);
		l3.add(sht.getLastRowNum()+1);
		
		Integer prevI = 4;
		for(Integer i:l1){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,0,0);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		prevI = 4;
		for(Integer i:l2){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,1,1);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		prevI = 4;
		for(Integer i:l3){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,2,2);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		//get imported values;
		List<String> measures = new ArrayList<String>();
		measures.add("2.0");
		Iterator<Row> importItr = sht.iterator();
		while(importItr.hasNext()){
			Row row = importItr.next();
			if(row.getRowNum()<4)continue;
			String measure = getCellValue(row.getCell(3));
			//System.out.println(measure);
			if(measures.contains(measure)){
				System.out.print("level1="+getCellValue(row.getCell(0)));
				System.out.print("\tlevel2="+getCellValue(row.getCell(1)));
				System.out.println("\tlevel3="+getCellValue(row.getCell(2)));
			}
		}
		
		FileOutputStream fos = new FileOutputStream(new File("/Users/lxp/Desktop/20180618.xlsx"));
		wb.write(fos);
		wb.close();
		fos.close();
		System.out.println("finished.");
	}
	
	static String getCellValue(Cell cell){
		CellType type = cell.getCellTypeEnum();
		if(type==CellType.NUMERIC){
			return String.valueOf(cell.getNumericCellValue());
		}else if(type==CellType.STRING){
			return cell.getStringCellValue();
		}
		
		
		return "";
	}
}

收起

展开全文

POI合并单元格
2013-09-10 10:42:40
java POI导出Excel文件多表头文件
收起
java excel poi合并单元格
2017-06-02 16:48:36
java excel poi合并单元格
收起

poi 合并单元格

2018-10-21 12:29:26

* 获取合并单元格的值 * * @param sheet * @param row * @param column * @return */ public static String getMergedRegionValue(Sheet sheet, int row, int...

/** 
	     * 获取合并单元格的值 
	     *  
	     * @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;  
	    }  
	  
	    /** 
	     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用getMergedRegionValue(Sheet sheet, int row, int column) 
	     * @description 
	     * @author liuzhenpeng 
	     * @date 2017年2月16日 
	     * @param sheet 
	     * @param cell 
	/** 
     * 获取合并单元格的值 
     *  
     * @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;  
    }  
  
    /** 
     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用getMergedRegionValue(Sheet sheet, int row, int column) 
     * @description 
     * @author liuzhenpeng 
     * @date 2017年2月16日 
     * @param sheet 
     * @param cell 
     * @return 
     */  
    public static String getMergedRegionValue(Sheet sheet, Cell cell) {  
        return getMergedRegionValue(sheet, cell.getRowIndex(),  
                cell.getColumnIndex());  
    }  
  
    /** 
     * 判断合并了行 
     *  
     * @param sheet 
     * @param row 
     * @param column 
     * @return 
     */  
    public static boolean isMergedRow(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 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;  
    }  
  
    /** 
     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用isMergedRegion(Sheet sheet, int row, int column) 
     * @description 
     * @author liuzhenpeng 
     * @date 2017年2月16日 
     * @param sheet 
     * @param cell 
     * @return 
     */  
    public static boolean isMergedRegion(Sheet sheet, Cell cell) {  
        int row = cell.getRowIndex();  
        int column = cell.getColumnIndex();  
        return isMergedRegion(sheet, row, column);  
    }  
  
    public static boolean isCellInRegion(int rowIndex, int colIndex,  
            Region region) {  
        if (rowIndex >= region.getFirstRow() && rowIndex <= region.getLastRow()) {  
            if (colIndex >= region.getFirstColumn()  
                    && colIndex <= region.getLastColumn()) {  
                return true;  
            }  
        }  
        return false;  
    }  
  
    public static boolean isCellInRegion(Cell cell, Region region) {  
        return isCellInRegion(cell.getRowIndex(), cell.getColumnIndex(), region);  
    }  
  
    public static Region getMergedRegion(Sheet sheet, int rowIndex, int colIndex) {  
        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 (rowIndex >= firstRow && rowIndex <= lastRow) {  
                if (colIndex >= firstColumn && colIndex <= lastColumn) {  
                    Region region = new Region();  
                    region.setFirstRow(firstRow);  
                    region.setLastRow(lastRow);  
                    region.setFirstColumn(firstColumn);  
                    region.setLastColumn(lastColumn);  
                    return region;  
                }  
            }  
        }  
        return null;  
    }  
  
    public static Region getMergedRegion(Sheet sheet, Cell cell) {  
        return getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());  
    }  
  
    /** 
     * 判断sheet页中是否含有合并单元格 
     *  
     * @param sheet 
     * @return 
     */  
    public static boolean hasMerged(Sheet sheet) {  
        return sheet.getNumMergedRegions() > 0 ? true : false;  
    }  
  
    /** 
     * 合并单元格 
     *  
     * @param sheet 
     * @param firstRow 
     *            开始行 
     * @param lastRow 
     *            结束行 
     * @param firstCol 
     *            开始列 
     * @param lastCol 
     *            结束列 
     */  
    public static void mergeRegion(Sheet sheet, int firstRow, int lastRow,  
            int firstCol, int lastCol) {  
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol,  
                lastCol));  
    }  
  
    /** 
     * 获取单元格的值 
     *  
     * @param cell 
     * @return 
     */  
    public static String getCellValue(Cell cell) {  
  
        if (cell == null)  
            return "";  
  
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {  
  
            return cell.getStringCellValue();  
  
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
  
            return String.valueOf(cell.getBooleanCellValue());  
  
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {  
              
            return cell.getCellFormula();  
  
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
            return String.valueOf(cell.getNumericCellValue());  
        }  
        return "";  
    }  
  
    public static ExcelInputStreamDto getUploadExcelInputStream(  
            HttpServletRequest request, Long maxExcelFileSize)  
            throws IOException {  
        String[] allowExtensions = { ".et", ".ett", ".xls", ".xlsx" };  
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(  
                request.getSession().getServletContext());  
        // 判断 request 是否有文件上传,即多部分请求  
        if (multipartResolver.isMultipart(request)) {  
            // 转换成多部分request  
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;  
  
            MultipartFile file = multiRequest.getFile("import");  
  
            if (file == null || file.getSize() == 0) {  
                throw new ServiceException("文件不存在");  
            }  
  
            if (maxExcelFileSize != null && file.getSize() > maxExcelFileSize) {  
                throw new ServiceException("文件过大");  
            }  
            boolean extMatch = false;  
            ExcelFormat excelFormat = null;  
            for (String ext : allowExtensions) {  
                if (file.getOriginalFilename().endsWith(ext)) {  
                    extMatch = true;  
                    if (".xls".equalsIgnoreCase(ext)) {  
                        excelFormat = ExcelFormat.xls;  
                    } else if (".xlsx".equalsIgnoreCase(ext)) {  
                        excelFormat = ExcelFormat.xlsx;  
                    } else if (".et".equalsIgnoreCase(ext)  
                            || ".ett".equalsIgnoreCase(ext)) {  
                        // WPS的et和ett格式可能内部是xls，也可能是xlsx，只能通过读取文件头判断  
                        if (file.getSize() < 2) {  
                            // 如果文件小于2字节，无法判断文件头，则直接返回格式不正确  
                            throw new ServiceException("不正确的文件格式");  
                        }  
                        byte[] fileHeaderBytes = new byte[2];  
                        InputStream is = file.getInputStream();  
                        is.read(fileHeaderBytes, 0, 2);  
                        String fileHeaderHex = GetTypeByHead  
                                .bytesToHexString(fileHeaderBytes);  
                        if ("504B".equalsIgnoreCase(fileHeaderHex)) {  
                            excelFormat = ExcelFormat.xlsx;  
                        } else if ("D0CF".equalsIgnoreCase(fileHeaderHex)) {  
                            excelFormat = ExcelFormat.xls;  
                        }  
                    } else {  
                        throw new ServiceException("不正确的文件格式");  
                    }  
                    break;  
                }  
            }  
            if (!extMatch) {  
                throw new ServiceException("不正确的文件格式");  
            }  
            ExcelInputStreamDto result = new ExcelInputStreamDto();  
            result.setExcelFormat(excelFormat);  
            result.setInputStream(file.getInputStream());  
            return result;  
        }  
        throw new ServiceException("不正确的请求");  
    }  
  
    /** 
     * 判断Row(行)是否为空行(行本身为null或行中的单元格全部为null) 
     * @param row 
     * @return 
     */  
    public static boolean isEmptyRow(Row row) {  
        if (row != null) {  
            short lastCellNum = row.getLastCellNum();  
            if (lastCellNum == 0) {// 如果不存在单元格则返回true  
                return true;  
            } else {  
                // 空单元格的个数  
                int emptyCellNum = 0;  
                for (int i = 0; i < lastCellNum; i++) {  
                    Cell cell = row.getCell(i);  
                    if (isEmptyCell(cell)) {  
                        emptyCellNum++;  
                    }  
                }  
                if (emptyCellNum == lastCellNum) {  
                    return true;  
                }  
            }  
        } else {  
            return true;  
        }  
        return false;  
    }  
  
    /** 
     * 判断Row(行)是否存在空的单元格或者这行是否存在单元格 
     * @param row 
     * @return 
     */  
    public static boolean rowContianEmptyCell(Row row) {  
        if (row != null) {  
            short lastCellNum = row.getLastCellNum();  
            if (lastCellNum == 0) {// 如果不存在单元格则返回true  
                return true;  
            } else {  
                for (int i = 0; i < lastCellNum; i++) {  
                    Cell cell = row.getCell(i);  
                    if (isEmptyCell(cell)) {  
                        return true;  
                    }  
                }  
            }  
        } else {  
            return true;  
        }  
        return false;  
    }  
  
    /** 
     * 判断Sheet是否存在空的行或存在空数据的行 
     * @param sheet 
     * @return 
     */  
    public static boolean sheetContainEmptyRow(Sheet sheet) {  
        if (sheet != null) {  
            int lastRowNum = sheet.getLastRowNum();  
            if (lastRowNum == 0) {// 如果不存在sheet则返回true  
                return true;  
            } else {  
                for (int i = 0; i < lastRowNum; i++) {  
                    Row row = sheet.getRow(i);  
                    if (isEmptyRow(row)) {  
                        return true;  
                    }  
                }  
            }  
        } else {  
            return true;  
        }  
        return false;  
    }  
  
    /** 
     * 基于指定列数判断Sheet是否存在空的行或存在空数据的行 
     * @param sheet 
     * @param columnNum 
     * @return 
     */  
    public static boolean sheetContainEmptyRow(Sheet sheet, int columnNum) {  
        if (sheet != null) {  
            int lastRowNum = sheet.getLastRowNum();  
            if (lastRowNum == 0) {// 如果不存在sheet则返回true  
                return true;  
            } else {  
                if (lastRowNum >= columnNum) {  
                    for (int i = 0; i < columnNum; i++) {  
                        Row row = sheet.getRow(i);  
                        if (isEmptyRow(row)) {  
                            return true;  
                        }  
                    }  
                }else{  
                    return true;  
                }  
            }  
        } else {  
            return true;  
        }  
        return false;  
    }  
    /** 
     * 获取表格中空行的行号 
     * @param sheet 
     * @return 
     */  
    public static List<Integer> getEmptyRowNos(Sheet sheet){  
        List<Integer> list=new ArrayList<>();  
        if (sheet != null) {  
            int lastRowNum = sheet.getLastRowNum();  
            if (lastRowNum != 0) {// 如果不存在sheet则返回true  
                for (int i = 0; i < lastRowNum; i++) {  
                    Row row = sheet.getRow(i);  
                    if (isEmptyRow(row)) {  
                        list.add(i);  
                    }  
                }  
            }   
        }  
        return list;  
    }  
      
    /** 
     * 判断Cell(单元格)是否为空 
     *  
     * @param cell 
     * @return 
     */  
    public static boolean isEmptyCell(Cell cell) {  
        String cellContent = getCellValue(cell);  
        if(StringUtils.hasText(cellContent)){  
            return false;  
        } else{  
            return true;  
        }  
    }  
  
    /** 
     * 关闭workbook 
     *  
     * @param workbook 
     */  
    public static void closeWorkbook(Workbook workbook) {  
        if (workbook != null) {  
            try {  
                workbook.close();  
            } catch (IOException e) {  
                LOG.error("关闭workbook失败", e);  
            }  
        }  
    }  
      
    public static void addDataToRow(Row row,List<String> values){  
        if(values!=null && !values.isEmpty()){  
            for (int i=0;i<values.size();i++) {  
                Cell cell=row.createCell(i);  
                cell.setCellValue(values.get(i));  
            }  
        }  
    }  
} 

	 
	
	     * @return 
	     */  
	    public static String getMergedRegionValue(Sheet sheet, Cell cell) {  
	        return getMergedRegionValue(sheet, cell.getRowIndex(),  
	                cell.getColumnIndex());  
	    }  
	  
	    /** 
	     * 判断合并了行 
	     *  
	     * @param sheet 
	     * @param row 
	     * @param column 
	     * @return 
	     */  
	    public static boolean isMergedRow(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 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;  
	    }  
	  
	    /** 
	     * 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用isMergedRegion(Sheet sheet, int row, int column) 
	     * @description 
	     * @author liuzhenpeng 
	     * @date 2017年2月16日 
	     * @param sheet 
	     * @param cell 
	     * @return 
	     */  
	    public static boolean isMergedRegion(Sheet sheet, Cell cell) {  
	        int row = cell.getRowIndex();  
	        int column = cell.getColumnIndex();  
	        return isMergedRegion(sheet, row, column);  
	    }  
	  
	    public static boolean isCellInRegion(int rowIndex, int colIndex,  
	            Region region) {  
	        if (rowIndex >= region.getFirstRow() && rowIndex <= region.getLastRow()) {  
	            if (colIndex >= region.getFirstColumn()  
	                    && colIndex <= region.getLastColumn()) {  
	                return true;  
	            }  
	        }  
	        return false;  
	    }  
	  
	    public static boolean isCellInRegion(Cell cell, Region region) {  
	        return isCellInRegion(cell.getRowIndex(), cell.getColumnIndex(), region);  
	    }  
	  
	    public static Region getMergedRegion(Sheet sheet, int rowIndex, int colIndex) {  
	        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 (rowIndex >= firstRow && rowIndex <= lastRow) {  
	                if (colIndex >= firstColumn && colIndex <= lastColumn) {  
	                    Region region = new Region();  
	                    region.setFirstRow(firstRow);  
	                    region.setLastRow(lastRow);  
	                    region.setFirstColumn(firstColumn);  
	                    region.setLastColumn(lastColumn);  
	                    return region;  
	                }  
	            }  
	        }  
	        return null;  
	    }  
	  
	    public static Region getMergedRegion(Sheet sheet, Cell cell) {  
	        return getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());  
	    }  
	  
	    /** 
	     * 判断sheet页中是否含有合并单元格 
	     *  
	     * @param sheet 
	     * @return 
	     */  
	    public static boolean hasMerged(Sheet sheet) {  
	        return sheet.getNumMergedRegions() > 0 ? true : false;  
	    }  
	  
	    /** 
	     * 合并单元格 
	     *  
	     * @param sheet 
	     * @param firstRow 
	     *            开始行 
	     * @param lastRow 
	     *            结束行 
	     * @param firstCol 
	     *            开始列 
	     * @param lastCol 
	     *            结束列 
	     */  
	    public static void mergeRegion(Sheet sheet, int firstRow, int lastRow,  
	            int firstCol, int lastCol) {  
	        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol,  
	                lastCol));  
	    }  
	  
	    /** 
	     * 获取单元格的值 
	     *  
	     * @param cell 
	     * @return 
	     */  
	    public static String getCellValue(Cell cell) {  
	  
	        if (cell == null)  
	            return "";  
	  
	        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {  
	  
	            return cell.getStringCellValue();  
	  
	        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
	  
	            return String.valueOf(cell.getBooleanCellValue());  
	  
	        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {  
	              
	            return cell.getCellFormula();  
	  
	        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
	            return String.valueOf(cell.getNumericCellValue());  
	        }  
	        return "";  
	    }  
	  
	    public static ExcelInputStreamDto getUploadExcelInputStream(  
	            HttpServletRequest request, Long maxExcelFileSize)  
	            throws IOException {  
	        String[] allowExtensions = { ".et", ".ett", ".xls", ".xlsx" };  
	        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(  
	                request.getSession().getServletContext());  
	        // 判断 request 是否有文件上传,即多部分请求  
	        if (multipartResolver.isMultipart(request)) {  
	            // 转换成多部分request  
	            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;  
	  
	            MultipartFile file = multiRequest.getFile("import");  
	  
	            if (file == null || file.getSize() == 0) {  
	                throw new ServiceException("文件不存在");  
	            }  
	  
	            if (maxExcelFileSize != null && file.getSize() > maxExcelFileSize) {  
	                throw new ServiceException("文件过大");  
	            }  
	            boolean extMatch = false;  
	            ExcelFormat excelFormat = null;  
	            for (String ext : allowExtensions) {  
	                if (file.getOriginalFilename().endsWith(ext)) {  
	                    extMatch = true;  
	                    if (".xls".equalsIgnoreCase(ext)) {  
	                        excelFormat = ExcelFormat.xls;  
	                    } else if (".xlsx".equalsIgnoreCase(ext)) {  
	                        excelFormat = ExcelFormat.xlsx;  
	                    } else if (".et".equalsIgnoreCase(ext)  
	                            || ".ett".equalsIgnoreCase(ext)) {  
	                        // WPS的et和ett格式可能内部是xls，也可能是xlsx，只能通过读取文件头判断  
	                        if (file.getSize() < 2) {  
	                            // 如果文件小于2字节，无法判断文件头，则直接返回格式不正确  
	                            throw new ServiceException("不正确的文件格式");  
	                        }  
	                        byte[] fileHeaderBytes = new byte[2];  
	                        InputStream is = file.getInputStream();  
	                        is.read(fileHeaderBytes, 0, 2);  
	                        String fileHeaderHex = GetTypeByHead  
	                                .bytesToHexString(fileHeaderBytes);  
	                        if ("504B".equalsIgnoreCase(fileHeaderHex)) {  
	                            excelFormat = ExcelFormat.xlsx;  
	                        } else if ("D0CF".equalsIgnoreCase(fileHeaderHex)) {  
	                            excelFormat = ExcelFormat.xls;  
	                        }  
	                    } else {  
	                        throw new ServiceException("不正确的文件格式");  
	                    }  
	                    break;  
	                }  
	            }  
	            if (!extMatch) {  
	                throw new ServiceException("不正确的文件格式");  
	            }  
	            ExcelInputStreamDto result = new ExcelInputStreamDto();  
	            result.setExcelFormat(excelFormat);  
	            result.setInputStream(file.getInputStream());  
	            return result;  
	        }  
	        throw new ServiceException("不正确的请求");  
	    }  
	  
	    /** 
	     * 判断Row(行)是否为空行(行本身为null或行中的单元格全部为null) 
	     * @param row 
	     * @return 
	     */  
	    public static boolean isEmptyRow(Row row) {  
	        if (row != null) {  
	            short lastCellNum = row.getLastCellNum();  
	            if (lastCellNum == 0) {// 如果不存在单元格则返回true  
	                return true;  
	            } else {  
	                // 空单元格的个数  
	                int emptyCellNum = 0;  
	                for (int i = 0; i < lastCellNum; i++) {  
	                    Cell cell = row.getCell(i);  
	                    if (isEmptyCell(cell)) {  
	                        emptyCellNum++;  
	                    }  
	                }  
	                if (emptyCellNum == lastCellNum) {  
	                    return true;  
	                }  
	            }  
	        } else {  
	            return true;  
	        }  
	        return false;  
	    }  
	  
	    /** 
	     * 判断Row(行)是否存在空的单元格或者这行是否存在单元格 
	     * @param row 
	     * @return 
	     */  
	    public static boolean rowContianEmptyCell(Row row) {  
	        if (row != null) {  
	            short lastCellNum = row.getLastCellNum();  
	            if (lastCellNum == 0) {// 如果不存在单元格则返回true  
	                return true;  
	            } else {  
	                for (int i = 0; i < lastCellNum; i++) {  
	                    Cell cell = row.getCell(i);  
	                    if (isEmptyCell(cell)) {  
	                        return true;  
	                    }  
	                }  
	            }  
	        } else {  
	            return true;  
	        }  
	        return false;  
	    }  
	  
	    /** 
	     * 判断Sheet是否存在空的行或存在空数据的行 
	     * @param sheet 
	     * @return 
	     */  
	    public static boolean sheetContainEmptyRow(Sheet sheet) {  
	        if (sheet != null) {  
	            int lastRowNum = sheet.getLastRowNum();  
	            if (lastRowNum == 0) {// 如果不存在sheet则返回true  
	                return true;  
	            } else {  
	                for (int i = 0; i < lastRowNum; i++) {  
	                    Row row = sheet.getRow(i);  
	                    if (isEmptyRow(row)) {  
	                        return true;  
	                    }  
	                }  
	            }  
	        } else {  
	            return true;  
	        }  
	        return false;  
	    }  
	  
	    /** 
	     * 基于指定列数判断Sheet是否存在空的行或存在空数据的行 
	     * @param sheet 
	     * @param columnNum 
	     * @return 
	     */  
	    public static boolean sheetContainEmptyRow(Sheet sheet, int columnNum) {  
	        if (sheet != null) {  
	            int lastRowNum = sheet.getLastRowNum();  
	            if (lastRowNum == 0) {// 如果不存在sheet则返回true  
	                return true;  
	            } else {  
	                if (lastRowNum >= columnNum) {  
	                    for (int i = 0; i < columnNum; i++) {  
	                        Row row = sheet.getRow(i);  
	                        if (isEmptyRow(row)) {  
	                            return true;  
	                        }  
	                    }  
	                }else{  
	                    return true;  
	                }  
	            }  
	        } else {  
	            return true;  
	        }  
	        return false;  
	    }  
	    /** 
	     * 获取表格中空行的行号 
	     * @param sheet 
	     * @return 
	     */  
	    public static List<Integer> getEmptyRowNos(Sheet sheet){  
	        List<Integer> list=new ArrayList<>();  
	        if (sheet != null) {  
	            int lastRowNum = sheet.getLastRowNum();  
	            if (lastRowNum != 0) {// 如果不存在sheet则返回true  
	                for (int i = 0; i < lastRowNum; i++) {  
	                    Row row = sheet.getRow(i);  
	                    if (isEmptyRow(row)) {  
	                        list.add(i);  
	                    }  
	                }  
	            }   
	        }  
	        return list;  
	    }  
	      
	    /** 
	     * 判断Cell(单元格)是否为空 
	     *  
	     * @param cell 
	     * @return 
	     */  
	    public static boolean isEmptyCell(Cell cell) {  
	        String cellContent = getCellValue(cell);  
	        if(StringUtils.hasText(cellContent)){  
	            return false;  
	        } else{  
	            return true;  
	        }  
	    }  
	  
	    /** 
	     * 关闭workbook 
	     *  
	     * @param workbook 
	     */  
	    public static void closeWorkbook(Workbook workbook) {  
	        if (workbook != null) {  
	            try {  
	                workbook.close();  
	            } catch (IOException e) {  
	                LOG.error("关闭workbook失败", e);  
	            }  
	        }  
	    }  
	      
	    public static void addDataToRow(Row row,List<String> values){  
	        if(values!=null && !values.isEmpty()){  
	            for (int i=0;i<values.size();i++) {  
	                Cell cell=row.createCell(i);  
	                cell.setCellValue(values.get(i));  
	            }  
	        }  
	    }  
	}

收起

展开全文

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!");
}

收起

展开全文

POI 合并单元格 及注意事项
2014-03-27 11:41:14
POI 合并单元格 及注意事项
收起
java poi 合并单元格
万次阅读 多人点赞 2017-03-29 16:39:01
poi 合并单元格 XSSFWorkbook wb = new ...//这个就是合并单元格 //参数说明：1：开始行 2：结束行 3：开始列 4：结束列 //比如我要合并第二行到第四行的第六列到第八列 sheet.addMergedRegion(new CellRang
收起
EXCEL POI 合并单元格解析
2020-01-09 11:09:03
EXCEL POI 合并单元格解析 1、EXCEL2007的合并单元格的XML格式 <mergeCells count="2"> <mergeCell ref="A1:A2"/> <mergeCell ref="B3:B4"/> </mergeCells> mergeCells 里面包含了所有...
收起
Poi合并单元格后居中显示
2017-03-23 08:04:28
Poi合并单元格后居中显示，大家帮忙看下我写的有什么问题，为什么居中显示不了，谢谢啦
收起
java
POI 合并单元格自定义插入
2019-07-08 16:41:42
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合并单元格后边框显示问题
千次阅读 2019-12-03 17:57:50
POI合并单元格后边框显示问题最近在做导出Excel报表业务. 牵扯到一个复杂表头… 首先我分成3层来为表头数据装配样式. 但是两大行中因为牵扯到一个合并问题,没有附上边框, 之后也百度了前辈们总结的经验, 附上代码:...
收起
poi 合并单元格添加边框_如何在Excel中添加和更改单元格边框
2020-09-21 12:59:38
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 css python 软件测试
JAVA poi 合并单元格
2019-09-22 10:48:09
完整帮助代码：JAVA poi 帮助类 ... // 得到一个sheet中有多少个合并单元格 Integer sheetMergeCount = sheet.getNumMergedRegions(); for (Integer i = 0; i < sheetMergeCount; i++) { ...
收起
poi 合并单元格添加边框_如何在Excel中的文本和单元格边框之间添加空间
2020-09-24 05:58:30
poi 合并单元格添加边框Adding a bit of extra padding to your cell boundaries can make it easier for you to read through data in a Microsoft Excel spreadsheet. If you want to add extra white space ...
收起
python excel java 正则表达式 xml

poi合并单元格

POI 合并单元格

一、合并效果

二、POI 合并单元格样例代码

POI合并单元格

java excel poi合并单元格

poi 合并单元格

PoI合并单元格

POI 合并单元格及注意事项

java poi 合并单元格

EXCEL POI 合并单元格解析

Poi合并单元格后居中显示

POI 合并单元格自定义插入

POI合并单元格后边框显示问题

poi 合并单元格添加边框_如何在Excel中添加和更改单元格边框

JAVA poi 合并单元格

poi 合并单元格添加边框_如何在Excel中的文本和单元格边框之间添加空间

poi合并单元格

poi合并单元格

POI 合并单元格

一、合并效果

二、POI 合并单元格样例代码

POI合并单元格

java excel poi合并单元格

poi 合并单元格

PoI合并单元格

POI 合并单元格 及 注意事项

java poi 合并单元格

EXCEL POI 合并单元格解析

Poi合并单元格后居中显示

POI 合并单元格自定义插入

POI合并单元格后边框显示问题

poi 合并单元格添加边框_如何在Excel中添加和更改单元格边框

JAVA poi 合并单元格

poi 合并单元格添加边框_如何在Excel中的文本和单元格边框之间添加空间

poi合并单元格

POI 合并单元格及注意事项