package test;
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;
public class ReadExcelUtil {
/
* 获取合并单元格的值
* @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 ;
}
/*
* 读取excel文件
* @param wb
* @param sheetIndex sheet页下标:从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
/
@SuppressWarnings("unused")
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.println(rs + " ");
}else{
System.out.print(c.getRichStringCellValue()+" ");
}
}
System.out.println();
}
}
/*
* 判断指定的单元格是否是合并单元格
* @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;
}
/
* 获取单元格的值
* @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 " ";
}
public static void main(String[] args) {
ReadExcelUtil re = new ReadExcelUtil();
re.readExcelToObj("d://poitest.xls");
}
/*
* 读取excel数据
* @param path
*/
@SuppressWarnings("unused")
private void readExcelToObj(String path) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(new File(path));
readExcel(wb, 0, 2, 0);
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
模版如上,但是取出数据总是多取出好多重复数据,谁能告诉我如上代码有什么问题。或者告诉我更好的解决办法也可以,谢谢!
https://github.com/legend0702/excelUtils
我自己写的 好久没更新了 你可以试试...
######谢谢版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。