java生成excel报表文件示例
此次简单的操作将数据从数据库导出生成excel报表以及将excel数据导入数据库
首先建立数据库的连接池：
packagejdbc;
importjava.io.FileInputStream;
importjava.sql.Connection;
importjava.util.Properties;
importorg.apache.commons.dbcp.BasicDataSource;
publicclassBaseDAO{
privatestaticBasicDataSourceds;
static{
try{
//1.读取配置文件conf.properties,采用java.util.Properties来读取
Propertiesp=newProperties();
//2.通过文件流读取并解析配置文件内容,本地数据库用的mysql,所以把配置文件mysql的配置放开,其他数据库配置注释
p.load(newFileInputStream("src/jdbc.properties"));
StringdriverName=p.getProperty("jdbc.driverClassName");//获取驱动名称
Stringurl=p.getProperty("jdbc.url");//获取数据库的url
Stringuser=p.getProperty("jdbc.username");//用户名
Stringpassword=p.getProperty("jdbc.password");//密码
intmaxActive=Integer.parseInt(p.getProperty("jdbc.maxActive"));//获取最大连接数
intmaxWait=Integer.parseInt(p.getProperty("jdbc.maxWait"));//获取最大等待时间
//3.创建一个连接池
ds=newBasicDataSource();
ds.setDriverClassName(driverName);//设置驱动名称
ds.setUrl(url);//设置数据库地址
ds.setUsername(user);//设置用户名
ds.setPassword(password);//设置密码
ds.setMaxActive(maxActive);//设置最大连接数
ds.setMaxWait(maxWait);//设置最大等待时间
}catch(Exceptione){
e.printStackTrace();
}
}
publicstaticConnectiongetConnection()throwsException{
try{
returnds.getConnection();
}catch(Exceptione){
System.out.println("连接数据库异常");
throwe;
}
}
publicstaticvoidclose(Connectionconn){
if(conn!=null){
try{
conn.close();
}catch(Exceptione){
e.printStackTrace();
}
}
}
}
生成与数据库相对应的java实体类：
packageentity;
publicclassTest{
privateStringa;
privateStringb;
privateStringc;
privateStringd;
privateStringe;
privateStringf;
privateStringg;
privateStringh;
privateStringi;
privateStringj;
publicStringgetA(){
returna;
}
publicvoidsetA(Stringa){
this.a=a;
}
publicStringgetB(){
returnb;
}
publicvoidsetB(Stringb){
this.b=b;
}
publicStringgetC(){
returnc;
}
publicvoidsetC(Stringc){
this.c=c;
}
publicStringgetD(){
returnd;
}
publicvoidsetD(Stringd){
this.d=d;
}
publicStringgetE(){
returne;
}
publicvoidsetE(Stringe){
this.e=e;
}
publicStringgetF(){
returnf;
}
publicvoidsetF(Stringf){
this.f=f;
}
publicStringgetG(){
returng;
}
publicvoidsetG(Stringg){
this.g=g;
}
publicStringgetH(){
returnh;
}
publicvoidsetH(Stringh){
this.h=h;
}
publicStringgetI(){
returni;
}
publicvoidsetI(Stringi){
this.i=i;
}
publicStringgetJ(){
returnj;
}
publicvoidsetJ(Stringj){
this.j=j;
}
}
将excel表格数据插入数据库，先读取excel表格数据
packagereadExcel;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.text.DecimalFormat;
importjava.text.SimpleDateFormat;
importjava.util.ArrayList;
importjava.util.Date;
importjava.util.List;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
publicclassReadExcel{
/**
*@paramargs
*@throwsIOException
*/
publicList<List<String>>readExcel(Filefile)throwsIOException{
List<List<String>>list=newArrayList<List<String>>();
if(!file.exists()){
System.out.println("文件不存在");
}else{
InputStreamfis=newFileInputStream(file);
list=parseExcel(file,fis);
}
returnlist;
}
publicList<List<String>>parseExcel(Filefile,InputStreamfis)throwsIOException{
Workbookworkbook=null;
List<List<String>>list=newArrayList<List<String>>();
if(file.toString().endsWith("xls")){
workbook=newHSSFWorkbook(fis);
}elseif(file.toString().endsWith("xlsx")){
workbook=newXSSFWorkbook(fis);
}else{
System.out.println("文件不是excel文档类型，此处无法读取");
}
for(inti=0;i<workbook.getNumberOfSheets();i++){
Sheetsheet=workbook.getSheetAt(i);
if(sheet!=null){
intlastRow=sheet.getLastRowNum();
//获取表格中的每一行
for(intj=0;j<=lastRow;j++){
Rowrow=sheet.getRow(j);
shortfirstCellNum=row.getFirstCellNum();
shortlastCellNum=row.getLastCellNum();
List<String>rowsList=newArrayList<String>();
if(firstCellNum!=lastCellNum){
//获取每一行中的每一列
for(intk=firstCellNum;k<lastCellNum;k++){
Cellcell=row.getCell(k);
if(cell==null){
rowsList.add("");
}else{
rowsList.add(chanegType(cell));
}
}
}else{
System.out.println("该表格只有一列");
}
list.add(rowsList);
}
}
}
returnlist;
}
publicStringchanegType(Cellcell){
Stringresult=newString();
switch(cell.getCellType()){//获取单元格的类型
caseHSSFCell.CELL_TYPE_NUMERIC://数字类型
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){//如果是数值类型
shortformat=cell.getCellStyle().getDataFormat();//获取这个单元的类型对应的数值
SimpleDateFormatsdf=null;
if(format==14||format==31||format==57||format==58){//如果数值为14,31,57,58其中的一种
//对应的日期格式为2016-03-01这种形式,
sdf=newSimpleDateFormat("yyyy-MM-dd");
doublevalue=cell.getNumericCellValue();
Datedate=org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result=sdf.format(date);//得到yyyy-MM-dd这种格式日期
}elseif(format==20||format==32){
//时间
sdf=newSimpleDateFormat("HH:mm");
doublevalue=cell.getNumericCellValue();
Datedate=org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result=sdf.format(date);//得到HH:mm
}else{
doublevalue=cell.getNumericCellValue();
CellStylestyle=cell.getCellStyle();
DecimalFormatdataformat=newDecimalFormat();
Stringtemp=style.getDataFormatString();
//单元格设置成常规
if(temp.equals("General")){
dataformat.applyPattern("#");
}
result=dataformat.format(value);//得到单元格数值
}
}
break;
caseHSSFCell.CELL_TYPE_STRING://String类型
result=cell.getRichStringCellValue().toString();
break;
caseHSSFCell.CELL_TYPE_BLANK:
result="";
default:
result="";
break;
}
returnresult;
}
}
将读取到的excel表格数据插入到数据库中去
packageimportdata;
importjava.io.File;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.util.ArrayList;
importjava.util.List;
importentity.Test;
importreadExcel.ReadExcel;
importjdbc.BaseDAO;
publicclassinportData{
publicstaticvoidmain(String[]args)throwsException{
//TODOAuto-generatedmethodstub
List<List<String>>list=newArrayList<List<String>>();
ReadExcelreadExcel=newReadExcel();
Filefile=newFile("d:/test.xlsx");
list=readExcel.readExcel(file);
Testtest=newTest();
Connectionconn=BaseDAO.getConnection();
PreparedStatementps=null;
inti=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());
Stringsql="insertintoTEST(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());
intn=ps.executeUpdate();
if(n!=1){
System.out.println("数据插入数据库失败");
}
System.out.println("第"+i+"条数据插入成功");
System.out.println();
i++;
}
}
}
}
将数据库中的数据查询出来并以excel表格的形式生成报表
packageexport;
importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.util.ArrayList;
importjava.util.List;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
importentity.Test;
importjdbc.BaseDAO;
publicclassExport{
publicstaticvoidcreateExcel(List<Test>list){
FileOutputStreamfos=null;
Workbookworkbook=newXSSFWorkbook();
Sheetsheet=workbook.createSheet("测试文件");
String[]title={"第一列","第二列","第三列","第四列","第五列","第六列","第七列","第八列","第九列","第十列"};
Rowrow=sheet.createRow((short)0);
inti=0;
for(Strings:title){
Cellcell=row.createCell(i);
cell.setCellValue(s);
i++;
}
intj=1;
for(Testt:list){
//创建第二行
RowrowData=sheet.createRow((short)j);
//第一列数据
Cellcell0=rowData.createCell((short)0);
cell0.setCellValue(t.getA());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第二列数据
Cellcell1=rowData.createCell((short)1);
cell1.setCellValue(t.getB());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第三列数据
Cellcell2=rowData.createCell((short)2);
cell2.setCellValue(t.getC());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第四列数据
Cellcell3=rowData.createCell((short)3);
cell3.setCellValue(t.getD());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第五列数据
Cellcell4=rowData.createCell((short)4);
cell4.setCellValue(t.getE());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第六列数据
Cellcell5=rowData.createCell((short)5);
cell5.setCellValue(t.getF());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第七列数据
Cellcell6=rowData.createCell((short)6);
cell6.setCellValue(t.getG());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第八列数据
Cellcell7=rowData.createCell((short)7);
cell7.setCellValue(t.getH());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第九列数据
Cellcell8=rowData.createCell((short)8);
cell8.setCellValue(t.getI());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
//第十列数据
Cellcell9=rowData.createCell((short)9);
cell9.setCellValue(t.getJ());
//设置单元格的宽度
sheet.setColumnWidth((short)0,(short)10000);
j++;
}
try{
//导出数据库文件保存路径
fos=newFileOutputStream("D:/export.xlsx");
/*if(fos.toString().endsWith("xlsx")){
workbook=newXSSFWorkbook();
}elseif(fos.toString().endsWith("xls")){
workbook=newHSSFWorkbook();
}*/
//将工作簿写入文件
workbook.write(fos);
System.out.println("导出文件成功");
}catch(IOExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("导出文件失败");
}
}
publicstaticvoidmain(String[]args)throwsException{
//连接数据库
Connectionconn=BaseDAO.getConnection();
PreparedStatementps=null;
Stringsql="select*fromTEST";
//执行sql语句
ps=conn.prepareStatement(sql);
//查询数据库之后得到的结果
ResultSetrs=ps.executeQuery();
List<Test>list=newArrayList<Test>();
//遍历查询结果
while(rs.next()){
Testtest=newTest();
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);
}
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。