目录
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;
}
}
4.综合运用写入Excel
package com.libii.business.util.excel.model;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
public class ExcelTest {
/**
* 格式化写入excel文件
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();//创建excel对象
HSSFCellStyle centerStyle = workbook.createCellStyle();//设置样式
centerStyle.setBorderBottom(BorderStyle.THIN); //下边框
centerStyle.setBorderLeft(BorderStyle.THIN);//左边框
centerStyle.setBorderTop(BorderStyle.THIN);//上边框
centerStyle.setBorderRight(BorderStyle.THIN);//右边框
centerStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平和垂直居中对齐
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置居中对其
centerStyle.setWrapText(true);//设置自动换行
String filePath = "C:\\Users\\admin\\Desktop\\输出文件\\1.xls";//输出文件地址
String nameUnit = "Sheet1";//excel当前页码名称
Sheet sheet = workbook.createSheet(nameUnit); // 创建一个工作表
sheet.autoSizeColumn(0);//设置文本自适应
setValue(sheet, centerStyle, 0, 0, 0, 0, "测试");
setValue(sheet, centerStyle, 1, 1, 0, 1, "测试");
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
workbook.close();
}
/**
* 合并单元格并设置样式和值
*
* @param sheet excel当前页
* @param style 样式
* @param firstRow 合并起始行
* @param lastRow 合并借宿行
* @param firstCol 合并起始列
* @param lastCol 合并结束列
* @param value 值
*/
public static void setValue(Sheet sheet, HSSFCellStyle style, int firstRow, int lastRow, int firstCol, int lastCol, String value) {
if (firstRow == lastRow && firstCol == lastCol) {
setUnitValue(sheet, style, firstRow, firstCol, value);
} else {
merge(sheet, style, firstRow, lastRow, firstCol, lastCol, value);
}
}
/**
* 设置单元格样式和值
*
* @param sheet excel当前页
* @param style 样式
* @param firstRow 行
* @param firstCol 列
* @param value 值
*/
private static void setUnitValue(Sheet sheet, HSSFCellStyle style, int firstRow, int firstCol, String value) {
Row row = sheet.getRow(firstRow);
if (row == null) row = sheet.createRow(firstRow);
Cell cell = row.getCell(firstCol);
if (cell == null) cell = row.createCell(firstCol);
if (value != null) cell.setCellValue(value);
if (style != null) cell.setCellStyle(style);
//设置格子的高度和宽度
//row.setHeight(500);//设置高度
//int columnIndex = cell.getColumnIndex();
//sheet.setColumnWidth(columnIndex, 5000);
}
/**
* 合并单元格并设置样式和值
*
* @param sheet excel当前页
* @param style 样式
* @param firstRow 合并起始行
* @param lastRow 合并借宿行
* @param firstCol 合并起始列
* @param lastCol 合并结束列
* @param value 值
*/
private static void merge(Sheet sheet, HSSFCellStyle style, int firstRow, int lastRow, int firstCol, int lastCol, String value) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
if (style != null) {//设置样式
for (int i = firstRow; i <= lastRow; i++) {
Row row = sheet.getRow(i);//3, 3, 0, 23
if (row == null) row = sheet.createRow(i);
for (int j = firstCol; j <= lastCol; j++) {
Cell cell = row.getCell(j);
if (cell == null) cell = row.createCell(j);
cell.setCellStyle(style);
}
}
}
Row row = sheet.getRow(firstRow);
Cell cell = row.getCell(firstCol);
if (value != null && cell != null) {
cell.setCellValue(value);
}
//设置格子的高度和宽度
//row.setHeight(500);//设置高度
//int columnIndex = cell.getColumnIndex();
//sheet.setColumnWidth(columnIndex, 5000);
}
}
二.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>
<dependency><!--excel内部缺失的包commons-compress版本冲突可以排除其他版本引入这个版本 -->
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.26.1</version>
</dependency>2.异步读取代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
public class ExcelReadUtil extends AnalysisEventListener<Map<Integer, String>> {
final private Logger logger = LoggerFactory.getLogger(this.getClass());//日志对象
//读取的数据量
private int sum = 0;
//消费队列最大等待次数
int maxWaitFrequency = 5;
//读取文件的绝对路径
private final String fileName;
//Excel字段头信息<列位置(从0开始),列内容>
private Map<Integer, String> headMap;
//转换excel的列名称为系统需要的列名称 <列内容,系统需要的内容> 示例: <姓名,name>
private final Map<String, String> kvMapper;
//读取过程中是否异常
private boolean readError = false;
//读取是否完成
private boolean readSuccess = false;
//存储读取数据的队列
private final BlockingQueue<Map<String, String>> queue;
//内存变量,读取完毕需要删除
private FileInputStream inputStream = null;
private ExcelReader excelReader = null;
public ExcelReadUtil(String fileName) throws Exception {
this(fileName, null);
}
/**
* 初始化读取工具类
*
* @param fileName 读取的excel文件名称
* @param kvMapper 字段映射信息 excel字段在前 系统字段在后
* @throws Exception 异常信息
*/
public ExcelReadUtil(String fileName, Map<String, String> kvMapper) throws Exception {
if (!new File(fileName).exists()) throw new FileNotFoundException("文件不存在: " + fileName);
this.kvMapper = kvMapper;
this.fileName = fileName;
readExcel();
this.queue = new LinkedBlockingQueue<>(50000);//队列
}
/**
* 读取excel文件初始化
*/
private void readExcel() {
new Thread(() -> {
try {
inputStream = new FileInputStream(this.fileName);
excelReader = EasyExcel.read(inputStream, this).build();//把前端传来的文件转成输入流
ReadSheet readSheet = EasyExcel.readSheet(0).build();//指定读取哪一个sheet
excelReader.read(readSheet);//开始读取
excelReader.finish();
} catch (Exception e) {
logger.error("构造excel处理对象异常", e);
} finally {
close(excelReader);//读的时候会创建临时文件,需要主动关闭
close(inputStream);
}
}).start();
}
/**
* 开始读取
*
* @param headReadCellMap 读取的head字段信息
* @param context 上下文对象
*/
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headReadCellMap, AnalysisContext context) {
//获取excel中的头字段
Map<Integer, String> headMap = headReadCellMap.entrySet().stream()//读取excel的第一行作为表头,过滤空的字段名称
.filter(entry -> entry.getValue().getStringValue() != null)
.filter(entry -> !entry.getValue().getStringValue().trim().isEmpty())
.collect(Collectors.toMap(Map.Entry::getKey, unit -> unit.getValue().getStringValue()));
if (this.kvMapper != null) {
Map<Integer, String> headMapTmp = new HashMap<>();
for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
Integer excelIndex = entry.getKey();
String excelHeadValue = entry.getValue();
String systemHeadValue = this.kvMapper.get(excelHeadValue);
if (systemHeadValue == null) continue;//不存在的记录,不写入
headMapTmp.put(excelIndex, systemHeadValue);
}
headMap = headMapTmp;
}
this.headMap = headMap;
}
/**
* 循环读取文件行内容
*
* @param data 当前读取的一行数据
* @param context 上下文对象
*/
public void invoke(Map<Integer, String> data, AnalysisContext context) {
if (this.readError) return;//读取数据出现异常,直接跳过
Map<String, String> line = new HashMap<>();
for (Map.Entry<Integer, String> entry : this.headMap.entrySet()) {
String fieldName = entry.getValue();
String value = data.get(entry.getKey());
line.put(fieldName, value);//读取excel文件转化为json,一行一个json对象
}
queuePut(line);
}
/**
* 消费数据
*
* @return 消费的一条数据
*/
public Map<String, String> queueGetData() throws Exception {
int waitFrequency = 0;
while (true) {
boolean status = this.readSuccess;
Map<String, String> line = this.queue.poll(1, TimeUnit.SECONDS); // 等待1秒
if (line != null) return line;
if (status) return null;
waitFrequency++;
if (waitFrequency > this.maxWaitFrequency) throw new Exception("消费数据失败");
}
}
/**
* 读取结束用于释放资源
*
* @param context 上下文对象
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
readSuccess = true;
}
/**
* 关闭资源
*
* @param obj 资源对象
*/
private void close(AutoCloseable obj) {
if (obj == null) return;
try {
obj.close();
} catch (Exception e) {
logger.error("处理excel关闭连接异常,连接类型: [{}]", obj.getClass().getName(), e);
}
}
public void close() {//主动关闭
close(excelReader);
close(inputStream);
}
/**
* 读取的数据写入队列
*
* @param line 读取写入数据到队列
*/
private void queuePut(Map<String, String> line) {
try {
this.queue.put(line);
this.sum++;
} catch (InterruptedException e) {
this.readError = true;
}
}
/**
* 获取读取的累计数据量
*
* @return 累计数据量
*/
public int getDataSum() {
return this.sum;
}
public static void main(String[] args) throws Exception {
String fileName = "C:\\Users\\admin\\Desktop\\data\\1.xls";
ExcelReadUtil excelListener = new ExcelReadUtil(fileName);//该监听器一行行将文件读取到内存
// ExcelListener excelListener = new ExcelListener(fileName,kvMapper);//kvMapper代表excel文件的头字段与转化后头字段的映射关系
while (true) {
Map<String, String> map = excelListener.queueGetData();
if (map == null) break;
System.out.println(map);
}
System.out.println("结束");
System.out.println("累计数据量: " + excelListener.getDataSum());
}
}3.写入代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.yn.util.resource.ResourceUtil;
import java.util.*;
import java.util.stream.Collectors;
public class ExcelWriterUtil implements AutoCloseable {
private final Map<String, List<String>> headerMap;//多页写入的头字段列表
private final Map<String, WriteSheet> sheetMap = new HashMap<>();//excel的分页对象
private List<String> header;//字段的写入顺序
private final ExcelWriter excelWriter;//excel写入对象
public ExcelWriterUtil(String fileName, List<String> header) {
this.excelWriter = EasyExcel.write(fileName).build();
this.headerMap = new HashMap<>();
this.header = header == null ? new ArrayList<>() : header;
}
public ExcelWriterUtil(String fileName) {
this.excelWriter = EasyExcel.write(fileName).build();
this.headerMap = new HashMap<>();
this.header = new ArrayList<>();
}
@Override
public void close() {
this.excelWriter.close();
}
/**
* 写入完成前先刷新否则别的进程读不到
*/
public void finish() {
this.excelWriter.finish();
}
/**
* 写入一行数据
*
* @param name 写入的sheet名称
* @param dataLine 需要写入的数据
*/
public void write(String name, Map<String, String> dataLine) {
WriteSheet writeSheet = getWriteSheet(name);
if (!this.headerMap.containsKey(name)) {
if (header.isEmpty()) header = new ArrayList<>(dataLine.keySet());//设置字段
this.headerMap.put(name, header);//写入head
List<List<String>> collect = header.stream().map(Arrays::asList).collect(Collectors.toList());
writeSheet.setHead(collect);
}
List<Object> mapperDataLine = this.headerMap.get(name).stream().map(dataLine::get).collect(Collectors.toList());
this.excelWriter.write(Collections.singletonList(mapperDataLine), writeSheet);
}
/**
* 写入多行数据
*
* @param name 写入的sheet名称
* @param dataBatch 需要写入的数据
*/
public void writeBatch(String name, List<Map<String, String>> dataBatch) {
if (dataBatch.isEmpty()) return;
WriteSheet writeSheet = getWriteSheet(name);
if (!this.headerMap.containsKey(name)) {
if (header.isEmpty()) header = new ArrayList<>(dataBatch.get(0).keySet());//设置字段
this.headerMap.put(name, header);
List<List<String>> collect = header.stream().map(Arrays::asList).collect(Collectors.toList());
writeSheet.setHead(collect);
}
List<String> header = this.headerMap.get(name);
List<List<Object>> mapperDataLine = new ArrayList<>();
for (Map<String, String> kvLineData : dataBatch) {
List<Object> dataLine = new ArrayList<>();
for (String key : header) {
Object value = kvLineData.get(key);
dataLine.add(value);
}
mapperDataLine.add(dataLine);
}
this.excelWriter.write(mapperDataLine, writeSheet);
}
/**
* 获取写入的sheet对象
*
* @param name sheet的名称
* @return 返回sheet对象
*/
private WriteSheet getWriteSheet(String name) {
if (!this.sheetMap.containsKey(name)) {
this.sheetMap.put(name, EasyExcel.writerSheet(name).build());
}
return this.sheetMap.get(name);
}
public static void main(String[] args) {
String fileName = "C:\\Users\\admin\\Desktop\\1\\11.xlsx";
ExcelWriterUtil writerExcel = new ExcelWriterUtil(fileName);
// ExcelWriterUtil writerExcel = new ExcelWriterUtil(fileName,header);//手动设置头字段的顺序
List<Map<String, String>> listData = new ArrayList<>();
Map<String, String> map1 = new HashMap<>();
Map<String, String> map2 = new HashMap<>();
map1.put("姓名", "张三");
map1.put("年龄", "123");
map2.put("姓名", "张四");
map2.put("年龄", "1234");
listData.add(map1);
listData.add(map2);
writerExcel.write("分页1", map1);
writerExcel.write("分页1", map1);
writerExcel.write("分页2", map2);
writerExcel.writeBatch("批量分页3", listData);
writerExcel.finish();
ResourceUtil.close(writerExcel);
}
}三.excel格式化写入指定位置 合并单元格等
1.pom
<dependency><!-- 自动get/set等 -->
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
<dependency><!-- 处理poi依赖冲突问题 -->
<groupId>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.6.1</version>
</dependency>
<dependency><!-- excel处理依赖 -->
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>2.model格式
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
@Data
public class UnitModel {
private Object value;
private HSSFCellStyle style;
private int firstRow;//起始行
private int lastRow;//结束行
private int firstCol;//起始列
private int lastCol;//结束列
final int left = 0;//向右移动的格子
final int down = 0;//向下移动的格子
public UnitModel(int firstRow, int lastRow, int firstCol, int lastCol, HSSFCellStyle style, Object value) {
this.firstRow = firstRow + down;
this.lastRow = lastRow + down;
this.firstCol = firstCol + left;
this.lastCol = lastCol + left;
this.style = style;
this.value = value;
}
public UnitModel(int firstRow, int lastRow, int firstCol, int lastCol, HSSFCellStyle style) {
this(firstRow, lastRow, firstCol, lastCol, style, null);
}
public UnitModel(int firstRow, int lastRow, int firstCol, int lastCol, String value) {
this(firstRow, lastRow, firstCol, lastCol, null, value);
}
public UnitModel(int firstRow, int lastRow, int firstCol, int lastCol) {
this(firstRow, lastRow, firstCol, lastCol, null, null);
}
public UnitModel(int firstRow, int firstCol, HSSFCellStyle style, String value) {
this.firstRow = firstRow;
this.firstCol = firstCol;
this.style = style;
this.value = value;
}
}3.工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 写入数据到excel
*/
public class ExcelUtil {
/**
* 读取Excel文件
*
* @param fileName 文件名称
* @return 文件内容
* @throws IOException 异常信息
*/
public static List<List<String>> readData(String fileName) throws IOException {
List<List<String>> data = new ArrayList<>();
FileInputStream fis = null;
Workbook workbook = null;
try {
fis = new FileInputStream(fileName);
workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
Row head = iterator.next();
List<String> headLine = new ArrayList<>();
data.add(headLine);
for (Cell cell : head) {
String cellValue = getCellValue(cell);
headLine.add(cellValue);
}
while (iterator.hasNext()) {
Row row = iterator.next();
List<String> unit = new ArrayList<>();
data.add(unit);
for (int i = 0; i < headLine.size(); i++) {
Cell cell = row.getCell(i);
String cellValue = getCellValue(cell);
unit.add(cellValue);
}
}
} finally {
close(fis);
close(workbook);
}
return data;
}
/**
* 读取Excel文件
*
* @param fileName 文件名称
* @param rows 读取的行数
* @return 文件内容
* @throws IOException 异常信息
*/
public static List<List<String>> readData(String fileName, int rows) throws IOException {
List<List<String>> data = new ArrayList<>();
FileInputStream fis = null;
Workbook workbook = null;
try {
fis = new FileInputStream(fileName);
workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
while (iterator.hasNext()) {
Row row = iterator.next();
List<String> unit = new ArrayList<>();
data.add(unit);
for (int i = 0; i < rows; i++) {
Cell cell = row.getCell(i);
String cellValue = getCellValue(cell);
unit.add(cellValue);
}
}
} finally {
close(fis);
close(workbook);
}
return data;
}
public static void writerSheet(HSSFWorkbook workbook, Map<String, List<UnitModel>> allData) {
for (Map.Entry<String, List<UnitModel>> entry : allData.entrySet()) {
String name = entry.getKey();
if (name == null) name = "未知";
List<UnitModel> dataList = entry.getValue();
Sheet sheet = workbook.createSheet(name); // 创建一个工作表
sheet.autoSizeColumn(0);
for (UnitModel unitModel : dataList) {
setValue(sheet, unitModel);
}
}
}
/**
* 读取内容
*/
private static String getCellValue(Cell cell) {
if (cell == null) return "";
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;
}
public static void setValue(Sheet sheet, UnitModel model) {
if (model.getFirstRow() == model.getLastRow() && model.getFirstCol() == model.getLastCol()) {
setUnitValue(sheet, model);
} else {
merge(sheet, model);
}
}
public static void setUnitValue(Sheet sheet, UnitModel model) {
HSSFCellStyle style = model.getStyle();
int firstRow = model.getFirstRow();
int firstCol = model.getFirstCol();
Object value = model.getValue();
Row row = sheet.getRow(firstRow);
if (row == null) row = sheet.createRow(firstRow);
Cell cell = row.getCell(firstCol);
if (cell == null) cell = row.createCell(firstCol);
if (value != null) cell.setCellValue(String.valueOf(value));
if (style != null) cell.setCellStyle(style);
}
private static void merge(Sheet sheet, UnitModel model) {
HSSFCellStyle style = model.getStyle();
int firstRow = model.getFirstRow();
int lastRow = model.getLastRow();
int firstCol = model.getFirstCol();
int lastCol = model.getLastCol();
Object value = model.getValue();
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
for (int i = firstRow; i <= lastRow; i++) {
Row row = sheet.getRow(i);//3, 3, 0, 23
if (row == null) row = sheet.createRow(i);
for (int j = firstCol; j <= lastCol; j++) {
Cell cell = row.getCell(j);
if (cell == null) cell = row.createCell(j);
if (style != null) cell.setCellStyle(style);
}
}
Row row = sheet.getRow(firstRow);
Cell cell = row.getCell(firstCol);
//设置cell的宽度
if (value != null) {
cell.setCellValue(String.valueOf(value));
cell.setCellStyle(style);
}
}
private static void close(AutoCloseable closeable) {
try {
if (closeable != null) closeable.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void main(String[] args) throws IOException {
String pathFileName = "C:\\Users\\admin\\Desktop\\1.xlsx";
if (new File(pathFileName).exists()) new File(pathFileName).delete();
List<UnitModel> unitModels = new ArrayList<>();
unitModels.add(new UnitModel(0, 0, 0, 2, "镇政策数量前五"));//(起始行,结束行,起始列,结束列,字段里面的内容), 跨行自动合并, 被使用的单元格不能再一次写入数据
unitModels.add(new UnitModel(1, 1, 0, 0, "镇名称"));
unitModels.add(new UnitModel(1, 1, 1, 1, "数量"));//模拟数据
Map<String, List<UnitModel>> allData = new HashMap<>();
allData.put("第一页", unitModels);
HSSFWorkbook workbook = null;
FileOutputStream out = null;
try {
workbook = new HSSFWorkbook();
ExcelUtil.writerSheet(workbook, allData);
out = new FileOutputStream(pathFileName);
workbook.write(out);
List<List<String>> lists = readData(pathFileName);
System.out.println(lists);
} finally {
close(out);
close(workbook);
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
