内容参考于：
Java使用POI读取和写入Excel指南 - Dreamer-1 - 博客园
Java实现excel简单读写_慕课手记
Java使用POI读取和写入Excel指南_慕课手记
1. 引入maven依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi引入poi，解析workbook视图 低版本Excel创建与读取 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml 高版本Excel创建与读取-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl 处理excel和poi功能是一样的 低版本Excel创建与读取-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>2. 读取或写入Excel数据
简单说明一下示例程序的整体结构：
-
ExcellReader.java 是实现读取Excel数据功能的类；
-
ExcellWriter.java 是创建新的Excel并向其中写入数据的类；
-
ControllerR.java 上传本地的Excel文件到后台
-
ControllerW.java 导出Excel文件
示例程序需要从桌面读取 readExample.xlsx 内的数据，readExample.xlsx 的内容如下：
读取Excel时主要调用ExcellReader.java类来读取和解析Excel的具体内容，这里以读取系统文件的形式演示读取过程：（兼容 xls 和 xlsx）
2.1 数据封装
读取和写入时封装每一“行”数据的SingleData.java代码如下：
@Getter
@Setter
public class SingleData {
private String name;
private Integer age;
private String location;
private String job;
}2.2 读取数据--ExcellReader.java
package com.ReadAndWrite.OperateExcell2;
import java.io.File;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.io.InputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import com.alibaba.fastjson.JSONArray;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
/**
* @author:
* @date:2021/12/21
* @description:
*/
@Slf4j
public class ExcellReader {
//private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
/**
* 根据文件后缀名类型获取对应的工作簿对象
*
* @param inputStream 读取文件的输入流
* @param fileType 文件后缀名类型（xls或xlsx）
* @return 包含文件数据的工作簿对象
* @throws IOException
*/
public static 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的版本,获取Workbook
*
* @param
* @param
* @return
* @throws IOException
*/
public static Workbook getWorkbook(File file) throws IOException {
//创建Excel，读取文件内容,HSSFWorkbook是针对.xls文件,XSSFWorkbook是针对.xslx文件。jxl不支持.xlsx,poi支持.xlsx
Workbook wb = null;//poi的workbook
FileInputStream in = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
/**
* 读取Excel文件内容
*
* @param fileName 要读取的Excel文件所在路径
* @return 读取结果列表，读取失败时返回null
*/
public static List<SingleData> readExcel(String fileName) {
Workbook workbook = null;
FileInputStream inputStream = null;
try {
// 获取Excel后缀名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
// 获取Excel文件
File excelFile = new File(fileName);
if (!excelFile.exists()) {
log.warn("指定的Excel文件不存在！");
return null;
}
// 获取Excel工作簿
inputStream = new FileInputStream(excelFile);
workbook = getWorkbook(inputStream, fileType);
// 读取excel中的数据
List<SingleData> resultDataList = parseExcel(workbook);
return resultDataList;
} catch (Exception e) {
log.warn("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
} catch (Exception e) {
log.warn("关闭数据流出错！错误信息：" + e.getMessage());
return null;
}
}
}
/**
* 读取Excel文件内容
*
* @param file 上传的Excel文件
* @return 读取结果列表，读取失败时返回null
*/
public static List<SingleData> readExcel(MultipartFile file) {
Workbook workbook = null;
try {
// 获取Excel后缀名
String fileName = file.getOriginalFilename();
if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
log.warn("解析Excel失败，因为获取到的Excel文件名非法！");
return null;
}
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
// 获取Excel工作簿
workbook = getWorkbook(file.getInputStream(), fileType);
// 读取excel中的数据
List<SingleData> resultDataList = parseExcel(workbook);
return resultDataList;
} catch (Exception e) {
log.warn("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
} catch (Exception e) {
log.warn("关闭数据流出错！错误信息：" + e.getMessage());
return null;
}
}
}
/**
* 解析Excel数据
*
* @param workbook Excel工作簿对象
* @return 解析结果
*/
private static List<SingleData> parseExcel(Workbook workbook) {
List<SingleData> resultDataList = new ArrayList<>();
// 解析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) {
log.warn("解析Excel失败，在第一行没有读取到任何数据！");
}
// 解析每一行的数据，构造数据对象
int rowStart = firstRowNum + 1;
int rowEnd = sheet.getPhysicalNumberOfRows();
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (null == row) {
continue;
}
SingleData resultData = convertRowToData(row);
if (null == resultData) {
log.warn("第 " + row.getRowNum() + "行数据不合法，已忽略！");
continue;
}
resultDataList.add(resultData);
}
}
return resultDataList;
}
/**
* 将单元格内容转换为字符串
*
* @param cell
* @return
*/
private static String convertCellValueToString(Cell cell) {
if (cell == null) {
return null;
}
String returnValue = null;
switch (cell.getCellType()) {
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;
}
/**
* 提取每一行中需要的数据，构造成为一个结果数据对象
* <p>
* 当该行中有单元格的数据为空或不合法时，忽略该行的数据
*
* @param row 行数据
* @return 解析后的行数据对象，行数据错误时返回null
*/
private static SingleData convertRowToData(Row row) {
SingleData resultData = new SingleData();
Cell cell;
int cellNum = 0;
// 获取姓名
cell = row.getCell(cellNum++);
String name = convertCellValueToString(cell);
resultData.setName(name);
// 获取年龄
cell = row.getCell(cellNum++);
String ageStr = convertCellValueToString(cell);
if (null == ageStr || "".equals(ageStr)) {
// 年龄为空
resultData.setAge(null);
} else {
resultData.setAge(Integer.parseInt(ageStr));
}
// 获取居住地
cell = row.getCell(cellNum++);
String location = convertCellValueToString(cell);
resultData.setLocation(location);
// 获取职业
cell = row.getCell(cellNum++);
String job = convertCellValueToString(cell);
resultData.setJob(job);
return resultData;
}
public JSONObject ExcellFileReader(File file) throws Exception {
//设置日期格式
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = df.format(new Date());
System.out.println("时间：" + time);
//创建JSONObject对象用来存储当前页的一行数据
//JSONObject jo = new JSONObject(new LinkedHashMap<>());
JSONObject jo = new JSONObject();
//创建JsonArray存储每一行数据
JSONArray ja = new JSONArray();
List<JSONObject> list = new ArrayList<>();
//创建Excel，读取文件内容,HSSFWorkbook是针对.xls文件,XSSFWorkbook是针对.xslx文件。jxl不支持.xlsx,poi支持.xlsx
/** 方式一、获取workbook*/
XSSFWorkbook workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
/** 方式二、获取workbook*/
Workbook workbook2 = getWorkbook(file);
/** 方式一、获取sheet*/
Sheet sheet = workbook.getSheetAt(0);
/** 方式二、获取sheet*/
Sheet sheet2 = workbook.getSheet("Sheet0");
//数据从第三行开始标题行 默认第一行为标题行，i = 0
Row TitleRow = sheet.getRow(2);
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();//4781
for (int i = 3; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
//获取当前行最后单元格列号
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
//方法一、获取单元格内容
Cell cell = row.getCell(j);
String value1 = cell.getStringCellValue();
System.out.print(value1 + " ");
//方法二、获取单元格内容
String value2 = new String(row.getCell(j).getStringCellValue().getBytes(Charset.forName("UTF-8")), "UTF-8");
//方法三、获取单元格内容
String value3 = row.getCell(j).getStringCellValue().getBytes(Charset.forName("UTF-8")).toString();
//方法四、
String value4 = URLEncoder.encode(row.getCell(j).getStringCellValue(), "UTF-8");
//方法五、获取单元格内容(键值形式，key为单元格表头，value为单元格内容)
DataFormatter formatter = new DataFormatter();
String key = formatter.formatCellValue(TitleRow.getCell(j));
String value = formatter.formatCellValue(row.getCell(j));
jo.put(key, value);
// Emmmm，不小心转码成链接了，这个不算。。。。
String errorValue = URLEncoder.encode(row.getCell(j).getStringCellValue(), "UTF-8");
}
ja.add(jo);//将每一行json数据追加到JsonArray
list.add(jo);//将每一行json数据添加到列表
System.out.println(file.getName() + ":\n" + "第" + (i + 1) + "行数据：" + jo.toString() + "\n");
}
return jo;
}
public static void sleep(Long mills) throws InterruptedException {
/**
* 随机休眠
*
* @param mills
*/
Thread.sleep((long) (mills + Math.random() * 1000));
}
public static void threadSleep(String time) {
//1-6
String[] split = time.split("-");
int first = Integer.parseInt(split[0]);
int second = Integer.parseInt(split[1]);
try {
Thread.sleep((first + (int) (Math.random() * (second - first))) * 1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
String excelFileName = "xxxxxx/excell.xls"; // 读取Excel文件内容
List<SingleData> readResult = ExcellReader.readExcel(excelFileName);
// todo 进行业务操作
}
}
2.3 写入数据--ExcellWriter.java
package com.ReadAndWrite.OperateExcell2;
import java.io.File;
import java.util.List;
import java.util.Iterator;
import java.io.IOException;
import java.util.ArrayList;
import java.io.FileOutputStream;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
* @author:
* @date:2021/12/21
* @description:
*/
@Slf4j
public class ExcellWriter {
private static List<String> CELL_HEADS; //列头
static {
// 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
CELL_HEADS = new ArrayList<>();
CELL_HEADS.add("姓名");
CELL_HEADS.add("年龄");
CELL_HEADS.add("居住城市");
CELL_HEADS.add("职业");
}
/**
* 生成Excel并写入数据信息
*
* @param dataList 数据列表
* @return 写入数据后的工作簿对象
*/
public static Workbook exportData(List<SingleData> dataList) {
// 生成xlsx的Excel
Workbook workbook = new SXSSFWorkbook();
// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
//Workbook workbook = new HSSFWorkbook();
// 生成Sheet表，写入第一行的列头
Sheet sheet = buildDataSheet(workbook);
//构建每行的数据内容
int rowNum = 1;
for (Iterator<SingleData> it = dataList.iterator(); it.hasNext(); ) {
SingleData data = it.next();
if (data == null) {
continue;
} //输出行数据
Row row = sheet.createRow(rowNum++);
convertDataToRow(data, row);
}
return workbook;
}
/**
* 生成sheet表，并写入第一行数据（列头）
*
* @param workbook 工作簿对象
* @return 已经写入列头的Sheet
*/
private static 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 static 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;
}
/**
* 将数据转换成行
*
* @param data 源数据
* @param row 行对象
* @return
*/
private static void convertDataToRow(SingleData data, Row row) {
int cellNum = 0;
Cell cell; // 姓名
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getName() ? "" : data.getName()); // 年龄
cell = row.createCell(cellNum++);
if (null != data.getAge()) {
cell.setCellValue(data.getAge());
} else {
cell.setCellValue("");
} // 所在城市
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getLocation() ? "" : data.getLocation()); // 职业
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getJob() ? "" : data.getJob());
}
public static void main(String[] args) {
List<SingleData> dataVOList = new ArrayList<>(2);
SingleData dataVO = new SingleData();
dataVO.setName("小明");
dataVO.setAge(18);
dataVO.setLocation("广州");
dataVO.setJob("大学生");
SingleData dataVO2 = new SingleData();
dataVO2.setName("小花");
dataVO2.setAge(19);
dataVO2.setLocation("深圳");
dataVO2.setJob("大学生");
dataVOList.add(dataVO);
dataVOList.add(dataVO2);
// 写入数据到工作簿对象内
Workbook workbook = ExcellWriter.exportData(dataVOList);
// 以文件的形式输出工作簿对象
FileOutputStream fileOut = null;
try {
String exportFilePath = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/writeExample.xlsx";
File exportFile = new File(exportFilePath);
if (!exportFile.exists()) {
exportFile.createNewFile();
}
fileOut = new FileOutputStream(exportFilePath);
workbook.write(fileOut);
fileOut.flush();
} catch (Exception e) {
log.warn("输出Excel时发生错误，错误原因：" + e.getMessage());
} finally {
try {
if (null != fileOut) {
fileOut.close();
}
if (null != workbook) {
workbook.close();
}
} catch (IOException e) {
log.warn("关闭输出流时发生错误，错误原因：" + e.getMessage());
}
}
}
}
2.4 上传Excell--ControllerR.java
package com.ReadAndWrite.OperateExcell2;
import java.util.Date;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author:
* @date:2021/12/21
* @description:
*/
@Slf4j
@RestController
public class ControllerR {
@PostMapping("/uploadExcel")
public ResponseEntity<?> uploadExcell(MultipartFile file) {
// 检查前台数据合法性
if (null == file || file.isEmpty()) {
log.warn("上传的Excel商品数据文件为空！上传时间：" + new Date());
return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
}
try {
// 解析Excel
List<SingleData> parsedResult = ExcellReader.readExcel(file);
// todo 进行业务操作
return new ResponseEntity<>(HttpStatus.OK);
} catch (Exception e) {
log.warn("上传的Excel商品数据文件为空！上传时间：" + new Date());
return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
}
}
}
2.5 导出Excell--ControllerW.java
package com.ReadAndWrite.OperateExcell2;
import java.util.Date;
import java.util.List;
import java.io.IOException;
import java.util.ArrayList;
import java.io.OutputStream;
//import scouter.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import cn.hutool.core.date.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
//import org.apache.solr.common.util.DateUtil;
import org.springframework.web.bind.annotation.GetMapping;
/**
* @author:
* @date:2021/12/21
* @description:
*/
@Slf4j
public class ControllerW {
@GetMapping("/exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
Workbook workbook = null;
OutputStream out = null;
try {
// todo 根据业务需求获取需要写入Excel的数据列表 dataList
// 生成Excel工作簿对象并写入数据
/************** 写入Excel流程 ******************/
// 创建需要写入的数据列表
List<SingleData> dataVOList = new ArrayList<>(2);
SingleData dataVO = new SingleData();
dataVO.setName("小明");
dataVO.setAge(18);
dataVO.setLocation("广州");
dataVO.setJob("大学生");
SingleData dataVO2 = new SingleData();
dataVO2.setName("小花");
dataVO2.setAge(19);
dataVO2.setLocation("深圳");
dataVO2.setJob("大学生");
dataVOList.add(dataVO);
dataVOList.add(dataVO2);
// 写入数据到工作簿对象内
workbook = ExcellWriter.exportData(dataVOList);
// 写入Excel文件到前端
if (null != workbook) {
String excelName = "示例Excel导出";
String fileName = excelName + DateUtil.format(new Date(), "yyyy年MM月dd日HH时mm分ss秒S毫秒") + ".xlsx";
//import scouter.util.DateUtil
fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
out = response.getOutputStream();
workbook.write(out);
out.flush();
}
} catch (Exception e) {
log.warn("写入Excel过程出错！错误原因：" + e.getMessage());
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != out) {
out.close();
}
} catch (IOException e) {
log.warn("关闭workbook或outputStream出错！");
}
}
}
//引用hutool,在项目maven的pom.xml的dependencies中加入以下内容:
//
//cn.hutool hutool-all 5.4.3
//
//日期时间工具-DateUtil
//
//由来
//
//考虑到Java本身对日期时间的支持有限，并且Date和Calendar对象的并存导致各种方法使用混乱和复杂，故使用此工具类做了封装。这其中的封装主要是日期和字符串之间的转换，以及提供对日期的定位(一个月前等等)。
//————————————————
//版权声明：本文为CSDN博主「佳同学」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
//原文链接：https://blog.csdn.net/weixin_42137723/article/details/112031473
}
2121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
