先引入依赖
<!-- poi引用 -->
<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>
工具类:
private static ExcelUtils excelUtils;
public ExcelUtils() {
}
public static ExcelUtils ExcelUtils() {
if (excelUtils== null) {
excelUtils= new ExcelUtils();
}
return excelUtils;
}
/**
* 动态赋值单元格样式
*/
Map<String, HSSFCellStyle> Style = new HashMap<>();
/**
* 动态赋值字体样式
*/
Map<String, Font> fontMap = new HashMap<>();
/**
* 导出
*
* @param list 导出的数据
* @param headers 导出的表头
* @param name 文件名称
* @throws IOException
*/
public void export(List<Map> list, String[] headers, String name) throws IOException {
Excel transToExcel = new Excel();
try {
//这个东西表示获取根目录
String path = System.getProperty("user.dir");
OutputStream os = new FileOutputStream(path + "/" + name + ".xls");
transToExcel.exporteExcel(name, headers, list, os);
os.close();
} catch (FileNotFoundException e) {
System.out.println("无法找到文件");
} catch (IOException e) {
System.out.println("写入文件失败");
}
}
/**
* 导出excel文件
*
* @param title 表sheet的名字
* @param headers 表头
* @param dataList 正文单元格
* @param out 输出流
*/
public void exporteExcel(String title, String[] headers, List<Map> dataList, OutputStream out) {
HSSFWorkbook workBook = new HSSFWorkbook();
createSheet(title, headers, dataList, workBook);
//这里可多次调用生成工作簿
// createSheet(title + "2", headers, dataList, workBook);
try {
workBook.write(out);
} catch (IOException e) {
System.out.println("写入文件失败" + e.getMessage());
}
}
/**
* 创建sheet
*
* @param title sheet的名字
* @param headers 表头
* @param dataList 正文单元格
*/
private void createSheet(String title, String[] headers, List<Map> dataList, HSSFWorkbook workBook) {
HSSFSheet sheet = workBook.createSheet(title);
sheet.setDefaultColumnWidth(15);
//设置表头和普通单元格的格式
HSSFCellStyle headStyle = setHeaderStyle(workBook);
HSSFCellStyle bodyStyle = setBodyStyle(workBook);
createHeader(headers, sheet, headStyle);
createBody(dataList, sheet, bodyStyle, headers);
}
/**
* 创建正文单元格
*
* @param dataList 数据数组
* @param sheet 表
* @param bodyStyle 单元格格式
* @param headers 表头
*/
private void createBody(List<Map> dataList, HSSFSheet sheet, HSSFCellStyle bodyStyle, String[] headers) {
//可以设置动态赋值颜色 setBodyStyleMov();
//当前行
int sheetRow = 1;
for (int a = 0; a < dataList.size(); a++) {
Map map = dataList.get(a);
HSSFRow row = sheet.createRow(sheetRow);
for (int j = 0; j < headers.length; j++) {
HSSFCell cell = row.createCell(j);
HSSFRichTextString textString = null;
String val=null;
if (map.get(headers[j]) != null) {
val = map.get(headers[j]).toString();
textString= new HSSFRichTextString(val);
// 这里可以给单个字体赋值颜色 textString.applyFont(fontMap.get("1"));
// 这里可以给单个单元格赋值不同颜色 cell.setCellStyle(style.get("1"));
//给单元格赋值颜色和值
cell.setCellStyle(bodyStyle);
cell.setCellValue(textString);
} else {
cell.setCellValue(textString);
}
}
sheetRow++;
}
}
/**
* 传入字符返回字母
* @param a
* @return
*/
public static String getLetter(String a) {
StringBuffer sb = new StringBuffer();
for(int i = 0;i<a.length();i++){
char c = a.charAt(i);
if((c<='z'&&c>='a')||(c<='Z'&&c>='A')){
if (sb.length()>0){
sb.append(",");
}
sb.append(c);
}
}
System.out.println(sb.toString());
return sb.toString();
}
/**
* 创建表头
*
* @param headers 表头
* @param sheet 表
* @param headStyle 表头格式
*/
private void createHeader(String[] headers, HSSFSheet sheet, HSSFCellStyle headStyle) {
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(headStyle);
HSSFRichTextString textString = new HSSFRichTextString(headers[i]);
cell.setCellValue(textString);
//只能解决英文、数字列宽自适应，如果该列为中文，会出现列宽不足现象。
sheet.autoSizeColumn((short) i);
// 解决自动设置列宽中文失效的问题
// sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
}
/**
* 设置正文单元格格式动态
*
* @param workBook
* @return
*/
private void setBodyStyleMov(HSSFWorkbook workBook) {
//拿到palette颜色板
HSSFPalette palette = workBook.getCustomPalette();
//自定义颜色 黄 这里是吧 IndexedColors.YELLOW.getIndex() 替换成 rgb(255,255,0)
palette.setColorAtIndex(IndexedColors.YELLOW.getIndex(), (byte) 255, (byte) 255, (byte) 0);
//黄色 吧颜色赋值好存到map之后可以调用
HSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(palette.getColor(IndexedColors.YELLOW.getIndex()).getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Style.put("1", style);
//这里同理替换颜色
palette.setColorAtIndex(IndexedColors.GOLD.getIndex(), (byte) 90, (byte) 140, (byte) 55);
Font font = workBook.createFont();
font.setBold(true);
font.setColor(palette.getColor(IndexedColors.GOLD.getIndex()).getIndex());
fontMap.put("1", font);
}
/**
* 设置正文单元格格式
*
* @param workBook
* @return
*/
private HSSFCellStyle setBodyStyle(HSSFWorkbook workBook) {
HSSFCellStyle style = workBook.createCellStyle();
//设置背景颜色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//边框颜色和宽度设置
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
//对齐方式设置
style.setAlignment(HorizontalAlignment.CENTER);
// 粗体字设置
Font font = workBook.createFont();
font.setFontName("微软雅黑");
style.setFont(font);
return style;
}
/**
* 设置表头格式
*
* @param workBook
* @return
*/
private HSSFCellStyle setHeaderStyle(HSSFWorkbook workBook) {
HSSFCellStyle style = workBook.createCellStyle();
//设置背景颜色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 边框颜色和宽度设置
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
// 对齐方式设置
style.setAlignment(HorizontalAlignment.CENTER);
// 粗体字设置
Font font = workBook.createFont();
font.setBold(true);
font.setFontName("微软雅黑");
style.setFont(font);
return style;
}