导入导出Excel文件

导入导出Excel文件
搭建环境

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

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

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

1、工程目录结构

2、struts.xml
1. <?xml version="1.0" encoding="UTF-8" ?>
2. <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
3. <struts>
4. <package name="importexport" extends="struts-default">
5. <action name="downloadExcelModelAction" class="com.importexport.action.DownloadExcelModelAction"></action>
6. <action name="exportExcelAction" class="com.importexport.action.ExportExcelAction"></action>
7. <action name="importExcelAction" class="com.importexport.action.ImportExcelAction"></action>
8. </package>
9. </struts>
3、web.xml
1. <?xml version="1.0" encoding="UTF-8"?>
2. <web-app version="2.5"
3. xmlns="http://java.sun.com/xml/ns/javaee"
4. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5. xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
6. http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
7. <display-name></display-name>
8. <welcome-file-list>
9. <welcome-file>index.jsp</welcome-file>
10. </welcome-file-list>
11. <filter>
12. <filter-name>struts2</filter-name>
13. <filter-class>
14. org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
15. </filter-class>
16. </filter>
17. <filter-mapping>
18. <filter-name>struts2</filter-name>
19. <url-pattern>/*</url-pattern>
20. </filter-mapping>
21. </web-app>
4、导入导出Excel基础类

4.1 ExportExcel.java

4.2 ImportExcel.java
1. package com.importexport.util;
3. import java.io.File;
4. import java.io.FileInputStream;
5. import java.io.InputStream;
6. import java.text.SimpleDateFormat;
7. import java.util.ArrayList;
8. import java.util.Date;
9. import java.util.List;
11. import org.apache.poi.hssf.usermodel.HSSFCell;
12. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
13. import org.apache.poi.hssf.usermodel.HSSFRow;
14. import org.apache.poi.hssf.usermodel.HSSFSheet;
15. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
17. public class ImportExcel {
18. /**
19. * @param file 要导入的Excel文件
20. * @param cLength 读取多少列
21. * @return
22. */
23. public List<List<List<String>>> importExcel(File file,int cLength){
24. try {
25. InputStream xlsIs = new FileInputStream(file);
26. HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsIs);
27. List<List<List<String>>> worksheetList = new ArrayList<List<List<String>>>();
28. //循环工作簿
29. for(int nSheet=0; nSheet < hssfWorkbook.getNumberOfSheets(); ++nSheet){
30. HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(nSheet);
31. if(hssfSheet == null){
32. worksheetList.add(null);//空工作簿占位
33. continue;
34. }
35. List<List<String>> workSheet = new ArrayList<List<String>>();
36. //循环行
37. for(int nRow=0; nRow <= hssfSheet.getLastRowNum(); ++nRow){
38. HSSFRow hssfRow = hssfSheet.getRow(nRow);
39. if(hssfRow == null){
40. workSheet.add(null);//空行占位
41. continue;
42. }
43. List<String> rowList = new ArrayList<String>();
44. int len = hssfRow.getLastCellNum();
45. len = len > cLength ? len : cLength;
46. for(int nCell=0; nCell<len; ++nCell){
47. HSSFCell xh = hssfRow.getCell(nCell);
48. if(xh == null){
49. rowList.add(null);
50. continue;
51. }
52. String cellValue = getVlaue(xh);
53. rowList.add(cellValue);
54. }
55. workSheet.add(rowList);//向工作簿中添加一行
56. }
57. worksheetList.add(workSheet);//向Excel文档中添加一个工作簿
58. }
59. return worksheetList;
60. } catch (Exception e) {
61. // TODO Auto-generated catch block
62. e.printStackTrace();
63. }
64. return null;
65. }
67. private String getVlaue(HSSFCell xh) {
68. // TODO Auto-generated method stub
69. String reValue = null;
70. if(xh.getCellType() == xh.CELL_TYPE_BOOLEAN){//返回布尔类型的值
71. reValue = String.valueOf(xh.getBooleanCellValue());
72. }else if(xh.getCellType() == xh.CELL_TYPE_NUMERIC){//返回数值类型的值
73. if(HSSFDateUtil.isCellDateFormatted(xh)){
74. SimpleDateFormat dateformat =new SimpleDateFormat("yyyy-MM-dd");
75. Date dt = HSSFDateUtil.getJavaDate(xh.getNumericCellValue());
76. reValue = dateformat.format(dt);
77. }else{
78. reValue = String.valueOf(xh.getNumericCellValue());
79. }
80. }else{//返回字符串类型的值
81. reValue = String.valueOf(xh.getStringCellValue());
82. }
83. return reValue;
84. }
85. }
5、导入导出action类

5.1 DownloadExcelModelAction.java
1. package com.importexport.action;
3. import java.io.OutputStream;
5. import java.util.ArrayList;
6. import java.util.HashMap;
7. import java.util.List;
9. import javax.servlet.http.HttpServletResponse;
11. import org.apache.struts2.ServletActionContext;
13. import com.opensymphony.xwork2.ActionSupport;
15. import com.importexport.util.ExportExcel;
17. /**
18. * 下载Excel模板
19. *
20. */
21. public class DownloadExcelModelAction extends ActionSupport {
23. /**
24. * 自定义Excel模板一（最简）下载
25. */
26. public void downLoadExcelOne() {
27. try {
28. String title = "人员信息列表";
29. String[] headers = { "姓名", "性别", "居住地" };
30. int[] columns = {};//从0开始计数，下拉选择数据列
31. List<String[]> valueList = new ArrayList<String[]>();//需要下拉选择数据列的选择项
33. HttpServletResponse response = ServletActionContext.getResponse();
34. response.setContentType("octets/stream");
35. String header = "自定义Excel模板一（最简）.xls";
36. header = new String(header.getBytes(), "iso-8859-1");
37. response.addHeader("Content-Disposition", "attachment;filename="
38. + header);
39. OutputStream out = response.getOutputStream();
40. ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
41. exportexcel.exportExcelModel(title, headers, columns, valueList,
42. out, null);
43. out.close();
44. } catch (Exception e) {
45. // TODO Auto-generated catch block
46. e.printStackTrace();
47. }
48. }
50. /**
51. * 自定义Excel模板二（含有下拉选择项）下载
52. */
53. public void downLoadExcelTwo() {
54. try {
55. String title = "人员信息列表";
56. String[] headers = { "姓名", "性别", "居住地",""};
57. int[] columns = {1,2};//从0开始计数，下拉选择数据列
58. List<String[]> valueList = new ArrayList<String[]>();
59. String[] sex = {"男","女"};
60. valueList.add(sex);
61. String[] address = {"广州","汕头","深圳","珠海"};
62. valueList.add(address);
64. HttpServletResponse response = ServletActionContext.getResponse();
65. response.setContentType("octets/stream");
66. String header = "自定义Excel模板二（含有下拉选择项）.xls";
67. header = new String(header.getBytes(), "iso-8859-1");
68. response.addHeader("Content-Disposition", "attachment;filename="
69. + header);
70. OutputStream out = response.getOutputStream();
71. ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
72. exportexcel.exportExcelModel(title, headers, columns, valueList,
73. out, null);
74. out.close();
75. } catch (Exception e) {
76. // TODO Auto-generated catch block
77. e.printStackTrace();
78. }
79. }
81. /**
82. * 自定义Excel模板三（增加security表单）下载
83. */
84. public void downLoadExcelThree() {
85. try {
86. String title = "人员信息列表";
87. String[] headers = { "姓名", "性别", "居住地" };
88. int[] columns = {};//从0开始计数，下拉选择数据列
89. List<String[]> valueList = new ArrayList<String[]>();//需要下拉选择数据列的选择项
91. String[] security = {"测试security","测试6666"};//验证表单的展示内容
93. HttpServletResponse response = ServletActionContext.getResponse();
94. response.setContentType("octets/stream");
95. String header = "自定义Excel模板三（增加security表单）.xls";
96. header = new String(header.getBytes(), "iso-8859-1");
97. response.addHeader("Content-Disposition", "attachment;filename="
98. + header);
99. OutputStream out = response.getOutputStream();
100. ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
101. exportexcel.exportExcelModel(title, headers, columns, valueList,
102. out, security);
103. out.close();
104. } catch (Exception e) {
105. // TODO Auto-generated catch block
106. e.printStackTrace();
107. }
108. }
109. }
5.2 ExportExcelAction.java
1. package com.importexport.action;
3. import java.io.OutputStream;
4. import java.util.ArrayList;
5. import java.util.HashMap;
6. import java.util.List;
8. import javax.servlet.http.HttpServletResponse;
10. import org.apache.commons.validator.Var;
11. import org.apache.struts2.ServletActionContext;
13. import com.importexport.util.ExportExcel;
14. import com.opensymphony.xwork2.ActionSupport;
16. /**
17. * 导出Excel文件
18. */
19. public class ExportExcelAction extends ActionSupport{
21. /**
22. * 导出数据成Excel文件（最简）
23. */
24. public void exportExcelOne() {
25. try {
26. String title = "人员信息列表";
27. String[] headers = { "姓名", "性别", "居住地" };
28. String[] keys = {"name","sex","address"};//HashMap中的key值
29. //封装要导出的数据
30. List<HashMap<Object,Object>> dataset = new ArrayList<HashMap<Object,Object>>();
31. for(int i = 0;i < 5;i++){
32. HashMap map = new HashMap();
33. map.put("name", "楚暮" + i);
34. map.put("sex", "半魔");
35. map.put("address", "湛离界");
36. dataset.add(map);
37. }
39. HttpServletResponse response = ServletActionContext.getResponse();
40. response.setContentType("octets/stream");
41. String header = "导出数据成Excel文件（最简）.xls";
42. header = new String(header.getBytes(), "iso-8859-1");
43. response.addHeader("Content-Disposition", "attachment;filename="
44. + header);
45. OutputStream out = response.getOutputStream();
46. ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
47. exportexcel.exportExcelList(title, headers, dataset , out, keys,null,null);
48. out.close();
49. } catch (Exception e) {
50. // TODO Auto-generated catch block
51. e.printStackTrace();
52. }
53. }
55. /**
56. * 导出数据成Excel文件（合并单元格）
57. */
58. public void exportExcelTwo() {
59. try {
60. String title = "人员信息列表";
61. String[] headers = { "姓名", "性别", "居住地" };
62. String[] keys = {"name","sex","address"};//HashMap中的key值
63. //封装要导出的数据
64. List<HashMap<Object,Object>> dataset = new ArrayList<HashMap<Object,Object>>();
65. for(int i = 0;i < 6;i++){
66. HashMap<Object, Object> map = new HashMap();
67. map.put("name", "楚暮" + i);
68. map.put("sex", "半魔");
69. map.put("address", "湛离界");
70. dataset.add(map);
71. }
73. int[] mergedCol = {1,2};//从0开始算起，合并第一列（sex）和第二列（address）
75. //{3,1,1,1} 3+1+1+1=6 表示把前3行合并单元格，第4行合并单元格，第5行合并单元格，第6行合并单元格
76. //{3,2,1} 3+2+1=6 表示前3行合并单元格，第4、5合并单元格，第6行合并单元格
77. //{2,4} 2+4=6 表示前2行合并单元格，第3,4,5,6行合并单元格
78. //{6} 表示前6行合并单元格
79. int[] mergedL = {3,1,2};
81. HttpServletResponse response = ServletActionContext.getResponse();
82. response.setContentType("octets/stream");
83. String header = "导出数据成Excel文件（合并单元格）.xls";
84. header = new String(header.getBytes(), "iso-8859-1");
85. response.addHeader("Content-Disposition", "attachment;filename="
86. + header);
87. OutputStream out = response.getOutputStream();
88. ExportExcel<HashMap> exportexcel = new ExportExcel<HashMap>();
89. exportexcel.exportExcelList(title, headers, dataset , out, keys,mergedCol,mergedL);
90. out.close();
91. } catch (Exception e) {
92. // TODO Auto-generated catch block
93. e.printStackTrace();
94. }
95. }
96. }
5.3 ImportExcelAction.java
1. package com.importexport.action;
3. import com.importexport.util.ImportExcel;
4. import com.opensymphony.xwork2.ActionSupport;
6. import java.io.File;
7. import java.util.List;
9. import org.apache.struts2.ServletActionContext;
10. /**
11. * 导入Excel文件
12. */
13. public class ImportExcelAction extends ActionSupport{
15. /**
16. * 导入Excel文件
17. */
18. public void importExcel(){
19. ImportExcel importExcel = new ImportExcel();
20. String path = ServletActionContext.getRequest().getRealPath("/");
21. File excelfile = new File(path + "自定义Excel模板二（含有下拉选择项）.xls");
22. List<List<List<String>>> excelAll = importExcel.importExcel(excelfile, 3);//3 : 读取3列
23. //解析打印数据
24. List<List<String>> excelSheet = excelAll.get(0);
25. for(List<String> excelRow : excelSheet){
26. System.out.println(excelRow.get(0) + "-" + excelRow.get(1) + "-" + excelRow.get(2));
27. }
28. }
29. }
6、index.jsp
1. <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
2. <%
3. String path = request.getContextPath();
4. String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
5. %>
7. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8. <html>
9. <head>
10. <title>导入导出Excel</title>
11. </head>
12. <body>
13. <h2>导出Excel模板</h2>
14. <a href="downloadExcelModelAction!downLoadExcelOne.action">下载自定义Excel模板一（最简）</a>
15. <a href="downloadExcelModelAction!downLoadExcelTwo.action">下载自定义Excel模板二（含有下拉选择项）</a>
16. <a href="downloadExcelModelAction!downLoadExcelThree.action">下载自定义Excel模板三（增加security表单）</a>
18. <h2>导出</h2>
19. <a href="exportExcelAction!exportExcelOne.action">导出数据成Excel文件（最简）</a>
20. <a href="exportExcelAction!exportExcelTwo.action">导出数据成Excel文件（合并单元格）</a>
22. <h2>导入</h2>
23. <a href="importExcelAction!importExcel.action">导入Excel文件</a>
24. </body>
25. </html>
7、效果截图

要导入的Excel数据

读取Excel数据，在控制台打印相关数据

附件源码：

下载地址：https://gitee.com/KingXin666/ImportExport
相关阅读:
lazarus linux下使用powerPDF控件中文乱码及英文和中文等宽的解决方法
 增强Lazreport控件功能及修正Bug(2022.05.20更新)
python爬虫练习——下载梨视频，带进度条
 Python爬虫练习——爬取豆瓣TOP250
python爬虫库常用手册
 python抓取小说——练习
 beautifulsoup CSS选择器解析方法
 python爬虫练习——爬取壁纸
 vim 从嫌弃到依赖（0）——概述
 安装和定位vimrc
原文地址：https://www.cnblogs.com/yangcx666/p/8723885.html

导入导出Excel文件

搭建环境

1、工程目录结构

2、struts.xml

3、web.xml

4、导入导出Excel基础类

4.1 ExportExcel.java

4.2 ImportExcel.java

5、导入导出action类

5.1 DownloadExcelModelAction.java

5.2 ExportExcelAction.java

5.3 ImportExcelAction.java

6、index.jsp

7、效果截图

附件源码：