JAVA实现数据库数据导入/导出到Excel(POI)

时间:2024-04-01 19:39:19
原文地址为:JAVA实现数据库数据导入/导出到Excel(POI)

准备工作:

1.导入POI包:POI下载地址http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/src/(重要)

如下

JAVA实现数据库数据导入/导出到Excel(POI)

2.导入Java界面美化包BeautyEye下载地址http://code.google.com/p/beautyeye/downloads/detail?name=beautyeye_lnf_v3.5_all_in_one.zip&can=2&q=(可选)如果不想加入界面美化代码可以把void setlookandfeel()这个方法及其调用去掉

 

jar在下载解压在文件夹dist目录下

JAVA实现数据库数据导入/导出到Excel(POI)

beautyeye_lnf.jar包是个开源的美化Java界面的包,推荐学习做出的Java界面比较美观

BeautyEye L&F简明开发者指南. http://code.google.com/p/beautyeye/wiki/Introduction

 

3.本案例使用Hiberate下配置Oracle导入导出数据

导出的数据库表为users表

SQL> desc system.users; 
Name               Type                           Nullable              Default Comments 
-----               ------------------                   --------                    ------- -------- 
ID                  NUMBER(10)                                   
NAME             VARCHAR2(50 CHAR)                            
PWORD           VARCHAR2(32 CHAR)                            
EMAIL            VARCHAR2(100 CHAR)        Y

注意Column顺序不能乱

1.ExcelOutandIn.java

ExcelOutandIn主要利用Jtable显示数据库里面的数据

显示效果如图所示

JAVA实现数据库数据导入/导出到Excel(POI)

JAVA实现数据库数据导入/导出到Excel(POI)
package com.wym.tools;

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Comparator;
import java.util.List;
import java.util.Vector;
import javax.swing.DefaultRowSorter;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.RowSorter;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.filechooser.FileFilter;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.NonUniqueObjectException;
import org.jb2011.lnf.beautyeye.BeautyEyeLNFHelper;
import cn.wym.hibernate.Users;
import cn.wym.hibernate.UsersDAO;

public class ExcelOutandIn extends JFrame implements ActionListener {

JButton button1
= new JButton("ToExcel");
JButton button2
= new JButton("FromExcel");

Container ct
= null;
DefaultTableModel defaultModel
= null;

JButton add
= new JButton("添加");
JButton delete
= new JButton("删除");
JButton save
= new JButton("保存");
JButton reset
= new JButton("刷新");

JPanel jp1
= new JPanel(), jp;
JPanel jp2
= new JPanel();
JScrollPane jsp
= null;
UsersDAO userdao
= new UsersDAO();
Users users
= null;
@SuppressWarnings(
"unchecked")
List list
= null;

public List getList() {
return list;
}

public void setList(List list) {
this.list = list;
}

protected JTable table = null;
protected String oldvalue = "";
protected String newvalue = "";

/**
*
@param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

ExcelOutandIn tm
= new ExcelOutandIn();
// tm.paintuser();
}

public void paintuser() {
list
= this.getUsers();
for (int i = 0; i < list.size(); i++) {
users
= (Users) list.get(i);
System.out.println(
" ID:" + users.getId() + "");
}
}

@SuppressWarnings(
"unchecked")
public List getUsers() {
list
= userdao.findAll();

return list;

}

private File getSelectedOpenFile(final String type) {
String name
= getName();

JFileChooser pathChooser
= new JFileChooser();
pathChooser.setFileFilter(
new FileFilter() {

@Override
public boolean accept(File f) {
if (f.isDirectory()) {
return true;
}
else {
if (f.getName().toLowerCase().endsWith(type)) {
return true;
}
else {
return false;
}
}
}

@Override
public String getDescription() {
return "文件格式(" + type + ")";
}
});
pathChooser.setSelectedFile(
new File(name + type));
int showSaveDialog = pathChooser.showOpenDialog(this);
if (showSaveDialog == JFileChooser.APPROVE_OPTION) {
return pathChooser.getSelectedFile();
}
else {
return null;
}
}

private File getSelectedFile(final String type) {
String name
= getName();

JFileChooser pathChooser
= new JFileChooser();
pathChooser.setFileFilter(
new FileFilter() {

@Override
public boolean accept(File f) {
if (f.isDirectory()) {
return true;
}
else {
if (f.getName().toLowerCase().endsWith(type)) {
return true;
}
else {
return false;
}
}
}

@Override
public String getDescription() {
return "文件格式(" + type + ")";
}
});
pathChooser.setSelectedFile(
new File(name + type));
int showSaveDialog = pathChooser.showSaveDialog(this);
if (showSaveDialog == JFileChooser.APPROVE_OPTION) {
return pathChooser.getSelectedFile();
}
else {
return null;
}
}

void setlookandfeel() {
try {

BeautyEyeLNFHelper.frameBorderStyle
= BeautyEyeLNFHelper.FrameBorderStyle.osLookAndFeelDecorated;
org.jb2011.lnf.beautyeye.BeautyEyeLNFHelper.launchBeautyEyeLNF();
}
catch (final Exception e) {
System.out.println(
"error");

}
}

void init() {
setlookandfeel();

buildTable();
jsp
= new JScrollPane(table);

ct.add(jsp);
button1.setActionCommand(
"ToExcel");
button1.addActionListener(
this);

button2.setActionCommand(
"FromExcel");
button2.addActionListener(
this);

delete.setActionCommand(
"delete");
delete.addActionListener(
this);

reset.setActionCommand(
"reset");
reset.addActionListener(
this);

save.setActionCommand(
"save");
save.addActionListener(
this);

add.setActionCommand(
"add");
add.addActionListener(
this);
}

public void ToExcel(String path) {

list
= getUsers();

HSSFWorkbook wb
= new HSSFWorkbook();
HSSFSheet sheet
= wb.createSheet("Users");

String[] n
= { "编号", "姓名", "密码", "邮箱" };

Object[][] value
= new Object[list.size() + 1][4];
for (int m = 0; m < n.length; m++) {
value[
0][m] = n[m];
}
for (int i = 0; i < list.size(); i++) {
users
= (Users) list.get(i);

value[i
+ 1][0] = users.getId();
value[i
+ 1][1] = users.getUsername();
value[i
+ 1][2] = users.getPassword();
value[i
+ 1][3] = users.getUEmail();

}
ExcelUtil.writeArrayToExcel(wb, sheet, list.size()
+ 1, 4, value);

ExcelUtil.writeWorkbook(wb, path);

}

/**
* 从Excel导入数据到数据库
*
@param filename
*/
public void FromExcel(String filename) {

String result
= "success";
/** Excel文件的存放位置。注意是正斜线 */
// String fileToBeRead = "F:\\" + fileFileName;
try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
filename));
// 创建对工作表的引用。
// HSSFSheet sheet = workbook.getSheet("Sheet1");
HSSFSheet sheet = workbook.getSheetAt(0);

int j = 1;//从第2行开始堵数据
// 第在excel中读取一条数据就将其插入到数据库中
while (j < sheet.getPhysicalNumberOfRows()) {
HSSFRow row
= sheet.getRow(j);
Users user
= new Users();

for (int i = 0; i <= 3; i++) {
HSSFCell cell
= row.getCell((short) i);

if (i == 0) {
user.setId((
int) cell.getNumericCellValue());
}
else if (i == 1)
user.setUsername(cell.getStringCellValue());
else if (i == 2)
user.setPassword(cell.getStringCellValue());

else if (i == 3)
user.setUEmail(cell.getStringCellValue());
}

System.out.println(user.getId()
+ " " + user.getUsername()
+ " " + user.getPassword() + " " + user.getUEmail());

j
++;

userdao.save(user);

}

}
catch (FileNotFoundException e2) {
// TODO Auto-generated catch block
System.out.println("notfound");
e2.printStackTrace();
}
catch (IOException e3) {
// TODO Auto-generated catch block
System.out.println(e3.toString());

e3.printStackTrace();
}
catch (NonUniqueObjectException e4) {
System.out.println(e4.toString());

}

}

public JTable CreateTable(String[] columns, Object rows[][]) {
JTable table;
TableModel model
= new DefaultTableModel(rows, columns);

table
= new JTable(model);
RowSorter sorter
= new TableRowSorter(model);
table.setRowSorter(sorter);

return table;

}

@SuppressWarnings(
"unchecked")
public void fillTable(List<Users> users) {
DefaultTableModel tableModel
= (DefaultTableModel) table.getModel();
tableModel.setRowCount(
0);// 清除原有行

// 填充数据
for (Users Users : users) {
Vector vector
= new Vector<Users>();

vector.add(Users.getId());
vector.add(Users.getUsername());
vector.add(Users.getPassword());
vector.add(Users.getUEmail());

// 添加数据到表格
tableModel.addRow(vector);
}

// 更新表格
table.invalidate();
}

@SuppressWarnings(
"unchecked")
public void tableAddRow(int id, String name, String pwd, String email) {
DefaultTableModel tableModel
= (DefaultTableModel) table.getModel();
tableModel.getColumnCount();

// 填充数据

Vector vector
= new Vector<Users>();

vector.add(id);
vector.add(name);
vector.add(pwd);
vector.add(email);

// 添加数据到表格
tableModel.addRow(vector);

// 更新表格
table.invalidate();
}

@SuppressWarnings(
"unchecked")
public void buildTable() {
String[] n
= { "编号", "姓名", "密码", "邮箱" };
list
= getUsers();
Object[][] value
= new Object[list.size()][4];

for (int i = 0; i < list.size(); i++) {
users
= (Users) list.get(i);

value[i][
0] = users.getId();
value[i][
1] = users.getUsername();
value[i][
2] = users.getPassword();
value[i][
3] = users.getUEmail();

}
defaultModel
= new DefaultTableModel(value, n) {
boolean[] editables = { false, true, true, true };

public boolean isCellEditable(int row, int col) {
return editables[col];
}
};
defaultModel.isCellEditable(
1, 1);
table
= new JTable(defaultModel);
RowSorter sorter
= new TableRowSorter(defaultModel);
table.setRowSorter(sorter);

// 设置排序
((DefaultRowSorter) sorter).setComparator(0, new Comparator<Object>() {
public int compare(Object arg0, Object arg1) {
try {
int a = Integer.parseInt(arg0.toString());
int b = Integer.parseInt(arg1.toString());
return a - b;
}
catch (NumberFormatException e) {
return 0;
}
}
});
defaultModel.addTableModelListener(
new TableModelListener() {

public void tableChanged(TableModelEvent e) {
if (e.getType() == TableModelEvent.UPDATE) {
newvalue
= table.getValueAt(e.getLastRow(), e.getColumn())
.toString();
System.out.println(newvalue);
int rowss = table.getEditingRow();
if (newvalue.equals(oldvalue)) {
System.out.println(rowss);
System.out.println(table.getValueAt(rowss,
0) + ""
+ table.getValueAt(rowss, 1) + ""
+ table.getValueAt(rowss, 2) + ""
+ table.getValueAt(rowss, 3));
JOptionPane.showMessageDialog(
null, "数据没有修改");

}
else {

int dialog = JOptionPane.showConfirmDialog(null,
"是否确认修改", "温馨提示", JOptionPane.YES_NO_OPTION);
if (dialog == JOptionPane.YES_OPTION) {

System.out.println(
" 修改了");
String s1
= (String) table.getValueAt(rowss, 0)
.toString();
int id = Integer.parseInt(s1);
users
= new Users();
users.setId(id);
users.setUEmail(table.getValueAt(rowss,
3)
.toString());
users.setUsername(table.getValueAt(rowss,
1)
.toString());
users.setPassword(table.getValueAt(rowss,
2)
.toString());

try {
userdao.saveOrUpdate2(users);

}
catch (NonUniqueObjectException noe) {
new UsersDAO().saveOrUpdate2(users);
}

}
else if (dialog == JOptionPane.NO_OPTION) {
table.setValueAt(oldvalue, rowss, table
.getSelectedColumn());
// System.out.println("没有确认修改");
}

}

}

}

});

table.addMouseListener(
new MouseAdapter() {

public void mouseClicked(MouseEvent e) {

// 记录进入编辑状态前单元格得数据

try {
oldvalue
= table.getValueAt(table.getSelectedRow(),
table.getSelectedColumn()).toString();
System.out.println(oldvalue);
}
catch (Exception ex) {
// TODO: handle exception
}

}

});
}

public ExcelOutandIn() {

// TODO Auto-generated constructor stub

new BorderLayout();

Font font
= new Font("宋体", 4, 14);

add.setFont(font);
save.setFont(font);
delete.setFont(font);
reset.setFont(font);
jp1.add(button1);
jp1.add(button2);

jp2.add(add);
jp2.add(delete);
// jp2.add(save);
jp2.add(reset);

ct
= this.getContentPane();

ct.add(jp1, BorderLayout.NORTH);
ct.add(jp2, BorderLayout.SOUTH);

init();
this.setTitle("ToOrFromExcel");
this.setVisible(true);
this.setSize(600, 400);
this.setLocation(400, 250);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

}

public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub

if (e.getActionCommand().equals("add")) {

AddUsers adduser
= new AddUsers();
jp
= new JPanel();
jp.add(adduser);
ct.add(jp, BorderLayout.WEST);

/*
* users= adduser.getU(); if(users==null){
* JOptionPane.showMessageDialog(null, "Null");
*
* }else{
*
* }
*/
// tableAddRow(id, name, pwd, email);
}
// defaultModel.addRow(v);
if (e.getActionCommand().equals("delete")) {
int srow = 0;

try {
srow
= table.getSelectedRow();
}
catch (Exception ee) {

}
int rowcount = defaultModel.getRowCount() - 1;// getRowCount返回行数,rowcount<0代表已经没有任何行了。

if (srow > 0) {
Object id
= defaultModel.getValueAt(srow, 0);
String ID
= id.toString();
users
= userdao.findById(Integer.parseInt(ID));

defaultModel.getRowCount();

System.out.println(ID);
defaultModel.removeRow(srow);

// userdao.delete(users);
defaultModel.setRowCount(rowcount);
}
}

if (e.getActionCommand().equals("save")) {
System.out.println(
"save");
ct.remove(jp);
}

if (e.getActionCommand().equals("reset")) {

System.out.println(
"reset");
fillTable(userdao.findAll());

}

if (e.getActionCommand().equalsIgnoreCase("toexcel")) {

File selectedFile
= getSelectedFile(".xls");
if (selectedFile != null) {
String path
= selectedFile.getPath();

// System.out.println(path);
ToExcel(path);
}

}
else if (e.getActionCommand().equalsIgnoreCase("FromExcel")) {
File selectedFile
= getSelectedOpenFile(".xls");
if (selectedFile != null) {
// String name=selectedFile.getName();
String path = selectedFile.getPath();
FromExcel(path);
fillTable(userdao.findAll());

}

}

}

}
JAVA实现数据库数据导入/导出到Excel(POI)

 

 

 

2.导入导出到Excel工具类ExcelUtil.java

JAVA实现数据库数据导入/导出到Excel(POI)
package com.wym.tools;

import java.io.File;

/**
* 描述:Excel写操作帮助类
*
*
*
*/
public class ExcelUtil {



/**
* 功能:创建HSSFSheet工作簿
*
@param wb HSSFWorkbook
*
@param sheetName String
*
@return HSSFSheet
*/
public static HSSFSheet createSheet(HSSFWorkbook wb,String sheetName){
HSSFSheet sheet
=wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(
12);
sheet.setGridsPrinted(
false);
sheet.setDisplayGridlines(
false);
return sheet;
}




/**
* 功能:创建HSSFRow
*
@param sheet HSSFSheet
*
@param rowNum int
*
@param height int
*
@return HSSFRow
*/
public static HSSFRow createRow(HSSFSheet sheet,int rowNum,int height){
HSSFRow row
=sheet.createRow(rowNum);
row.setHeight((
short)height);
return row;
}



public static HSSFCell createCell0(HSSFRow row,int cellNum){
HSSFCell cell
=row.createCell(cellNum);
return cell;
}


/**
* 功能:创建CELL
*
@param row HSSFRow
*
@param cellNum int
*
@param style HSSFStyle
*
@return HSSFCell
*/
public static HSSFCell createCell(HSSFRow row,int cellNum,CellStyle style){
HSSFCell cell
=row.createCell(cellNum);
cell.setCellStyle(style);
return cell;
}



/**
* 功能:创建CellStyle样式
*
@param wb HSSFWorkbook
*
@param backgroundColor 背景色
*
@param foregroundColor 前置色
*
@param font 字体
*
@return CellStyle
*/
public static CellStyle createCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
CellStyle cs
=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
return cs;
}


/**
* 功能:创建带边框的CellStyle样式
*
@param wb HSSFWorkbook
*
@param backgroundColor 背景色
*
@param foregroundColor 前置色
*
@param font 字体
*
@return CellStyle
*/
public static CellStyle createBorderCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
CellStyle cs
=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
cs.setBorderLeft(CellStyle.BORDER_DASHED);
cs.setBorderRight(CellStyle.BORDER_DASHED);
cs.setBorderTop(CellStyle.BORDER_DASHED);
cs.setBorderBottom(CellStyle.BORDER_DASHED);
return cs;
}





/**
* 功能:多行多列导入到Excel并且设置标题栏格式
*/
public static void writeArrayToExcel(HSSFSheet sheet,int rows,int cells,Object [][]value){

Row row[]
=new HSSFRow[rows];
Cell cell[]
=new HSSFCell[cells];

for(int i=0;i<row.length;i++){
row[i]
=sheet.createRow(i);


for(int j=0;j<cell.length;j++){
cell[j]
=row[i].createCell(j);
cell[j].setCellValue(convertString(value[i][j]));

}

}
}



/**
* 功能:多行多列导入到Excel并且设置标题栏格式
*/
public static void writeArrayToExcel(HSSFWorkbook wb,HSSFSheet sheet,int rows,int cells,Object [][]value){

Row row[]
=new HSSFRow[rows];
Cell cell[]
=new HSSFCell[cells];


HSSFCellStyle ztStyle
= (HSSFCellStyle)wb.createCellStyle();

Font ztFont
= wb.createFont();
ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
//ztFont.setItalic(true); // 设置字体为斜体字
// ztFont.setColor(Font.COLOR_RED); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px
ztFont.setFontName("华文行楷"); // 将“华文行楷”字体应用到当前单元格上
// ztFont.setUnderline(Font.U_DOUBLE);
ztStyle.setFont(ztFont);

for(int i=0;i<row.length;i++){
row[i]
=sheet.createRow(i);


for(int j=0;j<cell.length;j++){
cell[j]
=row[i].createCell(j);
cell[j].setCellValue(convertString(value[i][j]));

if(i==0)
cell[j].setCellStyle(ztStyle);

}

}
}



/**
* 功能:合并单元格
*
@param sheet HSSFSheet
*
@param firstRow int
*
@param lastRow int
*
@param firstColumn int
*
@param lastColumn int
*
@return int 合并区域号码
*/
public static int mergeCell(HSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));
}



/**
* 功能:创建字体
*
@param wb HSSFWorkbook
*
@param boldweight short
*
@param color short
*
@return Font
*/
public static Font createFont(HSSFWorkbook wb,short boldweight,short color,short size){
Font font
=wb.createFont();
font.setBoldweight(boldweight);
font.setColor(color);
font.setFontHeightInPoints(size);
return font;
}


/**
* 设置合并单元格的边框样式
*
@param sheet HSSFSheet
*
@param ca CellRangAddress
*
@param style CellStyle
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,CellStyle style) {
for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {
HSSFRow row
= HSSFCellUtil.getRow(i, sheet);
for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {
HSSFCell cell
= HSSFCellUtil.getCell(row, j);
cell.setCellStyle(style);
}
}
}

/**
* 功能:将HSSFWorkbook写入Excel文件
*
@param wb HSSFWorkbook
*
@param absPath 写入文件的相对路径
*
@param wbName 文件名
*/
public static void writeWorkbook(HSSFWorkbook wb,String fileName){
FileOutputStream fos
=null;
File f
=new File(fileName);
try {
fos
=new FileOutputStream(f);
wb.write(fos);
int dialog = JOptionPane.showConfirmDialog(null,
f.getName()
+"导出成功!是否打开?",
"温馨提示", JOptionPane.YES_NO_OPTION);
if (dialog == JOptionPane.YES_OPTION) {

Runtime.getRuntime().exec(
"cmd /c start \"\" \"" + fileName + "\"");
}


}
catch (FileNotFoundException e) {
JOptionPane.showMessageDialog(
null, "导入数据前请关闭工作表");

}
catch ( Exception e) {
JOptionPane.showMessageDialog(
null, "没有进行筛选");

}
finally{
try {
if(fos!=null){
fos.close();
}
}
catch (IOException e) {
}
}
}



public static String convertString(Object value) {
if (value == null) {
return "";
}
else {
return value.toString();
}
}




}
JAVA实现数据库数据导入/导出到Excel(POI)

 

导入的Excel格式

JAVA实现数据库数据导入/导出到Excel(POI)

原文:http://www.cnblogs.com/J-wym/p/3260722.html

转载请注明本文地址:JAVA实现数据库数据导入/导出到Excel(POI)