java生成excel报表文件示例

此次简单的操作将数据从数据库导出生成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);
  }
  
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。