600字范文 > java导出excel报表_java生成excel报表文件示例

java导出excel报表_java生成excel报表文件示例

时间：2022-11-09 21:44:58

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

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

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");//获取数据库的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> 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

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

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 = 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表格的形式生成报表

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

//设置单元格的宽度