Struts2 POI 导入导出Excel数据
页面端：
<html>
<head>
<title>导入数据</title>
</head>
<body>
<h1>导入excel数据</h1>
<s:form action="import" method="post" enctype="multipart/form-data">
导入Excel文件:<s:file name="excelFile" /> <br />
<s:submit value="导入"></s:submit>
</s:form>
</body>
</html>
配置struts.xml
<action name="exportExcel" class="com.bestbpo.action.ExcelAction">
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="contentDisposition">attachment;filename=${fileName}</param>
<param name="inputName">excelStream</param>
<param name="bufferSize">1024</param>
</result>
</action>
ExcelAction类：extends ActionSupport
InputStream excelStream;
String fileName;
操作poi
private void exportExcel(OutputStream os) {
Workbook workbook = null;
workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("学生信息");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("学号");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("性别");
row.createCell(3).setCellValue("生日");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
List<Student> list = new StudentService().findAll();
for (int i = 1; i <= list.size(); i++) {
Student stu = list.get(i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(stu.getId());
row.createCell(1).setCellValue(stu.getName());
row.createCell(2).setCellValue(stu.getSex());
Cell cell = row.createCell(3);
cell.setCellValue(stu.getBirthday());
cell.setCellStyle(cellStyle);
}
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
}
如果用struts2提供的下载方法的话：
poi里的workbook.write(接受的是一个OutputStream);
将这个workbook写入到一个输出流
先new一个输出流ByteArrayOutputStreambaos=newByteArrayOutputStream();
workbook.write(baos);
baos.flush();
由于struts2框架 struts.xml 里面配制的是要传给struts一个输入流 然后有struts框架在转换成一个输出流给页面提供下载，所以要将workbook写进的输出流转成输入流：
byte[] aa=baos.toByteArray();//这句代码是将输出流转成一个byte数组
在new一个inputStream
excelStream=newByteArrayInputStream(aa,0,aa.length);
如果不依靠框架：（推荐）
public class ExportExcelAction implements ServletResponseAware { private String format = "xls"; private HttpServletResponse response; private String fileName; //省略getter setter public void setFormat(String format) { //根据请求的文件的格式设置format的内容是 xls还是xlsx } public String execute() throws Exception { //具体代码见后面 } /**设置响应头*/ private void setResponseHeader() { //具体代码见后面 } /**导出数据*/ private void exportExcel(OutputStream os) { //具体代码见后面 } }
public void setFormat(String format) { this.format = format; if ("xls".equals(format)) { this.fileName = "导出数据.xls"; } if ("xlsx".equals(format)) { this.fileName = "导出数据.xlsx"; } }
private void setResponseHeader() { response.setContentType("application/octet-stream;charset=iso-8859-1"); try { response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(this.fileName, "UTF-8")); // 客户端不缓存 response.addHeader("Pragma", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
private void exportExcel(OutputStream os) { Workbook workbook = null; if ("xls".equals(format)) { workbook = new HSSFWorkbook(); } if ("xlsx".equals(format)) { workbook = new XSSFWorkbook(); } Sheet sheet = workbook.createSheet("学生信息"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("学号"); row.createCell(1).setCellValue("姓名"); row.createCell(2).setCellValue("性别"); row.createCell(3).setCellValue("生日"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); List<Student> list = new StudentService().findAll(); for (int i = 1; i <= list.size(); i++) { Student stu = list.get(i - 1); row = sheet.createRow(i); row.createCell(0).setCellValue(stu.getId()); row.createCell(1).setCellValue(stu.getName()); row.createCell(2).setCellValue(stu.getSex()); Cell cell = row.createCell(3); cell.setCellValue(stu.getBirthday()); cell.setCellStyle(cellStyle); } try { workbook.write(os); } catch (IOException e) { e.printStackTrace(); } }
public String execute() throws Exception { setResponseHeader(); exportExcel(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); return null; }
