org.apache.poi.util.LocaleUtil Java Examples

public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length != 2) {
        return ErrorEval.VALUE_INVALID;
    }
    try {
        double startDateAsNumber = getValue(args[0]);
        int offsetInMonthAsNumber = (int) getValue(args[1]);

        Date startDate = DateUtil.getJavaDate(startDateAsNumber);
        Calendar calendar = LocaleUtil.getLocaleCalendar();
        calendar.setTime(startDate);
        calendar.add(Calendar.MONTH, offsetInMonthAsNumber);
        return new NumberEval(DateUtil.getExcelDate(calendar.getTime()));
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
 

/**
 * Returns a string representation of the cell
 *
 * This method returns a simple representation,
 * anything more complex should be in user code, with
 * knowledge of the semantics of the sheet being processed.
 *
 * Formula cells return the formula string,
 * rather than the formula result.
 * Dates are displayed in dd-MMM-yyyy format
 * Errors are displayed as #ERR<errIdx>
 */
public String toString() {
    switch (getCellTypeEnum()) {
        case BLANK:
            return "";
        case BOOLEAN:
            return getBooleanCellValue()?"TRUE":"FALSE";
        case ERROR:
            return ErrorEval.getText((( BoolErrRecord ) _record).getErrorValue());
        case FORMULA:
            return getCellFormula();
        case NUMERIC:
            //TODO apply the dataformat for this cell
            if (HSSFDateUtil.isCellDateFormatted(this)) {
                SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", LocaleUtil.getUserLocale());
                sdf.setTimeZone(LocaleUtil.getUserTimeZone());
                return sdf.format(getDateCellValue());
            }
return  String.valueOf(getNumericCellValue());
        case STRING:
            return getStringCellValue();
        default:
            return "Unknown Cell Type: " + getCellTypeEnum();
    }
}
 

/**
 * Get EXCEL date as Java Calendar with given time zone.
 * @param date  The Excel date.
 * @param use1904windowing  true if date uses 1904 windowing,
 *  or false if using 1900 date windowing.
 * @param timeZone The TimeZone to evaluate the date in
 * @param roundSeconds round to closest second
 * @return Java representation of the date, or null if date is not a valid Excel date
 */
public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone, boolean roundSeconds) {
    if (!isValidExcelDate(date)) {
        return null;
    }
    int wholeDays = (int)Math.floor(date);
    int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
    Calendar calendar;
    if (timeZone != null) {
        calendar = LocaleUtil.getLocaleCalendar(timeZone);
    } else {
        calendar = LocaleUtil.getLocaleCalendar(); // using default time-zone
    }
    setCalendar(calendar, wholeDays, millisecondsInDay, use1904windowing, roundSeconds);
    return calendar;
}
 

/**
    * Calculate the workday past x workdays from a starting date, considering a range of holidays.
    *
    * @param start start date.
    * @param workdays number of workdays to be past from starting date.
    * @param holidays an array of holidays.
    * @return date past x workdays.
    */
public Date calculateWorkdays(double start, int workdays, double[] holidays) {
	Date startDate = DateUtil.getJavaDate(start);
	int direction = workdays < 0 ? -1 : 1;
	Calendar endDate = LocaleUtil.getLocaleCalendar();
	endDate.setTime(startDate);
	double excelEndDate = DateUtil.getExcelDate(endDate.getTime());
	while (workdays != 0) {
		endDate.add(Calendar.DAY_OF_YEAR, direction);
		excelEndDate += direction;
		if (endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY
				&& endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY
				&& !isHoliday(excelEndDate,	holidays)) {
			workdays -= direction;
		}
	}
	return endDate.getTime();
}
 

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length != 2) {
        return ErrorEval.VALUE_INVALID;
    }

    try {
        double startDateAsNumber = NumericFunction.singleOperandEvaluate(args[0], ec.getRowIndex(), ec.getColumnIndex());
        int months = (int) NumericFunction.singleOperandEvaluate(args[1], ec.getRowIndex(), ec.getColumnIndex());

        // Excel treats date 0 as 1900-01-00; EOMONTH results in 1900-01-31
        if (startDateAsNumber >= 0.0 && startDateAsNumber < 1.0) {
            startDateAsNumber = 1.0;
        }

        Date startDate = DateUtil.getJavaDate(startDateAsNumber, false);

        Calendar cal = LocaleUtil.getLocaleCalendar();
        cal.setTime(startDate);
        cal.clear(Calendar.HOUR);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.clear(Calendar.MINUTE);
        cal.clear(Calendar.SECOND);
        cal.clear(Calendar.MILLISECOND);

        cal.add(Calendar.MONTH, months + 1);
        cal.set(Calendar.DAY_OF_MONTH, 1);
        cal.add(Calendar.DAY_OF_MONTH, -1);

        return new NumberEval(DateUtil.getExcelDate(cal.getTime()));
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
 

/**
 * Update formats when locale has been changed
 *
 * @param observable
 *            usually this is our own Observable instance
 * @param localeObj
 *            only reacts on Locale objects
 */
public void update(Observable observable, Object localeObj) {
    if (!(localeObj instanceof Locale))
        return;
    Locale newLocale = (Locale)localeObj;
    if (!localeIsAdapting || newLocale.equals(locale))
        return;

    locale = newLocale;

    dateSymbols = DateFormatSymbols.getInstance(locale);
    decimalSymbols = DecimalFormatSymbols.getInstance(locale);
    generalNumberFormat = new ExcelGeneralNumberFormat(locale);

    // taken from Date.toString()
    defaultDateformat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", dateSymbols);
    defaultDateformat.setTimeZone(LocaleUtil.getUserTimeZone());

    // init built-in formats

    formats.clear();
    Format zipFormat = ZipPlusFourFormat.instance;
    addFormat("00000\\-0000", zipFormat);
    addFormat("00000-0000", zipFormat);

    Format phoneFormat = PhoneFormat.instance;
    // allow for format string variations
    addFormat("[<=9999999]###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
    addFormat("[<=9999999]###-####;(###) ###-####", phoneFormat);
    addFormat("###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
    addFormat("###-####;(###) ###-####", phoneFormat);

    Format ssnFormat = SSNFormat.instance;
    addFormat("000\\-00\\-0000", ssnFormat);
    addFormat("000-00-0000", ssnFormat);
}
 

private String decodeValueFromID() {
    try {
        switch((int)getID()) {
            case PropertyIDMap.PID_CODEPAGE:
                return CodePageUtil.codepageToEncoding(((Number)value).intValue());
            case PropertyIDMap.PID_LOCALE:
                return LocaleUtil.getLocaleFromLCID(((Number)value).intValue());
        }
    } catch (Exception e) {
        LOG.log(POILogger.WARN, "Can't decode id "+getID());
    }
    return null;
}
 

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
	Calendar now = LocaleUtil.getLocaleCalendar();
	now.clear(Calendar.HOUR);
       now.set(Calendar.HOUR_OF_DAY,0);
	now.clear(Calendar.MINUTE);
	now.clear(Calendar.SECOND);
	now.clear(Calendar.MILLISECOND);
	return new NumberEval(DateUtil.getExcelDate(now.getTime()));
}
 

/**
 * Creates the Country record with the default country set to 1
 * and current country set to 7 in case of russian locale ("ru_RU") and 1 otherwise
 */
private static CountryRecord createCountry() {
    CountryRecord retval = new CountryRecord();

    retval.setDefaultCountry(( short ) 1);

    // from Russia with love ;)
    if ( "ru_RU".equals( LocaleUtil.getUserLocale().toString() ) ) {
        retval.setCurrentCountry(( short ) 7);
    } else {
        retval.setCurrentCountry(( short ) 1);
    }

    return retval;
}
 

/**
 * Update formats when locale has been changed
 *
 * @param observable usually this is our own Observable instance
 * @param localeObj only reacts on Locale objects
 */
public void update(Observable observable, Object localeObj) {
    if (!(localeObj instanceof Locale))  return;
    Locale newLocale = (Locale)localeObj;
    if (!localeIsAdapting || newLocale.equals(locale)) return;
    
    locale = newLocale;
    
    dateSymbols = DateFormatSymbols.getInstance(locale);
    decimalSymbols = DecimalFormatSymbols.getInstance(locale);
    generalNumberFormat = new ExcelGeneralNumberFormat(locale);

    // taken from Date.toString()
    defaultDateformat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", dateSymbols);
    defaultDateformat.setTimeZone(LocaleUtil.getUserTimeZone());       

    // init built-in formats

    formats.clear();
    Format zipFormat = ZipPlusFourFormat.instance;
    addFormat("00000\\-0000", zipFormat);
    addFormat("00000-0000", zipFormat);

    Format phoneFormat = PhoneFormat.instance;
    // allow for format string variations
    addFormat("[<=9999999]###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
    addFormat("[<=9999999]###-####;(###) ###-####", phoneFormat);
    addFormat("###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
    addFormat("###-####;(###) ###-####", phoneFormat);

    Format ssnFormat = SSNFormat.instance;
    addFormat("000\\-00\\-0000", ssnFormat);
    addFormat("000-00-0000", ssnFormat);
}
 

/**
 * @param month 1-based
 */
private static Calendar makeDate(int year, int month, int day) throws EvaluationException {
    if (month < 1 || month > 12) {
        throw new EvaluationException(ErrorEval.VALUE_INVALID);
    }
    Calendar cal = LocaleUtil.getLocaleCalendar(year, month - 1, 1, 0, 0, 0);
    if (day < 1 || day > cal.getActualMaximum(Calendar.DAY_OF_MONTH)) {
        throw new EvaluationException(ErrorEval.VALUE_INVALID);
    }
    cal.set(Calendar.DAY_OF_MONTH, day);
    return cal;
}
 

private static Date parseYYYYMMDDDateInternal(String timeStr) throws FormatException {
    if(timeStr.length() != 10) {
        throw new FormatException("Bad length");
    }

    String yearStr = timeStr.substring(0, 4);
    String monthStr = timeStr.substring(5, 7);
    String dayStr = timeStr.substring(8, 10);
    int year = parseInt(yearStr, "year", Short.MIN_VALUE, Short.MAX_VALUE);
    int month = parseInt(monthStr, "month", 1, 12);
    int day = parseInt(dayStr, "day", 1, 31);

    Calendar cal = LocaleUtil.getLocaleCalendar(year, month-1, day);
    return cal.getTime();
}
 

/**
 * Creates a new date formatter with the given specification.
 *
 * @param locale The locale.
 * @param format The format.
 */
public CellDateFormatter(Locale locale, String format) {
    super(format);
    DatePartHandler partHandler = new DatePartHandler();
    StringBuffer descBuf = CellFormatPart.parseFormat(format,
            CellFormatType.DATE, partHandler);
    partHandler.finish(descBuf);
    // tweak the format pattern to pass tests on JDK 1.7,
    // See https://issues.apache.org/bugzilla/show_bug.cgi?id=53369
    String ptrn = descBuf.toString().replaceAll("((y)(?!y))(?<!yy)", "yy");
    dateFmt = new SimpleDateFormat(ptrn, locale);
    dateFmt.setTimeZone(LocaleUtil.getUserTimeZone());
}
 

/**
 * Creates a formatter using the {@link Locale#getDefault() default locale}.
 */
public HSSFDataFormatter() {
    this(LocaleUtil.getUserLocale());
}
 

/** Creates a new general formatter. */
public CellGeneralFormatter() {
    this(LocaleUtil.getUserLocale());
}
 

/** Retrieves the proper Java type instance based on the Excel CellTypeEnum */
private Object getCellValue(Cell cell) {
  Object value;

  // Empty cells are null, instead of BLANK
  if (cell == null) {
    return null;
  }

  switch (cell.getCellTypeEnum()) {
    case STRING:
      value = cell.getStringCellValue();
      break;
    case NUMERIC:
      if (isCellDateFormatted(cell)) {
        try {
          // Excel dates are LocalDateTime, stored without timezone.
          // Interpret them as UTC to prevent ambiguous DST overlaps which happen in other
          // timezones.
          setUserTimeZone(LocaleUtil.TIMEZONE_UTC);
          Date dateCellValue = cell.getDateCellValue();
          value = formatUTCDateAsLocalDateTime(dateCellValue);
        } finally {
          resetUserTimeZone();
        }
      } else {
        value = cell.getNumericCellValue();
      }
      break;
    case BOOLEAN:
      value = cell.getBooleanCellValue();
      break;
    case FORMULA:
      value = getTypedFormulaValue(cell);
      break;
    default:
      value = null;
      break;
  }
  return value;
}
 

/**
    * Creates Excel file based on the provided data and writes it out to an OutputStream. 
    * 
    * 
    * Warning: The styling is untested with this option and may fail. If you want full styling look at createExcel()
    *
    * @param out
    *            output stream to which the file written; usually taken from HTTP response
    * @param dataToExport
    *            array of data to print out; first index of array describes a row, second a column
    * @param dateHeader
    *            text describing current date; if <code>NULL</code> then no date is printed; if not <code>NULL</code>
    *            then text is written out along with current date in the cell; the date is formatted according to
    *            {@link #EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT}
    * @param displaySheetTitle
    *            whether to display title (printed in the first (0,0) cell)
    * 
    * @param produceXlsxFile
    *            whether excel file should be of .xlsx or .xls format. Use .xls only if you want to read the file back
    *            in again afterwards.
    * @throws IOException
    */
   public static void createExcel(OutputStream out, List<ExcelSheet> sheets, String dateHeader,
    boolean displaySheetTitle, boolean produceXlsxFile) throws IOException {
//set user time zone, which is required for outputting cells of time format 
HttpSession ss = SessionManager.getSession();
UserDTO user = (UserDTO) ss.getAttribute(AttributeNames.USER);
TimeZone userTimeZone = user.getTimeZone();
LocaleUtil.setUserTimeZone(userTimeZone);

//in case .xlsx is requested use SXSSFWorkbook.class (which keeps 100 rows in memory, exceeding rows will be flushed to disk)
Workbook workbook = produceXlsxFile ? new SXSSFWorkbook(100): new HSSFWorkbook();
ExcelUtil.initStyles(workbook);

for (ExcelSheet sheet : sheets) {
    ExcelUtil.createSheet(workbook, sheet, dateHeader, displaySheetTitle);
}

workbook.write(out);
out.close();
   }
 

private static void generateSheetData(final Sheet sheet, final CellStyle style, short startingRow) {
  int currentRow = startingRow;
  // Create first row values
  Row row1 = sheet.createRow(currentRow++);
  row1.createCell(0).setCellValue(1.0);
  row1.createCell(1).setCellValue("One");
  row1.createCell(2).setCellValue("One");
  Cell c13 = row1.createCell(3);
  c13.setCellValue(LocaleUtil.getLocaleCalendar(1983, 04/*zero based*/, 18, 4, 0, 0));
  c13.setCellStyle(style);
  Cell c14 = row1.createCell(4);
  c14.setCellFormula("A2+1");
  // For formulas we read pre-computed values. Editors set the precomputed value by default. We need to add it here
  // explicitly as the library doesn't pre compute the formula value.
  c14.setCellValue(2.0d);
  row1.createCell(5).setCellValue(true);
  row1.createCell(6).setCellFormula("B2*20");
  row1.createCell(6).setCellValue("#ERROR");

  // Create second row values
  Row row2 = sheet.createRow(currentRow++);
  row2.createCell(0).setCellValue(2.0);
  row2.createCell(1).setCellValue("Two");
  row2.createCell(2).setCellValue("Two");
  Cell c23 = row2.createCell(3);
  c23.setCellValue(LocaleUtil.getLocaleCalendar(2013, 06/*zero based*/, 05, 5, 0, 1));
  c23.setCellStyle(style);
  Cell c24 = row2.createCell(4);
  c24.setCellFormula("A3+1");
  c24.setCellValue(3.0d);
  row2.createCell(5).setCellValue(false);
  row2.createCell(6).setCellFormula("B3*20");
  row2.createCell(6).setCellValue("#ERROR");

  // Create third row values
  Row row3 = sheet.createRow(currentRow++);
  row3.createCell(0).setCellValue(3.0);
  row3.createCell(1).setCellValue("Three and Three");
  row3.createCell(5).setCellValue(false);

  // Create fourth row values
  Row row4 = sheet.createRow(currentRow++);
  row4.createCell(0).setCellValue(4.0);
  row4.createCell(1).setCellValue("Four and Four, Five and Five");

  // Create fifth row values
  Row row5 = sheet.createRow(currentRow++);
  row5.createCell(0).setCellValue(5.0);

  sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 2, 1, 2));
  sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 4, 5, 5));
  sheet.addMergedRegion(new CellRangeAddress(startingRow + 3, startingRow + 4, 1, 2));
}
 

void checkForLocaleChange() {
    checkForLocaleChange(LocaleUtil.getUserLocale());
}
 

private static Calendar getDate(double date) {
    Calendar processedDate = LocaleUtil.getLocaleCalendar();
    processedDate.setTime(DateUtil.getJavaDate(date, false));
    return processedDate;
}
 

/**
 * Note - works with Java Calendar months, not Excel months
 */
private static double evaluate(int year, int month, int pDay) throws EvaluationException {
   // We don't support negative years yet
	if (year < 0) {
		throw new EvaluationException(ErrorEval.VALUE_INVALID);
	}
	// Negative months are fairly easy
	while (month < 0) {
	   year--;
	   month += 12;
	}
	// Negative days are handled by the Java Calendar
	
	// Excel has bugs around leap years in 1900, handle them
	// Special case for the non-existant 1900 leap year
	if (year == 1900 && month == Calendar.FEBRUARY && pDay == 29) {
		return 60.0;
	}

	// If they give a date in 1900 in Jan/Feb, with the days
	//  putting it past the leap year, adjust
	int day = pDay;
	if (year == 1900) {
		if ((month == Calendar.JANUARY && day >= 60) ||
				(month == Calendar.FEBRUARY && day >= 30)) {
			day--;
		}
	}

	// Turn this into a Java date
	Calendar c = LocaleUtil.getLocaleCalendar(year, month, day);
	
	// Handle negative days of the week, that pull us across
	//  the 29th of Feb 1900
	if (pDay < 0 && c.get(Calendar.YEAR) == 1900 &&
	      month > Calendar.FEBRUARY && 
	      c.get(Calendar.MONTH) < Calendar.MARCH) {
	   c.add(Calendar.DATE, 1);
	}

	// TODO Identify if we're doing 1900 or 1904 date windowing
	boolean use1904windowing = false;
	
	// Have this Java date turned back into an Excel one
	return DateUtil.getExcelDate(c.getTime(), use1904windowing);
}
 

private static SimpleDate createDate(int dayCount) {
    /** use UTC time-zone to avoid daylight savings issues */
	Calendar cal = LocaleUtil.getLocaleCalendar(LocaleUtil.TIMEZONE_UTC);
	DateUtil.setCalendar(cal, dayCount, 0, false, false);
	return new SimpleDate(cal);
}
 

/**
 * @param aDate a given date.
 * @return <code>true</code> if date is weekend, <code>false</code> otherwise.
 */
protected boolean isWeekend(double aDate) {
    Calendar date = LocaleUtil.getLocaleCalendar();
    date.setTime(DateUtil.getJavaDate(aDate));
    return date.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || date.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY;
}
 

void checkForLocaleChange() {
    checkForLocaleChange(LocaleUtil.getUserLocale());
}
 

public ExcelStyleDateFormatter(String pattern) {
    super(processFormatPattern(pattern), LocaleUtil.getUserLocale());
}
 

/**
 * Returns a {@link CellFormat} that applies the given format.  Two calls
 * with the same format may or may not return the same object.
 *
 * @param format The format.
 *
 * @return A {@link CellFormat} that applies the given format.
 */
public static CellFormat getInstance(String format) {
    return getInstance(LocaleUtil.getUserLocale(), format);
}
 

/**
 * Creates a new date formatter with the given specification.
 *
 * @param format The format.
 */
public CellDateFormatter(String format) {
    this(LocaleUtil.getUserLocale(), format);
}
 

/**
 * Creates a new cell number formatter.
 *
 * @param format The format to parse.
 */
public CellNumberFormatter(String format) {
    this(LocaleUtil.getUserLocale(), format);
}
 

/**
 * Create an object to represent a format part.
 *
 * @param desc The string to parse.
 */
public CellFormatPart(String desc) {
    this(LocaleUtil.getUserLocale(), desc);
}
 

/**
 * Creates a new formatter object, storing the format in {@link #format}.
 *
 * @param format The format.
 */
public CellFormatter(String format) {
    this(LocaleUtil.getUserLocale(), format);
}