关闭

POI 设置和获取excel单元格格式

标签: POI读写单元格设置单元格背景颜色获取背景颜色设置excel字体
946人阅读 评论(0) 收藏 举报
分类:


public static void readExpectSheetAndWriteCaseSheet(matchAndNotMatchExcelObject matchExcelResult, String filePath) {
  try {
   ArrayList<ExceptionObject> readExpcetSheet = new ArrayList<ExceptionObject>();
   
   ArrayList<ReplyObject> matchExcelList = matchExcelResult.getMatchExcelList();
   
   String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
   
   InputStream stream = new FileInputStream(filePath);
//   Workbook xssfWorkbook = null;
   XSSFWorkbook xssfWorkbook = null;
   if (fileType.equals("xls")) {
//    xssfWorkbook = new HSSFWorkbook(stream);
       } else if (fileType.equals("xlsx")) {
        xssfWorkbook = new XSSFWorkbook(stream);
       }
   XSSFSheet xssfSheet = xssfWorkbook.getSheet("Sheet2");
//   Sheet xssfSheet = xssfWorkbook.getSheet("Sheet2");
   System.out.println("sheet2 lastRowNum = "+xssfSheet.getLastRowNum());
   for (int rowNum = 2; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
    if (rowNum == xssfSheet.getLastRowNum()) {
     System.out.println("ssssss");
    }
    System.out.println("read sheet2 &&&&&&&&&&&&  "+rowNum);
    ExceptionObject exceptionObject = new ExceptionObject();
    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
          exceptionObject.setInput(xssfRow.getCell(5).getStringCellValue());
          XSSFCellStyle cellStyle = xssfRow.getCell(7).getCellStyle();
          if (cellStyle.getFillForegroundXSSFColor()!=null) {
           exceptionObject.setBkColor(cellStyle);
           System.out.println(cellStyle.getFillForegroundXSSFColor().getARGBHex());
    }else {
     exceptionObject.setBkColor(null);
    }
          System.out.println("get == "+exceptionObject.getBkColor());
           exceptionObject.setSeg(xssfRow.getCell(8).getStringCellValue());
           exceptionObject.setGrammar(xssfRow.getCell(9).getStringCellValue());
//           System.out.println("Sheet2 setGrammar = "+xssfRow.getCell(9).getStringCellValue());
           exceptionObject.setCurrentOnto(xssfRow.getCell(10).getStringCellValue());
           exceptionObject.setUpdatedRequest(xssfRow.getCell(11).getStringCellValue());
           exceptionObject.setReplyType(xssfRow.getCell(12).getStringCellValue());
           exceptionObject.setCurrentReply(xssfRow.getCell(13).getStringCellValue());
                
           System.out.println("sheet2 ^^^^^^^^^^^^^ = "+rowNum);
           readExpcetSheet.add(exceptionObject);
   }
   
   System.out.println("readExpcetSheet  line size ###### == "+readExpcetSheet.size());
   
   //write
    XSSFSheet xssfSheetW = xssfWorkbook.getSheet("Sheet1");
    
    /*xssfSheetW.getLastRowNum();
    XSSFRow row2 = xssfSheetW.getRow(0);
    row2.getLastCellNum();
    
    int lastRowNum = xssfSheetW.getLastRowNum();
    int unnecessaryCount =  lastRowNum - matchExcelList.size() - 2;
    int forSize = lastRowNum - unnecessaryCount;*/
    
    FileOutputStream out = new FileOutputStream(filePath);

     XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
     Font font =  xssfWorkbook.createFont();
     font.setColor(HSSFColor.GREEN.index);
    cellStyle.setFont(font);
//    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
    cellStyle.setWrapText(true);//设置自动换行
    
    XSSFCellStyle cellStyleRed = xssfWorkbook.createCellStyle();
     Font fontRed =  xssfWorkbook.createFont();
     fontRed.setColor(HSSFColor.RED.index);
     cellStyleRed.setFont(fontRed);
//     cellStyleRed.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
     cellStyleRed.setWrapText(true);//设置自动换行

     //20 last19(0-19)  第3行(rowNum = 2)  < 18+2(readExpcetSheet.size())  19
    for (int rowNum = 2; rowNum < readExpcetSheet.size()+2; rowNum++) {
     if (rowNum == readExpcetSheet.size()) {
      System.out.println("ssssssssssssss");
     }
     boolean grammarEquals = false;
     boolean updatedRequestEquals = false;
     ReplyObject replyObject = matchExcelList.get(rowNum-2);
     ExceptionObject exceptionObject = readExpcetSheet.get(rowNum-2);
     XSSFRow row = xssfSheetW.getRow(rowNum);
     
      if (replyObject.getInput().equals(exceptionObject.getInput())) {
       if (replyObject.getSeg().equals(exceptionObject.getSeg())) {
        row.getCell(8).setCellStyle(cellStyle);
       }else {
        row.getCell(8).setCellStyle(cellStyleRed);
       }
       
       ArrayList<String> grammar = replyObject.getGrammar();
              String grammarS = "";
              if (grammar != null) {
               for (int i = 0; i < grammar.size(); i++) {
                grammarS += grammar.get(i)+"\n";
               }
       }
              if (grammarS.equals(exceptionObject.getGrammar())) {
               grammarEquals = true;
               row.getCell(9).setCellStyle(cellStyle);
       }else {
        row.getCell(9).setCellStyle(cellStyleRed);
       }
       
              if (replyObject.getCurrentOnto().equals("")) {
        System.out.println(replyObject.getCurrentOnto());
       }
             if (exceptionObject.getCurrentOnto().equals("")) {
        System.out.println(replyObject.getCurrentOnto());
       }
              
             if (replyObject.getCurrentOnto().equals(exceptionObject.getCurrentOnto())) {
        row.getCell(10).setCellStyle(cellStyle);
       }else {
        row.getCell(10).setCellStyle(cellStyleRed);
       }
            
           if (replyObject.getUpdatedRequest().equals(exceptionObject.getUpdatedRequest())) {
            updatedRequestEquals = true;
       row.getCell(11).setCellStyle(cellStyle);
      }else {
       row.getCell(11).setCellStyle(cellStyleRed);
      }
           
           if (replyObject.getReplyType().equals(exceptionObject.getReplyType())) {
       row.getCell(12).setCellStyle(cellStyle);
      }else {
       row.getCell(12).setCellStyle(cellStyleRed);
      }
      
      if (replyObject.getCurrentReply().equals(exceptionObject.getCurrentReply())) {
       row.getCell(13).setCellStyle(cellStyle);
      }else {
       row.getCell(13).setCellStyle(cellStyleRed);
      }
      
      System.out.println("rowNum = "+rowNum +":"+ exceptionObject.getBkColor());
      /*if (updatedRequestEquals && exceptionObject.getBkColor() != nu ll) {
       row.getCell(7).getCellStyle().cloneStyleFrom(exceptionObject.getBkColor());
       System.out.println("DestCell name: " + row.getCell(7).getCellStyle().getFillForegroundXSSFColor().getARGBHex()
             + " SourceCell name: " + exceptionObject.getBkColor().getFillForegroundXSSFColor().getARGBHex());

       updatedRequestEquals = false;
      }*/
      if (grammarEquals && updatedRequestEquals) {
       if (exceptionObject.getBkColor() != null) {
       row.getCell(7).setCellStyle(exceptionObject.getBkColor());
       }
      }
     }
     }
    
    out.flush();
    xssfWorkbook.write(out);
    out.close();
    xssfWorkbook.close();
 }catch(FileNotFoundException e){
 }
 catch (Exception e) {
  e.printStackTrace();
 }
 }


CellStyle setBorder = xssfWorkbook.createCellStyle();

一、设置背景色:

setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

三、设置居中:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

四、设置字体:

Font font =  xssfWorkbook.createFont();
font.setColor(HSSFColor.GREEN.index);
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行


注意:1,一定要在同一个workbook中,读写才能正常

           2,当文本中有换行时,一定要设置此单元格样式为自动换行


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:11022次
    • 积分:319
    • 等级:
    • 排名:千里之外
    • 原创:15篇
    • 转载:42篇
    • 译文:0篇
    • 评论:4条
    最新评论