一. 概述
现在java后端主流框架为springboot, 而大部分企业管理系统设计都会设计excel的导入导出, 本文将介绍springboot整合Apache POI的使用
二. 使用
2.1 导包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
2.2.1 excel导出示例
controller层
@RestController
@RequestMapping("/exportExcel")
public class exportExcel {
@Autowired
private CrateWorkbook crateWorkbook;
@RequestMapping()
public void exportExcel(HttpServletResponse response) throws Exception {
// 创建工作表
HSSFWorkbook workbook = crateWorkbook.crateWorkbook();
// 文件名格式化
String filename = URLEncoder.encode("template.xls", "UTF-8");
OutputStream outputStream = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filename);
// 文件导出
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
sevice层
@Service
public class CrateWorkbook {
public HSSFWorkbook crateWorkbook() {
// 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
String[] tableHeaders = {"id", "姓名", "年龄"};
// 创建工作表:Sheet1
HSSFSheet sheet = workbook.createSheet("Sheet1");
// 创建单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置水平对齐方式:中间对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐方式:中间对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置字体样式
Font font = workbook.createFont();
font.setColor(HSSFColor.RED.index);
font.setBold(true);
cellStyle.setFont(font);
// 将第一行的三个单元格给合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
HSSFRow row = sheet.createRow(0);
// 设置表标题为: 通讯录
HSSFCell beginCell = row.createCell(0);
beginCell.setCellValue("通讯录");
beginCell.setCellStyle(cellStyle);
row = sheet.createRow(1);
// 创建表头
for (int i = 0; i < tableHeaders.length; i++) {
HSSFCell cell = row.createCell(i);
// 设置内容
cell.setCellValue(tableHeaders[i]);
// 设置格式
cell.setCellStyle(cellStyle);
}
// 创建要插入的内容
List<User> users = new ArrayList<>();
users.add(new User(1L, "张三", 20));
users.add(new User(2L, "李四", 21));
users.add(new User(3L, "王五", 22));
// 遍历插入内容
for (int i = 0; i < users.size(); i++) {
row = sheet.createRow(i + 2);
User user = users.get(i);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getAge());
}
return workbook;
}
}
2.3 excel导入示例
controller层
@RestController
@RequestMapping("/importExcel")
public class importExcel {
@Autowired
private ImportExcelUtil importExcelUtil;
@RequestMapping()
public Object importExcel(@RequestParam("file") MultipartFile file){
// 获取文件输入流
InputStream inputStream = file.getInputStream();
// 获取缓冲流
BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
// 获取工作簿
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
// 调用方法获取内容
List<Map<String, Object>> result = importExcelUtil. importExcel(workbook,0);
return result;
}
}
sevice层
@Service
public class ImportExcelUtil {
public List<Map<String, Object>> importExcel(Workbook wb, int sheetNum) {
List<Map<String, Object>> dataLst = new ArrayList<>();
// 获取指定工作表
Sheet sheet = wb.getSheetAt(sheetNum);
// 获取最后一行的行号(0开始)
int totalRows = sheet.getLastRowNum();
// 获取每行的列数, 获取内容行的格数
int totalCells = sheet.getRow(2).getLastCellNum();
// 获取标题行数据
String[] head = new String[totalCells];
// 获取首行
Row headRow = sheet.getRow(0);
// 遍历每个单元格的值
for (int i = 0; i < totalCells; i++) {
Cell cell = headRow.getCell(i);
head[i] = getCellValue(cell);
}
// 遍历内容行获取数据,从2行开始,也就是行的下标为1
for (int r = 1; r <= totalRows; r++) {
headRow = sheet.getRow(r);
if (null == headRow) {
// 过滤空行,空行一下内容全部上移一行
sheet.shiftRows(r + 1, totalRows, -1);
r--;
totalRows --;
continue;
}
// 过滤空行, 当前行每个单元格的值都为空时, 当前行一下行全部往上移一行
int count = 0;
for (int i = 0; i < totalCells; i++) {
// 获取当前单元格
Cell cell = headRow.getCell(i);
// 调用方法获取数值
String cellValue = this.getCellValue(cell);
if (null == cellValue || "".equals(cellValue)) {
count++;
}
}
if (count == totalCells) {
if (r + 1 > totalRows) {
break;
}
sheet.shiftRows(r + 1, totalRows, -1);
r--;
totalRows --;
continue;
}
// 开始遍历行的单元格值
HashMap<String, Object> map = new HashMap<String, Object>();
// 遍历行单元格
for (int c = 0; c < totalCells; c++) {
Cell cell = headRow.getCell(c);
String cellValue = this.getCellValue(cell);
map.put(head[c], cellValue);
}
dataLst.add(map);
}
return dataLst;
}
// 根据单元格的格式获取值
public String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC:
// 数值
cellValue = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
}else {
// 数字,表格中返回的数字类型是科学计数法,因此需要转换
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##########");
cellValue = format.format(value);
} else if (temp.equals("000000")) {
format.applyPattern("000000");
cellValue = format.format(value);
} else {
DataFormatter formatter = new DataFormatter();
cellValue = formatter.formatCellValue(cell);
}
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
// cellValue = cell.getCellFormula() + "";
try {
// 数字,表格中返回的数字类型是科学计数法,因此需要转换
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##########");
cellValue = format.format(value);
} else if (temp.equals("000000")) {
format.applyPattern("000000");
cellValue = format.format(value);
} else {
DataFormatter formatter = new DataFormatter();
cellValue = formatter.formatCellValue(cell);
}
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue());
}
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
}
三. 常用方法封装
下面介绍都是返回Workbook, 同学们可以更具需求再次封装
参考文章: 简单使用Apache POI
public class ExcelUtil {
public static final int Excel2003 = 0;
public static final int Excel2007 = 1;
/**
* 获取输出流
*
* @param response
* @param fileName
* @return
* @throws IOException
*/
public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) throws IOException {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
}
/**
* 创建一个新的Workbook, 设置标题列
* @param headName
* @return
*/
public static Workbook createWorkbookModel(List<String> headNames){
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
// 创建工作表:Sheet1
Sheet sheet = workbook.createSheet("Sheet1");
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
// 设置水平对齐方式:中间对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐方式:中间对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置字体样式
Font font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setBold(true);
cellStyle.setFont(font);
Row row = sheet.createRow(0);
// 创建表头
for (int i = 0; i < headNames.size(); i++) {
org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);
// 设置内容
cell.setCellValue(headNames.get(i));
// 设置格式
cell.setCellStyle(cellStyle);
}
return workbook;
}
/**
* 根据版本号，获取Excel poi对象
*
* @param edition 版本号
* @param in 输入流
* @return
* @throws IOException
*/
public static Workbook getWorkbook(int edition, InputStream in) throws IOException {
if (edition == 0) {
return new HSSFWorkbook(in);
} else if (edition == 1) {
return new XSSFWorkbook(in);
}
return null;
}
// 根据单元格的格式获取值
public static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC:
// 数值
cellValue = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
}else {
// 数字,表格中返回的数字类型是科学计数法,因此需要转换
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##########");
cellValue = format.format(value);
} else if (temp.equals("000000")) {
format.applyPattern("000000");
cellValue = format.format(value);
} else {
DataFormatter formatter = new DataFormatter();
cellValue = formatter.formatCellValue(cell);
}
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
// cellValue = cell.getCellFormula() + "";
try {
// 数字,表格中返回的数字类型是科学计数法,因此需要转换
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##########");
cellValue = format.format(value);
} else if (temp.equals("000000")) {
format.applyPattern("000000");
cellValue = format.format(value);
} else {
DataFormatter formatter = new DataFormatter();
cellValue = formatter.formatCellValue(cell);
}
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue());
}
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
/**
* 从指定excel表格中逐行读取数据
*
* @param workbook 工作簿
* @param startRow 开始行
* @param startCol 开始列
* @param indexSheet 工作表下标
* @return
*/
public static List<List<String>> getExcelString(Workbook workbook, int startRow, int startCol, int indexSheet) {
List<List<String>> stringTable = new ArrayList<List<String>>();
// 获取指定表对象
Sheet sheet = workbook.getSheetAt(indexSheet);
// 获取最大行数
int rowNum = sheet.getLastRowNum();
for (int i = startRow; i <= rowNum; i++) {
List<String> oneRow = new ArrayList<String>();
Row row = sheet.getRow(i);
// 根据当前指针所在行数计算最大列数
int colNum = row.getLastCellNum();
for (int j = startCol; j <= colNum; j++) {
// 确定当前单元格
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
// 生成一行数据
oneRow.add(cellValue);
}
stringTable.add(oneRow);
}
return stringTable;
}
/**
* 根据给定的数据直接生成workbook
*
* @param workbook 工作簿
* @param sheetName 工作表名字
* @param data 数据
* @return
*/
public static Workbook createExcel(Workbook workbook, String sheetName, List<List<String>> data) {
Sheet sheet = workbook.createSheet(sheetName);
for (int i = 0; i < data.size(); i++) {
List<String> oneRow = data.get(i);
Row row = sheet.createRow(i);
for (int j = 0; j < oneRow.size(); j++) {
Cell cell = row.createCell(j);
cell.setCellValue(oneRow.get(j));
}
}
return workbook;
}
/**
* 给单元格设置背景色/内容/批注
* @param wb 工作表
* @param cell 单元格
* @param sheet 工作蒲
* @param annotation 批注内容
* @param content 单元格内容
*/
public static void setCellStyle(XSSFWorkbook wb,XSSFCell cell,XSSFSheet sheet,String annotation,String content) {
// 创建绘图对象
XSSFDrawing p = sheet.createDrawingPatriarch();
// 插入单元格内容
cell.setCellValue(content);
// 获取批注对象
// (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short
// col2, int row2)
// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) 5, 5, (short) 10, 10));
// 输入批注信息
comment.setString(new XSSFRichTextString(annotation));
// 添加作者,选中B5单元格,看状态栏
// comment.setAuthor("toad");
// 将批注添加到单元格对象中
cell.setCellComment(comment);
CellStyle cellStyle=wb.createCellStyle();
// 设置背景色为红色
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置单元格样式
cell.setCellStyle(cellStyle);
}
/**
* 往指定的sheet表中插入数据，插入的方法是提供一组valueMap。int[]是2维数组代表需要插入的数据坐标，从0开始
*
* @param workbook
* @param sheetIndex
* @param valueMap
* @return
*/
public static Workbook insertExcel(Workbook workbook, int sheetIndex, Map<int[], String> valueMap) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
Iterator<Entry<int[], String>> it = valueMap.entrySet().iterator();
while (it.hasNext()) {
Entry<int[], String> cellEntry = it.next();
int x = cellEntry.getKey()[0];
int y = cellEntry.getKey()[1];
String value = cellEntry.getValue();
Row row = sheet.getRow(y);
Cell cell = row.getCell(x);
cell.setCellValue(value);
}
return workbook;
}
/**
* 设置指定行的行高
*
* @param workbook
* @param rowHeight
* @param sheetIndex
* @param rowIndex
* @return
*/
public static Workbook setRowHeight(Workbook workbook, int rowHight, int sheetIndex, int rowIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
Row row = sheet.getRow(rowIndex);
row.setHeight((short) rowHight);
return workbook;
}
/**
* 设置列宽
*
* @param workbook
* @param columnWidth
* @param sheetIndex
* @param columnIndex
* @return
*/
public static Workbook setColumnWidth(Workbook workbook, int columnWidth, int sheetIndex, int columnIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
sheet.setColumnWidth(columnIndex, columnWidth);
return workbook;
}
/**
* 删除指定行
*
* @param workbook
* @param sheetIndex
* @param rowIndex
* @return
*/
public static Workbook removeRow(Workbook workbook, int sheetIndex, int rowIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
int lastRowNum = sheet.getLastRowNum();
if (rowIndex >= 0 && rowIndex < lastRowNum) {
sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
}
if (rowIndex == lastRowNum) {
sheet.removeRow(sheet.getRow(rowIndex));
}
return workbook;
}
/**
* 在指定位置插入空白行
*
* @param workbook
* @param sheetIndex
* @param rowIndex
* @return
*/
public static Workbook insertBlankRow(Workbook workbook, int sheetIndex, int rowIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
int lastRowNum = sheet.getLastRowNum();
if (rowIndex >= 0 && rowIndex <= lastRowNum) {
sheet.shiftRows(rowIndex, lastRowNum, 1);
// 获得上一行的Row对象
Row preRow = sheet.getRow(rowIndex - 1);
short rowNum = preRow.getLastCellNum();
Row curRow = sheet.createRow(rowIndex);
// 新生成的Row创建与上一个行相同风格的Cell
for (short i = preRow.getFirstCellNum(); i < rowNum; i++) {
Cell cell = preRow.getCell(i);
CellStyle style = cell.getCellStyle();
curRow.createCell(i).setCellStyle(style);
}
return workbook;
}
return null;
}
/**
* 根据sheet(0)作为模板重建workbook
*
* @param workbook
* @param sheetNum
* @param sheetNames
* @return
*/
public static Workbook rebuildWorkbook(Workbook workbook, int sheetNum, String... sheetNames) {
if(sheetNames.length == sheetNum){
for (int i = 0; i < sheetNum; i++) {
workbook.cloneSheet(0);
// 生成后面的工作表并指定表名
workbook.setSheetName(i + 1, sheetNames[i]);
}
// 删除第一张工作表
workbook.removeSheetAt(0);
return workbook;
}
return null;
}
}
四. 附:excel导入出现科学计数值处理工具类
public static Pattern pattern1 = Pattern.compile("\\d{1,10}\\.?\\d{0,5}");
public final static Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
public final static DecimalFormat ds = new DecimalFormat("0");
public final static boolean isENum(String input) {//判断输入字符串是否为科学计数法
return pattern.matcher(input).matches();
}
/**
* 判断是否为科学数值, 是则进行科学计数法转十进制数字
* @param num 值
* @return
*/
public static String numConvert(String num){
if(null != num){
if(isENum(num)){
Double d = 1.6D;
//使得结果精确的初始化姿势
BigDecimal bigDecimal = new BigDecimal(d.toString());
bigDecimal = new BigDecimal(num);
String str = bigDecimal.toPlainString();
return subZeroAndDot(str);
}
return subZeroAndDot(num);
}else {
return "0";
}
}
/**
* 使用java正则表达式去掉多余的.与0
* @param s
* @return
*/
public static String subZeroAndDot(String s){
if(s.indexOf(".") > 0){
s = s.replaceAll("0+?$", "");//去掉多余的0
s = s.replaceAll("[.]$", "");//如最后一位是.则去掉
}
return s;
}
五. 附: Workbook转InputStream
public InputStream workbookToInputStream(Workbook workbook){
InputStream inputStream = null;
try{
//临时缓冲区
ByteArrayOutputStream out = new ByteArrayOutputStream();
//创建临时文件
workbook.write(out);
byte [] bookByteAry = out.toByteArray();
inputStream = new ByteArrayInputStream(bookByteAry);
}
catch (Exception e){
e.printStackTrace();
}
return inputStream;
}
六. 附: 导出文件名带中文处理
// 文件名
String fileNameutf = new String(fileName.getBytes(), "UTF-8");
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+".xlsx");
网友评论