2. Import Produk dengan File Excel
Pada suatu aplikasi , terkadang kita butuh fitur import dari file excel ke dalam database. Kita akan membuat fitur import ketika akan menambahkan produk ke dalam database
1. Membuat File Excel
2. Membuat Service Import Produk
produk.service.ts
import {
HttpException,
HttpStatus,
Inject,
Injectable,
NotFoundException,
} from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { InjectRepository } from '@nestjs/typeorm';
import BaseResponse from 'src/utils/response/base.response';
import { Produk } from './produk.entity';
import { Between, Like, Repository } from 'typeorm';
import {
CreateProdukArrayDto,
UpdateProdukDto,
findAllProduk,
} from './produk.dto';
import { ResponsePagination, ResponseSuccess } from 'src/interface/response';
import { Workbook } from 'exceljs';
import * as fs from 'fs';
@Injectable()
export class ProdukService extends BaseResponse {
constructor(
@InjectRepository(Produk)
private readonly produkRepository: Repository<Produk>,
@Inject(REQUEST) private req: any,
) {
super();
}
....
async importProduk(filename: string): Promise<ResponseSuccess> {
const filePath = `public/uploads/${filename}`; //untuk mendapatkan lokasi file excel di upload
const workbook = new Workbook();
const result = await workbook.xlsx
.readFile(filePath)
.then(async (workbook) => {
const ws = workbook.getWorksheet('Laporan'); // mengambil sheet Laporan
const produks = [];
ws.eachRow((row) => { // Iterasi semua baris pada excel
const rowData = [];
row.eachCell((cell) => { // iterasi semua cell pada setiap baris
rowData.push(cell.value);
});
produks.push(rowData);
});
console.log('produk', produks);
produks.shift(); // menghapus baris pertama
const payload = [];
produks.forEach((row) => { //iterasi untuk membuat payload untuk disimpan ke db sesuai CreateProdukDto
const mapel = {
barcode: row[1],
nama_produk: row[2],
deskripsi_produk: row[3],
harga: row[4],
stok: row[5],
kategori_id: row[6],
};
payload.push(mapel);
});
const response = await this.createBulk({ // create data secara bulk ke database
data: payload,
});
fs.unlinkSync(filePath); // hapus file excel
return response;
})
.catch(() => {
throw new HttpException('Ada Kesalahan', HttpStatus.BAD_GATEWAY);
});
return this._success(result.message);
//
}
}
4. Hasil Controller Import Produk
produk.controller.ts
import {
Body,
Controller,
Get,
Param,
Post,
Put,
UploadedFile,
UseGuards,
UseInterceptors,
} from '@nestjs/common';
import { ProdukService } from './produk.service';
import {
CreateProdukArrayDto,
UpdateProdukDto,
findAllProduk,
} from './produk.dto';
import { JwtGuard } from '../auth/auth.guard';
import { Pagination } from 'src/utils/decorator/pagination.decorator';
import { InjectUpdatedBy } from 'src/utils/decorator/inject-updated_by.decorator';
import { FileInterceptor } from '@nestjs/platform-express';
import { diskStorage } from 'multer';
@UseGuards(JwtGuard)
@Controller('produk')
export class ProdukController {
constructor(private produkService: ProdukService) {}
...
@UseInterceptors(
FileInterceptor('file', {
storage: diskStorage({
destination: 'public/uploads',
filename: (req, file, cb) => {
const fileExtension = file.originalname.split('.').pop();
cb(null, `${new Date().getTime()}.${fileExtension}`);
},
}),
}),
)
@Post('import')
async uploadFile(@UploadedFile() file: Express.Multer.File) {
return this.produkService.importProduk(file.filename);
}
}
4. Hasil console.log
console.log(produks)
[
[
'No',
'barcode',
'nama_produk',
'deskripsi_produk',
'harga',
'stok',
'kategori_id'
],
[ 1, 12089922, 'motor mio 1', 'motor mio 1', 5000000, 100, 2 ],
[ 2, 12089923, 'motor mio 2', 'motor mio 2', 5000001, 100, 2, null ],
[ 3, 12089924, 'motor mio 3', 'motor mio 3', 5000002, 100, 2 ],
[ 4, 12089925, 'motor mio 4', 'motor mio 4', 5000003, 100, 2, null ],
[ 5, 12089926, 'motor mio 5', 'motor mio 5', 5000004, 100, 2 ],
[ 6, 12089927, 'motor mio 6', 'motor mio 6', 5000005, 100, 2, null ],
[ 7, 12089928, 'motor mio 7', 'motor mio 7', 5000006, 100, 2 ],
[ 8, 12089929, 'motor mio 8', 'motor mio 8', 5000007, 100, 2 ],
[ 9, 12089930, 'motor mio 9', 'motor mio 9', 5000008, 100, 2 ],
[ 10, 12089931, 'motor mio 10', 'motor mio 10', 5000009, 100, 2 ],
[ 11, 12089932, 'motor mio 11', 'motor mio 11', 5000010, 100, 2 ],
[ 12, 12089933, 'motor mio 12', 'motor mio 12', 5000011, 100, 2 ],
[ 13, 12089934, 'motor mio 13', 'motor mio 13', 5000012, 100, 2 ]
]
console.log(payload)
[
{
barcode: 12089922,
nama_produk: 'motor mio 1',
deskripsi_produk: 'motor mio 1',
harga: 5000000,
stok: 100,
kategori_id: 2
},
{
barcode: 12089923,
nama_produk: 'motor mio 2',
deskripsi_produk: 'motor mio 2',
harga: 5000001,
stok: 100,
kategori_id: 2
},
{
barcode: 12089924,
nama_produk: 'motor mio 3',
deskripsi_produk: 'motor mio 3',
harga: 5000002,
stok: 100,
kategori_id: 2
},
{
barcode: 12089925,
nama_produk: 'motor mio 4',
deskripsi_produk: 'motor mio 4',
harga: 5000003,
stok: 100,
kategori_id: 2
},
{
barcode: 12089926,
nama_produk: 'motor mio 5',
deskripsi_produk: 'motor mio 5',
harga: 5000004,
stok: 100,
kategori_id: 2
},
{
barcode: 12089927,
nama_produk: 'motor mio 6',
deskripsi_produk: 'motor mio 6',
harga: 5000005,
stok: 100,
kategori_id: 2
},
{
barcode: 12089928,
nama_produk: 'motor mio 7',
deskripsi_produk: 'motor mio 7',
harga: 5000006,
stok: 100,
kategori_id: 2
},
{
barcode: 12089929,
nama_produk: 'motor mio 8',
deskripsi_produk: 'motor mio 8',
harga: 5000007,
stok: 100,
kategori_id: 2
},
{
barcode: 12089930,
nama_produk: 'motor mio 9',
deskripsi_produk: 'motor mio 9',
harga: 5000008,
stok: 100,
kategori_id: 2
},
{
barcode: 12089931,
nama_produk: 'motor mio 10',
deskripsi_produk: 'motor mio 10',
harga: 5000009,
stok: 100,
kategori_id: 2
},
{
barcode: 12089932,
nama_produk: 'motor mio 11',
deskripsi_produk: 'motor mio 11',
harga: 5000010,
stok: 100,
kategori_id: 2
},
{
barcode: 12089933,
nama_produk: 'motor mio 12',
deskripsi_produk: 'motor mio 12',
harga: 5000011,
stok: 100,
kategori_id: 2
},
{
barcode: 12089934,
nama_produk: 'motor mio 13',
deskripsi_produk: 'motor mio 13',
harga: 5000012,
stok: 100,
kategori_id: 2
}
]