POI读取Excel2003数据存储数据库

POI读取Excel2003数据存储数据库

 

1、JSP Form表单部分代码（关键：enctype="multipart/form-data"）

<FORM id=impform name=impform action="ImportOperation1.jsp" 

method=post enctype="multipart/form-data" onSubmit="onSave();return false">

<TABLE class=viewform width="100%">

  <COLGROUP>

  <COL width="20%">

  <COL width="80%">

  <TBODY>

  <TR class=Title>

    <TH colSpan=2><%=SystemEnv.getHtmlLabelName(19464,user.getLanguage())%></TH></TR>

  <TR class=spacing>

    <TD class=line1 colSpan=2 ></TD></TR>

  <TR class= Spacing><TD class=Line colSpan=2></TD></TR>

  <TR>

<TD><%=SystemEnv.getHtmlLabelName(16699,user.getLanguage())%></TD>

<TD class=Field>

<input class=inputstyle type=file  name="filename" size=40>&nbsp;&nbsp;

<button Class=AddDoc type=button onclick="onSave(this)">

<%=SystemEnv.getHtmlLabelName(18596,user.getLanguage())%></button> </TD>

  </TR>

  <TR class= Spacing><TD class=Line colSpan=2></TD></TR>

  

<%

String msg="";

if(request.getAttribute("msg")!=null){

msg = Util.null2String((String)request.getAttribute("msg"));

}

if(msg!=""){

%>

<TR>

<td id="msg" align="left" colspan="3">

<font size="2" color="#FF0000"><%=msg %></font>

</td>

</TR>

<%} %>

<TR>

<td><a href="markerInformation1.xls"><%=SystemEnv.getHtmlLabelName(18616,user.getLanguage())%></a></td>

 

</TR>

<TR>

<td  align="left" colspan="3">

说明：请按模板排列导入EXCEL文档中各字段的顺序 </td>

</TR>

<TR class= Spacing><TD class=Line1 colSpan=2></TD></TR>

</TBODY>

</TABLE>

<div id='_xTableLoadmask' style='background:#FFFFFF;padding:3px;width:100%;display:none' valign='top'></div>

 

</FORM>

 

2、接收请求ImportOperation1.jsp

<%@page import="weaver.file.FileUploadToPath"%>

<%@ page import="weaver.general.Util" %>

<%@ page import="java.util.*" %>

<%@ page language="java" contentType="text/html; charset=GBK" %> <%@ include file="/systeminfo/init.jsp" %>

<jsp:useBean id="rs" class="weaver.conn.RecordSet" scope="page" />

<%@ page import="westvalley.basis.Markerinformation"%>

 

<%

 

//文件上传

FileUploadToPath fu = new FileUploadToPath(request) ; 

//上传后的文件路径

filename = fu.uploadFiles("filename");

Markerinformation mi = new Markerinformation(request,response);

mi.import1Data(filename,request);

//进行导入

System.err.println("信息"+mi.getMsg());

request.setAttribute("msg", mi.getMsg());

request.getRequestDispatcher("import1.jsp").forward(request, response);

 

%>

 

3、读取Excel内容并插入数据

/**

* 市场信息反馈导入来自营销中心

* @param Excelfilepath

* @throws Exception

*/

public String import1Data(String path,HttpServletRequest request) throws IOException {

       InputStream is = new FileInputStream(path);

       HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

       msg = "";

       User localUser = (User)request.getSession(true).getAttribute("weaver_user@bean");

String strUserId  = Util.null2String(localUser.getUID());

 

       try {

        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

       

        int checkresult = check1Header(hssfSheet);

if(checkresult !=-1){

msg += "数据导入失败，第"+(checkresult)+"行，表头有误，与模板中的表头不一样，请认真核对";

}

       

if("".equals(msg)){

// 读取列数

for (int i = 0; i < hssfWorkbook.getNumberOfSheets(); i++) {

   // 读取行数

   for (int j = 1; j <= hssfSheet.getLastRowNum(); j++) {

       HSSFRow hssfRow = hssfSheet.getRow(j);

       if (hssfRow != null) {

           HSSFCell classify_timeCell = hssfRow.getCell((short)0);

           HSSFCell areaCell = hssfRow.getCell((short) 1);

           HSSFCell typeCell = hssfRow.getCell((short) 2);

           HSSFCell keywordCell = hssfRow.getCell((short) 3);

           HSSFCell information_feedbakCell = hssfRow.getCell((short) 4);

           HSSFCell note1Cell = hssfRow.getCell((short) 5);

           HSSFCell note2Cell = hssfRow.getCell((short) 6);

           HSSFCell advise_feedbakCell = hssfRow.getCell((short) 7);

           String classify_time = trimToString( getValue(classify_timeCell).equals("")?"":getValue(classify_timeCell)+"-01");   //月份

           String area ="";

           if(areaCell!=null){

            area = trimToString( getValue(areaCell)); //区域

           }else{

            msg += "第"+(j)+"行,第"+(2)+"列，区域不能为空！<br>";

            continue;

           }

           String type = trimToString( returnType(getValue(typeCell).trim())); //类型

           if("7".equals(type)){

            msg += "第"+(j)+"行,第"+(3)+"列，分类错误！<br>";

            continue;

           }

           String keyword = trimToString( getValue(keywordCell)); //关键字

           String information_feedbak = trimToString( getValue(information_feedbakCell)); //信息反馈

           String note1 = trimToString( getValue(note1Cell)); //备注1

           String note2 = trimToString( getValue(note2Cell)); //备注2

           String advise_feedbak = trimToString( getValue(advise_feedbakCell)); //建议-反馈

           

           //拼接插入语句

           String  strInsertSql = " INSERT INTO marker_information_back("+

            "classify_time,"+

            "area,"+

            "type,"+

            "keyword,"+

            "information_feedbak,"+

            "note1,"+

            "note2,"+

            "advise_feedbak,"+

            "create_id,"+

            "create_time) values ("

+ "'"+ classify_time+ "',"

+ "'"+ area+ "',"

+ "'"+ type+ "',"

+ "'"+ keyword+ "',"

+ "'"+ information_feedbak+ "',"

+ "'"+ note1+ "',"

+ "'"+ note2+ "',"

+ "'"+ advise_feedbak+ "',"

+ "'"+ strUserId+ "',"

+ "GETDATE()) ";

            System.out.println(strInsertSql);

rs2.executeSql(strInsertSql);

       }

   }

}

if("".equals(msg)){

msg += "导入成功";

}else {

msg += "导入失败";

recordSetTrans.rollback();

}

}

} catch (Exception e) {

log.info("异常："+e);

}

       return msg;

 

   }

 

 /**

* 校验excle格式

* @param sheet

* @return

*/

private static int check1Header(HSSFSheet sheet) {

 

String cellValue ="";

for(int j=0;j<1;j++){//行

for(int i=0;i<8;i++){//列

HSSFRow row = sheet.getRow(0);

HSSFCell cell =row.getCell((short)i);

cellValue = cellToString(cell).trim();

if(j==0){

if(i==0){

if(!"月份".equals(cellValue)){return i;}

}

if(i==1){

if(!"地区".equals(cellValue)){return i;}

}

if(i==2){

if(!"分类".equals(cellValue)){return i;}

}

if(i==3){

if(!"关键字".equals(cellValue)){return i;}

}

if(i==4){

if(!"信息反馈".equals(cellValue)){return i;}

}

if(i==5){

if(!"备注1".equals(cellValue)){return i;}

}

if(i==6){

if(!"备注2".equals(cellValue)){return i;}

}

if(i==7){

if(!"建议-反馈".equals(cellValue)){return i;}

}

}

}

}

return -1;

}

 

/**

* 校验表头

* @param sheet

* @return

*/

private static int check2Header(HSSFSheet sheet) {

 

String cellValue ="";

for(int j=0;j<1;j++){//行

for(int i=0;i<6;i++){//列

HSSFRow row = sheet.getRow(0);

HSSFCell cell =row.getCell((short)i);

cellValue = cellToString(cell).trim();

if(j==0){

if(i==0){

if(!"客户名称".equals(cellValue)){return i;}

}

if(i==1){

if(!"日期".equals(cellValue)){return i;}

}

if(i==2){

if(!"上年采购".equals(cellValue)){return i;}

}

if(i==3){

if(!"今年采购".equals(cellValue)){return i;}

}

if(i==4){

if(!"信息反馈".equals(cellValue)){return i;}

}

if(i==5){

if(!"备注".equals(cellValue)){return i;}

}

 

}

}

}

return -1;

}

 

public String returnType(String str){

String result ="";

if("产品".equals(str)){

result ="1";

}else if("功能、性能".equals(str)){

result ="2";

}else if("市场".equals(str)){

result ="3";

}else if("体验".equals(str)){

result ="4";

}else if("质量".equals(str)){

result ="5";

}else if("其它".equals(str)){

result ="6";

}else{

result ="7";

}

 

return result;

 

}

4、模板

月份	地区	分类	关键字	信息反馈	备注1	备注2	建议-反馈
2015-10	福建	市场	第三方
2015-10	福建	市场	行业