java generates sample excel report files
- 2020-06-07 04:31:08
- OfStack
This simple operation exports the data from the database to generate the excel report and imports the excel data into the database
First, establish the connection pool of the database:
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 configuration file conf.properties, using java.util.Properties To read the
Properties p=new Properties();
//2. Read and parse the configuration file contents through a file stream , For the local database mysql, So put the profile mysql Configuration release of , Other database configuration comments
p.load(new FileInputStream("src/jdbc.properties"));
String driverName=p.getProperty("jdbc.driverClassName");// Get the driver name
String url=p.getProperty("jdbc.url");// Get database url
String user=p.getProperty("jdbc.username");// The user name
String password=p.getProperty("jdbc.password");// password
int maxActive=Integer.parseInt(p.getProperty("jdbc.maxActive"));// Gets the maximum number of connections
int maxWait=Integer.parseInt(p.getProperty("jdbc.maxWait"));// Gets the maximum wait time
//3. create 1 A connection pool
ds=new BasicDataSource();
ds.setDriverClassName(driverName);// Set the driver name
ds.setUrl(url);// Set the database address
ds.setUsername(user);// Set user name
ds.setPassword(password);// Set the password
ds.setMaxActive(maxActive);// Set the maximum number of connections
ds.setMaxWait(maxWait);// Set the maximum wait time
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
try {
return ds.getConnection();
} catch (Exception e) {
System.out.println(" Connection database exception ");
throw e;
}
}
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Generate java entity classes 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 and read excel table data first
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(" File does not exist ");
}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(" File is not excel The document type 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 in the table 1 line
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){
// For each 1 Each of line 1 column
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 form has only 1 column ");
}
list.add(rowsList);
}
}
}
return list;
}
public String chanegType(Cell cell){
String result = new String();
switch (cell.getCellType()) { // Gets the type of the cell
case HSSFCell.CELL_TYPE_NUMERIC:// Numeric types
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ // If it's a numeric type
short format = cell.getCellStyle().getDataFormat(); // Gets the numeric value corresponding to the type of the cell
SimpleDateFormat sdf = null;
if(format == 14 || format == 31 || format == 57 || format == 58){ // If the value is 14,31,57,58 One of the 1 Kind of
// The corresponding date format is 2016-03-01 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 This 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();
// The cells are set to normal
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;
}
}
Inserts the excel table data read 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 inportData {
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){
test.setA(rowlist.get(0).toString());
test.setB(rowlist.get(1).toString());
test.setC(rowlist.get(2).toString());
test.setD(rowlist.get(3).toString());
test.setE(rowlist.get(4).toString());
test.setF(rowlist.get(5).toString());
test.setG(rowlist.get(6).toString());
test.setH(rowlist.get(7).toString());
test.setI(rowlist.get(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,test.getI());
ps.setString(10,test.getJ());
int n=ps.executeUpdate();
if(n!=1){
System.out.println(" Data insertion into database failed ");
}
System.out.println(" The first "+i+" Bar insert successful ");
System.out.println();
i++;
}
}
}
}
Query the data in the database and generate the report in the form of 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(" The test file ");
String[] title={" The first 1 column "," The first 2 column "," The first 3 column "," The first 4 column "," The first 5 column "," The first 6 column "," The first 7 column "," The first 8 column "," The first 9 column "," The first 10 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 first 2 line
Row rowData=sheet.createRow((short)j);
// The first 1 Column data
Cell cell0=rowData.createCell((short)0);
cell0.setCellValue(t.getA());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 2 Column data
Cell cell1=rowData.createCell((short)1);
cell1.setCellValue(t.getB());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 3 Column data
Cell cell2=rowData.createCell((short)2);
cell2.setCellValue(t.getC());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 4 Column data
Cell cell3=rowData.createCell((short)3);
cell3.setCellValue(t.getD());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 5 Column data
Cell cell4=rowData.createCell((short)4);
cell4.setCellValue(t.getE());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 6 Column data
Cell cell5=rowData.createCell((short)5);
cell5.setCellValue(t.getF());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 7 Column data
Cell cell6=rowData.createCell((short)6);
cell6.setCellValue(t.getG());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 8 Column data
Cell cell7=rowData.createCell((short)7);
cell7.setCellValue(t.getH());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 9 Column data
Cell cell8=rowData.createCell((short)8);
cell8.setCellValue(t.getI());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
// The first 10 Column data
Cell cell9=rowData.createCell((short)9);
cell9.setCellValue(t.getJ());
// Set the width of the cell
sheet.setColumnWidth((short)0, (short)10000);
j++;
}
try {
// Export database file to save path
fos=new FileOutputStream("D:/export.xlsx");
/*if(fos.toString().endsWith("xlsx")){
workbook=new XSSFWorkbook();
}else if(fos.toString().endsWith("xls")){
workbook=new HSSFWorkbook();
}*/
// Write the workbook to a 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";
// perform sql statements
ps=conn.prepareStatement(sql);
// The result of querying the database
ResultSet rs=ps.executeQuery();
List<Test> list=new ArrayList<Test>();
// Iterate through 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);
}
}