excel - 为什么 JExcel 在分配的货币符号 (£) 上强制使用单元格的正前缀 ($)？

Question

我有一个程序，它使用 JExcel 库来读取 excel 工作表并对其进行一些处理。

我注意到的一次令人不安的事情(在我们的一位用户通知之后)是 JExcel 似乎强制将格式化为货币单元格的单元格转换为使用 $ 符号。我已经进行了很多挖掘，但我不知道下一步该去哪里。跳到底部看看问题的症结所在。

本质上，我们有这个方法:

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

public class Launcher {

    /**
     * @param args
     */
    public static void main(String[] args) {
        nothingSpecifc("D:\\Documents and Settings\\castone\\My Documents\\JExcelCurrencyExample.xls");
    }
    public static void nothingSpecifc(String excelFilePath){
       try { 
           File myFile = new File(excelFilePath);
           Locale myLocal = Locale.UK;
           WorkbookSettings wbkSettings = new WorkbookSettings();
           wbkSettings.setLocale(myLocal);
           wbkSettings.setEncoding("UTF-8");
           wbkSettings.setExcelDisplayLanguage("UK");
           Workbook workbook = Workbook.getWorkbook(myFile,wbkSettings);
           Sheet mySheet = workbook.getSheet(0);
           Cell[] myRow = mySheet.getRow(0);
           String myCellA1 = myRow[0].getContents();
           System.out.println(myCellA1);
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

我的示例 .xls 文件在 A1 中有这个值:



但是运行之后，我得到了这个值!



现在我无法理解为什么会发生这种情况。令人不安的是，我担心我实现的任何修复都会强制将其他货币符号转换为 £ 符号。

查看 Workbook.java，我正在调用此方法:

 /**
   * A factory method which takes in an excel file and reads in the contents.
   *
   * @exception IOException
   * @exception BiffException
   * @param file the excel 97 spreadsheet to parse
   * @param ws the settings for the workbook
   * @return a workbook instance
   */
  public static Workbook getWorkbook(java.io.File file, WorkbookSettings ws)
    throws IOException, BiffException
  {
    FileInputStream fis = new FileInputStream(file);

    // Always close down the input stream, regardless of whether or not the
    // file can be parsed.  Thanks to Steve Hahn for this
    File dataFile = null;

    try
    {
      dataFile = new File(fis, ws);
    }
    catch (IOException e)
    {
      fis.close();
      throw e;
    }
    catch (BiffException e)
    {
      fis.close();
      throw e;
    }

    fis.close();

    Workbook workbook = new WorkbookParser(dataFile, ws);
    workbook.parse();

    return workbook;
  }

所以本质上，这个类只是文件 I/O 处理和 excel 之间的一个巧妙的包装器/接口(interface)。我正在研究 WorkbookSettings 和 WorkbookParser 的默认构造函数。

一、WorkbookSettings:

  /**
   * Default constructor
   */
  public WorkbookSettings()
  {
    initialFileSize = DEFAULT_INITIAL_FILE_SIZE;
    arrayGrowSize = DEFAULT_ARRAY_GROW_SIZE;
    localeFunctionNames = new HashMap();
    excelDisplayLanguage = CountryCode.USA.getCode();
    excelRegionalSettings = CountryCode.UK.getCode();
    refreshAll = false;
    template = false;
    excel9file = false;
    windowProtected = false;
    hideobj = HIDEOBJ_SHOW_ALL;

    // Initialize other properties from the system properties
    try
    {
      boolean suppressWarnings = Boolean.getBoolean("jxl.nowarnings");
      setSuppressWarnings(suppressWarnings);
      drawingsDisabled        = Boolean.getBoolean("jxl.nodrawings");
      namesDisabled           = Boolean.getBoolean("jxl.nonames");
      gcDisabled              = Boolean.getBoolean("jxl.nogc");
      rationalizationDisabled = Boolean.getBoolean("jxl.norat");
      mergedCellCheckingDisabled =
        Boolean.getBoolean("jxl.nomergedcellchecks");
      formulaReferenceAdjustDisabled =
                                Boolean.getBoolean("jxl.noformulaadjust");
      propertySetsDisabled = Boolean.getBoolean("jxl.nopropertysets");
      ignoreBlankCells = Boolean.getBoolean("jxl.ignoreblanks");
      cellValidationDisabled = Boolean.getBoolean("jxl.nocellvalidation");
      autoFilterDisabled = !Boolean.getBoolean("jxl.autofilter"); 
             // autofilter currently disabled by default
      useTemporaryFileDuringWrite = 
        Boolean.getBoolean("jxl.usetemporaryfileduringwrite");
      String tempdir =
        System.getProperty("jxl.temporaryfileduringwritedirectory");

      if (tempdir != null)
      {
        temporaryFileDuringWriteDirectory = new File(tempdir);
      }

      encoding = System.getProperty("file.encoding");
    }
    catch (SecurityException e)
    {
      logger.warn("Error accessing system properties.", e);
    }

    // Initialize the locale to the system locale
    try
    {
      if (System.getProperty("jxl.lang")    == null ||
          System.getProperty("jxl.country") == null)
      {
        locale = Locale.getDefault();
      }
      else
      {
        locale = new Locale(System.getProperty("jxl.lang"),
                            System.getProperty("jxl.country"));
      }

      if (System.getProperty("jxl.encoding") != null)
      {
        encoding = System.getProperty("jxl.encoding");
      }
    } 
    catch (SecurityException e)
    {
      logger.warn("Error accessing system properties.", e);
      locale = Locale.getDefault();
    }
  }

这里似乎什么都没有，我已将编码、本地和显示语言设置为英国特定/兼容的。默认情况下，区域设置已经是英国。

现在对于 WorkbookParser:

public class WorkbookParser extends Workbook
  implements ExternalSheet, WorkbookMethods
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(WorkbookParser.class);

  /**
   * The excel file
   */
  private File excelFile;
  /**
   * The number of open bofs
   */
  private int bofs;
  /**
   * Indicates whether or not the dates are based around the 1904 date system
   */
  private boolean nineteenFour;
  /**
   * The shared string table
   */
  private SSTRecord sharedStrings;
  /**
   * The names of all the worksheets
   */
  private ArrayList boundsheets;
  /**
   * The xf records
   */
  private FormattingRecords formattingRecords;
  /**
   * The fonts used by this workbook
   */
  private Fonts fonts;

  /**
   * The sheets contained in this workbook
   */
  private ArrayList sheets;

  /**
   * The last sheet accessed
   */
  private SheetImpl lastSheet;

  /**
   * The index of the last sheet retrieved
   */
  private int lastSheetIndex;

  /**
   * The named records found in this workbook
   */
  private HashMap namedRecords;

  /**
   * The list of named records
   */
  private ArrayList nameTable;

  /**
   * The list of add in functions
   */
  private ArrayList addInFunctions;

  /**
   * The external sheet record.  Used by formulas, and names
   */
  private ExternalSheetRecord externSheet;

  /**
   * The list of supporting workbooks - used by formulas
   */
  private ArrayList supbooks;

  /**
   * The bof record for this workbook
   */
  private BOFRecord workbookBof;

  /**
   * The Mso Drawing Group record for this workbook
   */
  private MsoDrawingGroupRecord msoDrawingGroup;

  /**
   * The property set record associated with this workbook
   */
  private ButtonPropertySetRecord buttonPropertySet;

  /**
   * Workbook protected flag
   */
  private boolean wbProtected;

  /**
   * Contains macros flag
   */
  private boolean containsMacros;

  /**
   * The workbook settings
   */
  private WorkbookSettings settings;

  /**
   * The drawings contained in this workbook
   */
  private DrawingGroup drawingGroup;

  /**
   * The country record (containing the language and regional settings)
   * for this workbook
   */
  private CountryRecord countryRecord;

  private ArrayList xctRecords;

  /**
   * Constructs this object from the raw excel data
   *
   * @param f the excel 97 biff file
   * @param s the workbook settings
   */
  public WorkbookParser(File f, WorkbookSettings s)
  {
    super();
    excelFile = f;
    boundsheets = new ArrayList(10);
    fonts = new Fonts();
    formattingRecords = new FormattingRecords(fonts);
    sheets = new ArrayList(10);
    supbooks = new ArrayList(10);
    namedRecords = new HashMap();
    lastSheetIndex = -1;
    wbProtected = false;
    containsMacros = false;
    settings = s;
    xctRecords = new ArrayList(10);
  }

我在这里看不到任何会影响工作簿创建/访问的内容。

再往下看，通过床单和单元格，我发现了两件有趣的事情:

单元格的格式有这个参数|值positivePrefix|"$" (id=709)

这个货币是正确的。看看这个:

所以我的问题是，当 getContents()方法在一个单元格上运行，为什么它不与货币一起返回(给，我想是 £$#####，因为它仍然有 postivePrefix)以及为什么将它的正前缀设置为 $反正？

我看不到 src 文档中的单元格是在哪里创建的，或者 getContents() 方法在哪里实现(我发现它是声明)，所以我无法进一步挖掘，但我希望其他人知道这个原因问题”，或者至少可以解决？

Answer 1

这为我做到了。进行一些设置。使用 WorkbookSettings 作为第二个参数获取工作簿。我认为编码对你来说很重要。

    WorkbookSettings wbs = new WorkbookSettings(); 
    wbs.setExcelRegionalSettings("GB");
    wbs.setExcelDisplayLanguage("GB");
    Locale l = new Locale("en_GB");
    wbs.setLocale(l);
    wbs.setEncoding("ISO8859_1");


    Workbook workbook = Workbook.getWorkbook(new File("experimental-in.xls"), wbs);