JAVA实现数据库数据导入/导出到Excel（POI）
准备工作：
1.导入POI包：POI下载地址http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/src/（重要）
如下
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目录下
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显示数据库里面的数据
显示效果如图所示
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()); } } } }
2.导入导出到Excel工具类ExcelUtil.java
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(); } } }
导入的Excel格式
原文：http://www.cnblogs.com/J-wym/p/3260722.html
