excel添加颜色和注释
package com.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class ExcelTest {
public static void main(String[] args){
File file = new File("D:\\test.xlsx");
InputStream inputStream = null;
FileOutputStream out = null;
try {
inputStream = new FileInputStream(file);
Workbook workBook = new XSSFWorkbook(inputStream);
int totalRows = 0;
int totalCells = 0;
Sheet sheet = workBook.getSheetAt(0);
// 得到Excel的行数
totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
// 读取Excel文档
File writefile = new File("D:\\结果.xlsx");
Workbook workbook = new XSSFWorkbook();
CellStyle redStyle = workbook.createCellStyle();
redStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
redStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
CellStyle blueStyle = workbook.createCellStyle();
blueStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
blueStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
Sheet writeSheet = workbook.createSheet("校验结果");
for (int i = 0; i < totalRows; i++) {
Row row = sheet.getRow(i);
Row writeRow = writeSheet.createRow(i);
if (row == null) {
continue;
}
//遍历列
for (int j = 0; j < totalCells; j++) {
Cell cell = row.getCell(j);
Cell writeCell = writeRow.createCell(j);
//问题列
String cellValue = getCellValue(cell);
if(Func.isNotEmpty(cellValue)) {
writeCell.setCellValue(cellValue);
writeCell.setCellStyle(redStyle);
setComment(writeCell, "与数据库重复", writeSheet);
}
}
}
out = new FileOutputStream("D:\\结果.xlsx");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}finally{
if(inputStream != null){
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(out != null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 获取值
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
String cellValue = "";
if (Func.isNotEmpty(cell)) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
cellValue = cell.getNumericCellValue() + "";
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "";
break;
default:
cellValue = "";
break;
}
}
return cellValue;
}
/**
* 设置注释
* @param cell
* @param text
* @param sheet
*/
public static void setComment(Cell cell, String text, Sheet sheet){
ClientAnchor anchor = new XSSFClientAnchor();
// 关键修改
anchor.setDx1(0);
anchor.setDx2(0);
anchor.setDy1(0);
anchor.setDy2(0);
anchor.setCol1(cell.getColumnIndex());
anchor.setRow1(cell.getRowIndex());
anchor.setCol2(cell.getColumnIndex() + 5);
anchor.setRow2(cell.getRowIndex() + 6);
// 结束
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
comment.setString(new XSSFRichTextString(text));
cell.setCellComment(comment);
}
}
示例图片
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/92442.html