JSP部分内容
<base
href="${pageContext.request.scheme }://${pageContext.request.serverName }:${pageContext.request.serverPort }${pageContext.request.contextPath }/">
<body>
<div align="left">
<form action="${pageContext.request.contextPath }/uf" method = "post" enctype="multipart/form-data">
<h3>EXCEL上传</h3>
<lable>1、选择文件，文件名必须是 模板.xls</lable><br>
<input align="left" type="file" id="file" name="excelfile" required="required"/>
<br/><br/>
<lable>2、选好文件后，点击 导入数据库 按钮</lable><br>
<input align="left" type="submit" id="submit" name="submit" value="导入数据库" />
</form>
</div>
</body>
JAVA部分
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.io.FileNotFoundException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
//文件上传用到的jar包
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
//注意导的包
import org.apache.commons.io.FilenameUtils;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOCase;
import org.apache.jasper.tagplugins.jstl.core.Out;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
/**
* Servlet implementation class uf
*/
@WebServlet("/uf")
public class uf extends HttpServlet {
private static final long serialVersionUID = 1L;
private static String fileName, filePath, excelFile;
private static int r;
/**
* @see HttpServlet#HttpServlet()
*/
public uf() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8");
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
PrintWriter out = response.getWriter();
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
DiskFileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload sfu = new ServletFileUpload(factory);
//上传到服务器的文件路径
filePath = request.getContextPath();
//跳转到提示页
//response.sendRedirect("warn.jsp");
try {
List<FileItem> fileItems = new ArrayList<FileItem>();
fileItems = sfu.parseRequest(request);
for (FileItem item : fileItems) {
// 在这个里面去判断fileItem对象中属于哪一种类型
if (item.isFormField()) {
// 说明是表单元素
// 进行表单元素处理方法
} else {
//上传文件到服务器
processUploadField(item, filePath,request,response);
}
}
} catch (FileUploadException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@SuppressWarnings("null")
private void processUploadField(FileItem item, String real,HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
InputStream in;
try {
in = item.getInputStream();
fileName = item.getName();
if (fileName != null) {
fileName = FilenameUtils.getName(fileName);
// 判断模板名称是否正确
if (fileName.equals("模板.xls")) {
// 在web项目中创建一个上传文件的路径
File storeDirectory = new File(filePath);
if (!storeDirectory.exists()) {
storeDirectory.mkdirs();
}
FileOutputStream os = new FileOutputStream(new File(storeDirectory, fileName));
int len = -1;
byte[] b = new byte[1024];
while ((len = in.read(b)) != -1) {
os.write(b, 0, len);
}
in.close();
os.close();
//文件所在完 路径
excelFile = filePath + "/" + fileName;
// 向SQL表中导入数据
readExcel(excelFile);
// 跳转到check页查看导入结果
response.sendRedirect("check.jsp");
} else {
//
System.out.println(fileName);
//request.setAttribute("fn", fileName);
response.sendRedirect("warn.jsp");
System.out.println("上传的文件名错误！");
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param Excelfile
* 读取Excel文件并向SQL表插入数据
* **/
public static void readExcel(String Excelfile) {
Date date = new Date();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SS");
try {
// 创建excel文件
File file = new File(Excelfile);
// 获取系统文档
POIFSFileSystem fspoi = new POIFSFileSystem(file);
// 创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook(fspoi);
// 创建工作表对象
// HSSFSheet sheet = workbook.getSheet("sheet1");
HSSFSheet sheet = workbook.getSheetAt(0);
// 得到Excel表格
for (r = 2; r < sheet.getLastRowNum() + 1; r++) {
HSSFRow row = sheet.getRow(r);
// 得到Excel工作表指定行的单元格
String insertSQL = "insert into YFZX_KaoQin (number,name,dkdate,qdtime,qttime,dept,reason,kqmonth,kqyear,INSERTDATE)"
+ "VALUES(" + row.getCell(0) + "," + "\'" + row.getCell(1).toString() + "\'" + "," + "\'"
+ row.getCell(2) + "\'" + "," + "\'" + row.getCell(3).toString() + "\'" + "," + "\'"
+ row.getCell(4).toString() + "\'" + "," + "\'" + row.getCell(5).toString() + "\'" + "," + "\'"
+ row.getCell(6).toString() + "\'" + "," + row.getCell(7) + "," + row.getCell(8) + "," + "\'"
+ df.format(date) + "\'" + ")";
System.out.println("正在向数据库插入第" + (r - 1) + "行数据：" + insertSQL);
String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String DBURL = "jdbc:sqlserver://IP:1433;DatabaseName=DBName";
String DBUSER = "DBUser";
String PASSWORD = "DBPassWord";
Connection cn = null;
java.sql.Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(DBDRIVER);
cn = (Connection) DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
stmt = cn.createStatement();
boolean sql = stmt.execute(insertSQL);
if (sql) {
rs = stmt.getResultSet();
java.sql.ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.println();
}
} else {
}
} catch (Exception ex) {
System.out.println(ex.getMessage());
ex.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (cn != null) {
try {
cn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
1782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
