服务器之家

服务器之家 > 正文

java生成excel报表文件示例

时间：2020-08-12 11:17     来源/作者：一念花开满天下

此次简单的操作将数据从数据库导出生成excel报表以及将excel数据导入数据库

首先建立数据库的连接池：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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实体类：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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表格数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
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表格数据插入到数据库中去

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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表格的形式生成报表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
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[] id="codetool">

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

原文链接：http://www.cnblogs.com/feitianshaoxai/p/6400350.html

标签：
Java Excel 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
男生常说24816是什么意思？女生说13579是什么意思？
男生常说24816是什么意思？女生说13579是什么意思？ 2019-09-17
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
返回顶部