java 读取40w excel_Java第四十天，业务实战（一），读取Excel文件

一、需要用到的工具类

1.字段名转换类

驼峰文件名和下划线文件名相互转换的工具类

package com.git.easyloan.netloan.entity.util;

public class FormatConversion {

public static String humpToUnderline(String name) {

StringBuilder result = new StringBuilder();

if (name != null && name.length() > 0) {

// 将第一个字符处理成大写

result.append(name.substring(0, 1).toUpperCase());

// 循环处理其余字符

for (int i = 1; i < name.length(); i++) {

String s = name.substring(i, i + 1);

// 在大写字母前添加下划线

if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) {

result.append("_");

}

// 其他字符直接转成大写

result.append(s.toUpperCase());

}

}

return result.toString();

}

public static String underlineToHump(String name) {

StringBuilder result = new StringBuilder();

// 快速检查

if (name == null || name.isEmpty()) {

// 没必要转换

return "";

} else if (!name.contains("_")) {

// 不含下划线，仅将首字母小写

return name.substring(0, 1).toLowerCase() + name.substring(1);

}

// 用下划线将原始字符串分割

String camels[] = name.split("_");

for (String camel : camels) {

// 跳过原始字符串中开头、结尾的下换线或双重下划线

if (camel.isEmpty()) {

continue;

}

// 处理真正的驼峰片段

if (result.length() == 0) {

// 第一个驼峰片段，全部字母都小写

result.append(camel.toLowerCase());

} else {

// 其他的驼峰片段，首字母大写

result.append(camel.substring(0, 1).toUpperCase());

result.append(camel.substring(1).toLowerCase());

}

}

return result.toString();

}

}

2.读取Excel文件类

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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.xssf.usermodel.XSSFWorkbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.io.File;

import java.io.FileInputStream;

public class ReadExcel {

private Logger logger = LoggerFactory.getLogger(this.getClass());

private final String XLS = "xls";

private final String XLSX = "xlsx";

// private PageData pd;

// private ArrayList pdResult;

private ArrayList attrs = new ArrayList<>();

/**

* 根据文件后缀名类型获取对应的工作簿对象

* @param inputStream 读取文件的输入流

* @param fileType 文件后缀名类型(xls或xlsx)

* @return 包含文件数据的工作簿对象

* @throws IOException

*/

public Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {

Workbook workbook = null;

if (fileType.equalsIgnoreCase(XLS)) {

workbook = new HSSFWorkbook(inputStream);

} else if (fileType.equalsIgnoreCase(XLSX)) {

workbook = new XSSFWorkbook(inputStream);

}

return workbook;

}

/**

* 解析Excel数据

* @param workbook Excel工作簿对象

* @return 解析结果

*/

private ArrayList parseExcel(Workbook workbook) {

ArrayList pdResult = new ArrayList();

PageData pd = new PageData();

// 解析sheet

for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {

Sheet sheet = workbook.getSheetAt(sheetNum);

// 校验sheet是否合法

if (sheet == null) {

continue;

}

// 获取第一行数据

int firstRowNum = sheet.getFirstRowNum();

Row firstRow = sheet.getRow(firstRowNum);

if (null == firstRow) {

logger.error("解析Excel失败，在第一行没有读取到任何数据！");

}

for(int index = 0;index < firstRow.getLastCellNum();index++){

if(convertCellValueToString(firstRow.getCell(index)) == null || convertCellValueToString(firstRow.getCell(index)).equals("")){

logger.error("第" + String.valueOf(index + 1) + "列标题栏为空，程序退出");

return null;

}else{

attrs.add(FormatConversion.underlineToHump(convertCellValueToString(firstRow.getCell(index))));

}

}

// 解析每一行的数据，构造数据对象

int rowStart = firstRowNum + 1;

int rowEnd = sheet.getPhysicalNumberOfRows();

for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {

Row row = sheet.getRow(rowNum);

if (null == row) {

continue;

}

pd = convertRowToData(row);

if (null == pd) {

logger.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");

continue;

}

pdResult.add(pd);

}

}

return pdResult;

}

/**

* 将单元格内容转换为字符串

* @param cell

* @return

*/

private String convertCellValueToString(Cell cell) {

if(cell==null){

return null;

}

String returnValue = null;

/**

* CELL_TYPE_NUMERIC 数值 0

* CELL_TYPE_STRING 字符串型 1

* CELL_TYPE_FORMULA 公式型 2

* CELL_TYPE_BLANK 空值 3

* CELL_TYPE_BOOLEAN 布尔型 4

* CELL_TYPE_ERROR 错误 5

*/

switch (cell.getCellTypeEnum()) {

case NUMERIC: //数字

Double doubleValue = cell.getNumericCellValue();

// 格式化科学计数法，取一位整数

DecimalFormat df = new DecimalFormat("0");

returnValue = df.format(doubleValue);

break;

case STRING: //字符串

returnValue = cell.getStringCellValue();

break;

case BOOLEAN: //布尔

Boolean booleanValue = cell.getBooleanCellValue();

returnValue = booleanValue.toString();

break;

case BLANK: // 空值

break;

case FORMULA: // 公式

returnValue = cell.getCellFormula();

break;

case ERROR: // 故障

break;

default:

break;

}

return returnValue;

}

/**

* 提取每一行中需要的数据，构造成为一个结果数据对象

*

* 当该行中有单元格的数据为空或不合法时，忽略该行的数据

*

* @param row 行数据

* @return 解析后的行数据对象，行数据错误时返回null

*/

private PageData convertRowToData(Row row) {

PageData pd = new PageData();

Cell cell;

int cellNum = 0;

for (int i = 0; i < row.getLastCellNum(); i++) {

cell = row.getCell(cellNum);

if(convertCellValueToString(cell) == null || convertCellValueToString(cell).equals("")){

pd.put(attrs.get(cellNum++), "?");

logger.error("第" + String.valueOf(i + 1) + "列数据为空，程序已自动填充为 ? ");

}else{

pd.put(attrs.get(cellNum++), convertCellValueToString(cell));

}

}

return pd;

}

public ArrayList ReadExcel(String fileUrl){

Workbook workbook = null;

FileInputStream inputStream = null;

ArrayList pdResult = new ArrayList<>();

try {

// 获取Excel后缀名

String fileType = fileUrl.substring(fileUrl.lastIndexOf(".") + 1, fileUrl.length());

// 获取Excel文件

File excelFile = new File(fileUrl);

if (!excelFile.exists()) {

logger.error("指定的Excel文件不存在！");

return null;

}

// 获取Excel工作簿

inputStream = new FileInputStream(excelFile);

workbook = getWorkbook(inputStream, fileType);

// 读取excel中的数据

pdResult = parseExcel(workbook);

return pdResult;

} catch (Exception e) {

logger.error("解析Excel失败，文件名：" + fileUrl + " 错误信息：" + e.getMessage());

return null;

} finally {

try {

if (null != workbook) {

workbook.close();

}

if (null != inputStream) {

inputStream.close();

}

} catch (Exception e) {

logger.error("关闭数据流出错！错误信息：" + e.getMessage());

return null;

}

}

}

}

3.写Excel文件类

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.*;

public class WriteExcel {

private ArrayList CELL_HEADS = new ArrayList<>(); //标题列表

// 初始化标题

private void initCellHeads(PageData pd){

Set set = pd.keySet();

Iterator it = set.iterator();

while(it.hasNext()){

CELL_HEADS.add(it.next().toString());

}

}

// 根据列表中第一行数据的 key 列表写入标题

public boolean initTitle(PageData pd){

if(pd == null){

return false;

}else{

initCellHeads(pd);

return true;

}

}

/**

* 生成sheet表，并写入第一行数据(列头)

*/

private Sheet buildDataSheet(Workbook workbook) {

Sheet sheet = workbook.createSheet();

// 设置列头宽度

for (int i=0; i< CELL_HEADS.size(); i++) {

sheet.setColumnWidth(i, 4000);

}

// 设置默认行高

sheet.setDefaultRowHeight((short) 400);

// 构建头单元格样式

CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());

// 写入第一行各列的数据

Row head = sheet.createRow(0);

for (int i = 0; i < CELL_HEADS.size(); i++) {

Cell cell = head.createCell(i);

cell.setCellValue(CELL_HEADS.get(i));

cell.setCellStyle(cellStyle);

}

return sheet;

}

/**

* 设置第一行列头的样式

* @param workbook 工作簿对象

* @return 单元格样式对象

*/

private CellStyle buildHeadCellStyle(Workbook workbook) {

CellStyle style = workbook.createCellStyle();

//对齐方式设置

style.setAlignment(HorizontalAlignment.CENTER);

//边框颜色和宽度设置

style.setBorderBottom(BorderStyle.THIN);

style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框

style.setBorderLeft(BorderStyle.THIN);

style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框

style.setBorderRight(BorderStyle.THIN);

style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框

style.setBorderTop(BorderStyle.THIN);

style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框

//设置背景颜色

style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

//粗体字设置

Font font = workbook.createFont();

font.setBold(true);

style.setFont(font);

return style;

}

/**

* 生成Excel并写入数据信息

*/

public Workbook exportData(ArrayList pdList){

// 生成xlsx的Excel

Workbook workbook = new SXSSFWorkbook();

// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls

//Workbook workbook = new HSSFWorkbook();

// 写入标题

if(!initTitle(pdList.get(0))){

return null;

}

// 生成Sheet表，写入第一行的列头

Sheet sheet = buildDataSheet(workbook);

// 构建每行的数据内容

// 第 0 行为标题，以后的都为内容

int rowNum = 1;

for (Iterator it = pdList.iterator(); it.hasNext(); ) {

PageData pd = it.next();

if (pd == null) {

continue;

}

//输出行数据

Row row = sheet.createRow(rowNum++);

convertDataToRow(pd, row);

}

return workbook;

}

/**

* 将数据转换成行

* @return

*/

private void convertDataToRow(PageData pd, Row row){

Cell cell;

int rows = pd.size();

for (int i = 0; i < rows; i++) {

cell = row.createCell(i);

cell.setCellValue(null == pd.getString(CELL_HEADS.get(i)) ? "?" : pd.getString(CELL_HEADS.get(i)));

}

}

}

二、读取写入Excel文件总控制类

想要读取或写入Excel文件只需要创建该类的对象进行操作即可；它会自动调用其他用到的工具类。该类仅有两个方法，一个读取，一个写入；参数也非常明了，故不再做多余的笔记

package com.git.easyloan.netloan.entity.util;

import javacommon.coreframe.util.PageData;

import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class ExcelUtil {

private Logger logger = LoggerFactory.getLogger(this.getClass());

private WriteExcel write;

public ArrayList ReadExcelFileToPageDataList(String resourceFileUrl){

return new ReadExcel().ReadExcel(resourceFileUrl);

}

public void WriteExcelFileFromPageDataList(ArrayList pds, String targetFileUrl){

write = new WriteExcel();

Workbook workbook = write.exportData(pds);

FileOutputStream fileOut = null;

try {

fileOut = new FileOutputStream(targetFileUrl);

workbook.write(fileOut);

fileOut.flush();

} catch (Exception e) {

logger.error("输出Excel时发生错误，错误原因：" + e.getMessage());

} finally {

try {

if (null != fileOut) {

fileOut.close();

}

if (null != workbook) {

workbook.close();

}

} catch (IOException e) {

logger.error("关闭输出流时发生错误，错误原因：" + e.getMessage());

}

}

}

}

三、在线、离线 jar 包

1.离线包下载地址

https://download.csdn.net/download/ITlanyue/12671478

2.在线包(使用Maven)配置文件声明

org.apache.poi

poi

3.16-beta1

org.apache.poi

poi-ooxml

3.16-beta1