HSSFRichTextString样式未应用于具有默认值的单元格

Question

我有模板xls，其中每个单元格的字体设置为Arial(font-size:6.5)。在我的应用程序中，我正在使用上面的默认模板生成新的excel报表。

Workbook workbook = new HSSFWorkbook(InputStream s);

因此，新的excel获得了我的模板XLS中指定的所有属性（字体样式）。

现在，当我使用HSSFRichTextString写入特定单元格时，它的样式不会被应用。比如在包含文本和数字的单元格中，我想把文本做为Arial，把数字做为Terminal。例如SS 123 SS：Arial,123：Terminal。但由于我的单元格的默认字体样式为Arial，所以单元格中的所有内容都只写成Arial。虽然当我在单元格中选择&检查123时，它所描绘的字体为终端，但实际应用的字体只有Arial。

private static HSSFRichTextString formatNumbersOfCellText(String outputString,Font numberFont, Font strFont){
    HSSFRichTextString formattedString = null;
     try{
        if(null != outputString &&               !outputString.trim().equalsIgnoreCase("")){
            int lstIndexCalculated=0;
            int startIndex ;
            int endIndex;
            String[] splittedArr = outputString.split("\\s");
            if(null != splittedArr && splittedArr.length > 0){
                formattedString = new HSSFRichTextString(outputString);
                for (int i = 0; i < splittedArr.length; i++) {

                        if(lstIndexCalculated == 0){
                            startIndex =       outputString.indexOf(splittedArr[i]);
                        }else{
                            startIndex = outputString.indexOf(splittedArr[i],lstIndexCalculated);
                            if(lstIndexCalculated < startIndex){
                                formattedString.applyFont(lstIndexCalculated,startIndex ,numberFont);
                            }
                        }
                        endIndex   = startIndex + (splittedArr[i].length());
                        lstIndexCalculated = endIndex;
                        if(isNumericField(splittedArr[i])){
                            formattedString.applyFont(startIndex,endIndex ,numberFont);
                        }else{
                            formattedString.applyFont(startIndex,endIndex ,strFont);
                        }
                        startIndex = 0;
                        endIndex   = 0;

                }
                if(lstIndexCalculated != 0){
                    return formattedString;
                }
            }
        }
    }catch(Exception e){
      return null;   
    }
    return null;

}

 RichTextString richString = new HSSFRichTextString( "SS 123 SS" );
 richString.applyFont( 0, 3, font1 );
 cell.setCellValue( richString );

 RichTextString richString = new HSSFRichTextString( "SS 123 SS" );
 richString.applyFont( 0, 3, font1 );
 richString.applyFont( 3, 6, font2 );
 richString.applyFont( 6, 9, font1 );
 cell.setCellValue( richString );

Answer 1

不知道你做错了什么。

假设source.xls如下所示：

这样的代码：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

class ReadAndWriteRichText {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("Source.xls");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);
   Row row = sheet.getRow(0);
   Cell cell = row.getCell(0);

   Font font1 = wb.getFontAt(cell.getCellStyle().getFontIndex());
   System.out.println(font1);

   Font font2 = wb.createFont();
   font2.setFontName("Terminal");
   font2.setFontHeightInPoints(font1.getFontHeightInPoints());
   System.out.println(font2);

   RichTextString richString = new HSSFRichTextString( "SS 123 SS" );
                                                      //^0 ^3 ^6 ^9
   richString.applyFont( 0, 3, font1 );
   richString.applyFont( 3, 6, font2 );
   richString.applyFont( 6, 9, font1 );
   cell.setCellValue( richString );

   FileOutputStream fileOut = new FileOutputStream("Target.xls");
   wb.write(fileOut);
   wb.close();

  } catch (Exception ex) {
  }
 }
}

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

class ReadAndWriteRichText {

 private static boolean isNumeric(String string) {
  try {  
   double d = Double.parseDouble(string);  
  } catch(NumberFormatException nfe) {  
   return false;  
  }  
  return true;  
 }

 private static RichTextString formatNumbersOfCellText(String outputString, Font numberFont, Font strFont) {

  RichTextString richString = new HSSFRichTextString(outputString);

  String[] tokens = outputString.split("\\s"); // splits on a single space, so possibly empty strings are in the array

  int start = 0;
  int end = 0;
  for (int i = 0; i < tokens.length; i++) {
   String token = tokens[i];
   end = start + token.length();

   if (i < tokens.length - 1) {
    end = end + 1; // if not the last token, then take one character more than the length because of formatting the split-delimiter-space after the token
   }

   if (isNumeric(token)) {
    richString.applyFont(start, end, numberFont);
   } else {
    richString.applyFont(start, end, strFont);
   }
   start = end;
  }

  return richString;
 }

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("Source.xls");
   Workbook wb = WorkbookFactory.create(inp);

   Font font1 = wb.createFont();
   font1.setFontName("Arial");
   font1.setFontHeightInPoints((short)26);

   Font font2 = wb.createFont();
   font2.setFontName("Terminal");
   font2.setFontHeightInPoints((short)26);

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {
    for (Cell cell : row) {
     String cellValue = cell.getStringCellValue(); // all cells must be text cells
     RichTextString richString = formatNumbersOfCellText(cellValue, font2, font1);
     cell.setCellValue(richString);
    }
   }

   FileOutputStream fileOut = new FileOutputStream("Target.xls");
   wb.write(fileOut);
   wb.close();

  } catch (Exception ex) {
    ex.printStackTrace();
  }
 }
}