Case Study - XLSX ZIP to TXT ZIP Conversion

📖 Information

Project Definition (XLSX ZIP to TXT ZIP Conversion)

A Spring Boot service that processes a user-uploaded ZIP file containing one or more Mockaroo-style XLSX files. The service extracts each XLSX, converts its content into a column-aligned TXT file, and returns all generated TXT files wrapped in a new ZIP archive. A separate logging endpoint allows for retrieval of application logs with pagination and sorting capabilities.

End-to-end flow (Convert ZIP API):

• Client sends a multipart request with a single file part: file (the uploaded ZIP).
• Service extracts the ZIP content, validates the file, and processes each internal .xlsx file.
• Each XLSX file is converted to a column-aligned TXT file.
• All generated TXT files are compressed into a new ZIP archive.
• Returns:
  • 200 OK with the converted ZIP file (application/zip) as the body.

Logs API flow (List Logs API):

• Client sends a POST request to /api/logs/list with a JSON request body (CustomPagingRequest) containing pagination and optional sorting parameters (page number, size, sort field, direction).
• Service loads a paged list of application logs (LogDto) using the parameters.
• Returns:
  • 200 OK JSON model (CustomResponse<CustomPagingResponse<LogResponse>>).

Error semantics:

• 400 Bad Request — Empty uploaded ZIP file or invalid pagination parameters (for logs API).
• 404 Not Found — Resource not found (not explicitly mentioned in controller but typical).
• 500 Internal Server Error — Unexpected failures (e.g., error during output ZIP creation).
• Custom Exceptions:
  • InvalidZipContentException: Uploaded ZIP is empty or contains no XLSX entries.
  • ZipProcessingException: Error during the creation of the output TXT ZIP.

---

Explore Rest APIs

Endpoints Summary

Method	URL	Description	Request Body	Headers/Path	Response	Status Codes
POST	/api/upload-zip	Upload a ZIP of XLSX files and download a ZIP of aligned TXT files.	Multipart: <ul><li>file: ZIP file (required)</li></ul>	Content-Type: multipart/form-data, Accept: application/zip	application/zip file	200, 400, 500 (via exceptions)
POST	/api/logs/list	List application logs with pagination and optional sorting.	JSON: CustomPagingRequest (page number, size, sort field, direction)	Content-Type: application/json, Accept: application/json	CustomResponse<CustomPagingResponse<LogResponse>>	200, 400

---

Technologies

• Java 25
• Spring Boot 3.0
• Restful API
• Open Api (Swagger)
• Maven
• Junit5
• Mockito
• Integration Tests
• Mapstruct
• Docker
• Docker Compose
• CI/CD (Github Actions)
• Postman
• Prometheus
• Grafana
• Kubernetes
• JaCoCo (Test Report)
• AOP
• Sonarqube
• Jenkins
• Apache POI

---

Postman

Import postman collection under postman_collection folder

---

Prerequisites

Define Variable in .env file

ZIP_DB_IP=localhost
ZIP_DB_PORT=3306
DATABASE_USERNAME={MY_SQL_DATABASE_USERNAME}
DATABASE_PASSWORD={MY_SQL_DATABASE_PASSWORD}

---

Open Api (Swagger)

http://localhost:1929/swagger-ui/index.html

JaCoCo (Test Report)

After the command named mvn clean install completes, the JaCoCo report will be available at:

target/site/jacoco/index.html

Navigate to the target/site/jacoco/ directory.

Open the index.html file in your browser to view the detailed coverage report.

---

Maven, Docker and Kubernetes Running Process

Maven Run

To build and run the application with Maven, please follow the directions shown below;

$ git clone https://github.com/Rapter1990/xlsxziptotxtzip.git # (Assuming a new repo name)
$ cd xlsxziptotxtzip
$ mvn clean install
$ mvn spring-boot:run

---

Docker Run

The application can be built and run by the Docker engine. The Dockerfile has multistage build, so you do not need to build and run separately.

Please follow directions shown below in order to build and run the application with Docker Compose file;

$ cd xlsxziptotxtzip
$ docker-compose up -d

If you change anything in the project and run it on Docker, you can also use this command shown below

$ cd xlsxziptotxtzip
$ docker-compose up --build

To monitor the application, you can use the following tools:

• Prometheus:
  Open in your browser at http://localhost:9090
  Prometheus collects and stores application metrics.

• Grafana:
  Open in your browser at http://localhost:3000
  Grafana provides a dashboard for visualizing the metrics.
  Default credentials:

  • Username: admin
  • Password: admin

Define prometheus data source url, use this link shown below

http://prometheus:9090

---

Kubernetes Run

To run the application, please follow the directions shown below;

• Start Minikube

$ minikube start

• Open Minikube Dashboard

$ minikube dashboard

• To deploy the application on Kubernetes, apply the Kubernetes configuration file underneath k8s folder

$ kubectl apply -f k8s

• To open Prometheus, click tunnel url link provided by the command shown below to reach out Prometheus

minikube service prometheus-service

• To open Grafana, click tunnel url link provided by the command shown below to reach out Prometheus

minikube service grafana-service

• Define prometheus data source url, use this link shown below

http://prometheus-service.default.svc.cluster.local:9090

---

Docker Image Location

https://hub.docker.com/repository/docker/noyandocker/xlsxziptotxtzip/general
https://hub.docker.com/repository/docker/noyandocker/xlsxziptotxtzip-jenkins/general

Sonarqube

• Go to localhost:9000 for Docker and Go there through minikube service sonarqube for Kubernetes
• Enter username and password as admin
• Change password
• Click Create Local Project
• Choose the baseline for this code for the project as Use the global setting
• Click Locally in Analyze Method
• Define Token
• Click Continue
• Copy sonar.host.url and sonar.token (sonar.login) in the properties part in  pom.xml
• Run mvn sonar:sonar to show code analysis

---

Jenkins

• Go to jenkins folder
• Run docker-compose up -d
• Open Jenkins in the browser via localhost:8080
• Define credentials for Github General token used by GIT_REPO_ID and docker-hub-credentials for Docker Username and Password
• Go to pipeline named cryptoexchangeapi
• Run Pipeline
• Show Pipeline Step to verify if it succeeded or failed

---

📸 Screenshots

Click here to show the screenshots of project

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Figure 7

Figure 8

Figure 9

Figure 10

Figure 11

Figure 12

Figure 13

Figure 14

Figure 15

Figure 16

Figure 17

Figure 18

Figure 19

Figure 20

Figure 21

Figure 22

Figure 23

Figure 24

Figure 25

Figure 26

Figure 27

Figure 28

Figure 29

Figure 30

Figure 31

Figure 32

Figure 33

Figure 34

Figure 35

Figure 36

Figure 37

Figure 38

Figure 39

Figure 40

---

Contributors

• Sercan Noyan Germiyanoğlu