目录
1、依赖
<!--excel poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.4</version>
</dependency>
<!--excel-->
2、controller层代码
// http://127.0.0.1:8080/springboot/getExcelWaterModel
@RequestMapping(value = "getExcelWaterModel", method = RequestMethod.GET)
public void getExcelWaterMode(HttpServletRequest request, HttpServletResponse response) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建sheet
Sheet sheet = workbook.createSheet("sheet1");
//表头字体
Font headerFont = workbook.createFont();
headerFont.setFontName("微软雅黑");
headerFont.setFontHeightInPoints((short) 18);
headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
headerFont.setColor(HSSFColor.BLACK.index);
//正文字体
Font contextFont = workbook.createFont();
contextFont.setFontName("微软雅黑");
contextFont.setFontHeightInPoints((short) 12);
contextFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
contextFont.setColor(HSSFColor.BLACK.index);
//表头样式，左右上下居中
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
headerStyle.setLocked(true);
headerStyle.setWrapText(false);// 自动换行
//单元格样式，左右上下居中 边框
CellStyle commonStyle = workbook.createCellStyle();
commonStyle.setFont(contextFont);
commonStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonStyle.setLocked(true);
commonStyle.setWrapText(false);// 自动换行
commonStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
commonStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
commonStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
commonStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式，左右上下居中 边框
CellStyle commonWrapStyle = workbook.createCellStyle();
commonWrapStyle.setFont(contextFont);
commonWrapStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonWrapStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonWrapStyle.setLocked(true);
commonWrapStyle.setWrapText(true);// 自动换行
commonWrapStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
commonWrapStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
commonWrapStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
commonWrapStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式，竖向 边框
CellStyle verticalStyle = workbook.createCellStyle();
verticalStyle.setFont(contextFont);
verticalStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
verticalStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
verticalStyle.setRotation((short) 255);//竖向
verticalStyle.setLocked(true);
verticalStyle.setWrapText(false);// 自动换行
verticalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
verticalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
verticalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
verticalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式，左右上下居中 无边框
CellStyle commonStyleNoBorder = workbook.createCellStyle();
commonStyleNoBorder.setFont(contextFont);
commonStyleNoBorder.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonStyleNoBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonStyleNoBorder.setLocked(true);
commonStyleNoBorder.setWrapText(false);// 自动换行
//单元格样式，左对齐 边框
CellStyle alignLeftStyle = workbook.createCellStyle();
alignLeftStyle.setFont(contextFont);
alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignLeftStyle.setLocked(true);
alignLeftStyle.setWrapText(false);// 自动换行
alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
alignLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
alignLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
alignLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式，左对齐 无边框
CellStyle alignLeftNoBorderStyle = workbook.createCellStyle();
alignLeftNoBorderStyle.setFont(contextFont);
alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftNoBorderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignLeftNoBorderStyle.setLocked(true);
alignLeftNoBorderStyle.setWrapText(false);// 自动换行
alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
//单元格样式，右对齐
CellStyle alignRightStyle = workbook.createCellStyle();
alignRightStyle.setFont(contextFont);
alignRightStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignRightStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignRightStyle.setLocked(true);
alignRightStyle.setWrapText(false);// 自动换行
alignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 左对齐
//下面开始处理数据
String fileName = null;
// 行号
int rowNum = 0;
//设置列宽
for (int i = 0; i < 7; i++) {
sheet.setColumnWidth(i, 6000);
}
//todo 1、第一行
Row r0 = sheet.createRow(rowNum++);
r0.setHeight((short) 800);//行高
//第一行第一列
Cell c00 = r0.createCell(0);
c00.setCellValue("山东省总工会工作人员考勤登记表");
c00.setCellStyle(headerStyle);
//合并单元格（从左到右依次：要合并的初始行位置、结束行位置，初始列位置、结束列位置）
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
//todo 考勤时间行
Row rn = sheet.createRow(rowNum++);
rn.setHeight((short) 800);
Cell c01 = rn.createCell(0);
c01.setCellValue("（考勤月份" + "2019" + "年" + "04" + "月）");
c01.setCellStyle(headerStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
//todo 第二行
Row r1 = sheet.createRow(rowNum++);
r1.setHeight((short) 500);
String date = new SimpleDateFormat("yyyy-MM").format(new Date());
String[] row_first = {"部室（盖章）：" + "部门名称1", "", "", "", "", "填表时间：", ""};
for (int i = 0; i < row_first.length; i++) {
Cell tempCell = r1.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
if (i == 0) {
tempCell.setCellValue(row_first[i]);
} else if (i == 5) {
tempCell.setCellStyle(alignRightStyle);
tempCell.setCellValue(row_first[i] + date.replace("-", "年") + "月");
} else {
tempCell.setCellValue(row_first[i]);
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
//todo 第三行
Row r3 = sheet.createRow(rowNum++);
r3.setHeight((short) 700);
String[] row_third = {"姓名", "病假天数", "事假天数", "带薪休假天数", "其他休假天数（需注明何种假）", "旷工天数", "是否销假"};
for (int i = 0; i < row_third.length; i++) {
Cell tempCell = r3.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(commonWrapStyle);
}
// todo 数据开始 从map中取出 key为list的集合
// List<OutRecord> list=null;
// Map<String, Object> map = this.checkingtotal(info);//调用本类中的方法获得map
// Iterator<String> iterator = map.keySet().iterator();
// while(iterator.hasNext()){
// String key=iterator.next();
// if(("list").equals(key)) {
// list = (List<OutRecord>) map.get(key);
// }
// }
// //循环每一行，插入数据
// for (OutRecord outrecord : list) {
// Row tempRow = sheet.createRow(rowNum++);
// tempRow.setHeight((short) 500);
// // 循环单元格填入数据
// for (int j = 0; j < 7; j++) {
// Cell tempCell = tempRow.createCell(j);
// tempCell.setCellStyle(commonStyle);
// String tempValue = null;
// if (j == 0) {
// // 姓名
// tempValue = outrecord.getRegistrantName();
// } else if (j == 1) {
// // 病假
// tempValue = outrecord.getBingjia()+"";
// } else if (j == 2) {
// // 事假
// tempValue = outrecord.getShijia()+"";
// } else if (j == 3) {
// // 带薪年假
// tempValue = outrecord.getNianjia()+"";
// } else if (j == 4) {
// // 其他休假
// if(outrecord.getHunjia()!=null) {
// tempValue = "婚假："+outrecord.getHunjia();
// }
// if(outrecord.getChanjia()!=null) {
// tempValue = "产假："+outrecord.getChanjia();
// }
// } else if (j == 5) {
// // 旷工天数
// } else {
// // 是否销假
// }
// if(("null").equals(tempValue)) {
// tempValue="";
// }
// tempCell.setCellValue(tempValue);
// }
// }
//todo 数据结束
// 注释行
Row remark = sheet.createRow(rowNum++);
remark.setHeight((short) 500);
String[] row_remark = {"注：每月考勤情况应于次月5日前按时报组织部", "", "", "", "", "", ""};
for (int i = 0; i < row_remark.length; i++) {
Cell tempCell = remark.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
tempCell.setCellValue(row_remark[i]);
}
// int listSize = list.size();
int listSize = 3;
// int remarkRowNum = list.size() + 4;
int remarkRowNum = listSize + 4;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum, remarkRowNum, 0, 6));
// 尾行
Row foot = sheet.createRow(rowNum++);
foot.setHeight((short) 500);
String[] row_foot = {"考勤人员：", "", "", "", "", "部室主要负责人：", ""};
for (int i = 0; i < row_foot.length; i++) {
Cell tempCell = foot.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
if (i == 0) {
tempCell.setCellValue(row_foot[i]);
} else if (i == 5) {
tempCell.setCellValue(row_foot[i]);
} else {
tempCell.setCellValue(row_foot[i]);
}
}
int footRowNum = listSize + 5;
// 合并
sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 5, 6));
fileName = " 工作人员考勤登记表.xls";
//输出Excel文件
OutputStream outputStream = response.getOutputStream();
response.reset();
// 处理文件名包含特殊字符出现的乱码问题
String userAgent = request.getHeader("User-Agent");
if (userAgent != null && userAgent.length() != 0) {
userAgent = userAgent.toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident") || userAgent.contains("edge")) {
if (fileName.length() > 150) {// 解决IE 6.0问题
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
} else {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
}
response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
response.setContentType("application/vnd.ms-excel");
//这里解决了导出文件名字的乱码问题
// response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
response.flushBuffer();
//直接返回的
// workbook.write(outputStream);
// 处理成带水印的操作
try {
ExcelWaterRemarkUtils.toExcelWithWater(workbook, response);
} catch (Exception e) {
e.printStackTrace();
}finally {
outputStream.close();
}
}
3、工具类
package com.yg.springboot.util.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.Color;
import java.awt.Font;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.*;
/**
* @author yuguang
* @date 2020/5/21 10:04
* @desc excel加水印处理，只能处理XSSFWorkbook操作的
*/
public class ExcelWaterRemarkUtils {
/***
* 需要有基础表，然后将表处理，再然后生成一个带水印的表
*/
public static void main(String[] args) throws IOException {
//读取excel文件
Workbook wb = new XSSFWorkbook(new FileInputStream("D:/客商信息.xlsx"));
//设置水印图片路径
String imgPath = "E:\\水印.png";
ExcelWaterRemarkUtils.createWaterMark("文字水印效果123", imgPath);
//获取excel sheet个数
int sheets = wb.getNumberOfSheets();
//循环sheet给每个sheet添加水印
for (int i = 0; i < sheets; i++) {
Sheet sheet = wb.getSheetAt(i);
//excel加密只读
//sheet.protectSheet(UUID.randomUUID().toString());
//获取excel实际所占行
int row = sheet.getFirstRowNum() + sheet.getLastRowNum();
//获取excel实际所占列
int ll = sheet.getFirstRowNum();
Row rrr = sheet.getRow(ll);
int cell = rrr.getLastCellNum() + 1;
//根据行与列计算实际所需多少水印
ExcelWaterRemarkUtils.putWaterRemarkToExcel(wb, sheet, imgPath, 0, 0, 5, 5, cell / 5 + 1, row / 5 + 1, 0, 0);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
wb.close();
byte[] content = os.toByteArray();
// Excel文件生成后存储的位置。
File file1 = new File("E:\\watermark.xlsx");
OutputStream fos = null;
try {
fos = new FileOutputStream(file1);
fos.write(content);
os.close();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void toExcelWithWater(Workbook hssfWorkbook, HttpServletResponse response) throws Exception {
//将文件流写入输出流
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
hssfWorkbook.write(baos);
} catch (IOException e) {
e.printStackTrace();
}
hssfWorkbook.close(); //关闭excel的流
//todo 输出流转输入流
ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray());
Workbook handleWorkbook = new XSSFWorkbook(swapStream);
try {
handle(handleWorkbook);
//写入到返回的输出流中
handleWorkbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
} finally {
handleWorkbook.close();
response.getOutputStream().close();
}
}
public static void handle(Workbook workbook) throws Exception {
//设置水印图片路径
String property = System.getProperty("user.dir");
String imgPath = property + "/水印.png";
ExcelWaterRemarkUtils.createWaterMark("文字水印效果123", imgPath);
//获取excel sheet个数
int sheets = workbook.getNumberOfSheets();
//循环sheet给每个sheet添加水印
for (int i = 0; i < sheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
//excel加密只读
//sheet.protectSheet(UUID.randomUUID().toString());
//获取excel实际所占行
int row = sheet.getFirstRowNum() + sheet.getLastRowNum();
//获取excel实际所占列
int ll = sheet.getFirstRowNum();
Row rrr = sheet.getRow(ll);
int cell = rrr.getLastCellNum() + 1;
//根据行与列计算实际所需多少水印
ExcelWaterRemarkUtils.putWaterRemarkToExcel(workbook, sheet, imgPath, 0, 0, 5, 5, cell / 5 + 1, row / 5 + 1, 0, 0);
}
}
public static void createWaterMark(String content, String path) throws IOException {
Integer width = 300;
Integer height = 200;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
String fontType = "宋体";
Integer fontStyle = Font.PLAIN;
Integer fontSize = 30;
Font font = new Font(fontType, fontStyle, fontSize);
Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
g2d.setColor(new Color(0, 0, 0, 80)); //设置字体颜色和透明度
g2d.setStroke(new BasicStroke(1)); // 设置字体
g2d.setFont(font); // 设置字体类型 加粗 大小
g2d.rotate(Math.toRadians(-10), (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(content, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
g2d.drawString(content, (int) x, (int) baseY);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
ImageIO.write(image, "png", new File(path));
}
/**
* 为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
*
* @param wb Excel Workbook
* @param sheet 需要打水印的Excel
* @param waterRemarkPath 水印地址，classPath，目前只支持png格式的图片，
* 因为非png格式的图片打到Excel上后可能会有图片变红的问题，且不容易做出透明效果。
* 同时请注意传入的地址格式，应该为类似："\\excelTemplate\\test.png"
* @param startXCol 水印起始列
* @param startYRow 水印起始行
* @param betweenXCol 水印横向之间间隔多少列
* @param betweenYRow 水印纵向之间间隔多少行
* @param XCount 横向共有水印多少个
* @param YCount 纵向共有水印多少个
* @param waterRemarkWidth 水印图片宽度为多少列
* @param waterRemarkHeight 水印图片高度为多少行
* @throws IOException
*/
public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String waterRemarkPath, int startXCol,
int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterRemarkWidth,
int waterRemarkHeight) throws IOException {
// 校验传入的水印图片格式
if (!waterRemarkPath.endsWith("png") && !waterRemarkPath.endsWith("PNG")) {
throw new RuntimeException("向Excel上面打印水印，目前支持png格式的图片。");
}
// 加载图片
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
InputStream imageIn = new FileInputStream(waterRemarkPath);
//InputStream imageIn = Thread.currentThread().getContextClassLoader().getResourceAsStream(waterRemarkPath);
if (null == imageIn || imageIn.available() < 1) {
throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(1)。");
}
BufferedImage bufferImg = ImageIO.read(imageIn);
if (null == bufferImg) {
throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(2)。");
}
ImageIO.write(bufferImg, "png", byteArrayOut);
// 开始打水印
Drawing drawing = sheet.createDrawingPatriarch();
// 按照共需打印多少行水印进行循环
for (int yCount = 0; yCount < YCount; yCount++) {
// 按照每行需要打印多少个水印进行循环
for (int xCount = 0; xCount < XCount; xCount++) {
// 创建水印图片位置
int xIndexInteger = startXCol + (xCount * waterRemarkWidth) + (xCount * betweenXCol);
int yIndexInteger = startYRow + (yCount * waterRemarkHeight) + (yCount * betweenYRow);
/*
* 参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）；
* 第四个参数是（是y轴的结束节点）； 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
* 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）； 第七个参数是（图片宽度，共多少列）；
* 第8个参数是（图片高度，共多少行）；
*/
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger,
yIndexInteger, xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
Picture pic = drawing.createPicture(anchor,
wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
pic.resize();
}
}
}
}
2208
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
