导入导出Excel文件

2023-01-30 09:39:38

搭建环境

先新建web project ，然后Add Struts Capabilties：

下载导入导出Excel所需的jar包：

poi-3.8-20120326.jar包 : http://poi.apache.org/download.html

1、工程目录结构

2、struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="importexport" extends="struts-default">
<action name="downloadExcelModelAction" class="com.importexport.action.DownloadExcelModelAction"></action>
<action name="exportExcelAction" class="com.importexport.action.ExportExcelAction"></action>
<action name="importExcelAction" class="com.importexport.action.ImportExcelAction"></action>
</package>
</struts>

3、web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<display-name></display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<filter>
<filter-name>struts2</filter-name>
<filter-class>
org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
</filter-class>
</filter>
<filter-mapping>
<filter-name>struts2</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>

4、导入导出Excel基础类

4.1 ExportExcel.java

4.2 ImportExcel.java

package com.importexport.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ImportExcel {
/**
* @param file 要导入的Excel文件
* @param cLength 读取多少列
* @return
*/
public List<List<List<String>>> importExcel(File file,int cLength){
try {
InputStream xlsIs = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsIs);
List<List<List<String>>> worksheetList = new ArrayList<List<List<String>>>();
//循环工作簿
for(int nSheet=0; nSheet < hssfWorkbook.getNumberOfSheets(); ++nSheet){
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(nSheet);
if(hssfSheet == null){
worksheetList.add(null);//空工作簿占位
continue;
}
List<List<String>> workSheet = new ArrayList<List<String>>();
//循环行
for(int nRow=0; nRow <= hssfSheet.getLastRowNum(); ++nRow){
HSSFRow hssfRow = hssfSheet.getRow(nRow);
if(hssfRow == null){
workSheet.add(null);//空行占位
continue;
}
List<String> rowList = new ArrayList<String>();
int len = hssfRow.getLastCellNum();
len = len > cLength ? len : cLength;
for(int nCell=0; nCell<len; ++nCell){
HSSFCell xh = hssfRow.getCell(nCell);
if(xh == null){
rowList.add(null);
continue;
}
String cellValue = getVlaue(xh);
rowList.add(cellValue);
}
workSheet.add(rowList);//向工作簿中添加一行
}
worksheetList.add(workSheet);//向Excel文档中添加一个工作簿
}
return worksheetList;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private String getVlaue(HSSFCell xh) {
// TODO Auto-generated method stub
String reValue = null;
if(xh.getCellType() == xh.CELL_TYPE_BOOLEAN){//返回布尔类型的值
reValue = String.valueOf(xh.getBooleanCellValue());
}else if(xh.getCellType() == xh.CELL_TYPE_NUMERIC){//返回数值类型的值
if(HSSFDateUtil.isCellDateFormatted(xh)){
SimpleDateFormat dateformat =new SimpleDateFormat("yyyy-MM-dd");
Date dt = HSSFDateUtil.getJavaDate(xh.getNumericCellValue());
reValue = dateformat.format(dt);
}else{
reValue = String.valueOf(xh.getNumericCellValue());
}
}else{//返回字符串类型的值
reValue = String.valueOf(xh.getStringCellValue());
}
return reValue;
}
}

5、导入导出action类

5.1 DownloadExcelModelAction.java

package com.importexport.action;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.importexport.util.ExportExcel;
/**
* 下载Excel模板
*
*/
public class DownloadExcelModelAction extends ActionSupport {
/**
* 自定义Excel模板一（最简）下载
*/
public void downLoadExcelOne() {
try {
String title = "人员信息列表";
String[] headers = { "姓名", "性别", "居住地" };
int[] columns = {};//从0开始计数，下拉选择数据列
List<String[]> valueList = new ArrayList<String[]>();//需要下拉选择数据列的选择项
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("octets/stream");
String header = "自定义Excel模板一（最简）.xls";
header = new String(header.getBytes(), "iso-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="
+ header);
OutputStream out = response.getOutputStream();
ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
exportexcel.exportExcelModel(title, headers, columns, valueList,
out, null);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 自定义Excel模板二（含有下拉选择项）下载
*/
public void downLoadExcelTwo() {
try {
String title = "人员信息列表";
String[] headers = { "姓名", "性别", "居住地",""};
int[] columns = {1,2};//从0开始计数，下拉选择数据列
List<String[]> valueList = new ArrayList<String[]>();
String[] sex = {"男","女"};
valueList.add(sex);
String[] address = {"广州","汕头","深圳","珠海"};
valueList.add(address);
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("octets/stream");
String header = "自定义Excel模板二（含有下拉选择项）.xls";
header = new String(header.getBytes(), "iso-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="
+ header);
OutputStream out = response.getOutputStream();
ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
exportexcel.exportExcelModel(title, headers, columns, valueList,
out, null);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 自定义Excel模板三（增加security表单）下载
*/
public void downLoadExcelThree() {
try {
String title = "人员信息列表";
String[] headers = { "姓名", "性别", "居住地" };
int[] columns = {};//从0开始计数，下拉选择数据列
List<String[]> valueList = new ArrayList<String[]>();//需要下拉选择数据列的选择项
String[] security = {"测试security","测试6666"};//验证表单的展示内容
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("octets/stream");
String header = "自定义Excel模板三（增加security表单）.xls";
header = new String(header.getBytes(), "iso-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="
+ header);
OutputStream out = response.getOutputStream();
ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
exportexcel.exportExcelModel(title, headers, columns, valueList,
out, security);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

5.2 ExportExcelAction.java

package com.importexport.action;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.validator.Var;
import org.apache.struts2.ServletActionContext;
import com.importexport.util.ExportExcel;
import com.opensymphony.xwork2.ActionSupport;
/**
* 导出Excel文件
*/
public class ExportExcelAction extends ActionSupport{
/**
* 导出数据成Excel文件（最简）
*/
public void exportExcelOne() {
try {
String title = "人员信息列表";
String[] headers = { "姓名", "性别", "居住地" };
String[] keys = {"name","sex","address"};//HashMap中的key值
//封装要导出的数据
List<HashMap<Object,Object>> dataset = new ArrayList<HashMap<Object,Object>>();
for(int i = 0;i < 5;i++){
HashMap map = new HashMap();
map.put("name", "楚暮" + i);
map.put("sex", "半魔");
map.put("address", "湛离界");
dataset.add(map);
}
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("octets/stream");
String header = "导出数据成Excel文件（最简）.xls";
header = new String(header.getBytes(), "iso-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="
+ header);
OutputStream out = response.getOutputStream();
ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
exportexcel.exportExcelList(title, headers, dataset , out, keys,null,null);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 导出数据成Excel文件（合并单元格）
*/
public void exportExcelTwo() {
try {
String title = "人员信息列表";
String[] headers = { "姓名", "性别", "居住地" };
String[] keys = {"name","sex","address"};//HashMap中的key值
//封装要导出的数据
List<HashMap<Object,Object>> dataset = new ArrayList<HashMap<Object,Object>>();
for(int i = 0;i < 6;i++){
HashMap<Object, Object> map = new HashMap();
map.put("name", "楚暮" + i);
map.put("sex", "半魔");
map.put("address", "湛离界");
dataset.add(map);
}
int[] mergedCol = {1,2};//从0开始算起，合并第一列（sex）和第二列（address）
//{3,1,1,1} 3+1+1+1=6 表示把前3行合并单元格，第4行合并单元格，第5行合并单元格，第6行合并单元格
//{3,2,1} 3+2+1=6 表示前3行合并单元格，第4、5合并单元格，第6行合并单元格
//{2,4} 2+4=6 表示前2行合并单元格，第3,4,5,6行合并单元格
//{6} 表示前6行合并单元格
int[] mergedL = {3,1,2};
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("octets/stream");
String header = "导出数据成Excel文件（合并单元格）.xls";
header = new String(header.getBytes(), "iso-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="
+ header);
OutputStream out = response.getOutputStream();
ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
exportexcel.exportExcelList(title, headers, dataset , out, keys,mergedCol,mergedL);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

5.3 ImportExcelAction.java

package com.importexport.action;
import com.importexport.util.ImportExcel;
import com.opensymphony.xwork2.ActionSupport;
import java.io.File;
import java.util.List;
import org.apache.struts2.ServletActionContext;
/**
* 导入Excel文件
*/
public class ImportExcelAction extends ActionSupport{
/**
* 导入Excel文件
*/
public void importExcel(){
ImportExcel importExcel = new ImportExcel();
String path = ServletActionContext.getRequest().getRealPath("/");
File excelfile = new File(path + "自定义Excel模板二（含有下拉选择项）.xls");
List<List<List<String>>> excelAll = importExcel.importExcel(excelfile, 3);//3 : 读取3列
//解析打印数据
List<List<String>> excelSheet = excelAll.get(0);
for(List<String> excelRow : excelSheet){
System.out.println(excelRow.get(0) + "-" + excelRow.get(1) + "-" + excelRow.get(2));
}
}
}

6、index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>导入导出Excel</title>
</head>
<body>
<h2>导出Excel模板</h2>
<a href="downloadExcelModelAction!downLoadExcelOne.action">下载自定义Excel模板一（最简）</a>
<a href="downloadExcelModelAction!downLoadExcelTwo.action">下载自定义Excel模板二（含有下拉选择项）</a>
<a href="downloadExcelModelAction!downLoadExcelThree.action">下载自定义Excel模板三（增加security表单）</a>
<h2>导出</h2>
<a href="exportExcelAction!exportExcelOne.action">导出数据成Excel文件（最简）</a>
<a href="exportExcelAction!exportExcelTwo.action">导出数据成Excel文件（合并单元格）</a>
<h2>导入</h2>
<a href="importExcelAction!importExcel.action">导入Excel文件</a>
</body>
</html>