Express.js application that converts API responses to Excel files with MySQL database integration.Convert API responses to Excel spreadsheets with a beautiful web interface and MySQL database integration.Convert API responses to Excel spreadsheets with a beautiful web interface.
api_to_excel_script/
├── app.js # Main Express application entry point- 📊 Real-time logging and status updates- 📊 Real-time logging and status updates
├── package.json # Node.js dependencies and scripts
├── .env.example # Environment variables template- 🗄️ MySQL database integration for project management- ✅ Automatic JSON validation
├── .gitignore # Git ignore rules
│- ✅ Automatic JSON validation- 📁 Organized file storage in `output/` folder
├── bin/
│ └── www # Server startup script- 📁 Organized file storage in `output/` folder- 🔄 Handles multiple API response formats
│
├── config/- 🔄 Handles multiple API response formats- 🌐 CORS enabled for cross-origin requests
│ └── database.js # MySQL connection pool configuration
│- 🌐 CORS enabled for cross-origin requests
├── routes/
│ └── api.js # API endpoints (fetch-api, projects)## 🚀 Quick Start
│
├── utils/## 🚀 Quick Start
│ └── excel.js # Excel file generation utility
│### Installation
├── views/ # 🎨 HTML Views (MVC - View Layer)
│ └── index.html # Main UI interface### 1. Install Dependencies
│
├── public/ # 🌐 Static Assets (served via express.static)`bash`bash
│ ├── stylesheets/
│ │ └── style.css # CSS stylesnpm installnpm install
│ └── javascripts/
│ └── main.js # Client-side JavaScript````
│
└── output/ # 📊 Generated Excel files
└── .gitkeep
```### 2. Setup Database Configuration### Start Server
## 🏗️ Architecture Overview
### MVC PatternCreate `.env` file:```bash
- **Model**: `config/database.js` - Database connection and queries
- **View**: `views/index.html` - HTML interface served to users ```bashnpm start
- **Controller**: `routes/api.js` - Business logic and API endpoints
cp .env.example .env```
### Static Assets
- **CSS**: `public/stylesheets/style.css` - UI styling```
- **JavaScript**: `public/javascripts/main.js` - Client-side logic
- **Serving**: Automatically served via `express.static('public')`**Or use the restart script:**
### UtilitiesEdit `.env` with your database credentials:
- **Excel Generator**: `utils/excel.js` - Handles data conversion to Excel format
```env```bash
### Flow
```DB_HOST=localhost./restart.sh
User Request → app.js → routes/api.js → utils/excel.js → output/file.xlsx
↓DB_USER=root```
views/index.html (served from views folder)
↓DB_PASSWORD=your_password
public/stylesheets/style.css (static assets)
public/javascripts/main.jsDB_NAME=your_existing_database_nameAccess the application at: **http://localhost:3000**
PORT=3000
### 1. Install Dependencies
```bash
npm install
```### 3. Update Database Query1. **Open the web interface** in your browser
### 2. Configure Environment2. **Fill in the form:**
```bash
cp .env.example .envOpen `routes/api.js` (line ~103) and update the query to match your table:
- API URL (e.g.,
https://apisetu.gov.in/)
Edit .env with your database credentials:
envjavascript - LGD State Code (e.g., 22)
DB_HOST=localhost
DB_USER=your_db_user// Change according to your table structure - X-APISETU-APIKEY (your API key)
DB_PASSWORD=your_db_password
DB_NAME=your_db_nameconst query = 'SELECT id as project_id, name as project_name FROM projects ORDER BY name'; - X-APISETU-CLIENTID (your client ID)
PORT=3000
### 3. Update Database Query
Open `routes/api.js` (around line 103) and update the SQL query to match your table:
```javascript**Examples:**3. **Click "Convert to Excel"**
const query = `SELECT id as project_id, name as project_name FROM your_table_name ORDER BY name`;
``````javascript4. **Check the logs** for real-time status
### 4. Start Server// If columns are 'project_id' and 'project_name':5. **Find your Excel file** in the `output/` folder
```bash
npm startconst query = 'SELECT project_id, project_name FROM projects ORDER BY project_name';
# or
./restart.sh## 📊 Supported API Response Formats
```
// If table name is different:
Visit: `http://localhost:3000`
const query = 'SELECT id as project_id, title as project_name FROM my_projects ORDER BY title';The application intelligently handles multiple response formats:
## 📡 API Endpoints
```
### POST `/api/fetch-api`
Fetches data from external API and converts to Excel.### ✅ Format 1: Object with data array (Your format)
**Request Body:**### 4. Start Server
```json
{```bash```json
"API_URL": "https://api.example.com",
"LGDStateCode": "12",npm start{
"X_APISETU_APIKEY": "your-api-key",
"X_APISETU_CLIENTID": "your-client-id",``` "status": 1,
"bodyData": {
"project_id": 4620,Or use the restart script: "response": "success",
"date": 14,
"month": 11,```bash "data": [
"year": 2024
}./restart.sh { "id": 1, "name": "John" },
}
`````` { "id": 2, "name": "Jane" }
**Response:** ]
```json
{Access the application at: **http://localhost:3000**}
"status": true,
"message": "Excel file created successfully",```
"fileName": "children_3y_6y_12_14_11_2024.xlsx"
}## 📁 Project Structure
```
### ✅ Format 2: Direct array
### GET `/api/projects`
Fetches all projects from database.```
**Response:**api_to_excel_script/```json
```json
{├── app.js # Main Express application[
"status": true,
"data": [├── package.json # Dependencies { "id": 1, "name": "John" },
{
"project_id": 1,├── .env.example # Environment template { "id": 2, "name": "Jane" }
"project_name": "Project A"
}├── restart.sh # Server restart script]
]
}├── bin/```
```
│ └── www # Server startup
### GET `/api/projects/:id`
Fetches a specific project by ID.├── config/### ✅ Format 3: Nested arrays
**Response:**│ └── database.js # MySQL connection
```json
{├── routes/```json
"status": true,
"data": {│ └── api.js # API endpoints{
"project_id": 1,
"project_name": "Project A"├── utils/ "results": [{ "id": 1, "name": "John" }]
}
}│ └── excel.js # Excel generation}
```
├── public/```
## 🛠️ Technology Stack
│ ├── index.html # Main UI
- **Backend**: Express.js v4.16.1
- **Database**: MySQL (via mysql2 v3.6.5)│ ├── stylesheets/### ✅ Format 4: Single object
- **Excel Generation**: xlsx v0.18.5
- **HTTP Client**: node-fetch v2.6.7│ │ └── style.css # Styles
- **Environment Config**: dotenv v16.3.1
- **CORS**: cors v2.8.5│ └── javascripts/```json
## 📝 Features│ └── main.js # Frontend logic{ "id": 1, "name": "John" }
✅ Convert API responses to Excel files └── output/ # Generated Excel files```
✅ MySQL database integration with connection pooling
✅ Raw SQL query support (no ORM) ```
✅ Modern gradient UI with real-time logging
✅ Project dropdown loaded from database ## 📁 Project Structure
✅ Flexible data handling (arrays, objects, nested data)
✅ Automatic output folder management ## 📝 How to Use
✅ CORS enabled for cross-origin requests
✅ Proper MVC architecture with separated concerns ```
## 📂 Directory Structure Details1. **Open** `http://localhost:3000` in your browserapi_to_excel_script/
### `/views`2. **Select project** from dropdown (loaded from database) or enter manually├── app.js # Main Express application
Contains HTML view files served to the user. Following MVC pattern, views are separated from static assets.
3. **Fill in:**├── package.json # Dependencies
### `/public`
Static assets (CSS, JavaScript, images) served directly by Express using `express.static()`. These files are accessible via URL paths like `/stylesheets/style.css`. - API URL (e.g., `https://apisetu.gov.in/`)├── restart.sh # Server restart script
### `/routes` - LGD State Code├── test-excel.js # Test script
Express route handlers that define API endpoints and business logic.
- X-APISETU-APIKEY├── sample-response.json # Sample API response
### `/config`
Configuration files like database connection setup. - X-APISETU-CLIENTID├── bin/
### `/utils` - Date, Month, Year│ └── www # Server startup
Utility functions and helper modules (like Excel generation).
4. **Click** "Convert to Excel"├── routes/
### `/output`
Generated Excel files are saved here. This folder is ignored in git (except .gitkeep).5. **Check logs** for real-time status│ ├── index.js # Home page
## 🔧 Troubleshooting6. **Find Excel file** in `output/` folder│ └── api.js # API endpoint
### Database Connection Failed├── utils/
- Check `.env` file has correct credentials
- Verify MySQL server is running: `mysql -u username -p`## 🗄️ Database Integration│ └── excel.js # Excel generation logic
- Test connection in terminal
├── views/
### Excel File Not Generated
- Check `output/` folder permissions: `chmod 755 output/`### Required Table Structure│ ├── index.jade # Main UI
- Verify API response format in console logs
- Check for errors in terminal│ └── layout.jade # Layout
### Projects Not LoadingYour existing MySQL table should have:├── public/
- Update SQL query in `routes/api.js` line ~103
- Verify table name and column names match your database schema- **Project ID column** (e.g., `id`, `project_id`)│ ├── stylesheets/
- Check database connection logs on server startup
- **Project Name column** (e.g., `name`, `project_name`)│ │ └── style.css # Styles
### Static Assets Not Loading
- Verify files exist in `public/stylesheets/` and `public/javascripts/`│ └── javascripts/
- Check browser console for 404 errors
- Ensure `express.static()` is configured in `app.js`### API Endpoints│ └── main.js # Frontend logic
## 🎯 Development Tips├── output/ # Generated Excel files
1. **View Changes**: HTML files in `views/` folder#### Get All Projects└── docs/
2. **Style Changes**: CSS in `public/stylesheets/style.css`
3. **Logic Changes**: JavaScript in `public/javascripts/main.js````http ├── USAGE.md # Usage guide
4. **API Changes**: Backend routes in `routes/api.js`
5. **Database Changes**: Connection config in `config/database.js`GET /api/projects ├── TESTING.md # Testing guide
## 📄 License``` └── ERROR-FIX-SUMMARY.md # Error fix details
ISC```
**Response:**
```json## 🧪 Testing
{
"status": true,Run the test script to verify functionality:
"data": [
{```bash
"project_id": 4620,node test-excel.js
"project_name": "Surajpur"```
}
],This will generate a test Excel file using sample data.
"count": 1
}## 🔧 Configuration
```
### Change Port
#### Get Project by ID
```http```bash
GET /api/projects/:idPORT=3001 npm start
````
#### Convert API to Excel### Environment Variables
```http
POST /api/fetch-apiCreate a `.env` file:
```
````
**Request Body:**PORT=3000
```jsonNODE_ENV=development
{```
"API_URL": "https://api.example.com",
"LGDStateCode": "22",## 📦 Dependencies
"X_APISETU_APIKEY": "your-api-key",
"X_APISETU_CLIENTID": "your-client-id",- **express** - Web framework
"bodyData": {- **cors** - Enable CORS
"project_id": 4620,- **node-fetch** - HTTP requests
"date": 14,- **xlsx** - Excel generation
"month": 10,- **jade** - Template engine
"year": 2025
}## 🎯 API Endpoint
}
```### POST `/api/fetch-api`
## 📊 Supported API Response Formats**Request:**
### ✅ Format 1: Object with data array```json
```json{
{ "API_URL": "https://api.example.com",
"status": 1, "LGDStateCode": "22",
"data": [ "X_APISETU_APIKEY": "your-api-key",
{ "id": 1, "name": "John" } "X_APISETU_CLIENTID": "your-client-id",
] "bodyData": {
} "project_id": 4620,
``` "date": 14,
"month": 10,
### ✅ Format 2: Direct array "year": 2025
```json }
[}
{ "id": 1, "name": "John" }```
]
```**Success Response:**
### ✅ Format 3: Nested arrays```json
```json{
{ "status": true,
"results": [ "message": "Excel generated successfully",
{ "id": 1, "name": "John" } "file": "children_3y_6y_22_14_10_2025.xlsx",
] "filePath": "/path/to/output/file.xlsx",
} "api_hit": "https://api.example.com/pt/22/srv/v1/children_3y_6y",
``` "records": 150
}
### ✅ Format 4: Single object```
```json
{ "id": 1, "name": "John" }## 📁 Output Files
````
Excel files are saved with descriptive names:
## 🔧 Configuration
`````
### Change Portchildren_3y_6y_{StateCode}_{date}_{month}_{year}.xlsx
```bash```
PORT=3001 npm start
```Example: `children_3y_6y_22_14_10_2025.xlsx`
### Environment Variables## 🐛 Troubleshooting
```env
DB_HOST=localhost # MySQL host### Port Already in Use
DB_USER=root # MySQL username
DB_PASSWORD=password # MySQL password```bash
DB_NAME=database_name # Database name# Use different port
PORT=3000 # Server portPORT=3001 npm start
NODE_ENV=development # Environment
```# Or kill existing process
pkill -f "node ./bin/www"
## 🐛 Troubleshooting```
### Database Connection Failed### Permission Issues
**Solution:**
1. Check `.env` credentials```bash
2. Verify MySQL is running: `mysql -u root -p`sudo chown -R $(whoami) ~/.npm
3. Ensure database exists```
4. Check user permissions
### Missing Dependencies
### Projects Not Loading
**Solution:**```bash
1. Update query in `routes/api.js` with correct table/column namesnpm install
2. Verify table has data: `SELECT * FROM projects LIMIT 5;````
3. Check console logs for errors
### Test Data Format
### Port Already in Use
**Solution:**```bash
```bashnode test-excel.js
PORT=3001 npm start```
`````
Or kill existing process:## 📚 Documentation
````bash
pkill -f "node ./bin/www"- **USAGE.md** - Detailed usage instructions
```- **TESTING.md** - Testing different response formats
- **ERROR-FIX-SUMMARY.md** - Error resolution details
## 🔒 Security
## ✅ Recent Fixes
- ✅ Parameterized SQL queries (prevents SQL injection)
- ✅ `.env` file in `.gitignore` (credentials not committed)- ✅ Fixed "forEach is not a function" error
- ✅ CORS enabled (configurable)- ✅ Added support for object responses with data arrays
- ✅ Environment-based configuration- ✅ Improved error handling and logging
- ✅ Better UI feedback with real-time logs
## 📦 Dependencies- ✅ Auto-detection of response format
- **express** - Web framework## 🤝 Contributing
- **mysql2** - MySQL database driver
- **dotenv** - Environment configurationFeel free to submit issues and enhancement requests!
- **cors** - Enable CORS
- **node-fetch** - HTTP requests## 📄 License
- **xlsx** - Excel generation
- **morgan** - Request loggingISC
## 📁 Output Files---
Excel files are saved with descriptive names:**Version:** 1.0.0
```**Last Updated:** November 19, 2025
children_3y_6y_{StateCode}_{date}_{month}_{year}.xlsx**Status:** ✅ Production Ready
````
Example: `children_3y_6y_22_14_10_2025.xlsx`
All files are stored in the `output/` folder.
## 🎯 Customizing SQL Queries
All queries use raw SQL in `routes/api.js`. Easy to customize:
```javascript
// Add WHERE condition
const query = "SELECT id, name FROM projects WHERE active = 1 ORDER BY name";
// Add JOIN
const query =
"SELECT p.id, p.name, d.name as district FROM projects p LEFT JOIN districts d ON p.district_id = d.id";
// Add more columns
const query =
"SELECT id, name, district, state, created_date FROM projects ORDER BY name";
```
## 📝 Version History
- **v2.0.0** - MySQL integration, HTML templates, simplified structure
- **v1.0.0** - Initial release with Jade templates
## 📄 License
ISC
---
**Last Updated:** November 19, 2025
**Status:** ✅ Production Ready