Java with excel database

Importing Excel Data into MySQL Database in Spring Boot via REST API using Apache POI Library

In this tutorial, we will show you how to import data from an excel file into a MySQL database using the Apache POI library in Spring Boot via REST API.

Apache POI is an open-source Java library that allows to create and modify various file formats of Microsoft Office documents using Java programming language.

Follow the steps below to complete this example:

Adding Dependency

Add the following dependencies to your Spring Boot project:

Add to the build.gradle file:

You can find the other versions of Apache POI in the Maven repository.

Adding Configurations

First, add the following credentials to your resources/application.properties configuration file:

Creating Entity Class

Create a Transaction.java entity class for mapping it with transaction table of database:

Creating JPA Repository Class

Create a TransactionRepository.java class for performing CRUD operations on the above transaction table:

Creating Service

Create a service class with a method to import data from an excel file to MySQL database:

Implementation class of the above Transaction service interface:

 multipartfiles) < if (!multipartfiles.isEmpty()) < Listtransactions = new ArrayList<>(); multipartfiles.forEach(multipartfile -> < try < XSSFWorkbook workBook = new XSSFWorkbook(multipartfile.getInputStream()); XSSFSheet sheet = workBook.getSheetAt(0); // looping through each row for (int rowIndex = 0; rowIndex < getNumberOfNonEmptyCells(sheet, 0) - 1; rowIndex++) < // current row XSSFRow row = sheet.getRow(rowIndex); // skip the first row because it is a header row if (rowIndex == 0) < continue; >Long senderId = Long.parseLong(getValue(row.getCell(0)).toString()); Long receiverId = Long.parseLong(getValue(row.getCell(1)).toString()); Long initiatorId = Long.parseLong(getValue(row.getCell(2)).toString()); String bankCode = String.valueOf(row.getCell(3)); int serviceCode = Integer.parseInt(row.getCell(4).toString()); double transactionAmount = Double.parseDouble(row.getCell(5).toString()); double feeAmount = Double.parseDouble(row.getCell(6).toString()); Transaction transaction = Transaction.builder().senderId(senderId).receiverId(receiverId) .initiatorId(initiatorId).bankCode(bankCode).serviceCode(serviceCode) .trxnAmount(transactionAmount).feeAmount(feeAmount).build(); transactions.add(transaction); > > catch (IOException e) < e.printStackTrace(); >>); if (!transactions.isEmpty()) < // save to database transactionRepository.saveAll(transactions); >> > private Object getValue(Cell cell) < switch (cell.getCellType()) < case STRING: return cell.getStringCellValue(); case NUMERIC: return String.valueOf((int) cell.getNumericCellValue()); case BOOLEAN: return cell.getBooleanCellValue(); case ERROR: return cell.getErrorCellValue(); case FORMULA: return cell.getCellFormula(); case BLANK: return null; case _NONE: return null; default: break; >return null; > public static int getNumberOfNonEmptyCells(XSSFSheet sheet, int columnIndex) < int numOfNonEmptyCells = 0; for (int i = 0; i > > return numOfNonEmptyCells; > > 

Creating Web Controller

Create a controller with a REST API endpoint that allows to import excel data into a MySQL database:

Enabling JPA Auditing

To enable Spring JPA auditing features like @CreateDate, @CreatedBy, @LastModifiedDate, and @LastModifiedBy, the main Spring Boot class should be annotated with @EnableJpaAuditing as shown in the example below:

This example is complete. You may also be interested in learning how to export MySQL data into excel.

Источник

Работа с таблицей Excel из Java

Собственно возникла проблема — обработать данные из таблицы и на их основе получить другую таблицу.

1. Макрос — единственной проблемой является VBA, на изучение которого времени нет совершенно, да и не нравится его синтаксис
2. Приложение на C# тут вроде все хорошо, но к машине на которой будет выполняться данное приложение сразу предъявляется много дополнительных требований:
   • .NET Framework
   • Установленный офис
   • установленная основная сборка взаимодействия (PIA) для приложения Office

3. связка Java и библиотека Apache POI—на этом способе я и хочу остановиться подробнее

1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19)—идет работа над поддержкой формата Office 2007
2. POI 3.2-FINAL Released (2008-10-19) — последний стабильный релиз

Я расскажу о работе с версией 3.2
Основным классом для работы с таблицей Excel является класс HSSFWorkbook пакета org.apache.poi.hssf.usermodel, представляющий книгу Excel.

Для чтения книги из файла можно применить следующий код:

public static HSSFWorkbook readWorkbook(String filename) < try < POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb; >catch (Exception e) < return null; >>

Метод возвращает объект класса HSSFWorkbook если все удачно и null в другом случае.

Для сохранения изменений можно применить следующий метод:

public static void writeWorkbook(HSSFWorkbook wb, String fileName) < try < FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); >catch (Exception e) < //Обработка ошибки >>

Метод записывает книгу wb в файл fileName

• По имени
  HSSFSheet sheet= wb.getSheet(«Лист 3»)
• По номеру (нумерация начинается с 0)
  HSSFSheet sheet= wb.getSheet(0)
• Создание нового листа
  HSSFSheet sheet= wb.createSheet([«имя листа»])

• По индексу (индексация начинается с 0)
  HSSFRow row = sheet.getRow(index)
• Через итератор

Iterator rowIter = sheet.rowIterator(); while (rowIter.hasNext())

• По индексу ячейки (индексация начинается с 0)
  HSSFCell cell = row.getCell(0);
• Через итератор

Iterator cellIter = row.cellIterator(); while (cellIter.hasNext())

• Логическое значение
  boolean b = cell.getBooleanCellValue();
  cell.setCellValue(b);
• Дата
  Date date = cell.getDateCellValue();
  cell.setCellValue(date);
• Числовое значение
  double d = cell.getNumericCellValue();
  cell.setCellValue(d);
• Строковое значение
  String str = cell.getRichStringCellValue().getString();
  cell.setCellValue(new HSSFRichTextString(str));
• Формула
  String formula = cell.getCellFormula();
  cell.setCellFormula(formula);

Этих знаний достаточно чтобы обрабатывать простые таблицы.
Библиотека также предоставляет богатые возможности по форматированию ячеек, по их слиянию, заморозке и т.д.
Подробное описание функций можно найти на их сайте.
Данный способ прежде всего ценен тем, что не требует установки самого офиса и пакета PIA.

Источник

Java code example to import data from Excel to database

Importing data from Excel to database is a common task of a software program. So in this post, I will share with you a sample Java program that reads data from an Excel file and inserts that data to a MySQL database.

Suppose that we have an Excel file that stores information about students enrolled in courses like this:

As you can see, this Excel file has only one sheet and the student information is stored in 3 columns: Student Name (text), Enrolled (date time) and Progress (number). So let’s create a corresponding table in the database with the following structure:

You can run the following MySQL script to create this table:

CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) NOT NULL, `enrolled` timestamp NOT NULL, `progress` int(11) NOT NULL, PRIMARY KEY (`id`) );

Note that the data type of the enrolled column is timestamp so it can hold both date and time values.

To read the Excel file from Java, we can use the Apache POI library. Suppose that your project uses Maven, so add the following dependency to the pom.xml file:

 org.apache.poi poi-ooxml 4.1.0 

 mysql mysql-connector-java 5.1.46 runtime 

And below is the full code of the sample program that reads data from the Excel file and insert that data to MySQL database:

package net.codejava; import java.io.*; import java.sql.*; import java.util.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; /** * Sample Java program that imports data from an Excel file to MySQL database. * * @author Nam Ha Minh - https://www.codejava.net * */ public class Excel2DatabaseTest < public static void main(String[] args) < String jdbcURL = "jdbc:mysql://localhost:3306/sales"; String username = "user"; String password = "password"; String excelFilePath = "Students.xlsx"; int batchSize = 20; Connection connection = null; try < long start = System.currentTimeMillis(); FileInputStream inputStream = new FileInputStream(excelFilePath); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); IteratorrowIterator = firstSheet.iterator(); connection = DriverManager.getConnection(jdbcURL, username, password); connection.setAutoCommit(false); String sql = "INSERT INTO students (name, enrolled, progress) VALUES (?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(sql); int count = 0; rowIterator.next(); // skip the header row while (rowIterator.hasNext()) < Row nextRow = rowIterator.next(); IteratorcellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) < Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) < case 0: String name = nextCell.getStringCellValue(); statement.setString(1, name); break; case 1: Date enrollDate = nextCell.getDateCellValue(); statement.setTimestamp(2, new Timestamp(enrollDate.getTime())); case 2: int progress = (int) nextCell.getNumericCellValue(); statement.setInt(3, progress); >> statement.addBatch(); if (count % batchSize == 0) < statement.executeBatch(); >> workbook.close(); // execute the remaining queries statement.executeBatch(); connection.commit(); connection.close(); long end = System.currentTimeMillis(); System.out.printf("Import done in %d ms\n", (end - start)); > catch (IOException ex1) < System.out.println("Error reading file"); ex1.printStackTrace(); >catch (SQLException ex2) < System.out.println("Database error"); ex2.printStackTrace(); >> >

As you can see, we use JDBC batch update feature to efficiently insert a large number of rows into the database, with batch size of 20 – which means it will group maximum 20 SQL statements in each batch sent to the server for processing.

We also use JDBC transaction to make sure that either all rows inserted or none. Finally, we measure the time it takes to complete the reading and inserting of data.

Run this program and you would see the output somehow like this:

That’s how to write Java code that imports data from an Excel file and insert into database. For your reference, I attached the sample project in the Attachments section below.

Related Tutorials:

Other Java Coding Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Источник

Java code example to export data from database to Excel file

This tutorial helps you write Java code to export data from a table in database to an Excel file – a very common task of a software program. To read data from database, we use JDBC with appropriate JDBC driver (MySQL is used in this tutorial). And to generate Excel file, we use Apache POI library.

Suppose that we have a table with the following structure:

This table contains some data like this:

I will guide you how to code simple program that exports data from this table to Excel 2007+ format (XSLX), and an advanced program that can export data from any table.

First, make sure that you specify the dependencies for MySQL JDBC driver and Apache POI API for Excel in Maven’s pom.xml file:

 mysql mysql-connector-java 5.1.46 runtime  org.apache.poi poi-ooxml 4.1.0 

1. Simple Java code example to export data from database to Excel file

The following code is for a simple Java program that connects to a MySQL database, reads all rows from the Review table and writes the data to an Excel file:

package net.codejava; import java.io.*; import java.sql.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; /** * A simple Java program that exports data from database to Excel file. * @author Nam Ha Minh * (C) Copyright codejava.net */ public class SimpleDb2ExcelExporter < public static void main(String[] args) < new SimpleDb2ExcelExporter().export(); >public void export() < String jdbcURL = "jdbc:mysql://localhost:3306/sales"; String username = "root"; String password = "password"; String excelFilePath = "Reviews-export.xlsx"; try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) < String sql = "SELECT * FROM review"; Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Reviews"); writeHeaderLine(sheet); writeDataLines(result, workbook, sheet); FileOutputStream outputStream = new FileOutputStream(excelFilePath); workbook.write(outputStream); workbook.close(); statement.close(); >catch (SQLException e) < System.out.println("Datababse error:"); e.printStackTrace(); >catch (IOException e) < System.out.println("File IO error:"); e.printStackTrace(); >> private void writeHeaderLine(XSSFSheet sheet) < Row headerRow = sheet.createRow(0); Cell headerCell = headerRow.createCell(0); headerCell.setCellValue("Course Name"); headerCell = headerRow.createCell(1); headerCell.setCellValue("Student Name"); headerCell = headerRow.createCell(2); headerCell.setCellValue("Timestamp"); headerCell = headerRow.createCell(3); headerCell.setCellValue("Rating"); headerCell = headerRow.createCell(4); headerCell.setCellValue("Comment"); >private void writeDataLines(ResultSet result, XSSFWorkbook workbook, XSSFSheet sheet) throws SQLException < int rowCount = 1; while (result.next()) < String courseName = result.getString("course_name"); String studentName = result.getString("student_name"); float rating = result.getFloat("rating"); Timestamp timestamp = result.getTimestamp("timestamp"); String comment = result.getString("comment"); Row row = sheet.createRow(rowCount++); int columnCount = 0; Cell cell = row.createCell(columnCount++); cell.setCellValue(courseName); cell = row.createCell(columnCount++); cell.setCellValue(studentName); cell = row.createCell(columnCount++); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper creationHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); cell.setCellStyle(cellStyle); cell.setCellValue(timestamp); cell = row.createCell(columnCount++); cell.setCellValue(rating); cell = row.createCell(columnCount); cell.setCellValue(comment); >> >

Note that the writeHeaderLine() method writes the column names of the table to the first line in the Excel file. The column names are known beforehand and fixed. The first column (ID) is omitted.

The writeDataLines() method iterates over all rows in the result set returned from the database, and writes data to the Excel file. Note that there’s a datetime value so a cell style is created to format value as datetime.

Run this program, you will see the Reviews-export.xlsx file is generated in the same directory of the program. Open this file by Microsoft Excel application and you will see:

2. Advanced Java code example to export data from database to Excel file

Let’s see the code of a more advanced program that can export data from any table in the database to Excel file. Following is the full code:

package net.codejava; import java.io.*; import java.sql.*; import java.text.*; import java.util.Date; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; /** * An advanced Java program that exports data from any table to Excel file. * @author Nam Ha Minh * (C) Copyright codejava.net */ public class AdvancedDb2ExcelExporter < public static void main(String[] args) < AdvancedDb2ExcelExporter exporter = new AdvancedDb2ExcelExporter(); exporter.export("Review"); exporter.export("Product"); >private String getFileName(String baseName) < DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss"); String dateTimeInfo = dateFormat.format(new Date()); return baseName.concat(String.format("_%s.xlsx", dateTimeInfo)); >public void export(String table) < String jdbcURL = "jdbc:mysql://localhost:3306/sales"; String username = "root"; String password = "password"; String excelFilePath = getFileName(table.concat("_Export")); try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) < String sql = "SELECT * FROM ".concat(table); Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(table); writeHeaderLine(result, sheet); writeDataLines(result, workbook, sheet); FileOutputStream outputStream = new FileOutputStream(excelFilePath); workbook.write(outputStream); workbook.close(); statement.close(); >catch (SQLException e) < System.out.println("Datababse error:"); e.printStackTrace(); >catch (IOException e) < System.out.println("File IO error:"); e.printStackTrace(); >> private void writeHeaderLine(ResultSet result, XSSFSheet sheet) throws SQLException < // write header line containing column names ResultSetMetaData metaData = result.getMetaData(); int numberOfColumns = metaData.getColumnCount(); Row headerRow = sheet.createRow(0); // exclude the first column which is the ID field for (int i = 2; i > private void writeDataLines(ResultSet result, XSSFWorkbook workbook, XSSFSheet sheet) throws SQLException < ResultSetMetaData metaData = result.getMetaData(); int numberOfColumns = metaData.getColumnCount(); int rowCount = 1; while (result.next()) < Row row = sheet.createRow(rowCount++); for (int i = 2; i else cell.setCellValue((String) valueObject); > > > private void formatDateCell(XSSFWorkbook workbook, Cell cell) < CellStyle cellStyle = workbook.createCellStyle(); CreationHelper creationHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); cell.setCellStyle(cellStyle); >>

In this program, the name of the Excel file is generated based on table name followed by the current datetime – as you can see in the getFileName() method. The writeHeaderLine() and writeDataLines() methods use ResultSetMetaData to read column names so this program can work with any table.

Note that this program excludes the first column of the table, which is supposed to be the ID column.

And you can specify the table name when running this program, for example:

AdvancedDb2ExcelExporter exporter = new AdvancedDb2ExcelExporter(); exporter.export("Review"); exporter.export("Product");

That’s a couple of example programs that show you how to export data from database to Excel file. To learn more about writing Excel file, read this tutorial: How to Write Excel Files in Java using Apache POI

Other Java Coding Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

Comments

With the above same code getting below exception even though I added more jars to the build path.
Exception in thread «main» java.lang.NoClassDefFoundError: org/apache/logging/log4j/LogManager
at org.apache.poi.ooxml.POIXMLDocumentPart.
Caused by: java.lang.ClassNotFoundException: org.apache.logging.log4j.LogManager

Источник