使用 SXSSFWorkbook 进行Excel导出下载
注意:测试结果没有达到预期,那换个方式试试
参考:1.https://blog.csdn.net/aiza4108/article/details/101129894
2.https://blog.csdn.net/cc_yy_zh/article/details/78772217
我想要的需求
1.导出的excel表头不能被修改
2.表头所在列的行(初始化行数,文章是50w行)是可以被编辑的(除过表头)
3.其余是不可编辑的
4.如下图
为特定列行加锁
1.先锁住整张sheet
2.在对不加锁的列和行进行解锁
加锁核心代码
为当前sheet加锁,加完锁后,整个sheet就会被锁定
// 为当前sheet加锁
sheet.protectSheet(EXCEL_LOCK);
解锁锁核心代码
// 1.为单元格解锁,并设置单元格格式为文本
CellStyle unlockStyle = sxssfWorkbook.createCellStyle();
unlockStyle.setLocked(false);
unlockStyle.setDataFormat(dataFormat.getFormat("@"));
// 2.将设置好的unlockStyle 赋予到特定单元格上
SXSSFCell cell = sheetRow.createCell(column);
cell.setCellStyle(unlockStyle);
示例代码1
package com.qzlink.util.excel;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Record;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
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.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.base.Joiner;
public class ExcelUtil {
private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private static final String EXCEL_LOCK = "732a138b-288d-49fd-8039-3f3673c64a66";
public static void exportExcel(Controller controller, String[] headers, String[] columns, String fileName, List<Record> dataList) {
HttpServletResponse response = controller.getResponse();
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
response.setContentType("application/msexcel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
OutputStream out = null;
try{
out = response.getOutputStream();
// 创建工作簿
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
// 数据格式化
DataFormat dataFormat = sxssfWorkbook.createDataFormat();
// 设置单元格格式为“文本”格式
CellStyle cellStyle = sxssfWorkbook.createCellStyle();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
// 设置单元格为锁定模式,并设置单元格格式为文本 // 以下加锁代码可不用,这段代码多此一举
CellStyle lockStyle = sxssfWorkbook.createCellStyle();
lockStyle.setLocked(true);
lockStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
lockStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
lockStyle.setDataFormat(dataFormat.getFormat("@"));
// 为单元格解锁,并设置单元格格式为文本 // 解锁代码
CellStyle unlockStyle = sxssfWorkbook.createCellStyle();
unlockStyle.setLocked(false);
unlockStyle.setDataFormat(dataFormat.getFormat("@"));
// 创建工作表
SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet1");
// 判断header是否为空
if (null == headers || headers.length <= 0) {
// 写入文件
sxssfWorkbook.write(out);
return;
}
// 判断columns是否为空
if (null == columns || columns.length <= 0 || null == dataList || dataList.size() <= 0) {
for(int row = 0; row < UPLOAD_FILE_UPPER_LIMIT_SIZE; row++) {
SXSSFRow sheetRow = sheet.createRow(row);
if (row == 0) { // header
for (int column=0; column<headers.length; column++) {
SXSSFCell cell = sheetRow.createCell(column);
cell.setCellValue(headers[column]);
// cell.setCellStyle(lockStyle); // 这段代码多此一举
}
} else { // 初始化50w数据
for (int column=0; column<headers.length; column++) {
SXSSFCell cell = sheetRow.createCell(column);
cell.setCellStyle(unlockStyle);
}
}
}
// 为整个sheet加锁
sheet.protectSheet(EXCEL_LOCK);
// 写入文件
sxssfWorkbook.write(out);
return;
}
// 设置数据
for (int row=0; row<=dataList.size(); row++) {
SXSSFRow sheetRow = sheet.createRow(row);
if (row == 0) { // 设置head
for (int column=0; column<headers.length; column++) {
sheetRow.createCell(column).setCellValue(headers[column]);
sheet.setDefaultColumnStyle(column,cellStyle);
}
} else { // 设置数据
for (int column=0; column<headers.length; column++) {
sheetRow.createCell(column).setCellValue(
dataList.get(row-1).getStr(columns[column]) == null ? "" : dataList.get(row-1).getStr(columns[column]));
sheet.setDefaultColumnStyle(column,cellStyle);
}
}
}
// 写入文件
sxssfWorkbook.write(out);
} catch (Exception e) {
log.error("ExcelUtil exportExcel exception catch : " + e);
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
controller.renderNull();
} catch (IOException e) {
log.error("ExcelUtil exportExcel exception finally catch : " + e);
}
}
}
}