java生成excel报表文件示例

发布于 2020-8-1|标签java
复制链接
本篇文章主要介绍了java生成excel报表文件示例,小妖觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小妖过来看看吧
此次简单的操作将数据从数据库导出生成excel报表以及将excel数据导入数据库首先建立数据库的连接池: ```java 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实体类: ```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表格数据 ```java 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> readExcel(File file) throws IOException{ List> list=new ArrayList>(); if(!file.exists()){ System.out.println("文件不存在"); }else{ InputStream fis=new FileInputStream(file); list=parseExcel(file,fis); } return list; } public List> parseExcel(File file,InputStream fis) throws IOException{ Workbook workbook=null; List> list=new ArrayList>(); 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 rowsList=new ArrayList(); if(firstCellNum!=lastCellNum){ //获取每一行中的每一列 for(int k=firstCellNum;k将读取到的excel表格数据插入到数据库中去 ```java 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 = new ArrayList>(); 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 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表格的形式生成报表 ```java 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 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 list=new ArrayList(); //遍历查询结果 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); } } ``` 原文地址: http://www.cnblogs.com/feitianshaoxai/p/6400350.html
冀ICP备17029012号-4 | 版权所有©鲍亚龙 |免责声明  | GIF图库  | NUXT版