org.dromara.hutool.poi.excel.SheetUtil Maven / Gradle / Ivy

Go to download
/*
 * Copyright (c) 2013-2024 Hutool Team and hutool.cn
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.dromara.hutool.poi.excel;

import org.apache.poi.ss.usermodel.IgnoredErrorType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.cellwalk.CellHandler;
import org.apache.poi.ss.util.cellwalk.CellWalk;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.dromara.hutool.core.reflect.FieldUtil;

/**
 * {@link Sheet}相关工具类
 *
 * @author Looly
 * @since 6.0.0
 */
public class SheetUtil {

	/**
	 * 获取或者创建sheet表

	 * 如果sheet表在Workbook中已经存在，则获取之，否则创建之
	 *
	 * @param book      工作簿{@link Workbook}
	 * @param sheetName 工作表名，{@code null}表示默认
	 * @return 工作表{@link Sheet}
	 * @since 4.0.2
	 */
	public static Sheet getOrCreateSheet(final Workbook book, final String sheetName) {
		if (null == book) {
			return null;
		}

		Sheet sheet;
		if (null == sheetName) {
			sheet = getOrCreateSheet(book, 0);
		} else {
			sheet = book.getSheet(sheetName);
			if (null == sheet) {
				sheet = book.createSheet(sheetName);
			}
		}
		return sheet;
	}

	/**
	 * 获取或者创建sheet表

	 * 自定义需要读取或写出的Sheet，如果给定的sheet不存在，创建之（命名为默认）

	 * 在读取中，此方法用于切换读取的sheet，在写出时，此方法用于新建或者切换sheet
	 *
	 * @param book       工作簿{@link Workbook}
	 * @param sheetIndex 工作表序号
	 * @return 工作表{@link Sheet}
	 * @since 5.2.1
	 */
	public static Sheet getOrCreateSheet(final Workbook book, final int sheetIndex) {
		Sheet sheet = null;
		try {
			sheet = book.getSheetAt(sheetIndex);
		} catch (final IllegalArgumentException ignore) {
			//ignore
		}
		if (null == sheet) {
			sheet = book.createSheet();
		}
		return sheet;
	}

	/**
	 * sheet是否为空
	 *
	 * @param sheet {@link Sheet}
	 * @return sheet是否为空
	 * @since 4.0.1
	 */
	public static boolean isEmpty(final Sheet sheet) {
		return null == sheet || (sheet.getLastRowNum() == 0 && sheet.getPhysicalNumberOfRows() == 0);
	}

	/**
	 * 遍历Sheet中的所有单元格
	 *
	 * @param sheet       {@link Sheet}
	 * @param cellHandler 单元格处理器
	 */
	public static void walk(final Sheet sheet, final CellHandler cellHandler) {
		walk(sheet,
			new CellRangeAddress(0, sheet.getLastRowNum(), 0, sheet.getLastRowNum()),
			cellHandler);
	}

	/**
	 * 遍历Sheet中的指定区域单元格
	 *
	 * @param sheet       {@link Sheet}
	 * @param range       区域
	 * @param cellHandler 单元格处理器
	 */
	public static void walk(final Sheet sheet, final CellRangeAddress range, final CellHandler cellHandler) {
		final CellWalk cellWalk = new CellWalk(sheet, range);
		cellWalk.traverse(cellHandler);
	}

	/**
	 * 设置忽略错误，即Excel中的绿色警告小标，只支持XSSFSheet和SXSSFSheet

	 * 见：https://stackoverflow.com/questions/23488221/how-to-remove-warning-in-excel-using-apache-poi-in-java
	 *
	 * @param sheet             {@link Sheet}
	 * @param cellRangeAddress  指定单元格范围
	 * @param ignoredErrorTypes 忽略的错误类型列表
	 * @throws UnsupportedOperationException 如果sheet不是XSSFSheet
	 * @since 5.8.28
	 */
	public static void addIgnoredErrors(final Sheet sheet, final CellRangeAddress cellRangeAddress, final IgnoredErrorType... ignoredErrorTypes) throws UnsupportedOperationException {
		if (sheet instanceof XSSFSheet) {
			((XSSFSheet) sheet).addIgnoredErrors(cellRangeAddress, ignoredErrorTypes);
		} else if (sheet instanceof SXSSFSheet) {
			// SXSSFSheet并未提供忽略错误方法，获得其内部_sh字段设置
			final XSSFSheet xssfSheet = (XSSFSheet) FieldUtil.getFieldValue(sheet, "_sh");
			if (null != xssfSheet) {
				xssfSheet.addIgnoredErrors(cellRangeAddress, ignoredErrorTypes);
			}
		} else {
			throw new UnsupportedOperationException("Only XSSFSheet supports addIgnoredErrors");
		}
	}

	/**
	 * 获取指定坐标点对应的合并单元格范围
	 *
	 * @param sheet {@link Sheet}
	 * @param x     x坐标，即列号
	 * @param y     行号
	 * @return CellRangeAddress or null
	 */
	public static CellRangeAddress getMergedRegion(final Sheet sheet, final int x, final int y) {
		for (final CellRangeAddress ca : sheet.getMergedRegions()) {
			if (ca.isInRange(y, x)) {
				return ca;
			}
		}
		return null;
	}
}