poi结构说明
HSSF提供读写Microsoft Excel XLS格式档案的功能
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能
HWPF提供读写Microsoft Word DOC格式档案的功能
HSLF提供读写Microsoft PowerPoint格式档案的功能
HDGF提供读Microsoft Visio格式档案的功能
HPBF提供读Microsoft Publisher格式档案的功能
HSMF提供读Microsoft Outlook格式档案的功能
引入poi依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
一、Excel文件的导出
第一步、获取表内容数据：
根据表头内容与实体类属性对应的map，利用反射机制获取get方法来取出该实体数据
/**
* @param excelName 文件名称
* @param fileFormat 文件格式
* @param sheetName sheet页名称
* @param map 表头内容-实体类属性
* @param lists 表数据
* @param response 导出
*/
public static void exportExcel(String excelName, String fileFormat, String sheetName,
Map<String, String> map, List<?> lists, HttpServletResponse response) {
List<String> titleLists = new ArrayList<>();
List<String> attributeLists = new ArrayList<>();
for (String next : map.keySet()) {
titleLists.add(next);
attributeLists.add(map.get(next));
}
Map<Integer, List<String>> contents = new LinkedHashMap<>();
for (int i = 0; i < lists.size(); i++) {
List<String> list = new ArrayList<>();
for (String s : attributeLists) {
String value = EntityIsNull.getValue(s, lists.get(i),null);
if (value != null && !"".equals(value)) {
list.add(value);
} else {
list.add("");
}
}
contents.put(i, list);
}
export(excelName, fileFormat, sheetName, titleLists, contents, response);
}
第二步、开始导出
/**
* @param excelName 文件名称
* @param fileFormat 文件格式
* @param sheetName sheet页名称
* @param titles 表头内容
* @param contents 表格内容
* @param response 导出
*/
private static void export(String excelName, String fileFormat, String sheetName, List<String> titles,
Map<Integer, List<String>> contents, HttpServletResponse response) {
Workbook workbook = null;
try {
if ("xls".equalsIgnoreCase(fileFormat)) {
workbook = new HSSFWorkbook();
} else if ("xlsx".equalsIgnoreCase(fileFormat)) {
workbook = new XSSFWorkbook();
} else {
workbook = new SXSSFWorkbook(100);
}
sheetName = sheetName != null ? sheetName : "sheet";
CellStyle style = getStyle(workbook, false);
Sheet sheet = workbook.createSheet(sheetName);
//定义当前表格下标
int lastRowNum = 0;
//创建行
Row titleRow = sheet.createRow(lastRowNum);
//设置行高
titleRow.setHeightInPoints(22);
//写入表格标题
for (int i = 0; i < titles.size(); i++) {
//创建列
Cell titleCell = titleRow.createCell(i);
//设置单元格属性
titleCell.setCellType(CellType.STRING);
//写入内容
titleCell.setCellValue(titles.get(i));
//设置样式
titleCell.setCellStyle(style);
}
lastRowNum++;
//写入表格内容
for (int i = 0; i < contents.size(); i++) {
Row contentRow = sheet.createRow(lastRowNum + i);
contentRow.setHeightInPoints(22);
List<String> list = contents.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = contentRow.createCell(j);
cell.setCellType(CellType.STRING);
cell.setCellValue(list.get(j));
cell.setCellStyle(style);
}
}
//设置自动列宽
setSizeColumn(sheet, titleRow.getPhysicalNumberOfCells());
workbook.write(generateResponseExcel(excelName, response));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
编辑表格内样式
/** 表格内样式调整 */
public static CellStyle getStyle(Workbook workbook, boolean setFont) {
CellStyle cellStyle = workbook.createCellStyle();
//设置内容居中-水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置内容居中-垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框 THIN--细实线 DOTTED--细虚线
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
//设置字体
if (setFont) {
Font font = workbook.createFont();
//字体加粗
font.setBold(true);
//字体格式
font.setFontName("宋体");
//字体字号
font.setFontHeight((short) (18 * 20));
cellStyle.setFont(font);
}
//设置文字自动换行
cellStyle.setWrapText(false);
return cellStyle;
}
第三步、设定响应请求头格式，发送文件到客户端
/**
* @param excelName 要生成的文件名字
* @param response 返回表格
*/
private static ServletOutputStream generateResponseExcel(String excelName, HttpServletResponse response) throws IOException {
excelName = excelName == null || "".equals(excelName) ? "excel" : URLEncoder.encode(excelName, "UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + excelName + ".xlsx");
return response.getOutputStream();
}
二、Excel文件的导入
/**
* @param file 文件
* @param map 表头内容-实体类属性
* @return List<Map < String, String>>
*/
public static List<Map<String, String>> importExcel(MultipartFile file, Map<String, String> map) {
//存储表格数据
List<Map<String, String>> list = new ArrayList<>();
String fileName = file.getOriginalFilename();
if (fileName != null) {
fileName = fileName.substring(fileName.lastIndexOf(".") + 1);
}
Workbook workbook = null;
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
if ("xls".equalsIgnoreCase(fileName)) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}else {
workbook = WorkbookFactory.create(inputStream);
}
//获取第一个sheet页内容
Sheet sheetAt = workbook.getSheetAt(0);
//获取表格第一行
Row titleRow = sheetAt.getRow(0);
//获取表格行数
int rowCount = sheetAt.getPhysicalNumberOfRows();
for (int i = 1; i < rowCount; i++) {
Map<String, String> hashMap = new HashMap<>();
//从表格第二行开始读取
Row contentRow = sheetAt.getRow(i);
if (!isEmptyRow(contentRow)) {
//获取行中的列数
int cellCount = contentRow.getPhysicalNumberOfCells();
for (int j = 0; j < cellCount; j++) {
Cell titleCell = titleRow.getCell(j);
Cell contentCell = contentRow.getCell(j);
String value = null;
switch (contentCell.getCellType()) {
case STRING:
value = contentCell.getStringCellValue().trim();
value = StringUtils.isEmpty(value) ? "" : value;
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(contentCell)) {
Date date = contentCell.getDateCellValue();
value = DateUtils.getString(date, "yyyy-MM-dd HH:mm:ss");
} else {
contentCell.setCellType(CellType.STRING);
value = contentCell.getStringCellValue();
value = StringUtils.isEmpty(value) ? "" : value;
}
break;
case BOOLEAN:
value = String.valueOf(contentCell.getBooleanCellValue());
break;
default:
value = "";
break;
}
String title = titleCell.getStringCellValue();
hashMap.put(map.get(title), value);
}
list.add(hashMap);
}
}
workbook.close();
inputStream.close();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
判断表格行数据是否为空
/**
* 判断表格行是否为空
*
* @param row 行
* @return boolean
*/
private static boolean isEmptyRow(Row row) {
if (row == null) {
return true;
}
//表开始列下标
int firstCellNum = row.getFirstCellNum();
//表最后列下标
int lastCellNum = row.getLastCellNum();
//空列数据
int nullCellNum = 0;
for (int i = firstCellNum; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (cell == null || CellType.BLANK.equals(cell.getCellType())) {
nullCellNum++;
continue;
}
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue().trim();
if (StringUtils.isEmpty(cellValue)) {
nullCellNum++;
}
}
return (lastCellNum - firstCellNum) == nullCellNum;
}
判断表格列值是否为空文章来源：https://www.toymoban.com/news/detail-531533.html
/**
* 判断列是否为空
*
* @param cell 列
* @return boolean
*/
private static boolean isEmptyCell(Cell cell) {
return cell == null || CellType.BLANK.equals(cell.getCellType());
}
设置表格自动列宽文章来源地址https://www.toymoban.com/news/detail-531533.html
/** 设置自动列宽 */
private static void setSizeColumn(Sheet sheet, int columnLength) {
for (int columnNum = 0; columnNum <= columnLength; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow; // 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, (int) ((columnWidth + 2.5) * 256));
}
}
获取对应属性名的值
/**
* 获取对应属性名的值
*
* @param fieldName 字段名
* @param object 对象
* @param format 时间格式(yyyy-MM-dd HH:mm:ss)
* @return String
*/
public static String getValue(String fieldName, Object object, String format) {
String value = null;
try {
String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class<?> aClass = object.getClass();
Field field = aClass.getDeclaredField(fieldName);
/** 获取属性类型 */
String type = field.getGenericType().getTypeName();
Method method = aClass.getMethod(getter);
if (type.contains("Date")) {
Date date = (Date) method.invoke(object);
/** 设置时间格式 */
if (date != null) {
if (format != null && !"".equals(format)) {
value = DateUtils.getString(date, format);
} else {
value = DateUtils.getString(date);
}
}
} else {
value = method.invoke(object) + "";
}
} catch (Exception e) {
e.printStackTrace();
}
return value;
}
测试
public static void main(String[] args) {
//文件导出
Map<String, String> map = new HashMap<>();
map.put("序号", "id");
map.put("姓名", "userName");
map.put("地址", "site");
List<User> userList = new ArrayList<>();
User user = new User();
user.setId(1);
user.setUserName("张三");
user.setSite("地球");
userList.add(user);
exportExcel("用户信息", "xlsx", "用户", map, userList, null);
//文件导入
File excelFile = new File("C:/test/用户信息.xlsx");
List<Map<String, String>> list = importExcel(excelFile, map);
System.out.println("list = " + list);
}
到了这里，关于poi实现Excel文件的导入导出的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
