1.excel导入数据
导入时候注意数字类型和字符类型的区别，数字类型要在excel中设置数字类型，对应数据库中也应该建立对应类型
<%@ page import="com.guanri.databa.*,java.util.*,com.guanri.util.*,com.jspsmart.upload.*" contentType="text/html; chart=GBK" %>
<%@ page import="java.util.*,java.io.*,org.apache.poi.poifs.filesystem.*,org.apache.poi.hssf.urmodel.*" %>
<jsp:include page="/admin/checkLogin.jsp"/>
<%
free people
String result = "";
String contracId = "";
String projectname = "";
double projectscale = 0;
String projecttype="";
String projectconunit="";
double designtime = 0;
String designpha = "";
double completedtime = 0;
String participants = "";
String positionofpro = "";
String dutiesas = "";
int inum = 0;
int unum = 0;
int rnum = 0;
int snum = 0;
String rmstr = "";
Table tb = new Table();
Vector vc = null;
Hashtable rows = new Hashtable();
String sql = "";
职称英语准考证打印时间
Method().equalsIgnoreCa("POST")) {
String sdir = "/download/xlsdir/"; //文件保存目录
String Property("file.parator");
String ssdir = filep+"download"+filep+"xlsdir"+filep;
Calendar c = Instance();
String fname = c.getTimeInMillis()+".xls";
String sfname = sdir + fname;
String ss = "xls"; //支持的文件后缀名
try{
SmartUpload su = new SmartUpload();
su.initialize(pageContext);
su.tAllowedFilesList(ss);
su.upload();
com.jspsmart.upload.File file = su.getFiles().getFile(0);
if(!file.isMissing()){
file.saveAs(sfname);
// System.out.RealPath("/")+ssdir+fname );
FileInputStream finput = new RealPath("/")+ssdir+fname );
//设定FileINputStream读取Excel档
POIFSFileSystem fs = new POIFSFileSystem( finput );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
//读取第一个工作表，宣告其为sheet
finput.clo();
HSSFRow row=null;
//宣告一列
HSSFCell cell=null;
//宣告一个储存格
short i=0;
short y=0;
//以巢状迴圈读取所有储存格资料
System.out.println("LastRowNum()="+LastRowNum());
for (i=1;i<=LastRowNum();i++)
{
Row(i);
for (y=1;y<=LastCellNum();y++)
{
Cell(y);
switch(y){
ca 1: //合同号
if(cell!=null){
contracId=(StringCellValue()).trim();
}
break;
ca 2: //项目名称
if(cell!=null){
projectname=(StringCellValue()).trim();
}
break;
ca 3: //项目规模
if(cell!=null){
NumericCellValue();
}
break;
ca 4: //项目类型
if(cell!=null){
projecttype=(StringCellValue()).trim();
}
break;
ca 5: //项目建设单位
if(cell!=null){
projectconunit=(StringCellValue()).trim();
}
break;
功夫熊猫3中英文字幕 ca 6: //设计时间
if(cell!=null){oh captain my captain
NumericCellValue();
}
break;
ca 7: //设计阶段
if(cell!=null){
designpha=(StringCellValue()).trim();
}
break;
ca 8: //竣工时间
if(cell!=null){
NumericCellValue();
}
break;
ca 9: //参与人姓名
if(cell!=null){
participants=(StringCellValue()).trim();
}
break;
ca 10: //担任项目职务
if(cell!=null){
positionofpro=(StringCellValue()).trim();
}
break;
ca 11: //担任职责
if(cell!=null){
dutiesas=(StringCellValue()).trim();
}
break;
}
}
sql = "inrt into hy_pro_briefinfo (contracId,projectname,projectscale,projecttype,projectconunit,designtime,designpha,completedtime,participants,positionofpro,dutiesas) ";
sql+="values ('"+contracId+"','"+projectname+"','"+projectscale+"','"+projecttype+"','"+projectconunit+"','"+(int)designtime+"','"+designpha+"','"+(int)completedtime+"','"+participants+"','"+positionofpro+"','"+dutiesas+"')";
//System.out.println(sql);
snum = tb.update(sql);
if(snum<=0){
++rnum;
rmstr = rmstr + ",";
}el{
++inum;
}
tb.commit();
tb.tCommit(true); 环球教育网校
// System.out.println("result="+result);
//System.out.println("snum="+snum +"inum="+inum);
}
result = "共成功上传 "+(inum+unum)+" 条记录，其中新增 "+inum+" 条,更新 "+unum+" 条；失败 "+rnum+" 条（ "+rmstr+" ）";
// System.out.println(result);
java.io.File f = new java.io.RealPath("/")+ssdir+fname);
f.delete();
}el{
result = "上传文件失败";
}
}catch(Exception ex){
ex.printStackTrace();
java.io.File f = new java.io.RealPath("/")+ssdir+fname);
//f.delete();
// result = ex.getMessage();
if(result.indexOf("1010")>0){
result = "文件格式不对！只支持"+ss+"格式文件";
}
if("You cannot get a numeric value from a String bad cell".equals(result)){
result = "格式错误：在数字单元格中存在非数字！第"+(unum+inum+1)+"条记录出错";
}
}
//finally{
// respon.ndRedirect("pro_per_info.jsp");
// }
}
%>
<jsp:include page="/admin/BodyStart" />
beware Ext.MessageBox.alert("提示",'<%=result %>');
tTimeout("window.location.href='pro_per_info.jsp'", 1000);
<jsp:include page="/admin/BodyEnd" />
2.excel导出数据
导出的时候注意中文编码的问题
&();
respon.tContentType("application/vnd.ms-excel");
必须按照先后顺序书写
<%@ page language="java" import="com.guanri.databa.*,java.util.*,com.guanri.util.*,com.jspsmart.upload.*" contentType="text/html; chart=gb2312" pageEncoding="gb2312"%>
<%@ page import="java.util.*,java.io.*,org.apache.poi.hssf.urmodel.*" %>
<%
//respon.tContentType("application/mxcel;chart=utf-8" );
//respon.tContentType("text/html;chart=utf-8");
&();
respon.tContentType("application/vnd.ms-excel");
request.tCharacterEncoding("gb2312");
String workSheetName = "pro_info.xls";
respon.tHeader("Content-disposition", "attachment; filename="+workSheetName);
//WriteExcel we=new WriteExcel();
//System.out.println("文件生成");
//we.getExcel("steet1",OutputStream());
HSSFWorkbook workbook = new HSSFWorkbook(); //产生工作簿对象
HSSFSheet sheet = ateSheet(); //产生工作表对象
sheet.tDefaultRowHeight((short) 10);
sheet.tDefaultColumnWidth((short) 20);
sheet.tColumnWidth((short) 4, (short) 10000);// 单位
workbook.tSheetName(0,"firstSheet");
// 设置字体
HSSFFont font = ateFont();
// font.tFontHeightInPoints(( short ) 20 ); // 字体高度
// font.tColor(HSSFFont.COLOR_RED); // 字体颜色
font.tFontName( " 黑体 " ); // 字体
font.tBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 宽度
// font.tItalic( true ); // 是否使用斜体
// font.tStrikeout(true); // 是否使用划线
// 设置单元格类型
HSSFCellStyle cellStyle = ateCellStyle();
cellStyle.tFont(font);
cellStyle.tAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居左边
cellStyle.tWrapText( true );
HSSFRow row = ateRow((short)0);//产生第一行
HSSFCell cell = ateCell((short) 0);//产生第一个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("本公司项目统计信息");
row = ateRow((short) 1);//产生第二行
cell = ateCell((short) 0);//产生第一个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("序号");
cell = ateCell((short) 1);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("姓名");
cell = ateCell((short) 2);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("项目名称");
cell = ateCell((short) 3);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("项目类型");
cell = ateCell((short) 4);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("项目规模");
cell = ateCell((short) 5);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("担任项目职务");
cell = ateCell((short) 6);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("设计时间");
cell = ateCell((short) 7);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("设计阶段");
cell = ateCell((short) 8);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("项目建设单位");
cell = ateCell((short) 9);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("竣工时间");
cell = ateCell((short) 10);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("专项设计");
cell = ateCell((short) 11);//产生第二个单元格
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
cell.tCellValue("直属专业领导");
Table tb = new Table();
Vector vc = null;
Hashtable rows=new Hashtable();
String sql = "";
sql = "lect * from hy_pro_briefinfo where 1=1";
sql = sql + " order by participants";
vc = tb.getContent(sql);
// System.out.println(sql);
// String result = "";
int order=0;
String contracId = "";
String projectname = "";
String projectscale = "";
String projecttype="";
String projectconunit="";
double designtime = 0;
easytalking String designpha = "";
double completedtime = 0;
String participants = "";
String positionofpro = "";
String dutiesas = "";
String leader="";
int iRow=2;
String info="";
String pername="";
int num=1;
for(int i=0;i<vc.size();i++){
rows = ((i);
order=i+1;
participants=(("participants");
if(!pername.equals(participants)){
pername=participants;
num=1;
}el{
num++;
}
// System.out.println("participants="+participants);
projectname=(("projectname");
projecttype=(("projecttype");
// System.out.println("projectname="+projectname);
positionofpro=(("positionofpro");
// System.out.println("positionofpro="+positionofpro);
designtime=((("designtime")).intValue();
projectscale=(("projectscale")+"";
// System.out.println("designtime="+designtime);
designpha=(("designpha");
// System.out.println("designpha="+designpha);
免费在线翻译 projectconunit=(("projectconunit");
// System.out.println("projectconunit="+projectconunit);
completedtime=((("completedtime")).intValue();
// System.out.println("completedtime="+completedtime);
dutiesas=(("dutiesas");
// System.out.println("dutiesas="+dutiesas);
row= ateRow((short)iRow);;
for(int j=1;j<=11;j++)
{
cell = ateCell((short)(j-1));
cell.tCellStyle(cellStyle); // 设置单元格样式
cell.tCellType(HSSFCell.CELL_TYPE_STRING);
cell.tEncoding(HSSFCell.ENCODING_UTF_16);
switch(j-1){
ca 0: //编号
info=num+"";
break;
ca 1: //姓名
info=participants+"";
break;
ca 2: //项目名称
info=projectname+"";
break;
ca 3: //项目类型
info=projecttype+"";
break;
ca 4: //面积
info="建筑面积："+projectscale+"平方米";
break;
ca 5: //担任项目职务
info=positionofpro+"";
break;
ca 6: //设计时间
info=designtime+"";
info=info.substring(0,info.indexOf("."));
break;
homie是什么意思 ca 7: //设计阶段
info=designpha+"";
break;
ca 8: //施工单位
info=projectconunit+"";
break;
ca 9: //竣工时间
info=completedtime+"";
info=info.substring(0,info.indexOf("."));
if("0".equals(info)){
info="";
}
break;
ca 10: //专项设计
info=dutiesas+"";
break; //直属专业领导
ca 11:
info=leader+"";
长春外语培训学校 break;
}
//System.out.println("info="+info);
cell.tCellValue(info);
}
iRow++;
}
OutputStream OutputStream();
output.flush();
workbook.OutputStream());
output.clo();
%>
