数据没有添加验证,需要添加
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;
}
}