关于java读取Excel ,最简单,最好理解兼容性强的 WorkbookFactory 方式
程序员文章站
2024-03-02 23:00:04
...
假如我们现在要用java读取这样一个excel:
直接上代码:
package com.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.DataFormatter;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import com.alibaba.fastjson.JSON;
public class ExcelReadUtil {
private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(ExcelReadUtil.class);
/**
* 读取excel文件流
* @param in
* @return
*/
public static List<Map<String, String>> readExcel(FileInputStream in) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Map<String, String> map = null;
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0); //示意访问sheet
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < rows; i++) {
map = new HashMap<String, String>();
// 获取第i行数据
XSSFRow sheetRow = (XSSFRow)sheet.getRow(i);
// 组织数据
map.put("REAL_NAME",getXssfCellValue(sheetRow.getCell(0)));// 姓名
map.put("SEX", getXssfCellValue(sheetRow.getCell(1)));// 性别
map.put("NATION", getXssfCellValue(sheetRow.getCell(2)));// 民族
map.put("CARD_TYPE", getXssfCellValue(sheetRow.getCell(3)));// 身份证件种类
map.put("CARD_NO", getXssfCellValue(sheetRow.getCell(4)));// 身份证件号码
map.put("EDUCATION", getXssfCellValue(sheetRow.getCell(5)));// 文化程度
map.put("POLICY_STATUS", getXssfCellValue(sheetRow.getCell(6)));// 政治面貌
map.put("BIRTH", getXssfCellValue(sheetRow.getCell(7)));// 出生年月
map.put("MOBILE_PHONE", getXssfCellValue(sheetRow.getCell(8)));// 移动电话
map.put("JOB_NUMBER", getXssfCellValue(sheetRow.getCell(9)));// 公司工号
map.put("AUTHENTICA_NO", getXssfCellValue(sheetRow.getCell(10)));// 资格证书编号
System.out.println("开始读取第"+i+"行数据:"+JSON.toJSONString(map));
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(workbook!=null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(in!=null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return list;
}
private static String getXssfCellValue(XSSFCell xssfCell) {
String cellvalue="";
DataFormatter formatter = new DataFormatter();
if (null != xssfCell) {
switch (xssfCell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC: // 数字
double value = xssfCell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("0");
cellvalue = df.format(value);
// int intValue = (int) value;
//cellvalue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
break;
case XSSFCell.CELL_TYPE_STRING: // 字符串
cellvalue=xssfCell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellvalue=String.valueOf(xssfCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA: // 公式
cellvalue=String.valueOf(xssfCell.getCellFormula());
break;
case XSSFCell.CELL_TYPE_BLANK: // 空值
cellvalue="";
break;
case XSSFCell.CELL_TYPE_ERROR: // 故障
cellvalue="";
break;
default:
cellvalue="UNKNOWN TYPE";
break;
}
} else {
System.out.print("-");
}
return cellvalue.trim();
}
//测试demo
public static void main(String[] args) throws IOException {
File xlsFile = new File("C:\\Users\\Administrator\\Desktop\\测试.xlsx");
FileInputStream fileInputStream = new FileInputStream(xlsFile);
// 获得工作簿内容
List<Map<String,String>> readExcel = ExcelReadUtil.readExcel(fileInputStream);
//打印
System.out.println(JSON.toJSONString(readExcel));
}
}
excel 测试文件已上传,在我的上传资源里,需要的自己下载,也可以按照我的截图自己弄一个
感谢!!!
上一篇: 微信企业付款到银行卡 .NET
下一篇: GO语言-数组特性