整理了下Hutool导入导出的简单使用。
导入maven或jar包(注意这里导入的poi只是为了优化样式)
<!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
代码部分:
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.ServletOutputStream;
import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
/**
* hutool测试(导入导出)
*/
public class HutoolTest {
public static void main(String[] args) {
//导出
exportExcel();
//导入
importExcel();
}
/**
* 导出
*/
private static void exportExcel(){
//本地导出的路径
String exportPath = "E:/测试图片/测试.xlsx";
try{
//处理表头对应数据
List<String> titleList = new LinkedList<>();
titleList.add("姓名");
titleList.add("年龄");
titleList.add("地点");
// 创建一些数据
List<List<String>> data = new ArrayList<>();
data.add(Arrays.asList("张三", "25", "北京"));
data.add(Arrays.asList("李四", "30", "上海"));
// 通过工具类创建writer
ExcelWriter writer = new ExcelWriter();
// 一次性写出内容,使用默认样式,强制输出标题
writer.writeHeadRow((Iterable<?>) titleList);
// 写入内容
writer.write(data);
//设置表头宽度
setSizeColumnBt(writer.getSheet(),titleList.size());
Workbook workbook = writer.getWorkbook();
//服务器上返回时
// response.setContentType("application/vnd.ms-excel;charset=ISO-8859-1");
// filename = new String(filename.getBytes("GB2312"), "ISO8859-1");
// response.setHeader("content-disposition", "attachment;filename=" + filename);
// response.addHeader("Pargam", "no-cache");
// response.addHeader("Cache-Control", "no-cache");
// ServletOutputStream sout = response.getOutputStream();
//本地导出时
ByteArrayOutputStream sout = new ByteArrayOutputStream();
workbook.write(sout);
InputStream input = new ByteArrayInputStream(sout.toByteArray());
// 测试导出到本地
writeToLocal(exportPath,input);
System.out.println("导出完成");
// 关闭writer,释放内存
writer.close();
workbook.close();
sout.flush();
sout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导入
*/
private static void importExcel(){
//本地导入的路径
String importPath = "E:/测试图片/测试.xlsx";
// 指定 Excel 文件路径(正常是通过input流转)
File excelFile = FileUtil.file(importPath);
// 创建 ExcelReader 对象
ExcelReader reader = ExcelUtil.getReader(excelFile);
// 读取第一个sheet的数据
List<List<Object>> data = reader.read();
for (List<Object> data1 : data){
System.out.println(data1.toString());
}
}
/**
* 自适应宽度(中文支持)(表头)
*
* @param sheet sheet
* @param cellSize 因为for循环从0开始,size值为 列数-1
*/
private static void setSizeColumnBt(Sheet sheet, int cellSize) {
for (int columnNum = 0; columnNum <= cellSize; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
Row currentRow = sheet.getRow(0);
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length + 1) {
columnWidth = length + 8;
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
/**
* 将InputStream写入本地文件
* @param destination 写入本地目录
* @param input 输入流
* @throws IOException IOException
*/
public static void writeToLocal(String destination, InputStream input)
throws IOException {
int index;
byte[] bytes = new byte[1024];
FileOutputStream downloadFile = new FileOutputStream(destination);
while ((index = input.read(bytes)) != -1) {
downloadFile.write(bytes, 0, index);
downloadFile.flush();
}
input.close();
downloadFile.close();
}
}
导出结果截图:
导入结果打印:
[姓名, 年龄, 地点]
[张三, 25, 北京]
[李四, 30, 上海]