將xls中的數(shù)據(jù)寫(xiě)入數(shù)據(jù)庫(kù)文章分類:Web前端Upload.jsp代碼
<script>
function callUpload() {
var fileValue = document.FileUpload.file1.value;
if (fileValue == "") {
alert("請(qǐng)選擇所需上傳的文件！");
return false;
}
showLayer.style.display = "inline";
//count()
FileUpload.submit();
}
</script>
<body>
<form name="FileUpload" method="post" action="uploads" enctype="multipart/form-data" >
<table border="0">
<tr>
<td nowrap>
<div align="left">導(dǎo)入的EXCEL文件(導(dǎo)入的明細(xì)會(huì)復(fù)蓋原有明細(xì)數(shù)據(jù))：</div>
</td>
</tr>
<tr>
<td nowrap>
<input type="file" class="mybutton" name="file1" size="50" style="border-style: solid; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px">
<input type="submit" class="mybutton" value="導(dǎo)入" name="shangchuan" onClick="return callUpload()" style="border-style: solid; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px" >
</td>
</tr>
</table>
<table width="100%">
<tr>
<td>
<hr width="100%">
</td>
</tr>
</table>
</form>
</body>
Upload.java代碼
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
final long MAX_SIZE = 3 * 1024 * 1024;// 設(shè)置上傳文件最大為 3M
String u_name="";
// 允許上傳的文件格式的列表
final String[] allowedExt = new String[] { "xls", "jpeg", "gif", "txt",
"doc", "docx", "mp3", "wma" };
response.setContentType("text/html");
// 設(shè)置字符編碼為UTF-8, 這樣支持漢字顯示
response.setCharacterEncoding("GBK");
//實(shí)例化RequestContext對(duì)象
RequestContext requestContext = new ServletRequestContext(request);
if(FileUpload.isMultipartContent(requestContext)){}
// 實(shí)例化一個(gè)硬盤(pán)文件工廠,用來(lái)配置上傳組件ServletFileUpload
DiskFileItemFactory dfif = new DiskFileItemFactory();
//上傳文件胡原始路徑
String realpath = this.getServletContext().getRealPath("/")+"ImagesUploadTemp" ;
// 設(shè)置存放臨時(shí)文件的目錄
dfif.setRepository(new File(realpath));
dfif.setSizeThreshold(4096);// 設(shè)置上傳文件時(shí)用于臨時(shí)存放文件的內(nèi)存大小,這里是4K.多于的部分將臨時(shí)存在硬盤(pán)
// 用以上工廠實(shí)例化上傳組件
ServletFileUpload sfu = new ServletFileUpload(dfif);
System.err.println(" reapath="+this.getServletContext().getRealPath("/")+"ImagesUploadTemp");
// 設(shè)置最大上傳尺寸
sfu.setSizeMax(MAX_SIZE);
PrintWriter out = response.getWriter();
// 從request得到 所有 上傳域的列表
List fileList = null;
try {
fileList = sfu.parseRequest(request);
} catch (FileUploadException e) {// 處理文件尺寸過(guò)大異常
e.printStackTrace();
if (e instanceof SizeLimitExceededException) {
out.println("文件尺寸超過(guò)規(guī)定大小:" + MAX_SIZE + "字節(jié)<p />");
out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>");
return;
}
//e.printStackTrace();
}
// 沒(méi)有文件上傳
if (fileList == null || fileList.size() == 0) {
out.println("文件大小不能為空，請(qǐng)選擇上傳文件<p />");
out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>");
return;
}
// 得到所有上傳的文件
Iterator fileItr = fileList.iterator();
// 循環(huán)處理所有文件
while (fileItr.hasNext()) {
FileItem fileItem = null;
String path = null;
long size = 0;
// 得到當(dāng)前文件
fileItem = (FileItem) fileItr.next();
// 忽略簡(jiǎn)單form字段而不是上傳域的文件域(<input type="text" />等)
if (fileItem == null || fileItem.isFormField()) {
continue;
}
// 得到文件的完整路徑
path = fileItem.getName();
path = new String(path.getBytes("ISO-8859-1"),"UTF-8");
System.out.println("完整路徑="+path);
// 得到文件的大小
size = fileItem.getSize();
if ("".equals(path) || size == 0) {
out.println("請(qǐng)選擇上傳文件<p />");
out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>");
return;
}
// 得到去除路徑的文件名
String t_name = path.substring(path.lastIndexOf("\\") + 1);
// 得到文件的擴(kuò)展名(無(wú)擴(kuò)展名時(shí)將得到全名)
String t_ext = t_name.substring(t_name.lastIndexOf(".") + 1);
// 拒絕接受規(guī)定文件格式之外的文件類型
int allowFlag = 0;
int allowedExtCount = allowedExt.length;
for (; allowFlag < allowedExtCount; allowFlag++) {
if (allowedExt[allowFlag].equals(t_ext))
break;
}
if (allowFlag == allowedExtCount) {
out.println("請(qǐng)上傳以下類型的文件<p />");
for (allowFlag = 0; allowFlag < allowedExtCount; allowFlag++)
out.println("*." + allowedExt[allowFlag]
+ " ");
out.println("<p /><a href=\"excelInsert.action\" target=\"_top\">返回</a>");
return;
}
long now = System.currentTimeMillis();
// 根據(jù)系統(tǒng)時(shí)間生成上傳后保存的文件名
String prefix = String.valueOf(now);
// 保存的最終文件完整路徑,保存在web根目錄下的ImagesUploaded目錄下
u_name = this.getServletContext().getRealPath("/")+"ImagesUploaded"+"\\"
+ prefix + "." + t_ext;
System.out.println("文件上傳后的路徑！u_name="+u_name);
try {
// 保存文件
fileItem.write(new File(u_name));
out.println("文件上傳成功. 已保存為: " + prefix + "." + t_ext
+ " 文件大小: " + size + "字節(jié)<p />");
out.println("<a href=\"excelInsert.action\" target=\"_top\">繼續(xù)上傳</a>");
} catch (Exception e) {
e.printStackTrace();
}
}
u_name = u_name.replace("\\", "/");
System.out.println("u_name =="+u_name);
insert(u_name,request,response);
/******************這個(gè)你把它去掉*****************/
//主要是為了導(dǎo)入成功以后，將它顯示到頁(yè)面上的方法
List list_lxmc = exam.getAllStlxMc();
for(int i=0;i<list_lxmc.size();i++){
// System.out.println("類型名稱"+list_lxmc.size()+";"+list_lxmc.get(i));
}
request.setAttribute("list_lxmc", list_lxmc);
List list_allSel = exam.getSelThemeTypeNamesobr_ones();
// System.out.println(list_allSel+"..............");
request.setAttribute("list_allSel", list_allSel);
/***************************/
request.getRequestDispatcher("/admin/thememanage/excelInsert.jsp").forward(request, response);
}
public void insert(String path,HttpServletRequest request,HttpServletResponse response){
try{
//////////// 讀取 u_name 文件 并保存///////////
System.out.println("開(kāi)始時(shí)間="+new Date());
java.io.File file = new java.io.File(path);
java.io.InputStream inStream = new java.io.FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(inStream);
//int sheetNum = wb.getNumberOfSheets();
int sheetNum = 1;
ThemeBean tb = null;
ArrayList list = new ArrayList();
//為了獲得32隨機(jī)數(shù)主鍵
UUIDGenerator uuid=new UUIDGenerator();
for (int i = 0; i < sheetNum; i++){
HSSFSheet childSheet = wb.getSheetAt(i);
int rowNum = childSheet.getLastRowNum() + 1;
// HSSFRow rowline = childSheet.getRow(rowNum);
//這里我取不到列數(shù)的，所以將它寫(xiě)死了
int cellNum =6;
// System.out.println("列數(shù)"+rowline.getLastCellNum());
for (int j = 2; j < rowNum; j++){
tb = new ThemeBean();
HSSFRow row = childSheet.getRow(j);
if (row == null)
continue;
String value = "";
for (int k = 0; k < cellNum; k++){
HSSFCell cell = row.getCell((short) k);
// System.out.println(cell.getCellType()+"===type");
if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING){
continue;
}
//循環(huán)取到xls中的值
value = String.valueOf(cell.getStringCellValue().trim());
//System.out.println("value="+a);
if (value == null)
value = "";
//對(duì)取到值 中有特殊符號(hào)進(jìn)行處理（這個(gè)方法可選）
value = value.replace("'", "");
value = value.replace("‘", "");
value = value.replace("’", "");
switch(k){
case 0: tb.setSTTM(value);
case 1: tb.setSTKSX1(value);
case 2: tb.setSTKSX2(value);
case 3: tb.setSTKSX3(value);
case 4: tb.setSTKSX4(value);
case 5: tb.setRIGHTRESULT(value);
}
}
tb.setSTZT("未使用");
tb.setBZ("備注就省略了");
tb.setSTXXID(uuid.getNextValue("STXXID"));
tb.setSTBH(uuid.getNextValue("STBH"));
//將值放到對(duì)象中在 放到list中,為了方便插入數(shù)據(jù)庫(kù)
list.add(tb);
}
}
try{
ThemeAction action = new ThemeAction();
//判斷這個(gè)xls有沒(méi)有被從復(fù)導(dǎo)入
String ret = action.upload(list);
if(ret!="ok"){
request.setAttribute("excel", ret);
}
System.out.println("結(jié)束時(shí)間="+new Date());
//我有4000多條數(shù)據(jù)大概花了1分鐘左右的，讀xls只花了2秒鐘的，插入數(shù)據(jù)庫(kù)時(shí)間要多點(diǎn)，可能是我的方法寫(xiě)的不好，還請(qǐng)理解，你們也可以用自己更好的方法來(lái)代替
}catch(Exception ex){
ex.printStackTrace();
System.out.println("有異常的");
return;
}
//關(guān)閉文件流
inStream.close();
//刪除臨時(shí)文件
file.delete();
}catch(Exception e){
e.printStackTrace();
}
}
|
|
|
