在平时开发项目时对数据的处理肯定是少不了的。对于数据处理也是很多程序员比较头疼的问题，就比如项目中的数据是如何添加进去呢？一条一条的录入？好像又有点不太友好，数据多了效率太低了，最关键的是甲方爸爸肯定不会满意的。
这时我们可以使用POI来操作Excel表格，可以通过POI来把Excel中的数据批量导入到数据库中，从而简化操作，提高效率。反之我们还可以通过POI把数据库中的数据导出到Excel表格中。
POI 简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
POI依赖文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>POI常用类
| 类名 | 说明 |
|---|---|
| HSSFWorkbook | Excel的文件对象 |
| HSSFSheet | Excel的表单 |
| HSSFRow | Excel的行 |
| HSSFCell | Excel的格子单元 |
| HSSFFont | Excel字体 |
| HSSFDataFormat | 格子单元的日期格式 |
| HSSFHeader | Excel文档Sheet的页眉 |
| HSSFFooter | Excel文档Sheet的页脚 |
| 样式： | |
| HSSFCellStyle | cell样式 |
| 辅助操作包括： | |
| HSSFDateUtil | 日期 |
| HSSFPrintSetup | 打印 |
| HSSFErrorConstants | 错误信息表 |
数据导出
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）表单组成，一个sheet是由多个row（HSSFRow）行组成，一个row是由多个cell（HSSFCell）单元格组成。
所以对应的操作步骤如下：
- 用HSSFWorkbook打开或者创建Excel文件对象。
- 用HSSFWorkbook对象返回或者创建Sheet对象。
- 用Sheet对象返回行对象，用行对象得到Cell对象。
- 对Cell对象读写。
下面来看一下数据导出功能
前台页面只需要给出一个跳转到后台导出方法的链接即可。
<button class="layui-btn layui-btn-sm" onclick="window.location.href='staff/export'">导出</button>后台对应导出的方法，代码如下：
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
//查询数据库中的数据
List<Staff> staffList = staffService.findAll();
//创建excel的文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建员工信息工作薄，表单
HSSFSheet sheet = workbook.createSheet("员工信息");
//在sheet里创建第一行，参数为行索引(excel的行)，从0开始
HSSFRow row1 = sheet.createRow(0);
//创建单元格excel的单元格，参数为列索引，从0开始
HSSFCell cell = row1.createCell(0);
//设置单元格内容，标题第一行（可以不设置，根据项目需求）
cell.setCellValue("员工信息");
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列，一行标题合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//设置列名（每列的小标题）
String[] fieldnames = {"姓名", "部门", "年龄", "工号"};
//在sheet里创建第二行，第一行设置了标题
HSSFRow row2 = sheet.createRow(1);
for(int i = 0; i < fieldnames.length; i++) {
row2.createCell(i).setCellValue(fieldnames[i]); //设置列名
}
//填充导出的数据到Excel中
for(int i = 0; i < staffList.size(); i++){
HSSFRow rows = sheet.createRow(i + 2);
HSSFCell cellName = rows.createCell(0);
cellName.setCellValue(staffList.get(i).getName());
HSSFCell cell1Dept = rows.createCell(1);
cell1Dept.setCellValue(staffList.get(i).getDept());
HSSFCell cell1Age = rows.createCell(2);
cell1Age.setCellValue(staffList.get(i).getAge());
HSSFCell cell1Number = rows.createCell(3);
cell1Number.setCellValue(staffList.get(i).getNumber());
}
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//filename可以设置Excel文件的名称
response.setHeader("Content-disposition", "attachment; filename=staff.xls");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}
注释怎么清楚，就不用多解释了吧，(#^ . ^#)。
导出结果如下：
设置样式
上面导出的内容大家也看到了，就是最原始的样式。其实在导出时我们也可以对其Excel设置相应的样式。
1、合并单元格
使用HSSFSheet类中的addMergedRegion(CellRangeAddress region)方法，上面导出也用到过。
参数CellRangeAddress表示合并的区域，方法如下：
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)其中参数依次表示起始行，截至行，起始列， 截至列。有兴趣的小伙伴可以点进封装类中看一下。
2、设置单元格的大小
HSSFSheet sheet = workbook.createSheet("员工信息");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("张三");
sheet.setColumnWidth(1, 256 * 50); //设置第一列的宽度，为50个字符宽度
row.setHeightInPoints(50); //设置一行的高度setColumnWidth方法和setHeightInPoints方法适合这是部分的样式，如果需要设置全部样式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽和行高。
3、设置单元格样式
单元格样式是通过HSSFCellStyle类来设置的，所以我们需要先得到HSSFCellStyle 类
HSSFCellStyle style = workbook.createCellStyle()3.1、设置水平对齐方式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中水平对齐相关参数
- 如果是左侧对齐就是 HSSFCellStyle.ALIGN_FILL。
- 如果是居中对齐就是 HSSFCellStyle.ALIGN_CENTER。
- 如果是右侧对齐就是 HSSFCellStyle.ALIGN_RIGHT。
- 如果是跨列举中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION。
- 如果是两端对齐就是 HSSFCellStyle.ALIGN_JUSTIFY。
3.2、设置垂直对齐方式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中垂直对齐相关参数
- 如果是靠上就是 HSSFCellStyle.VERTICAL_TOP。
- 如果是居中就是 HSSFCellStyle.VERTICAL_CENTER。
- 如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM。
- 如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY。
3.3、设置边框
style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框
style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色
style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色
style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色
style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色3.4、应用样式
//用在某一个单元格中
cell.setCellStyle(cellStyle);
//用在一行中，设置一行的样式
row.setRowStyle(cellStyle);4、设置字体样式
字体样式是通过HSSFFont类来设置的，所以我们需要先得到HSSFFont类
HSSFFont font = workbook.createFont()设置样式
font.setFontName("华文行楷");//设置字体名称
font.setFontHeightInPoints((short)28);//设置字号
font.setColor(HSSFColor.RED.index);//设置字体颜色
font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
font.setStrikeout(true);//设置删除线下划线选项值：
- 单下划线 FontFormatting.U_SINGLE
- 双下划线 FontFormatting.U_DOUBLE
- 会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING
- 会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING
- 无下划线 FontFormatting.U_NONE
上标下标选项值：
- 上标 FontFormatting.SS_SUPER
- 下标 FontFormatting.SS_SUB
- 普通，默认值 FontFormatting.SS_NONE
应用样式
//字体也是单元格格式的一部分，所以从属于HSSFCellStyle
// 将字体对象赋值给单元格样式对象
style.setFont(font);
// 将单元格样式应用于单元格
cell.setCellStyle(cellStyle);数据导入
导入数据时，页面需要给出一个选择文件的标签，页面就不多说了，只要有一个上传的标签即可。
这是用layui写的一个简单的页面，代码如下：
<style type="text/css">
#updateFile{
margin: 20px 0px 20px 50px;
}
</style>
<form id="importForm" method="post" class="layui-form" >
<div class="layui-form-item" style="margin: 0px;">
<button type="button" name="file" class="layui-btn layui-btn-normal" id="updateFile">选择文件</button>
</div>
<div class="layui-form-item">
<div class="layui-input-block" style="margin-left: 50px;">
<input class="layui-btn layui-btn-normal" id="upload" value=" 导 入 " />
<a href="staff/exportmoban" rel="external nofollow" >下载模板</a>
</div>
</div>
</form>
<script type="text/javascript">
layui.use(['form', 'upload'], function(){
var upload = layui.upload;
upload.render({
elem: '#updateFile',
url: 'staff/excelimport',
auto: false,
accept: 'file',
exts: 'xls|excel|xlsx',
bindAction: '#upload',
done: function(res){ //导出成功后回调
}
});
});
</script>其中下载模板和导出数据基本一样，只需要创建一个Excel给出一条样式数据即可，代码如下：
@RequestMapping(value = "exportmoban")
@ResponseBody
public void exportmoban(HttpServletRequest request, HttpServletResponse response) throws Exception{
//创建excel的文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建员工信息工作薄，表单
HSSFSheet sheet = workbook.createSheet("员工信息");
//在sheet里创建第一行，参数为行索引(excel的行)，从0开始
HSSFRow row1 = sheet.createRow(0);
//创建单元格excel的单元格，参数为列索引，从0开始
HSSFCell cell = row1.createCell(0);
//设置单元格内容，标题第一行（可以不设置，根据项目需求）
cell.setCellValue("员工信息");
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列，一行标题合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//设置列名（每列的小标题）
String[] fieldnames = {"姓名", "部门", "年龄", "工号"};
//在sheet里创建第二行，第一行设置了标题
HSSFRow row2 = sheet.createRow(1);
for(int i = 0; i < fieldnames.length; i++) {
row2.createCell(i).setCellValue(fieldnames[i]); //设置列名
}
//模板数据
HSSFRow row3 = sheet.createRow(2);
row3.createCell(0).setCellValue("王五");
row3.createCell(1).setCellValue("软件部");
row3.createCell(2).setCellValue(“18“);
row3.createCell(3).setCellValue("003");
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//filename可以设置Excel文件的名称
response.setHeader("Content-disposition", "attachment; filename=staff.xls");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}模板下载如图。
导入数据，我们只需要选择一个有数据的Excel表格（数据格式需要和模板中格式一样），点击导入即可。
后台对应导入数据的excelimport方法，代码如下：
@RequestMapping(value = "/excelimport")
@ResponseBody
public boolean excelimport(MultipartFile file) {
// 实例化工具类
ImportExcel excelReader = new ImportExcel();
try {
InputStream is = file.getInputStream();
// 导入excel
excelReader.readExcelContent(is);
Map<Integer, String> map = excelReader.readExcelContent(is);
//遍历数据保存
//因为第一行和第二行是标题，所以从2开始
for (int i = 2; i <= map.size() + 1; i++) {
String[] scoreArray = map.get(i).split("-");
if (scoreArray.length > 0) {
String name = scoreArray[0];
String dept = scoreArray[1];
Integer age = Integer.parseInt(scoreArray[2]);
String number = scoreArray[3];
Staff staff = new Staff();
staff.setName(name);
staff.setDept(dept);
staff.setAge(age);
staff.setNumber(number);
//保存
staffService.save(staff);
}
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}注： 如果对数据需要做一些效验的话可以放在for循环中处理，这里只是写一个小案例，没有添加任何效验，根据项目需求自己添加。
其中ImportExcel类是一个处理导入的Excel数据的封装类。代码如下：
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class ImportExcel {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
//得到首行的row
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
//e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
//由于第0行和第一行已经合并了 在这里索引从2开始
row = sheet.getRow(2);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 2; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
str += getCellFormatValue(row.getCell((short) j)).trim() + "-";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
}
导入的数据：
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
