如何导入excel并存入数据库（jxl）

数据没有添加验证，需要添加

package com.scintmed.core.action.pub;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.sql.DriverManager;

 

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

 

import com.mysql.jdbc.PreparedStatement;

import com.opensymphony.xwork2.ActionSupport;

import com.scintmed.core.action.support.BaseActionSupport;

import com.scintmed.core.utils.string.UUIDPK;

public class ImportExcelAction extends BaseActionSupport{

private static final String URL = "jdbc:mysql://192.168.1.133:3306/erp?user=root&password=root&useUnicode=true"; 

private java.sql.Connection conn = null; 

private PreparedStatement ps = null; 

    private int num;

private File file;

/**

 * @param args

 * @throws IOException 

 * @throws FileNotFoundException 

 */

public  void ImportExcel() throws FileNotFoundException, IOException {

InputStream is = new FileInputStream(file);

try

{

Workbook book =  Workbook.getWorkbook(is);

int sheetNum=book.getNumberOfSheets();

for(int num=0;num<sheetNum;num++){

Sheet sheet = book.getSheet(num);

int rowNumber = sheet.getRows();

 

//获得对应的单元格

Cell medicineCodeCell = null;

Cell productNameCell = null;

Cell productStandardCell = null;

Cell typeCell = null;

Cell performanceAndCompositionCell = null;

Cell produceAreaCell = null;

Cell fileCell = null;

Cell remarkCell = null;

Cell registerCodeCell = null;

Cell productUnitCell = null;

Cell addressCell = null;

Cell postalCodeCell = null;

Cell expiryDateCell = null;

Cell approvedDateCell = null;

Cell trialScopeCell = null;

Cell changeDateCell = null;

Class.forName("com.mysql.jdbc.Driver").newInstance(); 

conn = DriverManager.getConnection(URL); 

ps = (PreparedStatement) conn.prepareStatement(

"insert into erp_bibliology(id,medicineCode,productName,productStandard,"+

"type,performanceAndComposition,produceArea,file,remark,"+

"registerCode,productUnit,address,postalCode,"+

"expiryDate,approvedDate,trialScope,changeDate)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 

for(int i=2;i<=rowNumber-1;i++){

medicineCodeCell = sheet.getCell(1, i); 

productNameCell = sheet.getCell(2, i); 

productStandardCell = sheet.getCell(3, i); 

typeCell = sheet.getCell(4, i); 

performanceAndCompositionCell = sheet.getCell(5, i); 

produceAreaCell = sheet.getCell(6, i); 

fileCell = sheet.getCell(7, i); 

remarkCell = sheet.getCell(8, i); 

registerCodeCell = sheet.getCell(9, i); 

productUnitCell = sheet.getCell(10, i); 

addressCell = sheet.getCell(11, i); 

postalCodeCell = sheet.getCell(12, i); 

expiryDateCell = sheet.getCell(13, i); 

approvedDateCell = sheet.getCell(14, i); 

trialScopeCell = sheet.getCell(15, i); 

changeDateCell = sheet.getCell(16, i); 

 

ps.setString(1,UUIDPK.getUUID(this));

ps.setString(2,medicineCodeCell.getContents());

ps.setString(3,productNameCell.getContents());

ps.setString(4,productStandardCell.getContents());

ps.setString(5,typeCell.getContents());

ps.setString(6,performanceAndCompositionCell.getContents());

ps.setString(7,produceAreaCell.getContents());

ps.setString(8,fileCell.getContents());

ps.setString(9,remarkCell.getContents());

ps.setString(10,registerCodeCell.getContents());

ps.setString(11,productUnitCell.getContents());

ps.setString(12,addressCell.getContents());

ps.setString(13,postalCodeCell.getContents());

ps.setString(14,expiryDateCell.getContents());

ps.setString(15,approvedDateCell.getContents());

ps.setString(16,trialScopeCell.getContents());

ps.setString(17,changeDateCell.getContents());

num = ps.executeUpdate();

}

}

 

//            setJavaScript("alert(123)");

 

} catch (Exception e)

{

e.printStackTrace();

}

 

}

 

 

 

public File getFile()

{

return file;

}

public void setFile(File file)

{

this.file = file;

}

 

 

 

}