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);
totalRows = sheet.getPhysicalNumberOfRows();
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
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();
}
}
}
}
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:
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;
}
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);
}
}
示例图片