From 351a8ba75519d18a8afe06f8a3a3f239f3a62a64 Mon Sep 17 00:00:00 2001
From: Matthew Lemon <y@yulqen.org>
Date: Fri, 31 May 2024 21:22:00 +0100
Subject: Can now parse a return XLSX file

We can now submit a datamap.csv and a single return xlsx file using
the following command, and get a Return JSON string back with the parsed date:

`curl -X POST -F "file=@./datamap.csv" -F "returnfile=@./resources/test_two_sheets.xlsm" \
-F "name=bobbins" -F "description=This is a long description of the datamap." \
http://localhost:5000/v1/return`

I have renamed the old test.xlsm file here to test_two_sheets.xlsm -
having remove a sheet from the test.xlsm. I did this to ensure that the
parsing handles multiple sheets which it does.

Still a fair amount of clearning up here but if you run the server and
send in this request, it works.
---
 .gitignore                     |   1 +
 cmd/dbasik-api/handlers.go     |  41 ++++++++++++++++++++---------------------
 datamap.csv                    |   6 +++---
 resources/test_two_sheets.xlsm | Bin 0 -> 30333 bytes
 4 files changed, 24 insertions(+), 24 deletions(-)
 create mode 100644 resources/test_two_sheets.xlsm

diff --git a/.gitignore b/.gitignore
index 4a553c2..b4484d8 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,4 @@
 bin/
 .env
 resources/test.xlsm
+tags
diff --git a/cmd/dbasik-api/handlers.go b/cmd/dbasik-api/handlers.go
index b6b4443..65bf2c4 100644
--- a/cmd/dbasik-api/handlers.go
+++ b/cmd/dbasik-api/handlers.go
@@ -5,12 +5,12 @@ import (
 	"encoding/json"
 	"fmt"
 	"io"
+	"io/ioutil"
 	"net/http"
 	"os"
+	"path"
 	"strconv"
 	"time"
-
-	"github.com/tealeg/xlsx/v3"
 )
 
 func (app *application) createReturnHandler(w http.ResponseWriter, r *http.Request) {
@@ -25,21 +25,31 @@ func (app *application) createReturnHandler(w http.ResponseWriter, r *http.Reque
 	dmDesc := r.FormValue("description")
 
 	// Get the return file and save it to a file
-	returnFile, handler, err := r.FormFile("returnfile")
+	returnFile, header, err := r.FormFile("returnfile")
 	app.logger.Info("got excel file")
 	if err != nil {
 		http.Error(w, "Missing file", http.StatusBadRequest)
 		return
 	}
 	defer returnFile.Close()
-	dst, err := os.Create(handler.Filename)
+
+	tmpDir, err := ioutil.TempDir("", "dbasik-returns")
+	if err != nil {
+		http.Error(w, err.Error(), http.StatusInternalServerError)
+		return
+	}
+	defer os.RemoveAll(tmpDir) // clean the tempdir up
+
+	dst, err := os.Create(path.Join(tmpDir, header.Filename))
 	if err != nil {
 		http.Error(w, "Cannot create new file object from uplaoded file.", http.StatusInternalServerError)
 		return
 	}
 	defer dst.Close()
-	if _, err := io.Copy(dst, returnFile); err != nil {
-		http.Error(w, err.Error(), http.StatusInternalServerError)
+
+	// Write the uploaded file to the new file in the tempdir
+	if _, err = io.Copy(dst, returnFile); err != nil {
+		http.Error(w, "cannot copy contents of uploaded file to temporary file", http.StatusInternalServerError)
 		return
 	}
 
@@ -78,24 +88,13 @@ func (app *application) createReturnHandler(w http.ResponseWriter, r *http.Reque
 	}
 	dm := Datamap{Name: dmName, Description: dmDesc, Created: time.Now(), DMLs: dmls}
 
-	// Parse the XLSX file based on the Datamap...
-	// open an existing file
-	wb, err := xlsx.OpenFile(dst.Name())
+	// we can pass the file path to ParseXLSX.
+	ret, err := ParseXLSX(path.Join(tmpDir, header.Filename), &dm)
 	if err != nil {
-		http.Error(w, "Cannot open Excel file", http.StatusBadRequest)
-		return
-	}
-	// wb now contains a reference to the workbook
-	// show all the sheets in the workbook
-	fmt.Println("Sheets in this file:")
-	for i, sh := range wb.Sheets {
-		fmt.Println(i, sh.Name)
+		app.serverErrorResponse(w, r, err)
 	}
-	fmt.Println("----")
-
-	//Here is where we parse our files.
 
-	err = app.writeJSONPretty(w, http.StatusOK, envelope{"Datamap": dm}, nil)
+	err = app.writeJSONPretty(w, http.StatusOK, envelope{"Return": ret}, nil)
 	if err != nil {
 		app.logger.Debug("writing out csv", "err", err)
 		app.serverErrorResponse(w, r, err)
diff --git a/datamap.csv b/datamap.csv
index 1e0f0a8..235dd83 100644
--- a/datamap.csv
+++ b/datamap.csv
@@ -1,3 +1,3 @@
-Project/Programme Name,rtp_template,TEXT,E6
-Acronym,rtp_template,TEXT,E6
-
+Project/Programme Name,rpt_template,TEXT,E6
+Acronym,rpt_template,TEXT,E12
+COMMUNICATIONS,dropdowns,TEXT,D2
diff --git a/resources/test_two_sheets.xlsm b/resources/test_two_sheets.xlsm
new file mode 100644
index 0000000..e599d6e
Binary files /dev/null and b/resources/test_two_sheets.xlsm differ
-- 
cgit v1.2.3