package com.servlet;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.locks.ReentrantLock;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import com.hsun.HLog;
import com.utils.UpdateMerInterface;
public class UploadImportExcel extends HttpServlet {
String upDirPathName;
String upDirDatePathName;
File tmpDir = null;
File saveDir = null;
static int sUploadFileIndex = 1000000;
private static final ReentrantLock m_lock = new ReentrantLock();
public static int getNextUploadFileIndex() {
m_lock.lock();
int iretindex = sUploadFileIndex;
sUploadFileIndex += 1;
if (sUploadFileIndex > 9000000)
sUploadFileIndex = 1000000;
m_lock.unlock();
return iretindex;
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
try {
if (ServletFileUpload.isMultipartContent(request)) {
String UserNameId = request.getParameter("UserNameId");
String mtype = request.getParameter("mtype");
HLog.m_messagelog.debug("UserNameId:" + UserNameId);
DiskFileItemFactory dff = new DiskFileItemFactory();
dff.setRepository(this.tmpDir);
dff.setSizeThreshold(1024000);
ServletFileUpload sfu = new ServletFileUpload(dff);
sfu.setFileSizeMax(100000000L);
sfu.setSizeMax(3000000000L);
FileItemIterator fii = sfu.getItemIterator(request);
while (fii.hasNext()) {
FileItemStream fis = fii.next();
if ((!fis.isFormField()) && (fis.getName().length() > 0)) {
String fileName = fis.getName();
String fieldName = fis.getFieldName();
if (fieldName.length() < 5)
continue;
String subfileName = fieldName.substring(0, 4);
System.out.println("333333:" + subfileName);
if (!subfileName.equals("file"))
continue;
String strFileIndexId = fieldName.substring(4);
HLog.m_messagelog.debug("fileName:" + fileName
+ ",subfilename:" + subfileName
+ ",strFileIndex:" + strFileIndexId);
HLog.m_messagelog
.debug("UploadQualifiFile:dopost,filename:"
+ fileName + ",fieldName:" + fieldName);
BufferedInputStream in = new BufferedInputStream(
fis.openStream());
Date now = new Date();
// SimpleDateFormat sdateformat = new SimpleDateFormat(
// "yyyyMMddHHmmss");
// String strNowDate = sdateformat.format(now);
String newFileName = UserNameId + ".xls";
BufferedOutputStream out = new BufferedOutputStream(
new FileOutputStream(new File(
this.upDirPathName + newFileName)));
Streams.copy(in, out, true);
System.out.println("1111:" + this.upDirPathName
+ newFileName);
UpdateMerInterface.readExcel(this.upDirPathName + newFileName);
}
}
response.setContentType("text/html;charset=UTF-8");
String retString = "<html><body><h1>通道切换成功</h1></body></html>";
response.getWriter().println(retString);
}
} catch (Exception e) {
e.printStackTrace();
HLog.m_messagelog.error("UploadQualifiFile:", e);
response.setContentType("text/html;charset=UTF-8");
response.getWriter()
.println(
"<html><body><input type=\"button\" value=\"上传失败\"/></html>");
}
}
public void init() throws ServletException {
super.init();
String rootpathname = getServletContext().getRealPath("/");
String tmpPath = rootpathname + "/tmpdir/";
String savePath = rootpathname + "batchexcel";
this.upDirPathName = savePath;
Date now = new Date();
SimpleDateFormat sdateformat = new SimpleDateFormat("yyyyMMdd");
String strNowDate = sdateformat.format(now);
this.upDirDatePathName = strNowDate;
this.upDirPathName = (this.upDirPathName + "/");
this.tmpDir = new File(tmpPath);
this.saveDir = new File(savePath);
if (!this.tmpDir.isDirectory())
this.tmpDir.mkdir();
if (!this.saveDir.isDirectory()) {
this.saveDir.mkdir();
this.saveDir = new File(this.upDirPathName);
this.saveDir.mkdir();
} else {
this.saveDir = new File(this.upDirPathName);
if (!this.saveDir.isDirectory())
this.saveDir.mkdir();
}
}
}
package com.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import DBConnect.DBConnectManager;
import DBConnect.DBConnection;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class UpdateMerInterface {
//读取Excel文件
public static void readExcel(String file)
throws BiffException, IOException{
List<String> list = new ArrayList<String>();
Workbook rwb = null;
Cell cell = null;
//创建输入流
InputStream stream = new FileInputStream(file);
//获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
//行数
for(int i=0; i<sheet.getRows(); i++){
//获取第i行,第j列的值 (本次为全部获取第i行第一列的值)
cell = sheet.getCell(0,i);
String str = cell.getContents();
list.add(str);
}
if(list!=null&&list.size()>0){
updateMerInterface(list);
}else{
System.out.println("表数据为空");
}
}
public static void updateMerInterface(List<String> list){
// 切换通道到 27 | 000022 | new--SYX | 1
String sql = "update merchant set up_interface_no = '000022' where merchant_no in(";
for(int i=0;i<list.size();i++){
//判断商户号长度
if(list.get(i)!=null&&list.get(i).length()==15){
sql+=list.get(i)+",";
}
}
sql = sql.substring(0,sql.length()-1)+")";
List tmplist_par = new ArrayList();
DBConnection tmpconn = DBConnectManager.getDbConn();
int iret = tmpconn.updateSQL(sql, tmplist_par);
if (iret < 0){
DBConnectManager.releaseDbConn(tmpconn);
}else{
DBConnectManager.freeDbConn(tmpconn);
}
}
}
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.locks.ReentrantLock;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import com.hsun.HLog;
import com.utils.UpdateMerInterface;
public class UploadImportExcel extends HttpServlet {
String upDirPathName;
String upDirDatePathName;
File tmpDir = null;
File saveDir = null;
static int sUploadFileIndex = 1000000;
private static final ReentrantLock m_lock = new ReentrantLock();
public static int getNextUploadFileIndex() {
m_lock.lock();
int iretindex = sUploadFileIndex;
sUploadFileIndex += 1;
if (sUploadFileIndex > 9000000)
sUploadFileIndex = 1000000;
m_lock.unlock();
return iretindex;
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
try {
if (ServletFileUpload.isMultipartContent(request)) {
String UserNameId = request.getParameter("UserNameId");
String mtype = request.getParameter("mtype");
HLog.m_messagelog.debug("UserNameId:" + UserNameId);
DiskFileItemFactory dff = new DiskFileItemFactory();
dff.setRepository(this.tmpDir);
dff.setSizeThreshold(1024000);
ServletFileUpload sfu = new ServletFileUpload(dff);
sfu.setFileSizeMax(100000000L);
sfu.setSizeMax(3000000000L);
FileItemIterator fii = sfu.getItemIterator(request);
while (fii.hasNext()) {
FileItemStream fis = fii.next();
if ((!fis.isFormField()) && (fis.getName().length() > 0)) {
String fileName = fis.getName();
String fieldName = fis.getFieldName();
if (fieldName.length() < 5)
continue;
String subfileName = fieldName.substring(0, 4);
System.out.println("333333:" + subfileName);
if (!subfileName.equals("file"))
continue;
String strFileIndexId = fieldName.substring(4);
HLog.m_messagelog.debug("fileName:" + fileName
+ ",subfilename:" + subfileName
+ ",strFileIndex:" + strFileIndexId);
HLog.m_messagelog
.debug("UploadQualifiFile:dopost,filename:"
+ fileName + ",fieldName:" + fieldName);
BufferedInputStream in = new BufferedInputStream(
fis.openStream());
Date now = new Date();
// SimpleDateFormat sdateformat = new SimpleDateFormat(
// "yyyyMMddHHmmss");
// String strNowDate = sdateformat.format(now);
String newFileName = UserNameId + ".xls";
BufferedOutputStream out = new BufferedOutputStream(
new FileOutputStream(new File(
this.upDirPathName + newFileName)));
Streams.copy(in, out, true);
System.out.println("1111:" + this.upDirPathName
+ newFileName);
UpdateMerInterface.readExcel(this.upDirPathName + newFileName);
}
}
response.setContentType("text/html;charset=UTF-8");
String retString = "<html><body><h1>通道切换成功</h1></body></html>";
response.getWriter().println(retString);
}
} catch (Exception e) {
e.printStackTrace();
HLog.m_messagelog.error("UploadQualifiFile:", e);
response.setContentType("text/html;charset=UTF-8");
response.getWriter()
.println(
"<html><body><input type=\"button\" value=\"上传失败\"/></html>");
}
}
public void init() throws ServletException {
super.init();
String rootpathname = getServletContext().getRealPath("/");
String tmpPath = rootpathname + "/tmpdir/";
String savePath = rootpathname + "batchexcel";
this.upDirPathName = savePath;
Date now = new Date();
SimpleDateFormat sdateformat = new SimpleDateFormat("yyyyMMdd");
String strNowDate = sdateformat.format(now);
this.upDirDatePathName = strNowDate;
this.upDirPathName = (this.upDirPathName + "/");
this.tmpDir = new File(tmpPath);
this.saveDir = new File(savePath);
if (!this.tmpDir.isDirectory())
this.tmpDir.mkdir();
if (!this.saveDir.isDirectory()) {
this.saveDir.mkdir();
this.saveDir = new File(this.upDirPathName);
this.saveDir.mkdir();
} else {
this.saveDir = new File(this.upDirPathName);
if (!this.saveDir.isDirectory())
this.saveDir.mkdir();
}
}
}
package com.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import DBConnect.DBConnectManager;
import DBConnect.DBConnection;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class UpdateMerInterface {
//读取Excel文件
public static void readExcel(String file)
throws BiffException, IOException{
List<String> list = new ArrayList<String>();
Workbook rwb = null;
Cell cell = null;
//创建输入流
InputStream stream = new FileInputStream(file);
//获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
//行数
for(int i=0; i<sheet.getRows(); i++){
//获取第i行,第j列的值 (本次为全部获取第i行第一列的值)
cell = sheet.getCell(0,i);
String str = cell.getContents();
list.add(str);
}
if(list!=null&&list.size()>0){
updateMerInterface(list);
}else{
System.out.println("表数据为空");
}
}
public static void updateMerInterface(List<String> list){
// 切换通道到 27 | 000022 | new--SYX | 1
String sql = "update merchant set up_interface_no = '000022' where merchant_no in(";
for(int i=0;i<list.size();i++){
//判断商户号长度
if(list.get(i)!=null&&list.get(i).length()==15){
sql+=list.get(i)+",";
}
}
sql = sql.substring(0,sql.length()-1)+")";
List tmplist_par = new ArrayList();
DBConnection tmpconn = DBConnectManager.getDbConn();
int iret = tmpconn.updateSQL(sql, tmplist_par);
if (iret < 0){
DBConnectManager.releaseDbConn(tmpconn);
}else{
DBConnectManager.freeDbConn(tmpconn);
}
}
}