flyFish's recorder.

Excel导出，导入（POI）

excel poi
2018/07/09 Share

文章结构

  • POI概念
  • POI组件
  • EXCEL(HSSF，XSSF)常用API（略）
  • 使用场景,代码举例
    • 本地加载
    • 网络加载
    • 网络输出
  • 注意点

后台管理经常会用到数据的导入导出，格式一般为Microsoft Excel，这里整理下在本地与web端excel的导入导出使用方法。

POI概念

Apache POI是一种流行的API，它允许程序员使用Java程序创建，修改和显示MS Office文件。这由Apache软件基金会开发使用Java分布式设计或修改Microsoft Office文件的开源库。它包含类和方法对用户输入数据或文件到MS Office文档进行解码。.

Apache POI组件

Apache POI包含类和方法，来将MS Office所有OLE 2文档复合。此API组件的列表如下。

  • HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
  • XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
  • HWPF － 提供读写Microsoft Word DOC格式档案的功能。
  • HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
  • HDGF － 提供读Microsoft Visio格式档案的功能。
  • HPBF － 提供读Microsoft Publisher格式档案的功能。
  • HSMF － 提供读Microsoft Outlook格式档案的功能。

这里我们只介绍HSSF和XSSF。

EXCEL(HSSF，XSSF)常用API

先来看一张图
相关接口

可以看出，HSSF与XSSF接口一致，方法类似。
这里就不展开写了，列几个比较好的文章。

poi官方文档
很详细
样式

使用场景,代码举例

加载本地文件

读取本地xlsx/xls文件，转化为List<List<String>>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
/**
 * 本地读取Excel文件
 * @author 王飞鱼
 * @date 2018年8月9日
 * <description>
 */
public class ImportExcel {

	/*总行数 */
	private int totalRows = 0;

	/*总列数 */
	private int totalCells = 0;

	/*错误信息 */
	private String errorInfo;

	public ImportExcel() {

	}

	public int getTotalRows() {
		return totalRows;
	}
	public int getTotalCells() {
		return totalCells;
	}
	public String getErrorInfo() {
		return errorInfo;
	}

	/**
	 * 
	 * @描述：验证excel文件
	 * @参数：filePath 文件完整路径
	 * @返回值：boolean
	 */
	public boolean validateExcel(String filePath) {
		/** 检查文件名是否为空或者是否是Excel格式的文件 */
		if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {
			errorInfo = "文件名不是excel格式";
			return false;
		}
		/** 检查文件是否存在 */
		File file = new File(filePath);
		if (file == null || !file.exists()) {
			errorInfo = "文件不存在";
			return false;
		}
		return true;
	}

	/**
	 * @描述：根据文件名读取excel文件
	 * @参数：filePath 文件完整路径
	 * @返回值：List
	 */
	public List<List<String>> read(String filePath) {
		List<List<String>> dataLst = new ArrayList<List<String>>();
		InputStream is = null;
		try {
			/** 验证文件是否合法 */
			if (!validateExcel(filePath)) {
				System.out.println(errorInfo);
				return null;
			}
			/** 判断文件的类型，是2003还是2007 */
			boolean isExcel2003 = true;
			if (POIUtil.isExcel2007(filePath)) {
				isExcel2003 = false;
			}
			/** 调用本类提供的根据流读取的方法 */
			File file = new File(filePath);
			is = new FileInputStream(file);
			dataLst = read(is, isExcel2003);
			is.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					is = null;
					e.printStackTrace();
				}
			}
		}
		/** 返回最后读取的结果 */
		return dataLst;

	}

	/**
	 * 
	 * @描述：根据流读取Excel文件
	 * @参数：inputStream
	 * @参数：isExcel2003
	 * @返回值：List
	 */
	public List<List<String>> read(InputStream inputStream, boolean isExcel2003) {
		List<List<String>> dataLst = null;
		try {
			/** 根据版本选择创建Workbook的方式 */
			Workbook wb = null;
			if (isExcel2003) {
				wb = new HSSFWorkbook(inputStream);
			} else {
				wb = new XSSFWorkbook(inputStream);
			}
			dataLst = read(wb);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return dataLst;
	}

	/**
	 * 
	 * @描述：读取数据
	 * @参数：Workbook
	 * @返回值：List<List<String>>
	 */
	private List<List<String>> read(Workbook wb) {
		List<List<String>> dataLst = new ArrayList<List<String>>();
		/* 得到第一个shell */
		Sheet sheet = wb.getSheetAt(0);
		/* 得到Excel的行数 */
		this.totalRows = sheet.getPhysicalNumberOfRows();
		/* 得到Excel的列数 */
		if (this.totalRows >= 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}
		/* 循环Excel的行 */
		for (int r = 0; r < this.totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			List<String> rowLst = new ArrayList<String>();
			/* 循环Excel的列 */
			for (int c = 0; c < this.getTotalCells(); c++) {
				Cell cell = row.getCell(c);
				String cellValue = "";
				if (null != cell) {
					// 以下是判断数据的类型
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_NUMERIC: // 数字
						cellValue = cell.getNumericCellValue() + "";
						break;
					case HSSFCell.CELL_TYPE_STRING: // 字符串
						cellValue = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
						cellValue = cell.getBooleanCellValue() + "";
						break;
					case HSSFCell.CELL_TYPE_FORMULA: // 公式
						cellValue = cell.getCellFormula() + "";
						break;
					case HSSFCell.CELL_TYPE_BLANK: // 空值
						cellValue = "";
						break;
					case HSSFCell.CELL_TYPE_ERROR: // 故障
						cellValue = "非法字符";
						break;
					default:
						cellValue = "未知类型";
						break;
					}
				}
				rowLst.add(cellValue);
			}
			/** 保存第r行的第c列 */
			dataLst.add(rowLst);
		}
		return dataLst;
	}

	public static void main(String[] args) throws Exception {
		ImportExcel poi = new ImportExcel();
		List<List<String>> list = poi.read("C:\\test.xlsx");
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				System.out.print("第" + (i) + "行");
				List<String> cellList = list.get(i);
				for (int j = 0; j < cellList.size(); j++) {
					// System.out.print(" 第" + (j + 1) + "列值：");
					System.out.print("    " + cellList.get(j));
				}
				System.out.println();
			}
		}
	}
}

/**
 * 
 * @author 王飞鱼
 * @date 2018年8月9日
 * <description>
 */
public class POIUtil {

	/**
	 * 
	 * @描述：是否是2003的excel，返回true是2003
	 * @时间：2012-08-29 下午16:29:11
	 * @参数：filePath 文件完整路径
	 * @返回值：boolean
	 */
	public static boolean isExcel2003(String filePath) {
		return filePath.matches("^.+\\.(?i)(xls)$");

	}

	/**
	 * 
	 * @描述：是否是2007的excel，返回true是2007
	 * @时间：2012-08-29 下午16:28:20
	 * @参数：文件完整路径
	 * @返回值：boolean
	 */
	public static boolean isExcel2007(String filePath) {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}

}

加载网络文件

通过input上传文件，ajax提交至后台，后台解析文件，并转化为对应类别的对象集合List<User>

1
2
3
4
5
6
<!-- jquery -->
<form id="from1" action="${webroot}/system/sysResource/improtExcel" method="post" enctype="multipart/form-data" >
	<input type="file" id="uploadFile" name="uploadFile"/>
</from>
<!-- angular -->
<input id="uploadFile" name="uploadFile" type="file">
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
//jquery  
function importEmp(){    
    //检验导入的文件是否为Excel文件     
    var uploadFile = document.getElementById("uploadFile").value;    
    if(uploadFile == null || uploadFile == ''){    
        alert("请选择要上传的Excel文件");    
        return;    
    }else{    
        var fileExtend = uploadFile.substring(uploadFile.lastIndexOf('.')).toLowerCase();     
        if(fileExtend == '.xls'){    
        }else{    
            alert("文件格式需为'.xls'格式");    
            return;    
        }    
    }    
    //提交表单     
    document.getElementById("from1").submit();    
}   

//angular
$scope.improtExcel = function () {  
  var uploadFile=document.querySelector("#uploadFile").files[0];  
  //验证  
  if(uploadFile == undefined){  
      alert("请选择文件！");  
	  return;  
  }  
  var size = uploadFile.size / 1024;  
  if(size>2048){  
      alert("附件大小不能大于"+2+"M！");  
	  return;  
  }  
  var fileend = uploadFile.name.substring(uploadFile.name.lastIndexOf("."));  
  if(fileend != ".xls"){  
      alert("请上传97-2003版本Excel！");  
	  return;  
  }  
  
  //jquery获取$("#uploadFile").prop("files")[0];  
  var formData = new FormData();  
  formData.append('nPartyId', groupId);//其他需要上传的字段 用户所在机构id  
  formData.append('uploadFile', uploadFile);//文件  
  $http({  
      url:BASEURL+"/partyGroup/improtExcel",  
	  method:"post",  
	  headers: {'Content-Type': undefined},//使用angular上传一定要加上这一句，不然传给后台的是空的。  
	  data: formData,  
  }).success(function (resData) {  
      layer.alert(resData.msg);  
	  $scope.getPersonalForGroupId(groupId);  
	  $scope.hideImport();  
  });  
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
//controller
@RequestMapping(value = "/improtExcel", method = { RequestMethod.POST })  
public JsonResponse<BaseResult> ImprotExcel(@RequestParam(value="uploadFile")MultipartFile file,int nPartyId) {
	//自定义返回值类型  
	JsonResponse<BaseResult> result = new JsonResponse<BaseResult>();
    try {  
    	PartyPersonnelImport person = new PartyPersonnelImport();
        List<Map<String, Object>> list = ImportExcelUntil.importExcel(file, person); 
        return partyGroupPersonnelService.ImprotExcel(list,nPartyId);
    } catch (Exception e) {  
        e.printStackTrace();  
    }  
    return result;
}
//工具类ImportExcelUntil
public class ImportExcelUntil{  
  
    /** 
     * 拼装单个obj 
     * @param obj 
     * @param row 
     * @return 
     * @throws Exception 
     */  
    private  static  Map<String, Object>  dataObj(Object obj, HSSFRow row) throws Exception {  
        Class<?> rowClazz= obj.getClass();   
        //import org.apache.commons.lang3.reflect.FieldUtils;   
        Field[] fields = FieldUtils.getAllFields(rowClazz);  
        if (fields == null || fields.length < 1) {  
            return null;  
        }  
          
        //容器  
        Map<String, Object> map = new HashMap<String, Object>();  
          
        //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）  
        for (int j = 0; j < fields.length; j++) {
            map.put(fields[j].getName(), getVal(row.getCell(j)));  
        }  
        return map;   
    }  
      
    public  static   List<Map<String, Object>> importExcel(MultipartFile file, Object obj) throws Exception {  
          
        //装载流  
        POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());  
        HSSFWorkbook hw= new HSSFWorkbook(fs);  
          
        //获取第一个sheet页  
        HSSFSheet sheet = hw.getSheetAt(0);  
          
        //容器  
        List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();  
          
        //遍历行 从第三行开始（去除标题）  
        for (int i = 2; i <= sheet.getLastRowNum(); i++) {  
            HSSFRow row= sheet.getRow(i);  
            if(row!=null){  
                //装载obj  
                 ret.add(dataObj(obj,row));  
            }  
        }  
        return ret;  
    }  
      
    /** 
     * 处理val（暂时只处理string和number，可以自己添加自己需要的val类型） 
     * @param hssfCell 
     * @return 
     */  
    public static String getVal(HSSFCell hssfCell) {  
        if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {  
            return hssfCell.getStringCellValue();  
        } else {  
        	String num = String.valueOf(hssfCell.getNumericCellValue());
        	if(num.indexOf(".") != -1){
        		num = num.substring(0, num.indexOf("."));
        	}
            return num;  
        }  
    }  
}

生成网络下载

创建Workbook，创建sheet，创建行row，创建单元格cell，设置单元格样式CellStyle，单元格赋值setCellValue。设置响应头，以输出流形式传出。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
List<Map<String , Object>> list = commonSqlService.mapSql(sql);//总记录
	//生成excel
	HSSFWorkbook wb = new HSSFWorkbook();  
    HSSFSheet sheet = wb.createSheet("用户积分记录");  
    HSSFRow row = sheet.createRow(0);
   	HSSFCellStyle cellStyle = wb.createCellStyle();
   	cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
  	HSSFCell cell = row.createCell(0);
   	HSSFCell cell1 = row.createCell(1);
   	HSSFCell cell2 = row.createCell(2);
   	HSSFCell cell3 = row.createCell(3);
   	HSSFCell cell4 = row.createCell(4);
   	cell.setCellValue("所属支部");
   	cell1.setCellValue("姓名");
    cell2.setCellValue("电话");
    cell3.setCellValue("积分");
    cell4.setCellValue("等级");
    int rowNumCount = 1;
  	for (Map<String, Object> map : list) {
  		 
  		row = sheet.createRow(rowNumCount++);
  		cell = row.createCell(0);
      	cell1 = row.createCell(1);
      	cell2 = row.createCell(2);
      	cell3 = row.createCell(3);
      	cell4 = row.createCell(4);

        cell.setCellValue(map.get("deptName").toString());
       	cell1.setCellValue(map.get("vcName").toString());
       	cell2.setCellValue(map.get("vcTel").toString());
       	cell3.setCellValue(map.get("vcScore").toString());
       	cell4.setCellValue(map.get("vcLevelName").toString());
  	}
    String fileName = "用户积分记录" + ".xls"; 
    String agent = request.getHeader("USER-AGENT").toLowerCase();  
    response.setContentType("applicationnd.ms-excel");  
    //String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");  
    if (agent.contains("firefox")) {
	    
        //火狐浏览器特殊处理  
        response.setCharacterEncoding("utf-8");  
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "UTF-8"));  
    } else {
	    response.setHeader("content-disposition", "attachment;filename=" + fileName);  
    }  
	OutputStream os = response.getOutputStream();  
    wb.write(response.getOutputStream());  
    os.close();  
    wb = null;

注意点

  • XSSF不能读取Excel2003以前（包括2003）的版本，HSSF能读取97-2003的版本，错误加载会抛出异常

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

CATALOG
  1. 1. POI概念
  2. 2. Apache POI组件
  3. 3. EXCEL(HSSF，XSSF)常用API
  4. 4. 使用场景,代码举例
    1. 4.1. 加载本地文件
    2. 4.2. 加载网络文件
    3. 4.3. 生成网络下载
  5. 5. 注意点
Total : 33
2018
1024
Pattern Adapter Pattern Principle Composite Pattern Bridge Pattern Decorator Pattern Facade Pattern Flyweight Pattern excel poi Proxy Pattern transaction acid jdbc mysql oracle spring database mvvm front end isolation level Pessimistic Lock Optimistic Lock ajax jquery angular js mybatis primarykey pay we chat problem 闲扯