javascript">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) ;
}
}
示例图片