12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946 |
- package excel
- import (
- "encoding/json"
- "errors"
- "eta/eta_mobile/models/data_manage/excel"
- "eta/eta_mobile/models/data_manage/excel/request"
- "eta/eta_mobile/utils"
- "fmt"
- "github.com/tealeg/xlsx"
- "github.com/xuri/excelize/v2"
- "os"
- "reflect"
- "strconv"
- "strings"
- "time"
- )
- type LuckySheetDataBak struct {
- CalcChain []interface{} `json:"calcChain" description:"公式链"`
- CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
- ChWidth int64 `json:"ch_width" description:"工作表区域的宽度"`
- Config struct {
- BorderInfo []struct {
- BorderType string `json:"borderType" description:""`
- Color string `json:"color" description:""`
- Range []struct {
- Column []int64 `json:"column" description:""`
- Row []int64 `json:"row" description:""`
- } `json:"range" description:""`
- RangeType string `json:"rangeType" description:""`
- Style string `json:"style" description:""`
- Value struct {
- B struct {
- Color string `json:"color" description:""`
- Style string `json:"style" description:""`
- } `json:"b" description:""`
- ColIndex int64 `json:"col_index" description:""`
- L struct {
- Color string `json:"color" description:""`
- Style string `json:"style" description:""`
- } `json:"l" description:""`
- R struct {
- Color string `json:"color" description:""`
- Style string `json:"style" description:""`
- } `json:"r" description:""`
- RowIndex int64 `json:"row_index" description:""`
- T struct {
- Color string `json:"color" description:""`
- Style string `json:"style" description:""`
- } `json:"t" description:""`
- } `json:"value" description:"" description:""`
- } `json:"borderInfo" description:""`
- Colhidden struct{} `json:"colhidden" description:""`
- Columnlen map[string]float64 `json:"columnlen" description:""`
- CustomHeight struct {
- Zero int64 `json:"0"`
- } `json:"customHeight" description:""`
- CustomWidth struct {
- Two int64 `json:"2" description:""`
- } `json:"customWidth" description:""`
- Merge struct{} `json:"merge" description:""`
- Rowlen map[string]float64 `json:"rowlen" description:""`
- } `json:"config" description:""`
- Data [][]struct {
- Ct struct {
- Fa string `json:"fa"`
- T string `json:"t"`
- } `json:"ct"`
- M string `json:"m"`
- V interface{} `json:"v"`
- } `json:"data" description:""`
- DataVerification struct{} `json:"dataVerification" description:""`
- Filter interface{} `json:"filter" description:""`
- FilterSelect interface{} `json:"filter_select" description:""`
- Hyperlink struct{} `json:"hyperlink" description:""`
- Images struct{} `json:"images" description:""`
- Index string `json:"index" description:""`
- JfgirdSelectSave []interface{} `json:"jfgird_select_save" description:""`
- LuckysheetAlternateformatSave []interface{} `json:"luckysheet_alternateformat_save" description:""`
- LuckysheetConditionformatSave []interface{} `json:"luckysheet_conditionformat_save" description:""`
- LuckysheetSelectSave []struct {
- Column []int64 `json:"column" description:""`
- ColumnFocus int64 `json:"column_focus" description:""`
- Height int64 `json:"height" description:""`
- HeightMove int64 `json:"height_move" description:""`
- Left int64 `json:"left" description:""`
- LeftMove int64 `json:"left_move" description:""`
- Row []int64 `json:"row" description:""`
- RowFocus int64 `json:"row_focus" description:""`
- Top int64 `json:"top" description:""`
- TopMove int64 `json:"top_move" description:""`
- Width int64 `json:"width" description:""`
- WidthMove int64 `json:"width_move" description:""`
- } `json:"luckysheet_select_save" description:"" description:""`
- LuckysheetSelectionRange []struct {
- Column []int64 `json:"column" description:""`
- Row []int64 `json:"row" description:""`
- } `json:"luckysheet_selection_range" description:""`
- RhHeight float64 `json:"rh_height" description:""`
- ScrollLeft float64 `json:"scrollLeft" description:""`
- ScrollTop float64 `json:"scrollTop" description:""`
- Status int64 `json:"status" description:""`
- Visibledatacolumn []int64 `json:"visibledatacolumn" description:""`
- Visibledatarow []int64 `json:"visibledatarow" description:""`
- ZoomRatio float64 `json:"zoomRatio" description:"sheet缩放比例"`
- }
- type LuckySheetData struct {
- Name string `json:"name" description:"工作表名称"`
- Index interface{} `json:"index" description:"工作表索引"`
- Row int `json:"row" description:"行数"`
- Column int `json:"column" description:"列数"`
- CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
- ChWidth int64 `json:"ch_width" description:"工作表区域的宽度"`
- Config LuckySheetDataConfig `json:"config" description:""`
-
- RhHeight float64 `json:"rh_height" description:"工作表区域的高度"`
- ScrollLeft float64 `json:"scrollLeft" description:"左右滚动条位置"`
- ScrollTop float64 `json:"scrollTop" description:"上下滚动条位置"`
- CalcChain []interface{} `json:"calcChain" description:"公式链"`
- Status interface{} `json:"status" description:"激活状态"`
- VisibleDataColumn []int64 `json:"visibledatacolumn" description:"所有列的位置信息,递增的列位置数据,初始化无需设置"`
- VisibleDataRow []int64 `json:"visibledatarow" description:"所有行的位置信息,递增的行位置数据,初始化无需设置"`
- ZoomRatio float64 `json:"zoomRatio" description:"sheet缩放比例"`
- }
- type LuckySheetDataConfig struct {
- BorderInfo []LuckySheetDataConfigBorderInfo `json:"borderInfo" description:"边框"`
- Colhidden map[string]int64 `json:"colhidden" description:"隐藏列,示例值:\"colhidden\":{\"30\":0,\"31\":0}"`
- Columnlen map[string]float64 `json:"columnlen" description:"每个单元格的列宽"`
-
-
-
-
-
-
- Merge map[string]LuckySheetDataConfigMerge `json:"merge" description:"合并单元格"`
- Rowlen map[string]float64 `json:"rowlen" description:"每个单元格的行高"`
- }
- type LuckySheetDataConfigMerge struct {
- Row int `json:"r" description:"行数"`
- Column int `json:"c" description:"列数"`
- Rs int `json:"rs" description:"合并的行数"`
- Cs int `json:"cs" description:"合并的列数"`
- }
- type LuckySheetDataConfigBorderInfo struct {
- 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"`
- Color string `json:"color" description:"边框颜色 color: 16进制颜色值"`
- Range []struct {
- Column []int64 `json:"column" description:"行"`
- Row []int64 `json:"row" description:"列"`
- } `json:"range" description:"选区范围 range: 行列信息数组"`
- RangeType string `json:"rangeType" description:"选区 rangeType: range | cell "`
- 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"`
- Value LuckySheetDataConfigBorderInfoCellValue `json:"value" description:"" description:"范围类型分单个单元格的数据"`
- }
- type LuckySheetDataConfigBorderInfoCellValue struct {
- B LuckySheetDataConfigBorderInfoCell `json:"b" description:"下边框"`
- L LuckySheetDataConfigBorderInfoCell `json:"l" description:"左边框"`
- R LuckySheetDataConfigBorderInfoCell `json:"r" description:"右边框"`
- T LuckySheetDataConfigBorderInfoCell `json:"t" description:"上边框"`
- ColIndex int64 `json:"col_index" description:"第几行"`
- RowIndex int64 `json:"row_index" description:"第几列"`
- }
- type LuckySheetDataConfigBorderInfoCell struct {
- Color string `json:"color" description:"边框颜色 color: 16进制颜色值"`
- 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"`
- 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"`
- }
- type LuckySheetCellData struct {
- Col int64 `json:"c" description:"列"`
- Row int64 `json:"r" description:"行"`
- Value LuckySheetDataValue `json:"v" description:"单元格内值的数据"`
- }
- type LuckySheetDataValue struct {
- CellType LuckySheetDataCellType `json:"ct" description:"单元格值格式:文本、时间等 "`
- Value interface{} `json:"v" description:"原始值"`
- Monitor string `json:"m" description:"显示值"`
- Background string `json:"bg" description:"背景色,实例值:#fff000"`
- 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 华文隶书 "`
- 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 华文隶书 "`
- FontColor string `json:"fc" description:"字体颜色,示例值:#fff000" `
- Bold int `json:"bl" description:"粗体,0 常规 、 1加粗 "`
- Italic int `json:"it" description:"斜体,0 常规 、 1 斜体"`
- Fontsize int `description:"字体大小,14"`
- CancelLine int ` description:"删除线, 0 常规 、 1 删除线"`
- HorizontalType int `description:"水平对齐, 0 居中、1 左、2右"`
- VerticalType int ` description:"垂直对齐, 0 中间、1 上、2下"`
- Fs interface{} `json:"fs" description:"字体大小,14"`
- Cl interface{} `json:"cl" description:"删除线, 0 常规 、 1 删除线"`
- Ht interface{} `json:"ht" description:"水平对齐, 0 居中、1 左、2右"`
- Vt interface{} `json:"vt" description:"垂直对齐, 0 中间、1 上、2下"`
-
-
- TextBeak int `description:"文本换行, 0 截断、1溢出、2 自动换行"`
- Tb interface{} `json:"tb" description:"文本换行, 0 截断、1溢出、2 自动换行"`
- Ps LuckySheetDataCellComment `json:"ps" description:"批注"`
- Function string `json:"f" description:"公式"`
- MergeCell LuckySheetDataConfigMerge `json:"mc" description:"合并单元格信息"`
- }
- type LuckySheetDataCellType struct {
- Fa string `json:"fa" description:"格式名称,例如:“General”为自动格式"`
- T string `json:"t" description:"格式类型,例如:“n”为数字类型"`
- S []struct {
- 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 华文隶书 "`
- 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 华文隶书 "`
- FontColor string `json:"fc" description:"字体颜色,示例值:#fff000" `
- Fontsize int `description:"字体大小,14"`
- CancelLine int ` description:"删除线, 0 常规 、 1 删除线"`
- HorizontalType int `description:"水平对齐, 0 居中、1 左、2右"`
- VerticalType int `description:"垂直对齐, 0 中间、1 上、2下"`
- Fs interface{} `json:"fs" description:"字体大小,14"`
- Cl interface{} `json:"cl" description:"删除线, 0 常规 、 1 删除线"`
- Ht interface{} `json:"ht" description:"水平对齐, 0 居中、1 左、2右"`
- Vt interface{} `json:"vt" description:"垂直对齐, 0 中间、1 上、2下"`
- Un int `json:"un" description:""`
- Bold int `json:"bl" description:"粗体,0 常规 、 1加粗 "`
- Italic int `json:"it" description:"斜体,0 常规 、 1 斜体"`
- Value interface{} `json:"v" description:"原始值"`
- } `json:"s"`
- }
- type LuckySheetDataCellComment struct {
- Left int `json:"left" description:"批注框距离左边工作表边缘位置"`
- Top int `json:"top" description:"批注框距离上边工作表边缘位置"`
- Width int `json:"width" description:"批注框宽度"`
- Height int `json:"height" description:"批注框高度"`
- Value string `json:"value" description:"批注内容"`
- IsShow bool `json:"isshow" description:"是否显示批注"`
- }
- func GetLuckySheetData(jsonStr string) (item *LuckySheetData, err error) {
- err = json.Unmarshal([]byte(jsonStr), &item)
- for k, v := range item.CellData {
- value := v.Value
- value.Fontsize = getIntValueByInterface(value.Fs)
- value.CancelLine = getIntValueByInterface(value.Cl)
- value.HorizontalType = getIntValueByInterface(value.Ht)
- value.VerticalType = getIntValueByInterface(value.Vt)
- value.FontFamily = getIntValueByInterface(value.FF)
- value.TextBeak = getIntValueByInterface(value.Tb)
- if len(value.CellType.S) > 0 {
- for kk, vv := range value.CellType.S {
- vv.Fontsize = getIntValueByInterface(vv.Fs)
- vv.CancelLine = getIntValueByInterface(vv.Cl)
- vv.HorizontalType = getIntValueByInterface(vv.Ht)
- vv.VerticalType = getIntValueByInterface(vv.Vt)
- vv.FontFamily = getIntValueByInterface(vv.FF)
- value.CellType.S[kk] = vv
- }
- }
- item.CellData[k].Value = value
- }
- if len(item.Config.BorderInfo) > 0 {
- for k, v := range item.Config.BorderInfo {
- v.Value.T.Style = getIntValueByInterface(v.Value.T.Style)
- v.Value.B.Style = getIntValueByInterface(v.Value.B.Style)
- v.Value.L.Style = getIntValueByInterface(v.Value.L.Style)
- v.Value.R.Style = getIntValueByInterface(v.Value.R.Style)
- item.Config.BorderInfo[k] = v
- }
- }
- return
- }
- func getIntValueByInterface(valInterface interface{}) (val int) {
- if valInterface == nil {
- return
- }
- switch reflect.TypeOf(valInterface).Kind() {
- case reflect.String:
- tmpValue := reflect.ValueOf(valInterface).String()
- tmpValInt, err := strconv.Atoi(tmpValue)
- if err != nil {
- val = 0
- } else {
- val = tmpValInt
- }
- case reflect.Int, reflect.Int32, reflect.Int64:
- tmpValue := reflect.ValueOf(valInterface).Int()
- val = int(tmpValue)
- }
- return
- }
- type TableData struct {
- TableDataList [][]LuckySheetDataValue
- RowWidthList []float64
- RowHeightList []float64
- RemoveTopRow int `description:"移除表格上方的行数"`
- RemoveBottomRow int `description:"移除表格下方的行数"`
- RemoveLeftColumn int `description:"移除表格左侧的列数"`
- RemoveRightColumn int `description:"移除表格右侧的列数"`
- MergeList []TableDataMerge `description:"合并数据列"`
- }
- type TableDataMerge struct {
- StartRowIndex int `json:"start_row_index" description:"开始的行下标"`
- StartColumnIndex int `json:"start_column" description:"开始的列下标"`
- MergeRowNum int `json:"merge_row_num" description:"合并的行数"`
- MergeColumnNum int `json:"merge_column_num" description:"合并的列数"`
- }
- func (tableData TableData) ToExcel() (downloadFilePath string, err error) {
-
-
-
- downloadFilePath, err = getDownloadPath()
- if err != nil {
- return
- }
- xlsxFile := xlsx.NewFile()
- if err != nil {
- return
- }
-
- err = tableData.WriteExcelSheetData(xlsxFile, "sheet1")
- if err != nil {
- return
- }
-
- err = xlsxFile.Save(downloadFilePath)
- if err != nil {
- return
- }
-
-
-
- return
- }
- func (tableData TableData) WriteExcelSheetData(xlsxFile *xlsx.File, sheetName string) (err error) {
- style := xlsx.NewStyle()
- alignment := xlsx.Alignment{
- Horizontal: "center",
- Vertical: "center",
- WrapText: true,
- }
- style.Alignment = alignment
- style.ApplyAlignment = true
- sheet, err := xlsxFile.AddSheet(sheetName)
- if err != nil {
- return
- }
- for k, v := range tableData.RowWidthList {
- err = sheet.SetColWidth(k, k, v/10)
- if err != nil {
- return
- }
- }
-
- lenRowHeight := len(tableData.RowHeightList)
- for index, v := range tableData.TableDataList {
- tmpRow := sheet.AddRow()
-
- if index < lenRowHeight && tableData.RowHeightList[index] > 0 {
- tmpRow.SetHeight(tableData.RowHeightList[index] / 2)
- }
- for _, cellInfo := range v {
- tmpStyle := new(xlsx.Style)
-
-
-
-
-
-
-
-
- defaultFill := xlsx.DefaultFill()
- if cellInfo.Background != `` {
- defaultFill.PatternType = "solid"
- backgroundColor := cellInfo.Background
-
- backgroundColor = getColor(backgroundColor)
- defaultFill.BgColor = strings.TrimPrefix(backgroundColor, "#")
- defaultFill.FgColor = strings.TrimPrefix(backgroundColor, "#")
- }
- tmpStyle.Fill = *defaultFill
-
- tmpStyle.Font = getExcelFontConf(cellInfo)
-
- tmpStyle.ApplyAlignment = true
- tmpStyle.Alignment = getExcelAlignmentConf(cellInfo)
-
- tmpStyle.Border = xlsx.Border{
- Left: "thin",
- LeftColor: "000000",
- Right: "thin",
- RightColor: "000000",
- Top: "thin",
- TopColor: "000000",
- Bottom: "thin",
- BottomColor: "000000",
- }
-
-
- tmpRow := tmpRow.AddCell()
- tmpRow.SetStyle(tmpStyle)
- valueStr := cellInfo.Monitor
- if valueStr == `` {
-
- if valueStr == `` && cellInfo.CellType.S != nil {
-
- for _, cellS := range cellInfo.CellType.S {
- valueStr += fmt.Sprint(cellS.Value)
- }
- }
- }
-
- switch cellInfo.CellType.Fa {
- case "General":
- if cellInfo.CellType.S != nil {
- tmpRow.SetString(valueStr)
- } else {
- tmpRow.SetValue(cellInfo.Value)
- }
- case "@":
- tmpRow.SetString(valueStr)
- default:
- tmpRow.SetString(valueStr)
- }
- if cellInfo.Function != `` {
-
-
- tmpRow.SetFormula(cellInfo.Function)
- }
-
-
-
- }
- }
- for _, v := range tableData.MergeList {
- for k, cell := range sheet.Row(v.StartRowIndex).Cells {
- if v.StartColumnIndex == k {
- cell.Merge(v.MergeColumnNum, v.MergeRowNum)
- }
- }
- }
- return
- }
- func (item *LuckySheetData) GetTableDataByLuckySheetDataStr(isRemoveBlankCell bool) (selfTableData TableData, err error) {
- luckySheetCellDataList := item.CellData
-
- tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
-
- var maxRow, maxCol int64
- for _, v := range luckySheetCellDataList {
-
- if v.Row > maxRow {
- maxRow = v.Row
- }
- if v.Col > maxCol {
- maxCol = v.Col
- }
- var tmpRow map[int64]LuckySheetDataValue
- tmpRow, ok := tableDataMap[v.Row]
- if ok {
- tmpRow[v.Col] = v.Value
- } else {
- tmpRow = make(map[int64]LuckySheetDataValue)
- tmpRow[v.Col] = v.Value
- }
- tableDataMap[v.Row] = tmpRow
- }
- tableDataList := make([][]LuckySheetDataValue, 0)
- var i int64
-
- configColumnConf := item.Config.Columnlen
- rowWidthMap := make(map[int]float64)
- rowWidthList := make([]float64, 0)
-
- configRowConf := item.Config.Rowlen
- rowHeightList := make([]float64, 0)
- for i = 0; i <= maxRow; i++ {
-
- tmpTableColDataList := make([]LuckySheetDataValue, 0)
-
- tmpHeight, ok := configRowConf[fmt.Sprint(i)]
- if !ok {
- tmpHeight = 0
- }
- rowHeightList = append(rowHeightList, tmpHeight)
- tmpRowData, ok := tableDataMap[i]
-
- if !ok {
- tmpRowData = make(map[int64]LuckySheetDataValue)
- }
- var j int64
- for j = 0; j <= maxCol; j++ {
- tmpTableColData, ok := tmpRowData[j]
- if !ok {
- tmpTableColData = LuckySheetDataValue{}
- }
-
- tmpTableColData.Monitor = handleCellVal(tmpTableColData)
- tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)
-
- tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
- if !ok {
- tmpWidth = 0
- }
- rowIndex := len(tmpTableColDataList) - 1
- if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
- rowWidthList = append(rowWidthList, tmpWidth)
- }
- rowWidthMap[rowIndex] = tmpWidth
- }
- tableDataList = append(tableDataList, tmpTableColDataList)
- }
- tableDataMergeList := make([]TableDataMerge, 0)
- tableRemoveNum := TableRemoveNum{}
-
- if isRemoveBlankCell {
- tableDataList, tableRemoveNum, rowHeightList, rowWidthList, tableDataMergeList = handleTableDataList(tableDataList, item.Config.Merge, rowHeightList, rowWidthList)
- }
-
- {
-
- selfTableData.RemoveTopRow = tableRemoveNum.RemoveTopRow
- selfTableData.RemoveBottomRow = tableRemoveNum.RemoveBottomRow
- selfTableData.RemoveLeftColumn = tableRemoveNum.RemoveLeftColumn
- selfTableData.RemoveRightColumn = tableRemoveNum.RemoveRightColumn
- }
- selfTableData.TableDataList = tableDataList
- selfTableData.RowWidthList = rowWidthList
- selfTableData.RowHeightList = rowHeightList
- selfTableData.MergeList = tableDataMergeList
- return
- }
- type TableRemoveNum struct {
- RemoveTopRow int `description:"移除表格上方的行数"`
- RemoveBottomRow int `description:"移除表格下方的行数"`
- RemoveLeftColumn int `description:"移除表格左侧的列数"`
- RemoveRightColumn int `description:"移除表格右侧的列数"`
- }
- func handleTableDataList(tableDataList [][]LuckySheetDataValue, luckySheetDataConfigMergeList map[string]LuckySheetDataConfigMerge, rowHeightList, rowWidthList []float64) ([][]LuckySheetDataValue, TableRemoveNum, []float64, []float64, []TableDataMerge) {
- var removeTopRow, removeBottomRow, removeLeftColumn, removeRightColumn int
- tableDataMergeList := make([]TableDataMerge, 0)
-
- lenRow := len(tableDataList)
-
- deleteRowIndexList := make([]int, 0)
- if lenRow > 0 {
- var flag = false
-
- for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
- isDelete := true
- for _, v := range tableDataList[rowIndex] {
- if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- removeTopRow++
- deleteRowIndexList = append(deleteRowIndexList, rowIndex)
- }
- }
-
- flag = false
-
- deleteBottomRowIndexList := make([]int, 0)
-
- if len(tableDataList) > 1 {
- for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
- isDelete := true
- for _, v := range tableDataList[rowIndex] {
- if v.Monitor != `` {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
- removeBottomRow++
- }
- }
- }
- if len(deleteBottomRowIndexList) > 0 {
- deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
- }
- lenDeleteRow := len(deleteRowIndexList)
- if lenDeleteRow > 0 {
- for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
-
- tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...)
-
- rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...)
- }
- }
- }
-
- deleteColumnIndexList := make([]int, 0)
- if len(tableDataList) > 0 {
- var flag = false
- lenColumn := len(tableDataList[0])
-
- for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
- isDelete := true
- for _, v := range tableDataList {
-
- if len(v) <= 0 {
- continue
- }
- if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- removeLeftColumn++
- deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
- }
- }
-
- flag = false
-
- deleteTailColumnIndexList := make([]int, 0)
-
- if lenColumn > 1 {
- for columnIndex := lenColumn - 1; columnIndex >= 0; columnIndex-- {
- isDelete := true
- for _, v := range tableDataList {
-
- if len(v) <= 0 {
- continue
- }
- if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
- removeRightColumn++
- }
- }
- }
- if len(deleteTailColumnIndexList) > 0 {
- deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
- }
- lenDeleteColumn := len(deleteColumnIndexList)
- if lenDeleteColumn > 0 {
- for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
-
- for k, v := range tableDataList {
- tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...)
- }
-
- rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...)
- }
- }
- }
-
- for _, v := range luckySheetDataConfigMergeList {
- indexRow := v.Row - removeTopRow
- indexColumn := v.Column - removeLeftColumn
- tableDataMerge := TableDataMerge{
- StartRowIndex: indexRow,
- StartColumnIndex: indexColumn,
- MergeRowNum: v.Rs - 1,
- MergeColumnNum: v.Cs - 1,
- }
- tableDataMergeList = append(tableDataMergeList, tableDataMerge)
- }
- return tableDataList, TableRemoveNum{
- RemoveTopRow: removeTopRow,
- RemoveBottomRow: removeBottomRow,
- RemoveLeftColumn: removeLeftColumn,
- RemoveRightColumn: removeRightColumn,
- }, rowHeightList, rowWidthList, tableDataMergeList
- }
- func (item *LuckySheetData) ToExcel() (downloadFilePath string, err error) {
- tableData, err := item.GetTableDataByLuckySheetDataStr(true)
- if err != nil {
- return
- }
- downloadFilePath, err = tableData.ToExcel()
- return
- }
- func getDownloadPath() (fpath string, err error) {
- dateDir := time.Now().Format("20060102")
- uploadDir := "static/xls/" + dateDir
- err = os.MkdirAll(uploadDir, utils.DIR_MOD)
- if err != nil {
- return
- }
- randStr := utils.GetRandStringNoSpecialChar(28)
- fileName := randStr + ".xlsx"
- fpath = uploadDir + "/" + fileName
- return
- }
- func getExcelFontConf(cellInfo LuckySheetDataValue) xlsx.Font {
- fontSize := 10
- familyName := ``
- var isBold, isItalic, isUnderline bool
-
- {
- if cellInfo.Bold == 1 {
- isBold = true
- }
- if cellInfo.Italic == 1 {
- isItalic = true
- }
- if cellInfo.CancelLine == 1 {
- isUnderline = true
- }
-
-
-
-
-
-
- if cellInfo.Fontsize > 0 {
- fontSize = cellInfo.Fontsize
- }
- tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
- if !familyNameOk {
- tmpFamilyName = "宋体"
- }
- familyName = tmpFamilyName
- }
- return xlsx.Font{
- Size: fontSize,
- Name: familyName,
-
-
- Color: strings.TrimPrefix(cellInfo.FontColor, "#"),
- Bold: isBold,
- Italic: isItalic,
- Underline: isUnderline,
- }
- }
- func getExcelAlignmentConf(cellInfo LuckySheetDataValue) xlsx.Alignment {
-
- isWrapText := false
- if cellInfo.TextBeak == 2 {
- isWrapText = true
- }
- rotateText := 0
-
-
-
-
-
-
- return xlsx.Alignment{
- Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
-
-
- TextRotation: rotateText,
- Vertical: LuckyVerticalMap[cellInfo.VerticalType],
- WrapText: isWrapText,
- }
- }
- var LuckyFontFamilyMap = map[int]string{
- 0: "Times New Roman",
- 1: "Arial",
- 2: "Tahoma",
- 3: "Verdana",
- 4: "微软雅黑",
- 5: "宋体",
- 6: "黑体",
- 7: "楷体",
- 8: "仿宋",
- 9: "新宋体",
- 10: "华文新魏",
- 11: "华文行楷",
- 12: "华文隶书",
- }
- var LuckyBorderMap = map[string]string{
- "": "Thin",
- "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",
- }
- var LuckyHorizontalMap = map[int]string{
-
- 0: "center",
- 1: "left",
- 2: "right",
- }
- var LuckyVerticalMap = map[int]string{
-
- 0: "center",
- 1: "top",
-
- }
- func GetColumnName(rowNumber int, colNumber int) (colName string, err error) {
- if rowNumber < 1 || colNumber < 1 {
- err = errors.New("Row and Column Number can not be less than 1")
- return
- }
- dividend := colNumber
- var modulo int
- for dividend > 0 {
- modulo = (dividend - 1) % 26
-
- colName = string(65+modulo) + colName
- dividend = (int)((dividend - modulo) / 26)
- }
- colName = colName + strconv.Itoa(rowNumber)
- return
- }
- func (item *LuckySheetData) GetTableDataByLuckySheetDataStrBak() (selfTableData TableData, err error) {
- luckySheetCellDataList := item.CellData
-
- tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
-
- var maxRow, maxCol int64
- for _, v := range luckySheetCellDataList {
-
- if v.Row > maxRow {
- maxRow = v.Row
- }
- if v.Col > maxCol {
- maxCol = v.Col
- }
- var tmpRow map[int64]LuckySheetDataValue
- tmpRow, ok := tableDataMap[v.Row]
- if ok {
- tmpRow[v.Col] = v.Value
- } else {
- tmpRow = make(map[int64]LuckySheetDataValue)
- tmpRow[v.Col] = v.Value
- }
- tableDataMap[v.Row] = tmpRow
- }
- tableDataList := make([][]LuckySheetDataValue, 0)
- var i int64
-
- configColumnConf := item.Config.Columnlen
- rowWidthMap := make(map[int]float64)
- rowWidthList := make([]float64, 0)
-
- configRowConf := item.Config.Rowlen
- rowHeightList := make([]float64, 0)
- for i = 0; i <= maxRow; i++ {
-
- tmpTableColDataList := make([]LuckySheetDataValue, 0)
-
- tmpHeight, ok := configRowConf[fmt.Sprint(i)]
- if !ok {
- tmpHeight = 0
- }
- rowHeightList = append(rowHeightList, tmpHeight)
- tmpRowData, ok := tableDataMap[i]
-
- if !ok {
- tmpRowData = make(map[int64]LuckySheetDataValue)
- }
- var j int64
- for j = 0; j <= maxCol; j++ {
- tmpTableColData, ok := tmpRowData[j]
- if !ok {
- tmpTableColData = LuckySheetDataValue{}
- }
- tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)
-
- tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
- if !ok {
- tmpWidth = 0
- }
- rowIndex := len(tmpTableColDataList) - 1
- if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
- rowWidthList = append(rowWidthList, tmpWidth)
- }
- rowWidthMap[rowIndex] = tmpWidth
- }
- tableDataList = append(tableDataList, tmpTableColDataList)
- }
-
- lenRow := len(tableDataList)
-
- deleteRowIndexList := make([]int, 0)
- if lenRow > 0 {
- var flag = false
-
- for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
- isDelete := true
- for _, v := range tableDataList[rowIndex] {
- if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- selfTableData.RemoveTopRow++
- deleteRowIndexList = append(deleteRowIndexList, rowIndex)
- }
- }
-
- flag = false
-
- deleteBottomRowIndexList := make([]int, 0)
- for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
- isDelete := true
- for _, v := range tableDataList[rowIndex] {
- if v.Monitor != `` {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
- selfTableData.RemoveBottomRow++
- }
- }
- if len(deleteBottomRowIndexList) > 0 {
- deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
- }
- lenDeleteRow := len(deleteRowIndexList)
- if lenDeleteRow > 0 {
- for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
-
- tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...)
-
- rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...)
- }
- }
- }
-
- deleteColumnIndexList := make([]int, 0)
- if len(tableDataList) > 0 {
- var flag = false
- lenColumn := len(tableDataList[0])
-
- for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
- isDelete := true
- for _, v := range tableDataList {
-
- if len(v) <= 0 {
- continue
- }
- if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- selfTableData.RemoveLeftColumn++
- deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
- }
- }
-
- flag = false
-
- deleteTailColumnIndexList := make([]int, 0)
- for columnIndex := lenColumn - 1; columnIndex >= 0; columnIndex-- {
- isDelete := true
- for _, v := range tableDataList {
-
- if len(v) <= 0 {
- continue
- }
- if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
- isDelete = false
- flag = true
- break
- }
- }
- if flag {
- break
- }
- if isDelete {
- deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
- selfTableData.RemoveRightColumn++
- }
- }
- if len(deleteTailColumnIndexList) > 0 {
- deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
- }
- lenDeleteColumn := len(deleteColumnIndexList)
- if lenDeleteColumn > 0 {
- for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
-
- for k, v := range tableDataList {
- tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...)
- }
-
- rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...)
- }
- }
- }
-
- tableDataMergeList := make([]TableDataMerge, 0)
- for _, v := range item.Config.Merge {
- indexRow := v.Row - selfTableData.RemoveTopRow
- indexColumn := v.Column - selfTableData.RemoveLeftColumn
- tableDataMerge := TableDataMerge{
- StartRowIndex: indexRow,
- StartColumnIndex: indexColumn,
- MergeRowNum: v.Rs - 1,
- MergeColumnNum: v.Cs - 1,
- }
- tableDataMergeList = append(tableDataMergeList, tableDataMerge)
- }
-
- selfTableData.TableDataList = tableDataList
- selfTableData.RowWidthList = rowWidthList
- selfTableData.RowHeightList = rowHeightList
- selfTableData.MergeList = tableDataMergeList
- return
- }
- func (item *LuckySheetData) ToExcel2() (downloadFilePath string, err error) {
- tableData, err := item.GetTableDataByLuckySheetDataStr(true)
- downloadFilePath, err = getDownloadPath()
- if err != nil {
- return
- }
- f := excelize.NewFile()
-
- sheetName := `Sheet1`
- sheetIndex, err := f.NewSheet(sheetName)
- if err != nil {
- return
- }
-
- for k, v := range tableData.RowWidthList {
- colName, tmpErr := excelize.ColumnNumberToName(k + 1)
- if tmpErr != nil {
- err = errors.New("获取单元格列名失败,ERR:" + tmpErr.Error())
- err = tmpErr
- return
- }
- err = f.SetColWidth(sheetName, colName, colName, v/10)
- if err != nil {
- err = errors.New("设置列宽度,ERR:" + tmpErr.Error())
- return
- }
- }
-
- for index, v := range tableData.TableDataList {
-
- if tableData.RowHeightList[index] > 0 {
- tmpErr := f.SetRowHeight(sheetName, index+1, tableData.RowHeightList[index]/2)
- if tmpErr != nil {
- err = errors.New("设置单元格高度失败,ERR:" + tmpErr.Error())
- return
- }
- }
- for colunmIndex, cellInfo := range v {
-
- defaultFill := excelize.Fill{
-
- Pattern: 1,
-
- Shading: 0,
- }
- if cellInfo.Background != `` {
- defaultFill.Type = "solid"
- defaultFill.Color = []string{cellInfo.Background}
- }
- cellStyle := &excelize.Style{
- Border: []excelize.Border{
- {Type: "left", Color: "000000", Style: 1},
- {Type: "top", Color: "000000", Style: 1},
- {Type: "bottom", Color: "000000", Style: 1},
- {Type: "right", Color: "000000", Style: 1},
-
-
- },
- Fill: defaultFill,
- Font: getExcelizeFontConf(cellInfo),
- Alignment: getExcelizeAlignmentConf(cellInfo),
-
-
-
-
-
-
- }
- cellStyleId, tmpErr := f.NewStyle(cellStyle)
- if tmpErr != nil {
- err = errors.New("new Style 失败,ERR:" + tmpErr.Error())
- return
- }
- columnName, tmpErr := excelize.ColumnNumberToName(colunmIndex + 1)
- if tmpErr != nil {
- err = errors.New("获取单元格列名失败,ERR:" + tmpErr.Error())
- return
- }
- cellName := columnName + strconv.Itoa(index+1)
- tmpErr = f.SetCellStyle(sheetName, cellName, cellName, cellStyleId)
- if tmpErr != nil {
- err = errors.New("设置style失败,ERR:" + tmpErr.Error())
- return
- }
- valueStr := cellInfo.Monitor
- if valueStr == `` {
-
- if valueStr == `` && cellInfo.CellType.S != nil {
-
- for _, cellS := range cellInfo.CellType.S {
- valueStr += fmt.Sprint(cellS.Value)
- }
- }
- }
- tmpErr = f.SetCellStr(sheetName, cellName, valueStr)
- if tmpErr != nil {
- err = errors.New("设置单元格数据失败,ERR:" + tmpErr.Error())
- return
- }
- }
- }
- for _, v := range tableData.MergeList {
-
- startColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + 1)
- if tmpErr != nil {
- err = tmpErr
- return
- }
- startCellName := startColumnName + strconv.Itoa(v.StartRowIndex+1)
-
- endColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + v.MergeColumnNum + 1)
- if tmpErr != nil {
- err = tmpErr
- return
- }
- endCellName := endColumnName + strconv.Itoa(v.StartRowIndex+v.MergeRowNum+1)
- err = f.MergeCell(sheetName, startCellName, endCellName)
- if err != nil {
- return
- }
- }
-
- f.SetActiveSheet(sheetIndex)
-
- err = f.SaveAs(downloadFilePath)
- return
- }
- func getExcelizeFontConf(cellInfo LuckySheetDataValue) *excelize.Font {
- fontSize := 10
- familyName := ``
-
- underlineStyle := ``
- var isBold, isItalic, isUnderline bool
-
- {
- if cellInfo.Bold == 1 {
- isBold = true
- }
- if cellInfo.Italic == 1 {
- isItalic = true
- }
- if cellInfo.CancelLine == 1 {
- isUnderline = true
- }
-
-
-
-
-
-
- if cellInfo.Fontsize > 0 {
- fontSize = cellInfo.Fontsize
- }
- tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
- if !familyNameOk {
- tmpFamilyName = "宋体"
- }
- familyName = tmpFamilyName
- }
- if isUnderline {
- underlineStyle = "single"
- }
-
-
- return &excelize.Font{
- Bold: isBold,
- Italic: isItalic,
- Underline: underlineStyle,
- Family: familyName,
- Size: float64(fontSize),
- Strike: isUnderline,
- Color: cellInfo.FontColor,
- VertAlign: "",
- }
- }
- func getExcelizeAlignmentConf(cellInfo LuckySheetDataValue) *excelize.Alignment {
-
- isWrapText := false
- if cellInfo.TextBeak == 2 {
- isWrapText = true
- }
- rotateText := 0
-
-
-
-
-
-
- return &excelize.Alignment{
- Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
-
-
-
-
-
- TextRotation: rotateText,
- Vertical: LuckyVerticalMap[cellInfo.VerticalType],
- WrapText: isWrapText,
- }
- }
- func getColor(bgStr string) string {
- if strings.Contains(bgStr, "(") {
- arr := strings.Split(bgStr, ",")
- if len(arr) != 3 {
- return bgStr
- }
-
- tmpFirstArr := strings.Split(arr[0], "(")
- arr[0] = tmpFirstArr[len(tmpFirstArr)-1]
-
- tmpLastArr := strings.Split(arr[2], ")")
- arr[2] = tmpLastArr[0]
- rgbArr := make([]int64, 0)
- for _, v := range arr {
- tmpInt, err := strconv.Atoi(utils.TrimStr(v))
- if err != nil {
- return bgStr
- }
- rgbArr = append(rgbArr, int64(tmpInt))
- }
- rgb := RGB{
- rgbArr[0],
- rgbArr[1],
- rgbArr[2],
- }
- bgStr = "" + rgb.Rgb2Hex().Str
- }
- return bgStr
- }
- func GetTableDataByCustomData(excelType int, data request.TableDataReq) (selfTableData TableData, err error) {
- tableDataList := make([][]LuckySheetDataValue, 0)
- mergeList := make([]TableDataMerge, 0)
-
- lenEdb := len(data.Data)
- if lenEdb <= 0 {
- return
- }
-
- lenCol := len(data.Data[0].Data)
- if lenCol <= 0 {
- return
- }
-
- if excelType == 1 {
- mergeList = append(mergeList, TableDataMerge{
- StartRowIndex: 0,
- StartColumnIndex: 0,
- MergeRowNum: 1,
- MergeColumnNum: 0,
- })
-
- {
- firstCol := make([]LuckySheetDataValue, 0)
- firstCol = append(firstCol, LuckySheetDataValue{
- Value: "日期",
- Monitor: "日期",
- MergeCell: LuckySheetDataConfigMerge{
- Row: 0,
- Column: 0,
- Rs: 2,
- Cs: 1,
- },
- })
- for _, v := range data.Data {
- edbName := v.EdbName
- if v.EdbAliasName != `` {
- edbName = v.EdbAliasName
- }
- firstCol = append(firstCol, LuckySheetDataValue{
- Value: edbName,
- Monitor: edbName,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
- tableDataList = append(tableDataList, firstCol)
- }
-
- {
- secondCol := make([]LuckySheetDataValue, 0)
- secondCol = append(secondCol, LuckySheetDataValue{})
- for _, v := range data.Data {
- name := v.Unit + " / " + v.Frequency
- secondCol = append(secondCol, LuckySheetDataValue{
- Value: name,
- Monitor: name,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
- tableDataList = append(tableDataList, secondCol)
- }
-
- {
- for i := 0; i < lenCol; i++ {
- dataCol := make([]LuckySheetDataValue, 0)
-
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: data.Data[0].Data[i].DataTime,
- Monitor: data.Data[0].Data[i].DataTime,
- MergeCell: LuckySheetDataConfigMerge{},
- })
-
- for _, v := range data.Data {
- background := ``
- if v.Data[i].DataType == 5 {
- background = "#ffefdd"
- }
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: v.Data[i].Value,
- Monitor: v.Data[i].ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- Background: background,
- })
- }
- tableDataList = append(tableDataList, dataCol)
- }
- }
-
- {
- for _, textColList := range data.TextRowData {
- dataCol := make([]LuckySheetDataValue, 0)
- for _, v := range textColList {
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: v.Value,
- Monitor: v.ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
- tableDataList = append(tableDataList, dataCol)
- }
- }
- } else {
-
- mergeList = append(mergeList, TableDataMerge{
- StartRowIndex: 0,
- StartColumnIndex: 0,
- MergeRowNum: 0,
- MergeColumnNum: 1,
- })
-
- {
- firstCol := make([]LuckySheetDataValue, 0)
- firstCol = append(firstCol, LuckySheetDataValue{
- Value: "日期",
- Monitor: "日期",
- MergeCell: LuckySheetDataConfigMerge{
- Row: 0,
- Column: 0,
- Rs: 1,
- Cs: 2,
- },
- })
- firstCol = append(firstCol, LuckySheetDataValue{})
-
- for _, v := range data.Data[0].Data {
- firstCol = append(firstCol, LuckySheetDataValue{
- Value: v.DataTime,
- Monitor: v.DataTime,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
-
- for _, textColList := range data.TextRowData {
- firstCol = append(firstCol, LuckySheetDataValue{
- Value: textColList[0].Value,
- Monitor: textColList[0].ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
- tableDataList = append(tableDataList, firstCol)
- }
-
-
- for i := 0; i < lenEdb; i++ {
- dataCol := make([]LuckySheetDataValue, 0)
-
- tmpEdbInfo := data.Data[i]
-
- {
- edbName := tmpEdbInfo.EdbName
- if tmpEdbInfo.EdbAliasName != `` {
- edbName = tmpEdbInfo.EdbAliasName
- }
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: edbName,
- Monitor: edbName,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
-
- {
- name := tmpEdbInfo.Unit + " / " + tmpEdbInfo.Frequency
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: name,
- Monitor: name,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
-
- for _, tmpData := range tmpEdbInfo.Data {
- background := ``
- if tmpData.DataType == 5 {
- background = "#ffefdd"
- }
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: tmpData.Value,
- Monitor: tmpData.ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- Background: background,
- })
- }
-
- for _, textColList := range data.TextRowData {
- dataCol = append(dataCol, LuckySheetDataValue{
- Value: textColList[i+1].Value,
- Monitor: textColList[i+1].ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- })
- }
- tableDataList = append(tableDataList, dataCol)
- }
- }
- selfTableData.MergeList = mergeList
- selfTableData.TableDataList = tableDataList
- return
- }
- func GetTableDataByMixedTableData(config [][]request.MixedTableCellDataReq, hideMerged bool) (selfTableData TableData, err error) {
- tableDataList := make([][]LuckySheetDataValue, 0)
- mergeList := make([]TableDataMerge, 0)
-
- {
- for _, row := range config {
- dataCol := make([]LuckySheetDataValue, 0)
- for _, cell := range row {
- tmp := LuckySheetDataValue{
- Value: cell.Value,
- Monitor: cell.ShowValue,
- MergeCell: LuckySheetDataConfigMerge{},
- }
-
- if cell.MerData != nil {
- if hideMerged && cell.MerData.Type == "merged" {
- continue
- }
- tmp.MergeCell.Rs = cell.MerData.Mer.Rowspan
- tmp.MergeCell.Cs = cell.MerData.Mer.Colspan
- tmp.MergeCell.Row = cell.MerData.Mer.Row
- tmp.MergeCell.Column = cell.MerData.Mer.Col
- }
- if cell.ShowStyle != "" {
- showFormatValue := fmt.Sprintf("%v", cell.ShowFormatValue)
- tmp.Monitor = showFormatValue
- }
- dataCol = append(dataCol, tmp)
- }
- tableDataList = append(tableDataList, dataCol)
- }
- }
- selfTableData.MergeList = mergeList
- selfTableData.TableDataList = tableDataList
- return
- }
- func HandleRuleToTableCell(excelInfoId int, oldTableData TableData) (newTableData TableData, err error) {
- newTableData = oldTableData
- excelRuleMappingList, err := excel.GetExcelRuleMappingByExcelInfoId(excelInfoId)
- if err != nil {
- return
- }
- if len(excelRuleMappingList) == 0 {
- return
- }
- tableDataList := oldTableData.TableDataList
- excelRuleMap := make(map[int]*excel.ExcelInfoRuleMappingView)
- for _, v := range excelRuleMappingList {
- excelRuleMap[v.ExcelInfoRuleMappingId] = v
- }
- ruleScopeMap := generateRuleScopeIndexMap(excelRuleMappingList)
- for row, scopeValues := range ruleScopeMap {
- for col, ruleId := range scopeValues {
- if v, ok := excelRuleMap[ruleId]; ok {
- if len(tableDataList) > row && len(tableDataList[row]) > col {
-
- if checkCellRule(v, tableDataList[row][col].Monitor, tableDataList) {
- tableDataList[row][col].Background = v.BackgroundColor
- tableDataList[row][col].FontColor = v.FontColor
- }
- } else {
- continue
- }
- } else {
- continue
- }
- }
- }
- return
- }
- func getCellValueByType(value string, valueType int, tableDataList [][]LuckySheetDataValue) (float64, bool) {
- if valueType == 2 {
- coords := strings.Split(value, ",")
- var coordIntArr []int
- for _, v := range coords {
- t, _ := strconv.Atoi(v)
- coordIntArr = append(coordIntArr, t)
- }
- if len(coordIntArr) == 2 {
- x, y := coordIntArr[0]-1, coordIntArr[1]-1
- conditionValue, err := strconv.ParseFloat(tableDataList[y][x].Monitor, 64)
- if err != nil {
- return 0, false
- }
- return conditionValue, true
- }
- } else {
- conditionValue, err := strconv.ParseFloat(value, 64)
- if err != nil {
- return 0, false
- }
- return conditionValue, true
- }
- return 0, false
- }
- func checkCellRule(ruleInfo *excel.ExcelInfoRuleMappingView, value string, tableDataList [][]LuckySheetDataValue) bool {
- var tableValue float64
- var tableTime time.Time
- var err error
- if ruleInfo.RuleType == 5 {
- tableTime, err = time.ParseInLocation(utils.FormatDate, value, time.Local)
- if err != nil {
- return false
- }
- } else {
- tableValue, err = strconv.ParseFloat(value, 64)
- if err != nil {
- return false
- }
- }
- switch ruleInfo.RuleType {
-
- case 1:
- conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
- if !ok {
- return false
- }
- if tableValue > conditionValue {
- return true
- }
-
- case 2:
- conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
- if !ok {
- return false
- }
- if tableValue < conditionValue {
- return true
- }
-
- case 3:
- leftcondValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
- if !ok {
- return false
- }
- rightcondValue, ok := getCellValueByType(ruleInfo.RightValueBack, ruleInfo.RightValueType, tableDataList)
- if !ok {
- return false
- }
- if leftcondValue <= tableValue && tableValue <= rightcondValue {
- return true
- }
-
- case 4:
- conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
- if !ok {
- return false
- }
- if tableValue == conditionValue {
- return true
- }
-
- case 5:
-
- var dateStart, dataEnd time.Time
- switch ruleInfo.LeftValueBack {
-
- case "today":
-
- dateStart = utils.Today()
- if tableTime == dateStart {
- return true
- }
-
- case "tomorrow":
- dateStart = utils.Tomorrow()
- if tableTime == dateStart {
- return true
- }
-
- case "last7days":
- dateStart, dataEnd = utils.Last7Days()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "lastweek":
- dateStart, dataEnd = utils.LastWeek()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "thisweek":
- dateStart, dataEnd = utils.ThisWeek()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "nextweek":
- dateStart, dataEnd = utils.NextWeek()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "lastmonth":
- dateStart, dataEnd = utils.LastMonth()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "thismonth":
- dateStart, dataEnd = utils.ThisMonth()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
-
- case "nextmonth":
- dateStart, dataEnd = utils.NextMonth()
- if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
- return true
- }
- default:
- return false
- }
- }
- return false
- }
- func generateRuleScopeIndexMap(items []*excel.ExcelInfoRuleMappingView) (ruleScopeMap map[int]map[int]int) {
- ruleScopeMap = make(map[int]map[int]int)
- for _, item := range items {
- coords := strings.Split(item.ScopeCoord, ",")
- var coordIntArr []int
- for _, v := range coords {
- t, _ := strconv.Atoi(v)
- coordIntArr = append(coordIntArr, t)
- }
- if len(coords) == 4 {
- xmin, ymin, xmax, ymax := coordIntArr[0]-1, coordIntArr[1]-1, coordIntArr[2]-1, coordIntArr[3]-1
- for i := ymin; i <= ymax; i++ {
- for j := xmin; j <= xmax; j++ {
- if _, ok := ruleScopeMap[i]; !ok {
- ruleScopeMap[i] = make(map[int]int)
- }
- ruleScopeMap[i][j] = item.ExcelInfoRuleMappingId
- }
- }
- }
- if len(coords) == 2 {
- x, y := coordIntArr[0]-1, coordIntArr[1]-1
- if _, ok := ruleScopeMap[y]; !ok {
- ruleScopeMap[y] = make(map[int]int)
- }
- ruleScopeMap[y][x] = item.ExcelInfoRuleMappingId
- }
- }
- return
- }
|