1、FileUtils后面接的路径是src下的文件,FileUtils的jar包没有测试过,有待验证;
2、解析表格使用jar包是org.apache.poi的jar包;
3、判断某一行是否是表头下面代码中只能作为参考,只是简单的表格样式,要根据时间情况做出调整
- 解析表格的jar包
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.10-FINAL</version>
</dependency>
- FileUtils
<dependency>
<groupId>org.apache.xmlgraphics</groupId>
<artifactId>fop-transcoder-allinone</artifactId>
<version>2.4</version>
</dependency>
- java代码
@Test
public void test() throws Exception {
//测试表格数据,表格放在src目录下
byte[] fileByte = new byte[0];
String url = null;//url为请求表格的url,测试时用的是本地表格,url就随意写满足下面的判断条件即可
File file = new File("D:\\collect\\service\\src\\test.xlsx");
fileByte = FileUtils.readFileToByteArray(file);
url = "https://ajdklmfjdisjufdjasdmfa.xls";
if (url.endsWith(".xls") || url.endsWith(".xlsx")) {
InputStream fin = null;
try {
fin = new ByteArrayInputStream(fileByte);
Workbook workbook = null;
/*if (url.endsWith(".xls")) {
workbook = new HSSFWorkbook(fin);
} else if (url.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fin);
}*/
//等同于上面注释部分
workbook = org.apache.poi.ss.usermodel.WorkbookFactory.create(fin);
//获取表格中的sheet,表格中最下面sheet1,sheet2,sheet3
for (int a = 0; a < workbook.getNumberOfSheets(); a++) {
Sheet sheet = workbook.getSheetAt(a);
//判断是否有合并的单元格
Map<Integer, org.apache.poi.ss.util.CellRangeAddress> mergeMap = new HashMap<>();
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
org.apache.poi.ss.util.CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
//cellRangeAddress.getFirstRow()获取合并单元格所在行
mergeMap.put(cellRangeAddress.getFirstRow(), cellRangeAddress);
}
//获取最后一行的单元格数
int rowNum = sheet.getLastRowNum();
int cellNum = sheet.getRow(rowNum - 1).getPhysicalNumberOfCells();
int index = 0;
boolean flag = false;
//遍历sheet表格的每一行,判断表头位置
for (int k = 0; k < sheet.getPhysicalNumberOfRows(); k++) {
int thisCellNum = sheet.getRow(k).getPhysicalNumberOfCells();//获取当前行的单元格数
String first = sheet.getRow(k).getCell(0).toString();//首个单元格
String last = sheet.getRow(k).getCell(thisCellNum - 1).toString();//最后一个单元格
//当前行无合并的单元格,当前行的单元格数是否与最后一行的单元格数相等,行首和行尾的单元格不为空
if (mergeMap.get(k) == null && (thisCellNum == cellNum) && StringUtils.isNotBlank(first) && StringUtils.isNotBlank(last)) {
index = k;
break;
}
//根据表头名称判断位置
for (int j = 0; j < thisCellNum; j++) {
String curCell = sheet.getRow(k).getCell(j).toString();
if ("序号".equals(curCell) || "纳税人名称".equals(curCell) || "纳税人识别号".equals(curCell)) {
flag = true;
break;
}
}
if (flag) {
index = k;
break;
}
}
//index是表头所在行的位置,下面行为所需表格内容
for (int i = index + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
JSONObject jsonObject = new JSONObject();
Row row = sheet.getRow(i);
String f = row.getCell(0).toString();
if (StringUtils.isBlank(f) || mergeMap.get(i) != null) {
continue;
}
//遍历一行中的每个单元格
for (int j = 0; j < sheet.getRow(i).getPhysicalNumberOfCells(); j++) {
String key = sheet.getRow(index).getCell(j).toString();
String value = "";
if (row.getCell(j) != null) {
//获取单元格中的值
if (row.getCell(j).getCellType() == Cell.CELL_TYPE_BLANK) {
value = "";
} else if (row.getCell(j).getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(row.getCell(j))) {
Date tempValue = row.getCell(j).getDateCellValue();
SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd");
value = simpleFormat.format(tempValue);
} else {
value = String.valueOf(row.getCell(j).getNumericCellValue());
}
} else if (row.getCell(j).getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
value = row.getCell(j).getStringCellValue();
} else {
value = row.getCell(j).toString();
}
}
//存储数据jsonObject
if (key != null && key != "null" && StringUtils.isNotBlank(key)) {
jsonObject.put(key, value);
}
}
//输出结果,也可以保存到json中
Iterator it = jsonObject.entrySet().iterator();
while (it.hasNext()) {
Map.Entry entry = (Map.Entry) it.next();
System.out.println(entry.getKey() + " = " + entry.getValue());
}
System.out.println("-----------------------------");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fin != null) {
fin.close();
}
}
}
}
- 表格样式
成绩表
序号 年级 姓名 成绩
1 一 张三 88
2 二 张思 99
3 三 张武 90
- 结果
姓名 = 张三
序号 = 1.0
成绩 = 88.0
年级 = 一
-----------------------------
姓名 = 张思
序号 = 2.0
成绩 = 99.0
年级 = 二
-----------------------------
姓名 = 张武
序号 = 3.0
成绩 = 90.0
年级 = 三
-----------------------------