Java|java教程
java excel
Java-java教程
此次简单的操作将数据从数据库导出生成excel报表以及将excel数据导入数据库
手机导航页源码html5,vscode浏览aspx,ubuntu怎么改ip,tomcat 支持 协议,sqlite中alter,ftp服务器需要机房嘛,jquery注册登录的插件,手机前端框架大全,爬虫滴管,某课php,站内seo优化是什么,化妆品网站静态模板,css美化网页,php登陆界面模板,博客全套页面设计,供销存管理系统 开源,swift小程序源代码lzw
首先建立数据库的连接池：
按键精灵源码库,vscode f5没反应,ubuntu自建分区,tomcat开启特别慢,python多开爬虫,php 是开源的吗,搜索引擎营销seo论文lzw
package jdbc;import java.io.FileInputStream;import java.sql.Connection;import java.util.Properties;import mons.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");//获取数据库的urlString 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实体类：
刷步数 网站源码,vscode+自动提示函数,汉化ubuntu系统,tomcat 多线程模式,爬虫电影简介,php 获取项目根目录,seo精准营销关键词,小说网站开发,psd手机模板lzw
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> readExcel(File file) throws IOException{ List<List> list=new ArrayList<List>(); if(!file.exists()){System.out.println("文件不存在"); }else{InputStream fis=new FileInputStream(file); list=parseExcel(file,fis); } return list;} public List<List> parseExcel(File file,InputStream fis) throws IOException{ Workbook workbook=null; List<List> list=new ArrayList<List>(); 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 rowsList=new ArrayList();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其中的一种 //对应的日期格式为 -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> list = new ArrayList<List>(); 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表格的形式生成报表
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 blocke.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); } }
更多java生成excel报表文件相关文章请关注PHP中文网！
