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