lucky_sheet.go 59 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878
  1. package excel
  2. import (
  3. "encoding/json"
  4. "errors"
  5. "eta/eta_api/models/data_manage/excel"
  6. "eta/eta_api/models/data_manage/excel/request"
  7. "eta/eta_api/utils"
  8. "fmt"
  9. "os"
  10. "reflect"
  11. "regexp"
  12. "strconv"
  13. "strings"
  14. "time"
  15. "github.com/shopspring/decimal"
  16. "github.com/tealeg/xlsx"
  17. "github.com/xuri/excelize/v2"
  18. )
  19. type LuckySheetDataBak struct {
  20. CalcChain []interface{} `json:"calcChain" description:"公式链"`
  21. CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
  22. ChWidth int64 `json:"ch_width" description:"工作表区域的宽度"`
  23. Config struct {
  24. BorderInfo []struct {
  25. BorderType string `json:"borderType" description:""`
  26. Color string `json:"color" description:""`
  27. Range []struct {
  28. Column []int64 `json:"column" description:""`
  29. Row []int64 `json:"row" description:""`
  30. } `json:"range" description:""`
  31. RangeType string `json:"rangeType" description:""`
  32. Style string `json:"style" description:""`
  33. Value struct {
  34. B struct {
  35. Color string `json:"color" description:""`
  36. Style string `json:"style" description:""`
  37. } `json:"b" description:""`
  38. ColIndex int64 `json:"col_index" description:""`
  39. L struct {
  40. Color string `json:"color" description:""`
  41. Style string `json:"style" description:""`
  42. } `json:"l" description:""`
  43. R struct {
  44. Color string `json:"color" description:""`
  45. Style string `json:"style" description:""`
  46. } `json:"r" description:""`
  47. RowIndex int64 `json:"row_index" description:""`
  48. T struct {
  49. Color string `json:"color" description:""`
  50. Style string `json:"style" description:""`
  51. } `json:"t" description:""`
  52. } `json:"value" description:"" description:""`
  53. } `json:"borderInfo" description:""`
  54. Colhidden struct{} `json:"colhidden" description:""`
  55. Columnlen map[string]float64 `json:"columnlen" description:""`
  56. CustomHeight struct {
  57. Zero int64 `json:"0"`
  58. } `json:"customHeight" description:""`
  59. CustomWidth struct {
  60. Two int64 `json:"2" description:""`
  61. } `json:"customWidth" description:""`
  62. Merge struct{} `json:"merge" description:""`
  63. Rowlen map[string]float64 `json:"rowlen" description:""`
  64. } `json:"config" description:""`
  65. Data [][]struct {
  66. Ct struct {
  67. Fa string `json:"fa"`
  68. T string `json:"t"`
  69. } `json:"ct"`
  70. M string `json:"m"`
  71. V interface{} `json:"v"`
  72. } `json:"data" description:""`
  73. DataVerification struct{} `json:"dataVerification" description:""`
  74. Filter interface{} `json:"filter" description:""`
  75. FilterSelect interface{} `json:"filter_select" description:""`
  76. Hyperlink struct{} `json:"hyperlink" description:""`
  77. Images struct{} `json:"images" description:""`
  78. Index string `json:"index" description:""`
  79. JfgirdSelectSave []interface{} `json:"jfgird_select_save" description:""`
  80. LuckysheetAlternateformatSave []interface{} `json:"luckysheet_alternateformat_save" description:""`
  81. LuckysheetConditionformatSave []interface{} `json:"luckysheet_conditionformat_save" description:""`
  82. LuckysheetSelectSave []struct {
  83. Column []int64 `json:"column" description:""`
  84. ColumnFocus int64 `json:"column_focus" description:""`
  85. Height int64 `json:"height" description:""`
  86. HeightMove int64 `json:"height_move" description:""`
  87. Left int64 `json:"left" description:""`
  88. LeftMove int64 `json:"left_move" description:""`
  89. Row []int64 `json:"row" description:""`
  90. RowFocus int64 `json:"row_focus" description:""`
  91. Top int64 `json:"top" description:""`
  92. TopMove int64 `json:"top_move" description:""`
  93. Width int64 `json:"width" description:""`
  94. WidthMove int64 `json:"width_move" description:""`
  95. } `json:"luckysheet_select_save" description:"" description:""`
  96. LuckysheetSelectionRange []struct {
  97. Column []int64 `json:"column" description:""`
  98. Row []int64 `json:"row" description:""`
  99. } `json:"luckysheet_selection_range" description:""`
  100. RhHeight float64 `json:"rh_height" description:""`
  101. ScrollLeft float64 `json:"scrollLeft" description:""`
  102. ScrollTop float64 `json:"scrollTop" description:""`
  103. Status int64 `json:"status" description:""`
  104. Visibledatacolumn []int64 `json:"visibledatacolumn" description:""`
  105. Visibledatarow []int64 `json:"visibledatarow" description:""`
  106. ZoomRatio float64 `json:"zoomRatio" description:"sheet缩放比例"`
  107. }
  108. // LuckySheetData sheet表格数据
  109. type LuckySheetData struct {
  110. Name string `json:"name" description:"工作表名称"`
  111. Index interface{} `json:"index" description:"工作表索引"`
  112. Row int `json:"row" description:"行数"`
  113. Column int `json:"column" description:"列数"`
  114. CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
  115. ChWidth int64 `json:"ch_width" description:"工作表区域的宽度"`
  116. Config LuckySheetDataConfig `json:"config" description:""`
  117. //Index int `json:"index" description:"工作表索引"`
  118. RhHeight float64 `json:"rh_height" description:"工作表区域的高度"`
  119. ScrollLeft float64 `json:"scrollLeft" description:"左右滚动条位置"`
  120. ScrollTop float64 `json:"scrollTop" description:"上下滚动条位置"`
  121. CalcChain []interface{} `json:"calcChain" description:"公式链"`
  122. Status interface{} `json:"status" description:"激活状态"`
  123. VisibleDataColumn []int64 `json:"visibledatacolumn" description:"所有列的位置信息,递增的列位置数据,初始化无需设置"`
  124. VisibleDataRow []int64 `json:"visibledatarow" description:"所有行的位置信息,递增的行位置数据,初始化无需设置"`
  125. ZoomRatio float64 `json:"zoomRatio" description:"sheet缩放比例"`
  126. }
  127. // LuckySheetDataConfig sheet表单的配置
  128. type LuckySheetDataConfig struct {
  129. BorderInfo []LuckySheetDataConfigBorderInfo `json:"borderInfo" description:"边框"`
  130. Colhidden map[string]int64 `json:"colhidden" description:"隐藏列,示例值:\"colhidden\":{\"30\":0,\"31\":0}"`
  131. Columnlen map[string]float64 `json:"columnlen" description:"每个单元格的列宽"`
  132. //CustomHeight struct {
  133. // Zero int64 `json:"0"`
  134. //} `json:"customHeight" description:""`
  135. //CustomWidth struct {
  136. // Two int64 `json:"2" description:""`
  137. //} `json:"customWidth" description:""`
  138. Merge map[string]LuckySheetDataConfigMerge `json:"merge" description:"合并单元格"`
  139. Rowlen map[string]float64 `json:"rowlen" description:"每个单元格的行高"`
  140. }
  141. // LuckySheetDataConfigMerge 合并单元格设置
  142. type LuckySheetDataConfigMerge struct {
  143. Row int `json:"r" description:"行数"`
  144. Column int `json:"c" description:"列数"`
  145. Rs int `json:"rs" description:"合并的行数"`
  146. Cs int `json:"cs" description:"合并的列数"`
  147. }
  148. // LuckySheetDataConfigBorderInfo 单元格边框信息
  149. type LuckySheetDataConfigBorderInfo struct {
  150. BorderType string `json:"borderType" description:"边框类型 border-left | border-right | border-top | border-bottom | border-all | border-outside | border-inside | border-horizontal | border-vertical | border-none"`
  151. Color string `json:"color" description:"边框颜色 color: 16进制颜色值"`
  152. Range []struct {
  153. Column []int64 `json:"column" description:"行"`
  154. Row []int64 `json:"row" description:"列"`
  155. } `json:"range" description:"选区范围 range: 行列信息数组"`
  156. RangeType string `json:"rangeType" description:"选区 rangeType: range | cell "`
  157. Style string `json:"style" description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ,和aspose.cells的getLineStyle()的值对应的话,需要自己做个转换,参考 aspose.cells"`
  158. Value LuckySheetDataConfigBorderInfoCellValue `json:"value" description:"" description:"范围类型分单个单元格的数据"`
  159. }
  160. // LuckySheetDataConfigBorderInfoCellValue 单元格边框信息(范围类型为:单个单元格)
  161. type LuckySheetDataConfigBorderInfoCellValue struct {
  162. B LuckySheetDataConfigBorderInfoCell `json:"b" description:"下边框"`
  163. L LuckySheetDataConfigBorderInfoCell `json:"l" description:"左边框"`
  164. R LuckySheetDataConfigBorderInfoCell `json:"r" description:"右边框"`
  165. T LuckySheetDataConfigBorderInfoCell `json:"t" description:"上边框"`
  166. ColIndex int64 `json:"col_index" description:"第几行"`
  167. RowIndex int64 `json:"row_index" description:"第几列"`
  168. }
  169. // LuckySheetDataConfigBorderInfoCell 单元格边框信息(cell类型)
  170. type LuckySheetDataConfigBorderInfoCell struct {
  171. Color string `json:"color" description:"边框颜色 color: 16进制颜色值"`
  172. Style int `description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ,和aspose.cells的getLineStyle()的值对应的话,需要自己做个转换,参考 aspose.cells"`
  173. Sl interface{} `json:"style" description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ,和aspose.cells的getLineStyle()的值对应的话,需要自己做个转换,参考 aspose.cells"`
  174. }
  175. // LuckySheetCellData 单个单元格数据
  176. type LuckySheetCellData struct {
  177. Col int64 `json:"c" description:"列"`
  178. Row int64 `json:"r" description:"行"`
  179. Value LuckySheetDataValue `json:"v" description:"单元格内值的数据"`
  180. }
  181. // LuckySheetDataValue 单元格内值的数据
  182. type LuckySheetDataValue struct {
  183. CellType LuckySheetDataCellType `json:"ct" description:"单元格值格式:文本、时间等 "`
  184. Value interface{} `json:"v" description:"原始值"`
  185. Monitor string `json:"m" description:"显示值"`
  186. Background string `json:"bg" description:"背景色,实例值:#fff000"`
  187. FontFamily int `description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体(Song)、6 黑体(ST Heiti)、7 楷体(ST Kaiti)、 8 仿宋(ST FangSong)、9 新宋体(ST Song)、10 华文新魏、11 华文行楷、12 华文隶书 "`
  188. FF interface{} `json:"ff" description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体(Song)、6 黑体(ST Heiti)、7 楷体(ST Kaiti)、 8 仿宋(ST FangSong)、9 新宋体(ST Song)、10 华文新魏、11 华文行楷、12 华文隶书 "`
  189. FontColor string `json:"fc" description:"字体颜色,示例值:#fff000" `
  190. Bold int `json:"bl" description:"粗体,0 常规 、 1加粗 "`
  191. Italic int `json:"it" description:"斜体,0 常规 、 1 斜体"`
  192. Fontsize int `description:"字体大小,14"`
  193. CancelLine int ` description:"删除线, 0 常规 、 1 删除线"`
  194. HorizontalType int `description:"水平对齐, 0 居中、1 左、2右"`
  195. VerticalType int ` description:"垂直对齐, 0 中间、1 上、2下"`
  196. Fs interface{} `json:"fs" description:"字体大小,14"`
  197. Cl interface{} `json:"cl" description:"删除线, 0 常规 、 1 删除线"`
  198. Ht interface{} `json:"ht" description:"水平对齐, 0 居中、1 左、2右"`
  199. Vt interface{} `json:"vt" description:"垂直对齐, 0 中间、1 上、2下"`
  200. //TextRotate string `json:"tr" description:"竖排文字, 3"`
  201. //RotateText string `json:"rt" description:"文字旋转角度, 介于0~180之间的整数,包含0和180"`
  202. TextBeak int `description:"文本换行, 0 截断、1溢出、2 自动换行"`
  203. Tb interface{} `json:"tb" description:"文本换行, 0 截断、1溢出、2 自动换行"`
  204. Ps LuckySheetDataCellComment `json:"ps" description:"批注"`
  205. Function string `json:"f" description:"公式"`
  206. MergeCell LuckySheetDataConfigMerge `json:"mc" description:"合并单元格信息"`
  207. }
  208. // LuckySheetDataCellType 单元格值格式:文本、时间等
  209. type LuckySheetDataCellType struct {
  210. Fa string `json:"fa" description:"格式名称,例如:“General”为自动格式"`
  211. T string `json:"t" description:"格式类型,例如:“n”为数字类型"`
  212. S []struct {
  213. FontFamily int `description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体(Song)、6 黑体(ST Heiti)、7 楷体(ST Kaiti)、 8 仿宋(ST FangSong)、9 新宋体(ST Song)、10 华文新魏、11 华文行楷、12 华文隶书 "`
  214. FF interface{} `json:"ff" description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体(Song)、6 黑体(ST Heiti)、7 楷体(ST Kaiti)、 8 仿宋(ST FangSong)、9 新宋体(ST Song)、10 华文新魏、11 华文行楷、12 华文隶书 "`
  215. FontColor string `json:"fc" description:"字体颜色,示例值:#fff000" `
  216. Fontsize int `description:"字体大小,14"`
  217. CancelLine int ` description:"删除线, 0 常规 、 1 删除线"`
  218. HorizontalType int `description:"水平对齐, 0 居中、1 左、2右"`
  219. VerticalType int `description:"垂直对齐, 0 中间、1 上、2下"`
  220. Fs interface{} `json:"fs" description:"字体大小,14"`
  221. Cl interface{} `json:"cl" description:"删除线, 0 常规 、 1 删除线"`
  222. Ht interface{} `json:"ht" description:"水平对齐, 0 居中、1 左、2右"`
  223. Vt interface{} `json:"vt" description:"垂直对齐, 0 中间、1 上、2下"`
  224. Un int `json:"un" description:""`
  225. Bold int `json:"bl" description:"粗体,0 常规 、 1加粗 "`
  226. Italic int `json:"it" description:"斜体,0 常规 、 1 斜体"`
  227. Value interface{} `json:"v" description:"原始值"`
  228. } `json:"s"`
  229. }
  230. // LuckySheetDataCellComment 批注
  231. type LuckySheetDataCellComment struct {
  232. Left int `json:"left" description:"批注框距离左边工作表边缘位置"`
  233. Top int `json:"top" description:"批注框距离上边工作表边缘位置"`
  234. Width int `json:"width" description:"批注框宽度"`
  235. Height int `json:"height" description:"批注框高度"`
  236. Value string `json:"value" description:"批注内容"`
  237. IsShow bool `json:"isshow" description:"是否显示批注"`
  238. }
  239. // GetLuckySheetData 获取LuckySheetData的结构体
  240. func GetLuckySheetData(jsonStr string) (item *LuckySheetData, err error) {
  241. err = json.Unmarshal([]byte(jsonStr), &item)
  242. for k, v := range item.CellData {
  243. value := v.Value
  244. value.Fontsize = getIntValueByInterface(value.Fs)
  245. value.CancelLine = getIntValueByInterface(value.Cl)
  246. value.HorizontalType = getIntValueByInterface(value.Ht)
  247. value.VerticalType = getIntValueByInterface(value.Vt)
  248. value.FontFamily = getIntValueByInterface(value.FF)
  249. value.TextBeak = getIntValueByInterface(value.Tb)
  250. if len(value.CellType.S) > 0 {
  251. for kk, vv := range value.CellType.S {
  252. vv.Fontsize = getIntValueByInterface(vv.Fs)
  253. vv.CancelLine = getIntValueByInterface(vv.Cl)
  254. vv.HorizontalType = getIntValueByInterface(vv.Ht)
  255. vv.VerticalType = getIntValueByInterface(vv.Vt)
  256. vv.FontFamily = getIntValueByInterface(vv.FF)
  257. value.CellType.S[kk] = vv
  258. }
  259. }
  260. item.CellData[k].Value = value
  261. }
  262. if len(item.Config.BorderInfo) > 0 {
  263. for k, v := range item.Config.BorderInfo {
  264. v.Value.T.Style = getIntValueByInterface(v.Value.T.Style)
  265. v.Value.B.Style = getIntValueByInterface(v.Value.B.Style)
  266. v.Value.L.Style = getIntValueByInterface(v.Value.L.Style)
  267. v.Value.R.Style = getIntValueByInterface(v.Value.R.Style)
  268. item.Config.BorderInfo[k] = v
  269. }
  270. }
  271. return
  272. }
  273. func getIntValueByInterface(valInterface interface{}) (val int) {
  274. if valInterface == nil {
  275. return
  276. }
  277. switch reflect.TypeOf(valInterface).Kind() {
  278. case reflect.String:
  279. tmpValue := reflect.ValueOf(valInterface).String()
  280. tmpValInt, err := strconv.Atoi(tmpValue)
  281. if err != nil {
  282. val = 0
  283. } else {
  284. val = tmpValInt
  285. }
  286. case reflect.Int, reflect.Int32, reflect.Int64:
  287. tmpValue := reflect.ValueOf(valInterface).Int()
  288. val = int(tmpValue)
  289. }
  290. return
  291. }
  292. // TableData 表格数据
  293. type TableData struct {
  294. TableDataList [][]LuckySheetDataValue
  295. RowWidthList []float64
  296. RowHeightList []float64
  297. RemoveTopRow int `description:"移除表格上方的行数"`
  298. RemoveBottomRow int `description:"移除表格下方的行数"`
  299. RemoveLeftColumn int `description:"移除表格左侧的列数"`
  300. RemoveRightColumn int `description:"移除表格右侧的列数"`
  301. MergeList []TableDataMerge `description:"合并数据列"`
  302. }
  303. // TableDataMerge 表格数据合并单元格配置
  304. type TableDataMerge struct {
  305. StartRowIndex int `json:"start_row_index" description:"开始的行下标"`
  306. StartColumnIndex int `json:"start_column" description:"开始的列下标"`
  307. MergeRowNum int `json:"merge_row_num" description:"合并的行数"`
  308. MergeColumnNum int `json:"merge_column_num" description:"合并的列数"`
  309. }
  310. // ToExcel 通过 TableData生成excel表格
  311. func (tableData TableData) ToExcel() (downloadFilePath string, err error) {
  312. //dir, err := os.Executable()
  313. //exPath := filepath.Dir(dir)
  314. //downloadFilePath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
  315. downloadFilePath, err = getDownloadPath()
  316. if err != nil {
  317. return
  318. }
  319. xlsxFile := xlsx.NewFile()
  320. if err != nil {
  321. return
  322. }
  323. // 将单个sheet的数据写入到excel
  324. err = tableData.WriteExcelSheetData(xlsxFile, "sheet1")
  325. if err != nil {
  326. return
  327. }
  328. //return
  329. err = xlsxFile.Save(downloadFilePath)
  330. if err != nil {
  331. return
  332. }
  333. //randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
  334. //downloadFileName := "即将到期客户数据_" + randStr + ".xlsx"
  335. //this.Ctx.Output.Download(downLoadnFilePath, downloadFileName)
  336. return
  337. }
  338. // WriteExcelSheetData 通过 TableData生成excel表格数据
  339. func (tableData TableData) WriteExcelSheetData(xlsxFile *xlsx.File, sheetName string) (err error) {
  340. style := xlsx.NewStyle()
  341. alignment := xlsx.Alignment{
  342. Horizontal: "center",
  343. Vertical: "center",
  344. WrapText: true,
  345. }
  346. style.Alignment = alignment
  347. style.ApplyAlignment = true
  348. sheet, err := xlsxFile.AddSheet(sheetName)
  349. if err != nil {
  350. return
  351. }
  352. for k, v := range tableData.RowWidthList {
  353. err = sheet.SetColWidth(k, k, v/10)
  354. if err != nil {
  355. return
  356. }
  357. }
  358. // 单元格高度配置列表
  359. lenRowHeight := len(tableData.RowHeightList)
  360. for index, v := range tableData.TableDataList {
  361. tmpRow := sheet.AddRow()
  362. //设置单元格高度
  363. if index < lenRowHeight && tableData.RowHeightList[index] > 0 {
  364. tmpRow.SetHeight(tableData.RowHeightList[index] / 2)
  365. }
  366. for _, cellInfo := range v {
  367. tmpStyle := new(xlsx.Style)
  368. //xlsx.Style{
  369. // Fill: xlsx.Fill{},
  370. // ApplyBorder: false,
  371. // ApplyFill: false,
  372. // ApplyFont: false,
  373. // NamedStyleIndex: nil,
  374. //}
  375. //fill := *NewFill("solid", "FF000000", "00FF0000")
  376. defaultFill := xlsx.DefaultFill()
  377. if cellInfo.Background != `` {
  378. defaultFill.PatternType = "solid"
  379. backgroundColor := cellInfo.Background
  380. // 这么做是为了避免传入的是RGB的格式(rgb(255, 255, 255))
  381. backgroundColor = getColor(backgroundColor)
  382. defaultFill.BgColor = strings.TrimPrefix(backgroundColor, "#")
  383. defaultFill.FgColor = strings.TrimPrefix(backgroundColor, "#")
  384. }
  385. tmpStyle.Fill = *defaultFill
  386. // 获取表格字体配置
  387. tmpStyle.Font = getExcelFontConf(cellInfo)
  388. //获取表格单元格排列配置
  389. tmpStyle.ApplyAlignment = true
  390. tmpStyle.Alignment = getExcelAlignmentConf(cellInfo)
  391. //边框配置
  392. tmpStyle.Border = xlsx.Border{
  393. Left: "thin",
  394. LeftColor: "000000",
  395. Right: "thin",
  396. RightColor: "000000",
  397. Top: "thin",
  398. TopColor: "000000",
  399. Bottom: "thin",
  400. BottomColor: "000000",
  401. }
  402. //tmpStyle.ApplyAlignment = true
  403. //tmpStyle.Alignment.WrapText = true
  404. tmpRow := tmpRow.AddCell()
  405. tmpRow.SetStyle(tmpStyle)
  406. valueStr := cellInfo.Monitor
  407. if valueStr == `` {
  408. //valueStr = fmt.Sprint(cellInfo.Value)
  409. if valueStr == `` && cellInfo.CellType.S != nil {
  410. //不是设置在单元格上面,而是设置在文本上
  411. for _, cellS := range cellInfo.CellType.S {
  412. valueStr += fmt.Sprint(cellS.Value)
  413. }
  414. }
  415. }
  416. //tmpRow.SetString(valueStr)
  417. switch cellInfo.CellType.Fa {
  418. case "General":
  419. if cellInfo.CellType.S != nil {
  420. tmpRow.SetString(valueStr)
  421. } else {
  422. tmpRow.SetValue(cellInfo.Value)
  423. }
  424. case "@":
  425. tmpRow.SetString(valueStr)
  426. default:
  427. tmpRow.SetString(valueStr)
  428. }
  429. if cellInfo.Function != `` {
  430. //xlsxFile.
  431. //xlsxFile.SetCellFormula
  432. tmpRow.SetFormula(cellInfo.Function)
  433. }
  434. //if cellInfo.Function != `` {
  435. // tmpRow.SetFormula(cellInfo.Function)
  436. //}
  437. }
  438. }
  439. for _, v := range tableData.MergeList {
  440. for k, cell := range sheet.Row(v.StartRowIndex).Cells {
  441. if v.StartColumnIndex == k {
  442. cell.Merge(v.MergeColumnNum, v.MergeRowNum)
  443. }
  444. }
  445. }
  446. return
  447. }
  448. // GetTableDataByLuckySheetDataStr 通过LuckySheet的string数据获取表格数据
  449. func (item *LuckySheetData) GetTableDataByLuckySheetDataStr(isRemoveBlankCell bool) (selfTableData TableData, err error) {
  450. luckySheetCellDataList := item.CellData
  451. // 表格数据
  452. tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
  453. // 最大行,最大列
  454. var maxRow, maxCol int64
  455. for _, v := range luckySheetCellDataList {
  456. //fmt.Println("row:", v.Row, "=====col:", v.Col)
  457. if v.Row > maxRow { //最大行
  458. maxRow = v.Row
  459. }
  460. if v.Col > maxCol { //最大列
  461. maxCol = v.Col
  462. }
  463. var tmpRow map[int64]LuckySheetDataValue
  464. tmpRow, ok := tableDataMap[v.Row]
  465. if ok {
  466. tmpRow[v.Col] = v.Value
  467. } else {
  468. tmpRow = make(map[int64]LuckySheetDataValue)
  469. tmpRow[v.Col] = v.Value
  470. }
  471. tableDataMap[v.Row] = tmpRow
  472. }
  473. tableDataList := make([][]LuckySheetDataValue, 0)
  474. var i int64
  475. // 单元格宽度
  476. configColumnConf := item.Config.Columnlen
  477. rowWidthMap := make(map[int]float64)
  478. rowWidthList := make([]float64, 0) //
  479. // 单元格高度
  480. configRowConf := item.Config.Rowlen
  481. rowHeightList := make([]float64, 0) //
  482. for i = 0; i <= maxRow; i++ {
  483. //列
  484. tmpTableColDataList := make([]LuckySheetDataValue, 0)
  485. // 每个单元格的高度
  486. tmpHeight, ok := configRowConf[fmt.Sprint(i)]
  487. if !ok {
  488. tmpHeight = 0
  489. }
  490. rowHeightList = append(rowHeightList, tmpHeight)
  491. tmpRowData, ok := tableDataMap[i]
  492. // 如果没有该行数据,那么就默认添加空行数据处理
  493. if !ok {
  494. tmpRowData = make(map[int64]LuckySheetDataValue)
  495. }
  496. var j int64
  497. for j = 0; j <= maxCol; j++ {
  498. tmpTableColData, ok := tmpRowData[j]
  499. if !ok {
  500. tmpTableColData = LuckySheetDataValue{}
  501. }
  502. //单元格显示的数据处理
  503. tmpTableColData.Monitor = handleCellVal(tmpTableColData)
  504. tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)
  505. // 每个单元格的宽度
  506. tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
  507. if !ok {
  508. tmpWidth = 0
  509. }
  510. rowIndex := len(tmpTableColDataList) - 1
  511. if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
  512. rowWidthList = append(rowWidthList, tmpWidth)
  513. }
  514. rowWidthMap[rowIndex] = tmpWidth
  515. }
  516. tableDataList = append(tableDataList, tmpTableColDataList)
  517. }
  518. tableDataMergeList := make([]TableDataMerge, 0)
  519. tableRemoveNum := TableRemoveNum{}
  520. // 数据处理,移除上下左右空行空列
  521. if isRemoveBlankCell {
  522. tableDataList, tableRemoveNum, rowHeightList, rowWidthList, tableDataMergeList = handleTableDataList(tableDataList, item.Config.Merge, rowHeightList, rowWidthList)
  523. }
  524. // 表格数据
  525. {
  526. // 移除空白单元格信息
  527. selfTableData.RemoveTopRow = tableRemoveNum.RemoveTopRow
  528. selfTableData.RemoveBottomRow = tableRemoveNum.RemoveBottomRow
  529. selfTableData.RemoveLeftColumn = tableRemoveNum.RemoveLeftColumn
  530. selfTableData.RemoveRightColumn = tableRemoveNum.RemoveRightColumn
  531. }
  532. selfTableData.TableDataList = tableDataList
  533. selfTableData.RowWidthList = rowWidthList
  534. selfTableData.RowHeightList = rowHeightList
  535. selfTableData.MergeList = tableDataMergeList
  536. return
  537. }
  538. // TableRemoveNum 上下左右移除的空行空列数量
  539. type TableRemoveNum struct {
  540. RemoveTopRow int `description:"移除表格上方的行数"`
  541. RemoveBottomRow int `description:"移除表格下方的行数"`
  542. RemoveLeftColumn int `description:"移除表格左侧的列数"`
  543. RemoveRightColumn int `description:"移除表格右侧的列数"`
  544. }
  545. // handleTableDataList 表格数据处理(移除上下左右的空行空列)
  546. func handleTableDataList(tableDataList [][]LuckySheetDataValue, luckySheetDataConfigMergeList map[string]LuckySheetDataConfigMerge, rowHeightList, rowWidthList []float64) ([][]LuckySheetDataValue, TableRemoveNum, []float64, []float64, []TableDataMerge) {
  547. var removeTopRow, removeBottomRow, removeLeftColumn, removeRightColumn int //上下左右需要移除的空行空列
  548. tableDataMergeList := make([]TableDataMerge, 0) //待合并的单元格信息
  549. //总共多少行
  550. lenRow := len(tableDataList)
  551. //移除上下空行
  552. deleteRowIndexList := make([]int, 0)
  553. if lenRow > 0 {
  554. var flag = false
  555. // 移除上方空列
  556. for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
  557. isDelete := true
  558. for _, v := range tableDataList[rowIndex] {
  559. if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
  560. isDelete = false
  561. flag = true
  562. break
  563. }
  564. }
  565. if flag {
  566. break
  567. }
  568. if isDelete {
  569. removeTopRow++
  570. deleteRowIndexList = append(deleteRowIndexList, rowIndex)
  571. }
  572. }
  573. // 移除下方空行
  574. flag = false
  575. //尾部
  576. deleteBottomRowIndexList := make([]int, 0)
  577. // 数据第1行不处理
  578. for rowIndex := lenRow - 1; rowIndex > removeTopRow; rowIndex-- {
  579. isDelete := true
  580. for _, v := range tableDataList[rowIndex] {
  581. if v.Monitor != `` {
  582. isDelete = false
  583. flag = true
  584. break
  585. }
  586. }
  587. if flag {
  588. break
  589. }
  590. if isDelete {
  591. deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
  592. removeBottomRow++
  593. }
  594. }
  595. if len(deleteBottomRowIndexList) > 0 {
  596. deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
  597. }
  598. lenDeleteRow := len(deleteRowIndexList)
  599. if lenDeleteRow > 0 {
  600. for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
  601. //表格数据
  602. tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
  603. //表格高度
  604. rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
  605. }
  606. }
  607. }
  608. //移除左右空列
  609. deleteColumnIndexList := make([]int, 0)
  610. if len(tableDataList) > 0 {
  611. var flag = false
  612. lenColumn := len(tableDataList[0])
  613. // 移除左边空列
  614. for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
  615. isDelete := true
  616. for _, v := range tableDataList {
  617. //如果一列都没有,说明是上面几行是空行,没有数据
  618. if len(v) <= 0 {
  619. continue
  620. }
  621. if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
  622. isDelete = false
  623. flag = true
  624. break
  625. }
  626. }
  627. if flag {
  628. break
  629. }
  630. if isDelete {
  631. removeLeftColumn++
  632. deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
  633. }
  634. }
  635. // 移除右方空列
  636. flag = false
  637. //右边
  638. deleteTailColumnIndexList := make([]int, 0)
  639. // 数据第1列不处理
  640. for columnIndex := lenColumn - 1; columnIndex > removeLeftColumn; columnIndex-- {
  641. isDelete := true
  642. for _, v := range tableDataList {
  643. //如果一列都没有,说明是上面几行是空行,没有数据
  644. if len(v) <= 0 {
  645. continue
  646. }
  647. if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
  648. isDelete = false
  649. flag = true
  650. break
  651. }
  652. }
  653. if flag {
  654. break
  655. }
  656. if isDelete {
  657. deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
  658. removeRightColumn++
  659. }
  660. }
  661. if len(deleteTailColumnIndexList) > 0 {
  662. deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
  663. }
  664. lenDeleteColumn := len(deleteColumnIndexList)
  665. if lenDeleteColumn > 0 {
  666. for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
  667. //表格数据
  668. for k, v := range tableDataList {
  669. tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
  670. }
  671. //表格宽度
  672. rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
  673. }
  674. }
  675. }
  676. //处理合并单元格
  677. for _, v := range luckySheetDataConfigMergeList {
  678. indexRow := v.Row - removeTopRow
  679. indexColumn := v.Column - removeLeftColumn
  680. tableDataMerge := TableDataMerge{
  681. StartRowIndex: indexRow,
  682. StartColumnIndex: indexColumn,
  683. MergeRowNum: v.Rs - 1,
  684. MergeColumnNum: v.Cs - 1,
  685. }
  686. tableDataMergeList = append(tableDataMergeList, tableDataMerge)
  687. }
  688. return tableDataList, TableRemoveNum{
  689. RemoveTopRow: removeTopRow,
  690. RemoveBottomRow: removeBottomRow,
  691. RemoveLeftColumn: removeLeftColumn,
  692. RemoveRightColumn: removeRightColumn,
  693. }, rowHeightList, rowWidthList, tableDataMergeList
  694. }
  695. // ToExcel 通过 luckySheetData生成excel表格
  696. func (item *LuckySheetData) ToExcel() (downloadFilePath string, err error) {
  697. tableData, err := item.GetTableDataByLuckySheetDataStr(true)
  698. if err != nil {
  699. return
  700. }
  701. downloadFilePath, err = tableData.ToExcel()
  702. return
  703. }
  704. // getDownloadPath 获取下载地址
  705. func getDownloadPath() (fpath string, err error) {
  706. dateDir := time.Now().Format("20060102")
  707. uploadDir := "static/xls/" + dateDir
  708. err = os.MkdirAll(uploadDir, utils.DIR_MOD)
  709. if err != nil {
  710. return
  711. }
  712. randStr := utils.GetRandStringNoSpecialChar(28)
  713. fileName := randStr + ".xlsx"
  714. fpath = uploadDir + "/" + fileName
  715. return
  716. }
  717. // getExcelFontConf 获取表格字体配置
  718. func getExcelFontConf(cellInfo LuckySheetDataValue) xlsx.Font {
  719. fontSize := 10
  720. familyName := ``
  721. var isBold, isItalic, isUnderline bool
  722. // 字体
  723. {
  724. if cellInfo.Bold == 1 {
  725. isBold = true
  726. }
  727. if cellInfo.Italic == 1 {
  728. isItalic = true
  729. }
  730. if cellInfo.CancelLine == 1 {
  731. isUnderline = true
  732. }
  733. //if cellInfo.Fontsize != `` {
  734. // convSize, _ := strconv.Atoi(cellInfo.Fontsize)
  735. // if convSize > 0 {
  736. // fontSize = convSize
  737. // }
  738. //}
  739. if cellInfo.Fontsize > 0 {
  740. fontSize = cellInfo.Fontsize
  741. }
  742. tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
  743. if !familyNameOk {
  744. tmpFamilyName = "宋体"
  745. }
  746. familyName = tmpFamilyName
  747. }
  748. return xlsx.Font{
  749. Size: fontSize,
  750. Name: familyName,
  751. //Family: v2.FontFamily,
  752. //Charset: 0,
  753. Color: strings.TrimPrefix(getColor(cellInfo.FontColor), "#"),
  754. Bold: isBold,
  755. Italic: isItalic,
  756. Underline: isUnderline,
  757. }
  758. }
  759. // getExcelAlignmentConf 获取表格单元格排列配置
  760. func getExcelAlignmentConf(cellInfo LuckySheetDataValue) xlsx.Alignment {
  761. //自动换行
  762. isWrapText := false
  763. if cellInfo.TextBeak == 2 {
  764. isWrapText = true
  765. }
  766. rotateText := 0
  767. //if cellInfo.RotateText != `` {
  768. // convSize, _ := strconv.Atoi(cellInfo.RotateText)
  769. // if convSize > 0 {
  770. // rotateText = convSize
  771. // }
  772. //}
  773. return xlsx.Alignment{
  774. Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
  775. //Indent: 0,
  776. //ShrinkToFit: false,
  777. TextRotation: rotateText,
  778. Vertical: LuckyVerticalMap[cellInfo.VerticalType],
  779. WrapText: isWrapText,
  780. }
  781. }
  782. // getExcelBorderConf 获取表格边框配置
  783. //func getExcelBorderConf(list []LuckySheetDataConfigBorderInfo) (v xlsx.Border) {
  784. // // 字体
  785. // for _, v := range list {
  786. // //v.BorderType
  787. // //v.Style
  788. //
  789. // if v.RangeType == "cell" {
  790. //
  791. // xlsx.Border{
  792. // Left: LuckyBorderMap[v.Value.L.Style],
  793. // LeftColor: strings.TrimPrefix(v.Value.L.Color, "#"),
  794. // Right: LuckyBorderMap[v.Value.R.Style],
  795. // RightColor: strings.TrimPrefix(v.Value.R.Color, "#"),
  796. // Top: LuckyBorderMap[v.Value.T.Style],
  797. // TopColor: strings.TrimPrefix(v.Value.T.Color, "#"),
  798. // Bottom: LuckyBorderMap[v.Value.B.Style],
  799. // BottomColor: strings.TrimPrefix(v.Value.B.Color, "#"),
  800. // }
  801. // } else {
  802. // xlsx.Border{
  803. // Left: LuckyBorderMap[v.Style],
  804. // LeftColor: strings.TrimPrefix(v.Color, "#"),
  805. // Right: LuckyBorderMap[v.Style],
  806. // RightColor: strings.TrimPrefix(v.Color, "#"),
  807. // Top: LuckyBorderMap[v.Style],
  808. // TopColor: strings.TrimPrefix(v.Color, "#"),
  809. // Bottom: LuckyBorderMap[v.Style],
  810. // BottomColor: strings.TrimPrefix(v.Color, "#"),
  811. // }
  812. // }
  813. // //v.Value.T.Style
  814. //
  815. // }
  816. //
  817. // return
  818. //}
  819. // LuckyFontFamilyMap 字体map
  820. var LuckyFontFamilyMap = map[int]string{
  821. 0: "Times New Roman",
  822. 1: "Arial",
  823. 2: "Tahoma",
  824. 3: "Verdana",
  825. 4: "微软雅黑",
  826. 5: "宋体", //宋体(Song)、
  827. 6: "黑体", // 黑体(ST Heiti)
  828. 7: "楷体", //楷体(ST Kaiti),
  829. 8: "仿宋", //仿宋(ST FangSong),
  830. 9: "新宋体", //新宋体(ST Song),
  831. 10: "华文新魏",
  832. 11: "华文行楷",
  833. 12: "华文隶书",
  834. }
  835. // LuckyBorderMap 边框map
  836. var LuckyBorderMap = map[string]string{
  837. "": "Thin",
  838. "1": "Thin",
  839. "2": "Hair",
  840. "3": "Dotted",
  841. "4": "Dashed",
  842. "5": "DashDot", //宋体(Song)、
  843. "6": "DashDotDot", // 黑体(ST Heiti)
  844. "7": "Double", //楷体(ST Kaiti),
  845. "8": "Medium", //仿宋(ST FangSong),
  846. "9": "MediumDashed", //新宋体(ST Song),
  847. "10": "MediumDashDot",
  848. "11": "MediumDashDotDot",
  849. "12": "SlantedDashDot",
  850. "13": "Thick",
  851. }
  852. // LuckyHorizontalMap 水平对齐map
  853. var LuckyHorizontalMap = map[int]string{
  854. //"": "general",
  855. 0: "center",
  856. 1: "left",
  857. 2: "right",
  858. }
  859. // LuckyVerticalMap 垂直对齐map
  860. var LuckyVerticalMap = map[int]string{
  861. //"": "center",
  862. 0: "center",
  863. 1: "top",
  864. //2: "bottom",
  865. }
  866. // GetColumnName Return the ColumnName
  867. func GetColumnName(rowNumber int, colNumber int) (colName string, err error) {
  868. if rowNumber < 1 || colNumber < 1 {
  869. err = errors.New("Row and Column Number can not be less than 1")
  870. return
  871. }
  872. dividend := colNumber
  873. var modulo int
  874. for dividend > 0 {
  875. modulo = (dividend - 1) % 26
  876. // Convert int to char
  877. colName = string(65+modulo) + colName
  878. dividend = (int)((dividend - modulo) / 26)
  879. }
  880. colName = colName + strconv.Itoa(rowNumber)
  881. return
  882. }
  883. // GetTableDataByLuckySheetDataStrBak 通过LuckySheet的string数据获取表格数据(备份:2022-08-23 10:30:32)
  884. func (item *LuckySheetData) GetTableDataByLuckySheetDataStrBak() (selfTableData TableData, err error) {
  885. luckySheetCellDataList := item.CellData
  886. // 表格数据
  887. tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
  888. // 最大行,最大列
  889. var maxRow, maxCol int64
  890. for _, v := range luckySheetCellDataList {
  891. //fmt.Println("row:", v.Row, "=====col:", v.Col)
  892. if v.Row > maxRow { //最大行
  893. maxRow = v.Row
  894. }
  895. if v.Col > maxCol { //最大列
  896. maxCol = v.Col
  897. }
  898. var tmpRow map[int64]LuckySheetDataValue
  899. tmpRow, ok := tableDataMap[v.Row]
  900. if ok {
  901. tmpRow[v.Col] = v.Value
  902. } else {
  903. tmpRow = make(map[int64]LuckySheetDataValue)
  904. tmpRow[v.Col] = v.Value
  905. }
  906. tableDataMap[v.Row] = tmpRow
  907. }
  908. tableDataList := make([][]LuckySheetDataValue, 0)
  909. var i int64
  910. // 单元格宽度
  911. configColumnConf := item.Config.Columnlen
  912. rowWidthMap := make(map[int]float64)
  913. rowWidthList := make([]float64, 0) //
  914. // 单元格高度
  915. configRowConf := item.Config.Rowlen
  916. rowHeightList := make([]float64, 0) //
  917. for i = 0; i <= maxRow; i++ {
  918. //列
  919. tmpTableColDataList := make([]LuckySheetDataValue, 0)
  920. // 每个单元格的高度
  921. tmpHeight, ok := configRowConf[fmt.Sprint(i)]
  922. if !ok {
  923. tmpHeight = 0
  924. }
  925. rowHeightList = append(rowHeightList, tmpHeight)
  926. tmpRowData, ok := tableDataMap[i]
  927. // 如果没有该行数据,那么就默认添加空行数据处理
  928. if !ok {
  929. tmpRowData = make(map[int64]LuckySheetDataValue)
  930. }
  931. var j int64
  932. for j = 0; j <= maxCol; j++ {
  933. tmpTableColData, ok := tmpRowData[j]
  934. if !ok {
  935. tmpTableColData = LuckySheetDataValue{}
  936. }
  937. tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)
  938. // 每个单元格的宽度
  939. tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
  940. if !ok {
  941. tmpWidth = 0
  942. }
  943. rowIndex := len(tmpTableColDataList) - 1
  944. if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
  945. rowWidthList = append(rowWidthList, tmpWidth)
  946. }
  947. rowWidthMap[rowIndex] = tmpWidth
  948. }
  949. tableDataList = append(tableDataList, tmpTableColDataList)
  950. }
  951. //总共多少行
  952. lenRow := len(tableDataList)
  953. //移除上下空行
  954. deleteRowIndexList := make([]int, 0)
  955. if lenRow > 0 {
  956. var flag = false
  957. // 移除上方空列
  958. for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
  959. isDelete := true
  960. for _, v := range tableDataList[rowIndex] {
  961. if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
  962. isDelete = false
  963. flag = true
  964. break
  965. }
  966. }
  967. if flag {
  968. break
  969. }
  970. if isDelete {
  971. selfTableData.RemoveTopRow++
  972. deleteRowIndexList = append(deleteRowIndexList, rowIndex)
  973. }
  974. }
  975. // 移除下方空行
  976. flag = false
  977. //尾部
  978. deleteBottomRowIndexList := make([]int, 0)
  979. for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
  980. isDelete := true
  981. for _, v := range tableDataList[rowIndex] {
  982. if v.Monitor != `` {
  983. isDelete = false
  984. flag = true
  985. break
  986. }
  987. }
  988. if flag {
  989. break
  990. }
  991. if isDelete {
  992. deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
  993. selfTableData.RemoveBottomRow++
  994. }
  995. }
  996. if len(deleteBottomRowIndexList) > 0 {
  997. deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
  998. }
  999. lenDeleteRow := len(deleteRowIndexList)
  1000. if lenDeleteRow > 0 {
  1001. for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
  1002. //表格数据
  1003. tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
  1004. //表格高度
  1005. rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
  1006. }
  1007. }
  1008. }
  1009. //移除左右空列
  1010. deleteColumnIndexList := make([]int, 0)
  1011. if len(tableDataList) > 0 {
  1012. var flag = false
  1013. lenColumn := len(tableDataList[0])
  1014. // 移除左边空列
  1015. for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
  1016. isDelete := true
  1017. for _, v := range tableDataList {
  1018. //如果一列都没有,说明是上面几行是空行,没有数据
  1019. if len(v) <= 0 {
  1020. continue
  1021. }
  1022. if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
  1023. isDelete = false
  1024. flag = true
  1025. break
  1026. }
  1027. }
  1028. if flag {
  1029. break
  1030. }
  1031. if isDelete {
  1032. selfTableData.RemoveLeftColumn++
  1033. deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
  1034. }
  1035. }
  1036. // 移除右方空列
  1037. flag = false
  1038. //右边
  1039. deleteTailColumnIndexList := make([]int, 0)
  1040. for columnIndex := lenColumn - 1; columnIndex >= 0; columnIndex-- {
  1041. isDelete := true
  1042. for _, v := range tableDataList {
  1043. //如果一列都没有,说明是上面几行是空行,没有数据
  1044. if len(v) <= 0 {
  1045. continue
  1046. }
  1047. if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
  1048. isDelete = false
  1049. flag = true
  1050. break
  1051. }
  1052. }
  1053. if flag {
  1054. break
  1055. }
  1056. if isDelete {
  1057. deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
  1058. selfTableData.RemoveRightColumn++
  1059. }
  1060. }
  1061. if len(deleteTailColumnIndexList) > 0 {
  1062. deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
  1063. }
  1064. lenDeleteColumn := len(deleteColumnIndexList)
  1065. if lenDeleteColumn > 0 {
  1066. for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
  1067. //表格数据
  1068. for k, v := range tableDataList {
  1069. tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
  1070. }
  1071. //表格宽度
  1072. rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
  1073. }
  1074. }
  1075. }
  1076. //处理合并单元格
  1077. tableDataMergeList := make([]TableDataMerge, 0)
  1078. for _, v := range item.Config.Merge {
  1079. indexRow := v.Row - selfTableData.RemoveTopRow
  1080. indexColumn := v.Column - selfTableData.RemoveLeftColumn
  1081. tableDataMerge := TableDataMerge{
  1082. StartRowIndex: indexRow,
  1083. StartColumnIndex: indexColumn,
  1084. MergeRowNum: v.Rs - 1,
  1085. MergeColumnNum: v.Cs - 1,
  1086. }
  1087. tableDataMergeList = append(tableDataMergeList, tableDataMerge)
  1088. }
  1089. // 表格数据
  1090. selfTableData.TableDataList = tableDataList
  1091. selfTableData.RowWidthList = rowWidthList
  1092. selfTableData.RowHeightList = rowHeightList
  1093. selfTableData.MergeList = tableDataMergeList
  1094. return
  1095. }
  1096. func (item *LuckySheetData) ToExcel2() (downloadFilePath string, err error) {
  1097. tableData, err := item.GetTableDataByLuckySheetDataStr(true)
  1098. downloadFilePath, err = getDownloadPath()
  1099. if err != nil {
  1100. return
  1101. }
  1102. f := excelize.NewFile()
  1103. // Create a new sheet.
  1104. sheetName := `Sheet1`
  1105. sheetIndex, err := f.NewSheet(sheetName)
  1106. if err != nil {
  1107. return
  1108. }
  1109. //设置列宽度
  1110. for k, v := range tableData.RowWidthList {
  1111. colName, tmpErr := excelize.ColumnNumberToName(k + 1)
  1112. if tmpErr != nil {
  1113. err = errors.New("获取单元格列名失败,ERR:" + tmpErr.Error())
  1114. err = tmpErr
  1115. return
  1116. }
  1117. err = f.SetColWidth(sheetName, colName, colName, v/10)
  1118. if err != nil {
  1119. err = errors.New("设置列宽度,ERR:" + tmpErr.Error())
  1120. return
  1121. }
  1122. }
  1123. // 写入数据
  1124. for index, v := range tableData.TableDataList {
  1125. //设置单元格高度
  1126. if tableData.RowHeightList[index] > 0 {
  1127. tmpErr := f.SetRowHeight(sheetName, index+1, tableData.RowHeightList[index]/2)
  1128. if tmpErr != nil {
  1129. err = errors.New("设置单元格高度失败,ERR:" + tmpErr.Error())
  1130. return
  1131. }
  1132. }
  1133. for colunmIndex, cellInfo := range v {
  1134. //f.NewStyle
  1135. defaultFill := excelize.Fill{
  1136. //Type: "solid",
  1137. Pattern: 1,
  1138. //Color: []string{cellInfo.Background},
  1139. Shading: 0,
  1140. }
  1141. if cellInfo.Background != `` {
  1142. defaultFill.Type = "solid"
  1143. defaultFill.Color = []string{cellInfo.Background}
  1144. }
  1145. cellStyle := &excelize.Style{
  1146. Border: []excelize.Border{
  1147. {Type: "left", Color: "000000", Style: 1},
  1148. {Type: "top", Color: "000000", Style: 1},
  1149. {Type: "bottom", Color: "000000", Style: 1},
  1150. {Type: "right", Color: "000000", Style: 1},
  1151. //{Type: "diagonalDown", Color: "A020F0", Style: 7},
  1152. //{Type: "diagonalUp", Color: "A020F0", Style: 8},
  1153. },
  1154. Fill: defaultFill,
  1155. Font: getExcelizeFontConf(cellInfo),
  1156. Alignment: getExcelizeAlignmentConf(cellInfo),
  1157. //Protection: nil,
  1158. //NumFmt: 0,
  1159. //DecimalPlaces: 0,
  1160. //CustomNumFmt: nil,
  1161. //Lang: "",
  1162. //NegRed: false,
  1163. }
  1164. cellStyleId, tmpErr := f.NewStyle(cellStyle)
  1165. if tmpErr != nil {
  1166. err = errors.New("new Style 失败,ERR:" + tmpErr.Error())
  1167. return
  1168. }
  1169. columnName, tmpErr := excelize.ColumnNumberToName(colunmIndex + 1)
  1170. if tmpErr != nil {
  1171. err = errors.New("获取单元格列名失败,ERR:" + tmpErr.Error())
  1172. return
  1173. }
  1174. cellName := columnName + strconv.Itoa(index+1)
  1175. tmpErr = f.SetCellStyle(sheetName, cellName, cellName, cellStyleId)
  1176. if tmpErr != nil {
  1177. err = errors.New("设置style失败,ERR:" + tmpErr.Error())
  1178. return
  1179. }
  1180. valueStr := cellInfo.Monitor
  1181. if valueStr == `` {
  1182. //valueStr = fmt.Sprint(cellInfo.Value)
  1183. if valueStr == `` && cellInfo.CellType.S != nil {
  1184. //不是设置在单元格上面,而是设置在文本上
  1185. for _, cellS := range cellInfo.CellType.S {
  1186. valueStr += fmt.Sprint(cellS.Value)
  1187. }
  1188. }
  1189. }
  1190. tmpErr = f.SetCellStr(sheetName, cellName, valueStr)
  1191. if tmpErr != nil {
  1192. err = errors.New("设置单元格数据失败,ERR:" + tmpErr.Error())
  1193. return
  1194. }
  1195. }
  1196. }
  1197. for _, v := range tableData.MergeList {
  1198. //开始列
  1199. startColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + 1)
  1200. if tmpErr != nil {
  1201. err = tmpErr
  1202. return
  1203. }
  1204. startCellName := startColumnName + strconv.Itoa(v.StartRowIndex+1)
  1205. //结束列
  1206. endColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + v.MergeColumnNum + 1)
  1207. if tmpErr != nil {
  1208. err = tmpErr
  1209. return
  1210. }
  1211. endCellName := endColumnName + strconv.Itoa(v.StartRowIndex+v.MergeRowNum+1)
  1212. err = f.MergeCell(sheetName, startCellName, endCellName)
  1213. if err != nil {
  1214. return
  1215. }
  1216. }
  1217. // Set active sheet of the workbook.
  1218. f.SetActiveSheet(sheetIndex)
  1219. // Save spreadsheet by the given path.
  1220. err = f.SaveAs(downloadFilePath)
  1221. return
  1222. }
  1223. // getExcelizeFontConf 获取表格字体配置
  1224. func getExcelizeFontConf(cellInfo LuckySheetDataValue) *excelize.Font {
  1225. fontSize := 10
  1226. familyName := ``
  1227. //single 单线 、double 双线
  1228. underlineStyle := ``
  1229. var isBold, isItalic, isUnderline bool
  1230. // 字体
  1231. {
  1232. if cellInfo.Bold == 1 {
  1233. isBold = true
  1234. }
  1235. if cellInfo.Italic == 1 {
  1236. isItalic = true
  1237. }
  1238. if cellInfo.CancelLine == 1 {
  1239. isUnderline = true
  1240. }
  1241. //if cellInfo.Fontsize != `` {
  1242. // convSize, _ := strconv.Atoi(cellInfo.Fontsize)
  1243. // if convSize > 0 {
  1244. // fontSize = convSize
  1245. // }
  1246. //}
  1247. if cellInfo.Fontsize > 0 {
  1248. fontSize = cellInfo.Fontsize
  1249. }
  1250. tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
  1251. if !familyNameOk {
  1252. tmpFamilyName = "宋体"
  1253. }
  1254. familyName = tmpFamilyName
  1255. }
  1256. if isUnderline {
  1257. underlineStyle = "single"
  1258. }
  1259. //VertAlign取值:
  1260. //BASELINE、superscript、subscript
  1261. return &excelize.Font{
  1262. Bold: isBold,
  1263. Italic: isItalic,
  1264. Underline: underlineStyle,
  1265. Family: familyName,
  1266. Size: float64(fontSize),
  1267. Strike: isUnderline,
  1268. Color: cellInfo.FontColor,
  1269. VertAlign: "",
  1270. }
  1271. }
  1272. // getExcelizeAlignmentConf 获取表格单元格排列配置
  1273. func getExcelizeAlignmentConf(cellInfo LuckySheetDataValue) *excelize.Alignment {
  1274. //自动换行
  1275. isWrapText := false
  1276. if cellInfo.TextBeak == 2 {
  1277. isWrapText = true
  1278. }
  1279. rotateText := 0
  1280. //if cellInfo.RotateText != `` {
  1281. // convSize, _ := strconv.Atoi(cellInfo.RotateText)
  1282. // if convSize > 0 {
  1283. // rotateText = convSize
  1284. // }
  1285. //}
  1286. return &excelize.Alignment{
  1287. Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
  1288. //Indent: 0,
  1289. //JustifyLastLine: false,
  1290. //ReadingOrder: 0,
  1291. //RelativeIndent: 0,
  1292. //ShrinkToFit: false,
  1293. TextRotation: rotateText,
  1294. Vertical: LuckyVerticalMap[cellInfo.VerticalType],
  1295. WrapText: isWrapText,
  1296. }
  1297. }
  1298. // getColor 获取hex颜色
  1299. func getColor(bgStr string) string {
  1300. isRgb := strings.HasPrefix(bgStr, "rgb")
  1301. re := regexp.MustCompile(`\(([^)]+)\)`)
  1302. matches := re.FindStringSubmatch(bgStr)
  1303. if len(matches) != 2 {
  1304. return bgStr
  1305. }
  1306. if isRgb {
  1307. arr := strings.Split(matches[1], ",")
  1308. if len(arr) < 3 {
  1309. return bgStr
  1310. }
  1311. rgbArr := make([]int64, 0)
  1312. for i, v := range arr {
  1313. if i >= 3 {
  1314. continue
  1315. }
  1316. tmpInt, err := strconv.Atoi(utils.TrimStr(v))
  1317. if err != nil {
  1318. return bgStr
  1319. }
  1320. rgbArr = append(rgbArr, int64(tmpInt))
  1321. }
  1322. rgb := RGB{
  1323. rgbArr[0],
  1324. rgbArr[1],
  1325. rgbArr[2],
  1326. }
  1327. bgStr = "" + rgb.Rgb2Hex().Str
  1328. }
  1329. return bgStr
  1330. }
  1331. // GetTableDataByCustomData 通过自定义表格数据获取表格数据
  1332. func GetTableDataByCustomData(excelType int, data request.TableDataReq, lang string) (selfTableData TableData, err error) {
  1333. tableDataList := make([][]LuckySheetDataValue, 0)
  1334. mergeList := make([]TableDataMerge, 0)
  1335. // 指标数
  1336. lenEdb := len(data.Data)
  1337. if lenEdb <= 0 {
  1338. return
  1339. }
  1340. // 日期数
  1341. lenCol := len(data.Data[0].Data)
  1342. if lenCol <= 0 {
  1343. return
  1344. }
  1345. // 指标列:1;指标行:2
  1346. if excelType == 1 {
  1347. mergeList = append(mergeList, TableDataMerge{
  1348. StartRowIndex: 0,
  1349. StartColumnIndex: 0,
  1350. MergeRowNum: 1,
  1351. MergeColumnNum: 0,
  1352. })
  1353. // 第一行
  1354. {
  1355. firstCol := make([]LuckySheetDataValue, 0)
  1356. dateStr := "日期"
  1357. switch lang {
  1358. case utils.EnLangVersion:
  1359. dateStr = "Date"
  1360. }
  1361. firstCol = append(firstCol, LuckySheetDataValue{
  1362. Value: dateStr,
  1363. Monitor: dateStr,
  1364. MergeCell: LuckySheetDataConfigMerge{
  1365. Row: 0, //行数
  1366. Column: 0, //列数
  1367. Rs: 2, //合并的行数
  1368. Cs: 1, //合并的列数
  1369. },
  1370. })
  1371. for _, v := range data.Data {
  1372. edbName := v.EdbName
  1373. if v.EdbAliasName != `` {
  1374. edbName = v.EdbAliasName
  1375. } else {
  1376. switch lang {
  1377. case utils.EnLangVersion:
  1378. edbName = v.EdbNameEn
  1379. }
  1380. }
  1381. firstCol = append(firstCol, LuckySheetDataValue{
  1382. Value: edbName,
  1383. Monitor: edbName,
  1384. MergeCell: LuckySheetDataConfigMerge{},
  1385. })
  1386. }
  1387. tableDataList = append(tableDataList, firstCol)
  1388. }
  1389. // 第二行
  1390. {
  1391. secondCol := make([]LuckySheetDataValue, 0)
  1392. secondCol = append(secondCol, LuckySheetDataValue{})
  1393. for _, v := range data.Data {
  1394. var name string
  1395. switch lang {
  1396. case utils.EnLangVersion:
  1397. name = v.UnitEn + " / " + utils.GetFrequencyEn(v.Frequency)
  1398. default:
  1399. name = v.Unit + " / " + v.Frequency
  1400. }
  1401. secondCol = append(secondCol, LuckySheetDataValue{
  1402. Value: name,
  1403. Monitor: name,
  1404. MergeCell: LuckySheetDataConfigMerge{},
  1405. })
  1406. }
  1407. tableDataList = append(tableDataList, secondCol)
  1408. }
  1409. // 开始数据了
  1410. {
  1411. for i := 0; i < lenCol; i++ {
  1412. dataCol := make([]LuckySheetDataValue, 0)
  1413. // 日期
  1414. dataCol = append(dataCol, LuckySheetDataValue{
  1415. Value: data.Data[0].Data[i].DataTime,
  1416. Monitor: data.Data[0].Data[i].DataTime,
  1417. MergeCell: LuckySheetDataConfigMerge{},
  1418. })
  1419. // 数据值
  1420. for _, v := range data.Data {
  1421. background := ``
  1422. if v.Data[i].DataType == 5 {
  1423. background = "#ffefdd"
  1424. }
  1425. dataCol = append(dataCol, LuckySheetDataValue{
  1426. Value: v.Data[i].Value,
  1427. Monitor: v.Data[i].ShowValue,
  1428. MergeCell: LuckySheetDataConfigMerge{},
  1429. Background: background,
  1430. })
  1431. }
  1432. tableDataList = append(tableDataList, dataCol)
  1433. }
  1434. }
  1435. // 开始文本行了
  1436. {
  1437. for _, textColList := range data.TextRowData {
  1438. dataCol := make([]LuckySheetDataValue, 0)
  1439. for _, v := range textColList {
  1440. dataCol = append(dataCol, LuckySheetDataValue{
  1441. Value: v.Value,
  1442. Monitor: v.ShowValue,
  1443. MergeCell: LuckySheetDataConfigMerge{},
  1444. })
  1445. }
  1446. tableDataList = append(tableDataList, dataCol)
  1447. }
  1448. }
  1449. } else {
  1450. // 指标行
  1451. mergeList = append(mergeList, TableDataMerge{
  1452. StartRowIndex: 0,
  1453. StartColumnIndex: 0,
  1454. MergeRowNum: 0,
  1455. MergeColumnNum: 1,
  1456. })
  1457. // 第一行
  1458. {
  1459. firstCol := make([]LuckySheetDataValue, 0)
  1460. dateStr := "日期"
  1461. switch lang {
  1462. case utils.EnLangVersion:
  1463. dateStr = "Date"
  1464. }
  1465. firstCol = append(firstCol, LuckySheetDataValue{
  1466. Value: dateStr,
  1467. Monitor: dateStr,
  1468. MergeCell: LuckySheetDataConfigMerge{
  1469. Row: 0, //行数
  1470. Column: 0, //列数
  1471. Rs: 1, //合并的行数
  1472. Cs: 2, //合并的列数
  1473. },
  1474. })
  1475. firstCol = append(firstCol, LuckySheetDataValue{})
  1476. // 日期列
  1477. for _, v := range data.Data[0].Data {
  1478. firstCol = append(firstCol, LuckySheetDataValue{
  1479. Value: v.DataTime,
  1480. Monitor: v.DataTime,
  1481. MergeCell: LuckySheetDataConfigMerge{},
  1482. })
  1483. }
  1484. // 文本列
  1485. for _, textColList := range data.TextRowData {
  1486. firstCol = append(firstCol, LuckySheetDataValue{
  1487. Value: textColList[0].Value,
  1488. Monitor: textColList[0].ShowValue,
  1489. MergeCell: LuckySheetDataConfigMerge{},
  1490. })
  1491. }
  1492. tableDataList = append(tableDataList, firstCol)
  1493. }
  1494. // 日期列+文本列+两列表头(日期这个文案表头)
  1495. //colLen := lenCol+2+len(data.TextRowData)
  1496. for i := 0; i < lenEdb; i++ {
  1497. dataCol := make([]LuckySheetDataValue, 0)
  1498. // 指标信息
  1499. tmpEdbInfo := data.Data[i]
  1500. // 指标名称
  1501. {
  1502. edbName := tmpEdbInfo.EdbName
  1503. if tmpEdbInfo.EdbAliasName != `` {
  1504. edbName = tmpEdbInfo.EdbAliasName
  1505. } else {
  1506. switch lang {
  1507. case utils.EnLangVersion:
  1508. edbName = tmpEdbInfo.EdbNameEn
  1509. }
  1510. }
  1511. dataCol = append(dataCol, LuckySheetDataValue{
  1512. Value: edbName,
  1513. Monitor: edbName,
  1514. MergeCell: LuckySheetDataConfigMerge{},
  1515. })
  1516. }
  1517. // 指标单位、频度
  1518. {
  1519. var name string
  1520. switch lang {
  1521. case utils.EnLangVersion:
  1522. name = tmpEdbInfo.UnitEn + " / " + utils.GetFrequencyEn(tmpEdbInfo.Frequency)
  1523. default:
  1524. name = tmpEdbInfo.Unit + " / " + tmpEdbInfo.Frequency
  1525. }
  1526. dataCol = append(dataCol, LuckySheetDataValue{
  1527. Value: name,
  1528. Monitor: name,
  1529. MergeCell: LuckySheetDataConfigMerge{},
  1530. })
  1531. }
  1532. // 指标数据列
  1533. for _, tmpData := range tmpEdbInfo.Data {
  1534. background := ``
  1535. if tmpData.DataType == 5 {
  1536. background = "#ffefdd"
  1537. }
  1538. dataCol = append(dataCol, LuckySheetDataValue{
  1539. Value: tmpData.Value,
  1540. Monitor: tmpData.ShowValue,
  1541. MergeCell: LuckySheetDataConfigMerge{},
  1542. Background: background,
  1543. })
  1544. }
  1545. // 文本列
  1546. for _, textColList := range data.TextRowData {
  1547. dataCol = append(dataCol, LuckySheetDataValue{
  1548. Value: textColList[i+1].Value,
  1549. Monitor: textColList[i+1].ShowValue,
  1550. MergeCell: LuckySheetDataConfigMerge{},
  1551. })
  1552. }
  1553. tableDataList = append(tableDataList, dataCol)
  1554. }
  1555. }
  1556. selfTableData.MergeList = mergeList
  1557. selfTableData.TableDataList = tableDataList
  1558. return
  1559. }
  1560. // GetTableDataByMixedTableData 通过混合表格数据获取表格数据
  1561. func GetTableDataByMixedTableData(config [][]request.MixedTableCellDataReq, hideMerged bool, excelInfoId int) (selfTableData TableData, err error) {
  1562. tableDataList := make([][]LuckySheetDataValue, 0)
  1563. mergeList := make([]TableDataMerge, 0)
  1564. // 获取管理规则
  1565. excelRuleMappingList, err := excel.GetExcelRuleMappingByExcelInfoId(excelInfoId)
  1566. if err != nil {
  1567. return
  1568. }
  1569. excelRuleMap := make(map[int]*excel.ExcelInfoRuleMappingView)
  1570. for _, v := range excelRuleMappingList {
  1571. excelRuleMap[v.ExcelInfoRuleMappingId] = v
  1572. }
  1573. ruleScopeMap := generateRuleScopeIndexMap(excelRuleMappingList)
  1574. // 开始文本行了
  1575. {
  1576. for i, row := range config {
  1577. dataCol := make([]LuckySheetDataValue, 0)
  1578. for j, cell := range row {
  1579. tmp := LuckySheetDataValue{
  1580. Value: cell.Value,
  1581. Monitor: cell.ShowValue,
  1582. MergeCell: LuckySheetDataConfigMerge{},
  1583. }
  1584. if cell.DataType != request.CustomTextDT && tmp.Monitor == `` {
  1585. tmp.Monitor = "-"
  1586. cell.ShowFormatValue = "-"
  1587. }
  1588. // TODO: 混合表格/平衡表-合并单元格
  1589. // 前端需要隐藏被合并的单元格, 混合表格/平衡表通过这个字段判断, 不通过HandleTableCell方法隐藏
  1590. if cell.MerData != nil {
  1591. if hideMerged && cell.MerData.Type == "merged" {
  1592. continue
  1593. }
  1594. tmp.MergeCell.Rs = cell.MerData.Mer.Rowspan
  1595. tmp.MergeCell.Cs = cell.MerData.Mer.Colspan
  1596. tmp.MergeCell.Row = cell.MerData.Mer.Row
  1597. tmp.MergeCell.Column = cell.MerData.Mer.Col
  1598. }
  1599. if cell.ShowStyle != "" {
  1600. var styleConfig request.MixCellShowStyle
  1601. if err := json.Unmarshal([]byte(cell.ShowStyle), &styleConfig); err != nil {
  1602. utils.FileLog.Info("表格样式showStyle解析失败", err.Error())
  1603. }
  1604. if styleConfig.BackgroundColor != "" {
  1605. tmp.Background = styleConfig.BackgroundColor
  1606. }
  1607. if styleConfig.Color != "" {
  1608. tmp.FontColor = styleConfig.Color
  1609. }
  1610. switch styleConfig.Align {
  1611. case "center":
  1612. tmp.HorizontalType = 0
  1613. tmp.Ht = 0
  1614. case "left":
  1615. tmp.HorizontalType = 1
  1616. tmp.Ht = 1
  1617. case "right":
  1618. tmp.HorizontalType = 2
  1619. tmp.Ht = 2
  1620. }
  1621. if styleConfig.FontSize != "" {
  1622. tmp.Fs = styleConfig.FontSize
  1623. }
  1624. showFormatValue := fmt.Sprintf("%v", cell.ShowFormatValue)
  1625. tmp.Monitor = showFormatValue
  1626. // 如果没有showValue, 则使用value
  1627. if cell.ShowValue == "" {
  1628. _, err := strconv.ParseFloat(cell.Value, 64)
  1629. if err == nil {
  1630. cell.ShowValue = cell.Value
  1631. }
  1632. }
  1633. _, err := strconv.ParseFloat(cell.Value, 64)
  1634. if err == nil {
  1635. hasPercent := false
  1636. if styleConfig.Nt == "percent" {
  1637. hasPercent = true
  1638. }
  1639. // 修复历史数据
  1640. if styleConfig.Pn != 0 {
  1641. if styleConfig.Decimal == nil {
  1642. styleConfig.Decimal = new(int)
  1643. }
  1644. *styleConfig.Decimal += styleConfig.Pn
  1645. if *styleConfig.Decimal < 0 {
  1646. *styleConfig.Decimal = 0
  1647. }
  1648. }
  1649. if styleConfig.Decimal != nil {
  1650. tmp.Monitor = utils.RoundNumber(cell.ShowValue, *styleConfig.Decimal, hasPercent)
  1651. } else {
  1652. if hasPercent {
  1653. numDecimal, _ := decimal.NewFromString(cell.ShowValue)
  1654. tmpStr := numDecimal.Mul(decimal.NewFromInt(100)).String()
  1655. tmp.Monitor = tmpStr + "%"
  1656. } else {
  1657. tmp.Monitor = cell.ShowValue
  1658. }
  1659. }
  1660. } else {
  1661. if cell.DataType == request.CustomTextDT {
  1662. tmp.Monitor = cell.Value
  1663. }
  1664. }
  1665. // switch styleConfig.Last {
  1666. // case "nt":
  1667. // // 先进行数字的百分比计算,然后保留小数点位数
  1668. // percent := tmpShowValue * 100
  1669. // if styleConfig.Decimal == nil {
  1670. // continue
  1671. // }
  1672. // factor := math.Pow(10, float64(*styleConfig.Decimal))
  1673. // rounded := math.Round(percent*factor) / factor
  1674. // tmp.Monitor = fmt.Sprintf("%g%%", rounded)
  1675. // case "decimal":
  1676. // // 先保留小数点位数,再进行百分比计算
  1677. // if styleConfig.Decimal == nil {
  1678. // continue
  1679. // }
  1680. // factor := math.Pow(10, float64(*styleConfig.Decimal))
  1681. // rounded := math.Round(tmpShowValue*factor) / factor
  1682. // if styleConfig.Nt == "percent" {
  1683. // percent := rounded * 100
  1684. // var precisionStr string
  1685. // if *styleConfig.Decimal > 2 {
  1686. // precision := *styleConfig.Decimal - 2
  1687. // precisionStr = strconv.FormatFloat(rounded, 'f', precision, 64)
  1688. // } else {
  1689. // precisionStr = strconv.FormatFloat(math.Round(percent), 'f', -1, 64)
  1690. // }
  1691. // tmp.Monitor = fmt.Sprintf("%s%%", precisionStr)
  1692. // } else {
  1693. // tmp.Monitor = fmt.Sprintf("%g", rounded)
  1694. // }
  1695. // }
  1696. // }
  1697. }
  1698. if ruleIds, ok := ruleScopeMap[i][j]; ok {
  1699. for _, ruleId := range ruleIds {
  1700. if checkCellRule(excelRuleMap[ruleId], cell.ShowValue, config) {
  1701. tmp.Background = strings.TrimSpace(excelRuleMap[ruleId].BackgroundColor)
  1702. tmp.FontColor = strings.TrimSpace(excelRuleMap[ruleId].FontColor)
  1703. }
  1704. }
  1705. }
  1706. dataCol = append(dataCol, tmp)
  1707. }
  1708. tableDataList = append(tableDataList, dataCol)
  1709. }
  1710. }
  1711. selfTableData.MergeList = mergeList
  1712. selfTableData.TableDataList = tableDataList
  1713. return
  1714. }