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 
{
/**//*
* 把數(shù)據(jù)庫中的字段導(dǎo)入到Excel ，并生成Excel文檔
**/
public ActionForward getDownload(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) throws Exception {
Form fm = (Form) actionForm;
// Excel 文件存放在服務(wù)器的相對(duì)路徑下
String outputFile = request.getRealPath("/tmp/Excel.xls");
try {
// 創(chuàng)建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel 工作簿中建一工作表
HSSFSheet sheet = workbook.createSheet("Sheet1");
// 設(shè)置單元格格式(文本)
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
// 在索引0的位置創(chuàng)建行（第一行）
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);
// 在單元格中輸入數(shù)據(jù)
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();// 查詢結(jié)果存入rs
connection.commit();// 執(zhí)行SQL
while (rs.next()) {
//設(shè)置j行從第二行開始
int j = 1;
row = sheet.createRow((short) j);
//設(shè)置i列從第二列開始
for (int i = 1; i <= 3; i++) {
HSSFCell cell = row.createCell((short) (i-1));
// 設(shè)置單元格格式
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", "創(chuàng)建文件失敗！");
e.printStackTrace();
}
// 刪除路徑下同名的Excel 文件
File path = new File(outputFile);
path.delete();
// 新建一輸出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相應(yīng)的Excel 工作簿存盤
workbook.write(fOut);
// 操作結(jié)束，關(guān)閉文件
fOut.flush();
fOut.close();
//該處如果Excel過大會(huì)影響效率，誰有好的想法可以提出來參考（不過從頁面下載完后就會(huì)清空）
request.getSession().setAttribute("Download", outputFile);
} catch (Exception ioexception) {
request.setAttribute("message", "創(chuàng)建文件失敗！");
return actionMapping.findForward("outJSP");
}
return actionMapping.findForward("outJSP");
}
/**//*
* 從Excel文件中讀取數(shù)據(jù)，并導(dǎo)入到數(shù)據(jù)庫中
**/
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; //導(dǎo)入記數(shù)
String name = null;
String sex = null;
String age = null;
try {
//通過得到的文件輸入流inputstream創(chuàng)建一個(gè)HSSFWordbook對(duì)象
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);//第一個(gè)工作表
HSSFRow hssfrow = hssfsheet.getRow(0);//第一行
//遍歷該表格中所有的工作表，i表示工作表的數(shù)量 getNumberOfSheets表示工作表的總數(shù)
for (int i = 0; i < hssfworkbook.getNumberOfSheets(); i++) {
hssfsheet = hssfworkbook.getSheetAt(i);
//遍歷該行所有的行,j表示行數(shù) getPhysicalNumberOfRows行的總數(shù)
for (int j = 1; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
hssfrow = hssfsheet.getRow(j);
//判斷是否還存在需要導(dǎo)入的數(shù)據(jù)
if (hssfrow == null) {
System.out.println("這里已沒有數(shù)據(jù)，在第"+i+"列,第"+j+"行");
break;
}
/** *//**將EXCEL中的第 j 行，第一列的值插入到實(shí)例中*/
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表格中的數(shù)據(jù)類型為字符串型
else {
name = hssfrow.getCell((short) 0).getStringCellValue().trim();
}
/** *//**將EXCEL中的第 j 行，第二列的值插入到實(shí)例中*/
//姓名
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表格中的數(shù)據(jù)類型為字符串型
else {
sex = hssfrow.getCell((short) 1).getStringCellValue().trim();
}
/** *//**將EXCEL中的第 j 行，第三列的值插入到實(shí)例中*/
//姓名
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表格中的數(shù)據(jù)類型為字符串型
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);
}
//導(dǎo)入成功加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="導(dǎo) 入" onclick="upload(this.form)" class="buttonGray">
</html:form>
</html>
<script language="javascript">
function upload(obj)
{
if(confirm("您現(xiàn)在選擇的是XXX,您確定要導(dǎo)入嗎？"))
{
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>
posted on 2008-02-19 11:31
Jarry 閱讀(4581)
評(píng)論(2) 編輯 收藏 所屬分類:
POI-Excel/Word