1. Demand
Party A’s requirement is to export a work start notice in this format
The front page looks like this:
The front-end select some departments, and the back-end searches according to the names of the selected departments, and fills the name of the head of the department in the corresponding position of the table, and reports the information from the front-end are filled in the specified position of the form.
First show the final exported result:
2. Solution idea
1. The front end transmits data in the form of form-data, and the back end uses the PostForm method of the gin framework to receive parameters and fill the parameters into the specified position
2. The front end transmits some departments, and the back end judges these departments, finds the department director through the department name, and fills the department director into the corresponding position
3. Export the library excelize used by excel:
go get github.com/xuri/excelize/v2
3. Source code and result display
1. Project structure
2. Download the excel file (source code) on the web page
package file
import (
"fmt"
"github.com/gin-gonic/gin"
)
func ExcelDown(ctx *gin.Context, filepath string, filename string) {
ctx.Writer.Header().Add("Content-Disposition", fmt.Sprintf("attachment; filename=%s", filename))
ctx.Writer.Header().Add("Content-Type", "application/msexcel")
ctx.File(filepath)
}
3. Export excel file
1. How to configure swagger and the steps and logic of exporting excel are written very clearly, see the code comments for details
package controller
import (
"fmt"
"ginEssential/common"
"ginEssential/file"
"ginEssential/model"
"ginEssential/response"
"github.com/gin-gonic/gin"
"github.com/xuri/excelize/v2"
"strings"
"time"
)
//Export start notice to excel
// @Summary Export start notice to excel
// @Description input information for export
// @Tags start notification management
// @Accept multipart/form-data
// @Produce application/msexcel
// @Param informNumber formData string true "notification number"
// @Param projectName formData string true "project name"
// @Param designPhase formData string true "design phase"
// @Param projectNumber formData string true "Project Number"
// @Param startTime formData string true "start time"
// @Param completionTime formData string true "completion time"
// @Param designBasis formData string true "design basis"
// @Param departments formData string true "Professional Departments"
// @Param remark formData string true "remark"
// @Param technology formData string true "Technical Support Department"
// @Router /informs/excel [post]
func InformExcel(ctx *gin.Context) {
DB := common. GetDB()
//Create a new workbook
f := excelize. NewFile()
// Declare the name of the worksheet
sheetName := "Commencement Notice"
f.SetSheetName("Sheet1", sheetName)
//Add data to the table
//Get the current time and fill in the header
now := time. Now()
currentDate := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
nowDate := currentDate. Format("2006-1-2")
// Get the notification number
informNumber := ctx.PostForm("informNumber")
//Get the project name
projectName := ctx.PostForm("projectName")
// get design stage
designPhase := ctx.PostForm("designPhase")
//Get engineering code
projectNumber := ctx.PostForm("projectNumber")
// get start time
startTime := ctx.PostForm("startTime")
// get completion time
completionTime := ctx.PostForm("completionTime")
// Get design basis
designBasis := ctx. PostForm("designBasis")
// Get which professional departments are sent from the front end
departments := ctx. PostForm("departments")
fmt.Println(departments)
//Determine which departments are inside
var department model. Department
//Judge whether there is a planning room inside
if strings.Contains(departments, "Planning Office") {
//According to the Planning Office Inquiry Office Director
if err := DB.Where("department_name = ?", "Planning Office").First( & amp;department).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
planDirector := department. DepartmentDirector
var department1 model.Department
if strings.Contains(departments, "Electrical Room") {
if err := DB.Where("department_name = ?", "Electrical Room").First( & amp;department1).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
electricalDirector := department1. DepartmentDirector
var department2 model. Department
if strings.Contains(departments, "transmission room") {
if err := DB.Where("department_name = ?", "transmission room").First( & amp;department2).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
transmissionDirector := department2. DepartmentDirector
var department3 model.Department
if strings.Contains(departments, "civil engineering room") {
if err := DB.Where("department_name = ?", "civil engineering room").First( & amp;department3).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
constructDirector := department3. DepartmentDirector
var department4 model.Department
if strings.Contains(departments, "power distribution room") {
if err := DB.Where("department_name = ?", "power distribution room").First( & amp;department4).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
distributeDirector := department4. DepartmentDirector
var department5 model.Department
if strings.Contains(departments, "Technical Department") {
if err := DB.Where("department_name = ?", "Technology Room").First( & amp;department5).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
technologyDirector := department5. DepartmentDirector
var department6 model.Department
if strings.Contains(departments, "review room") {
if err := DB.Where("department_name = ?", "Review Room").First( & amp;department6).Error; err != nil {
response.Fail(ctx, "The department does not exist!", nil)
return
}
}
evaluateDirector := department6. DepartmentDirector
// get notes
remark := ctx.PostForm("remark")
//Get technical support department
technology := ctx.PostForm("technology")
data := [][]interface{}{
{"Engineering Project Commencement Notice"},
{"Time:", nowDate, nil, nil, nil, "Number:", informNumber, nil, nil},
{"Project Name", projectName, nil, nil},
{"Design Phase", designPhase, nil, nil, nil, "Engineering Code", projectNumber, nil},
{"Project Plan", "Start Time", startTime, nil, nil, "Completion Time", completionTime, nil},
{"Design Basis", designBasis, nil},
{nil, "Planning Office", nil, nil, nil, "Department Director", planDirector, nil},
{nil, "Electrical Director", nil, nil, nil, "Director", electricalDirector, nil},
{nil, "Transmission Room", nil, nil, nil, "Director", transmissionDirector, nil},
{"Professional Department Office", "Civil Engineering Office", nil, nil, nil, "Department Director", constructDirector, nil},
{nil, "Power distribution room", nil, nil, nil, "Director", distributeDirector, nil},
{nil, "Technology Department", nil, nil, nil, "Director", technologyDirector, nil},
{nil, "Review Office", nil, nil, nil, "Department Director", evaluateDirector, nil},
{"Remark", remark, nil},
{"Technical Support Department", technology, nil},
}
//Loop through this two-dimensional array
for i, row := range data {
//When assigning values by column, you need to specify the starting storage coordinates
startCell, err := excelize. JoinCellName("A", i + 1)
if err != nil {
fmt.Println(err)
return
}
if err := f.SetSheetRow(sheetName, startCell, & amp; row); err != nil {
fmt.Println(err)
return
}
}
\t//Merge Cells
mergeCellRanges := [][]string{
{"A1", "I1"},
{"B2", "E2"},
{"G2", "I2"},
{"B3", "I3"},
{"B4", "E4"},
{"G4", "I4"},
{"C5", "E5"},
{"G5", "I5"},
{"B6", "I6"},
{"C7", "E7"},
{"G7", "I7"},
{"C8", "E8"},
{"G8", "I8"},
{"C9", "E9"},
{"G9", "I9"},
{"C10", "E10"},
{"G10", "I10"},
{"C11", "E11"},
{"G11", "I11"},
{"C12", "E12"},
{"G12", "I12"},
{"C13", "E13"},
{"G13", "I13"},
{"B14", "I14"},
{"B15", "I15"},
{"A7", "A9"},
{"A11", "A13"},
}
for _, ranges := range mergeCellRanges {
if err := f.MergeCell(sheetName, ranges[0], ranges[1]); err != nil {
fmt.Println(err)
return
}
}
//Create format, center alignment
style1, err := f.NewStyle( & amp;excelize.Style{
Alignment: &excelize.Alignment{Horizontal: "center"},
})
if err != nil {
fmt.Println(err)
return
}
//Apply the style to a specific cell
if f.SetCellStyle(sheetName, "A1", "I15", style1); err != nil {
fmt.Println(err)
return
}
//Save the generated excel
// Use the current time to name the file
currentTime1 := time.Now().Format("20060102-150405")
kai := "start"
filename := kai + currentTime1 + ".xlsx"
filepath := "Book1.xlsx"
err5 := f.SaveAs(filepath)
// Save excel according to the given path
if err5 != nil {
response.Fail(ctx, "Export failed", nil)
} else {
\t\t//download
file.ExcelDown(ctx, filepath, filename)
}
}
2. Parameter passing and result display:
