本文保存一些个人封装的常用的POI导入导出方法，随时调整
/**
* 验证excel是否全部为空
*
* @param row 当前行
* @param firstRow 第一行标题行
* @return
*/
public static boolean isAllRowEmpty(Row row, Row firstRow) {
if (row == null) {
return true;
}
int count = 0;
//单元格数量
int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
//判断多少个单元格为空
for (int c = 0; c < rowCount; c++) {
Cell cell = row.getCell(c);
if (cell == null || cell.getCellType() == CellType.BLANK || StrUtil.isEmpty((cell + "").trim())) {
count += 1;
}
}
if (count == rowCount) {
return true;
}
return false;
}
private static Object getCellValue(Cell cell, Class fieldType) {
Object result = null;
CellType cellType = cell.getCellType();
switch (cellType) {
case _NONE:
break;
case BLANK:
break;
case STRING:
if (fieldType.equals(Integer.class)) {
DecimalFormat decimalFormat = new DecimalFormat("#");
result = Integer.valueOf(decimalFormat.format(cell.getNumericCellValue()));
} else {
result = cell.getStringCellValue().trim();
}
break;
case NUMERIC:
if (fieldType.equals(LocalDate.class)) {
result = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
} else if (fieldType.equals(LocalDateTime.class)) {
result = cell.getLocalDateTimeCellValue();
} else if (fieldType.equals(String.class)) {
DecimalFormat decimalFormat = new DecimalFormat("#");
result = decimalFormat.format(cell.getNumericCellValue());
} else if (fieldType.equals(Integer.class)) {
result = Integer.valueOf((int)cell.getNumericCellValue());
} else if (fieldType.equals(BigDecimal.class)) {
result = BigDecimal.valueOf(cell.getNumericCellValue());
} else {
result = cell.getNumericCellValue();
}
break;
case FORMULA:
break;
case BOOLEAN:
break;
case ERROR:
break;
}
return result;
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框(隐式数据域).
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static void setXSSFValidation(Workbook wb,List<Sheet> hiddenSheetList,Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = null;
int charNum = Arrays.stream(textlist).mapToInt(String::length).sum();
if (charNum < 256){
constraint = helper.createExplicitListConstraint(textlist);
}else {
// 下拉列表太多 设置隐藏Sheet存储值
String hiddenSheetName = UUID.randomUUID().toString().substring(1,16);
Sheet hiddenSheet = wb.createSheet(hiddenSheetName);
for (int i = 0; i < textlist.length; i++) {
Row row = hiddenSheet.createRow(i);
Cell cell = row.createCell(0);
String cat = textlist[i];
cell.setCellValue(cat);
}
hiddenSheetList.add(hiddenSheet);
hiddenSheet.protectSheet("passw0rd");
wb.setSheetHidden(hiddenSheetList.size(),true);
String hiddenSheetScope = "'"+hiddenSheetName+"'"+"!$A$1:$A$"+textlist.length;
constraint = helper.createFormulaListConstraint(hiddenSheetScope);
}
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
// 填充 单元格图片
public static void setPictureCell(String pictureUrl, Cell cell, CellStyle bodyCellStyle) {
if (bodyCellStyle != null) {
cell.setCellStyle(bodyCellStyle);
}
if (StrUtil.isEmpty(pictureUrl)) {
return;
}
ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
// 此处是把网络图片进行解析为byte数组
byte[] imgData = ImageUtils.getImage(pictureUrl);
ImageUtils.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
cell.getSheet().getWorkbook().addPicture(imgData, ImageUtils.getImageType(imgData)));
}
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.Arrays;
public class ImageUtils {
private static final Logger log = LoggerFactory.getLogger(ImageUtils.class);
/**
* 获取文件类型
*
* @param photoByte 文件字节码
* @return 后缀（不含".")
*/
public static String getFileExtendName(byte[] photoByte)
{
String strFileExtendName = "JPG";
if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
&& ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97))
{
strFileExtendName = "GIF";
}
else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70))
{
strFileExtendName = "JPG";
}
else if ((photoByte[0] == 66) && (photoByte[1] == 77))
{
strFileExtendName = "BMP";
}
else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71))
{
strFileExtendName = "PNG";
}
return strFileExtendName;
}
/**
* 获取图片类型,设置图片插入类型
*/
public static int getImageType(byte[] value)
{
String type = ImageUtils.getFileExtendName(value);
if ("JPG".equalsIgnoreCase(type))
{
return Workbook.PICTURE_TYPE_JPEG;
}
else if ("PNG".equalsIgnoreCase(type))
{
return Workbook.PICTURE_TYPE_PNG;
}
return Workbook.PICTURE_TYPE_JPEG;
}
/**
* 获取画布
*/
public static Drawing<?> getDrawingPatriarch(Sheet sheet)
{
if (sheet.getDrawingPatriarch() == null)
{
sheet.createDrawingPatriarch();
}
return sheet.getDrawingPatriarch();
}
public static byte[] getImage(String imagePath)
{
InputStream is = getFile(imagePath);
try
{
return IOUtils.toByteArray(is);
}
catch (Exception e)
{
log.error("图片加载异常 {}", e);
return null;
}
finally
{
IOUtils.closeQuietly(is);
}
}
public static InputStream getFile(String imagePath)
{
try
{
byte[] result = readFile(imagePath);
result = Arrays.copyOf(result, result.length);
return new ByteArrayInputStream(result);
}
catch (Exception e)
{
log.error("获取图片异常 {}", e);
}
return null;
}
/**
* 读取文件为字节数据
*
* @param url 地址
* @return 字节数据
*/
public static byte[] readFile(String url)
{
InputStream in = null;
try
{
if (url.startsWith("http"))
{
// 网络地址
URL urlObj = new URL(url);
URLConnection urlConnection = urlObj.openConnection();
urlConnection.setConnectTimeout(30 * 1000);
urlConnection.setReadTimeout(60 * 1000);
urlConnection.setDoInput(true);
in = urlConnection.getInputStream();
}
return IOUtils.toByteArray(in);
}
catch (Exception e)
{
log.error("获取文件路径异常 {}", e);
return null;
}
finally
{
IOUtils.closeQuietly(in);
}
}
}