JAVA实现数据库数据导入/导出到Excel（POI技术）

准备工作：

1.导入POI包：POI下载地址：http://download.csdn.net/detail/zxm1306192988/9522142（重要）

如下

2.导入导出到Excel工具类ExcelUtil.java，封装了POI对Excel的操作

package net.dqsy.util;


import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 描述：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();
        }
    }



 
}

3.导出Excel

JButton toExcelButton = new JButton("导出excel");
		toExcelButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent arg0) {
				// TODO Auto-generated method stub
				 File selectedFile = getSelectedFile(".xls");
		            if (selectedFile != null) {
		                String path = selectedFile.getPath();
		                // System.out.println(path);

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

		                String[] n = { "题干", "A", "B", "C", "D", "Answer" };

		                Object[][] value = new Object[questionInfos.size() + 1][6];
		                
		                for (int m = 0; m < n.length; m++) {
		                    value[0][m] = n[m];
		                }
		                for (int i = 0; i < questionInfos.size(); i++) {
		                    value[i + 1][0] = questionInfos.get(i).getQuestion();
		                    value[i + 1][1] = questionInfos.get(i).getA();
		                    value[i + 1][2] = questionInfos.get(i).getB();
		                    value[i + 1][3] = questionInfos.get(i).getC();
		                    value[i + 1][4] = questionInfos.get(i).getD();
		                    value[i + 1][5] = questionInfos.get(i).getAnswer();
		                }
		                ExcelUtil.writeArrayToExcel(wb, sheet, questionInfos.size() + 1, 6, value);

		                ExcelUtil.writeWorkbook(wb, path);
		             }
			}
		});

4.导入Excel

JButton fromExcelButton = new JButton("导入excel");
		fromExcelButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent arg0) {
				// TODO Auto-generated method stub
				File selectedFile = getSelectedOpenFile(".xls");
	            if (selectedFile != null) {
	                String path = selectedFile.getPath();
	                String result = "success";
	                try {
	                    // 创建对Excel工作簿文件的引用
	                    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
	                            path));
	                    // 创建对工作表的引用。
	                    // HSSFSheet sheet = workbook.getSheet("Sheet1");
	                    HSSFSheet sheet = workbook.getSheetAt(0);

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

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

	                            if (i == 0) {
	                            	onequestioninfo.setQuestion(cell.getStringCellValue());
	                            } else if (i == 1){
	                            	onequestioninfo.setA(cell.getStringCellValue());
	                            }
	                            else if (i == 2){
	                            	onequestioninfo.setB(cell.getStringCellValue());
	                            }
	                            else if (i == 3){
	                            	onequestioninfo.setC(cell.getStringCellValue());
	                            }
	                            else if (i == 4){
	                            	onequestioninfo.setD(cell.getStringCellValue());
	                            }
	                            else if (i == 5){
	                            	onequestioninfo.setAnswer(cell.getStringCellValue());
	                            }
	                        }

	                        j++;

	                        jdbc.addQuestionInfo(onequestioninfo);
	                    }

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