转自/qq_21454973/article/details/80581459并稍微改动60行 读取cell内容。
文件：
输出：
厂家1_1000000_自然人11_196493_
厂家1_1000000_自然人12_164194_
厂家1_1000000_自然人13_17269_
厂家1_1000000_自然人14_56635_
厂家1_1000000_自然人15_565406_
厂家2_2000000_自然人21_483501_
厂家2_2000000_自然人22_621527_
厂家2_2000000_自然人23_415647_
厂家2_2000000_自然人24_54321_
厂家2_2000000_自然人25_425002_
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
代码：
package com.navitek.utils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
/**
* @Author: syl
* @Date: /7/3 0003 16:39
* @Description:
*/
public class ExcelUtils {
public static void main(String[] args) {
getAllByExcel("E:\\all_temp\\temp.xls");
}
public static void getAllByExcel(String filepath) {
try {
// 同时支持Excel 、
File excelFile = new File(filepath); // 创建文件对象
FileInputStream is = new FileInputStream(excelFile); // 文件流
Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
String[] res = readExcel(workbook, 0, 1, 0);
for (int i = 0; i < res.length; i++) {
System.out.println(res[i]);
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static String[] readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
String[] res = new String[sheet.getLastRowNum() - tailLine + 1];
for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
row = sheet.getRow(i);
res[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 + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
res[i] += rs+ "_";
} else {
//System.out.print(c.getRichStringCellValue() + "");
res[i] += getCellValue(c)+ "_";
}
}
//System.out.println();
}
if(startReadLine > 0){
String[] result = new String[res.length - startReadLine];
for (int i = 0; i < startReadLine; i++) {
for (int j = 0; j < res.length; j++) {
if(j == res.length - 1)
continue;
res[j] = res[j+1];
}
}
for (int i = 0; i < result.length; i++) {
result[i] = res[i];
}
return result;
}else{
return res;
}
}
private static 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;
}
public static 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;
}
private static String getCellValue(Cell cell) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
String cellValue = "";
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); // 日期型
} else {
cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = "错误";
break;
default:
cellValue = "错误";
}
return cellValue;
}
}
