此次简单的操作将数据从数据库导出生成excel报表以及将excel数据导入数据库
首先建立数据库的连接池：
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.读取配置文件conf.properties,采用java.util.properties来读取
properties p=new properties();
//2.通过文件流读取并解析配置文件内容,本地数据库用的mysql,所以把配置文件mysql的配置放开,其他数据库配置注释
p.load(new fileinputstream("src/jdbc.properties"));
string drivername=p.getproperty("jdbc.driverclassname");//获取驱动名称
string url=p.getproperty("jdbc.url");//获取数据库的url
string user=p.getproperty("jdbc.username");//用户名
string password=p.getproperty("jdbc.password");//密码
int maxactive=integer.parseint(p.getproperty("jdbc.maxactive"));//获取最大连接数
int maxwait=integer.parseint(p.getproperty("jdbc.maxwait"));//获取最大等待时间
//3.创建一个连接池
ds=new basicdatasource();
ds.setdriverclassname(drivername);//设置驱动名称
ds.seturl(url);//设置数据库地址
ds.setusername(user);//设置用户名
ds.setpassword(password);//设置密码
ds.setmaxactive(maxactive);//设置最大连接数
ds.setmaxwait(maxwait);//设置最大等待时间
} catch (exception e) {
e.printstacktrace();
}
}
public static connection getconnection() throws exception {
try {
return ds.getconnection();
} catch (exception e) {
system.out.println("连接数据库异常");
throw e;
}
}
public static void close(connection conn){
if(conn!=null){
try {
conn.close();
} catch (exception e) {
e.printstacktrace();
}
}
}
}
生成与数据库相对应的java实体类：
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;
}
}
将excel表格数据插入数据库，先读取excel表格数据
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("文件不是excel文档类型 ，此处无法读取");
}
for(int i=0;i<workbook.getnumberofsheets();i++){
sheet sheet=workbook.getsheetat(i);
if(sheet!=null){
int lastrow=sheet.getlastrownum();
//获取表格中的每一行
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(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("该表格只有一列");
}
list.add(rowslist);
}
}
}
return list;
}
public string chanegtype(cell cell){
string result = new string();
switch (cell.getcelltype()) { //获取单元格的类型
case hssfcell.cell_type_numeric:// 数字类型
if(cell.getcelltype() == hssfcell.cell_type_numeric){ //如果是数值类型
short format = cell.getcellstyle().getdataformat(); //获取这个单元的类型对应的数值
simpledateformat sdf = null;
if(format == 14 || format == 31 || format == 57 || format == 58){ //如果数值为14,31,57,58其中的一种
//对应的日期格式为 2016-03-01这种形式,
sdf = new simpledateformat("yyyy-mm-dd");
double value = cell.getnumericcellvalue();
date date = org.apache.poi.ss.usermodel.dateutil.getjavadate(value);
result = sdf.format(date);//得到yyyy-mm-dd这种格式日期
}else if (format == 20 || format == 32) {
//时间
sdf = new simpledateformat("hh:mm");
double value = cell.getnumericcellvalue();
date date = org.apache.poi.ss.usermodel.dateutil.getjavadate(value);
result = sdf.format(date);//得到hh:mm
} else {
double value = cell.getnumericcellvalue();
cellstyle style = cell.getcellstyle();
decimalformat dataformat = new decimalformat();
string temp = style.getdataformatstring();
// 单元格设置成常规
if (temp.equals("general")) {
dataformat.applypattern("#");
}
result = dataformat.format(value); //得到单元格数值
}
}
break;
case hssfcell.cell_type_string:// string类型
result = cell.getrichstringcellvalue().tostring();
break;
case hssfcell.cell_type_blank:
result = "";
default:
result = "";
break;
}
return result;
}
}
将读取到的excel表格数据插入到数据库中去
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("数据插入数据库失败");
}
system.out.println("第"+i+"条数据插入成功");
system.out.println();
i++;
}
}
}
}
将数据库中的数据查询出来并以excel表格的形式生成报表
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("测试文件");
string[] title={"第一列","第二列","第三列","第四列","第五列","第六列","第七列","第八列","第九列","第十列"};
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){
//创建第二行
row rowdata=sheet.createrow((short)j);
//第一列数据
cell cell0=rowdata.createcell((short)0);
cell0.setcellvalue(t.geta());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第二列数据
cell cell1=rowdata.createcell((short)1);
cell1.setcellvalue(t.getb());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第三列数据
cell cell2=rowdata.createcell((short)2);
cell2.setcellvalue(t.getc());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第四列数据
cell cell3=rowdata.createcell((short)3);
cell3.setcellvalue(t.getd());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第五列数据
cell cell4=rowdata.createcell((short)4);
cell4.setcellvalue(t.gete());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第六列数据
cell cell5=rowdata.createcell((short)5);
cell5.setcellvalue(t.getf());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第七列数据
cell cell6=rowdata.createcell((short)6);
cell6.setcellvalue(t.getg());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第八列数据
cell cell7=rowdata.createcell((short)7);
cell7.setcellvalue(t.geth());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第九列数据
cell cell8=rowdata.createcell((short)8);
cell8.setcellvalue(t.geti());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
//第十列数据
cell cell9=rowdata.createcell((short)9);
cell9.setcellvalue(t.getj());
//设置单元格的宽度
sheet.setcolumnwidth((short)0, (short)10000);
j++;
}
try {
//导出数据库文件保存路径
fos=new fileoutputstream("d:/export.xlsx");
/*if(fos.tostring().endswith("xlsx")){
workbook=new xssfworkbook();
}else if(fos.tostring().endswith("xls")){
workbook=new hssfworkbook();
}*/
//将工作簿写入文件
workbook.write(fos);
system.out.println("导出文件成功");
} catch (ioexception e) {
// todo auto-generated catch block
e.printstacktrace();
system.out.println("导出文件失败");
}
}
public static void main(string[] args) throws exception {
//连接数据库
connection conn=basedao.getconnection();
preparedstatement ps=null;
string sql="select * from test";
//执行sql语句
ps=conn.preparestatement(sql);
//查询数据库之后得到的结果
resultset rs=ps.executequery();
list<test> list=new arraylist<test>();
//遍历查询结果
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);
}
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚。
更多java生成excel报表文件。