参考黑马教程：
https://www.bilibili.com/video/BV1eA41157va?p=5
写着写着发现有更好的Demo案例：
https://cloud.tencent.com/developer/article/1966065
所需依赖：
<dependency>
<groupId>org.apache.tika</groupId>
<artifactId>tika-core</artifactId>
<version>1.27</version> <!-- 请检查并使用最新版本 -->
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
Excel输出API常见方法：
@SneakyThrows
public void downloadXlsByJxl(HttpServletResponse response) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
ServletOutputStream outputStream = response.getOutputStream();
/* 基于输出流创建一个新的工作簿 */
WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
WritableSheet sheet = workbook.createSheet("第一个工作表", 0);
/* 创建标题行 */
List<String> titleList = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现在地址");
/* 创建列宽配置 */
int standardCharWidth = 1;
int chineseCharWidth = 3;
List<Integer> columnWidthSets = Arrays.asList(
standardCharWidth * chineseCharWidth * 2,
standardCharWidth * chineseCharWidth * 3,
standardCharWidth * standardCharWidth * 12,
standardCharWidth * 12,
standardCharWidth * standardCharWidth * 20);
Label eachLabel = null;
for (int columnIdx = 0; columnIdx < titleList.size(); columnIdx++) {
String title = titleList.get(columnIdx);
eachLabel = new Label(columnIdx, 0, title);
sheet.addCell(eachLabel);
/* 列宽设置, 列下表， 一个标准字母的宽度 */
sheet.setColumnView(columnIdx, columnWidthSets.get(columnIdx));
}
List<User> allUserList = userMapper.selectAll();
for (int recordIdx = 0, rowIdx = 1; recordIdx < allUserList.size(); recordIdx ++, rowIdx ++) {
User user = allUserList.get(recordIdx);
eachLabel = new Label(0, rowIdx, String.valueOf(user.getId()));
sheet.addCell(eachLabel);
eachLabel = new Label(1, rowIdx, user.getUserName());
sheet.addCell(eachLabel);
eachLabel = new Label(2, rowIdx, user.getPhone());
sheet.addCell(eachLabel);
eachLabel = new Label(3, rowIdx, sdf.format(user.getHireDate()));
sheet.addCell(eachLabel);
eachLabel = new Label(4, rowIdx, user.getAddress());
sheet.addCell(eachLabel);
}
/* 写入图片 非图片文件无法写入 Warning: Image type doc not supported. Supported types are png */
File file = new File("C:\\Users\\Administrator\\Pictures\\AngelLegionPhoto\\Girl_20240303155025.png");
WritableImage writableImage = new WritableImage(10, 10, 1, 1, file);
sheet.addImage(writableImage);
/* 写入链接 */
WritableHyperlink writableHyperlink = new WritableHyperlink(10, 11, new URL("https://www.bilibili.com/video/BV1eA41157va"));
sheet.addHyperlink(writableHyperlink);
writableHyperlink = new WritableHyperlink(10, 12, new File("D:\\迅雷下载\\20121015120541355.doc"));
sheet.addHyperlink(writableHyperlink);
String fileName = "JXL-Demo.xls";
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
response.setContentType("application/vnd.ms-excel");
workbook.write();
workbook.close();
outputStream.close();
}
输出结果：
Excel读取常见API：
获取存在记录的单元格的边界列和行
能满足图片，公式，普通值的获取
@SneakyThrows
public void readXlsByJxl(MultipartFile multipartFile) {
InputStream inputStream = multipartFile.getInputStream();
Workbook workbook = Workbook.getWorkbook(inputStream);
Sheet sheet = workbook.getSheet(0);
int[] columnPageBreaks = sheet.getColumnPageBreaks();
int[] rowPageBreaks = sheet.getRowPageBreaks();
int columns = sheet.getColumns();
int rows = sheet.getRows();
int numberOfImages = sheet.getNumberOfImages();
Hyperlink[] hyperlinks = sheet.getHyperlinks();
log.info("columnPageBreaks {}", Arrays.toString(columnPageBreaks));
log.info("rowPageBreaks {}", Arrays.toString(rowPageBreaks));
log.info("columns {}", columns);
log.info("rows {}", rows);
log.info("numberOfImages {}", numberOfImages);
for (int rowIdx = 0; rowIdx < rows; rowIdx++) {
StringBuilder currentRow = new StringBuilder();
for (int colIdx = 0; colIdx < columns; colIdx++) {
Cell cell = sheet.getCell(colIdx, rowIdx);
CellType type = cell.getType();
String contents = cell.getContents();
currentRow.append("type ").append(type.toString()).append(" ,").append(contents).append(" |");
}
log.info("cell -> {}", currentRow);
}
/* 读取图片 */
if (0 != numberOfImages) {
for (int i = 0; i < numberOfImages; i++) {
Image image = sheet.getDrawing(i);
File imageFile = image.getImageFile();
String name = imageFile.getName();
double column = image.getColumn();
double row = image.getRow();
byte[] imageData = image.getImageData();
Tika tika = new Tika();
/* 图片是准确的mimeType, 非图片只能识别为octStream */
String detect = tika.detect(imageData);
log.info("image -> idx {}, {} - {}, {}, {}", i, (int) column, (int) row, name, detect);
}
}
for (Hyperlink hyperlink : hyperlinks) {
boolean isFile = hyperlink.isFile();
boolean isLocation = hyperlink.isLocation();
boolean isUrl = hyperlink.isURL();
log.info("isFile {}, isLocation {}, isUrl {}", isFile, isLocation, isUrl);
String path = isFile ? hyperlink.getFile().getPath() : isUrl ? hyperlink.getURL().getPath() : "internalLocation";
int column = hyperlink.getColumn();
int row = hyperlink.getRow();
log.info("hyperlink -> {}, {}", column + "," + row, path);
}
}
样例输出
columnPageBreaks null rowPageBreaks null columns 11 rows 16 numberOfImages 1 cell -> type Label ,编号 |type Label ,姓名 |type Label ,手机号 |type Label ,入职日期 |type Label ,现在地址 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,1 |type Label ,大一 |type Label ,13800000001 |type Label ,2001-01-01 |type Label ,北京市西城区宣武大街1号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,2 |type Label ,不二 |type Label ,13800000002 |type Label ,2002-01-02 |type Label ,北京市西城区宣武大街2号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,3 |type Label ,张三 |type Label ,13800000003 |type Label ,2003-03-03 |type Label ,北京市西城区宣武大街3号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,4 |type Label ,李四 |type Label ,13800000004 |type Label ,2004-02-04 |type Label ,北京市西城区宣武大街4号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,5 |type Label ,王五 |type Label ,13800000005 |type Label ,2005-03-05 |type Label ,北京市西城区宣武大街5号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,6 |type Label ,赵六 |type Label ,13800000006 |type Label ,2006-04-06 |type Label ,北京市西城区宣武大街6号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,7 |type Label ,沈七 |type Label ,13800000007 |type Label ,2007-06-07 |type Label ,北京市西城区宣武大街7号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,8 |type Label ,酒八 |type Label ,13800000008 |type Label ,2008-07-08 |type Label ,北京市西城区宣武大街8号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,9 |type Label ,第九 |type Label ,13800000009 |type Label ,2009-03-09 |type Label ,北京市西城区宣武大街9号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,10 |type Label ,石十 |type Label ,13800000010 |type Label ,2010-07-10 |type Label ,北京市西城区宣武大街10号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,11 |type Label ,肖十一 |type Label ,13800000011 |type Label ,2011-12-11 |type Label ,北京市西城区宣武大街11号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Label ,https://www.bilibili.com/video/BV1eA41157va | cell -> type Label ,12 |type Label ,星十二 |type Label ,13800000012 |type Label ,2012-05-12 |type Label ,北京市西城区宣武大街12号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Label ,D:\迅雷下载\20121015120541355.doc | cell -> type Label ,13 |type Label ,钗十三 |type Label ,13800000013 |type Label ,2013-06-13 |type Label ,北京市西城区宣武大街13号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,14 |type Label ,贾十四 |type Label ,13800000014 |type Label ,2014-06-14 |type Label ,北京市西城区宣武大街14号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,15 |type Label ,甄世武 |type Label ,13800000015 |type Label ,2015-06-15 |type Label ,北京市西城区宣武大街15号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | image -> idx 0, 10 - 9, Girl_20240303155025.png, image/png isFile false, isLocation false, isUrl true hyperlink -> 10,11, /video/BV1eA41157va isFile true, isLocation false, isUrl false hyperlink -> 10,12, D:\迅雷下载\2012
标签：Java,type,Excel,Label,cell,new,sheet,轻量,Empty From： https://www.cnblogs.com/mindzone/p/18115970