Java excel to mysql

Java code to save excel data to mysql

As you have an in which you’re adding cell values, you can proceed as follows: You’ll need make this little change: I also suggest you check out this MySQL and Java JDBC — Tutorial Solution: Use Apache POI to read data from xls files. Solution: As Mathias Schwarz wrote: You can read an Excel file in Java using the Apache POI library: http://poi.apache.org/ There are some code samples on their howto page which will tell you how to read an Excel file: http://poi.apache.org/spreadsheet/how-to.html To store the data you can use the JDBC driver for MySQL.

How to save the excel file data into mysql database table as a record by record using apache POI

 import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.util.*; import java.sql.*; public class XLToDB < public static final String INSERT_RECORDS = "INSERT INTO RECORDS(ID, NAME, VALUE1, VALUE2, VALUE3, VALUE4, VALUE5) VALUES(. )"; private static String GET_COUNT = "SELECT COUNT(*) FROM RECORDS"; public static void main(String[] args) throws Exception< XLToDB obj = new XLToDB(); obj.insertRecords("E:/test.xlsx"); >public void insertRecords(String filePath) < /* Create Connection objects */ Connection con = null; PreparedStatement prepStmt = null; java.sql.Statement stmt = null; int count = 0; ArrayListmylist = new ArrayList(); try < con = DBHelper.getConnection(); System.out.println("Connection :: ["+con+"]"); prepStmt = con.prepareStatement(INSERT_RECORDS); stmt = con.createStatement(); ResultSet result = stmt.executeQuery(GET_COUNT); while(result.next()) < int val = result.getInt(1); System.out.println(val); count = val+1; >//prepStmt.setInt(1,count); /* We should now load excel objects and loop through the worksheet data */ FileInputStream fis = new FileInputStream(new File(filePath)); System.out.println("FileInputStream Object created. "); /* Load workbook */ XSSFWorkbook workbook = new XSSFWorkbook (fis); System.out.println("XSSFWorkbook Object created. "); /* Load worksheet */ XSSFSheet sheet = workbook.getSheetAt(0); System.out.println("XSSFSheet Object created. "); // we loop through and insert data Iterator ite = sheet.rowIterator(); System.out.println("Row Iterator invoked. "); while(ite.hasNext()) < Row row = (Row) ite.next(); System.out.println("Row value fetched. "); IteratorcellIterator = row.cellIterator(); System.out.println("Cell Iterator invoked. "); int index=1; while(cellIterator.hasNext()) < Cell cell = cellIterator.next(); System.out.println("getting cell value. "); switch(cell.getCellType()) < case Cell.CELL_TYPE_STRING: //handle string columns prepStmt.setString(index, cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //handle double data int i = (int)cell.getNumericCellValue(); prepStmt.setInt(index, (int) cell.getNumericCellValue()); break; >index++; > //we can execute the statement before reading the next row prepStmt.executeUpdate(); > /* Close input stream */ fis.close(); /* Close prepared statement */ prepStmt.close(); /* Close connection */ con.close(); >catch(Exception e) < e.printStackTrace(); >> > my excel is like below 101 Mobiles 10 10 10 10 10 102 Batteries 20 21 22 20 56 103 Watches 25 242 52 55 28 104 Keyboards 10 11 12 12 12 105 Pens 5 5 5 5 3 -----------------------------------> in my database it saves in this direction[--------------------->>>] 

Java code to save excel data to mysql Code Example, Get code examples like «java code to save excel data to mysql» instantly right from your google search results with the Grepper Chrome Extension. Grepper. Follow. java code to save excel data to mysql . sql by Black Beetle on Apr 29 2020 Donate . 0 Source: java2career.com. java code to save excel data to mysql . sql by Black

How to store data from excel sheet into mysql database using jdbc connection

As you have an ArrayList in which you’re adding cell values, you can proceed as follows:

public void insertRowInDB(List cellValues) < Connection con = null; PreparedStatement preparedStatement = null; try< Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/databaseName?" + "user=sqlUser&password=sqlPassword"); String sql = "INSERT INTO tableName(field1, field2, . fieldN) VALUES (. )"; preparedStatement = con.prepareStatement(sql); int paramIndex = 1; for(Object cell : cellValues)< preparedStatement.setObject(paramIndex, cell); paramIndex++; >int status = preparedStatement.executeUpdate(); //DO something with the status if needed > catch(SQLException ex) < /* log the exception */ >finally < try< preparedStatemnt.close(); con.close(); >catch(SQLException ignored) <> > > 

You’ll need make this little change:

while(rows.hasNext()) < List list = new ArrayList(); // initialize the list here HSSFRow row=(HSSFRow)rows.next(); System.out.println("\n"); Iterator cells=row.cellIterator(); while( cells.hasNext() ) < /* all your code seems fine here, just uncomment list.add(something) lines */ >insertRowInDB(list); // insert the cells in your database > 

I also suggest you check out this MySQL and Java JDBC — Tutorial

Import excel spreadsheet data into MYSQL database, import excel spreadsheet data into MYSQL database using java. Ask Question Asked 6 years, 1 month ago. pstm.close(); con.close(); input.close(); System.out.println(«Success import excel to mysql table»); > catch (IOException e) < e.printStackTrace(); >> > I have debug the entire code and it is compiling till line …

How to import a .xls file to mysql Database using java?

Use Apache POI to read data from xls files.

public extractDataFromXls(String fileName, sheetName) throws FileNotFoundException < FileInputStream fileInputStream = new FileInputStream(fileName); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet(sheetName); HSSFRow row = worksheet.getRow(0); for (short i = 0; i < 5; i++) < HSSFCell cell = row.getCell(i); String value = cell.getStringCellValue(); System.out.println(value); >> 

Java — import from excel to mysql, java — import from excel to mysql. Ask Question Asked 3 years, 7 This code show in console all data from row, but I don’t now to add that data in list and list import to MySQL. to open an Excel workbook, and then read it. Even after doing this, you still would need to use JDBC to write that data to MySQL. Share. …

You can read an Excel file in Java using the Apache POI library: http://poi.apache.org/ There are some code samples on their howto page which will tell you how to read an Excel file: http://poi.apache.org/spreadsheet/how-to.html

To store the data you can use the jdbc driver for mysql. You can get the connector here: http://www.mysql.com/products/connector/

How to import an excel file in to a MySQL database, now the table is created, goto > Tools > Import > Import Wizard. now in the import wizard dialogue box, click Next. click Add File, browse and select the file to be imported. choose the correct dilimination. («,» seperated for .csv file) click Next, check if the mapping is done properly.

Источник

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.

Источник

How to Import an Excel File into MySQL Database Quickly

Introduction to importing Excel Files into a SQL database.

Excel files can import into MySQL database using some library files. Actually not the excel file but excel file content by reading the excel and import into MySQL database. This article will help you to do so. Let’s start this with dividing this into two part. Part one will do import Excel files into a jTable first. Part two will do a bulk upload the data contains in jTable. Let’s start.

What kinds of files can be imported?

Here we are dealing with excel files only. (.xls, .xlsx, .xlsm files )

Step 01 : Import Excel File into jTable

To import data from excel to java jTable you need Apache POI(Apache POI – the Java API for Microsoft Documents) library added to your project libraries. Download and extract the compressed files. This application selects excel file from the computer and displays it on java jTable.

We are using one excel file to import which contains 5 columns, if your excel file contains more columns or less change the code accordingly.

Click import excel button, then choose excel file you want to import, then file will be imported and displayed on the JTable. Below code is for a function which help to import Excel file, once you added the Library files copy and paste below function into your project and call it whenever required or button click of an “import Excel” button.

 public void importExcelToJtableJava() < DefaultTableModel model= (DefaultTableModel) boq_jtable.getModel(); File excelFile; FileInputStream excelFIS = null; BufferedInputStream excelBIS = null; XSSFWorkbook excelImportToJTable = null; String defaultCurrentDirectoryPath = "C:\\Users\\Authentic\\Desktop"; JFileChooser excelFileChooser = new JFileChooser(defaultCurrentDirectoryPath); excelFileChooser.setDialogTitle("Select Excel File"); FileNameExtensionFilter fnef = new FileNameExtensionFilter("EXCEL FILES", "xls", "xlsx", "xlsm"); excelFileChooser.setFileFilter(fnef); int excelChooser = excelFileChooser.showOpenDialog(null); if (excelChooser == JFileChooser.APPROVE_OPTION) < try < excelFile = excelFileChooser.getSelectedFile(); jExcelFilePath.setText(excelFile.toString()); excelFIS = new FileInputStream(excelFile); excelBIS = new BufferedInputStream(excelFIS); excelImportToJTable = new XSSFWorkbook(excelBIS); XSSFSheet excelSheet = excelImportToJTable.getSheetAt(0); for (int row = 0; row < excelSheet.getLastRowNum(); row++) < XSSFRow excelRow = excelSheet.getRow(row); XSSFCell excelLineNum = excelRow.getCell(0); XSSFCell excelItemName = excelRow.getCell(1); XSSFCell excelDescription = excelRow.getCell(2); XSSFCell excelServiceDuration = excelRow.getCell(3); XSSFCell excelQuantity = excelRow.getCell(4); model.addRow(new Object[]); > JOptionPane.showMessageDialog(null, "Imported Successfully . "); > catch (IOException iOException) < JOptionPane.showMessageDialog(null, iOException.getMessage()); >finally < try < if (excelFIS != null) < excelFIS.close(); >if (excelBIS != null) < excelBIS.close(); >if (excelImportToJTable != null) < excelImportToJTable.close(); >> catch (IOException iOException) < JOptionPane.showMessageDialog(null, iOException.getMessage()); >> > > 

Step 02: Upload jTable data into MySQL Database

Now, jTable is ready with data and it is ready to bulk insert to MySQL database. Let’s use “executeBatch()” function. Read jTable raws one by one using for loop and execute a batch upload.

Remember that Database must be connected to MySQL before doing this step. If you don’t know how to do it, please check this link. Below code is function to execute batch update when clicking a submit or save button.

 private void jButtonSaveActionPerformed(java.awt.event.ActionEvent evt) < // TODO add your handling code here: DefaultTableModel bulkModel = (DefaultTableModel) boq_jtable.getModel(); Class.forName("com.mysql.jdbc.Driver"); Connection con=null; con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/smartlead","root",""); bulkStmt = con.createStatement(); if(con==null) JOptionPane.showMessageDialog(this, "Unable to Connect to server. "); for(int i = 0; i < bulkModel.getRowCount(); i++)< String tempClientId = bulkModel.getValueAt(i, 0).toString(); String tempLineNum = bulkModel.getValueAt(i, 1).toString(); String tempItemName= bulkModel.getValueAt(i, 2).toString(); String tempDescription = bulkModel.getValueAt(i, 3).toString(); String tempServiceDuration = bulkModel.getValueAt(i, 4).toString(); String tempQuantity = bulkModel.getValueAt(i, 5).toString(); bulkQuery="INSERT INTO `boq_network`( `client_id`, `line_number`, `item_name`, `description`, `service_duration`, `quantiry`) VALUES ('"+tempClientId+"','"+tempLineNum+"','"+tempItemName+"','"+tempDescription+"','"+tempServiceDuration+"','"+tempQuantity+"')"; bultStmt.addBatch(bulkQuery); >int[] rowsInserted = bultStmt.executeBatch(); System.out.println("Data Inserted"); System.out.println("rowsInserted Count = " + rowsInserted.length); JOptionPane.showMessageDialog(null, "Saved"); > 

Источник

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.

Источник