import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.opensymphony.xwork.Preparable;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;
import com.opensymphony.webwork.ServletActionContext;
import com.opensymphony.webwork.interceptor.ServletResponseAware;
import com.ytincl.scdp.common.util.FormatUtilJB;
import org.apache.commons.beanutils.LazyDynaBean;
import com.ytincl.drms.common.importfromfile.web.action.Excelutil;
import com.ytincl.drms.common.importfromfile.web.action.judgeStringUtil;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.BeansException;
import org.springframework.beans.MutablePropertyValues;
import org.springframework.beans.PropertyValue;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
import uk.ltd.getahead.dwr.WebContextFactory;
import com.opensymphony.webwork.interceptor.ServletRequestAware;
import javax.servlet.http.HttpServletRequest;
import com.opensymphony.xwork.util.XWorkList;
/*
* 传入一个列表数据,将其写入到EXCEL中 2008.01.09 赵祖龙
*
*/
/*
* 传入一个列表数据,将其写入到EXCEL中 2008.01.09 赵祖龙
* 加入对于多行超过65535的处理 2008.01.17 赵祖龙
*/
public static void writeExcel(String filename, List writeValue, HttpServletResponse response) throws IOException {
String Disposition = "attachment;filename=" + URLEncoder.encode(filename, "utf8");
System.out.println("Dispositin"+Disposition);
response.setContentType("application/ms-excel; charset=gb2312");
response.setHeader("Content-Disposition", Disposition);
HSSFWorkbook wb = new HSSFWorkbook();
if (writeValue == null) {
return;
}
List listTitle = new ArrayList(); //保存当前的EXCEL题头
listTitle = (List) writeValue.get(0);//得到第一行的题头
List listWriteContent = new ArrayList();
for (int icount = 1; icount < writeValue.size(); icount++) {
listWriteContent.add((List) writeValue.get(icount));
}
// System.out.println("得到的未带题头的行数"+listWriteContent.size()+"带题头的行数"+writeValue.size());
// 判断当前总行数是否超过EXCEL一页的总行数65536
int iMaxLines = 65535;
int iCurrentLines = listWriteContent.size();//要写的内容总行数
int iPageCount = iCurrentLines / iMaxLines;//总页数
int ileftValue = iCurrentLines % iMaxLines;//尾页要写的行数
if (iCurrentLines == 0) {
return;
}
//页数大于1并且尾页行大于0
if (iPageCount > 1 && ileftValue > 0) {
iPageCount = iPageCount + 1;
}
if(iPageCount==0 && ileftValue>0){
//进行按SHEET页写数据
for (int p = 0; p <= iPageCount; p++) {
HSSFRow row = null;
HSSFCell cell = null;
HSSFSheet sheet = wb.createSheet("sheet" + Integer.toString(p));
int iStartRow = 0;
int iEndRow = 0;
//确定当前页开始写的行首及行尾号
if (p == 0) {
iStartRow = 0;
if (iPageCount == 0) {
iEndRow = iCurrentLines - 1;
} else {
iEndRow = (p + 1) * iMaxLines - 1;
}
} else {
iStartRow = p * iMaxLines;
if (p == iPageCount - 1) {
iEndRow = iCurrentLines - 1;
} else {
iEndRow = (p + 1) * iMaxLines - 1;
}
}
// System.out.println("开始行"+iStartRow+"结束行"+iEndRow);
List listPageValue = new ArrayList();//保存每页的数据
listPageValue.add(listTitle);
for (int i = iStartRow; i <= iEndRow; i++) {
if (listWriteContent.get(i) == null) {
continue;
}
listPageValue.add((List) listWriteContent.get(i));
}
// System.out.println("iStartRow" + iStartRow + "iEndRow" + iEndRow + "p" + p);
int iRow = 0; //定义每页开始写入的行号
for (int i = 0; i < listPageValue.size(); i++) {
if (listPageValue.get(i) == null) {
continue;
}
//得到要写的内容行数据
List listValue = (List) listPageValue.get(i);
row = sheet.createRow((short) i);
wb.getSheet(Integer.toString(p));//得到要写数据当前的页
for (int n = 0; n < listValue.size(); n++) {
cell = row.createCell((short) n);
if ( i ==1) {
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.BLUE.index);
cell.setCellStyle(style);
// System.out.println("设定第一行的背景颜色");
}
cell.setEncoding(cell.ENCODING_UTF_16);
// System.out.println(listValue.get(n));
cell.setCellType(cell.CELL_TYPE_STRING);
if (listValue.get(n) == null) {
listValue.set(n, "");
}
cell.setCellValue(listValue.get(n).toString());
// System.out.println("现在要写的数据"+listValue.get(n).toString());
}
}
}
}else{
//进行按SHEET页写数据
for (int p = 0; p < iPageCount; p++) {
HSSFRow row = null;
HSSFCell cell = null;
HSSFSheet sheet = wb.createSheet("sheet" + Integer.toString(p));
int iStartRow = 0;
int iEndRow = 0;
//确定当前页开始写的行首及行尾号
if (p == 0) {
iStartRow = 0;
if (iPageCount == 0) {
iEndRow = iCurrentLines - 1;
} else {
iEndRow = (p + 1) * iMaxLines - 1;
}
} else {
iStartRow = p * iMaxLines;
if (p == iPageCount - 1) {
iEndRow = iCurrentLines - 1;
} else {
iEndRow = (p + 1) * iMaxLines - 1;
}
}
// System.out.println("开始行"+iStartRow+"结束行"+iEndRow);
List listPageValue = new ArrayList();//保存每页的数据
listPageValue.add(listTitle);
for (int i = iStartRow; i <= iEndRow; i++) {
if (listWriteContent.get(i) == null) {
continue;
}
listPageValue.add((List) listWriteContent.get(i));
}
// System.out.println("iStartRow" + iStartRow + "iEndRow" + iEndRow + "p" + p);
int iRow = 0; //定义每页开始写入的行号
for (int i = 0; i < listPageValue.size(); i++) {
if (listPageValue.get(i) == null) {
continue;
}
//得到要写的内容行数据
List listValue = (List) listPageValue.get(i);
row = sheet.createRow((short) i);
wb.getSheet(Integer.toString(p));//得到要写数据当前的页
for (int n = 0; n < listValue.size(); n++) {
cell = row.createCell((short) n);
if ( i ==1) {
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.BLUE.index);
cell.setCellStyle(style);
// System.out.println("设定第一行的背景颜色");
}
cell.setEncoding(cell.ENCODING_UTF_16);
// System.out.println(listValue.get(n));
cell.setCellType(cell.CELL_TYPE_STRING);
if (listValue.get(n) == null) {
listValue.set(n, "");
}
cell.setCellValue(listValue.get(n).toString());
// System.out.println("现在要写的数据"+listValue.get(n).toString());
}
}
}
}
// System.out.println("总行数iCurrentLines" + iCurrentLines + "总页数iPageCount" + iPageCount);
ServletOutputStream outstream = response.getOutputStream();
wb.write(outstream);
outstream.flush();
}
/*
* 组织数据后调用写入EXCEL文件的操作writeExcel 2008.01.09 赵祖龙
*
*/
public static void outputExcel(String filename, List printList, HttpServletResponse response) throws IOException {
List rowValue = new ArrayList();
List titleValue = new ArrayList();
//定义行标题
titleValue.add("年度");
titleValue.add("省局编号");
titleValue.add("省局名称");
titleValue.add("省内分发单元代码");
titleValue.add("省内分发单元名称");
rowValue.add(titleValue);
//得到行内容
for (int i = 0; i < printList.size(); i++) {
DistSjSnffdy fbkResult = (DistSjSnffdy) printList.get(i);
List listValue = new ArrayList();
int iYxn = fbkResult.getYxn();
listValue.add(Integer.toString(iYxn));
String strSjid = fbkResult.getSjid();
listValue.add(strSjid);
String strSjmc = fbkResult.getSjmc();
listValue.add(strSjmc);
String strSnffdy = fbkResult.getSnffdyid();
listValue.add(strSnffdy);
String strSnffdymc = fbkResult.getSnffdymc();
listValue.add(strSnffdymc);
rowValue.add(listValue);
}
writeExcel( filename, rowValue, response);
}
public List getExportList() {
return exportList;
}
public void setExportList(List exportList) {
this.exportList = exportList;
}
public String exportToExcell() {
try {
if (exportList == null|| exportList.size() == 0) {
addScdpActionMessage("当前界面没有导出的记录!");
}else{
outputExcel("省内分发单元.xls", this.exportList, this.response);
}
//导出操作完成后,界面进行刷新
listView();
} catch (IOException e) {
System.out.println("导出文件未成功" + e.toString());
} catch (Exception e) {
e.printStackTrace();
}
return SUCCESS;
}
/*
* 完成将EXCE导入到数据库的操作 2008.03.21 赵祖龙
*
*/
public String importExceltoDb() throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
// 声明各列的头
// 年度、省内分发单元代码、省内分发单元名称、省局代码、省局名称
// 新开发时,此处需要修改----------
this.beanprops = new String[] { "yxn", "sjid", "sjmc", "snffdyid", "snffdymc"};
UserInfo userInfor = (UserInfo) findUserInfo();
String userId = userInfor.getUserId();
// 将当前Excel打开,得到其中数据保存到list中
List listValue = null;
try {
listValue = parseExceltoList();
} catch (IOException e) {
System.out.println("读文件出错" + e.getMessage());
addScdpActionMessage("读取Excel文件进出错!");
return INPUT;
}
// 完成字段映射,得到可以批次保存到后台数据库的list
List listBd = Bean2Bean(listValue);
// 列表插入数据库
if(listBd!=null){
snffdySvc.insertBatch(listBd);
}
System.out.println("batchSn="+batchSn);
System.out.println("userId="+userId);
//调后台数据库的后续处理
snffdySvc.callProcAfterInsert(batchSn, "SNFFDY",userId);
if(this.listErrMsgInfor==null){
}else{
String strErrMsg=null;
for (int i=0;i<listErrMsgInfor.size();i++){
strErrMsg = strErrMsg+""+listErrMsgInfor.get(i);
}
addScdpActionMessage(strErrMsg);
return INPUT;
}
System.out.println("importExceltoDb----8--------------");
addScdpActionMessage("保存成功");
return INPUT;
}
/*
* 将两个JavaBean进行映射,新模块,此处需要修改,需要加入业务判断 2008.03.21 赵祖龙
*
*/
public List Bean2Bean(List listValue) {
List errList = null;
java.util.List result = new java.util.ArrayList();
java.util.List ErrList = new java.util.ArrayList();
LazyDynaBean dbean;
UserInfo userInfor = (UserInfo) findUserInfo();
String userId = userInfor.getUserId();
judgeStringUtil judgement = new judgeStringUtil();
System.out.println("Bean2Bean----------1-----------listValue.size()="+listValue.size());
for (int ii = 0; ii < listValue.size(); ii++) {
DistSjSnffdy ffgxbd = new DistSjSnffdy();
String strErrMsg = null;
dbean = null;
dbean = (LazyDynaBean) listValue.get(ii);
ffgxbd.setSnffdyid((String) dbean.get("snffdyid"));
System.out.println("snffdyid1111111="+dbean.get("snffdyid"));
// 判断省内分发单元代码是否为数字
if (!judgement.judgeStrIsNumber((String) dbean.get("snffdyid"))) {
strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "分发单元代码不是数字!" + "***";
}
// 判断分发单元代码是否为8位
if (!judgement.judgeStrLength((String) dbean.get("snffdyid"), 8)) {
strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "分发单元代码不是8位" + "***";
}
System.out.println("yxn111888811111="+(String) dbean.get("yxn"));
String strYXN =(String) dbean.get("yxn");
ffgxbd.setYxn(Integer.valueOf(strYXN.trim()));
System.out.println("yxn2222222222222="+strYXN);
// 判断变动通知单号是否为数字
if (!judgement.judgeStrIsNumber((String) dbean.get("yxn"))) {
strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "有效年不是数字!" + "***";
}
// 判断变动通知单号是否为12位
if (!judgement.judgeStrLength((String) dbean.get("yxn"), 4)) {
strErrMsg = strErrMsg + "第" + ii + 1 + "行" + "有效年不是4位" + "***";
}
ffgxbd.setImpuser(userId);
ffgxbd.setBatchsn(batchSn);
if (strErrMsg == null) {
result.add(ffgxbd);
} else {
ErrList.add(strErrMsg);
}
}
listErrMsgInfor = ErrList;
return result;
}
/*
* 将Excel解析成List 2008.03.20 赵祖龙
*
*/
public List parseExceltoList() throws IOException {
System.out.println("parseExceltoList----1----------");
List listValue = null;
String path = getFileUploadPath();
System.out.println("parseExceltoList----2----------");
// 为了多文件上传
for (int i = 0; i < doc.length; i++) {
System.out.println("parseExceltoList----3----------");
// 如果存在上传的文件
if (doc[i] != null) {
// 打开输入流
FileInputStream input = new FileInputStream(doc[i]);
// 此入添加将输入流转成list操作
if (excelutil == null) {
excelutil = new Excelutil();
}
HSSFWorkbook wb = new HSSFWorkbook(input);
listValue = excelutil.parseSheettoList(wb.getSheetAt(0), beanprops);
// 关闭流
input.close();
}
}
return listValue;
}
/**
* 获得文件上传路径
*
* @return
*/
private String getFileUploadPath() {
String path = request.getSession().getServletContext().getInitParameter("fileUploadPath");
System.out.println("getFileUploadPath----1----------path="+path);
if ((path == null) || path.trim().length() == 0) {
path = this.request.getSession().getServletContext().getRealPath("//WEB-INF//upload//");
}
if (!path.endsWith(File.separator)) {
path = path + File.separator;
}
File file = new File(path);
if (!file.exists()) {
file.mkdirs();
}
return path;
}
public void setServletRequest(HttpServletRequest argrequest) {
setRequest(argrequest);
}
public HttpServletRequest getRequest() {
return request;
}
转http://blog.csdn.net/traceofsun/article/details/5842414