JSP 用poi 读取Excel 

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.poifs.filesystem.*" %>
<%@ page import="org.apache.poi.ss.usermodel.CellType" %>


<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Generate SQL </title>
</head>
<body>
<h1>TemplateName <%= request.getParameter("TemplateName")%> </h1>
<p>

        
        
        <%
            String templateName = request.getParameter("TemplateName"); 
            String srcTemplateName=templateName+"_src";
            String trgTemplateName=templateName+"_trg";
            String primaryKeyDefine="PK nvarchar(150)";
            String PrimaryKey ="PK";
            String FilterCondition="a.PK=b.PK";
            String SystemIdentity="WFS";
            String srcQuery="";
            String trgQuery="";
            String SQL ="INSERT INTO [dbo].[BusinessLogicCmpDefine]  ([TemplateName],[SrcTemplateName],[TrgTemplateName],[PrimaryKeyDefine],[PrimaryKey] ,[FilterCondition] ,[SystemIdentity])";
           
            SQL=SQL+" <br> VALUES ('" +templateName+ "','"+srcTemplateName+"','"+trgTemplateName+"','"+primaryKeyDefine+"','"+PrimaryKey+"','"+FilterCondition+"','"+SystemIdentity+"')";
            
            
            out.println(SQL);
           
         


          
        %>
       
</p>
<h2>View <%=srcTemplateName%> </h2>
<p>
        Create view [dbo].[<%=srcTemplateName%>] 
        <br>
        as 
        <br>
        <%
        
        try (FileInputStream fileIn = new FileInputStream("c:\\demo.xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
        
        
         
            for (int k = 1; k <= sheet.getLastRowNum(); k++) {
                HSSFRow row = sheet.getRow(k);
                if(row !=null){
                    HSSFCell cell = row.getCell(1);
                    if(cell==null){
                        out.println("no data<br>");
                    }else{
                        String value =cell.getStringCellValue();

                      

                       
                        if(value.equals(templateName)){
                           
                          
                            HSSFCell srcQueryCell = row.getCell(5);
                            srcQuery=srcQueryCell.getStringCellValue().replaceAll("\r|\n","<br>");;

                            HSSFCell trgQueryCell = row.getCell(6);
                            trgQuery=trgQueryCell.getStringCellValue().replaceAll("\r|\n","<br>");
                            out.println(srcQuery+"<br> ");
                         
                        }
                        
        
                    }
                }
            }
        }
        %>
       
     
</p>
<h2>View <%=trgTemplateName%></h2>
<p>
        Create view [dbo].[<%=trgTemplateName%>] 
        <br>
        as 
        <br>
        <%out.println(trgQuery+"<br> ");%>
</p>

<h2>Excute SQL</h2>
<p>
       exec  USP_CompareR1AndR3Data '<%=templateName%>' ,'WFS'
</p>
<p>
        今天的日期是: <%= (new java.util.Date()).toLocaleString()%>
       
</p>
</body>
</html>

 上面是今天下午刚学的代码，还不能兼容xlsx的格式，晚上学习的代码可以兼容xlsx的格式。下载的poi中有弟三方类库，需要放到tomcat的lib目录中才，并且使用下面的代码。

<%--
  Created by IntelliJ IDEA.
  User: hellohongfu
  Date: 2017/12/21
  Time: 0:16
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="java.io.*,java.util.*"  %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.poifs.filesystem.*" %>
<%@ page import="org.apache.poi.ss.usermodel.*" %>
<%@ page import="org.apache.poi.xssf.usermodel.*" %>
<html>
<head>
    <title>excel demo</title>
</head>
<body>

<%
    InputStream inp = new FileInputStream("c:\\demo.xlsx");
    //InputStream inp = new FileInputStream("workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    for (int k = 1; k <= sheet.getLastRowNum(); k++){
        Row row=sheet.getRow(k);
        Cell cell = row.getCell(1);
        if (cell != null){
            String value =cell.getStringCellValue();
            out.println("cell value:"+value+"<br>");
        }

    }

    Row row = sheet.getRow(2);
    Cell cell = row.getCell(3);



    out.println(cell.getStringCellValue());
    if (cell == null)
        cell = row.createCell(3);
    cell.setCellType(CellType.STRING);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
%>
</body>
</html>

 

posted on 2017-12-20 18:19  HelloHongfu  阅读(498)  评论(0)  编辑  收藏  举报

导航