java生成excel報表文件示例

發布時間：2020-09-28 05:47:59 來源：腳本之家閱讀：164 作者：一念花開滿天下欄目：編程語言

此次簡單的操作將數據從數據庫導出生成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);
  }
  
}

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持億速云。

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

java生成excel報表文件示例

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

java生成excel報表文件示例

猜你喜歡

最新資訊

相關推薦

相關標簽