Quick introduction to Apache POI

Remember in our working with spreadsheets chapter, we have used the data below. We will use it again in this chapter.

We've create a mysql test database, with a table called orders with data below

This chapter shows how to configure database using JDBC. It includes saving and retrieving the data from the database into spreadsheet. MYSQL database is used in below example.

First we will prepare our mysql database as shown below

Here are the MySQL command to get the database and table data going

# Create database
CREATE DATABASE `test`CHARACTER SET utf8 COLLATE utf8_bin; 

# Create test table
CREATE TABLE `test`.`orders` (
 `Id` INT (4) UNSIGNED NOT NULL AUTO_INCREMENT,
 `Region` CHAR(32) NOT NULL,
 `Rep` CHAR(32) NOT NULL,
 `Item` CHAR(32) NOT NULL,
 `Quantity_Sold` INT (4) NOT NULL,
 PRIMARY KEY (`Id`)
) ENGINE = MYISAM CHARSET = utf8 COLLATE = utf8_bin ;

# add user test with password test
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test'; 

#Flush privs
FLUSH PRIVILEGES; 

# Give permissions
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'test'@'localhost' WITH GRANT OPTION; 

# add data
INSERT INTO `test`.`orders` (`Region`, `Rep`, `Item`, `Quantity_Sold`) VALUES ('East', 'Yuv', 'Pencil', '95'); 
INSERT INTO `test`.`orders` (`Region`, `Rep`, `Item`, `Quantity_Sold`) VALUES ('Central', 'Nimrit', 'Binder', '50'); 
INSERT INTO `test`.`orders` (`Region`, `Rep`, `Item`, `Quantity_Sold`) VALUES ('Central', 'Kamal', 'Pencil', '36'); 

DatabaseExample.java

package com.sc;

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;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DatabaseExample
{
    public static void main(String[] args) throws Exception
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","test" ,"test");
        Statement stmt = conn.createStatement();
        ResultSet resultSet   = stmt.executeQuery("select * from orders");
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet spreadsheet = wb.createSheet("RecentOrdersByRegion");
        XSSFRow row=spreadsheet.createRow(1);
        XSSFCell cell;
        cell=row.createCell(1);
        cell.setCellValue("ID");
        cell=row.createCell(2);
        cell.setCellValue("Region");
        cell=row.createCell(3);
        cell.setCellValue("Rep");
        cell=row.createCell(4);
        cell.setCellValue("Item");
        cell=row.createCell(5);
        cell.setCellValue("Quantity_Sold");
        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("Region"));
            cell=row.createCell(3);
            cell.setCellValue(resultSet.getString("Rep"));
            cell=row.createCell(4);
            cell.setCellValue(resultSet.getString("Item"));
            cell=row.createCell(5);
            cell.setCellValue(resultSet.getString("Quantity_Sold"));
            i++;
        }
        try {
            FileOutputStream out = new FileOutputStream(
                    new File("C:/poi/databasetest.xlsx"));
            wb.write(out);
            out.close();
            System.out.println("Database test workbook sheet processed and written to file C:/poi/databasetest.xlsx");
        }
        catch(Exception e) {
            System.out.print(e.getMessage());
        }
    }
}

Output after compiling and executing