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