web中使用POI的例子
struts1.x的例子，struts2.x可以参考自己修改
1.action的写法
import
java.io.
*
;
import
java.sql.
*
;
import
java.util.ArrayList;
import
javax.servlet.http.HttpServletRequest;
import
javax.servlet.http.HttpServletResponse;
import
org.apache.poi.hssf.usermodel.
*
;
import
org.apache.struts.action.
*
;
import
org.apache.struts.upload.FormFile;
import
org.apache.commons.beanutils.BeanUtils;
public
class
Action
{
/**//*
* 把数据库中的字段导入到Excel ，并生成Excel文档
**/
public ActionForward getDownload(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) throws Exception
{
Form fm = (Form) actionForm;
// Excel 文件存放在服务器的相对路径下
String outputFile = request.getRealPath("/tmp/Excel.xls");
try
{
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel 工作簿中建一工作表
HSSFSheet sheet = workbook.createSheet("Sheet1");
// 设置单元格格式(文本)
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
// 在索引0的位置创建行（第一行）
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell1 = row.createCell((short) 0);// 第一列
HSSFCell cell2 = row.createCell((short) 1);
HSSFCell cell3 = row.createCell((short) 2);
// 定义单元格为字符串类型
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
// 在单元格中输入数据
cell1.setCellValue("姓名");
cell2.setCellValue("性别");
cell3.setCellValue("年龄");
Connection connection = session.connection();
String sql = "Select t.name, t.sex, t.age from table t where t.sex = ?";
try
{
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, fm.getSex());// 传入查询条件
ResultSet rs = ps.executeQuery();// 查询结果存入rs
connection.commit();// 执行SQL
while (rs.next())
{
//设置j行从第二行开始
int j = 1;
row = sheet.createRow((short) j);
//设置i列从第二列开始
for (int i = 1; i <= 3; i++)
{
HSSFCell cell = row.createCell((short) (i-1));
// 设置单元格格式
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getString(i));
}
j++;
}
request.setAttribute("message", "文件生成成功！");
} catch (SQLException e)
{
request.setAttribute("message", "创建文件失败！");
e.printStackTrace();
}
// 删除路径下同名的Excel 文件
File path = new File(outputFile);
path.delete();
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
// 操作结束，关闭文件
fOut.flush();
fOut.close();
//该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）
request.getSession().setAttribute("Download", outputFile);
} catch (Exception ioexception)
{
request.setAttribute("message", "创建文件失败！");
return actionMapping.findForward("outJSP");
}
return actionMapping.findForward("outJSP");
}
/**//*
* 从Excel文件中读取数据，并导入到数据库中
**/
public ActionForward getUpload(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) throws Exception
{
// 获取excel 文件
Form fm = (Form) actionForm;
FormFile formfile = fm.getUploadfile();
InputStream inputstream = formfile.getInputStream();
fm.clear();// 清空
Session session = HibernateSession.currentSession();
ArrayList list = new ArrayList();
int input = 0; //导入记数
String name = null;
String sex = null;
String age = null;
try
{
//通过得到的文件输入流inputstream创建一个HSSFWordbook对象
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);//第一个工作表
HSSFRow hssfrow = hssfsheet.getRow(0);//第一行
//遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
for (int i = 0; i < hssfworkbook.getNumberOfSheets(); i++)
{
hssfsheet = hssfworkbook.getSheetAt(i);
//遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 1; j < hssfsheet.getPhysicalNumberOfRows(); j++)
{
hssfrow = hssfsheet.getRow(j);
//判断是否还存在需要导入的数据
if (hssfrow == null)
{
System.out.println("这里已没有数据，在第"+i+"列,第"+j+"行");
break;
}
/** *//**将EXCEL中的第 j 行，第一列的值插入到实例中*/
if (hssfrow.getCell((short) 0) == null)
{
name = "";
} else if (hssfrow.getCell((short) 0).getCellType() == 0)
{
name = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else
{
name = hssfrow.getCell((short) 0).getStringCellValue().trim();
}
/** *//**将EXCEL中的第 j 行，第二列的值插入到实例中*/
//姓名
if(hssfrow.getCell((short) 1) == null)
{
sex = "";
} else if(hssfrow.getCell((short) 1).getCellType() == 0)
{
sex = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else
{
sex = hssfrow.getCell((short) 1).getStringCellValue().trim();
}
/** *//**将EXCEL中的第 j 行，第三列的值插入到实例中*/
//姓名
if(hssfrow.getCell((short) 1) == null)
{
age = "";
} else if(hssfrow.getCell((short) 1).getCellType() == 0)
{
age = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else
{
age = hssfrow.getCell((short) 1).getStringCellValue().trim();
}
name = name.trim();
sex = sex.toUpperCase();
if (name.equals(""))
{
error.setName(name);
error.setMessage("姓名不能为空");
list.add(error);
continue;
} else
{
fm.setName(name);
fm.setSex(sex);
fm.setAge(age);
session.save(fm);
}
//导入成功加1
input++;
}
}
session.saveObjs(list.toArray());
} catch ()
{
}
}
}
2.Form的写法
import org.apache.struts.action.ActionForm;
import
org.apache.struts.upload.FormFile;
public
class
Form
extends
ActionForm
{
// 上传的文件
private FormFile _flddo;
public void setUploadfile(FormFile formfile)
{
_flddo = formfile;
}
public FormFile getUploadfile()
{
return _flddo;
}
public void clear()
{
_flddo = null;
}
}
3.上传页面Upload.jsp
<%
@ page contentType="text/html; charset=GBK" language="java"
%>
<%
@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html"
%>
<
html
>
< html:form
action
="/Action.do?method=getUpload"
method
="POST"
enctype
="multipart/form-data"
>
< html:file
property
="uploadfile"
size
="80%"
/>
< input
type
="button"
value
="导 入"
onclick
="upload(this.form)"
class
="buttonGray"
>
</ html:form
>
</
html
>
<
script
language
="javascript"
>
function upload(obj)
{
if(confirm("您现在选择的是XXX,您确定要导入吗？"))
{
var uploadfile = document.all.uploadfile.value;
if((null == uploadfile) ||( "" == uploadfile))
{
alert("上传文件没有指定！");
return false;
}
obj.action = '<html:rewrite page="/Action.do?method=getUpload"/>';
obj.submit();
}
}
</
script
>
4.下载页面Download.jsp
<%
@ page contentType="text/html; charset=GBK"
%>
<%
@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html"
%>
<%
@ taglib uri="http://jakarta.apache.org/struts/tags-bean" prefix="bean"
%>
<%
//获取下载文件
String download = (String) request.getSession().getAttribute("Download");
//清空文件
request.getSession().removeAttribute("Download");
%>
<
html
>
下传文件 < a href
="<%=download %>"
name
="下载"
>
下载
</
a
>
</
html
>标签：web,Excel,org,HSSFCell,例子,POI,apache,import,public From： https://blog.51cto.com/u_16065168/6493876