package com.smart.common.util;
import com.smart.common.constant.ExcelDataType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.List;
import java.util.*;
/**
* 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion)
*/
public class ExcelExport {
private static Logger log = LoggerFactory.getLogger(ExcelExport.class);
/**
* 单元格靠左对齐
*/
public static final Integer CELL_ALIGN_LEFT = 1;
/**
* 单元格居中对齐
*/
public static final Integer CELL_ALIGN_CENTER = 2;
/**
* 单元格靠右对齐
*/
public static final Integer CELL_ALIGN_RIGHT = 3;
/**
* 工作薄对象
*/
private SXSSFWorkbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 当前行号
*/
private int rownum;
/**
* 导出模板工作薄对象
*/
private Workbook workbook;
/**
* 构造函数
*
* 导出文件模板,读取第一个工作表
* @param headerNum
* 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ExcelExport(String fileName, int headerNum) throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
*
* 导出文件模板对象,读取第一个工作表
* @param headerNum
* 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ExcelExport(File file, int headerNum) throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
*
* 导出文件模板
* @param headerNum
* 标题行号,数据行号=标题行号+1
* @param sheetIndex
* 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ExcelExport(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
*
* 导出文件模板对象
* @param headerNum
* 标题行号,数据行号=标题行号+1
* @param sheetIndex
* 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ExcelExport(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
*
* 导出文件模板对象
* @param headerNum
* 标题行号,数据行号=标题行号+1
* @param sheetIndex
* 工作表编号
* @throws IOException
*/
public ExcelExport(String fileName, InputStream is, int headerNum, int sheetIndex)
throws IOException {
if (StringUtils.isBlank(fileName)) {
throw new RuntimeException("导入文档为空!");
} else if (fileName.toLowerCase().endsWith("xls")) {
this.workbook = new HSSFWorkbook(is);
} else if (fileName.toLowerCase().endsWith("xlsx")) {
this.workbook = new XSSFWorkbook(is);
} else if (fileName.toLowerCase().endsWith("xlsm")) {
this.workbook = new XSSFWorkbook(is);
} else {
throw new RuntimeException("文档格式不正确!");
}
if (this.workbook.getNumberOfSheets() < sheetIndex) {
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.workbook.getSheetAt(sheetIndex);
if (this.sheet.getLastRowNum() < 0) {
throw new RuntimeException("文档模板错误!");
}
this.rownum = headerNum + 1;
this.styles = createStyles(workbook);
log.debug("Initialize success.");
}
/**
* 构造函数 -- SXSSFWorkbook
* @param fileName
* @param headerNum
* @param sheetIndex
* @param batchType
* @throws IOException
*/
public ExcelExport(String fileName, int headerNum, int sheetIndex, String batchType)
throws IOException {
if (StringUtils.isBlank(fileName)) {
throw new RuntimeException("导出文档为空!");
} else {
InputStream is = new FileInputStream(new File(fileName));
XSSFWorkbook workb = new XSSFWorkbook(is);
this.wb = new SXSSFWorkbook(workb, 1000);
if (this.wb.getNumberOfSheets() < sheetIndex) {
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.rownum = headerNum + 1;
this.styles = createStyles(wb);
log.debug("Initialize success.");
}
}
/**
* 构造函数
*
* @param title
* 表格标题,传“空值”,表示无标题
* @param headerMap
* 表头数组
*/
public ExcelExport(String title, Map<String, String> headerMap) {
List<String> headerList = new ArrayList<String>();
headerList.add("序号");
for (String key : headerMap.keySet()) {
headerList.add(headerMap.get(key));
}
initialize(title, headerList);
}
/**
* 构造函数
*
* @param title
* 表格标题,传“空值”,表示无标题
* @param headerList
* 表头列表
*/
public ExcelExport(String title, List<String> headerList) {
// 自动增加序号列
headerList.add(0, "序号");
initialize(title, headerList);
}
/**
* 初始化函数
*
* @param title
* 表格标题,传“空值”,表示无标题
* @param headerList
* 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellSty