Java to excel code

Convert CSV to Excel or Excel to CSV File using Java

CSV file format is used to save comma-separated values. CSV to Excel conversion is helpful when there are numbers involved in the input data and you need to perform some calculations. Likewise, Excel to CSV conversion is helpful in some scenarios, like serialization of data in some cases. Let us explore the inter-conversion between CSV and Excel file format (XLS or XLSX) in detail using Aspose.Cells for Java API:

Java CSV to Excel and Excel to CSV Converter#

To convert these file formats, firstly you need to configure Aspose.Cells for Java API in your Java environment. This will let you achieve the requirements with only a couple of lines of code. The API will take care of the rest of the tasks and activities. You can set it up either by downloading the JAR file or by adding the following Maven Repository specifications:

 AsposeJavaAPI Aspose Java API https://repository.aspose.com/repo/  

 com.aspose aspose-cells 23.4  

After installing and configuring the API successfully, let us proceed to learn the conversion CSV and Excel (XLSX/XLS) file formats.

Convert CSV to Excel XLS in Java#

One of the popular use cases for CSV to Excel conversion is when you need to populate some values into a Table in the Excel worksheet. Likewise, the are many possibilities and use cases where you might need to perform this conversion. To keep this simple and easy, let us consider a basic CSV file as an example:

My Data. Items A,Items B,Items C,Items D,Items E,Items F,Items G,Items H 12,23,33,66,11,87,99,33 23,22,33,77,31,22,45,56 34,11,12,23,22,34,11,12 45,43,54,88,36,45,45,37 65,65,65,65,13,65,9,35 34,22,27,22,32,23,23,32 213,186,224,341,145,276,232,205 

Let us follow the steps below to convert a CSV to Excel file:

1. Initialize LoadOptions class object
2. Specify FileFormatType as CSV
3. Instantiate an object of Workbook class
4. Save the output Excel file

The code snippet below shows how to convert CSV to Excel (XLSX/XLS) using Java:

The screenshot below shows the output Excel file generated with the above code snippet:

Convert Excel XLS to CSV in Java#

Excel (XLSX/XLS) to CSV conversion is also an important use case. As we have created an Excel file already, let us consider it as an input file to proceed with the conversion. This can explain the high fidelity between the conversion of CSV and Excel files that we have converted already. Let us follow the steps below for converting Excel (XLSX/XLS) to CSV using Java.

1. Load the input Excel file with Workbook constructor
2. Save output CSV file by specifying the CSV SaveFormat

The following code snippet shows how to convert Excel (XLSX/XLS) to CSV in Java:

After converting the input Excel file, you may compare the output CSV file with the file which was used above in the first example. You will notice the output files are identical to each other. This high fidelity speaks volumes about the efficiency of these file format conversions.

Online CSV and Excel Converter Tools#

We also provide online tools to convert CSV to Excel and Excel to CSV, which are based on Aspose.Cells. These are free tools and you can use them without signing up.

Conclusion#

In a nutshell, we have learned how easily we can convert an Excel file to CSV and CSV to Excel with Java. However, if you notice any problem with configuration or conversion then please feel free to reach out to us at Free Support Forum. We would love to help you out!

See Also#

• CSV to Excel Files or Excel to CSV in C#
• JSON to Excel in Java
• Word to JSON in C#
• Word to JSON in Python
• DOC to JSON in C#
• Excel File to JSON in Python
• XLSX to JSON in C#
• XLS to JSON in C#
• XLSX to JSON in Java
• Excel to JSON in Java
• DOCX to JSON in Java
• DOC to JSON in Java
• Word to JSON in Java
• TXT to JSON in Java
• JSON to PDF in Python
• Excel to JSON in Java
• TXT to JSON in Python

• Aspose.Cells Product Family

• CSV to Excel
• CSV to XLS
• CSV to XLSX
• Convert CSV to Excel
• Excel to CSV
• XLS to CSV
• XLSX to CSV
• csv to excel java
• csv to xlsx java
• java convert csv to xlsx
• java csv to xlsx

Источник

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

Источник