当前位置: 代码迷 >> Java Web开发 >> 怎么把excel里面的数据 导入oracle中已经存在的表中,用代码实现 最好能远程导入服务器上
  详细解决方案

怎么把excel里面的数据 导入oracle中已经存在的表中,用代码实现 最好能远程导入服务器上

热度:249   发布时间:2016-04-17 12:34:15.0
如何把excel里面的数据 导入oracle中已经存在的表中,用代码实现 最好能远程导入服务器上
如何把excel里面的数据 导入oracle中已经存在的表中,用代码实现 最好能远程导入服务器上
各位大虾帮帮忙··

------解决方案--------------------
我的一个class,函数que(sheet名),返回所有行列数据在Iterator(array)
Java code
import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.text.NumberFormat;import java.util.ArrayList;import java.util.Iterator;import java.util.Locale;import org.apache.log4j.ConsoleAppender;import org.apache.log4j.Level;import org.apache.log4j.Logger;import org.apache.log4j.PatternLayout;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.poifs.filesystem.POIFSFileSystem;public class jexcel {    private String file = "";    private POIFSFileSystem fs = null;    private HSSFWorkbook wb = null;    private int cols = 41;// 数据列数,去掉砂层厚度后的    private int cell_colums, cell_rows;    private static Logger logger = Logger.getLogger(jexcel.class.getName());    /**     * 构造函数,     *      * @param f -     *            Excel文件名     */    public jexcel(String f) {        String pattern = "%5p (%F:%L) - %m%n";        PatternLayout layout = new PatternLayout(pattern);        ConsoleAppender appender = new ConsoleAppender(layout);        logger.removeAllAppenders();        logger.addAppender(appender);        logger.setLevel((Level) Level.DEBUG);        try {            this.setFile(f);            this.init();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }    }    public jexcel() {    }    public void setFile(String f) {        this.file = f;    }    private void init() throws FileNotFoundException, IOException {        fs = new POIFSFileSystem(new FileInputStream(file));        wb = new HSSFWorkbook(fs);    }public Iterator que(String n) {        // Map<Integer, String[]> data=new HashMap<Integer, String[]>();//use        // map        // String datas[][] = null;//use array        ArrayList datass = null;        if (fs == null) {            try {                this.init();            } catch (FileNotFoundException e) {                e.printStackTrace();            } catch (IOException e) {                e.printStackTrace();            }        }        HSSFSheet sheet = wb.getSheet(n);        // HSSFSheet csheet=wb.cloneSheet(0);        // wb.setSheetName(3,"复制"+wb.getSheetName(0),(short)1);//注意(short)1中文编码        // try {        // FileOutputStream fo=new FileOutputStream(file);        // try {        // wb.write(fo);        // fo.close();        // } catch (IOException e) {        // // TODO 自动生成 catch 块        // e.printStackTrace();        // }        //                    // } catch (FileNotFoundException e) {        // // TODO 自动生成 catch 块        // e.printStackTrace();        // }        // Iterator rows=sheet.rowIterator();        logger.debug("行数:" + sheet.getLastRowNum());        datass = new ArrayList(sheet.getLastRowNum());        cell_rows = sheet.getLastRowNum();        for (int j = 1; j <= sheet.getLastRowNum(); j++) {            HSSFRow row = sheet.getRow(j);            int ron = row.getRowNum();            // System.out.println("row #"+ron);            if (j == 1) {                logger.debug("列数:" + row.getLastCellNum());            }            cell_colums = row.getLastCellNum();            // String[] ce=new String[cols];            String[] ce = new String[cell_colums];            String tem = "";            for (int i = 0; i < cell_colums; i++) {                HSSFCell cell = row.getCell((short) i);                try {                    switch (cell.getCellType()) {                    case HSSFCell.CELL_TYPE_NUMERIC:                        double dd = cell.getNumericCellValue();                        NumberFormat nf = NumberFormat                                .getInstance(Locale.CHINESE);// java.text.NumberFormat,数字格式化                        nf.setGroupingUsed(false);// 不使用组,就是逗号科学计数                        tem = nf.format(dd);                        if (tem.indexOf(".") > 0) {                            int p = tem.indexOf(".");                            // logger.info(tem.substring(p));                            if (tem.substring(p).length() > 3)                                tem = tem.substring(0, tem.length() - 1);                        }                        break;                    case HSSFCell.CELL_TYPE_STRING:                        // logger.info(tem);                        tem = cell.getStringCellValue().trim();                        if (tem.length() == 0)                            tem = " ";                        break;                    case HSSFCell.CELL_TYPE_BLANK:                        tem = " ";                        break;                    default:                        tem = "null";                        break;                    }                    ce[i] = tem;                } catch (NullPointerException e) {                    // logger.error("read error data! "+j);                    break;                }            }            datass.add(ce);        }        logger.debug("" + datass.size());        return datass.iterator();    }}
  相关解决方案