基本概念
java操纵excel文件常用的有jxl和poi两种方式，jxl不支持.xlsx，而poi支持.xlsx。本文主要介绍poi方式。
Apache POI是一种流行的API，它允许程序员使用Java程序创建，修改和显示MS Office文件。这由Apache软件基金会开发使用Java分布式设计或修改Microsoft Office文件的开源库。它包含类和方法对用户输入数据或文件到MS Office文档进行解码。
poi一般常用的有HSSFWorkbook和XSSFWorkbook两个实现类。
HSSFWorkbook是针对.xls文件，这种方式导出的文件格式为office 2003专用格式，优点是导出数据速度快，但是最多65536行数据
XSSFWorkbook是针对.xslx文件，这种方式导出的文件格式为office 2007专用格式，即.xlsx，优点是导出的数据不受行数限制，缺点导出速度慢
内置其他的api:
HSSF : 读写 Microsoft Excel XLS 格式文档
XSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
SXSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
HWPF : 读写 Microsoft Word DOC 格式文档
HSLF : 读写 Microsoft PowerPoint 格式文档
HDGF : 读 Microsoft Visio 格式文档
HPBF : 读 Microsoft Publisher 格式文档
HSMF : 读 Microsoft Outlook 格式文档
导入依赖
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
操作示例
//1.创建Excel对象
XSSFWorkbook wb = new XSSFWorkbook();
HSSFWorkbook wb = new HSSFWorkbook();
//2.创建Sheet对象
Sheet sheet = wb.createSheet();
Row createRow(int rownum);// 创建行
Row getRow(int rownum);// 获取行
int getPhysicalNumberOfRows();// 获取工作单行数
int addMergedRegion(CellRangeAddress region);// 设置合并单元格范围
void autoSizeColumn(int column);// 设置默认单元格列宽
void setColumnWidth(int columnIndex, int width);// 设置单元格列宽(1/256)
void setDefaultRowHeightInPoints(float height);// 设置默认单元格行高
//3.创建行对象(索引从0开始)
Row nRow = sheet.createRow(0);
//4.设置行高和列宽
nRow.setHeightInPoints(26.25f);
sheet.setColumnWidth(1,26*256); //(列的索引,列宽*256(理解为固定写法))
//5.创建单元格对象(索引从0开始)
Cell nCell = nRow.createCell(0);
//6.设置单元格内容
nCell.setCellValue("dinTalk");
void setCellValue(RichTextString value);// 设置富文本
void setCellFormula(String formula) throws FormulaParseException;// 设置公式
void setCellType(CellType cellType);// 设置单元格类型 _NONE NUMERIC STRING FORMULA BLANK BOOLEAN ERROR
String getStringCellValue();
double getNumericCellValue();// 获取数值类型
String getCellFormula();// 获取公式
//7.创建单元格样式对象
CellStyle style = wb.createCellStyle();
//8.创建字体对象
Font font = wb.createFont();
//9.设置字体和其大小及效果
font.setFontName("黑体");
font.setFontHeightInPoints((short)12);
font.setBold(true); //加粗
//10.设置样式
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER); //横向居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
style.setBorderTop(BorderStyle.THIN); //上细线
style.setBorderBottom(BorderStyle.THIN); //下细线
style.setBorderLeft(BorderStyle.THIN); //左细线
style.setBorderRight(BorderStyle.THIN); //右细线
void setFontName(String name);// 设置字体名称
public void setBold(boolean bold);// 设置是否加粗
void setFontHeightInPoints(short height);// 设置字体大小
//11.为单元格应用样式
nCell.setCellStyle(style);
//12. Workbook
Sheet createSheet();// 创建表单
Sheet createSheet(String sheetname);
Sheet getSheetAt(int index);// 获取表单
Sheet getSheet(String name);
CellStyle createCellStyle();// 创建单元格样式
Font createFont();// 创建字体
void write(OutputStream stream);// 写入输出流
void close();// 关闭
13. 合并单元格
rangeAdd = new CellRangeAddress(0, 6, 0, 0);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);//下细线
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
操作Execl工具类
ExcelExportServiceForZxfjkc.java:
/**
* @author 小牛
* @Time 2021-07-20
* @Description:解析excel工具类
*/
@Service
public class ExcelExportServiceForZxfjkc {
@Autowired
private TpDmGyGjService tpDmGyGjService;
private static Logger logger = LoggerFactory.getLogger(ExcelExportServiceForPartenrs.class);
/**
* 将数据流写入到文件中
*
* @param filename 为单个excel的路径和excel的名称，
* @param inputStream inputStream就是获取的list数据流
* @return
* @author zhangjun
*/
public static int execute(String filename, InputStream inputStream) {
logger.info("开始进行数据解析...");
int success = 1;
FileOutputStream outStream = null;
try {
File blobFile = new File(filename);
File fileParent = blobFile.getParentFile();
if(!fileParent.exists()){
fileParent.mkdirs();
}
blobFile.createNewFile();
outStream = new FileOutputStream(blobFile);
byte[] buffer = new byte[2048];
while ((inputStream.read(buffer)) != -1) {
outStream.write(buffer);
}
outStream.flush();
} catch (Exception e) {
logger.error(e.getMessage(), e);
success = 0;
} finally {
if (outStream != null) {
try {
outStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
logger.info("ExcelExportServiceForZxfjkc.execute info={}","解析数据(数据转成文件)完成...");
return success;
}
}
//获取代码 对应的 证件类型
public String getZzlxName(int dm) {
//居民身份证 201
// 中国护照227
// 港澳居民来往内地通行证210
// 港澳居民居住证 237
// 台湾居民来往大陆通行证213
// 台湾居民居住证 238
// 外国护照208
// 外国人永久居住证233
// 外国人工作许可证（A类）239
// 外国人工作许可证（B类）240
// 外国人工作许可证（C类）241
String zzlx="";
switch (dm) {
case 201:
zzlx= "居民身份证";
break;
case 227:
zzlx= "中国护照";
break;
case 210:
zzlx= "港澳居民来往内地通行证";
break;
case 237:
zzlx = "港澳居民居住证";
break;
case 213:
zzlx = "台湾居民来往大陆通行证";
break;
case 238:
zzlx = "台湾居民居住证";
break;
case 208:
zzlx = "外国护照";
break;
case 233:
zzlx = "外国人永久居住证";
break;
case 239:
zzlx = "外国人工作许可证（A类）";
break;
case 240:
zzlx = "外国人工作许可证（B类）";
break;
case 241:
zzlx = "外国人工作许可证（C类）";
break;
default:
zzlx = "未知证件代码，请核实";
}
return zzlx;
}
/**
* 将多个Excel打包成zip文件
*
* @param srcFile 该路径下的所有需要打成Zip的文件
* @param zipFile 压缩的Zip文件
* @author zhangjun
*/
public static void zipFiles(List<File> srcFile, File zipFile) {
byte[] buf = new byte[1024];
try {
// 创建压缩文件 Create the ZIP file
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipFile));
// 打包该文件夹下的所有文件 Compress the files
int size = srcFile.size();
logger.info("ExcelExportServiceForZxfjkc.zipFiles info={}","该临时目录下共有:"+size+"文件!");
int num =1;
for (int i = 0; i < size; i++) {
logger.info("ExcelExportServiceForZxfjkc.zipFiles info={}","开始压缩第:"+num+"文件...");
File file = srcFile.get(i);
FileInputStream in = new FileInputStream(file);
// 将Zip文件添加到输出流中 Add ZIP entry to output stream.
out.putNextEntry(new ZipEntry(file.getName()));
// 将输出流安装字节流的形式读取到Zip压缩文件中 Transfer bytes from the file to the ZIP file
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
// Complete the entry
out.closeEntry();
in.close();
num++;
}
logger.info("ExcelExportServiceForZxfjkc.zipFiles info={}","第"+size+"个文件压缩成功..全部文件压缩完成!");
// Complete the ZIP file
out.close();
} catch (IOException e) {
logger.info("ExcelExportServiceForZxfjkc.zipFiles info={}","压缩文件出现异常...");
logger.error(e.getMessage(), e);
}
}
/**
* 删除目录下所有的文件;
* @param file 服务器目录地址 例如:
* @author zhangjun
*/
public static boolean deleteExcelPath(File file) {
logger.info("ExcelExportServiceForZxfjkc.deleteExcelPath info={}","开始删除临时目录中的文件...");
if (file.isDirectory()) {
logger.info("ExcelExportServiceForZxfjkc.deleteExcelPath info={}","该文件属于文件夹,优先删除文件夹下的文件");
String[] children = file.list();
int length = children.length;
//递归删除目录中的子目录下
int number= 1;
for (int i = 0; i < length; i++) {
boolean success = deleteExcelPath(new File(file, children[i]));
if (!success) {
return false;
}
System.out.println("临时目录及文件删除成功...");
logger.info("ExcelExportServiceForZxfjkc.deleteExcelPath info={}","删除第"+number+"文件");
number++;
}
}
// 目录此时为空，可以删除
return file.delete();
}
/**
* 将Zip文件向浏览器发起是否下载请求
* @param response 响应
* @param serverPath 服务器路径
* @param str Zip文件名
* @author zhangjun
*/
public static void downFile(HttpServletResponse response, String serverPath, String str) {
try {
String path = serverPath + str;
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","临时文件目录为:"+path);
File file = new File(path);
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","判断file.exists():"+true+"的话,将发起下载请求...");
if (file.exists()) {
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","开始向浏览器发起下载请求...");
InputStream ins = new FileInputStream(path);
// 放到缓冲流里面
BufferedInputStream bins = new BufferedInputStream(ins);
// 获取文件输出IO流
OutputStream outs = response.getOutputStream();
BufferedOutputStream bouts = new BufferedOutputStream(outs);
// 设置response内容的类型
response.setContentType("application/octet-stream");
// 设置头部信息
response.setHeader(
"Content-disposition",
"attachment;filename="
+ URLEncoder.encode(str, "UTF-8"));
int bytesRead = 0;
byte[] buffer = new byte[1024];
//开始向网络传输文件流
while ((bytesRead = bins.read(buffer)) != -1) {
bouts.write(buffer);
}
// 这里一定要调用flush()方法
bouts.flush();
response.flushBuffer();
ins.close();
bins.close();
outs.close();
bouts.close();
} else {
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","判断file.exists():"+false);
//response.sendRedirect("/admin/login");
}
} catch (IOException e) {
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","向浏览器发起下载请求异常...");
logger.error(e.getMessage(), e);
} finally {
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","下载请求发出以后,执行删除临时目录及文件...");
File files = new File(serverPath);
//删除临时目录
deleteExcelPath(files);
logger.info("ExcelExportServiceForZxfjkc.downFile info={}","临时目录删除功能结束!");
}
}
}
如何手动撸一个execl文件导出
虽然省略了大量code,但可以参考具体的操作，比如样式怎么操作的
// 将数据导出至execl中
public byte[] exportExcelForBZYS(TpZxfjkcZnjyzb data_znjy, TpZxfjkcJxjyzb data_jxjy, TpZxfjkcZfdkzczb data_zfdk,
TpZxfjkcZfzjzczb data_zfzj, TpZxfjkcSylrzb data_sylr, String title,
String qymc, String qynbm) throws Exception {
byte[] bs = null;
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("V1.6");
sheet.setDefaultColumnWidth((short) 20);
//sheet.setDefaultRowHeightInPoints((short) 15.625 * 2);
Row row = null;
Cell cell = null;
/* if (data_znjy == null) {
throw new Exception("数据不能为空！");
}*/
int rowIndex = 1;
// 标题
Font titleFont = workbook.createFont();
titleFont.setBold(true);//加粗
titleFont.setColor(IndexedColors.BLACK.getIndex());
titleFont.setFontHeightInPoints((short) 20);
// 副标题
Font subtitleFont = workbook.createFont();
titleFont.setBold(true);//加粗
titleFont.setColor(IndexedColors.BLACK.getIndex());
titleFont.setFontHeightInPoints((short) 15);
//标题的样式
CellStyle titleCellStyle = workbook.createCellStyle();
titleCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
titleCellStyle.setFont(titleFont);
titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//副标题的样式
CellStyle tsubitleCellStyle = workbook.createCellStyle();
tsubitleCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
tsubitleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
tsubitleCellStyle.setFont(subtitleFont);
tsubitleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
tsubitleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//带背景单元的样式
CellStyle CellWithBackgroundStyle = workbook.createCellStyle();
CellWithBackgroundStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
CellWithBackgroundStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
CellWithBackgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
CellWithBackgroundStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellWithBackgroundStyle.setBorderLeft(BorderStyle.THIN);
CellWithBackgroundStyle.setBorderBottom(BorderStyle.THIN);
CellWithBackgroundStyle.setBorderRight(BorderStyle.THIN);
CellWithBackgroundStyle.setBorderTop(BorderStyle.THIN);
//带背景单元的样式 白色背景+边框
CellStyle CellStyleWithWhiteBorder = workbook.createCellStyle();
CellStyleWithWhiteBorder.setAlignment(HorizontalAlignment.CENTER);
CellStyleWithWhiteBorder.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
CellStyleWithWhiteBorder.setFillPattern(FillPatternType.SOLID_FOREGROUND);
CellStyleWithWhiteBorder.setFillForegroundColor(IndexedColors.WHITE.getIndex());
CellStyleWithWhiteBorder.setBorderLeft(BorderStyle.THIN);
CellStyleWithWhiteBorder.setBorderBottom(BorderStyle.THIN);
CellStyleWithWhiteBorder.setBorderRight(BorderStyle.THIN);
CellStyleWithWhiteBorder.setBorderTop(BorderStyle.THIN);
//标题
row = sheet.createRow(rowIndex++);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(0);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(title);//标题
cell.setCellStyle(titleCellStyle);
CellRangeAddress rangeAdd = new CellRangeAddress(1, 1, 0, 6);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
row = sheet.createRow(rowIndex++);
row.setHeightInPoints((short) 25);
cell = row.createCell(0);
cell.setCellValue("填报日期：");//------
cell = row.createCell(4);
cell.setCellValue("扣除年度：");
cell = row.createCell(5);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getBsnd());
}else {
cell.setCellValue("");
}
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 25);
cell = row.createCell(0);
cell.setCellValue("纳税人姓名：");
cell = row.createCell(1);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getXm());
}else {
cell.setCellValue("");
}
cell = row.createCell(4);
cell.setCellValue("纳税人识别号：");
cell = row.createCell(5);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getZzhm());//即身份证号码
}else {
cell.setCellValue("");
}
//======================
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(0);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("纳税人信息");
rangeAdd = new CellRangeAddress(rowIndex, rowIndex+2, 0, 0);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
cell = row.createCell(1);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("证件类型");
cell = row.createCell(2);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getZzlxStr());
}else {
cell.setCellValue("");
}
rangeAdd = new CellRangeAddress(rowIndex, rowIndex, 2, 4);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
cell = row.createCell(5);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("");
cell = row.createCell(6);
cell.setCellStyle(CellStyleWithWhiteBorder);
cell.setCellValue("");//todo
//-----------------------------
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(1);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("证件号码");
cell = row.createCell(2);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getZzhm());
}else {
cell.setCellValue("");
}
rangeAdd = new CellRangeAddress(rowIndex, rowIndex, 2, 4);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
cell = row.createCell(5);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("电子邮箱");
cell = row.createCell(6);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getDzyx());
}else {
cell.setCellValue("");
}
//--------------------------------------
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(1);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("联系地址");
cell = row.createCell(2);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getNsrjcxxzb().getLxdz());
}else {
cell.setCellValue("");
}
rangeAdd = new CellRangeAddress(rowIndex, rowIndex, 2, 4);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
cell = row.createCell(5);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("配偶情况");
cell = row.createCell(6);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getHaspo() == 1 ? "有配偶":"没有配偶");
}else {
cell.setCellValue("");
}
//------------------------------------
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(0);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("纳税人配偶信息");
cell = row.createCell(1);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("姓名");
cell = row.createCell(2);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getPoxm());
}else {
cell.setCellValue("");
}
cell = row.createCell(3);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("身份证件类型");
cell = row.createCell(4);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(getZzlxName(data_znjy.getPozzlx()));
}else {
cell.setCellValue("");
}
cell = row.createCell(5);
cell.setCellStyle(CellWithBackgroundStyle);
cell.setCellValue("身份证件号码");
cell = row.createCell(6);
cell.setCellStyle(CellStyleWithWhiteBorder);
if(data_znjy != null) {
cell.setCellValue(data_znjy.getPozzhm());
}else {
cell.setCellValue("");
}
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(0);
cell.setCellStyle(tsubitleCellStyle);
cell.setCellValue("一、子女教育支出扣除信息");//副标题 Subtitle 1
rangeAdd = new CellRangeAddress(rowIndex, rowIndex, 0, 6);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
//-------------------------
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeightInPoints((short) 15.625 * 2);
cell = row.createCell(0);
cell.setCellStyle(CellStyleWithWhiteBorder);
cell.setCellValue("较上次报送信息是否发生变化： □首次报送（请填写全部信息） □无变化（不需重新填写） □有变化（请填写发生变化的信息）");
rangeAdd = new CellRangeAddress(rowIndex, rowIndex, 0, 6);
sheet.addMergedRegion(rangeAdd);
//设置合并单元格的边框
RegionUtil.setBorderBottom(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,rangeAdd,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,rangeAdd,sheet);
if(data_znjy != null) {
for(int i=0;i<data_znjy.getMxList().size();i++) {
int index = addCellListZNJY(workbook,sheet,rowIndex, data_znjy.getMxList().get(i),i+1);
rowIndex = index;
}
}
//============================================================================= 省略
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workBook.write(baos);
Byte[ ] bs = baos.toByteArray();
//bs = ExcelUtil.Excel2Bytes(workbook);
return bs;
}
Controller层代码
@RequestMapping(value = "/zxfjkc/export", method = RequestMethod.GET)
public String zxfjkcExport(HttpSession session, HttpServletRequest request, HttpServletResponse response,
RedirectAttributes attr) throws Exception {
String dcmbys = request.getParameter("dcmbys");
Map<String, Object> info = new HashMap<>();
//声明一个集合，用来存放多个Excel文件路径及名称
List<File> srcFile = new ArrayList<>();
//如果觉得文章对你有帮助，欢迎关注微信公众号：小牛呼噜噜
String qymc = tpQyxxQyxxService.selectByQynbm((String) info.get("qynbm")).getQymc();
try {
//获取数据
List<TpJygzNsrjcxxzb> nsrList = nsrjcxxzbService.selectByQynbm((String) info.get("qynbm"));
String path = Thread.currentThread().getContextClassLoader().getResource("").toString().replace("classes/", "").replace("file:/","") + "tempZxfjkc/" ;
//导出execl前，先删除旧的 文件夹
File file_old = new File(path);
if(file_old.exists()){
excelExportServiceForZxfjkc.deleteExcelPath(file_old);
}
for(TpJygzNsrjcxxzb nsr : nsrList) {
info.put("nsrid",nsr.getNsrid());
TpZxfjkcZnjyzb mx_znjy = znjyService.getZnjyMx(info);//获取明细信息
TpZxfjkcJxjyzb mx_jxjy = jxjyzbService.queryMx(info);
TpZxfjkcZfdkzczb mx_zfdk = zfdkService.queryMx(info);
TpZxfjkcZfzjzczb mx_zfzj = zfzjzczbService.queryMx(info);
TpZxfjkcSylrzb mx_sylr = sylrService.queryMx(info);
if(mx_znjy == null && mx_jxjy ==null && mx_zfdk == null && mx_zfzj == null && mx_sylr == null) {
continue;
}
String execlName = "【"+nsr.getXm()+"】"+"_【"+nsr.getZzhm()+"】的专项附加扣除信息";
//初始化
byte[] outPut= null;
InputStream inputStream = null;
//execl
outPut = excelExportServiceForZxfjkc.exportExcelForBZYS(mx_znjy,mx_jxjy,mx_zfdk,mx_zfzj,mx_sylr,"个人所得税专项附加扣除",qymc,(String) info.get("qynbm"));
int length = outPut.length;
inputStream = new ByteArrayInputStream(outPut, 0, length);
excelExportServiceForZxfjkc.execute(path + execlName + ".xlsx", inputStream);
//将文件放进list
srcFile.add(new File(path + execlName + ".xlsx"));
}
LOGGER.info("所有Execl文件 都已写入临时目录");
// 将服务器上存放Excel的文件夹打成zip包
File zipFile = new File(path + "专项附加扣除信息(导出)" + ".zip");
// 将多个excel打包成zip文件
excelExportServiceForZxfjkc.zipFiles(srcFile, zipFile);
//下载
//实现将压缩包写入流中，下载到本地，并删除临时文件中的压缩包及文件
excelExportServiceForZxfjkc.downFile(response, path, "专项附加扣除信息(导出)" + ".zip");
System.out.println("压缩包成功通过浏览器中下载下来------");
} catch (Exception e) {
//输出错误信息
LOGGER.error(Toolkits.getExceptionInfo(e));
}
return null;
}
尾语
poi功能虽然强大，但实际开发过程中，需要写大量重复代码，太麻烦了，easyexcel应允而生。easyexcel 是阿里巴巴开源的一款 excel 解析工具，底层逻辑也是基于 apache poi 进行二次开发的。不同的是，再读写数据的时候，采用 sax 模式一行一行解析，在并发量很大的情况下，依然能稳定运行。缺点就是读写性能稍慢。
大家感兴趣的话，多多留言交流，点个关注在走，小牛会及时更新后续之作easyexcel的入门篇。
