Skip to content

Instantly share code, notes, and snippets.

@madan712
Last active June 27, 2019 19:23
Show Gist options
  • Save madan712/1bdaee47739d3bd3c60ed98ceb259765 to your computer and use it in GitHub Desktop.
Save madan712/1bdaee47739d3bd3c60ed98ceb259765 to your computer and use it in GitHub Desktop.
Java - Export database tables to an excel file
package com.javaxp;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
@SpringBootApplication
public class Application implements CommandLineRunner {
@Autowired
private JdbcTemplate jdbcTemplate;
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
public void run(String... args) throws Exception {
// Export database table
export("TABLE_1");
export("TABLE_2");
}
public void export(String tableName) {
writeExcelFile(tableName + ".xlsx", getData(tableName));
}
public void writeExcelFile(String fileName, List<Map<String, Object>> data) {
// 10000 - the number of rows that are kept in memory until flushed out
SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
Sheet sheet = workbook.createSheet();
AtomicInteger rowIndex = new AtomicInteger();
data.forEach(rowData -> {
Row row = sheet.createRow(rowIndex.getAndIncrement());
AtomicInteger columnIndex = new AtomicInteger();
rowData.values().forEach(columnData -> {
Cell cell = row.createCell(columnIndex.getAndIncrement());
cell.setCellValue(columnData.toString());
});
});
try {
FileOutputStream out = new FileOutputStream(fileName);
workbook.write(out);
out.flush();
out.close();
// dispose of temporary files backing this workbook on disk
workbook.dispose();
workbook.close();
System.out.println("File created successfully:" + fileName);
} catch (IOException e) {
e.printStackTrace();
}
}
public List<Map<String, Object>> getData(String tableName) {
String sqlQuery = "SELECT * FROM " + tableName;
return jdbcTemplate.queryForList(sqlQuery);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment