java的EXcel模板的下载、导入和导出
声明：本文章仅基于个人粗略整理总结，如有不足之处，欢迎指出。
基于ssm+layui开发，所需jar包
<!-- poi3.9：导出excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>1、下载Excel模板
前端：
<div class="layui-form-item"></div>
<form action="/device/downloadExcel" method="get">
<div class="layui-form-item">
<label class="layui-form-label">下载模板:</label>
<div class="layui-input-inline">
<input class="layui-btn " type="submit" value="下载模板">
</div>
</form>
<label class="layui-form-label"></label>
<!-- -->
<form class="layui-form" method="POST" enctype="multipart/form-data"
id="form1" action="${ctx}/device/uploadExcel">
<div class="layui-form-item">
<label class="layui-form-label">上传文件:</label>
<div class="layui-input-inline">
<input id="upfile" type="file" name="upfile" calss="layui-btn">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label"> </label>
<div class="layui-input-inline">
<input class="layui-btn " type="submit" value="批量导入Excel数据"
onclick="return checkData()">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">注意事项:</label>
<div class="layui-input-block">
<span style="color: red"></br>1、请根据模板填写数据。</br>2、非必填列不要删除。</br>3、不要调整各个列的先后顺序。</span>
</div>
</div>
</form>
</body>
<script type="text/javascript" src="${ctx }/layui/layui.js"></script>
<script language="javascript">
layui.config({
base : "js/"
}).use(['form','layer','jquery','laydate'],function(){
var form = layui.form,
layer = parent.layer === undefined ? layui.layer : parent.layer,laydate = layui.laydate
$ = layui.jquery;
});
//JS校验form表单信息
function checkData() {
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if ("" == fileDir) {
top.layer.msg("选择需要导入的Excel文件！", {
icon : 5
});
return false;
}
if (".xls" != suffix && ".xlsx" != suffix) {
top.layer.msg("文件格式有误！</br>选择Excel格式的文件导入！", {
icon : 5
});
return false;
}
$("#form1").attr('disabled','disabled');
var index = top.layer.msg('数据提交中，请稍候', {
icon : 16,
shade : 1
});
$('#form1').ajaxSubmit({
url : ctx + '/device/uploadExcel',
dataType:'text',//服务器返回的数据类型 可选XML ,Json jsonp script html text等
error:function(){
$("#form1").removeAttr('disabled');
top.layer.msg("导入excel出错！", {
icon : 5
});
},
success:function(d){
//alert(d);
top.layer.msg(d, {icon : 1
});
$("#upfile").val("");
$("#form1").removeAttr('disabled');
//刷新父页面
//parent.location.reload();
}
}); //form1
return true;
}
</script>后端：
模板文件存放位置：
代码：
@RequestMapping(value="downloadExcel", method = RequestMethod.GET)//method = RequestMethod.GET将数据传递给前端
public void downloadExcel(HttpServletResponse response,HttpServletRequest request)throws IOException {
//获取输入流，原始模板位置
String filePath = getClass().getResource("/resource/template/xx模板.xlsx" ).getPath();
InputStream bis = new BufferedInputStream(new FileInputStream(new File(filePath)));
//假如以中文名下载的话，设置下载文件名称
String filename = "导入模板.xls";
//转码，免得文件名中文乱码
filename = URLEncoder.encode(filename,"UTF-8");
//设置文件下载头
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
//1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("multipart/form-data");
BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
int len = 0;
while((len = bis.read()) != -1){
out.write(len);
out.flush();
}
out.close();
}2、导入数据
controller
@RequestMapping(value = "uploadExcel",method = { RequestMethod.GET , RequestMethod.POST} )
@ResponseBody
public ResponseEntity<String> ajaxUploadExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
String commentHTML=null;
HttpHeaders responseHeaders = new HttpHeaders();
responseHeaders.setContentType(new MediaType("text","html",Charset.forName("UTF-8")));
try {
commentHTML = deviceService.importDeviceExcel(request, response);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (StringUtils.isNotBlank(commentHTML)) {
return new ResponseEntity<String>(commentHTML, responseHeaders, HttpStatus.OK);
}else {
return new ResponseEntity<String>("导入失败！", responseHeaders, HttpStatus.OK);
}
}Service：
private String no="0";//excel里的序号
public String importDeviceExcel(HttpServletRequest request, HttpServletResponse response)throws Exception{
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream in = null;
List<List<String>> listob = null;
MultipartFile file = multipartRequest.getFile("upfile");
if (file.isEmpty()) {
throw new Exception("文件不存在！");
}
in = file.getInputStream();
listob = ImportExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
in.close();
int successCount=0;
int failCount=0;
//导入字段条件判断
ArrayList<ArrayList<String>> fieldData =new ArrayList<ArrayList<String>>();//必填值为空的数据行
Device device = new Device();//格式无误的数据行
int lisize=listob.size();
for (int i = 0; i < lisize; i++) {//这部分for循环可根据需求选择忽视
List<String> lo = listob.get(i);
if (!no.equals(lo.get(0))) {//no为EXCEL的序列号，本例中excel的第一行是各列的标题，不导入，从第二行开始
no=lo.get(0);
if (StringUtils.isNotBlank(lo.get(1))){//判断每行某个数据是否符合规范要求
//符合要求，插入到数据库Device表中
saveDeviceFromExcel(device,lo);
lo=null;
successCount+=1;
}else{
//需要另外生成excel的不规范行，构造excel的数据
ArrayList<String> dataString=new ArrayList<String>();
for(int j=0;j<lo.size();j++){
String a=String.valueOf(lo.get(j));
dataString.add(a);
}
fieldData.add(dataString);
}
}
}
failCount=fieldData.size();
String o=null;
if (failCount>0) {
//不符合规范的重新生成EXCEL表
insExcel(fieldData,response);
o="成功导入"+successCount+"行，未成功导入"+failCount+"行,请在有误数据表内查看!";
}else{
//o="全部导入成功！";
}
no="0";
return o;
}
public void insExcel( ArrayList<ArrayList<String>> fieldData,HttpServletResponse response) throws Exception{
if(fieldData!=null&&fieldData.size()>0){//如果存在不规范行，则重新生成表
//使用ExcelFileGenerator完成导出
LoadExcelUtil loadExcelUtil = new LoadExcelUtil(fieldData);
OutputStream os = response.getOutputStream();
//导出excel建议加上重置输出流，可以不加该代码，但是如果不加必须要保证输出流中不应该在存在其他数据，否则导出会有问题
response.reset();
//配置：//文件名
String fileName = "有误数据表（"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+"）.xls";
//处理乱码
fileName = new String(fileName.getBytes("gbk"),"iso-8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+fileName);
response.setBufferSize(1024);
//导出excel的操作
loadExcelUtil.expordExcel(os);
}
}导入时读取EXCEL工具类
基本思路：
将list集合的中的数据导入到excel表中的步骤如下：
1、创建 WorkBook对象
2、获取要导出的表格的总记录数
3、根据总记录和每个excels的所容纳的记录数，进行分页处理，得出分为几个sheet的excels
4、得出sheet，进行每个sheet的循环处理
4.1、通过workbook对象进行创建createSheet,HSSFSheet对象
4.2、拥有了HSSFSheet对象，创建sheet.createRow行表头，单独的一行即数据说明
4.3、创建表头行HSSFRow，开始导入表头行数据，开始对表头这一行进行数据循环填充
4.4、接着开始循环每一行数据，对总记录进行循环，如果超过最大容量，那么最大值就是它，否则就是实际记录数
4.5、开始进入循环，然后都要创建一个行对象HSSFRow,根据现在的页数取出第几条数据集合，进行循环
4.5.1、也是通过HSSFRow，创建HSSFCell列对象
4.5.2、通过cell对象设置相应的样式或者编码
4.5.3、编码设置完，就可以进行每列数据的填充
5、返回workBook对象
package com.irs.util;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @ClassName: ImportExcelUtil
* @Description: excel数据导入数据库
* @Author:
* @Version: V1.00 （版本号）
* @CreateDate:
*/
public class ImportExcelUtil {
private final static String excel2003L = ".xls"; // 2003- 版本的excel
private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
/**
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
*
* @param in,fileName
* @return
* @throws IOException
*/
public static List<List<String>> getBankListByExcel(InputStream in, String fileName) throws Exception {
List<List<String>> list = null;
// 创建Excel工作薄
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<String>>();
// 遍历Excel中所有的sheet work.getNumberOfSheets()
for (int i = 0; i < 1; i++) {
sheet = work.getSheetAt(i);
if (sheet != null) {
// 遍历当前sheet中的所有行
int totalRow = sheet.getPhysicalNumberOfRows();
for (int j = 2; j < totalRow; j++) {
row = sheet.getRow(j);
if (row != null || row.getFirstCellNum() != j) {
// 遍历所有的列
List<String> li = new ArrayList<String>();
int a=row.getLastCellNum();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
String callCal = null;
if (cell == null || cell.equals("") || cell + "" == "") {
callCal = "";
} else {
callCal = getCellValue(cell) + "";
}
li.add(callCal);
}
list.add(li);
li = null;
//System.out.println(list.toString());
}
}
return list;
}
}
return null;
}
/**
*
* @Title: getWorkbook
* @Description: 根据文件后缀，自适应上传文件的版本
* @Author:
* @Version: V1.00 （版本号）
* @CreateDate:
* @Parameters: @param inStr
* @Parameters: @param fileName
* @Parameters: @return
* @Parameters: @throws Exception
* @Return Workbook
* @Throws
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
wb = new HSSFWorkbook(inStr); // 2003-
} else if (excel2007U.equals(fileType)) {
wb = new XSSFWorkbook(inStr); // 2007+
} else {
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
*
* @Title: getCellValue
* @Description: 对表格中数值进行格式化
* @Author
* @Version: V1.00 （版本号）
* @CreateDate:
* @Parameters: @param cell
* @Parameters: @return
* @Return Object
* @Throws
*/
public static Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
3、导出数据
导出工具类
package com.irs.util;
import java.io.OutputStream;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class LoadExcelUtil {
private final int SPLIT_COUNT = 100; //Excel每个工作簿的行数
private ArrayList<String> fieldName = null; //excel标题数据集
private ArrayList<ArrayList<String>> fieldData = null; //excel数据内容
private HSSFWorkbook workBook = null;
/**
* 构造器
* @param fieldName 结果集的字段名
* @param data
*/
public LoadExcelUtil(ArrayList<ArrayList<String>> fieldData) {
this.fieldData = fieldData;
}
/**
* 创建HSSFWorkbook对象
* @return HSSFWorkbook
*/
public HSSFWorkbook createWorkbook() {
workBook = new HSSFWorkbook();//创建一个工作薄对象
int rows = fieldData.size();//总的记录数
int sheetNum = 0; //指定sheet的页数
if (rows % SPLIT_COUNT == 0) {
sheetNum = rows / SPLIT_COUNT;
} else {
sheetNum = rows / SPLIT_COUNT + 1;
}
for (int i = 1; i <= sheetNum; i++) {//循环2个sheet的值
HSSFSheet sheet = workBook.createSheet("Page " + i);//使用workbook对象创建sheet对象
/**************对标题添加样式begin********************/
HSSFRow headRow = sheet.createRow((short) 0); //创建行，0表示第一行（本例是excel的标题）
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//参数说明：1：开始行 2：结束行 3：开始列 4：结束列
headRow.createCell(0).setCellValue("序号");
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//参数说明：1：开始行 2：结束行 3：开始列 4：结束列
//设置样式-颜色
HSSFCellStyle style = workBook.createCellStyle();
style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//水平居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = workBook.createFont();//创建字体对象
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//字体颜色变红
font.setColor(HSSFColor.RED.index);
//如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体
style.setFont(font);
headRow.createCell(1).setCellValue("名称");
//headRow.createCell(1).setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));//参数说明：1：开始行 2：结束行 3：开始列 4：结束列
headRow.createCell(2).setCellValue("所属馈线");
//headRow.createCell(2).setCellStyle(style);
//创建绘图对象
HSSFPatriarch p=sheet.createDrawingPatriarch();
//创建单元格对象,批注插入到4行,1列,B5单元格
HSSFCell cell=sheet.createRow(4).createCell(1);
//插入单元格内容
cell.setCellValue(new HSSFRichTextString("批注"));
//获取批注对象
//(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
//输入批注信息
comment.setString(new HSSFRichTextString("插件批注成功!插件批注成功!"));
//添加作者,选中B5单元格,看状态栏
comment.setAuthor("toad");
//将批注添加到单元格对象中
cell.setCellComment(comment);
//分页处理excel的数据，遍历所有的结果
for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
if (((i - 1) * SPLIT_COUNT + k) >= rows)//如果数据超出总的记录数的时候，就退出循环
break;
HSSFRow row = sheet.createRow((short) (k + 2));//创建1行
//分页处理，获取每页的结果集，并将数据内容放入excel单元格
ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);
for (int n = 0; n < rowList.size(); n++) {//遍历某一行的结果
HSSFCell cell = row.createCell( n);//使用行创建列对象
if(rowList.get(n) != null){
cell.setCellValue((String) rowList.get(n).toString());
}else{
cell.setCellValue("");
}
}
}
}
return workBook;
}
public void expordExcel(OutputStream os) throws Exception {
workBook = createWorkbook();
workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出
os.close();
}
}
另外：layui2.4之后，可根据调用toolbarDemo头工具栏事件，自动实现简单的数据导出
https://www.layui.com/demo/table/toolbar.html
其他补充：
Poi导出版本优缺点：
| 版本 | Excel后缀 | 优点 | 缺点 |
| HSSFWorkbook （03之前） | .xls | 不会内存溢出 | 最多只能导出 65535行 |
| XSSFWorkbook （03-07之间） | .xlsx | 容易内存溢出 | 最多可以导出104w条数据 |
| SXSSFWorkbook-POI3.8（07之后） | .xlsx | 既然一部分数据持久化到了硬盘中，且不能被查看和访问那么就会导致： 1、在同一时间点我们只能访问一定数量内存中存储的数据; 2、不再支持sheet.clone() 3、不再支持对公式的求值，在硬盘中的数据没法读取到内存中进行计算 4、在使用模板方式下载数据的时候，不能改动表头 |
|
使用情况：
1、当导入导出的数据不超过7w的情况下，可以使用 HSSFWorkbook 或者 XSSFWorkbook都行；
2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式，公式，格式等操作的情况下，推荐使用SXSSFWorkbook;
3、当数据量查过7w，并且我们需要操做Excel中的表头，样式，公式等，这时候我们可以使用 XSSFWorkbook 配合进行分批查询，分批写入Excel的方式来做；
百万数据导入导出
思路：利用阿里的POI封装工具EasyExcel
- 首先在查询数据库层面，需要分批进行查询（比如每次查询20w）
- 每查询一次结束，就使用EasyExcel工具将这些数据写入一次；
- 当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中；
- 如此循环直到数据全部导出到Excel完毕。
ps:我们需要计算Sheet个数，以及循环写入次数。特别是最后一个Sheet的写入次数
因为你不知道最后一个Sheet会写入多少数据，可能是100w，也可能是25w因为我们这里的500w只是模拟数据，有可能导出的数据比500w多也可能少
ps:我们需要计算写入次数，因为我们使用的分页查询，所以需要注意写入的次数。
其实查询数据库多少次就是写入多少次