Langsung ke konten utama

Membuat export dan import Excel di spring boot

Membuat export dan import Excel di spring boot
Image result for spring boot
          Adakalanya seorang developer diminta untuk membuat fitur export dan import berbentuk excel dan sebenarnya java sudah menyediakan suatu liblary yang memudahkan kita untuk bekerja dengan MS-Office yaitu Apache POI. Disini saya akan menggunakan Apache POI untuk membaca dan menulis Excel.

Catatan : Disini saya sarankan untuk belajar Apache POI Excel terlebih dahulu agar anda mengerti kode yang saya buat di bawah ini.

Bahan :
- IntelliJ IDEA
- PostgreSQL
- Maven
- Java 8

Jika sudah tersedia bahan - bahan di atas langsung saja kunjungi situs ini
Tambahkan dependency Apache POI di pom.xml seperti di bawah ini.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.fluex404</groupId>
	<artifactId>springboot_export_n_import</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboot_export_n_import</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.0.1</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.0.1</version>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>
Buat databasenya disini saya membuat database yang bernama springboot_export_n_importdb lalu tambahkan perintah dibawah ini ke application.properties

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url=jdbc:postgresql://localhost:5432/springboot_export_n_importdb
spring.datasource.username=postgres
spring.datasource.password=postgres

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto=update
Buat class model Siswa.java

package com.fluex404.springboot_export_n_import.model;

import lombok.AllArgsConstructor;
import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

@Data
@AllArgsConstructor
@Entity
@Table(name="siswa")
public class Siswa implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    private String kelas;
    private String jurusan;

    public Siswa() {
    }
}
Buat interface dao SiswaDao.java

package com.fluex404.springboot_export_n_import.dao;

import com.fluex404.springboot_export_n_import.model.Siswa;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface SiswaDao extends JpaRepository<Siswa, Integer> {
}
Buat controller. Disini RestController dan Controller
Buat class controller HttpMappingController.java

package com.fluex404.springboot_export_n_import.controller;

import com.fluex404.springboot_export_n_import.dao.SiswaDao;
import com.fluex404.springboot_export_n_import.myapachepoi.ExcelGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class HttpMappingController {

    @Autowired
    private ExcelGenerator excel;

    @GetMapping("/import")
    public String creteGetImport() {

        return "import";
    }

    @PostMapping("/import")
    public String cretePostImport(@RequestParam(name = "file") MultipartFile file) throws Exception {


        excel.importExcel(file);

        return "redirect:/import";
    }

}
Buat Class HttpMappingRestController.java

package com.fluex404.springboot_export_n_import.controller;

import com.fluex404.springboot_export_n_import.dao.SiswaDao;
import com.fluex404.springboot_export_n_import.model.Siswa;
import com.fluex404.springboot_export_n_import.myapachepoi.ExcelGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.ByteArrayInputStream;
import java.util.List;

@RestController
public class HttpMappingRestController {

    private SiswaDao siswaDao;
    private ExcelGenerator excel;

    @Autowired
    public HttpMappingRestController(SiswaDao siswaDao, ExcelGenerator excel) {
        this.siswaDao = siswaDao;
        this.excel = excel;
    }

    @GetMapping("/export")
    public ResponseEntity<InputStreamResource> excelSiswaReport() throws Exception {
        List<Siswa> siswas = siswaDao.findAll();

        ByteArrayInputStream in = excel.exportExcel(siswas);

        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=siswa.xlsx");

        return ResponseEntity.ok().headers(headers).body(new InputStreamResource(in));

    }


}
Buat ExcelGenerator.java

package com.fluex404.springboot_export_n_import.myapachepoi;

import com.fluex404.springboot_export_n_import.dao.SiswaDao;
import com.fluex404.springboot_export_n_import.model.Siswa;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.Iterator;
import java.util.List;

@Component
public class ExcelGenerator {

    @Autowired
    private SiswaDao siswaDao;

    /* export */
    public ByteArrayInputStream exportExcel(List<Siswa> siswas) throws Exception{
        String[] columns = {"Id", "Name", "Kelas", "Jurusan"};
        try(
                Workbook workbook = new XSSFWorkbook();
                ByteArrayOutputStream out = new ByteArrayOutputStream()
                )
        {
            CreationHelper creationHelper = workbook.getCreationHelper();
            Sheet sheet = workbook.createSheet("Data Siswa");
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.BLUE.getIndex());
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);

            //Row ofor Header
            Row headerRow = sheet.createRow(0);

            //Header
            for(int i=0;i<columns.length;i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns[i]);
                cell.setCellStyle(headerCellStyle);
            }


            int rowIdx = 1;
            for(Siswa siswa : siswas) {
                Row row = sheet.createRow(rowIdx);

                row.createCell(0).setCellValue(siswa.getId());
                row.createCell(1).setCellValue(siswa.getName());
                row.createCell(2).setCellValue(siswa.getKelas());
                row.createCell(3).setCellValue(siswa.getJurusan());
                rowIdx++;
            }

            workbook.write(out);
            workbook.close();
            return new ByteArrayInputStream(out.toByteArray());
        }catch(Exception e) {

        }
        return null;
    }

    /* Import */
    public void importExcel(MultipartFile file) throws Exception{

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        for(int i=0;i<(CoutRowExcel(sheet.rowIterator()));i++) {
            if(i == 0) {
                continue;
            }

            Row row = sheet.getRow(i);

            String nama = row.getCell(1).getStringCellValue();
            String kelas = row.getCell(2).getStringCellValue();
            String jurusan = row.getCell(3).getStringCellValue();

            siswaDao.save(new Siswa(0, nama, kelas, jurusan));
        }

    }

    /* Cout Row of Excel Table */
    public static int CoutRowExcel(Iterator<Row> iterator) {
        int size = 0;
        while(iterator.hasNext()) {
            Row row = iterator.next();
            size++;
        }
        return size;
    }
}

Buat import.html

<html xmlns:th="http://www.thymeleaf.org" lang="en">
    <head>
        <title>Import File</title>
    </head>
    <body>

        <form th:action="@{/import}" method="post" enctype="multipart/form-data">
            <input type="file" name="file">
            <input type="submit" value="Import">
        </form>

    </body>
</html>








File Projectnya bisa si download disini https://github.com/fluex404/springboot_export_n_import_apachepoi

Jika Ada pertanyaan bisa di tanyakan di www.facebook.com/fluex404
Label:

Komentar

Posting Komentar

Postingan populer dari blog ini

whois

  Pengertian:  whois adalah tools yang digunakan untuk melihat informasi mengenai kepemilikan domain dan website. ICANN meregulasikan domain name dan kepemelikian, tapi record listnya di adakan di semua company dikenal sebagai registrasi.  jadi orang bisa query list of records, dan register akan menghandle request anda dan mengirim detail dari whois record yang sesuai. How to Install whois :  sudo apt-get install whois How to use:  whois cnn.com dan hasilnya akan seperti ini. Domain Name: CNN.COM Registry Domain ID: 3269879_DOMAIN_COM-VRSN Registrar WHOIS Server: whois.corporatedomains.com Registrar URL: http://www.cscglobal.com/global/web/csc/digital-brand-services.html Updated Date: 2018-04-10T16:43:38Z Creation Date: 1993-09-22T04:00:00Z Registry Expiry Date: 2026-09-21T04:00:00Z Registrar: CSC Corporate Domains, Inc. Registrar IANA ID: 299 Registrar Abuse Contact Email: domainabuse@cscglobal.com Registrar Abuse Contact Phone: 8887802723 Domain Status: clien...
1 komentar
Baca selengkapnya

Spring Boot CRUD Thymeleaf-Pagination + Bootstrap Dynamic Modals

Spring Boot CRUD Thymeeaf-Pagination + Bootstrap Dynamic Modals                Sebelumnya saya sudah sharing bagaimana cara membuat Spring Boot CRUD sederhana di artikel sebelumnya yang blum lihat silahkan lihat disini . Sekarang saya akan membuat Spring Boot CRUD lagi ala pagination dan Dynamic Modals dengan bootstrap. Bahan : - IntelliJ IDEA - PostgreSQL - Maven - JDK1.8 Jika bahan nya sudah tersedia agan bisa kunjungi situs resmi spring nya https://start.spring.io/  buat seperti ini lalu download dan exract. Buat database yang bernama "springbootcrud2" disini saya menggunakan pgadmin4. Buka intelliJ atau IDE kesayangan kalian buka file application.properties di dalam resources dan isikan ini untuk configurasi databasenya sesuai kan dengan database kalian lalu save. ## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties) spring.datasource.url = jdbc:postgresql://loc...
4 komentar
Baca selengkapnya