- Java Programming Examples
- Example - Home
- Example - Environment
- Example - Strings
- Example - Arrays
- Example - Date & Time
- Example - Methods
- Example - Files
- Example - Directories
- Example - Exceptions
- Example - Data Structure
- Example - Collections
- Example - Networking
- Example - Threading
- Example - Applets
- Example - Simple GUI
- Example - JDBC
- Example - Regular Exp
- Example - Apache PDF Box
- Example - Apache POI PPT
- Example - Apache POI Excel
- Example - Apache POI Word
- Example - OpenCV
- Example - Apache Tika
- Example - iText
- Java Tutorial
- Java - Tutorial
- Java Useful Resources
- Java - Quick Guide
- Java - Useful Resources
How to insert data from a database to a spread sheet using Java
Problem Description
How to insert data from a database to a spread sheet using Java.
Solution
Following is the program to insert data from a database to a spread sheet using Java.
import java.io.File; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class InsertDataFromDataBaseToSpreadSheet { public static void main(String[] args) throws Exception { //Connecting to the database Class.forName("com.mysql.jdbc.Driver"); Connection connect = DriverManager.getConnection( "jdbc:mysql://localhost:3306/details", "root" , "password"); //Getting data from the table emp_tbl Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("select * from student_data"); //Creating a Work Book XSSFWorkbook workbook = new XSSFWorkbook(); //Creating a Spread Sheet XSSFSheet spreadsheet = workbook.createSheet("employe db"); XSSFRow row = spreadsheet.createRow(1); XSSFCell cell; cell = row.createCell(1); cell.setCellValue("EMP ID"); cell = row.createCell(2); cell.setCellValue("EMP NAME"); cell = row.createCell(3); cell.setCellValue("DEG"); cell = row.createCell(4); cell.setCellValue("SALARY"); cell = row.createCell(5); cell.setCellValue("DEPT"); int i = 2; while(resultSet.next()) { row = spreadsheet.createRow(i); cell = row.createCell(1); cell.setCellValue(resultSet.getInt("ID")); cell = row.createCell(2); cell.setCellValue(resultSet.getString("NAME")); cell = row.createCell(3); cell.setCellValue(resultSet.getString("BRANCH")); cell = row.createCell(4); cell.setCellValue(resultSet.getString("PERCENTAGE")); cell = row.createCell(5); cell.setCellValue(resultSet.getString("EMAIL")); i++; } FileOutputStream out = new FileOutputStream( new File("C:/poiexcel/exceldatabase.xlsx")); workbook.write(out); out.close(); System.out.println("exceldatabase.xlsx written successfully"); } }
Database
mysql> select * from student_data; +----+--------+--------+------------+---------------------+ | ID | NAME | BRANCH | PERCENTAGE | EMAIL | +----+--------+--------+------------+---------------------+ | 1 | Ram | IT | 85 | ram123@gmail.com | | 2 | Rahim | EEE | 95 | rahim123@gmail.com | | 3 | Robert | ECE | 90 | robert123@gmail.com | +----+--------+--------+------------+---------------------+ 3 rows in set (0.00 sec)
Result
java_apache_poi_excel
Advertisements