我用的maven是：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>1.ExcelUtil.java工具类，其中有合并单元格的判断，取值，单元格格式设置等
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
/**
* Created by wyc on 2019/5/29.
*/
public class ExcelUtil {
private static ExcelUtil instance = null;
private SXSSFWorkbook wb = null;
private Sheet sheet = null;
/**
* 单例模式获取实例
* @return ExcelUtil对象
*/
public static ExcelUtil getInstance(){
if (instance == null)
instance = new ExcelUtil();
return instance;
}
public ExcelUtil() {
}
public ExcelUtil(SXSSFWorkbook wb, Sheet sheet) {
this.wb = wb;
this.sheet = sheet;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public 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 sheet
* @param row
* @param column
* @return
*/
public int[] getMergedLatitudeArray(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
int[] latitude = {column,column,row,row,};
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){
int[] mergedLatitude = {firstColumn,lastColumn,firstRow,lastRow};
return mergedLatitude ;
}
}
}
return latitude ;
}
/**
* 判断合并了行
* @param sheet
* @param row
* @param column
* @return
*/
public 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 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;
}
/**
* 判断sheet页中是否含有合并单元格
* @param sheet
* @return
*/
public boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
public void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public 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 "";
}
/**
* 从excel读取内容
*/
public static void readContent(String fileName) {
boolean isE2007 = false; //判断是否是excel2007格式
if(fileName.endsWith("xlsx"))
isE2007 = true;
try {
InputStream input = new FileInputStream(fileName); //建立输入流
Workbook wb = null;
//根据文件格式(2003或者2007)来初始化
if(isE2007)
wb = new XSSFWorkbook(input);
else
wb = new HSSFWorkbook(input);
Sheet sheet = wb.getSheetAt(0); //获得第一个表单
Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
while (rows.hasNext()) {
Row row = rows.next(); //获得行数据
System.out.println("Row #" + row.getRowNum()); //获得行号从0开始
Iterator<Cell> cells = row.cellIterator(); //获得第一行的迭代器
while (cells.hasNext()) {
Cell cell = cells.next();
System.out.println("Cell #" + cell.getColumnIndex());
switch (cell.getCellType()) { //根据cell中的类型来输出数据
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println("unsuported sell type======="+cell.getCellType());
break;
}
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
// 设置表头的单元格样式
public CellStyle getHeadStyle() {
// 创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
// 设置填充字体的样式
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体的样式
font.setFontHeight(14);// 设置字体的大小
cellStyle.setFont(font);// 将字体填充到表格中去
// 设置单元格边框为细线条（上下左右）
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
// 设置表体的单元格样式
public CellStyle getBodyStyle() {
// 创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(false);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体
font.setFontHeight(12);// 设置字体的大小
cellStyle.setFont(font);// 将字体添加到表格中去
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public Cell setHeadStyle(String[] titles) {
Cell cell = null;
CellStyle headStyle = getHeadStyle();
headStyle.setWrapText(false);
Row headRow = sheet.createRow(0);
// 构建表头
for (int i = 0; i < titles.length; i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
return cell;
}
// 设置表头的单元格样式
public CellStyle getHeadStyle(SXSSFWorkbook workbook) {
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
// 设置填充字体的样式
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) workbook.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体的样式
font.setFontHeight(12);// 设置字体的大小
cellStyle.setFont(font);// 将字体填充到表格中去
// 设置单元格边框为细线条（上下左右）
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
// 设置表体的单元格样式
public CellStyle getBodyStyle(SXSSFWorkbook workbook) {
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容不显示自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = (XSSFFont) workbook.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗
font.setFontName("宋体");// 设置字体
font.setFontHeight(10);// 设置字体的大小
cellStyle.setFont(font);// 将字体添加到表格中去
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public Cell setHeadStyle(String[] titles, SXSSFWorkbook workbook) {
Cell cell = null;
CellStyle headStyle = getHeadStyle(workbook);
Row headRow = sheet.createRow(0);
// 构建表头
for (int i = 0; i < titles.length; i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
return cell;
}
/**
* 列头单元格样式
*/
public CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 18);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}2.ExcelMergedRegionParser解析多表头导入，以最后一行表头为准，根据阈值判断数值类型，找到最后一行表头
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.usermodel.Workbook;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.util.*;
public class ExcelMergedRegionParser {
// 阈值 临界值
private static int THRESHOLD = 3;
// 数据行起始位置，默认为-1没有符合条件的数据行
private static int dataLineNum = -1;
// Excel对象
private static Workbook workbook = null;
// sheet下标，默认为0
private static int sheetNum = 0;
/**
*
* @param workbook Excel对象
*/
public ExcelMergedRegionParser(Workbook workbook){
this.workbook = workbook;
}
/**
*
* @param workbook Excel对象
* @param sheetNum sheet下标
*/
public ExcelMergedRegionParser(Workbook workbook, int sheetNum){
this.workbook = workbook;
this.sheetNum = sheetNum;
}
/**
*
* @param workbook Excel对象
* @param sheetNum sheet下标
* @param THRESHOLD 临界值
*/
public ExcelMergedRegionParser(Workbook workbook, int sheetNum, int THRESHOLD){
this.workbook = workbook;
this.sheetNum = sheetNum;
this.THRESHOLD = THRESHOLD;
}
/**
*
* @return 返回解析后的EXCEL最后一行汉字标题，有序
*/
public List<String> getHeaderList(){
List<String> headList = new ArrayList<>();
// 获取sheet页签
Sheet sheet = workbook.getSheetAt(sheetNum);
// 获取工具类实例
ExcelUtil excelUtil = ExcelUtil.getInstance();
// 该sheet中是否含有合并单元格
if (excelUtil.hasMerged(sheet)){
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0;i<rows;i++){
int count = 0;
Row row = sheet.getRow(i);
if(row == null)
continue;
int cells = row.getPhysicalNumberOfCells();
for(int j = 0 ;j<cells;j++){
Cell cell = row.getCell(j);
// 如果有一列为null，则跳过
if (cell == null)
continue;
// 判断是数字类型就加1
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
count++;
}
// 如果计数器大于阈值说明找到了第一个数据行
if (count >= THRESHOLD){
// 数据行所在位置
dataLineNum = i;
// 声明一个有序去重set集合
Set<String> setList = new LinkedHashSet<>();
row = sheet.getRow(i-1);
// 注释的代码，是为了防止阈值为0时报NULLpoint异常
if (row == null)
continue;
cells = row.getPhysicalNumberOfCells();
for(int j = 0 ;j<cells;j++){
Cell cell = row.getCell(j);
// 判断如果有空就跳过，这里加1是为了弥补损失
if (cell == null){
cells++;
continue;
}
// 判断每一个单元格是否为合并单元格
boolean flag = excelUtil.isMergedRegion(sheet,i-1,j);
String value = "";
// true
if(flag){
// 获取合并单元格的值
value = excelUtil.getMergedRegionValue(sheet,i-1,j);
setList.add(value);
}else {
value = excelUtil.getCellValue(cell);
setList.add(value);
}
}
// 将set集合转化为list集合
headList = new ArrayList<>(setList) ;
break;
}
}
}else{
// 获取最大物理行行数
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0;i<rows;i++){
// 数值类型计数器
int count = 0;
Row row = sheet.getRow(i);
if(row == null)
continue;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()){
Cell cell = cellIterator.next();
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
// 每次加1
count++;
}
// 如果计数器大于阈值说明找到了第一个数据行
if (count >= THRESHOLD){
// 数据行位置
dataLineNum = i;
// 获取标题行位置
row = sheet.getRow(i-1);
// 注释的代码，是为了防止阈值为0时报NULLpoint异常
if (row == null)
continue;
Iterator<Cell> cellSpecIterator = row.cellIterator();
while (cellSpecIterator.hasNext()){
Cell cell = cellSpecIterator.next();
String value = excelUtil.getCellValue(cell);
headList.add(value);
}
break;
}
}
}
return headList;
}
/**
*
* @return 数据行下标
*/
public int getDataLineNum(){
return this.dataLineNum;
}
/**
* 得到用户自定义表头布局
* @return 返回递归得到的标题List
*/
public List<CellBean> getHeaderLayout(){
List<CellBean> headList = new ArrayList<>();
// 获取sheet页签
Sheet sheet = workbook.getSheetAt(sheetNum);
// 获取最大物理行行数
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0;i<rows;i++){
int count = 0;
Row row = sheet.getRow(i);
if(row == null)
continue;
int cells = row.getPhysicalNumberOfCells();
for(int j = 0 ;j<cells;j++){
Cell cell = row.getCell(j);
// 如果有一列为null，则跳过
if (cell == null)
continue;
// 判断是数字类型就加1
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
count++;
}
// 如果计数器大于阈值说明找到了第一个数据行
if (count >= THRESHOLD){
// 数据行所在位置
dataLineNum = i;
// 进行递归拿到标题List<CellBean>
headList = getCellBean(0,cells,0,0) ;
break;
}
}
return headList;
}
/**
*
* @param firstColumn 循环开始列
* @param lastColumn 循环结束咧
* @param firstRow 循环开始行
* @param lastRow 循环结束行
* @return
*/
public List<CellBean> getCellBean(int firstColumn, int lastColumn, int firstRow, int lastRow){
// 递归结束条件 判断到达数据行结束
if (lastRow==dataLineNum)
return null;
List<CellBean> headList = new ArrayList<>();
// 获取sheet页签
Sheet sheet = workbook.getSheetAt(sheetNum);
// 获取工具类实例
ExcelUtil excelUtil = ExcelUtil.getInstance();
// 双重循环开始
for (int i = firstRow;i<=lastRow;i++){
Row row = sheet.getRow(i);
for (int j = firstColumn;j<=lastColumn;j++){
Cell cell = row.getCell(j);
// 如果是合并单元格就不重复进行赋值和递归了，如果合并单元格的值为空，那么细粒度标题也不会出现在List中
if (excelUtil.getCellValue(cell)==null||"".equals(excelUtil.getCellValue(cell)))
continue;
CellBean cellBean = new CellBean();
// 判断该单元格是否为合并单元格
if (excelUtil.isMergedRegion(sheet,i,j)){
// 获取合并单元格的值
cellBean.value = excelUtil.getMergedRegionValue(sheet,i,j);
// 获取合并单元格的二维坐标，同样也支持非合并单元格
cellBean.latitude = excelUtil.getMergedLatitudeArray(sheet,i,j);
// 这里对起始行和结束行都加1是为了对下一行进行递归
cellBean.list = getCellBean(cellBean.latitude[0],cellBean.latitude[1],cellBean.latitude[2]+1,cellBean.latitude[3]+1);
}else {
// 根据类型去单个单元格的值
cellBean.value = excelUtil.getCellValue(cell);
// 获取非合并单元格的二维坐标，同样也支持合并单元格
cellBean.latitude = excelUtil.getMergedLatitudeArray(sheet,i,j);
// 这里对起始行和结束行都加1是为了对下一行进行递归
cellBean.list = getCellBean(cellBean.latitude[0],cellBean.latitude[1],cellBean.latitude[2]+1,cellBean.latitude[3]+1);
}
headList.add(cellBean);
}
}
return headList;
}
/**
* 遍历List<CellBean>
* @param cellBeanList
*/
public void print(List<CellBean> cellBeanList){
if (cellBeanList == null){
return;
}
for (int i =0 ;i<cellBeanList.size();i++){
System.out.println(cellBeanList.get(i).value);
print(cellBeanList.get(i).list);
}
}
public static void main(String[] args) {
try {
// File f = new File("F:\\日常薪资导入模板.xls");
File f = new File("F:\\奖金筹划导入模板.xls");
FileInputStream fis = new FileInputStream(f);
BufferedInputStream bis = new BufferedInputStream(fis);
POIFSFileSystem fs = new POIFSFileSystem(bis);
HSSFWorkbook workbook = null;
workbook = new HSSFWorkbook(fs);
ExcelMergedRegionParser excelParserController = new ExcelMergedRegionParser(workbook,0,3);
List<String> list = excelParserController.getHeaderList();
for(int i = 0;i<list.size();i++){
System.out.println(list.get(i)+", ");
}
System.out.println(excelParserController.getDataLineNum());
excelParserController.print(excelParserController.getHeaderLayout());
}catch (Exception e){
e.printStackTrace();
}
}
}
3.CellBean实体类
import java.util.List;
public class CellBean {
// 开始列，结束列，开始行，结束行
int[] latitude;
String value;
List<CellBean> list;
}
672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
