方法一：
最常见的是用poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
/**
* 表格处理工具类
*
* @author luc
* @date 2020/8/515:52
*/
@Slf4j
public class ExcelUtils {
public static void main(String[] args) {
try {
FileInputStream inp = new FileInputStream("D://统计表.xlsx");
Workbook workbook = WorkbookFactory.create(inp);
//获取sheet数
int sheetNum = workbook.getNumberOfSheets();
JSONObject jsonObject = new JSONObject();
for (int s = 0; s < sheetNum; s++) {
// Get the Sheet of s.
Sheet sheet = workbook.getSheetAt(s);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
if (rownum <= 1) {
continue;
}
//获取第一行
Row row1 = sheet.getRow(0);
//获取最大列数
int colnum = row1.getPhysicalNumberOfCells();
JSONArray jsonArray = new JSONArray();
for (int i = 1; i < rownum; i++) {
Row row = sheet.getRow(i);
if (row != null) {
// List<Object> list = new ArrayList<>();
JSONObject rowObj = new JSONObject();
//循环列
for (int j = 0; j < colnum; j++) {
Cell cellData = row.getCell(j);
if (cellData != null) {
//判断cell类型
switch (cellData.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cellData)) {
//转换为日期格式YYYY-mm-dd
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getDateCellValue());
} else {
//数字
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getStringCellValue());
break;
}
default:
rowObj.put(row1.getCell(j).getStringCellValue(), "");
}
} else {
rowObj.put(row1.getCell(j).getStringCellValue(), "");
}
}
jsonArray.add(rowObj);
}
}
System.out.println(jsonArray.toJSONString());
jsonObject.put(sheet.getSheetName(), jsonArray);
}
System.out.println(jsonObject.toJSONString());
} catch (Exception e) {
e.printStackTrace();
}
}
}
方法二：
更好用的是用easyexcel（阿里开发的）
<!--easyexcel文件解析-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
package com.sundear.document.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.poifs.filesystem.FileMagic;
import tk.mybatis.mapper.util.Assert;
import java.io.*;
import java.util.*;
/**
* 表格处理工具类
*
* @author luc
* @date 2020/8/515:52
*/
@Slf4j
public class EasyExcelUtils {
public static void readExcel(InputStream inStream, Class head, ReadListener listener) {
EasyExcel.read(inStream, head, listener).sheet().doRead();
}
/**
* 根据excel输入流，读取excel文件
*
* @param inputStream exece表格的输入流
* @return 返回双重list的集合
**/
public static String writeWithoutHead(InputStream inputStream) {
SyncReadListener listener = new SyncReadListener();
ExcelReader excelReader = EasyExcelFactory.read(inputStream, null, listener).headRowNumber(0).build();
excelReader.readAll();
List<Object> list = listener.getList();
log.info("读取结果:"+JSONObject.toJSONString(list));
excelReader.finish();
return JSONObject.toJSONString(list);
}
public static void main(String[] args) {
String result =new String();
try{
FileInputStream inp = new FileInputStream("D://人口信息.xls");
result = writeWithoutHead(inp);
}catch (Exception e){
e.printStackTrace();
}
//Assert.assertNotNull(result);
}
}