JAVA poi 导入数据库_javaPOI操作excel 进行数据导入数据库

POI操作excel 进行数据导入数据库   经过几天的学习学习，抄袭抄袭，窃取，外加学习。终于在项目中成功运行此功能模块。因此总结一下：

POI导入关键是FormFile的应用。

jsp：

function myFormCheck(theform)

{

if(theform.theFile.value==”")

{

alert(“请点击浏览按钮，选择您要上传的文件!”)

theform.theFile.focus;

return (false);

}

else

{

str= theform.theFile.value;

strs=str.toLowerCase();

lens=strs.length;

extname=strs.substring(lens-4,lens);

if(extname!=”.xls”)

{

alert(“请选择excel文件！”)

return (false);

}

}

}

Insert title here

struts-config.xml

ACTIONFORM

package com.odpnet.web.actionform;

import org.apache.struts.action.ActionForm;

import org.apache.struts.upload.FormFile;

public class UploadForm extends ActionForm {

private FormFile theFile;

public FormFile getTheFile() {

return theFile;

}

public void setTheFile(FormFile theFile) {

this.theFile = theFile;

}

}

Action

package com.odpnet.web.action;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.ArrayList;

import java.util.List;

import java.util.logging.Logger;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.upload.FormFile;

import com.odpnet.biz.service.ProcessExecutionService;

import com.odpnet.web.actionform.UploadForm;

public class CosmeticsFormulaImportTableAction extends Action {

private static final Logger log = Logger.getLogger(“CosmeticsFormulaActionExcl”);

private ProcessExecutionService processExecutionService;

public void setProcessExecutionService(

ProcessExecutionService processExecutionService) {

this.processExecutionService = processExecutionService;

}

public ActionForward execute(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response) {

UploadForm uploadForm = (UploadForm) form;// TODO Auto-generated method stub

FormFile xlsFile=uploadForm.getTheFile();

System.out.println(“%%%%%%%%%%%%%%%%%%%%%%%%”+xlsFile.getFileName());

System.out.println(“%%%%%%%%%%%%%%%%%%%%%%%%”+xlsFile.getContentType());

System.out.println(“%%%%%%%%%%%%%%%%%%%%%%%%”+xlsFile.getFileSize());

if(!xlsFile.getContentType().equals(“application/vnd.ms-excel”))不是excel文件

{

System.out.println(“类型不对”);

return mapping.findForward(“import_data_error”);

}

else

{

try {

if(!processExecutionService.importTableTemplate(xlsFile.getInputStream()))

{

return mapping.findForward(“import_data_error”);

}

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return mapping.findForward(“import_data_error”);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return mapping.findForward(“import_data_error”);

}

List list = new ArrayList();;

String sqlStr =”from CosmeticsFormula”;

list = processExecutionService.getList(sqlStr);

System.out.print(“______——–))))))”+list.size());

request.setAttribute(“list”, list);

return mapping.findForward(“CosmeticsFormulaList”);

}

}

}

Dao 方法及关键的POI方法

public boolean importTableTemplate(InputStream is) {

Vector vc = readExcel(is);

if(vc.size()!=0) {

for (int i = 0; i < vc.size(); i++) {

System.out.println(vc.size());

ArrayList ay = (ArrayList) vc.get(i);

CosmeticsFormula cf =new CosmeticsFormula((String)ay.get(0),(String)ay.get(1),(String)ay.get(2),(String)ay.get(3),(String)ay.get(4),(String)ay.get(5),(String)ay.get(6),(String)ay.get(7));

this.getHibernateTemplate().save(cf);

//if (null != ay && ay.size() == 4) {

System.out.println(ay.get(0));

}

return true;

}else {

return false;

}

}

public Vector readExcel(InputStream is) {

DecimalFormat df = new DecimalFormat(“”);

ArrayList ay = null;

try {

// 创建对Excel工作簿文件的引用

HSSFWorkbook workbook = new HSSFWorkbook(is);

// 获取sheet数

// System.out.println(“===SheetsNum===” +

// workbook.getNumberOfSheets());

// 循环每一个sheet

for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {

// 判断sheet是否为空

if (null != workbook.getSheetAt(numSheets)) {

// 获得一个sheet

HSSFSheet aSheet = workbook.getSheetAt(numSheets);

// 获取sheet的第一行数

// System.out.println(“+++getFirstRowNum+++”

// +aSheet.getFirstRowNum());

// 获取sheet的最后一行数

System.out.println(“+++getLastRowNum+++”

+aSheet.getLastRowNum());

Vector vc = new Vector();

// 循环每一行

for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet

.getLastRowNum(); rowNumOfSheet++) {

ay = new ArrayList();

// 判断是否为空行

if (null != aSheet.getRow(rowNumOfSheet)) {

// 获取一个行引用

HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

// 获取sheet的第一列数

// System.out.println(>>>getFirstCellNum<<

// 获取sheet的最后一列数

// System.out.println(>>>getLastCellNum<<

// 循环每一列

for (short cellNumOfRow = 0; cellNumOfRow <= aRow

.getLastCellNum(); cellNumOfRow++) {

// 判断列是否为空

if (null != aRow.getCell(cellNumOfRow)) {

// 获取列引用

HSSFCell aCell = aRow.getCell(cellNumOfRow);

// 取列类型

int cellType = aCell.getCellType();

System.out.println(“++++++++++^^^^^^^^^^^^”+cellType);

switch (cellType) {

case 0:// Numeric

String strCell = df.format(aCell

.getNumericCellValue());

// 添加单元格数据到队列中

ay.add(strCell);

break;

case 1:// String

strCell = aCell.getStringCellValue();

// 添加单元格数据到队列中

ay.add(strCell);

break;

default:

}

}

}

}

vc.add(ay);

}

return vc;

}

}

} catch (Exception e) {

System.out.println(“读取excel文件错误:” + e);

}

return null;

}

大体就是这样。。我用的是POI 2.5.1版本的。