如何使用POI生成Excel模板以及设置样式？具体设置样式参考我写的工具类模板
1.导入pom文件
<!--ApachePOI依赖包-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!--支持excel 2007版本的操作-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
2.工具类
package cn.mindgd.util;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
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 org.springframework.util.Base64Utils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author JiaXinMa
* @description Excel工具类
* @date 2021/4/12
*/
@Slf4j
@Data
public class ExcelUtils {
private Workbook workbook;
private String pattern;// 日期格式
public ExcelUtils(Workbook workbook) {
this.workbook = workbook;
}
/**
* @author: JiaXinMa
* @description: 下载2007模板
* @date: 2021/6/8
*/
public static void downloadExcel2003(HttpServletResponse response, HttpServletRequest request, List<List<Object>> cells, String fireName, String sheetName) {
try {
fireName = fireName + ".xls";
ExcelUtils.setResponseAttribute(response, request, fireName);
HSSFWorkbook workbook = getHSSFWorkbook(cells, sheetName);
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
//关闭流
workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流
outputStream.close();
} catch (IOException e) {
log.info("下载模板失败！！");
e.printStackTrace();
}
}
/**
* @author: JiaXinMa
* @description: 获取一个文件对象2003版本 HSSFWorkbook
* @date: 2021/6/8
* .xls
*/
public static HSSFWorkbook getHSSFWorkbook(List<List<Object>> rowsData, String sheetName) {
//1.创建一个文件对象HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建表单sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
//3.数据进入Workbook
writeRowsDataOnWorkbook(rowsData, sheet);
return workbook;
}
/**
* @author: JiaXinMa
* @description: 下载2007模板
* @date: 2021/6/8
*/
public static void downloadExcel2007(HttpServletResponse response, HttpServletRequest request, List<List<Object>> cells, String fireName, String sheetName) {
try {
fireName = fireName + ".xlsx";
ExcelUtils.setResponseAttribute(response, request, fireName);
XSSFWorkbook workbook = getXSSFWorkbook(cells, sheetName);
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
//关闭流
workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流
outputStream.close();
} catch (IOException e) {
log.info("下载模板失败！！");
e.printStackTrace();
}
}
/**
* @author: JiaXinMa
* @description: 获取一个文件对象2007版本 XSSFWorkbook
* @date: 2021/6/8
* .xlsx
*/
public static XSSFWorkbook getXSSFWorkbook(List<List<Object>> rowsData, String sheetName) {
//1.创建一个文件对象XSSFWorkbook
XSSFWorkbook workbook = new XSSFWorkbook();
//2.创建表单sheet
XSSFSheet sheet = workbook.createSheet(sheetName);
//3.数据进入Workbook
writeRowsDataOnWorkbook(rowsData, sheet);
return workbook;
}
/**
* @author: JiaXinMa
* @description: 写数据进入Workbook
* @date: 2021/6/8
* 如果想设置样式可以在这里修改
*/
private static void writeRowsDataOnWorkbook(List<List<Object>> rowsData, Sheet sheet) {
// 遍历输出每行
for (int i = 0; i < rowsData.size(); i++) {
// 创建一个row
Row row = sheet.createRow(i);
// 每一行的数据
List<Object> rowData = rowsData.get(i);
// 遍历生成每个单元格
for (int j = 0; j < rowData.size(); j++) {
Cell cell = row.createCell(j);
// 设置单元格样式
//cell.setCellStyle()
//单元格赋值
Object cellData = rowData.get(j);
setDataOnCell(cell, cellData);
}
}
}
/**
* @author: JiaXinMa
* @description: 导出 MaterielStorage excel文件
* @date: 2021/6/8
* sheet为9列
* 第一行为9个单元格合并为一个单元格，背景为灰色，居中
* 第二行为字体加粗，背景为灰色
* 如果想要设置样式的可以参考该方法
*/
public static void downloadMaterielStorageExcelFile(HttpServletResponse response, HttpServletRequest request, List<List<Object>> rowsData, String fileName, String sheetName) {
try {
// 创建一个Workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个Sheet XSSFSheet
XSSFSheet sheet = workbook.createSheet(sheetName);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
sheet.addMergedRegion(region);
// 设置列宽
setMaterielStorageColumnWidthOnSheet(sheet);
CellStyle titleStyle = workbook.createCellStyle();//标题
CellStyle contentFirstStyle = workbook.createCellStyle();//内容第一行
CellStyle contentStyle = workbook.createCellStyle();//内容
//设置单元格背景
titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
contentFirstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
contentFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置单元格边框
setBorderOnCell(titleStyle);
setBorderOnCell(contentFirstStyle);
setBorderOnCell(contentStyle);
// 设置单元格字体
setFontStylePlus(workbook, titleStyle, "等线", 20, false);
setFontStylePlus(workbook, contentFirstStyle, "等线", 14, true);
setFontStylePlus(workbook, contentStyle, "等线", 11, false);
// 设置单元格居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
contentFirstStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setAlignment(HorizontalAlignment.CENTER);
// 遍历输出每行
for (int i = 0; i < rowsData.size(); i++) {
// 创建一个row
XSSFRow row = sheet.createRow(i);
// 每一行的数据
List<Object> rowData = rowsData.get(i);
// 遍历生成每个单元格
for (int j = 0; j < rowData.size(); j++) {
XSSFCell cell = row.createCell(j);
// 设置单元格样式
if (i == 0) {
cell.setCellStyle(titleStyle);//标题样式
} else if (i == 1) {
cell.setCellStyle(contentFirstStyle);//内容第一行样式
} else {
cell.setCellStyle(contentStyle);// 内容样式
}
//单元格赋值
Object cellData = rowData.get(j);
setDataOnCell(cell, cellData);
}
}
fileName = fileName + ".xls";
ExcelUtils.setResponseAttribute(response, request, fileName);
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
//关闭流
workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流
outputStream.close();
} catch (IOException e) {
log.info("导出excel失败");
}
}
/**
* @author: JiaXinMa
* @description: 设置列的宽度
* @date: 2021/6/8
* 设置MaterielStorage的Excel文件
*/
private static void setMaterielStorageColumnWidthOnSheet(Sheet sheet) {
sheet.setColumnWidth(0, 2500); // 第1列的宽度为2000
sheet.setColumnWidth(1, 4500); // 第2列的宽度为4500
sheet.setColumnWidth(2, 4500); // 第3列的宽度为4500
sheet.setColumnWidth(3, 4500); // 第4列的宽度为4500
sheet.setColumnWidth(4, 4500); // 第5列的宽度为4500
sheet.setColumnWidth(5, 4500); // 第6列的宽度为4500
sheet.setColumnWidth(6, 4500); // 第7列的宽度为4500
sheet.setColumnWidth(7, 4500); // 第8列的宽度为4500
sheet.setColumnWidth(8, 13000); // 第9列的宽度为13000
}
/**
* @author: JiaXinMa
* @description: 导出 MaterielStorage汇总 excel文件
* @date: 2021/6/17
* sheet为11列
* 第一行为11个单元格合并为一个单元格，背景为灰色，居中
* 第二行为字体加粗，背景为灰色
* 如果想要设置样式的可以参考该方法
*/
public static void downloadMaterielStorageTotalExcelFile(HttpServletResponse response, HttpServletRequest request, List<List<Object>> rowsData, String fileName, String sheetName) {
try {
// 创建一个Workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个Sheet XSSFSheet
XSSFSheet sheet = workbook.createSheet(sheetName);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 10);
sheet.addMergedRegion(region);
// 设置列宽
setMaterielStorageTotalColumnWidthOnSheet(sheet);
CellStyle titleStyle = workbook.createCellStyle();//标题
CellStyle contentFirstStyle = workbook.createCellStyle();//内容第一行
CellStyle contentStyle = workbook.createCellStyle();//内容
//设置单元格背景
titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
contentFirstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
contentFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置单元格边框
setBorderOnCell(titleStyle);
setBorderOnCell(contentFirstStyle);
setBorderOnCell(contentStyle);
// 设置单元格字体
setFontStylePlus(workbook, titleStyle, "等线", 20, false);
setFontStylePlus(workbook, contentFirstStyle, "等线", 14, true);
setFontStylePlus(workbook, contentStyle, "等线", 11, false);
// 设置单元格居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
contentFirstStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setAlignment(HorizontalAlignment.CENTER);
// 遍历输出每行
for (int i = 0; i < rowsData.size(); i++) {
// 创建一个row
XSSFRow row = sheet.createRow(i);
// 每一行的数据
List<Object> rowData = rowsData.get(i);
// 遍历生成每个单元格
for (int j = 0; j < rowData.size(); j++) {
XSSFCell cell = row.createCell(j);
// 设置单元格样式
if (i == 0) {
cell.setCellStyle(titleStyle);//标题样式
} else if (i == 1) {
cell.setCellStyle(contentFirstStyle);//内容第一行样式
} else {
cell.setCellStyle(contentStyle);// 内容样式
}
//单元格赋值
Object cellData = rowData.get(j);
setDataOnCell(cell, cellData);
}
}
fileName = fileName + ".xls";
ExcelUtils.setResponseAttribute(response, request, fileName);
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
//关闭流
workbook.close();//在servlet 完成生命周期之后servlet 容器会自动关闭流
outputStream.close();
} catch (IOException e) {
log.info("导出excel失败");
}
}
/**
* @author: JiaXinMa
* @description: 设置列的宽度
* @date: 2021/6/17
* 设置MaterielStorage汇总的Excel文件
*/
private static void setMaterielStorageTotalColumnWidthOnSheet(Sheet sheet) {
sheet.setColumnWidth(0, 2500); // 第1列的宽度为2000
for (int i = 1; i < 11; i++) {// 第2-11列的宽度为4500
sheet.setColumnWidth(i, 4500);
}
}
/**
* @author: JiaXinMa
* @description: 设置列的宽度
* @date: 2021/6/8
* Integer[] rowColumnWidth 每一列的宽度,有几列就有几个长度
*/
private static void setColumnWidthOnSheetPlus(Sheet sheet, Integer[] rowColumnWidth) {
for (int i = 0; i < rowColumnWidth.length; i++) {
sheet.setColumnWidth(i, rowColumnWidth[i]);
}
}
/**
* @author: JiaXinMa
* @description: 设置字体
* @date: 2021/6/8
*/
private static void setFontStylePlus(Workbook workbook, CellStyle cellStyle, String fontName, int fontSize, Boolean isBold) {
Font font = workbook.createFont();
font.setColor((short) 42); // 设置字体颜色
font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getColor().getIndex()); // XSSFColor中未找到颜色和short数值的映射,使用HSSFColor来定位颜色的short值
font.setFontName(fontName); // 设置字体
font.setFontHeightInPoints((short) fontSize);// 设置字体大小
font.setBold(isBold);//设置粗体显示
// font.setUnderline(HSSFFont.SYMBOL_CHARSET);//设置文字下划线
cellStyle.setFont(font);
}
/**
* @author: JiaXinMa
* @description: 设置字体
* @date: 2021/6/8
*/
private static void setFontStyle(Workbook workbook, CellStyle cellStyle) {
Font font = workbook.createFont();
font.setColor((short) 42); // 设置字体颜色
font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getColor().getIndex()); // XSSFColor中未找到颜色和short数值的映射,使用HSSFColor来定位颜色的short值
font.setFontName("宋体"); // 设置字体
font.setFontHeightInPoints((short) 12);// 设置字体大小
// font.setBold(true);//设置粗体显示
// font.setUnderline(HSSFFont.SYMBOL_CHARSET);//设置文字下划线
cellStyle.setFont(font);
}
/**
* @author: JiaXinMa
* @description: 给每一行赋值和样式
* @date: 2021/6/7
*/
private static void setDataAndStyleOnRow(CellStyle titleStyle, Row row, List<Object> rowData) {
for (int j = 0; j < rowData.size(); j++) {
//给单元格设置样式
Cell cell = row.createCell(j);
cell.setCellStyle(titleStyle);
//给单元格赋值
Object cellData = rowData.get(j);
setDataOnCell(cell, cellData);
}
}
/**
* @author: JiaXinMa
* @description: 给单元格设置边框
* @date: 2021/6/7
*/
private static void setBorderOnCell(CellStyle cellStyle) {
cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
}
/**
* @author: JiaXinMa
* @description: 给单元格赋值
* @date: 2021/6/7
*/
private static void setDataOnCell(Cell cell, Object cellData) {
// 如果为空,就不做设值处理
if (cellData == null) {
return;
}
// String类型数值
if (cellData.getClass() == String.class) {
cell.setCellValue((String) cellData);
}
// double类型数值
else if (cellData.getClass() == double.class || cellData.getClass() == Double.class) {
cell.setCellValue((Double) cellData);
}
// float类型数值
else if (cellData.getClass() == float.class || cellData.getClass() == Float.class) {
cell.setCellValue((Float) cellData);
}
// integer类型数值
else if (cellData.getClass() == int.class || cellData.getClass() == Integer.class) {
cell.setCellValue((Integer) cellData);
}
}
/**
* @author: JiaXinMa
* @description: 设置响应头的属性
* @date: 2021/6/8
*/
public static void setResponseAttribute(HttpServletResponse response, HttpServletRequest request, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
String agent = request.getHeader("USER-AGENT").toLowerCase();
String uncodeFileNameString = "";
if (agent.contains("firefox")) {//判断是否火狐
uncodeFileNameString = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?=";
} else {
uncodeFileNameString = URLEncoder.encode(fileName, "UTF-8");
uncodeFileNameString = uncodeFileNameString.replace("+", "%20"); // 空格被转为了 "+" ，要转成utf-8的空格符号 "%20"
}
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + uncodeFileNameString);
}
/**
* 是否是2003的excel，返回true是2003
*/
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 是否是2007的excel，返回true是2007
*/
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 验证EXCEL文件
*/
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
return false;
}
return true;
}
/**
* 读取 Excel 第一页数据
*/
public List<List<String>> read() throws Exception {
return read(0, 0, getRowCount(0) - 1);
}
/**
* 读取 Excel 数据
* 指定 sheet 页，从 0 开始
*/
public List<List<String>> read(int sheetIx) throws Exception {
return read(sheetIx, 0, getRowCount(sheetIx) - 1);
}
/**
* 读取 Excel 数据
* 指定 sheet 页，从 0 开始
* 指定开始行，从 0 开始
* 指定结束行，从 0 开始
*/
public List<List<String>> read(int sheetIx, int start, int end) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetIx);
List<List<String>> list = new ArrayList<List<String>>();
if (end > getRowCount(sheetIx)) {
end = getRowCount(sheetIx);
}
int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数
for (int i = start; i <= end; i++) {
List<String> rowList = new ArrayList<String>();
Row row = sheet.getRow(i);
for (int j = 0; j < cols; j++) {
if (row == null) {
rowList.add(null);
continue;
}
rowList.add(getCellValueToString(row.getCell(j)));
}
list.add(rowList);
}
return list;
}
/**
* 返回sheet中的行数
*/
public int getRowCount(int sheetIx) {
Sheet sheet = workbook.getSheetAt(sheetIx);
if (sheet.getPhysicalNumberOfRows() == 0) {
return 0;
}
return sheet.getLastRowNum() + 1;
}
/**
* 将转换单元格的类型为String
*/
private String getCellValueToString(Cell cell) {
String strCell = "";
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (pattern != null) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
strCell = sdf.format(date);
} else {
strCell = date.toString();
}
break;
}
// 不是日期格式，则防止当数字过长时以科学计数法显示
cell.setCellType(CellType.STRING);
strCell = cell.toString();
break;
case STRING:
strCell = cell.getStringCellValue();
break;
default:
break;
}
return strCell;
}
}
3.具体业务层和控制层我就不列代码了，直接调用工具类的方法就行
效果如下：
控制层最好就是无返回值
如果想读取excel文件可以调用工具类的 **read()**方法，大家可以结合自己的业务多尝试写一些工具类，一起学习交流
想看更多精彩内容，可以关注我的博客园
我的博客园
6292
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
