excle导入导出

Struts的jar包就不说了~~~！！！

工程.jar包结构：

poi-3.7-20101029.jar
poi-examples-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar

有些jar包的用不着的、但是我为了以防万一全部都倒进来了！

我的导入页面(OutExcel.jsp)：

view plaincopy to clipboardprint?
01.<%-- 记住这里需要设置enctype="multipart/form-data"--%>
02.<s:form action="userInfo" method="post" enctype="multipart/form-data">
03. 导入Excel文件：<s:file name="excelFile"></s:file> <br/>
04. <s:submit value="导入"></s:submit>
05. </s:form>
<%-- 记住这里需要设置enctype="multipart/form-data"--%>
<s:form action="userInfo" method="post" enctype="multipart/form-data">
导入Excel文件：<s:file name="excelFile"></s:file> <br/>
<s:submit value="导入"></s:submit>
</s:form>

Struts2.xml配置:

view plaincopy to clipboardprint?
01.<constant name="struts.i18n.encoding" value="UTF-8"></constant>
02. <constant name="struts.ui.theme" value="simple"></constant>
03.
04.<package name="excelOutPut" extends="struts-default">
05. <action name="userInfo" class="userAction">
06. <result>/excel/InputExcel.jsp</result>
07. </action>
08. <action name="outPut" class="outAction">
09. <result>/excel/InputExcel.jsp</result>
10. </action>
11. </package>
<constant name="struts.i18n.encoding" value="UTF-8"></constant>
<constant name="struts.ui.theme" value="simple"></constant>

<package name="excelOutPut" extends="struts-default">
<action name="userInfo" class="userAction">
<result>/excel/InputExcel.jsp</result>
</action>
<action name="outPut" class="outAction">
<result>/excel/InputExcel.jsp</result>
</action>
</package>

Userinfo类：

view plaincopy to clipboardprint?
01.package com.boxun.bean;
02.
03.import java.math.BigDecimal;
04.
05./**
06. * Userinfo entity. @author MyEclipse Persistence Tools
07. */
08.
09.public class Userinfo implements java.io.Serializable {
10.
11. // Fields
12.
13. private Integer id;
14. private String name;
15. private String pass;
16. private String lastname;
17. private String addres;
18. private String remark;
19.
20. // Constructors
21.
22. /** default constructor */
23. public Userinfo() {
24. }
25.
26. /** full constructor */
27. public Userinfo(String name, String pass, String lastname, String addres,
28. String remark) {
29. this.name = name;
30. this.pass = pass;
31. this.lastname = lastname;
32. this.addres = addres;
33. this.remark = remark;
34. }
35.
36. // Property accessors
37.
38. public Integer getId() {
39. return this.id;
40. }
41.
42. public void setId(Integer id) {
43. this.id = id;
44. }
45.
46. public String getName() {
47. return this.name;
48. }
49.
50. public void setName(String name) {
51. this.name = name;
52. }
53.
54. public String getPass() {
55. return this.pass;
56. }
57.
58. public void setPass(String pass) {
59. this.pass = pass;
60. }
61.
62. public String getLastname() {
63. return this.lastname;
64. }
65.
66. public void setLastname(String lastname) {
67. this.lastname = lastname;
68. }
69.
70. public String getAddres() {
71. return this.addres;
72. }
73.
74. public void setAddres(String addres) {
75. this.addres = addres;
76. }
77.
78. public String getRemark() {
79. return this.remark;
80. }
81.
82. public void setRemark(String remark) {
83. this.remark = remark;
84. }
85.
86.}
package com.boxun.bean;

import java.math.BigDecimal;

/**
* Userinfo entity. @author MyEclipse Persistence Tools
*/

public class Userinfo implements java.io.Serializable {

// Fields

private Integer id;
private String name;
private String pass;
private String lastname;
private String addres;
private String remark;

// Constructors

/** default constructor */
public Userinfo() {
}

/** full constructor */
public Userinfo(String name, String pass, String lastname, String addres,
String remark) {
this.name = name;
this.pass = pass;
this.lastname = lastname;
this.addres = addres;
this.remark = remark;
}

// Property accessors

public Integer getId() {
return this.id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}

public String getPass() {
return this.pass;
}

public void setPass(String pass) {
this.pass = pass;
}

public String getLastname() {
return this.lastname;
}

public void setLastname(String lastname) {
this.lastname = lastname;
}

public String getAddres() {
return this.addres;
}

public void setAddres(String addres) {
this.addres = addres;
}

public String getRemark() {
return this.remark;
}

public void setRemark(String remark) {
this.remark = remark;
}

}

ExcelWorkSheet类:

view plaincopy to clipboardprint?
01.package com.boxun.bean;
02.
03.import java.util.ArrayList;
04.
05.import java.util.List;
06.
07.import org.apache.poi.hssf.record.formula.functions.T;
08.
09.public class ExcelWorkSheet<T> {
10. private String sheetName;
11. private List<T> data = new ArrayList<T>(); //数据行
12. private List<String> columns; //列名
13. public String getSheetName() {
14. return sheetName;
15. }
16. public void setSheetName(String sheetName) {
17. this.sheetName = sheetName;
18. }
19. public List<T> getData() {
20. return data;
21. }
22. public void setData(List<T> data) {
23. this.data = data;
24. }
25. public List<String> getColumns() {
26. return columns;
27. }
28. public void setColumns(List<String> columns) {
29. this.columns = columns;
30. }
31.
32.}
package com.boxun.bean;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.record.formula.functions.T;

public class ExcelWorkSheet<T> {
private String sheetName;
private List<T> data = new ArrayList<T>(); //数据行
private List<String> columns; //列名
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
public List<String> getColumns() {
return columns;
}
public void setColumns(List<String> columns) {
this.columns = columns;
}

}

导入的Action类：

view plaincopy to clipboardprint?
01.package com.boxun.action;
02.
03.import java.io.File;
04.
05.
06.import java.io.FileInputStream;
07.import java.io.IOException;
08.import java.io.InputStream;
09.import java.util.ArrayList;
10.import java.util.Iterator;
11.import java.util.List;
12.
13.import org.apache.poi.hssf.usermodel.HSSFWorkbook;
14.import org.apache.poi.ss.usermodel.Cell;
15.import org.apache.poi.ss.usermodel.Row;
16.import org.apache.poi.ss.usermodel.Sheet;
17.import org.apache.poi.ss.usermodel.Workbook;
18.import org.apache.poi.xssf.usermodel.XSSFWorkbook;
19.
20.import com.boxun.bean.ExcelWorkSheet;
21.import com.boxun.bean.Userinfo;
22.import com.boxun.biz.IUserBiz;
23.import com.opensymphony.xwork2.ActionSupport;
24.
25.public class UserAction extends ActionSupport {
26. private IUserBiz userBiz;
27. public void setUserBiz(IUserBiz userBiz) {
28. this.userBiz = userBiz;
29. }
30.
31. private File excelFile; //上传的文件
32.
33. private String excelFileFileName; //保存原始文件名
34.
35. //将Excel文件解析完毕后信息存放到这个对象中
36. private ExcelWorkSheet<Userinfo> excelWorkSheet;
37.
38. public File getExcelFile() {
39. return excelFile;
40. }
41.
42. public void setExcelFile(File excelFile) {
43. this.excelFile = excelFile;
44. }
45.
46. public String getExcelFileFileName() {
47. return excelFileFileName;
48. }
49.
50. public void setExcelFileFileName(String excelFileFileName) {
51. this.excelFileFileName = excelFileFileName;
52. }
53.
54. public ExcelWorkSheet<Userinfo> getExcelWorkSheet() {
55. return excelWorkSheet;
56. }
57.
58. public void setExcelWorkSheet(ExcelWorkSheet<Userinfo> excelWorkSheet) {
59. this.excelWorkSheet = excelWorkSheet;
60. }
61.
62.
63.
64. //判断文件类型
65. public Workbook createWorkBook(InputStream is) throws IOException{
66. if(excelFileFileName.toLowerCase().endsWith("xls")){
67. return new HSSFWorkbook(is);
68. }
69. if(excelFileFileName.toLowerCase().endsWith("xlsx")){
70. return new XSSFWorkbook(is);
71. }
72. return null;
73. }
74.
75. public String execute() throws Exception{
76. Workbook book = createWorkBook(new FileInputStream(excelFile));
77. //book.getNumberOfSheets(); 判断Excel文件有多少个sheet
78. Sheet sheet = book.getSheetAt(0);
79. excelWorkSheet = new ExcelWorkSheet<Userinfo>();
80. //保存工作单名称
81. Row firstRow = sheet.getRow(0);
82. Iterator<Cell> iterator = firstRow.iterator();
83.
84. //保存列名
85. List<String> cellNames = new ArrayList<String>();
86. while (iterator.hasNext()) {
87. cellNames.add(iterator.next().getStringCellValue());
88. }
89. excelWorkSheet.setColumns(cellNames);
90. for (int i = 1; i <= sheet.getLastRowNum(); i++) {
91. Row ros = sheet.getRow(i);
92. Userinfo user = new Userinfo();
93. user.setId((int)ros.getCell(0).getNumericCellValue());
94. user.setName(ros.getCell(1).getStringCellValue());
95. user.setPass(ros.getCell(2).getStringCellValue());
96. user.setLastname(ros.getCell(3).getStringCellValue());
97. user.setAddres(ros.getCell(4).getStringCellValue());
98. user.setRemark(ros.getCell(5).getStringCellValue());
99. excelWorkSheet.getData().add(user);
100. }
101. for (int i = 0; i < excelWorkSheet.getData().size(); i++) {
102. Userinfo info = excelWorkSheet.getData().get(i);
103. System.out.println(info.getLastname());
104. }
105. return SUCCESS;
106. }
107.
108.
109.
110.
111.}
package com.boxun.action;

import java.io.File;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.boxun.bean.ExcelWorkSheet;
import com.boxun.bean.Userinfo;
import com.boxun.biz.IUserBiz;
import com.opensymphony.xwork2.ActionSupport;

public class UserAction extends ActionSupport {
private IUserBiz userBiz;
public void setUserBiz(IUserBiz userBiz) {
this.userBiz = userBiz;
}

private File excelFile; //上传的文件

private String excelFileFileName; //保存原始文件名

//将Excel文件解析完毕后信息存放到这个对象中
private ExcelWorkSheet<Userinfo> excelWorkSheet;

public File getExcelFile() {
return excelFile;
}

public void setExcelFile(File excelFile) {
this.excelFile = excelFile;
}

public String getExcelFileFileName() {
return excelFileFileName;
}

public void setExcelFileFileName(String excelFileFileName) {
this.excelFileFileName = excelFileFileName;
}

public ExcelWorkSheet<Userinfo> getExcelWorkSheet() {
return excelWorkSheet;
}

public void setExcelWorkSheet(ExcelWorkSheet<Userinfo> excelWorkSheet) {
this.excelWorkSheet = excelWorkSheet;
}

//判断文件类型
public Workbook createWorkBook(InputStream is) throws IOException{
if(excelFileFileName.toLowerCase().endsWith("xls")){
return new HSSFWorkbook(is);
}
if(excelFileFileName.toLowerCase().endsWith("xlsx")){
return new XSSFWorkbook(is);
}
return null;
}

public String execute() throws Exception{
Workbook book = createWorkBook(new FileInputStream(excelFile));
//book.getNumberOfSheets(); 判断Excel文件有多少个sheet
Sheet sheet = book.getSheetAt(0);
excelWorkSheet = new ExcelWorkSheet<Userinfo>();
//保存工作单名称
Row firstRow = sheet.getRow(0);
Iterator<Cell> iterator = firstRow.iterator();

//保存列名
List<String> cellNames = new ArrayList<String>();
while (iterator.hasNext()) {
cellNames.add(iterator.next().getStringCellValue());
}
excelWorkSheet.setColumns(cellNames);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row ros = sheet.getRow(i);
Userinfo user = new Userinfo();
user.setId((int)ros.getCell(0).getNumericCellValue());
user.setName(ros.getCell(1).getStringCellValue());
user.setPass(ros.getCell(2).getStringCellValue());
user.setLastname(ros.getCell(3).getStringCellValue());
user.setAddres(ros.getCell(4).getStringCellValue());
user.setRemark(ros.getCell(5).getStringCellValue());
excelWorkSheet.getData().add(user);
}
for (int i = 0; i < excelWorkSheet.getData().size(); i++) {
Userinfo info = excelWorkSheet.getData().get(i);
System.out.println(info.getLastname());
}
return SUCCESS;
}




}

显示导入的数据页面(InputExcel.jsp)：

view plaincopy to clipboardprint?
01.<h1><s:property value="excelWorkSheet.sheetName" /> </h1>
02. <p>
03. <s:iterator value="excelWorkSheet.columns">
04. <s:property /> ||
05. </s:iterator>
06. </p>
07.
08. <s:iterator var="user" value="excelWorkSheet.data">
09. <p>
10. <s:property value="#user.id"/>
11. <s:property value="#user.name"/>
12. <s:property value="#user.pass"/>
13. <s:property value="#user.lastname"/>
14. <s:property value="#user.addres"/>
15. <s:property value="#user.remark"/>
16. </p>
17. </s:iterator>
<h1><s:property value="excelWorkSheet.sheetName" /> </h1>
<p>
<s:iterator value="excelWorkSheet.columns">
<s:property /> ||
</s:iterator>
</p>

<s:iterator var="user" value="excelWorkSheet.data">
<p>
<s:property value="#user.id"/>
<s:property value="#user.name"/>
<s:property value="#user.pass"/>
<s:property value="#user.lastname"/>
<s:property value="#user.addres"/>
<s:property value="#user.remark"/>
</p>
</s:iterator>

导出数据页面(Out.jsp)：

view plaincopy to clipboardprint?
01.<s:form name="form1" action="outPut.action" method="post">
02. <input type="hidden" name="format" value="xls" />
03. <s:submit name="sub" value="导出数据"></s:submit>
04. </s:form>
<s:form name="form1" action="outPut.action" method="post">
<input type="hidden" name="format" value="xls" />
<s:submit name="sub" value="导出数据"></s:submit>
</s:form>

导出数据Action:

view plaincopy to clipboardprint?
01.package com.boxun.action;
02.
03.import java.io.IOException;
04.
05.
06.import java.io.OutputStream;
07.import java.util.List;
08.
09.import javax.servlet.http.HttpServletResponse;
10.
11.import org.apache.poi.ss.usermodel.CellStyle;
12.import org.apache.poi.ss.usermodel.Row;
13.import org.apache.poi.ss.usermodel.Sheet;
14.import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15.import org.apache.poi.ss.usermodel.Workbook;
16.import org.apache.struts2.interceptor.ServletResponseAware;
17.
18.import com.boxun.bean.Userinfo;
19.import com.boxun.biz.IUserBiz;
20.import com.opensymphony.xwork2.ActionSupport;
21.
22.public class OutAction extends ActionSupport implements ServletResponseAware{
23.
24. /**
25. *
26. */
27. private static final long serialVersionUID = 1L;
28.
29. private IUserBiz biz ;
30. public void setBiz(IUserBiz biz) {
31. this.biz = biz;
32. }
33. private String format = "xls";
34. private HttpServletResponse response;
35. private String fileName;
36.
37. public String execute(){
38.
39. @SuppressWarnings("all")
40. List la = biz.getAllSql();
41.
42. setResponseHeader();
43. try {
44. exportExcel(response.getOutputStream());
45. response.getOutputStream().flush();
46. response.getOutputStream().close();
47. } catch (IOException e) {
48. e.printStackTrace();
49. }
50. return null;
51. }
52.
53. /** 设置响应头*/
54. public void setResponseHeader(){
55. try{
56.// response.setContentType("application/msexcel;charset=UTF-8"); //两种方法都可以
57. response.setContentType("application/octet-stream;charset=iso-8859-1");
58. response.setHeader("Content-Disposition", "attachment;filename="
59. +java.net.URLEncoder.encode(this.fileName, "UTF-8"));
60. //客户端不缓存
61. response.addHeader("Pargam", "no-cache");
62. response.addHeader("Cache-Control", "no-cache");
63. }catch(Exception ex){
64. ex.printStackTrace();
65. }
66. }
67.
68. /**导出数据*/
69. private void exportExcel(OutputStream os) throws IOException{
70. Workbook book = new HSSFWorkbook();
71. Sheet sheet = book.createSheet("导出信息");
72. Row row = sheet.createRow(0);
73. row.createCell(0).setCellValue("编号");
74. row.createCell(1).setCellValue("用户名");
75. row.createCell(2).setCellValue("密码");
76. row.createCell(3).setCellValue("真实姓名");
77. row.createCell(4).setCellValue("地址");
78. row.createCell(5).setCellValue("备注");
79. CellStyle sty = book.createCellStyle();
80. List<Userinfo> list = biz.getAll();
81. for (int i = 1; i < list.size(); i++) {
82. Userinfo user = list.get(i-1);
83. row = sheet.createRow(i);
84. row.createCell(0).setCellValue(user.getId());
85. row.createCell(1).setCellValue(user.getName());
86. row.createCell(2).setCellValue(user.getPass());
87. row.createCell(3).setCellValue(user.getLastname());
88. row.createCell(4).setCellValue(user.getAddres());
89. row.createCell(5).setCellValue(user.getRemark());
90. }
91. try{
92. book.write(os);
93. }catch(Exception ex){
94. ex.printStackTrace();
95. }
96. }
97.
98.
99. public String getFormat() {
100. return format;
101. }
102.
103.
104. public void setFormat(String format) {
105. this.format = format;
106. this.fileName = "导出数据.xls";
107. }
108.
109. public String getFileName() {
110. return fileName;
111. }
112.
113.
114. public void setFileName(String fileName) {
115. this.fileName = fileName;
116. }
117.
118. /**记住一定有该属性的set方法*/
119. public void setServletResponse(HttpServletResponse response) {
120. this.response = response;
121. }
122.
123.
124.
125.
126.
127.
128.
129.
130.}
package com.boxun.action;

import java.io.IOException;

import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.interceptor.ServletResponseAware;

import com.boxun.bean.Userinfo;
import com.boxun.biz.IUserBiz;
import com.opensymphony.xwork2.ActionSupport;

public class OutAction extends ActionSupport implements ServletResponseAware{

/**
*
*/
private static final long serialVersionUID = 1L;

private IUserBiz biz ;
public void setBiz(IUserBiz biz) {
this.biz = biz;
}
private String format = "xls";
private HttpServletResponse response;
private String fileName;

public String execute(){

@SuppressWarnings("all")
List la = biz.getAllSql();

setResponseHeader();
try {
exportExcel(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}

/** 设置响应头*/
public void setResponseHeader(){
try{
// response.setContentType("application/msexcel;charset=UTF-8"); //两种方法都可以
response.setContentType("application/octet-stream;charset=iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename="
+java.net.URLEncoder.encode(this.fileName, "UTF-8"));
//客户端不缓存
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
}catch(Exception ex){
ex.printStackTrace();
}
}

/**导出数据*/
private void exportExcel(OutputStream os) throws IOException{
Workbook book = new HSSFWorkbook();
Sheet sheet = book.createSheet("导出信息");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("用户名");
row.createCell(2).setCellValue("密码");
row.createCell(3).setCellValue("真实姓名");
row.createCell(4).setCellValue("地址");
row.createCell(5).setCellValue("备注");
CellStyle sty = book.createCellStyle();
List<Userinfo> list = biz.getAll();
for (int i = 1; i < list.size(); i++) {
Userinfo user = list.get(i-1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getPass());
row.createCell(3).setCellValue(user.getLastname());
row.createCell(4).setCellValue(user.getAddres());
row.createCell(5).setCellValue(user.getRemark());
}
try{
book.write(os);
}catch(Exception ex){
ex.printStackTrace();
}
}


public String getFormat() {
return format;
}

public void setFormat(String format) {
this.format = format;
this.fileName = "导出数据.xls";
}

public String getFileName() {
return fileName;
}

public void setFileName(String fileName) {
this.fileName = fileName;
}

/**记住一定有该属性的set方法*/
public void setServletResponse(HttpServletResponse response) {
this.response = response;
}

}

我用的是sql语句导出、先从数据库把数据查询出来、在导出查询出来的数据。

我的Excel数据格式：

我在导入过程中碰到一个问题~~~就是在用户名和密码这一项的时候、不能够完全的写数字~~~

如果全部是Number(Integer或者int)类型那么在转换这一属性数据的时候就会出错~~~搞不懂！ 必须要加上一个字符才能正确转换！

o(︶︿︶)o 唉！！！