Excel导入导出数据到SQL数据库的实例。

Question

大侠们，我这两天在学Excel的导入导出，知道了有一些SQL语句可以实现导入导出，但是让我写个程序，我不知道JSP页面或者说是整个工程不知道该怎么写，希望谁能给我说个思路，或者写个例子，帮帮忙，谢谢！！

Answer 1

不过可能是说用命令行方式把一个数据库表转换为excel表。楼主想要查询结果集转换为excel表，我知道有两种方式：一种是转换为csv格式（这是Excel的一种格式）。这种格式是纯文本，文本的一行对应于Excel表的一行，不同的列之间用“,”分隔。
另外一种方式就是利用java操作excel的API，比如Jakarta POI，从而可以对excel文件进行读写。可参见：http://jakarta.apache.org/poi/

Answer 2

另外一种方式就是利用java操作excel的API，比如Jakarta POI，从而可以对excel文件进行读写。可参见：http://jakarta.apache.org/poi/我说的就是这个方法，但是我刚刚接触，都不知道从何入手，请详细指点一下吧！
拜托~

Answer 3

我用的是jxl，感觉不错，你可以去看看实例
http://www.1to2.us/Java-Excel-a159665.htm

Answer 4

//建立输出表实例
_workbook_out = new HSSFWorkbook();
//以后关闭要用实例fileOut
fileOut = new FileOutputStream(outfilename);
//建立表格实例
HSSFSheet sheet_out = _workbook_out.createSheet();
rows 结果集中的记录数
for (int r = 0; r < rows; r++) //row numbers
{//建立行
HSSFRow row_out = sheet_out.createRow(r);
//建立该行列循环
    for (short c = 0; c < cells; c++) {
    //建立输出单元格对象实例
    HSSFCell cell_out = row_out.createCell(c);
    //单元格汉字编码转换
    cell_out.setEncoding(HSSFCell.ENCODING_UTF_16);
    //设置单元格属性
    cell_out.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    //添值(这个值就是你准备要取的结果集中的某个你要放置的值)
    cell_out.setCellValue(numvalue);
    }//列循环结束} //行循环结束//最后处理输出工作表关闭写入数据
_workbook_out.write(fileOut);
fileOut.close();你看看这段代码吧 注释的很详细了

Answer 5

有excel和sql的导入导出的例子吗？

Answer 6

readChannel(String inputDate, String branchCode,
                                    Connection con,
                                    String toFileName, String templateName,
                                    String reportName, String errorMessage) {
        System.out.println("==========" + toFileName);
        if (sys_logger.isDebugEnabled()){
            sys_logger.debug(toFileName + " begin...");
            sys_logger.debug("---from " + templateName);
        }
        Object synObj;
        if (synFile.get(templateName) == null) {
            synObj = new Object();
            synFile.put(templateName, synObj);
        }        synObj = synFile.get(templateName);
        synchronized (synObj) {            POIFSFileSystem pfs = null;
            HSSFWorkbook workbook = null;
            HSSFSheet sheet = null;
            HSSFRow hr = null;
            HSSFCell cell = null;            FileOutputStream fos = null;
            PreparedStatement pstmt = null;
            PreparedStatement pstmt1 = null;
            ResultSet rs = null;            String sheetname = null;
            String col = null;
            String row = null;
            String num = null;
            String celltype = null;
            int i = 0;
            try {                File filedelete = new File(toFileName);
                if (filedelete.exists()) {
                    filedelete.delete();
                }                //xls定义
                pfs = new POIFSFileSystem(new FileInputStream(templateName));
                workbook = new HSSFWorkbook(pfs);                String sql =
                    "select sheetname,colpos,rowpos,num,celltype from tbrptresult "
                    + "where branch_code=? and reportname=? "
                    + "order by sheetname,to_number(colpos),to_number(rowpos)";                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, branchCode);
                pstmt.setString(2, reportName);
                rs = pstmt.executeQuery();                //sheet = copy.getSheet(Integer.parseInt("1"));                while (rs.next()) {                    sheetname = rs.getString(1); //in fact sheetname is  sheetid
                    col = rs.getString(2);
                    row = rs.getString(3);
                    num = StringUtils.encode(rs.getString(4));
                    celltype = rs.getString(5);                    //不带格式,只修改数值
                    sheet = workbook.getSheetAt(Integer.parseInt(sheetname));
                    hr = sheet.getRow(Integer.parseInt(row));
                    cell = hr.getCell( (short) (Integer.parseInt(col)));
                    if (num != null) {
                        i++;
                        //logger.debug(i + ":(" + row + ":" + col + ":" + num +":" + cell.getCellType() + ")");                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            cell.setCellValue( (new Double(num)).doubleValue());
                        }
                        else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK ||
                                 cell.getCellType() ==
                                 HSSFCell.CELL_TYPE_STRING) {
                            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            cell.setEncoding( (short) 1);
                            cell.setCellValue(num);
                        }
                    }                    cell = null;
                    hr = null;
                    sheet = null;                    //LoggerUtils.recordError(ReportConstants.REPORT_SYS_LOG, "6-991" + copy.toString() );
                }                //去掉多余行 begin
                if (!PreLoader.HQ_BRANCH_CODE.equals(branchCode)){
                    String hassub = "";
                    String day = inputDate.substring(8, 10);
                    String sql2 =
                        "select hassubbranch from t_rpt_branch where branch_code=? " +
                        " and status='1'  ";

Answer 7

pstmt1 = con.prepareStatement(sql2);
                    pstmt1.setString(1, branchCode);
                    //pstmt1.setString(2, reportName);
                    rs = pstmt1.executeQuery();
                    while (rs.next()) {
                        hassub = rs.getString(1);
                    }
                    if (hassub.equalsIgnoreCase("0")) {
                        if (reportName.equalsIgnoreCase("DAILYRPT3")) {
                            hiderow(2, 9, day, workbook);
                            hiderow(3, 11, day, workbook);
                            hiderow(4, 10, day, workbook);
                            hiderow(5, 11, day, workbook);
                            hiderow(6, 10, day, workbook);
                            hiderow(7, 10, day, workbook);
                            hiderow(8, 6, day, workbook);
                            hiderow(9, 6, day, workbook);
                            hiderow(10, 6, day, workbook);
                            hiderow(11, 6, day, workbook);
                            hiderow(12, 6, day, workbook);
                        }
                        if (reportName.equalsIgnoreCase("DAILYRPT3AG") ||
                            reportName.equalsIgnoreCase("DAILYRPT3BANC")) {
                            hiderow(2, 11, day, workbook);
                            hiderow(3, 6, day, workbook);
                        }
                        if (reportName.equalsIgnoreCase("DAILYRPT3BRK") ||
                            reportName.equalsIgnoreCase("DAILYRPT3DM") ||
                            reportName.equalsIgnoreCase("DAILYRPT3GODT")) {
                            hiderow(2, 10, day, workbook);
                            hiderow(3, 6, day, workbook);
                        }
                    }
                    if (hassub.equalsIgnoreCase("1")) {
                        if (reportName.equalsIgnoreCase("DAILYRPT3")) {
                            hiderow(2, 9, day, workbook);
                            hiderow(3, 11, day, workbook);
                            hiderow(4, 11, day, workbook);
                            hiderow(5, 11, day, workbook);
                            hiderow(6, 11, day, workbook);
                            hiderow(7, 11, day, workbook);
                            hiderow(8, 6, day, workbook);
                            hiderow(9, 6, day, workbook);
                            hiderow(10, 6, day, workbook);
                            hiderow(11, 6, day, workbook);
                            hiderow(12, 6, day, workbook);
                        }
                        if (reportName.equalsIgnoreCase("DAILYRPT3AG") ||
                            reportName.equalsIgnoreCase("DAILYRPT3BANC")) {
                            int ns = workbook.getNumberOfSheets();
                            for (int k = 2; k < ns; k++) {
                                hiderow(k, 11, day, workbook);
                            }
                            //hiderow(2, 11, day, workbook);
                            //hiderow(3, 6, day, workbook);
                        }
                        if (reportName.equalsIgnoreCase("DAILYRPT3BRK") ||
                            reportName.equalsIgnoreCase("DAILYRPT3DM") ||
                            reportName.equalsIgnoreCase("DAILYRPT3GODT")) {
                            int ns = workbook.getNumberOfSheets();
                            for (int k = 2; k < ns; k++) {
                                hiderow(k, 10, day, workbook);
                            }
                            //hiderow(2, 10, day, workbook);
                            //hiderow(3, 6, day, workbook);
                        }                    }
                }
                //end                fos = new FileOutputStream(new File(toFileName));
                workbook.write(fos);
                fos.flush();
                System.out.println("from " + templateName);
                System.out.println(toFileName + " genarate over");
                if (sys_logger.isDebugEnabled()){
                    sys_logger.debug(toFileName + " genarate over");
                }
            }
            //catch (Throwable t) {
            catch (Exception t) {
                t.printStackTrace();
                sys_logger.error(i + ":Exception(" + row + ":" + col + ":" + num + ":" +
                             cell.getCellType() + ")");
                sys_logger.error(t);
            }
            finally {                try {
                    if (fos != null) {
                        fos.close();
                    }
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
                workbook = null;
                pfs = null;
                //LoggerUtils.recordError(ReportConstants.REPORT_WEB_LOG, "3" + reportName);
                ConnectionPool.releaseResultSet(rs);
                ConnectionPool.releasePreparedStatement(pstmt);
                ConnectionPool.releasePreparedStatement(pstmt1);
            }
        }
    }
这是我前面写的一个函数是从数据库中读记录写入EXCEL 你参考一下，用POI作的

Answer 8

LZ自己看看吧最好自己研究透彻 倒入的话建议把excel 转换为cvs进行倒入

Answer 9

用sqlserver的DTS比上面的好像都要方便吧

Answer 10

建议楼主用DTS，在程序用dtsrunui调用DTS包。