如何把excel里面的数据 导入oracle中已经存在的表中，用代码实现 最好能远程导入服务器上
如何把excel里面的数据 导入oracle中已经存在的表中，用代码实现 最好能远程导入服务器上
各位大虾帮帮忙··
[解决办法]
我的一个class，函数que(sheet名)，返回所有行列数据在Iterator（array）
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(); }}
[解决办法]
用poi做吧,我也是用poi写的,有问题给我发信息
这是我的jsp例子
<%@page contentType="text/html; charset=GBK" import="java.io.*,org.apache.poi.poifs.filesystem.POIFSFileSystem,org.apache.poi.hssf.record.*,org.apache.poi.hssf.model.*,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.util.*"%>
<%@ page import="auth.*,java.util.Vector"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.Calendar"%>
<jsp:useBean id="DBC" scope="page" class="auth.DBCurrent"/>
<jsp:useBean id="BAC" scope="page" class="auth.BACurrent"/>
<%@ page import="java.util.Date,java.util.Random,java.lang.*,java.util.*"%>
<%@ page import="auth.*"%>
<%response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
Connection conn = DBC.getConn();
Statement stmt = DBC.stmtOpen(conn);
String sql = "";
ResultSet rs = null;
//sql = "select max(gyyxx_id)+1 t from yyf_gyyxx ";
//rs = DBC.doQuery(stmt,sql);
//rs.next();
// int Maxd = rs.getInt("t");
//out.println("第一一个="+Maxd);
%>
<html>
<head><title>Read Excel file </title>
</head>
<body>
An example of using Jakarta POI's HSSF package to read an excel spreadsheet:
<form name="form1" method="get" action="">
Select an Excel file to read.
<input type="file" name="xls_filename" onChange="form1.submit()">
</form>
<%
String filename = request.getParameter("xls_filename");
if (filename != null && !filename.equals("")) {
%>
<br>You chose the file <%= filename %>.
<br><br>It's contents are:
<%
try
{
// create a poi workbook from the excel spreadsheet file
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(filename));
HSSFWorkbook wb = new HSSFWorkbook(fs);
for (int k = 0; k < wb.getNumberOfSheets(); k++)
{
%>
<br><br>Sheet <%= k %> <br>
<%
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
%>
<br><b>ROW <%= row.getRowNum() %> </b>
<%Vector vet = new Vector();
for (short c = 0; c < cells; c++)
{
HSSFCell cell = row.getCell(c);
if (cell != null) {
String value = null;
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
value = Math.round((float)cell.getNumericCellValue()*10)/10.0+"";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = String.valueOf( cell.getNumericCellValue())+"";
break;
case HSSFCell.CELL_TYPE_STRING :
value =cell.getStringCellValue();
break;
default :
value ="";
}
vet.add(value);
%>
<%//= "CELL col=" + cell.getCellNum()+ " VALUE=" + value %>
<%
} //if
}//for
out.println("<br>"+"---"+vet.get(0)+"---"+vet.get(1)+"---"+vet.get(2)+"---"+vet.get(3)+"---"+vet.get(4)+"---"+vet.get(5)+"---"+vet.get(6)+"---"+vet.get(7)+"<br>");
//if(BAC.NullToStr(vet.get(0)).equals("")==false){
//if(BAC.NullToStr(vet.get(0)).equals("")==false){
//if (BAC.isInt(vet.get(0))==true){
String lpo = vet.get(5)+"";
String epo = vet.get(7)+"";
String str = lpo.substring(0,4)+"-"+lpo.substring(4,6)+"-"+"1";//出生日期
String ends = epo.substring(0,4)+"-"+epo.substring(4,6)+"-"+"1";//截止日期
//以下是求今天是不是本月最后一天
SimpleDateFormat mn=new SimpleDateFormat("yyyy-MM-dd");
Date DaTime = new Date();
DaTime=mn.parse(ends);
Calendar CalTime = Calendar.getInstance();
CalTime.setTime(DaTime);
int LastDay = CalTime.getActualMaximum(Calendar.DAY_OF_MONTH);
int Now = CalTime.get(Calendar.DAY_OF_MONTH);
out.println(str);//
String endsE = epo.substring(0,4)+"-"+epo.substring(4,6)+"-"+LastDay;//截止日期最终
out.println("最后一天="+endsE);//
//str.substring(0,7);
//}
String S0 = vet.get(0).toString();
String S3 = vet.get(3).toString();
String S4 = vet.get(4).toString();
String S6 = vet.get(6).toString();
int maxID = DBC.getMaxID("yyf_gyyxx","gyyxx_id","") + 1;
String Isql = "insert into yyf_gyyxx values("+maxID+",3,'"+S0+"','"+S3+"','"+S4+"',date'"+str+"','"+S6+"',date'"+endsE+"',0,6)";
int err = DBC.doUpdate(Isql);//out.println("<br>=="+value);
out.println("<br>----------------");
out.println("sql="+Isql);
out.println("<br>----------------");
//}
//rs.close();
//stmt.close();
//conn.close();
/*if (err!=1){
%>
<script>
alert("错误提示：数据添加失败！--<%=S0%>");
</script>
<%
}else{
%>
<script>
alert('提示:添加成功!');
</script>
<%}*///out.println("<br>");
}
}
}
}
catch (Exception e)
{
%>
Error occurred: <%= e.getMessage() %>
<%
e.printStackTrace();
}
}
%>
</body>
</html>