Excelize 2.4.0 is Released – New support 152 formula functions

·

4 min read

github.com/xuri/excelize

Excelize is a library written in pure Go providing a set of functions that allow you to write to and read from XLSX / XLSM / XLTM files. Supports reading and writing spreadsheet documents generated by Microsoft Excel™ 2007 and later. Supports complex components by high compatibility, and provided streaming API for generating or reading data from a worksheet with huge amounts of data.

GitHub: github.com/xuri/excelize

We are pleased to announce the release of version 2.4.0. Featured are a handful of new areas of functionality and numerous bug fixes.

A summary of changes is available in the Release Notes. A full list of changes is available in the changelog.

Release Notes

The most notable changes in this release are:

Compatibility

Upgrade requirements Go language version is 1.15 or later.

Notable Features

  • New API GetCellRichText support to get the rich text of cell by given worksheet
  • Now support to set and get: print scaling, print black and white, and specified the first printed page number of the worksheet by SetPageLayout and GetPageLayout
  • Now support to change and get tab color of the worksheet by SetSheetPrOptions and GetSheetPrOptions
  • SetCellHyperlink now support to set hyperlink display & tooltips text, related issue #790
  • Support ShowError option when adding the pivot table
  • Support setting formula for cell in streaming API, related issue #625
  • The formula calculation engine now supports not equal operator
  • The nested formula function now supports cell references as arguments
  • Support to specifies that each data marker in the series has a different color
  • New support 152 formula functions: ATAN, AVERAGE, AVERAGEA, BESSELI, BESSELJ, BIN2DEC, BIN2HEX, BIN2OCT, BITAND, BITLSHIFT, BITOR, BITRSHIFT, BITXOR, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMPLEX, CONCAT, CONCATENATE, COUNT, COUNTBLANK, CUMIPMT, CUMPRINC, DATE, DATEDIF, DB, DDB, DEC2BIN, DEC2HEX, DEC2OCT, DOLLARDE, DOLLARFR, EFFECT, ENCODEURL, EXACT, FALSE, FIND, FINDB, FISHER, FISHERINV, FIXED, FV, FVSCHEDULE, GAMMA, GAMMALN, HARMEAN, HEX2BIN, HEX2DEC, HEX2OCT, HLOOKUP, IF, IFERROR, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMCOSH, IMCOT, IMCSC, IMCSCH, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSEC, IMSECH, IMSIN, IMSINH, IMSQRT, IMSUB, IMSUM, IMTAN, IPMT, IRR, ISTEXT, ISPMT, KURT, LARGE, LEFT, LEFTB, LEN, LENB, LOOKUP, LOWER, MAX, MID, MIDB, MIN, MINA, MIRR, N, NOMINAL, NORM.DIST, NORMDIST, NORM.INV, NORMINV, NORM.S.DIST, NORMSDIST, NORM.S.INV, NORMSINV, NOT, NOW, NPER, NPV, OCT2BIN, OCT2DEC, OCT2HEX, PDURATION, PERCENTILE.INC, PERCENTILE, PERMUT, PERMUTATIONA, PMT, POISSON.DIST, POISSON, PPMT, PROPER, QUARTILE, QUARTILE.INC, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, ROMAN, ROW, ROWS, SHEET, SKEW, SMALL, STDEV, STDEV.S, STDEVA, SUBSTITUTE, T, TODAY, TRIM, TRUE, UNICHAR, UNICODE, UPPER, VAR.P, VARP, VLOOKUP

Improve the Compatibility

  • Now set the empty string for the cell when SetCellValue with nil, resolve issue #756
  • Remove useless internal XML omitempty tag on style pattern fill color
  • Fix compatibility issue of Google Sheets offline browser extension #769
  • Use absolute reference in the auto filters defined name to make it compatible with OpenOffice, resolve issue #776
  • Handle end element event in the worksheet row/column iterator XML SAX parser, faster row/column iterate and fix inconsistent read rows count of the file in some case
  • Improves compatibility for worksheet relative XML path
  • Avoid duplicate rich text string items #787
  • Improves compatibility for absolute XML path, Windows-style directory separator, and inline namespace

Bug Fixes

  • Fix round precision issue #764
  • Add missing fields and change the order of the fields of workbook fields, prevent generate the corrupted file in some case, resolve issue #766
  • Fix hyperbolic cotangent calculation incorrect
  • Correct adjust calculation chain in duplicate rows, resolve issue #774
  • Correct adjust defined name in the workbook when deleting a worksheet, resolve issue #775
  • Fix cyclomatic complexity issue of internal function newFills and parseToken
  • Fix custom row height check issue
  • Fix unmerge all cells cause corrupted file, resolve issue #782
  • Fix part of auto filter rules missing after saved
  • Fix UpdateLinkedValue which returns an error when has chart sheet or dialog sheet
  • Fix incorrect default column from GetColWidth in some case
  • Fix can't add timelines and slicers for a pivot table in generated spreadsheet, resolve issue #804
  • Fix incorrect SetDefinedName's localSheetId attribute to use sheetIndex
  • Fix missing cell locked or hidden protection in some case, resolve issue #809
  • Fix streaming data writer result missing after call normal API, resolve issue #813
  • Fix the negative values series missing chart color issue

Performance

  • Faster numeric precision process

Miscellaneous

  • The dependencies module has been updated
  • Unit tests and godoc updated
  • Documentation website with multilingual: Arabic, German, Spanish, English, French, Russian, Chinese, Japanese, and Korean, which has been updated
  • Welcome join the Slack channel or Telegram Group to meet other members of our community