java generate excel report file

This simple operation will export data from the database to generate excel reports and import 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.读取配置文件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();                               
      }                                          
    }                                           
  } 

}

Generate the 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 the 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("文件不是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;    
  }  
}

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 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++;
      }
    }
    
    
  }

}

Query the data in the database and generate reports in the form of excel tables

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);
  }
  
}

The above is the entire content of this article, I hope it will be helpful to everyone's learning, and I also hope that everyone will support the PHP Chinese website.

For more articles related to java generating excel report files, please pay attention to the PHP Chinese website!