java poi解析excel,带合并单元的excel
来源:互联网 发布:eview触摸屏编程软件 编辑:程序博客网 时间:2024/05/01 16:26
首先,mavn导入jar包
<!-- 解析excel需要导入的 jar包 begin -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- 解析excel需要导入的 jar包 end -->
其次,代码如下:
package excel;import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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 org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;
/**
*
* @author wcyong
*
* @date 2013-6-21
*/
public class TestReadMergeRegionExcel {
@Test
public void testReadExcel() {
readExcelToObj("C:\\Users\\Administrator\\Desktop\\11.xls");
}
/**
* 读取excel数据
* @param path
*/
private void readExcelToObj(String path) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(new File(path));
readExcel(wb, 0, 0, 0);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取excel文件
* @param wb
* @param sheetIndex sheet页下标:从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
*/
private void readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {
row = sheet.getRow(i);
for(Cell c : row) {
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//判断是否具有合并单元格
if(isMerge) {
String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
System.out.print(rs + " ");
}else {
System.out.print(getCellValue(c)+" ");
}
}
System.out.println();
}
}
/**
* 获取合并单元格的值
* @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
*/
private 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
*/
private 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
*/
private boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
private 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 "";
}
}
- java poi解析excel,带合并单元的excel
- java 解析excel,带合并单元的excel
- 用POI读取具有任意合并单元的excel数据
- java poi 解析excel
- java POI解析Excel
- java poi实现excel的解析
- Java POI 导入Excel以及解析Excel
- Java使用poi解析Excel
- java+poi解析excel文件
- java poi 解析excel数据
- java--poi生成excel动态合并内容相同的行
- java poi 读取有合并单元格的EXCEL文件
- POI设置Excel单元格式
- JAVA POI 操作EXCEL 合并单元格
- Java poi导出excel合并标题单元格
- java poi导出excel,合并单元格
- poi java导出excel时合并单元格
- java POI 实现Excel合并单元格
- 时钟周期 机器周期 指令周期
- Neural Networks for Machine Learning(系列)letcure 1
- LAST_INSERT_ID()
- 解析自己的域名到CSDN博客、CSDN博客自定义域名
- 未知宽高的容器设置居中、未知行数的文本设置居中
- java poi解析excel,带合并单元的excel
- uva12219 Common Subexpression Elimination
- lua 自学笔记4 table-图
- 数组处理
- arm mmu
- 第十周项目3-利用二叉树遍历思想解决问题(2)
- 学linux实战运维,就来爱维教育
- idea导入maven项目 无法加载依赖包
- 【iOS开发】将 .caf 格式转为 .mp3 格式方法