POI操作Excel的工具类

最新推荐文章于 2023-02-15 21:22:53 发布

xiaoyi8837

最新推荐文章于 2023-02-15 21:22:53 发布

阅读量196

点赞数

分类专栏：通用技术

本文链接：https://blog.csdn.net/xiaoyi8837/article/details/53214080

版权

通用技术专栏收录该内容

1 篇文章 0 订阅

订阅专栏

该类用于操作excel：写单元格，设置属性


/**
 * Created by XY on 2016/4/26.
 */
public class ExcelUtils {
    public static HSSFSheet ExcelSheet;
    public static HSSFWorkbook ExcelBook;
    public static HSSFRow Row;
    public static HSSFCell Cell;

    public static HSSFCellStyle greenCellStyle,redCellStyle;

    public static void setExcelFile(String Path,String SheetName) throws Exception{
        FileInputStream ExcelFile = new FileInputStream(Path);
        ExcelBook = new HSSFWorkbook(ExcelFile);
        ExcelSheet = ExcelBook.getSheet(SheetName); 
    }



    public static void setCellData(String Result, int RowNum, int ColNum) throws Exception{
        Row  = ExcelSheet.getRow(RowNum);
        Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
        if (Cell == null) {
            Cell = Row.createCell(ColNum);
            Cell.setCellValue(Result);
        } else {
            Cell.setCellValue(Result);
        }
    }
    public static String getCellDate(int RowNum,int CloNum){
        Cell=ExcelSheet.getRow(RowNum).getCell(CloNum);
        Cell.setCellType(Cell.CELL_TYPE_STRING);//设置cell单元格类型为string

        String cellData=Cell.getStringCellValue();
        return cellData;
    }
    public int getLastRowNums(){
        return ExcelSheet.getLastRowNum();
    }


    public static void setConditionalFormat(){
        SheetConditionalFormatting scf = ExcelSheet.getSheetConditionalFormatting();  
        //设置单元格条件格式
        ConditionalFormattingRule passRule = scf.createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"Failed\"", null);  
        PatternFormatting passFormatting = passRule.createPatternFormatting();  
        passFormatting.setFillBackgroundColor(HSSFColor.RED.index);  

        ConditionalFormattingRule failedRule = scf.createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"Passed\"", null);  
        PatternFormatting failedFormatting = failedRule.createPatternFormatting();  
        failedFormatting.setFillBackgroundColor(HSSFColor.GREEN.index);

        ConditionalFormattingRule nullRule = scf.createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"Null\"", null);  
        PatternFormatting nullFormatting = nullRule.createPatternFormatting();  
        nullFormatting.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);


        ConditionalFormattingRule[] cfRules = {passRule, failedRule,nullRule};

        CellRangeAddress[] regions = {CellRangeAddress.valueOf("E1:H100")};

        scf.addConditionalFormatting(regions, cfRules);
    }

    public static void writeFile(String Path) throws Exception{
        FileOutputStream fileOut = new FileOutputStream(Path);
        ExcelBook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

}