本文目录一览：
- 1、java excel一般怎样导入oracel数据库
- 2、poi如何去写入excel文件
- 3、java通过poi生成excel的版本问题
- 4、npoi 读取excel 怎么让数据有双引号
- 5、java如何读写excel2010
- 6、如何用java将excel导入oracle
java excel一般怎样导入oracel数据库
你是说用java读取excel的数据插入数据库？ 可以使用第三方插件，下载一个jxl.jar放到工程中；
如果你的excel数据是一行对应一条表数据的话，我这里有个例子你参考下：
public class ReaderExcel {
public static void main(String[] args) throws FileNotFoundException {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File("D:/test.xls"));
Sheet rs = wb.getSheet(0);
int column = 3;//列数
for (int i = 0; i rs.getRows(); i++) {
for (int j = 0; j column; j++) {
/**
比如你有3列：用户名、密码、 ***
那么这个内循环全部循环完就可以得到一行数据的3个值；
rs.getCell(j, i).getContents().trim()
具体你可以找个excel试试，打印一下看下输出就懂了
*/
System.out.println(rs.getCell(j, i).getContents().trim());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
记得下载jxl.jar导入项目中哦！！！望采纳！
poi如何去写入excel文件
直接全部在action里面写的，这个就不多说了，直接上代码：public String executeExcel() throws Exception{ String realPath = ServletActionContext.getServletContext().getRealPath("/fileupload"); System.out.println(fileFileName); String filePath = ""; if(this.file!=null){ File saveFile = new File(new File(realPath),this.fileFileName); filePath = realPath+"\\"+this.fileFileName; System.out.println(filePath); if(!saveFile.getParentFile().exists()){ saveFile.getParentFile().mkdirs(); } FileUtils.copyFile(file, saveFile); } this.exlToDB(filePath); ActionContext.getContext().put("message","导入成功"); return "success"; } //读取excel2007，并把数据插入数据库 public void exlToDB(String filePath){ boolean flag = true; AllKpi akpi = new AllKpi(); try { // 文件流指向excel文件 FileInputStream fin=new FileInputStream(filePath); XSSFWorkbook workbook = new XSSFWorkbook(fin);// 创建工作薄 XSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表 XSSFRow row = null;// 对应excel的行 XSSFCell cell = null;// 对应excel的列 int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数 System.out.println(totalRow); // 以下的字段一一对应数据库表的字段 float idd = 0.0f; String id = ""; String Name = ""; String DEPT_NAME = ""; String Weight = ""; span/spanString ALGORITHM = ""; String text = " "; //String sql = "insert into DSP_TJ_KPI values(DSP_TJ_KPI_SEQ.nextval,?,?,?,'无',?)"; for (int i = 1; i = totalRow; i++) { row = sheet.getRow(i); //System.out.println(row.getCell(0).toString()); if(row.getCell(0) != null !"".equals(row.getCell(0)) row.getCell(1) != null !"".equals(row.getCell(1)) row.getCell(2) != null !"".equals(row.getCell(2)) row.getCell(3) != null !"".equals(row.getCell(3))){ cell = row.getCell((short) 0); Name = cell.toString(); System.out.println(Name); cell = row.getCell((short) 1); Weight = cell.toString(); System.out.println(Weight); cell = row.getCell((short) 2); DEPT_NAME = cell.toString(); System.out.println(DEPT_NAME); cell = row.getCell((short) 3); ALGORITHM = cell.toString(); System.out.println(ALGORITHM); akpi.setAllkpiName(Name); akpi.setAllkpiDeptName(DEPT_NAME); akpi.setAllkpiWeight(Weight); akpi.setAlgorithm(ALGORITHM); akpi.setText(text); allKpiService.addAllKpi(akpi); //以下注释代码为连接jdbc测试代码块 /*pst = con.prepareStatement(sql); //pst.setString(1, student_id); pst.setString (1, DEPT_NAME); pst.setString (2, Name); pst.setString (3, Weight); span/spanpst.setString(4, ALGORITHM); pst.execute();*/ System.out.println("preparestatement successful"); } } /*pst.close(); con.close();*/ fin.close(); } catch (FileNotFoundException e) { flag = false; e.printStackTrace(); } catch (IOException ex) { flag = false; ex.printStackTrace(); }
java通过poi生成excel的版本问题
XSSF不能读取Excel2003以前（包括2003）的版本,
没需要就按你之前的继续，如果在读取前判断文件是2003前的版本还是2007的版本，提供个思路。XSSF和HSSF虽然在不同的包里，但却引用了同一接口Workbook,
Workbook book = null;
try {
book = new XSSFWorkbook(excelFile);
} catch (Exception ex) {
book = new HSSFWorkbook(new FileInputStream(excelFile));
}
各版本的Excel中测试，没有发生异常
npoi 读取excel 怎么让数据有双引号
1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。
2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始
3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。 HSSF和XSSF的区别如下：
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
下面是用NPOI读写Excel的例子：ExcelHelper封装的功能主要是把DataTable中数据写入到Excel中，或者是从Excel读取数据到一个DataTable中。
ExcelHelper类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
namespace NetUtilityLib
{
public class ExcelHelper : IDisposable
{
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper(string fileName)
{
this.fileName = fileName;
disposed = false;
}
/// summary
/// 将DataTable数据导入到excel中
/// /summary
/// param name="data"要导入的数据/param
/// param name="isColumnWritten"DataTable的列名是否要导入/param
/// param name="sheetName"要导入的excel的sheet的名称/param
/// returns导入数据行数(包含列名那一行)/returns
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// summary
/// 将excel中的数据导入到DataTable中
/// /summary
/// param name="sheetName"excel工作薄sheet的名称/param
/// param name="isFirstRowColumn"之一行是否是DataTable的列名/param
/// returns返回的DataTable/returns
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取之一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i = rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
}
}
java如何读写excel2010
package com.b2bjy.crm.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POIExcelUtil
{
/** *//** 总行数 */
private int totalRows = 0;
/** *//** 总列数 */
private int totalCells = 0;
/** *//** 构造 *** */
public POIExcelUtil()
{}
/** *//**
* ul
* liDescription:[根据文件名读取excel文件]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @param fileName
* @return
* @throws Exception
*/
public ArrayListString read(String fileName)
{
ArrayListString dataLst = new ArrayListString();
/** *//** 检查文件名是否为空或者是否是Excel格式的文件 */
if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
{
return dataLst;
}
boolean isExcel2003 = true;
/** *//** 对文件的合法性进行验证 */
if (fileName.matches("^.+\\.(?i)(xlsx)$"))
{
isExcel2003 = false;
}
/** *//** 检查文件是否存在 */
File file = new File(fileName);
if (file == null || !file.exists())
{
return dataLst;
}
try
{
/** *//** 调用本类提供的根据流读取的 *** */
dataLst = read(new FileInputStream(file), isExcel2003);
}
catch (Exception ex)
{
ex.printStackTrace();
}
/** *//** 返回最后读取的结果 */
return dataLst;
}
/** *//**
* ul
* liDescription:[根据流读取Excel文件]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @param inputStream
* @param isExcel2003
* @return
*/
public ArrayListString read(InputStream inputStream,
boolean isExcel2003)
{
ArrayListString dataLst = null;
try
{
/** *//** 根据版本选择创建Workbook的方式 */
Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
: new XSSFWorkbook(inputStream);
dataLst = read(wb);
}
catch (IOException e)
{
e.printStackTrace();
}
return dataLst;
}
/** *//**
* ul
* liDescription:[得到总行数]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @return
*/
public int getTotalRows()
{
return totalRows;
}
/** *//**
* ul
* liDescription:[得到总列数]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @return
*/
public int getTotalCells()
{
return totalCells;
}
/** *//**
* ul
* liDescription:[读取数据]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @param wb
* @return
*/
private ArrayListString read(Workbook wb)
{
ArrayListString rowLst = new ArrayListString();
/** *//** 得到之一个shell */
Sheet sheet = wb.getSheetAt(0);
this.totalRows = sheet.getPhysicalNumberOfRows();
if (this.totalRows = 1 sheet.getRow(0) != null)
{
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** *//** 循环Excel的行 */
for (int r = 0; r this.totalRows; r++)
{
Row row = sheet.getRow(r);
if (row == null)
{
continue;
}
/** *//** 循环Excel的列 */
for (short c = 0; c 1; c++)
{
Cell cell = row.getCell(c);
String cellValue = "";
if (cell == null)
{
rowLst.add(cellValue);
continue;
}
/** *//** 处理数字型的,自动去零 */
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType())
{
cellValue = getRightStr(cell.getNumericCellValue() + "");
}
/** *//** 处理字符串型 */
else if (Cell.CELL_TYPE_STRING == cell.getCellType())
{
cellValue = cell.getStringCellValue();
}
/** *//** 处理布尔型 */
else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType())
{
cellValue = cell.getBooleanCellValue() + "";
}
/** *//** 其它的,非以上几种数据类型 */
else
{
cellValue = cell.toString() + "";
}
rowLst.add(cellValue);
}
}
return rowLst;
}
/** *//**
* ul
* liDescription:[正确地处理整数后自动加零的情况]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @param sNum
* @return
*/
private String getRightStr(String sNum)
{
DecimalFormat decimalFormat = new DecimalFormat("#.000000");
String resultStr = decimalFormat.format(new Double(sNum));
if (resultStr.matches("^[-+]?\\d+\\.[0]+$"))
{
resultStr = resultStr.substring(0, resultStr.indexOf("."));
}
return resultStr;
}
/** *//**
* ul
* liDescription:[测试main *** ]/li
* liCreated by [Huyvanpull] [Jan 20, 2010]/li
* liMidified by [modifier] [modified time]/li
* ul
*
* @param args
* @throws Exception
*/
public String getPhones(){
ArrayListString dataLst = new POIExcelUtil().read("D:\\b.xls");
StringBuffer rowData = new StringBuffer();
for (int i = 0; i dataLst.size()-1; i++) {
rowData.append(dataLst.get(i)).append(",");
}
rowData.append(dataLst.get(dataLst.size()-1));
if (rowData.length() 0)
{
System.out.println(rowData);
}
return rowData.toString();
}
public static void main(String[] args) throws Exception
{
System.out.println(new POIExcelUtil().getPhones());
}
}
如何用java将excel导入oracle
思路：用户选择要导入的EXCEL文件，上传至WEB服务器。然后将文件存放目录传给POI类。通过对row循环取到cell的值，最后insert到ORACLE中。
public boolean saleDeptToDB(String spreadSheet)throws HekException{
boolean flag = false;
IDBConn db = DBConn.getInstance();
IDBOperate dbOp = DBOperate.getInstance();
Connection conn = db.getConn();
PreparedStatement pstmt = null;
try{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(spreadSheet));
HSSFSheet sheet = workbook.getSheetAt(0);
String sql = "insert into tableName(DEPT_ID,DEPT_CODE,DEPT_DESC,ITEM_NO,ITEM_CATE,BUDGET_QTY,BUDGET_AMOUNT,TIME_ID)";
sql +="values(?,?,?,?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
for(int rowNumOfSheet=1;rowNumOfSheet
HSSFRow rowOfSheet = sheet.getRow(rowNumOfSheet);
HekSaleDeptManual hek = new HekSaleDeptManual();
HSSFCell cell0 = rowOfSheet.getCell((short)0);
if(cell0 != null)hek.setDeptId((int)cell0.getNumericCellValue());
HSSFCell cell1 = rowOfSheet.getCell((short)1);
if(cell1 != null)hek.setDeptCode(cell1.getRichStringCellValue().toString());
HSSFCell cell2 = rowOfSheet.getCell((short)2);
if(cell2 != null)hek.setDeptDesc(cell2.getRichStringCellValue().toString());
HSSFCell cell3 = rowOfSheet.getCell((short)3);
if(cell3 != null)hek.setItemNo(cell3.getRichStringCellValue().toString());
HSSFCell cell4 = rowOfSheet.getCell((short)4);
if(cell4 != null)hek.setItemCate(cell4.getRichStringCellValue().toString());
HSSFCell cell5 = rowOfSheet.getCell((short)5);
if(cell5 != null)hek.setBudgetQty(cell5.getNumericCellValue());
HSSFCell cell6 = rowOfSheet.getCell((short)6);
if(cell6 != null)hek.setBudgetQty(cell6.getNumericCellValue());
HSSFCell cell7 = rowOfSheet.getCell((short)7);
if(cell7 != null)hek.setTimeID(cell7.getRichStringCellValue().toString());
dbOp.insertBathHekDept(pstmt, hek);
}
pstmt.executeBatch();
flag = true;
conn.commit();
}catch(SQLException ex){
db.rollbackTransaction(conn);
System.out.println("recordToDB Error: "+ex);
}catch(IOException ioex){
System.out.println("saleDeptToDB read file Error: "+ioex);
}finally{
db.closePstmt(pstmt);