1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396 |
- package excel
- import (
- "encoding/json"
- "errors"
- "fmt"
- "github.com/tealeg/xlsx"
- "github.com/xuri/excelize/v2"
- "hongze/hongze_ETA_mobile_api/utils"
- "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缩放比例"`
- }
- // LuckySheetData sheet表格数据
- type LuckySheetData struct {
- CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
- ChWidth int64 `json:"ch_width" description:"工作表区域的宽度"`
- Config LuckySheetDataConfig `json:"config" description:""`
- //Index int `json:"index" 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缩放比例"`
- }
- // LuckySheetDataConfig 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:"每个单元格的列宽"`
- //CustomHeight struct {
- // Zero int64 `json:"0"`
- //} `json:"customHeight" description:""`
- //CustomWidth struct {
- // Two int64 `json:"2" description:""`
- //} `json:"customWidth" description:""`
- Merge map[string]LuckySheetDataConfigMerge `json:"merge" description:"合并单元格"`
- Rowlen map[string]float64 `json:"rowlen" description:"每个单元格的行高"`
- }
- // LuckySheetDataConfigMerge 合并单元格设置
- type LuckySheetDataConfigMerge struct {
- Row int `json:"r" description:"行数"`
- Column int `json:"c" description:"列数"`
- Rs int `json:"rs" description:"合并的行数"`
- Cs int `json:"cs" description:"合并的列数"`
- }
- // LuckySheetDataConfigBorderInfo 单元格边框信息
- 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:"范围类型分单个单元格的数据"`
- }
- // LuckySheetDataConfigBorderInfoCellValue 单元格边框信息(范围类型为:单个单元格)
- 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:"第几列"`
- }
- // LuckySheetDataConfigBorderInfoCell 单元格边框信息(cell类型)
- 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"`
- }
- // LuckySheetCellData 单个单元格数据
- type LuckySheetCellData struct {
- Col int64 `json:"c" description:"列"`
- Row int64 `json:"r" description:"行"`
- Value LuckySheetDataValue `json:"v" description:"单元格内值的数据"`
- }
- // LuckySheetDataValue 单元格内值的数据
- 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下"`
- //TextRotate string `json:"tr" description:"竖排文字, 3"`
- //RotateText string `json:"rt" description:"文字旋转角度, 介于0~180之间的整数,包含0和180"`
- 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:"合并单元格信息"`
- }
- // LuckySheetDataCellType 单元格值格式:文本、时间等
- 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"`
- }
- // LuckySheetDataCellComment 批注
- 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:"是否显示批注"`
- }
- // GetLuckySheetData 获取LuckySheetData的结构体
- 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
- }
- // TableData 表格数据
- type TableData struct {
- TableDataList [][]LuckySheetDataValue
- RowWidthList []float64
- RowHeightList []float64
- RemoveTopRow int `description:"移除表格上方的行数"`
- RemoveBottomRow int `description:"移除表格下方的行数"`
- RemoveLeftColumn int `description:"移除表格左侧的列数"`
- RemoveRightColumn int `description:"移除表格右侧的列数"`
- MergeList []TableDataMerge `description:"合并数据列"`
- }
- // TableDataMerge 表格数据合并单元格配置
- 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:"合并的列数"`
- }
- // GetTableDataByLuckySheetDataStr 通过LuckySheet的string数据获取表格数据
- func (item *LuckySheetData) GetTableDataByLuckySheetDataStr() (selfTableData TableData, err error) {
- luckySheetCellDataList := item.CellData
- // 表格数据
- tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
- // 最大行,最大列
- var maxRow, maxCol int64
- for _, v := range luckySheetCellDataList {
- //fmt.Println("row:", v.Row, "=====col:", v.Col)
- 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)
- }
- // 数据处理,移除上下左右空行空列
- 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
- }
- // TableRemoveNum 上下左右移除的空行空列数量
- type TableRemoveNum struct {
- RemoveTopRow int `description:"移除表格上方的行数"`
- RemoveBottomRow int `description:"移除表格下方的行数"`
- RemoveLeftColumn int `description:"移除表格左侧的列数"`
- RemoveRightColumn int `description:"移除表格右侧的列数"`
- }
- // handleTableDataList 表格数据处理(移除上下左右的空行空列)
- 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)
- 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:]...) // 删除开头N个元素
- //表格高度
- rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
- }
- }
- }
- //移除左右空列
- 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)
- 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:]...) // 删除开头N个元素
- }
- //表格宽度
- rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
- }
- }
- }
- //处理合并单元格
- 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()
- //dir, err := os.Executable()
- //exPath := filepath.Dir(dir)
- //downloadFilePath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
- downloadFilePath, err = getDownloadPath()
- if err != nil {
- return
- }
- xlsxFile := xlsx.NewFile()
- if err != nil {
- return
- }
- style := xlsx.NewStyle()
- alignment := xlsx.Alignment{
- Horizontal: "center",
- Vertical: "center",
- WrapText: true,
- }
- style.Alignment = alignment
- style.ApplyAlignment = true
- sheet, err := xlsxFile.AddSheet("sheet1")
- if err != nil {
- return
- }
- for k, v := range tableData.RowWidthList {
- err = sheet.SetColWidth(k, k, v/10)
- if err != nil {
- return
- }
- }
- for index, v := range tableData.TableDataList {
- tmpRow := sheet.AddRow()
- //设置单元格高度
- if tableData.RowHeightList[index] > 0 {
- tmpRow.SetHeight(tableData.RowHeightList[index] / 2)
- }
- for _, cellInfo := range v {
- tmpStyle := new(xlsx.Style)
- //xlsx.Style{
- // Fill: xlsx.Fill{},
- // ApplyBorder: false,
- // ApplyFill: false,
- // ApplyFont: false,
- // NamedStyleIndex: nil,
- //}
- //fill := *NewFill("solid", "FF000000", "00FF0000")
- defaultFill := xlsx.DefaultFill()
- if cellInfo.Background != `` {
- defaultFill.PatternType = "solid"
- backgroundColor := cellInfo.Background
- // 这么做是为了避免传入的是RGB的格式(rgb(255, 255, 255))
- 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",
- }
- //tmpStyle.ApplyAlignment = true
- //tmpStyle.Alignment.WrapText = true
- tmpRow := tmpRow.AddCell()
- tmpRow.SetStyle(tmpStyle)
- valueStr := cellInfo.Monitor
- if valueStr == `` {
- //valueStr = fmt.Sprint(cellInfo.Value)
- if valueStr == `` && cellInfo.CellType.S != nil {
- //不是设置在单元格上面,而是设置在文本上
- for _, cellS := range cellInfo.CellType.S {
- valueStr += fmt.Sprint(cellS.Value)
- }
- }
- }
- 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
- err = xlsxFile.Save(downloadFilePath)
- if err != nil {
- return
- }
- //randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
- //downloadFileName := "即将到期客户数据_" + randStr + ".xlsx"
- //this.Ctx.Output.Download(downLoadnFilePath, downloadFileName)
- return
- }
- // getDownloadPath 获取下载地址
- func getDownloadPath() (fpath string, err error) {
- dateDir := time.Now().Format("20060102")
- uploadDir := "static/xls/" + dateDir
- err = os.MkdirAll(uploadDir, 0766)
- if err != nil {
- return
- }
- randStr := utils.GetRandStringNoSpecialChar(28)
- fileName := randStr + ".xlsx"
- fpath = uploadDir + "/" + fileName
- return
- }
- // getExcelFontConf 获取表格字体配置
- 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 != `` {
- // convSize, _ := strconv.Atoi(cellInfo.Fontsize)
- // if convSize > 0 {
- // fontSize = convSize
- // }
- //}
- if cellInfo.Fontsize > 0 {
- fontSize = cellInfo.Fontsize
- }
- tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
- if !familyNameOk {
- tmpFamilyName = "宋体"
- }
- familyName = tmpFamilyName
- }
- return xlsx.Font{
- Size: fontSize,
- Name: familyName,
- //Family: v2.FontFamily,
- //Charset: 0,
- Color: strings.TrimPrefix(cellInfo.FontColor, "#"),
- Bold: isBold,
- Italic: isItalic,
- Underline: isUnderline,
- }
- }
- // getExcelAlignmentConf 获取表格单元格排列配置
- func getExcelAlignmentConf(cellInfo LuckySheetDataValue) xlsx.Alignment {
- //自动换行
- isWrapText := false
- if cellInfo.TextBeak == 2 {
- isWrapText = true
- }
- rotateText := 0
- //if cellInfo.RotateText != `` {
- // convSize, _ := strconv.Atoi(cellInfo.RotateText)
- // if convSize > 0 {
- // rotateText = convSize
- // }
- //}
- return xlsx.Alignment{
- Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
- //Indent: 0,
- //ShrinkToFit: false,
- TextRotation: rotateText,
- Vertical: LuckyVerticalMap[cellInfo.VerticalType],
- WrapText: isWrapText,
- }
- }
- // getExcelBorderConf 获取表格边框配置
- //func getExcelBorderConf(list []LuckySheetDataConfigBorderInfo) (v xlsx.Border) {
- // // 字体
- // for _, v := range list {
- // //v.BorderType
- // //v.Style
- //
- // if v.RangeType == "cell" {
- //
- // xlsx.Border{
- // Left: LuckyBorderMap[v.Value.L.Style],
- // LeftColor: strings.TrimPrefix(v.Value.L.Color, "#"),
- // Right: LuckyBorderMap[v.Value.R.Style],
- // RightColor: strings.TrimPrefix(v.Value.R.Color, "#"),
- // Top: LuckyBorderMap[v.Value.T.Style],
- // TopColor: strings.TrimPrefix(v.Value.T.Color, "#"),
- // Bottom: LuckyBorderMap[v.Value.B.Style],
- // BottomColor: strings.TrimPrefix(v.Value.B.Color, "#"),
- // }
- // } else {
- // xlsx.Border{
- // Left: LuckyBorderMap[v.Style],
- // LeftColor: strings.TrimPrefix(v.Color, "#"),
- // Right: LuckyBorderMap[v.Style],
- // RightColor: strings.TrimPrefix(v.Color, "#"),
- // Top: LuckyBorderMap[v.Style],
- // TopColor: strings.TrimPrefix(v.Color, "#"),
- // Bottom: LuckyBorderMap[v.Style],
- // BottomColor: strings.TrimPrefix(v.Color, "#"),
- // }
- // }
- // //v.Value.T.Style
- //
- // }
- //
- // return
- //}
- // LuckyFontFamilyMap 字体map
- var LuckyFontFamilyMap = map[int]string{
- 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: "华文隶书",
- }
- // LuckyBorderMap 边框map
- var LuckyBorderMap = map[string]string{
- "": "Thin",
- "1": "Thin",
- "2": "Hair",
- "3": "Dotted",
- "4": "Dashed",
- "5": "DashDot", //宋体(Song)、
- "6": "DashDotDot", // 黑体(ST Heiti)
- "7": "Double", //楷体(ST Kaiti),
- "8": "Medium", //仿宋(ST FangSong),
- "9": "MediumDashed", //新宋体(ST Song),
- "10": "MediumDashDot",
- "11": "MediumDashDotDot",
- "12": "SlantedDashDot",
- "13": "Thick",
- }
- // LuckyHorizontalMap 水平对齐map
- var LuckyHorizontalMap = map[int]string{
- //"": "general",
- 0: "center",
- 1: "left",
- 2: "right",
- }
- // LuckyVerticalMap 垂直对齐map
- var LuckyVerticalMap = map[int]string{
- //"": "center",
- 0: "center",
- 1: "top",
- //2: "bottom",
- }
- // GetColumnName Return the ColumnName
- 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
- // Convert int to char
- colName = string(65+modulo) + colName
- dividend = (int)((dividend - modulo) / 26)
- }
- colName = colName + strconv.Itoa(rowNumber)
- return
- }
- // GetTableDataByLuckySheetDataStrBak 通过LuckySheet的string数据获取表格数据(备份:2022-08-23 10:30:32)
- 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 {
- //fmt.Println("row:", v.Row, "=====col:", v.Col)
- 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:]...) // 删除开头N个元素
- //表格高度
- rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
- }
- }
- }
- //移除左右空列
- 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:]...) // 删除开头N个元素
- }
- //表格宽度
- rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
- }
- }
- }
- //处理合并单元格
- 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()
- downloadFilePath, err = getDownloadPath()
- if err != nil {
- return
- }
- f := excelize.NewFile()
- // Create a new sheet.
- sheetName := `Sheet1`
- sheetIndex, _ := f.NewSheet(sheetName)
- //设置列宽度
- 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 {
- //f.NewStyle
- defaultFill := excelize.Fill{
- //Type: "solid",
- Pattern: 1,
- //Color: []string{cellInfo.Background},
- 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},
- //{Type: "diagonalDown", Color: "A020F0", Style: 7},
- //{Type: "diagonalUp", Color: "A020F0", Style: 8},
- },
- Fill: defaultFill,
- Font: getExcelizeFontConf(cellInfo),
- Alignment: getExcelizeAlignmentConf(cellInfo),
- //Protection: nil,
- //NumFmt: 0,
- //DecimalPlaces: 0,
- //CustomNumFmt: nil,
- //Lang: "",
- //NegRed: false,
- }
- 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 == `` {
- //valueStr = fmt.Sprint(cellInfo.Value)
- 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
- }
- }
- // Set active sheet of the workbook.
- f.SetActiveSheet(sheetIndex)
- // Save spreadsheet by the given path.
- err = f.SaveAs(downloadFilePath)
- return
- }
- // getExcelizeFontConf 获取表格字体配置
- func getExcelizeFontConf(cellInfo LuckySheetDataValue) *excelize.Font {
- fontSize := 10
- familyName := ``
- //single 单线 、double 双线
- 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 != `` {
- // convSize, _ := strconv.Atoi(cellInfo.Fontsize)
- // if convSize > 0 {
- // fontSize = convSize
- // }
- //}
- if cellInfo.Fontsize > 0 {
- fontSize = cellInfo.Fontsize
- }
- tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily]
- if !familyNameOk {
- tmpFamilyName = "宋体"
- }
- familyName = tmpFamilyName
- }
- if isUnderline {
- underlineStyle = "single"
- }
- //VertAlign取值:
- //BASELINE、superscript、subscript
- return &excelize.Font{
- Bold: isBold,
- Italic: isItalic,
- Underline: underlineStyle,
- Family: familyName,
- Size: float64(fontSize),
- Strike: isUnderline,
- Color: cellInfo.FontColor,
- VertAlign: "",
- }
- }
- // getExcelizeAlignmentConf 获取表格单元格排列配置
- func getExcelizeAlignmentConf(cellInfo LuckySheetDataValue) *excelize.Alignment {
- //自动换行
- isWrapText := false
- if cellInfo.TextBeak == 2 {
- isWrapText = true
- }
- rotateText := 0
- //if cellInfo.RotateText != `` {
- // convSize, _ := strconv.Atoi(cellInfo.RotateText)
- // if convSize > 0 {
- // rotateText = convSize
- // }
- //}
- return &excelize.Alignment{
- Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType],
- //Indent: 0,
- //JustifyLastLine: false,
- //ReadingOrder: 0,
- //RelativeIndent: 0,
- //ShrinkToFit: false,
- TextRotation: rotateText,
- Vertical: LuckyVerticalMap[cellInfo.VerticalType],
- WrapText: isWrapText,
- }
- }
- // getColor 获取hex颜色
- 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
- }
|