目录
Excel支持两种格式的读写功能 分别为xlsx和xls版本
一.Apache poi读写
Java领域解析、生成Excel比较有名的框架有Apache poi。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc
1.pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>2.Excel读写
import org.apache.poi.hssf.usermodel.*;
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.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
public class ExcelUtil {
public ReaderExcel.Model readExcel(String path) throws IOException {
ReaderExcel excelUtil = new ReaderExcel();
ReaderExcel.Model read = excelUtil.read(path);
return read;
}
public void writerExcel(String path, List<List<String>> data) {
List<String> head = data.remove(0);
writerExcel(path, "首页", head, data);
}
public void writerExcel(String path, List<String> head, List<List<String>> data) {
writerExcel(path, "首页", head, data);
}
public void writerExcel(String path, String sheetName, List<String> head, List<List<String>> data) {
ExcelUtil excelUtil = null;
if (path.endsWith(".xls")) {
excelUtil = new WriterXLS();
} else if (path.endsWith(".xlsx")) {
excelUtil = new WriterXLSX();
}
excelUtil.createSheet(sheetName);//创建sheet页
excelUtil.createHead(head);//设置表头
excelUtil.createContent(data);//设置内容
excelUtil.writeToFile(path);//写入文件xlsx/xls
}
/**
* 禁止外部访问create方法
* 创建Sheet页
*
* @param sheetName
*/
protected void createSheet(String sheetName) {
System.out.println("父createSheet");
}
/**
* 创建字段
*
* @param headList
*/
protected void createHead(List<String> headList) {
System.out.println("createSheet");
}
/**
* 创建内容
*
* @param contentList
*/
protected void createContent(List<List<String>> contentList) {
System.out.println("createSheet");
}
/**
* 写入文件
*
* @param filePath
*/
protected void writeToFile(String filePath) {
}
private class WriterXLS extends ExcelUtil {
private HSSFWorkbook hssfWorkbook;
private HSSFSheet hssfSheet;
private HSSFRow hssfRow;
private HSSFCell hssfCell;
@Override
protected void createSheet(String sheetName) {
hssfWorkbook = new HSSFWorkbook();
hssfSheet = hssfWorkbook.createSheet(sheetName);
}
@Override
protected void createHead(List<String> headList) {
//创建表头，也就是第一行
if (hssfSheet == null) hssfWorkbook.createSheet("首页");
hssfRow = hssfSheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
hssfCell = hssfRow.createCell(i);
hssfCell.setCellValue(headList.get(i));
}
}
@Override
protected void createContent(List<List<String>> contentList) {
//创建表内容，从第二行开始
for (int i = 0; i < contentList.size(); i++) {
hssfRow = hssfSheet.createRow(i + 1);
for (int j = 0; j < contentList.get(i).size(); j++) {
hssfRow.createCell(j).setCellValue(contentList.get(i).get(j));
}
}
}
@Override
protected void writeToFile(String filePath) {
File file = new File(filePath);
try {
hssfWorkbook.write(new FileOutputStream(file));
hssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private class WriterXLSX extends ExcelUtil {
private XSSFWorkbook xssfWorkbook;
private XSSFSheet xssfSheet;
private XSSFRow xssfRow;
private XSSFCell xssfCell;
@Override
protected void createSheet(String sheetName) {
xssfWorkbook = new XSSFWorkbook();
xssfSheet = xssfWorkbook.createSheet(sheetName);
}
@Override
protected void createHead(List<String> headList) {
//创建表头，也就是第一行
if (xssfSheet == null) xssfWorkbook.createSheet("首页");
xssfRow = xssfSheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
xssfCell = xssfRow.createCell(i);
xssfCell.setCellValue(headList.get(i));
}
}
@Override
protected void createContent(List<List<String>> contentList) {
//创建表内容，从第二行开始
for (int i = 0; i < contentList.size(); i++) {
xssfRow = xssfSheet.createRow(i + 1);
for (int j = 0; j < contentList.get(i).size(); j++) {
xssfRow.createCell(j).setCellValue(contentList.get(i).get(j));
}
}
}
@Override
protected void writeToFile(String filePath) {
File file = new File(filePath);
//将文件保存到指定的位置
try {
xssfWorkbook.write(new FileOutputStream(file));
xssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private class ReaderExcel extends ExcelUtil {
/**
* 读取Excel文件
*
* @param filePath 文件地址
* @return
*/
private Model read(String filePath) throws IOException {
FileInputStream inputStream = null;
Model dataLst = null;
try {
inputStream = new FileInputStream(filePath);
Workbook wb = null;
if (filePath.endsWith(ExcelType.XLSX.suffix)) {
wb = new XSSFWorkbook(inputStream);
} else if (filePath.endsWith(ExcelType.XLS.suffix)) {
wb = new HSSFWorkbook(inputStream);
} else {
//不支持
}
dataLst = readDate(wb);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (inputStream != null) inputStream.close();
}
return dataLst;
}
/**
* 读取数据
*
* @param wb
* @return
*/
private Model readDate(Workbook wb) {
Sheet sheet = wb.getSheetAt(0);//得到第一个shell
int totalRows = sheet.getPhysicalNumberOfRows();//得到Excel的行数
if (totalRows == 0) return null;
Row headRow = sheet.getRow(0);
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();//得到Excel的列数
List<String> head = new LinkedList<>();
List<List<String>> dataList = new LinkedList<>();
Model model = new Model();
model.head = head;
model.data = dataList;
for (int i = 0; i < totalCells; i++) head.add(headRow.getCell(i).getStringCellValue());
for (int index = 1; index < totalRows; index++) {//循环Excel的行
Row row = sheet.getRow(index);
if (row == null) continue;
List<String> rowList = new LinkedList<>();
dataList.add(rowList);//保存第r行的第c列
for (int field = 0; field < totalCells; field++) {//循环Excel的列
Cell cell = row.getCell(field);
String cellValue = null == cell ? "" : getCellValue(cell);// 以下是判断数据的类型
rowList.add(cellValue);
}
}
return model;
}
/**
* 解析一行内容
*
* @param cell
* @return
*/
private String getCellValue(Cell cell) {
String cellValue;
switch (cell.getCellTypeEnum()) {
case NUMERIC:
cellValue = (cell != null && HSSFDateUtil.isCellDateFormatted(cell)) ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()) : NumberToTextConverter.toText(cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA:
cellValue = cell.getCellFormula() + "";
break;
case BLANK:
cellValue = "";
break;
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
/**
* excel数据存储对象
*/
public class Model {
public List<String> head;//字段
public List<List<String>> data;//数据
}
enum ExcelType {
XLSX("xlsx", 2007),
XLS("xls", 2003);
public String suffix;
public int type;
ExcelType(String suffix, int type) {
this.suffix = suffix;
this.type = type;
}
}
}3.Excel读写运行测试
import com.ExcelUtil;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class MyExcelTest {
public static void main(String[] args) throws IOException {
//读取测试
String filePath = "C:\\Users\\Administrator\\Desktop\\数据\\住建局.xls";
ExcelUtil excelUtil = new ExcelUtil();
ExcelUtil.Model model = excelUtil.readExcel(filePath);
System.out.println(model.head);
for (List<String> dataLine : model.data) {
System.out.println(dataLine);
}
//写入测试
List<String> headList = new ArrayList<>();
headList.add("name");
headList.add("age");
List<List<String>> contentList = getData();//内容测试数据
ExcelUtil writeToExcel = new ExcelUtil();
String saveFile = "C:\\Users\\Administrator\\Desktop\\数据\\work.xlsx";
writeToExcel.writerExcel(saveFile,headList,contentList);//写入文件xlsx/xls
}
//写入测试数据
static List<List<String>> getData() {
List<List<String>> contentList = new ArrayList<>();
List<String> line1 = new ArrayList<>();
line1.add("张三");
line1.add("11");
List<String> line2 = new ArrayList<>();
line2.add("李四");
line2.add("12");
contentList.add(line1);
contentList.add(line2);
return contentList;
}
}
二.EasyExcel
阿里巴巴开源的一个excel处理框架EasyExcel,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析
1.pom
<!--自动set与get忽略-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
<!--EXCEL解析-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>2.读取代码
2.1监听器
package com;
import java.util.*;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
public class ExcelListener extends AnalysisEventListener<Map<Integer, String>> {
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
System.out.println("开始读取");
super.invokeHead(headMap, context);
}
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
System.out.println(data);//依次按行读取
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println(headMap);//读取头信息
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("读取完成");
}
}
2.2测试代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
public class ExcelUtils {
public static void readExcel(InputStream inputStream) {
ExcelListener excelListener = new ExcelListener();//该监听器一行行将文件读取到内存
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(inputStream, excelListener).build();//把前端传来的文件转成输入流
ReadSheet readSheet = EasyExcel.readSheet(0).build();//指定读取哪一个sheet
excelReader.read(readSheet);//开始读取
} catch (Exception e) {
e.printStackTrace();
} finally {
if (excelReader != null) {
//读的时候会创建临时文件,需要主动关闭
excelReader.finish();
excelReader.close();
}
}
}
public static void main(String[] args) throws FileNotFoundException {
String fileName = "C:\\Users\\Administrator\\Desktop\\数据\\11.xlsx";
InputStream in = new FileInputStream(fileName);
readExcel(in);
}
}3.写入代码
3.1 字段模板代码
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Model {
@ExcelProperty({"大标题", "小字段1"})//ExcelProperty中,相同的标题会合并,默认使用成员变量名称做标题
String name;
@ExcelProperty({"大标题", "小字段2"})
String age;
public Model(String name, String age) {
this.name = name;
this.age = age;
}
}3.2测试代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.io.FileNotFoundException;
import java.util.LinkedList;
import java.util.List;
public class ExcelUtils {
public static void main(String[] args) throws FileNotFoundException {
String fileName = "C:\\Users\\Administrator\\Desktop\\数据\\11.xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, Model.class).build();
//ExcelWriter excelWriter = EasyExcel.write(fileName).head("二维list自定义表头").build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();// 这里注意 如果同一个sheet只要创建一次
List<Model> data = new LinkedList<>();
data.add(new Model("a", "b"));
data.add(new Model("1", "2"));
data.add(new Model("2", "3"));
data.add(new Model("3", "4"));
// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
for (int i = 0; i < 3; i++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
excelWriter.write(data, writeSheet);
}
excelWriter.finish();
excelWriter.close();
}
}
1154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
