English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
This simple operation will export data from the database to generate an Excel report and import Excel data into the database
Firstly, establish a database connection pool:
package jdbc;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class BaseDAO {
private static BasicDataSource ds;
static{
try {
//1.Read the configuration file conf.properties, using java.util.Properties to read
Properties p=new Properties();
//2.Read and parse the content of the configuration file through the file stream, the local database uses mysql, so un-comment the configuration file mysql, and comment out the configuration of other databases
p.load(new FileInputStream("src/jdbc.properties"));
String driverName=p.getProperty("jdbc.driverClassName");//Get the driver name
String url=p.getProperty("jdbc.url");//Get the database URL
String user=p.getProperty("jdbc.username");//Username
String password=p.getProperty("jdbc.password");//Password
int maxActive=Integer.parseInt(p.getProperty("jdbc.maxActive"));//Get the maximum number of connections
int maxWait=Integer.parseInt(p.getProperty("jdbc.maxWait"));//Get the maximum wait time
//3.Create a connection pool
ds=new BasicDataSource();
ds.setDriverClassName(driverName);//Set the driver name
ds.setUrl(url);//Set the database address
ds.setUsername(user);//Set the username
ds.setPassword(password);//Set the password
ds.setMaxActive(maxActive);//Set the maximum number of connections
ds.setMaxWait(maxWait);//Set the maximum wait time
}
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
try {
return ds.getConnection();
}
System.out.println("Database connection exception");
throw e;
}
}
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
}
e.printStackTrace();
}
}
}
}
Generate a Java entity class corresponding to the database:
package entity;
public class Test {
private String a;
private String b;
private String c;
private String d;
private String e;
private String f;
private String g;
private String h;
private String i;
private String j;
public String getA() {
return a;
}
public void setA(String a) {
this.a = a;
}
public String getB() {
return b;
}
public void setB(String b) {
this.b = b;
}
public String getC() {
return c;
}
public void setC(String c) {
this.c = c;
}
public String getD() {
return d;
}
public void setD(String d) {
this.d = d;
}
public String getE() {
return e;
}
public void setE(String e) {
this.e = e;
}
public String getF() {
return f;
}
public void setF(String f) {
this.f = f;
}
public String getG() {
return g;
}
public void setG(String g) {
this.g = g;
}
public String getH() {
return h;
}
public void setH(String h) {
this.h = h;
}
public String getI() {
return i;
}
public void setI(String i) {
this.i = i;
}
public String getJ() {
return j;
}
public void setJ(String j) {
this.j = j;
}
}
Insert excel table data into the database, first read the excel table data
package readExcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
/**
* @param args
* @throws IOException
*/
public List<List<String>> readExcel(File file) throws IOException{
List<List<String>> list=new ArrayList<List<String>>();
if(!file.exists()){
System.out.println("文件不存在");
}else{
InputStream fis=new FileInputStream(file);
list=parseExcel(file,fis);
}
return list;
}
public List<List<String>> parseExcel(File file,InputStream fis) throws IOException{
Workbook workbook=null;
List<List<String>> list=new ArrayList<List<String>>();
if(file.toString().endsWith("xls")){
workbook=new HSSFWorkbook(fis);
}else if(file.toString().endsWith("xlsx")){}}
workbook=new XSSFWorkbook(fis);
}else{
System.out.println("The file is not an excel document type, it cannot be read here");
}
for(int i=0;i<workbook.getNumberOfSheets();i++){
Sheet sheet=workbook.getSheetAt(i);
if(sheet!=null){
int lastRow=sheet.getLastRowNum();
//Get each row of the table
for(int j=0;j<=lastRow;j++){
Row row=sheet.getRow(j);
short firstCellNum=row.getFirstCellNum();
short lastCellNum=row.getLastCellNum();
List<String> rowsList=new ArrayList<String>();
if(firstCellNum!=lastCellNum){
//Get each column of each row
for(int k=firstCellNum;k<lastCellNum;k++){
Cell cell=row.getCell(k);
if(cell==null){
rowsList.add("");
}else{
rowsList.add(chanegType(cell));
}
}
}else{
System.out.println("This table has only one column");
}
list.add(rowsList);
}
}
}
return list;
}
public String chanegType(Cell cell){
String result = new String();
switch (cell.getCellType()) { //Get the type of the cell
case HSSFCell.CELL_TYPE_NUMERIC:// Numeric type
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ //If it is a numeric type
short format = cell.getCellStyle().getDataFormat(); //Get the value corresponding to the type of this cell
SimpleDateFormat sdf = null;
if(format == 14 || format == 31 || format == 57 || format == 58){ //If the value is14,31,57,58one of them
//The corresponding date format is 2016-03-01in this form
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);//Get yyyy-MM-dd such format date
}else if (format == 20 || format == 32) {
//Time
sdf = new SimpleDateFormat("HH:mm");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);//Get HH:mm
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat dataformat = new DecimalFormat();
String temp = style.getDataFormatString();
// Set the cell to General
if (temp.equals("General")) {
dataformat.applyPattern("#");
}
result = dataformat.format(value); //Get the cell value
}
}
break;
case HSSFCell.CELL_TYPE_STRING:// String type
result = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
return result;
}
}
Insert the read excel table data into the database
package importdata;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import entity.Test;
import readExcel.ReadExcel;
import jdbc.BaseDAO;
public class importData {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
List<List<String>> list = new ArrayList<List<String>>();
ReadExcel readExcel=new ReadExcel();
File file=new File("d:/test.xlsx");
list=readExcel.readExcel(file);
Test test=new Test();
Connection conn=BaseDAO.getConnection();
PreparedStatement ps=null;
int i=1;
for(List<String> rowlist:list){
if(rowlist!=null){
1).toString());
2).toString());
3).toString());
4).toString());
5).toString());
6).toString());
7).toString());
8).toString());
test.setJ(rowlist.get(9).toString());
String sql="insert into TEST(A,B,C,D,E,F,G,H,I,J) values(?,?,?,?,?,?,?,?,?,?);";
ps=conn.prepareStatement(sql);
ps.setString(1,test.getA());
ps.setString(2,test.getB());
ps.setString(3,test.getC());
ps.setString(4,test.getD());
ps.setString(5,test.getE());
ps.setString(6,test.getF());
ps.setString(7,test.getG());
ps.setString(8,test.getH());
ps.setString(9
ps.setString(10,test.getJ());
int n=ps.executeUpdate();
if(n!=1){
System.out.println("Failed to insert data into the database");
}
System.out.println("The ",+i+"Data insertion successful");
System.out.println();
i++;
}
}
}
}
Query data from the database and generate a report in the form of an Excel table
package export;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import entity.Test;
import jdbc.BaseDAO;
public class Export {
public static void createExcel(List<Test> list){
FileOutputStream fos=null;
Workbook workbook=new XSSFWorkbook();
Sheet sheet=workbook.createSheet("Test File");
String[] title={"First Column","Second Column","Third Column","Fourth Column","Fifth Column","Sixth Column","Seventh Column","Eighth Column","Ninth Column","Tenth Column"};
Row row=sheet.createRow((short)0);
int i=0;
for(String s:title){
Cell cell=row.createCell(i);
cell.setCellValue(s);
i++;
}
int j=1;
for(Test t:list){
//Create the second row
Row rowData=sheet.createRow((short)j);
//Data in the first column
Cell cell0=rowData.createCell((short)0);
cell0.setCellValue(t.getA());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Second column data
Cell cell1=rowData.createCell((short)1);
cell1.setCellValue(t.getB());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Third column data
Cell cell2=rowData.createCell((short)2);
cell2.setCellValue(t.getC());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Fourth column data
Cell cell3=rowData.createCell((short)3);
cell3.setCellValue(t.getD());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Fifth column data
Cell cell4=rowData.createCell((short)4);
cell4.setCellValue(t.getE());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Sixth column data
Cell cell5=rowData.createCell((short)5);
cell5.setCellValue(t.getF());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Seventh column data
Cell cell6=rowData.createCell((short)6);
cell6.setCellValue(t.getG());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Eighth column data
Cell cell7=rowData.createCell((short)7);
cell7.setCellValue(t.getH());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Ninth column data
Cell cell8=rowData.createCell((short)8);
cell8.setCellValue(t.getI());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
//Tenth column data
Cell cell9=rowData.createCell((short)9);
cell9.setCellValue(t.getJ());
//Set cell width
sheet.setColumnWidth((short)0, (short)10000);
j++;
}
try {
//Export database file save path
/export.xlsx
/*if(fos.toString().endsWith("xlsx")){
workbook=new XSSFWorkbook();
} else if(fos.toString().endsWith("xls")){
workbook=new HSSFWorkbook();
}*/
//Write workbook to file
workbook.write(fos);
System.out.println("Export file successful");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Export file failed");
}
}
public static void main(String[] args) throws Exception {
//Connect to database
Connection conn=BaseDAO.getConnection();
PreparedStatement ps=null;
String sql="select * from TEST";
//Execute the sql statement
ps=conn.prepareStatement(sql);
//The results obtained after querying the database
ResultSet rs=ps.executeQuery();
List<Test> list=new ArrayList<Test>();
//Traverse the query results
while(rs.next()){
Test test=new Test();
test.setA(rs.getString("A"));
test.setB(rs.getString("B"));
test.setC(rs.getString("C"));
test.setD(rs.getString("D"));
test.setE(rs.getString("E"));
test.setF(rs.getString("F"));
test.setG(rs.getString("G"));
test.setH(rs.getString("H"));
test.setI(rs.getString("I"));
test.setJ(rs.getString("J"));
list.add(test);
}
createExcel(list);
}
}
That's all for this article. I hope it will be helpful to your learning and I also hope that everyone will support the Yelling Tutorial more.
Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume relevant legal liabilities. If you find any content suspected of copyright infringement, please send an email to notice#w3Please send an email to codebox.com (replace # with @ when sending email) to report violations, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.