<%@ 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>