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

来源：互联网发布：好看的推理小说知乎编辑：程序博客网时间：2024/05/20 02:28

准备工作：

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() {@Overridepublic 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() {@Overridepublic void actionPerformed(ActionEvent arg0) {// TODO Auto-generated method stubFile 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();                }             }}});

0 0