You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

430 lines
20KB

  1. // Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of
  2. // this source code is governed by a BSD-style license that can be found in
  3. // the LICENSE file.
  4. //
  5. // Package excelize providing a set of functions that allow you to write to
  6. // and read from XLSX files. Support reads and writes XLSX file generated by
  7. // Microsoft Excel™ 2007 and later. Support save file without losing original
  8. // charts of XLSX. This library needs Go version 1.10 or later.
  9. package excelize
  10. import "encoding/xml"
  11. // Source relationship and namespace.
  12. const (
  13. SourceRelationship = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
  14. SourceRelationshipChart = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
  15. SourceRelationshipComments = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments"
  16. SourceRelationshipImage = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
  17. SourceRelationshipTable = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table"
  18. SourceRelationshipDrawingML = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
  19. SourceRelationshipDrawingVML = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"
  20. SourceRelationshipHyperLink = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
  21. SourceRelationshipWorkSheet = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
  22. SourceRelationshipPivotTable = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
  23. SourceRelationshipPivotCache = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition"
  24. SourceRelationshipVBAProject = "http://schemas.microsoft.com/office/2006/relationships/vbaProject"
  25. SourceRelationshipChart201506 = "http://schemas.microsoft.com/office/drawing/2015/06/chart"
  26. SourceRelationshipChart20070802 = "http://schemas.microsoft.com/office/drawing/2007/8/2/chart"
  27. SourceRelationshipChart2014 = "http://schemas.microsoft.com/office/drawing/2014/chart"
  28. SourceRelationshipCompatibility = "http://schemas.openxmlformats.org/markup-compatibility/2006"
  29. NameSpaceDrawingML = "http://schemas.openxmlformats.org/drawingml/2006/main"
  30. NameSpaceDrawingMLChart = "http://schemas.openxmlformats.org/drawingml/2006/chart"
  31. NameSpaceDrawingMLSpreadSheet = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
  32. NameSpaceSpreadSheet = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  33. NameSpaceSpreadSheetX14 = "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
  34. NameSpaceSpreadSheetX15 = "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
  35. NameSpaceSpreadSheetExcel2006Main = "http://schemas.microsoft.com/office/excel/2006/main"
  36. NameSpaceMacExcel2008Main = "http://schemas.microsoft.com/office/mac/excel/2008/main"
  37. NameSpaceXML = "http://www.w3.org/XML/1998/namespace"
  38. NameSpaceXMLSchemaInstance = "http://www.w3.org/2001/XMLSchema-instance"
  39. StrictSourceRelationship = "http://purl.oclc.org/ooxml/officeDocument/relationships"
  40. StrictSourceRelationshipChart = "http://purl.oclc.org/ooxml/officeDocument/relationships/chart"
  41. StrictSourceRelationshipComments = "http://purl.oclc.org/ooxml/officeDocument/relationships/comments"
  42. StrictSourceRelationshipImage = "http://purl.oclc.org/ooxml/officeDocument/relationships/image"
  43. StrictNameSpaceSpreadSheet = "http://purl.oclc.org/ooxml/spreadsheetml/main"
  44. NameSpaceDublinCore = "http://purl.org/dc/elements/1.1/"
  45. NameSpaceDublinCoreTerms = "http://purl.org/dc/terms/"
  46. NameSpaceDublinCoreMetadataIntiative = "http://purl.org/dc/dcmitype/"
  47. // ExtURIConditionalFormattings is the extLst child element
  48. // ([ISO/IEC29500-1:2016] section 18.2.10) of the worksheet element
  49. // ([ISO/IEC29500-1:2016] section 18.3.1.99) is extended by the addition of
  50. // new child ext elements ([ISO/IEC29500-1:2016] section 18.2.7)
  51. ExtURIConditionalFormattings = "{78C0D931-6437-407D-A8EE-F0AAD7539E65}"
  52. ExtURIDataValidations = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
  53. ExtURISparklineGroups = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
  54. ExtURISlicerListX14 = "{A8765BA9-456A-4DAB-B4F3-ACF838C121DE}"
  55. ExtURISlicerCachesListX14 = "{BBE1A952-AA13-448e-AADC-164F8A28A991}"
  56. ExtURISlicerListX15 = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}"
  57. ExtURIProtectedRanges = "{FC87AEE6-9EDD-4A0A-B7FB-166176984837}"
  58. ExtURIIgnoredErrors = "{01252117-D84E-4E92-8308-4BE1C098FCBB}"
  59. ExtURIWebExtensions = "{F7C9EE02-42E1-4005-9D12-6889AFFD525C}"
  60. ExtURITimelineRefs = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}"
  61. ExtURIDrawingBlip = "{28A0092B-C50C-407E-A947-70E740481C1C}"
  62. ExtURIMacExcelMX = "{64002731-A6B0-56B0-2670-7721B7C09600}"
  63. )
  64. var supportImageTypes = map[string]string{".gif": ".gif", ".jpg": ".jpeg", ".jpeg": ".jpeg", ".png": ".png", ".tif": ".tiff", ".tiff": ".tiff"}
  65. // xlsxCNvPr directly maps the cNvPr (Non-Visual Drawing Properties). This
  66. // element specifies non-visual canvas properties. This allows for additional
  67. // information that does not affect the appearance of the picture to be stored.
  68. type xlsxCNvPr struct {
  69. ID int `xml:"id,attr"`
  70. Name string `xml:"name,attr"`
  71. Descr string `xml:"descr,attr"`
  72. Title string `xml:"title,attr,omitempty"`
  73. HlinkClick *xlsxHlinkClick `xml:"a:hlinkClick"`
  74. }
  75. // xlsxHlinkClick (Click Hyperlink) Specifies the on-click hyperlink
  76. // information to be applied to a run of text. When the hyperlink text is
  77. // clicked the link is fetched.
  78. type xlsxHlinkClick struct {
  79. R string `xml:"xmlns:r,attr,omitempty"`
  80. RID string `xml:"r:id,attr,omitempty"`
  81. InvalidURL string `xml:"invalidUrl,attr,omitempty"`
  82. Action string `xml:"action,attr,omitempty"`
  83. TgtFrame string `xml:"tgtFrame,attr,omitempty"`
  84. Tooltip string `xml:"tooltip,attr,omitempty"`
  85. History bool `xml:"history,attr,omitempty"`
  86. HighlightClick bool `xml:"highlightClick,attr,omitempty"`
  87. EndSnd bool `xml:"endSnd,attr,omitempty"`
  88. }
  89. // xlsxPicLocks directly maps the picLocks (Picture Locks). This element
  90. // specifies all locking properties for a graphic frame. These properties inform
  91. // the generating application about specific properties that have been
  92. // previously locked and thus should not be changed.
  93. type xlsxPicLocks struct {
  94. NoAdjustHandles bool `xml:"noAdjustHandles,attr,omitempty"`
  95. NoChangeArrowheads bool `xml:"noChangeArrowheads,attr,omitempty"`
  96. NoChangeAspect bool `xml:"noChangeAspect,attr"`
  97. NoChangeShapeType bool `xml:"noChangeShapeType,attr,omitempty"`
  98. NoCrop bool `xml:"noCrop,attr,omitempty"`
  99. NoEditPoints bool `xml:"noEditPoints,attr,omitempty"`
  100. NoGrp bool `xml:"noGrp,attr,omitempty"`
  101. NoMove bool `xml:"noMove,attr,omitempty"`
  102. NoResize bool `xml:"noResize,attr,omitempty"`
  103. NoRot bool `xml:"noRot,attr,omitempty"`
  104. NoSelect bool `xml:"noSelect,attr,omitempty"`
  105. }
  106. // xlsxBlip directly maps the blip element in the namespace
  107. // http://purl.oclc.org/ooxml/officeDoc ument/relationships - This element
  108. // specifies the existence of an image (binary large image or picture) and
  109. // contains a reference to the image data.
  110. type xlsxBlip struct {
  111. Embed string `xml:"r:embed,attr"`
  112. Cstate string `xml:"cstate,attr,omitempty"`
  113. R string `xml:"xmlns:r,attr"`
  114. }
  115. // xlsxStretch directly maps the stretch element. This element specifies that a
  116. // BLIP should be stretched to fill the target rectangle. The other option is a
  117. // tile where a BLIP is tiled to fill the available area.
  118. type xlsxStretch struct {
  119. FillRect string `xml:"a:fillRect"`
  120. }
  121. // xlsxOff directly maps the colOff and rowOff element. This element is used to
  122. // specify the column offset within a cell.
  123. type xlsxOff struct {
  124. X int `xml:"x,attr"`
  125. Y int `xml:"y,attr"`
  126. }
  127. // xlsxExt directly maps the ext element.
  128. type xlsxExt struct {
  129. Cx int `xml:"cx,attr"`
  130. Cy int `xml:"cy,attr"`
  131. }
  132. // xlsxPrstGeom directly maps the prstGeom (Preset geometry). This element
  133. // specifies when a preset geometric shape should be used instead of a custom
  134. // geometric shape. The generating application should be able to render all
  135. // preset geometries enumerated in the ST_ShapeType list.
  136. type xlsxPrstGeom struct {
  137. Prst string `xml:"prst,attr"`
  138. }
  139. // xlsxXfrm directly maps the xfrm (2D Transform for Graphic Frame). This
  140. // element specifies the transform to be applied to the corresponding graphic
  141. // frame. This transformation is applied to the graphic frame just as it would
  142. // be for a shape or group shape.
  143. type xlsxXfrm struct {
  144. Off xlsxOff `xml:"a:off"`
  145. Ext xlsxExt `xml:"a:ext"`
  146. }
  147. // xlsxCNvPicPr directly maps the cNvPicPr (Non-Visual Picture Drawing
  148. // Properties). This element specifies the non-visual properties for the picture
  149. // canvas. These properties are to be used by the generating application to
  150. // determine how certain properties are to be changed for the picture object in
  151. // question.
  152. type xlsxCNvPicPr struct {
  153. PicLocks xlsxPicLocks `xml:"a:picLocks"`
  154. }
  155. // directly maps the nvPicPr (Non-Visual Properties for a Picture). This element
  156. // specifies all non-visual properties for a picture. This element is a
  157. // container for the non-visual identification properties, shape properties and
  158. // application properties that are to be associated with a picture. This allows
  159. // for additional information that does not affect the appearance of the picture
  160. // to be stored.
  161. type xlsxNvPicPr struct {
  162. CNvPr xlsxCNvPr `xml:"xdr:cNvPr"`
  163. CNvPicPr xlsxCNvPicPr `xml:"xdr:cNvPicPr"`
  164. }
  165. // xlsxBlipFill directly maps the blipFill (Picture Fill). This element
  166. // specifies the kind of picture fill that the picture object has. Because a
  167. // picture has a picture fill already by default, it is possible to have two
  168. // fills specified for a picture object.
  169. type xlsxBlipFill struct {
  170. Blip xlsxBlip `xml:"a:blip"`
  171. Stretch xlsxStretch `xml:"a:stretch"`
  172. }
  173. // xlsxSpPr directly maps the spPr (Shape Properties). This element specifies
  174. // the visual shape properties that can be applied to a picture. These are the
  175. // same properties that are allowed to describe the visual properties of a shape
  176. // but are used here to describe the visual appearance of a picture within a
  177. // document.
  178. type xlsxSpPr struct {
  179. Xfrm xlsxXfrm `xml:"a:xfrm"`
  180. PrstGeom xlsxPrstGeom `xml:"a:prstGeom"`
  181. }
  182. // xlsxPic elements encompass the definition of pictures within the DrawingML
  183. // framework. While pictures are in many ways very similar to shapes they have
  184. // specific properties that are unique in order to optimize for picture-
  185. // specific scenarios.
  186. type xlsxPic struct {
  187. NvPicPr xlsxNvPicPr `xml:"xdr:nvPicPr"`
  188. BlipFill xlsxBlipFill `xml:"xdr:blipFill"`
  189. SpPr xlsxSpPr `xml:"xdr:spPr"`
  190. }
  191. // xlsxFrom specifies the starting anchor.
  192. type xlsxFrom struct {
  193. Col int `xml:"xdr:col"`
  194. ColOff int `xml:"xdr:colOff"`
  195. Row int `xml:"xdr:row"`
  196. RowOff int `xml:"xdr:rowOff"`
  197. }
  198. // xlsxTo directly specifies the ending anchor.
  199. type xlsxTo struct {
  200. Col int `xml:"xdr:col"`
  201. ColOff int `xml:"xdr:colOff"`
  202. Row int `xml:"xdr:row"`
  203. RowOff int `xml:"xdr:rowOff"`
  204. }
  205. // xdrClientData directly maps the clientData element. An empty element which
  206. // specifies (via attributes) certain properties related to printing and
  207. // selection of the drawing object. The fLocksWithSheet attribute (either true
  208. // or false) determines whether to disable selection when the sheet is
  209. // protected, and fPrintsWithSheet attribute (either true or false) determines
  210. // whether the object is printed when the sheet is printed.
  211. type xdrClientData struct {
  212. FLocksWithSheet bool `xml:"fLocksWithSheet,attr"`
  213. FPrintsWithSheet bool `xml:"fPrintsWithSheet,attr"`
  214. }
  215. // xdrCellAnchor directly maps the oneCellAnchor (One Cell Anchor Shape Size)
  216. // and twoCellAnchor (Two Cell Anchor Shape Size). This element specifies a two
  217. // cell anchor placeholder for a group, a shape, or a drawing element. It moves
  218. // with cells and its extents are in EMU units.
  219. type xdrCellAnchor struct {
  220. EditAs string `xml:"editAs,attr,omitempty"`
  221. From *xlsxFrom `xml:"xdr:from"`
  222. To *xlsxTo `xml:"xdr:to"`
  223. Ext *xlsxExt `xml:"xdr:ext"`
  224. Sp *xdrSp `xml:"xdr:sp"`
  225. Pic *xlsxPic `xml:"xdr:pic,omitempty"`
  226. GraphicFrame string `xml:",innerxml"`
  227. ClientData *xdrClientData `xml:"xdr:clientData"`
  228. }
  229. // xlsxWsDr directly maps the root element for a part of this content type shall
  230. // wsDr.
  231. type xlsxWsDr struct {
  232. XMLName xml.Name `xml:"xdr:wsDr"`
  233. OneCellAnchor []*xdrCellAnchor `xml:"xdr:oneCellAnchor"`
  234. TwoCellAnchor []*xdrCellAnchor `xml:"xdr:twoCellAnchor"`
  235. A string `xml:"xmlns:a,attr,omitempty"`
  236. Xdr string `xml:"xmlns:xdr,attr,omitempty"`
  237. R string `xml:"xmlns:r,attr,omitempty"`
  238. }
  239. // xlsxGraphicFrame (Graphic Frame) directly maps the xdr:graphicFrame element.
  240. // This element specifies the existence of a graphics frame. This frame contains
  241. // a graphic that was generated by an external source and needs a container in
  242. // which to be displayed on the slide surface.
  243. type xlsxGraphicFrame struct {
  244. XMLName xml.Name `xml:"xdr:graphicFrame"`
  245. Macro string `xml:"macro,attr"`
  246. NvGraphicFramePr xlsxNvGraphicFramePr `xml:"xdr:nvGraphicFramePr"`
  247. Xfrm xlsxXfrm `xml:"xdr:xfrm"`
  248. Graphic *xlsxGraphic `xml:"a:graphic"`
  249. }
  250. // xlsxNvGraphicFramePr (Non-Visual Properties for a Graphic Frame) directly
  251. // maps the xdr:nvGraphicFramePr element. This element specifies all non-visual
  252. // properties for a graphic frame. This element is a container for the non-
  253. // visual identification properties, shape properties and application properties
  254. // that are to be associated with a graphic frame. This allows for additional
  255. // information that does not affect the appearance of the graphic frame to be
  256. // stored.
  257. type xlsxNvGraphicFramePr struct {
  258. CNvPr *xlsxCNvPr `xml:"xdr:cNvPr"`
  259. ChicNvGraphicFramePr string `xml:"xdr:cNvGraphicFramePr"`
  260. }
  261. // xlsxGraphic (Graphic Object) directly maps the a:graphic element. This
  262. // element specifies the existence of a single graphic object. Document authors
  263. // should refer to this element when they wish to persist a graphical object of
  264. // some kind. The specification for this graphical object is provided entirely
  265. // by the document author and referenced within the graphicData child element.
  266. type xlsxGraphic struct {
  267. GraphicData *xlsxGraphicData `xml:"a:graphicData"`
  268. }
  269. // xlsxGraphicData (Graphic Object Data) directly maps the a:graphicData
  270. // element. This element specifies the reference to a graphic object within the
  271. // document. This graphic object is provided entirely by the document authors
  272. // who choose to persist this data within the document.
  273. type xlsxGraphicData struct {
  274. URI string `xml:"uri,attr"`
  275. Chart *xlsxChart `xml:"c:chart,omitempty"`
  276. }
  277. // xlsxChart (Chart) directly maps the c:chart element.
  278. type xlsxChart struct {
  279. C string `xml:"xmlns:c,attr"`
  280. RID string `xml:"r:id,attr"`
  281. R string `xml:"xmlns:r,attr"`
  282. }
  283. // xdrSp (Shape) directly maps the xdr:sp element. This element specifies the
  284. // existence of a single shape. A shape can either be a preset or a custom
  285. // geometry, defined using the SpreadsheetDrawingML framework. In addition to a
  286. // geometry each shape can have both visual and non-visual properties attached.
  287. // Text and corresponding styling information can also be attached to a shape.
  288. // This shape is specified along with all other shapes within either the shape
  289. // tree or group shape elements.
  290. type xdrSp struct {
  291. Macro string `xml:"macro,attr"`
  292. Textlink string `xml:"textlink,attr"`
  293. NvSpPr *xdrNvSpPr `xml:"xdr:nvSpPr"`
  294. SpPr *xlsxSpPr `xml:"xdr:spPr"`
  295. Style *xdrStyle `xml:"xdr:style"`
  296. TxBody *xdrTxBody `xml:"xdr:txBody"`
  297. }
  298. // xdrNvSpPr (Non-Visual Properties for a Shape) directly maps the xdr:nvSpPr
  299. // element. This element specifies all non-visual properties for a shape. This
  300. // element is a container for the non-visual identification properties, shape
  301. // properties and application properties that are to be associated with a shape.
  302. // This allows for additional information that does not affect the appearance of
  303. // the shape to be stored.
  304. type xdrNvSpPr struct {
  305. CNvPr *xlsxCNvPr `xml:"xdr:cNvPr"`
  306. CNvSpPr *xdrCNvSpPr `xml:"xdr:cNvSpPr"`
  307. }
  308. // xdrCNvSpPr (Connection Non-Visual Shape Properties) directly maps the
  309. // xdr:cNvSpPr element. This element specifies the set of non-visual properties
  310. // for a connection shape. These properties specify all data about the
  311. // connection shape which do not affect its display within a spreadsheet.
  312. type xdrCNvSpPr struct {
  313. TxBox bool `xml:"txBox,attr"`
  314. }
  315. // xdrStyle (Shape Style) directly maps the xdr:style element. The element
  316. // specifies the style that is applied to a shape and the corresponding
  317. // references for each of the style components such as lines and fills.
  318. type xdrStyle struct {
  319. LnRef *aRef `xml:"a:lnRef"`
  320. FillRef *aRef `xml:"a:fillRef"`
  321. EffectRef *aRef `xml:"a:effectRef"`
  322. FontRef *aFontRef `xml:"a:fontRef"`
  323. }
  324. // aRef directly maps the a:lnRef, a:fillRef and a:effectRef element.
  325. type aRef struct {
  326. Idx int `xml:"idx,attr"`
  327. ScrgbClr *aScrgbClr `xml:"a:scrgbClr"`
  328. SchemeClr *attrValString `xml:"a:schemeClr"`
  329. SrgbClr *attrValString `xml:"a:srgbClr"`
  330. }
  331. // aScrgbClr (RGB Color Model - Percentage Variant) directly maps the a:scrgbClr
  332. // element. This element specifies a color using the red, green, blue RGB color
  333. // model. Each component, red, green, and blue is expressed as a percentage from
  334. // 0% to 100%. A linear gamma of 1.0 is assumed.
  335. type aScrgbClr struct {
  336. R float64 `xml:"r,attr"`
  337. G float64 `xml:"g,attr"`
  338. B float64 `xml:"b,attr"`
  339. }
  340. // aFontRef (Font Reference) directly maps the a:fontRef element. This element
  341. // represents a reference to a themed font. When used it specifies which themed
  342. // font to use along with a choice of color.
  343. type aFontRef struct {
  344. Idx string `xml:"idx,attr"`
  345. SchemeClr *attrValString `xml:"a:schemeClr"`
  346. }
  347. // xdrTxBody (Shape Text Body) directly maps the xdr:txBody element. This
  348. // element specifies the existence of text to be contained within the
  349. // corresponding shape. All visible text and visible text related properties are
  350. // contained within this element. There can be multiple paragraphs and within
  351. // paragraphs multiple runs of text.
  352. type xdrTxBody struct {
  353. BodyPr *aBodyPr `xml:"a:bodyPr"`
  354. P []*aP `xml:"a:p"`
  355. }
  356. // formatPicture directly maps the format settings of the picture.
  357. type formatPicture struct {
  358. FPrintsWithSheet bool `json:"print_obj"`
  359. FLocksWithSheet bool `json:"locked"`
  360. NoChangeAspect bool `json:"lock_aspect_ratio"`
  361. OffsetX int `json:"x_offset"`
  362. OffsetY int `json:"y_offset"`
  363. XScale float64 `json:"x_scale"`
  364. YScale float64 `json:"y_scale"`
  365. Hyperlink string `json:"hyperlink"`
  366. HyperlinkType string `json:"hyperlink_type"`
  367. Positioning string `json:"positioning"`
  368. }
  369. // formatShape directly maps the format settings of the shape.
  370. type formatShape struct {
  371. Type string `json:"type"`
  372. Width int `json:"width"`
  373. Height int `json:"height"`
  374. Format formatPicture `json:"format"`
  375. Color formatShapeColor `json:"color"`
  376. Paragraph []formatShapeParagraph `json:"paragraph"`
  377. }
  378. // formatShapeParagraph directly maps the format settings of the paragraph in
  379. // the shape.
  380. type formatShapeParagraph struct {
  381. Font formatFont `json:"font"`
  382. Text string `json:"text"`
  383. }
  384. // formatShapeColor directly maps the color settings of the shape.
  385. type formatShapeColor struct {
  386. Line string `json:"line"`
  387. Fill string `json:"fill"`
  388. Effect string `json:"effect"`
  389. }