<%--
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.*" %>
<%@ page import="java.sql.*" %>
<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();
%>
<table>
<thead>
<tr>
<th>
id
</th>
<th>
company
</th>
<th>
name
</th>
</tr>
</thead>
<tbody>
<%
String connectionUrl = "jdbc:sqlserver://DESKTOP-U9122RH;" +
"databaseName=ryqy;integratedSecurity=true;";
// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
out.println("test sql connection <br>");
try {
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
// Create and execute an SQL statement that returns some data.
String querySql = "select * from account";
stmt = con.createStatement();
rs = stmt.executeQuery(querySql);
// Iterate through the data in the result set and display it.
int isExists=0;
while (rs.next()) {
isExists=1;
System.out.println(rs.getString(1) + " " + rs.getString(2));
String id=rs.getString(1);
String company=rs.getString(1);
String name =rs.getString(3);
out.println("<tr><td>"+id+"</td><td>"+company+"</td><td>"+name+"</td></tr>");
}
out.println("test sql connection success <br>");
if(isExists==1)
{
out.println("已经存在不用再创建！ <br>");
}else
{
out.println("不存在 <br>");
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
out.println("test sql connection fault <br>");
}
finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
%>
</tbody>
</table>
</body>
</html>
