[切换城市]
[04-16]excel合并单元格后自动换行高度怎么不能跟着内容自动调整
如题~求高手指教,非常感谢!
例如我合并单元格后,选自动换行,内容有三行,但双击左边行高,确只能显示一行的内容
百度，万能的百度。
昨天有百度了,那些方法都没效果.没合并时是有效的
双击这行下边缘
=600) window.open('/attachment/Mon_b7ef1905f.jpg');" style="max-width:600" onload="if(this.offsetWidth>'600')this.width='600';" >
回 5楼(边月弓影) 的帖子
表格合并后换行不能自动
我要怎么教你啊，你把所有单元格选中，全部设置为可以自动换行。
回 7楼(边月弓影) 的帖子
试过没有效果呢,您看一下我的载图,实际&整理&那儿有两行字的'600')this.width='600';" title="点击查看原图">
回 7楼(边月弓影) 的帖子
一个单元格是可以,但合并后就不行啦
回 9楼(violetal) 的帖子
合并单元格之后最适合行高就会失效，只能手动了有一个方法就是使用excel的宏，需要写代码，网上有很多
楼主啊，整理那行就没有两行合并的意义呀！
回 10楼(边月弓影) 的帖子
哦,谢谢哈~那得去研究研究
回 11楼(pssky) 的帖子
可是不合并描述一个事项可能两三行,还不是也要手动调整
回 10楼(边月弓影) 的帖子
亲~再装个WPS就可以,一点不影响.搞定了
回 14楼(violetal) 的帖子
谢谢。不过平时办公软件用得少。没准儿最新版的office功能更强大呢
访问内容超出本站范围，不能确定是否安全
限100 字节
如果您在写长篇帖子又不马上发表，建议存为草稿
您目前还是游客，请
同步到:QQ帐号未绑定，&QQ空间
新浪微博未绑定，&新浪微博 QQ微博未绑定，&腾讯微博
教鱼友做一道鱼胶鲜菇炖鸡，这种天气吃最滋补了！（步骤图）
老家镇上赶集，带鱼友们尝一尝家乡特色美食，你们吃过吗？
出海捕鱼探访中国第一渔村，明天开始又可以出海捕鱼了。
还没有账号？
扫一扫下载客户端
关注小鱼微信帐号
版权所有，并保留所有权利。EXCEL 模板 自动调整行高 - lineageII - ITeye博客
博客分类：
package com.testritegroup.datahub.print.
import java.io.F
import java.io.FileInputS
import java.io.FileOutputS
import java.io.IOE
import java.io.InputS
import java.util.ArrayL
import java.util.HashM
import java.util.I
import java.util.L
import java.util.M
import java.util.TreeM
import java.util.Map.E
import java.util.regex.P
import mons.logging.L
import mons.logging.LogF
import org.apache.poi.hssf.usermodel.HSSFW
import org.apache.poi.ss.usermodel.C
import org.apache.poi.ss.usermodel.CellS
import org.apache.poi.ss.usermodel.ClientA
import org.apache.poi.ss.usermodel.CreationH
import org.apache.poi.ss.usermodel.DateU
import org.apache.poi.ss.usermodel.D
import org.apache.poi.ss.usermodel.F
import org.apache.poi.ss.usermodel.P
import org.apache.poi.ss.usermodel.R
import org.apache.poi.ss.usermodel.S
import org.apache.poi.ss.usermodel.W
import org.apache.poi.ss.usermodel.WorkbookF
import org.apache.poi.ss.util.CellRangeA
import org.apache.poi.ss.util.CellR
import org.apache.poi.util.IOU
import org.apache.poi.xssf.usermodel.XSSFCellS
import org.apache.poi.xssf.usermodel.XSSFC
import org.apache.poi.xssf.usermodel.XSSFW
* @className:POIExcelUtil.java
* @classDescription:POI操作类
public class POIExcelUtil {
private static Log log = LogFactory.getLog(POIExcelUtil.class);
// ------------------------写Excel-----------------------------------
* 创建workBook对象 xlsx(2007以上版本)
public static Workbook createWorkbook() {
return createWorkbook(false);
* 创建WorkBook对象
* @param flag
true:xlsx() false:xls(2007以下)
public static Workbook createWorkbook(boolean flag) {
if (flag) {
wb = new XSSFWorkbook();
wb = new HSSFWorkbook();
* 添加图片
* @param wb
workBook对象
* @param sheet
* @param picFileName
图片文件名称（全路径）
* @param picType
* @param row
图片所在的行
* @param col
图片所在的列
public static void addPicture(Workbook wb, Sheet sheet, String picFileName,
int picType, int row, int col) {
InputStream is =
// 读取图片
is = new FileInputStream(picFileName);
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = wb.addPicture(bytes, picType);
is.close();
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
// 设置图片的位置
anchor.setCol1(col);
anchor.setRow1(row);
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize();
} catch (Exception e) {
if (is != null) {
is.close();
} catch (IOException e1) {
e1.printStackTrace();
e.printStackTrace();
* 创建Cell 默认为水平和垂直方式都是居中
* @param style
CellStyle对象
* @param row
* @param column
单元格所在的列
public static Cell createCell(CellStyle style, Row row, short column) {
return createCell(style, row, column, XSSFCellStyle.ALIGN_CENTER,
XSSFCellStyle.ALIGN_CENTER);
* 创建Cell并设置水平和垂直方式
* @param style
CellStyle对象
* @param row
* @param column
单元格所在的列
* @param halign
水平对齐方式：XSSFCellStyle.VERTICAL_CENTER.
* @param valign
垂直对齐方式：XSSFCellStyle.ALIGN_LEFT
public static Cell createCell(CellStyle style, Row row, short column,
short halign, short valign) {
Cell cell = row.createCell(column);
setAlign(style, halign, valign);
cell.setCellStyle(style);
* 合并单元格
* @param sheet
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
public static void mergeCell(Sheet sheet, int firstRow, int lastRow,
int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol,
lastCol));
// ---------------------------------设置样式-----------------------
* 设置单元格对齐方式
* @param style
* @param halign
* @param valign
public static CellStyle setAlign(CellStyle style, short halign, short valign) {
style.setAlignment(halign);
style.setVerticalAlignment(valign);
* 设置单元格边框(四个方向的颜色一样)
* @param style
* @param borderStyle
边框类型 ：dished-虚线 thick-加粗 double-双重 dotted-有点的
CellStyle.BORDER_THICK
* @param borderColor
颜色 IndexedColors.GREEN.getIndex()
public static CellStyle setBorder(CellStyle style, short borderStyle,
short borderColor) {
// 设置底部格式（样式+颜色）
style.setBorderBottom(borderStyle);
style.setBottomBorderColor(borderColor);
// 设置左边格式
style.setBorderLeft(borderStyle);
style.setLeftBorderColor(borderColor);
// 设置右边格式
style.setBorderRight(borderStyle);
style.setRightBorderColor(borderColor);
// 设置顶部格式
style.setBorderTop(borderStyle);
style.setTopBorderColor(borderColor);
* 自定义颜色（xssf)
* @param style
* @param red
RGB red (0-255)
* @param green
RGB green (0-255)
* @param blue
RGB blue (0-255)
public static CellStyle setBackColorByCustom(XSSFCellStyle style, int red,
int green, int blue) {
// 设置前端颜色
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(red,
green, blue)));
// 设置填充模式
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
* 设置前景颜色
* @param style
* @param color
：IndexedColors.YELLOW.getIndex()
public static CellStyle setBackColor(CellStyle style, short color) {
// 设置前端颜色
style.setFillForegroundColor(color);
// 设置填充模式
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
* 设置背景颜色
* @param style
* @param color
：IndexedColors.YELLOW.getIndex()
* @param fillPattern
：CellStyle.SPARSE_DOTS
public static CellStyle setBackColor(CellStyle style, short backColor,
short fillPattern) {
// 设置背景颜色
style.setFillBackgroundColor(backColor);
// 设置填充模式
style.setFillPattern(fillPattern);
* 设置字体（简单的需求实现，如果复杂的字体，需要自己去实现）尽量重用
* @param style
* @param fontSize
字体大小 shot(24)
* @param color
字体颜色 IndexedColors.YELLOW.getIndex()
* @param fontName
字体名称 "Courier New"
public static CellStyle setFont(Font font, CellStyle style, short fontSize,
short color, String fontName) {
font.setFontHeightInPoints(color);
font.setFontName(fontName);
// font.setItalic(true);// 斜体
// font.setStrikeout(true);//加干扰线
font.setColor(color);// 设置颜色
// Fonts are set into a style so create a new one to use.
style.setFont(font);
* @param createHelper
createHelper对象
* @param style
CellStyle对象
* @param formartData
date:"m/d/yy h:mm"; int:"#,###.0000" ,"0.0"
public static CellStyle setDataFormat(CreationHelper createHelper,
CellStyle style, String formartData) {
style.setDataFormat(createHelper.createDataFormat().getFormat(
formartData));
* 将Workbook写入文件
* @param wb
workbook对象
* @param fileName
文件的全路径
public static boolean createExcel(Workbook wb, String fileName) {
boolean flag =
FileOutputStream fileOut =
File file = new File(fileName);
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
if (fileOut != null) {
fileOut.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
e.printStackTrace();
// --------------------读取Excel-----------------------
* 读取Excel
* @param filePathName
public static Workbook readExcel(String filePathName) {
InputStream inp =
Workbook wb =
inp = new FileInputStream(filePathName);
wb = WorkbookFactory.create(inp);
inp.close();
} catch (Exception e) {
if (null != inp) {
inp.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
e.printStackTrace();
* 读取Cell的值
* @param sheet
public static Map readCell(Sheet sheet) {
Map map = new HashMap();
// 遍历所有行
for (Row row : sheet) {
// 便利所有列
for (Cell cell : row) {
// 获取单元格的类型
CellReference cellRef = new CellReference(row.getRowNum(),
cell.getColumnIndex());
// System.out.print(cellRef.formatAsString());
String key = cellRef.formatAsString();
// System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
map.put(key, cell.getRichStringCellValue().getString());
// System.out.println(cell.getRichStringCellValue()
// .getString());
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// System.out.println(cell.getDateCellValue());
map.put(key, cell.getDateCellValue());
// System.out.println(cell.getNumericCellValue());
map.put(key, cell.getNumericCellValue());
// boolean
case Cell.CELL_TYPE_BOOLEAN:
// System.out.println(cell.getBooleanCellValue());
map.put(key, cell.getBooleanCellValue());
case Cell.CELL_TYPE_FORMULA:
// System.out.println(cell.getCellFormula());
map.put(key, cell.getCellFormula());
System.out.println();
map.put(key, "");
* 替换明细内容
* @param sheet
* @param detailList 明细列表
private static void replaceDetail(Workbook workbook, Sheet sheet, String detailKey, List&Map&String, String&& detailList) {
// 找到 #detail 的位置，复制该行到到下一行
if(detailList == null || detailList.size() == 0)
int detailSize = detailList.size();
Cell detailCell = findCell(sheet, detailKey);
if(detailCell == null)
// #detail 所在行号
int detailRow = detailCell.getRowIndex();
// #detail 所在列号
int detailColunm = detailCell.getColumnIndex();
for(int i = 1; i & detailS i++){
copyRow(workbook, sheet, detailRow, detailRow + i);
int[] rownum = getDetailColunmNum(sheet, detailRow,detailColunm, detailList.get(0).size());
for(Map&String, String& detailMap : detailList){
int colunm = 0;
Row thisRow = sheet.getRow(detailRow);
float defaultRowHeight = thisRow.getHeightInPoints();
float maxHeight = defaultRowH
for(Entry&String, String& entry: detailMap.entrySet()){
Cell thisCell = thisRow.getCell(rownum[colunm]);
float thisHeight = getExcelCellAutoHeight(entry.getValue(),defaultRowHeight, getMergedCellNum(thisCell) * sheet.getColumnWidth(rownum[colunm])/256);
if(thisHeight & maxHeight) maxHeight = thisH
if(thisCell == null) {
thisCell = thisRow.createCell(rownum[colunm]);
thisCell.setCellValue(entry.getValue());
thisCell.setCellValue(entry.getValue());
thisCell.getCellStyle().setWrapText(true);
thisRow.setHeightInPoints(maxHeight);
detailRow++;
private static int[] getDetailColunmNum(Sheet sheet, int detailRow, int detalColunm, int detailMapSize){
int[] rowNum = new int[detailMapSize];
int mergedRow = 0;
int j = 0;
for(int i = 0; i & detailMapSize + mergedRi++){
Cell cell = sheet.getRow(detailRow).getCell(i+detalColunm);
if(isMergedRegionNotFirstCell(cell)){
mergedRow++;
rowNum[j++] = i+detalC
return rowN
* 判断当前单元格是合并单元格的非第一行
* @param cell
private static int getMergedCellNum(Cell cell) {
if(cell == null) return 1;
Sheet sheet = cell.getSheet();
CellRangeAddress range =
int mergedNum = sheet.getNumMergedRegions();
for (int i = 0; i & mergedN i++) {
range = sheet.getMergedRegion(i);
if(cell.getColumnIndex() == range.getFirstColumn() && cell.getRowIndex() == range.getFirstRow()){
return range.getLastColumn() - range.getFirstColumn() + 1;
* 判断当前单元格是合并单元格的非第一行
* @param cell
private static boolean isMergedRegionNotFirstCell(Cell cell) {
if(cell == null)
Sheet sheet = cell.getSheet();
CellRangeAddress range =
int mergedNum = sheet.getNumMergedRegions();
for (int i = 0; i & mergedN i++) {
range = sheet.getMergedRegion(i);
if(cell.getColumnIndex() & range.getFirstColumn()
&& cell.getColumnIndex() &= range.getLastColumn()
&& cell.getRowIndex() == range.getFirstRow()){
* 替换文本
* @param sheet
* @param replaceTextMap
替换文本MAP key=原字符串 value=替换字符串
private static void replaceHeader(Sheet sheet, Map&String, String& replaceTextMap) {
// 遍历所有行
for (Row thisRow : sheet) {
boolean isFound =
// 便利所有列
float defaultRowHeight = thisRow.getHeightInPoints();
float maxHeight = defaultRowH
for (Cell thisCell : thisRow) {
// 获取单元格的类型
CellReference cellRef = new CellReference(thisRow.getRowNum(),
thisCell.getColumnIndex());
switch (thisCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
String targetText = thisCell.getRichStringCellValue()
.getString();
if (targetText != null && !targetText.trim().equals("")) {
if(replaceTextMap.containsKey(targetText)){
float thisHeight = getExcelCellAutoHeight((String)replaceTextMap.get(targetText),defaultRowHeight, getMergedCellNum(thisCell) * sheet.getColumnWidth(thisCell.getColumnIndex())/256);
if(thisHeight & maxHeight) maxHeight = thisH
thisCell.setCellValue((String)replaceTextMap.get(targetText));
thisCell.getCellStyle().setWrapText(true);
(" Sheet[" + sheet.getSheetName() + "]"
+ "行:" + (thisCell.getRowIndex() + 1) + "列:"
+ getColLetter(thisCell.getColumnIndex()) + " "
+ targetText + " replace " + targetText + " -& "
+ (String)replaceTextMap.get(targetText));
case Cell.CELL_TYPE_NUMERIC:
// boolean
case Cell.CELL_TYPE_BOOLEAN:
case Cell.CELL_TYPE_FORMULA:
if(isFound) thisRow.setHeightInPoints(maxHeight);
* 替换文本
* @param sheet
* @param key
* @param value
替换字符串
private static Cell findCell(Sheet sheet, String findStr) {
// 遍历所有行
for (Row row : sheet) {
// 便利所有列
for (Cell cell : row) {
// 获取单元格的类型
CellReference cellRef = new CellReference(row.getRowNum(),
cell.getColumnIndex());
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
String targetText = cell.getRichStringCellValue()
.getString();
if (targetText != null && !targetText.trim().equals("")) {
if (targetText.contains(findStr))
case Cell.CELL_TYPE_NUMERIC:
// boolean
case Cell.CELL_TYPE_BOOLEAN:
case Cell.CELL_TYPE_FORMULA:
* 将列的索引换算成ABCD字母，这个方法要在插入公式时用到。
* @param colIndex
* @return ABCD字母。
private static String getColLetter(int colIndex) {
String ch = "";
if (colIndex & 26)
ch = "" + (char) ((colIndex) + 65);
ch = "" + (char) ((colIndex) / 26 + 65 - 1)
+ (char) ((colIndex) % 26 + 65);
* 遍历文件夹下的文件，并打印文件夹下文件的路径
* @param directoryPath 文件夹目录
public static void traversal(List&String& filesPathList, String directoryPath) {
File dir = new File(directoryPath);
File[] files = dir.listFiles();
if (files == null) {
for (int i = 0; i & files. i++) {
if (files[i].isDirectory()) {
traversal(filesPathList, files[i].getAbsolutePath());
filesPathList.add(files[i].getAbsolutePath());
private static void copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) {
// Get the source / new row
Row newRow = worksheet.getRow(destinationRowNum);
Row sourceRow = worksheet.getRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null) {
worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
newRow = worksheet.createRow(destinationRowNum);
// Loop through source columns to add to new row
for (int i = 0; i & sourceRow.getLastCellNum(); i++) {
// Grab a copy of the old/new cell
Cell oldCell = sourceRow.getCell(i);
Cell newCell = newRow.createCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) {
// Copy style from old cell and apply to new cell
CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);
// If there is a cell comment, copy
if (newCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
// If there is a cell hyperlink, copy
if (oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
// Set the cell data type
newCell.setCellType(oldCell.getCellType());
// Set the cell data value
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i & worksheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
(newRow.getRowNum() +
(cellRangeAddress.getFirstRow() -
cellRangeAddress.getLastRow())),
cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
worksheet.addMergedRegion(newCellRangeAddress);
public static float getExcelCellAutoHeight(String str,float defaultRowHeight, int fontCountInline) {
int defaultCount = 0;
for (int i = 0; i & str.length(); i++) {
int ff = getregex(str.substring(i, i + 1));
defaultCount = defaultCount +
if (defaultCount & fontCountInline){
return ((int) (defaultCount / fontCountInline) + 1) * defaultRowH//计算
return defaultRowH
public static int getregex(String charStr) {
if(charStr==" ")
// 判断是否为字母或字符
if (pile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
// 判断是否为全角
if (pile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
//全角符号 及中文
if (pile("[^x00-xff]").matcher(charStr).matches()) {
* @param args
* @throws Exception
public static void main(String[] args) throws Exception {
Map&String, String& replaceTextMap = new HashMap&String, String&();
replaceTextMap.put("#寄单地址", "[寄单地址厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号]");
replaceTextMap.put("#厂商编号", "[厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号]");
replaceTextMap.put("#厂商名称", "[厂商名称厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号]");
Workbook wb = readExcel("F:\\PO差异报表.xls");
Sheet sheet = wb.getSheetAt(0);
replaceHeader(sheet, replaceTextMap);
List&Map&String, String&& detailList = new ArrayList&Map&String,String&&();
Map&String,String& detailMap = new TreeMap&String, String&();
detailMap.put("ID11", "厂商编号");
detailMap.put("ID12", "厂商编号");
detailMap.put("ID13", "厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号厂商编号");
detailMap.put("ID14", "ID14");
detailMap.put("ID15", "ID15");
detailMap.put("ID16", "ID16");
detailList.add(detailMap);
detailMap = new TreeMap&String, String&();
detailMap.put("ID21", "ID21");
detailMap.put("ID22", "ID22");
detailMap.put("ID23", "ID23");
detailMap.put("ID24", "ID24");
detailMap.put("ID25", "ID25");
detailMap.put("ID26", "ID26");
detailList.add(detailMap);
replaceDetail(wb, sheet, "#detail", detailList);
replaceDetail(wb, sheet, "#detail1", detailList);
createExcel(wb, "F:\\PO差异报表_replace.xls");
浏览: 55117 次
看来这样的账号不能公开，公开就被人改了。为什么有些人就这么自私 ...
这个能行吗？哪个用过了给个评价吧。
人生如戏 不如说戏源于人生而致人生如戏
1：31一年多过去了，我还是没有什么变化 ...
这样做的目的，不了解。}