public class ExcelExportUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);
/**
* 导出
*
* @param title
* @param headers
* @param strArray
* @param response
* @throws UnsupportedEncodingException
*/
public static void exportExcel(String title, String[] headers, String[][] strArray, HttpServletResponse response) throws UnsupportedEncodingException {
ExcelUtil excelUtil = new ExcelUtil();
excelUtil.exportExcel(title, headers, strArray, response);
}
/**
* @param workbook
* @param response
* @param fileName
* @throws Exception
*/
public static void exportExcel(Workbook workbook, HttpServletResponse response, String fileName) throws Exception {
OutputStream os = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Allow-Methods", "POST,PUT,GET,DELETE");
response.setHeader("Access-Control-Max-Age", "3600");
response.setHeader("Access-Control-Allow-Headers", "*");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
workbook.write(os);
os.flush();
os.close();
}
/**
* 导出文件
*
* @param response
* @param file 完整文件路径，包括文件名称
* @param fileName 文件名称 ，包括后缀
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String file, String fileName) throws Exception {
//读取文件流
FileReader fileReader = new FileReader(file);
File file1 = fileReader.getFile();
if (null == file1 || !file1.exists()) {
throw new DoValidException("文件不存在");
}
BufferedInputStream inputStream = fileReader.getInputStream();
Workbook sheets = ExcelExportUtil.chooseFormat(fileName, inputStream);
exportExcel(sheets, response, fileName);
}
/**
* 根据 list<t> 数据 导出excel
*
* @param response
* @param data 实际数据
* @param fileName 文件名称
* @param sheetName 默认附表名称
* @throws Exception
*/
public static <E> void exportExcel(HttpServletResponse response, List<E> data, Class<E> clazz, String fileName, String sheetName) throws Exception {
EasyExcelUtil.exportExcel(response, data, clazz, fileName, sheetName);
}
/**
* 根据 list<t> 数据 导出excel
*
* @param response
* @param data 实际数据
* @param fileName 文件名称
* @throws Exception
*/
public static <E> void exportExcel(HttpServletResponse response, List<E> data, Class<E> clazz, String fileName) throws Exception {
exportExcel(response, data, clazz, fileName, "sheet");
}
/**
* 根据 list<t> 数据 导出excel
* 先写excel，再读取，最后下载导出
*
* @param response
* @param data 实际数据
* @param fileName 文件名称
* @param sheetName 默认附表名称
* @throws Exception
*/
public static <E> void exportExcelByWriterAfter(HttpServletResponse response, List<E> data, Class<E> clazz, String filePath, String fileName, String sheetName) throws Exception {
EasyExcelUtil.exportExcelByWriterAfter(response, data, clazz, filePath, fileName, sheetName);
}
/**
* 根据 list<t> 数据 导出excel
* 先写excel，再读取，最后下载导出
*
* @param response
* @param data 实际数据
* @param fileName 文件名称
* @throws Exception
*/
public static <E> void exportExcelByWriterAfter(HttpServletResponse response, List<E> data, Class<E> clazz, String filePath, String fileName) throws Exception {
exportExcelByWriterAfter(response, data, clazz, filePath, fileName, "sheet");
}
/**
* 选择EXCEL版本（xls和xlsx）
*
* @param file 文件流（上传的文件流）
* @return EXCEL版本
* @throws Exception 向上抛出异常
*/
public static Workbook chooseExcelFormat(MultipartFile file) throws Exception {
if (!FileUploaderManger.verify(file, FileTypeEnum.EXCEL)) {
throw new DoValidException("不是正确的excel格式");
}
String fileName = file.getOriginalFilename();
return chooseFormat(fileName, file.getInputStream());
}
/**
* 读取静态文件夹下的模板Excel
*
* @param path 模板地址
* @return EXCEL表格文件
* @throws Exception 向上抛出异常
*/
public static Workbook readExcelExport(String path) throws Exception {
Resource resource = new ClassPathResource(path);
String fileName = resource.getFilename();
InputStream is = resource.getInputStream();
return ExcelExportUtil.chooseFormat(fileName, is);
}
/**
* 选择EXCEL版本
*
* @param fileName 文件名
* @param is 流
* @return 结果
* @throws Exception 向上抛出异常
*/
public static Workbook chooseFormat(String fileName, InputStream is) throws Exception {
Workbook excelFile;
byte[] buf = IOUtils.toByteArray(is);
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(buf);
if (StringUtils.isEmpty(fileName)) {
throw new DoValidException("文件名不存在");
}
String matches = "^.+\\.(?i)(xls)$";
if (fileName.matches(matches)) {
excelFile = new HSSFWorkbook(byteArrayInputStream);
} else {
excelFile = new XSSFWorkbook(byteArrayInputStream);
}
return excelFile;
}
/**
* 以上一行为模板创建新的行
*
* @param sheet 表
* @param rowNum 第几行
* @param cellNums 创建列总数
* @return 返回生成的行
*/
public static Row createRowAndCell(Sheet sheet, int rowNum, int cellNums) {
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
for (int j = 0; j <= cellNums; j++) {
Cell upCell = sheet.getRow(rowNum - 1).getCell(j);
Cell cell = row.createCell(j);
cell.setCellStyle(upCell.getCellStyle());
}
}
return row;
}
/**
* 构造名称管理器和数据验证及公式
*
* @param workbook 目标工作簿
* @param dropDownDataSource 以父级id为key,子级名称列表为value的集合
* @param dataSourceSheetName 作为数据源的工作表名称
* @param columnStep 起始列的列号（以下表0为初始列）
* @param totalLevel 总共的层级数量
* @throws IOException
* @throws InvalidFormatException
*/
public static Workbook cascade(Workbook workbook, Map<String, List<String>> dropDownDataSource, final String dataSourceSheetName, final int columnStep, final int totalLevel) throws IOException, InvalidFormatException {
Sheet dataSourceSheet = workbook.createSheet(dataSourceSheetName);
workbook.setSheetHidden(workbook.getSheetIndex(dataSourceSheet), true);
Row headerRow = dataSourceSheet.createRow(0);
boolean firstTime = true;
int columnIndex = 0;
// 构造名称管理器数据源
for (String key : dropDownDataSource.keySet()) {
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(key);
if (dropDownDataSource.get(key) == null || dropDownDataSource.get(key).size() == 0) {
continue;
}
ArrayList<String> values = (ArrayList) dropDownDataSource.get(key);
int dataRowIndex = 1;
for (String value : values) {
Row row = firstTime ? dataSourceSheet.createRow(dataRowIndex) : dataSourceSheet.getRow(dataRowIndex);
if (row == null) {
row = dataSourceSheet.createRow(dataRowIndex);
}
row.createCell(columnIndex).setCellValue(value);
dataRowIndex++;
}
// 构造名称管理器
String range = buildRange(columnIndex, 2, values.size());
Name name = null;
try {
name = workbook.createName();
name.setNameName(key);
} catch (Exception e) {
name = workbook.getName(key);
}
String formula = dataSourceSheetName + "!" + range;
name.setRefersToFormula(formula);
columnIndex++;
firstTime = false;
}
Sheet assetSheet = workbook.getSheetAt(0);
// 第一级设置DataValidation
setValidationData(assetSheet, 1, 100, 0 + columnStep, 0 + columnStep, dataSourceSheet.getRow(0).getCell(0).toString());
// 剩下的层级设置DataValidation
for (int i = 1; i < totalLevel; i++) {
char[] offset = new char[1];
offset[0] = (char) ('A' + columnStep + i - 1);
String formulaString = buildFormulaString(new String(offset), 1);
CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 0 + columnStep + i, 0 + columnStep + i);
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formulaString);
DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
assetSheet.addValidationData(validation);
}
return workbook;
}
/**
* 添加数据有效性检查.
*
* @param sheet 要添加此检查的Sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
* @param formulaString 有效性检查的name
* @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小
*/
public static void setValidationData(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String formulaString) throws IllegalArgumentException {
if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow + ":" + lastRow + ":" + firstCol + ":" + lastCol);
}
if (sheet instanceof XSSFSheet) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
} else if (sheet instanceof HSSFSheet) {
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formulaString);
DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
sheet.addValidationData(validation);
}
}
/**
* 构造名称引用的数据源区域：
*/
private static String buildRange(int offset, int startRow, int rowCount) {
char start = (char) ('A' + offset);
return "$" + start + "$" + startRow + ":$" + start + "$" + (startRow + rowCount - 1);
}
/**
* 构造indirect公式：
*/
private static String buildFormulaString(String offset, int rowNum) {
return "INDIRECT($" + offset + (rowNum) + ")";
}}
