java读取excel文件
<%@ page contentType="text/html; charset=gb2312"%>
<%@ page import="java.io.*,jxl.*,jxl.write.*,jxl.write.*,jxl.format.*"%>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@ page import="java.awt.*"%>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import="com.jspsmart.SmartFile"%>
<jsp:useBean id="common" class="com.teach.db.commonMethod" scope="page">
</jsp:useBean>
<%//实现EXCEL文件上传，上传之后把EXCEL文件中的内容导入SQL server数据库中
//用smartUpload组件把EXCEL文件上传
com.jspsmart.SmartUpload smartUpload = new com.jspsmart.SmartUpload(); //初始化smartUPload
smartUpload.initialize(pageContext);
smartUpload.service(request, response);
smartUpload.upload();
com.jspsmart.SmartFile file = smartUpload.getFiles().getFile(0); //得到上页输入的文件
//取中文表单参数
String newFileName = "test.xls";
//改名上传
file.saveAs("excel/" + newFileName, smartUpload.SAVE_VIRTUAL);
%>
<%!public String codeToString(String str) {//处理中文字符串的函数
String s = str;
try {
byte tempB[] = s.getBytes("ISO-8859-1");
s = new String(tempB);
return s;
} catch (Exception e) {
return s;
}
}
%>
<html>
<head>
<title></title>
</head>
<body>
<%//得到前页传过来的参数
String courseId=session.getAttribute("courseId").toString();
%>
<%String path = request.getRealPath("\\") + "excel" + "\\"
+ "test.xls";//Excel文件URL
InputStream is = new FileInputStream(path);//写入到FileInputStream
jxl.Workbook wb = Workbook.getWorkbook(is); //得到工作薄
jxl.Sheet st = wb.getSheet(0);//得到工作薄中的第一个工作表
int rsRows = st.getRows(); //得到excel的总行数
for (int i = 1; i < rsRows; i++) {
Cell cell0 = st.getCell(0, i);//得到工作表的第一个单元格,即A1
Cell cell1 = st.getCell(1, i);//得到工作表的第二个单元格,即A1
try {
String content0 = cell0.getContents();//getContents()将Cell中的字符转为字符串
int content1 = Integer.parseInt(cell1.getContents()); //得到条形码
//存入数据库
String sql = "insert into sy_score(courseId,scoreStudentName,score)"
+ "values("
+ courseId
+ ",''"
+ content0
+ "'',"
+ content1+")";
System.out.println(sql);
common.Del_Insert(sql); //执行SQL语句
推荐文章 |