import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.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.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import com.paic.commcc.support.ex.BizException;
/**
* <Description> excle的上传和下载(导入导出)的方法(POI版) <br>
* @version 1.0<br>
* @taskId <br>
* @CreateDate 2017-9-30 <br>
* @since PADM<br>
* @see <br>
*/
@Component
public class ExcelUpAndDownUtil {
private static Logger logger = LoggerFactory.getLogger(ExcelUpAndDownUtil.class);
public final static String YAHEI = "微软雅黑";
final static short FONTHEIGHT = 10;
final static short CELLWEIGHT = 20;
/**
* Description: 校验excel文件(非空,文件类型,文件大小)<br>
*
* @author EX-LIANGHUILU001<br>
* @taskId <br>
* @param file
* @return <br>
*/
public static String checkFile(MultipartFile file) {
// 1.判断文件是否存在
if (null == file) {
logger.error("文件不存在");
return "上传文件不能为空,请选择excel文件";
}
// 2.判断文件是否是excel文件
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName)) {
logger.error("文件名为空");
return "上传文件的文件名为空";
}
if (!fileName.endsWith("xls") && !fileName.endsWith("XLS") && !fileName.endsWith("xlsx")
&& !fileName.endsWith("XLSX")) {
logger.error("不是规定的xls格式文件");
return "上传文件不是规定的xls格式文件,请选择excel文件";
}
// 3、检查文件大小，不要超过10M
long fileSize = file.getSize();
long size = 1024l * 1024 * 10;
if (fileSize > size) {
return "文件大小不得超过10M！";
}
return "true";
}
/**
* Description:根据不同excel版本创建工作薄对象 <br>
* @taskId <br>
* @param file
* MultipartFile类型文件
* @return <br>
*/
public static Workbook getWorkBook(MultipartFile file) {
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName)) {
return null;
}
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith("xls") || fileName.endsWith("XLS")) {
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx") || fileName.endsWith("XLSX")) {
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
logger.error("文件不识别", e);
}
return workbook;
}
/**
* Description:校验列目录是否符合模板目录 <br>
* @taskId <br>
* @param workbook
* 工作簿对象
* @param strings
* 模板目录
* @param rowNum
* 列目录所在行数//一般是第一行//0
* @return <br>
*/
public static boolean isDIR(Workbook workbook, String[] strings, int rowNum) {
for (int sheetNums = 0; sheetNums < workbook.getNumberOfSheets(); sheetNums++) {
Sheet sheet = workbook.getSheetAt(sheetNums);
if (sheet == null) {
return false;
}
Row row = sheet.getRow(rowNum);
if (row == null) {
return false;
}
// 校验模板
if (row.getLastCellNum() != strings.length) {
return false;
}
for (int i = 0; i < row.getLastCellNum(); i++) {
String cellValue = row.getCell(i).getStringCellValue().trim();
if (!cellValue.equals(strings[i].trim())) {
return false;
}
}
}
return true;
}
/**
* Description:读取Excel文档内容 <br>
* @taskId <br>
* @param workbook
* 工作簿对象
* @param startRow
* 从指定行开始循环读取数据//一般是列目录后一行//1//禁止输入负值
* @param directoryRow
* 列目录所在行数//一般为0
* @return <br>
*/
public static List<String[]> readExcel(Workbook workbook, int startRow, int directoryRow) {
// 创建返回对象，把每行中的值作为一个String数组
List<String[]> list = new ArrayList<String[]>();
if (workbook != null) {
// int lastCellNum = 0;
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
// 获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
int lastRowNum = sheet.getLastRowNum();// 最后一行的行数
if (startRow > lastRowNum || startRow < 0) {
continue;
}
if (sheet.getRow(directoryRow) == null) {
continue;
}
int cellsNum = sheet.getRow(directoryRow).getLastCellNum();
// 从指定行开始循环读取数据
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
String[] cells = new String[cellsNum];
// 循环当前行
String str;
int counter = 0;
for (int cellNum = 0; cellNum < cellsNum; cellNum++) {
Cell cell = row.getCell(cellNum);
str = getCellValue(cell);
if (StringUtils.isBlank(str)) {
str = StringUtils.EMPTY;
counter++;
}
cells[cellNum] = str.trim();
}
// 中间空行处理
if (counter == cellsNum && rowNum != lastRowNum) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
// 最后一行空行处理
else if (counter == cellsNum && rowNum == lastRowNum) {
break;
}
list.add(cells);
}
}
try {
workbook.close();
} catch (IOException e) {
logger.error("工作簿对象关闭异常", e);
}
}
return list;
}
/**
* Description:读取Excel文档内容到数组列表 <br>
* @taskId <br>
* @param workbook
* 工作簿对象
* @param startRow
* 从指定行开始循环读取数据//一般是列目录后一行//1//禁止输入负值
* @param directoryRow
* 列目录所在行数//一般为0
* @param heads
* @return <br>
*/
public static List<String[]> readExcelToArr(Workbook workbook, int startRow, int directoryRow, String[] heads) {
if (workbook == null || heads == null || heads.length < 1) {
return new ArrayList<String[]>();
}
// 创建返回对象，把每行中的值作为一个String数组
List<String[]> list = new ArrayList<String[]>(workbook.getNumberOfSheets() * 10);
Sheet sheet;
int lastRowNum;
int cellsNum;
Row headRow;
Row bodyRow;
String data;
Cell cell;
String[] cells;
int counter;
// 遍历所有的sheet
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
// 获得当前sheet工作表
sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
// 获取数据最后一行的行数
lastRowNum = sheet.getLastRowNum();
if (startRow > lastRowNum || startRow < 0) {
continue;
}
// 判断表头
headRow = sheet.getRow(directoryRow);
if (headRow == null) {
continue;
}
cellsNum = headRow.getLastCellNum();
// 从指定行开始循环读取数据
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
bodyRow = sheet.getRow(rowNum);
if (bodyRow == null) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
// 循环当前行
cells = new String[heads.length];
counter = 0;
for (int cellNum = 0; cellNum < cellsNum; cellNum++) {
cell = bodyRow.getCell(cellNum);
data = getCellValue(cell);
if (StringUtils.isBlank(data)) {
data = StringUtils.EMPTY;
counter++;
}
cells[cellNum] = data.trim();
}
// 中间空行处理
if (counter == cellsNum && rowNum != lastRowNum) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
// 最后一行空行处理
else if (counter == cellsNum && rowNum == lastRowNum) {
break;
}
list.add(cells);
}
}
try {
workbook.close();
} catch (IOException e) {
logger.error("工作簿对象关闭异常", e);
}
return list;
}
/**
* Description:读取Excel文档内容到map列表 <br>
* @taskId <br>
* @param workbook
* 工作簿对象
* @param startRow
* 从指定行开始循环读取数据//一般是列目录后一行//1//禁止输入负值
* @param directoryRow
* 列目录所在行数//一般为0
* @param heads
* @return <br>
*/
public static List<Map<String, String>> readExcelToMaps(Workbook workbook, int startRow, int directoryRow,
String[] heads) {
if (workbook == null || heads == null || heads.length < 1) {
return new ArrayList<Map<String, String>>();
}
// 创建返回对象，把每行中的值作为一个Map
List<Map<String, String>> maps = new ArrayList<Map<String, String>>(workbook.getNumberOfSheets() * 10);
Sheet sheet;
int lastRowNum;
int cellsNum;
Row headRow;
Row bodyRow;
String data;
Cell cell;
Map<String, String> cells;
int counter;
// 遍历所有的sheet
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
// 获得当前sheet工作表
sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
// 获取数据最后一行的行数
lastRowNum = sheet.getLastRowNum();
if (startRow > lastRowNum || startRow < 0) {
continue;
}
// 判断表头
headRow = sheet.getRow(directoryRow);
if (headRow == null) {
continue;
}
cellsNum = headRow.getLastCellNum();
// 从指定行开始循环读取数据
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
bodyRow = sheet.getRow(rowNum);
if (bodyRow == null) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
// 循环当前行
counter = 0;
cells = new HashMap<String, String>();
for (int cellNum = 0; cellNum < cellsNum; cellNum++) {
cell = bodyRow.getCell(cellNum);
data = getCellValue(cell);
if (StringUtils.isBlank(data)) {
data = StringUtils.EMPTY;
counter++;
}
cells.put(heads[cellNum], data.trim());
}
// 中间空行处理
if (counter == cellsNum && rowNum != lastRowNum) {
throw new BizException("第" + (rowNum + 1) + "行为空行，请调整后导入！");
}
// 最后一行空行处理
else if (counter == cellsNum && rowNum == lastRowNum) {
break;
}
maps.add(cells);
}
}
try {
workbook.close();
} catch (IOException e) {
logger.error("工作簿对象关闭异常", e);
}
return maps;
}
/**
* Description: 生成含列目录的sheet(2003 excel)<br>
* @taskId <br>
* @param workbook
* @param sheetTitle
* 工作表名称
* @param headers
* 列目录
* @param fontName
* 单元格字体名称//黑体,宋体,仿宋
* @param fontHeight
* 单元格字体大小//10
* @param isBold
* 单元格字体是否加粗,true加粗
* @param cellWeight
* 单元格宽度,建议默认为20个字节
* @param sheetNum
* sheet页码,从0开始算起
* @throws Exception
* <br>
*/
public static HSSFSheet createExcelModel(HSSFWorkbook workbook, String sheetTitle, String[] headers,
String fontName, short fontHeight, Boolean isBold, short cellWeight, int sheetNum) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度
// sheet.autoSizeColumn(1, true); //自适应列的宽度(列目录的宽度)
sheet.setDefaultColumnWidth(cellWeight);
// 生成样式
HSSFCellStyle style = setStyleAndFont(workbook, fontName, fontHeight, isBold, HorizontalAlignment.CENTER);
// 列目录
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
return sheet;
}
public static SXSSFSheet createExcelModelForSXSSF(SXSSFWorkbook workbook, String sheetTitle, String[] headers,
String fontName, short fontHeight, Boolean isBold, short cellWeight, int sheetNum) {
// 生成一个表格
SXSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度
// sheet.autoSizeColumn(1, true); //自适应列的宽度(列目录的宽度)
sheet.setDefaultColumnWidth(cellWeight);
// 生成样式
CellStyle style = setStyleAndFont(workbook, fontName, fontHeight, isBold, HorizontalAlignment.CENTER);
// 列目录
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
return sheet;
}
private static CellStyle setStyleAndFont(SXSSFWorkbook workbook, String fontName, short fontHeight, Boolean isBold,
HorizontalAlignment alignment) {
CellStyle style = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat(); // 格式化单元格
style.setDataFormat(format.getFormat("@")); // 设置为文本格式
Font headfont = workbook.createFont();
headfont.setFontName(fontName);
headfont.setFontHeightInPoints(fontHeight);// 字体大小
if (isBold) {
headfont.setBold(true);
}
style.setFont(headfont);
style.setAlignment(alignment);
return style;
}
/**
* Description: 生成要导出或要下载模板的excel文件(HSSFWorkbook对象)(2003 excel)<br>
* @taskId <br>
* @param sheetTitle
* 工作表名称
* @param headers
* 列目录
* @param dataList
* list数据,嵌入MAP,key为对应的列目录名称,value为列目录对应的当前内容
* @throws Exception
* <br>
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public static HSSFWorkbook exportExcel(String sheetTitle, String[] headers, List<Map<String, String>> dataList)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 只下载模板
HSSFSheet sheet = createExcelModel(workbook, sheetTitle, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
if (dataList == null || dataList.isEmpty()) {
// 创建列目录模板
return workbook;
}
// 创建导出数据单元格样式
HSSFCellStyle style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
HSSFRow rowN;
int rowIndex = 1;
int sheetNum = 0;
for (Iterator iterator = dataList.iterator(); iterator.hasNext();) {
// 每50000条数据就换一页显示// 创建列目录模板//2003 excel文档每一页做多能支持65535数据
if (rowIndex % 50001 == 0) {
sheetNum++;
rowIndex = 1;
String sheetTitleName = sheetTitle + Integer.toString(sheetNum);
sheet = createExcelModel(workbook, sheetTitleName, headers, YAHEI, FONTHEIGHT, true, CELLWEIGHT,
sheetNum);
}
Map<String, String> map = (Map<String, String>) iterator.next();
rowN = sheet.createRow(rowIndex);
rowIndex++;
int colNum = 0;
for (Map.Entry<String, String> entry : map.entrySet()) {
HSSFCell cell = rowN.createCell(colNum);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(entry.getValue() == null ? "" : entry.getValue());
colNum = colNum + 1;
}
}
return workbook;
}
/**
* Description: 生成要导出或要下载模板的HSSFWorkbook对象集,(2003 excel)<br>
* @taskId <br>
* @param sheetTitle
* 工作表名称
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @throws Exception
* <br>
*/
@SuppressWarnings("unused")
public static HSSFWorkbook exportExcelList(String sheetTitle, String[] headers, List<List<String>> dataList)
throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.setSheetName(0, sheetTitle);
return workbook;
}
// 第一行创建列目录
HSSFSheet sheet = createExcelModel(workbook, sheetTitle, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
// dataList为空,就只生成下载模板对象
if (dataList == null || dataList.isEmpty()) {
// 创建列目录模板
return workbook;
}
// 创建导出数据单元格样式
HSSFCellStyle style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
int rowIndex = 1;
int sheetNum = 0;
for (List<String> rowList : dataList) {
// 每50000条数据就换一页显示// 创建列目录模板//2003 excel文档每一页做多能支持65535数据
if (rowIndex % 50001 == 0) {
sheetNum++;
rowIndex = 1;
String sheetTitleName = sheetTitle + Integer.toString(sheetNum);
sheet = createExcelModel(workbook, sheetTitleName, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
sheetNum);
}
// 将内容写入到单元格内
HSSFRow row = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : rowList) {
HSSFCell cell = row.createCell(colNum);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
colNum = colNum + 1;
}
rowIndex++;
}
dataList.clear();
return workbook;
}
/**
* Description: 生成要导出或要下载模板的HSSFWorkbook对象集,(2003 excel)<br>
* @taskId <br>
* @param sheetTitle
* 工作表名称
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @throws Exception
* <br>
*/
@SuppressWarnings("unused")
public static List<HSSFWorkbook> exportExcelNew(String sheetTitle, String[] headers, List<List<String>> dataList)
throws Exception {
List<HSSFWorkbook> workbookList = new ArrayList<HSSFWorkbook>();
HSSFWorkbook workbook = new HSSFWorkbook();
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.setSheetName(0, sheetTitle);
workbookList.add(workbook);
return workbookList;
}
// 第一行创建列目录
HSSFSheet sheet = createExcelModel(workbook, sheetTitle, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
// dataList为空,就只生成下载模板对象
if (dataList == null || dataList.size() == 0) {
// 创建列目录模板
workbookList.add(workbook);
return workbookList;
}
// 创建导出数据单元格样式
HSSFCellStyle style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
HSSFRow rowN;
int rowIndex = 1;
int sheetNum = 0;
for (List<String> rowList : dataList) {
if (rowIndex % 50001 == 0) {// 5W一个文件,压缩下载效率比较高
rowIndex = 1;
workbookList.add(workbook);
workbook = new HSSFWorkbook();
sheet = createExcelModel(workbook, sheetTitle, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT, 0);
style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
sheetNum = 0;
}
// 将内容写入到单元格内
rowN = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : rowList) {
HSSFCell cell = rowN.createCell(colNum);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
// cell.getCellStyle().cloneStyleFrom(style);
cell.setCellValue(value == null ? "" : value);
colNum = colNum + 1;
}
rowIndex++;
}
dataList.clear();
workbookList.add(workbook);
workbook.close();
return workbookList;
}
/**
* Description:下载excel文档的方法 <br>
* @taskId <br>
* @param workbook
* @param fileName
* 下载的文档名称
* @param httpResponse
* <br>
*/
public static void download(Workbook workbook, String fileName, HttpServletResponse httpResponse) {
OutputStream out = null;
try {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
httpResponse.setContentType("application/download;charset=UTF-8");
httpResponse.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
httpResponse.setHeader("Pragma", "public");
httpResponse.setHeader("Content-disposition", "attachment;filename="" + fileName + ".xls"");
out = httpResponse.getOutputStream();
workbook.write(out);
out.flush();
httpResponse.flushBuffer();
} catch (Exception e) {
logger.error("下载模板文件异常", e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("下载模板文件的工作簿对象关闭异常", e);
}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
logger.error("下载模板文件的输出流关闭异常", e);
}
}
}
}
/**
* Description:下载压缩的ZIP文档的方法 <br>
* @taskId <br>
* @param workbook
* @param fileName
* 下载的文档名称
* @param httpResponse
* <br>
*/
public static void downloadNew(List<HSSFWorkbook> workbookList, String fileName, HttpServletResponse httpResponse) {
httpResponse.setContentType("application/download;charset=UTF-8");
httpResponse.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
httpResponse.setHeader("Pragma", "no-cache");
// httpResponse.setHeader("Pragma", "public");
OutputStream out = null;
ZipEntry zipEntry = null;
ZipOutputStream zos = null;
OutputStreamWriter writer = null;
try {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
httpResponse.setHeader("Content-disposition", "attachment;filename="" + fileName + ".zip"");
out = httpResponse.getOutputStream();
zos = new ZipOutputStream(out);// (outputStream,Charset.forName("GBK"));这个是1.7之后才可以的
writer = new OutputStreamWriter(zos);
int num = 1;
String zipFileName = "";
for (HSSFWorkbook workbook : workbookList) {
if (num != workbookList.size()) {
zipFileName = fileName + "-" + num + ".xls";
} else {
zipFileName = fileName + "-" + "End.xls";
}
zipFileName = java.net.URLDecoder.decode(zipFileName, StandardCharsets.UTF_8.name());// 中文乱码,2次解码用decode,一次用encode
zipEntry = new ZipEntry(zipFileName);// 设置压缩包中文件的名字
zos.putNextEntry(zipEntry);
workbook.write(zos);// 将创建的excel写入压缩包中
workbook.close();
num++;
}
// zos.se
// zos.setEncoding(StandardCharsets.UTF_8.name());
httpResponse.flushBuffer();
} catch (Exception e) {
logger.error("下载模板文件异常", e);
} finally {
if (writer != null) {
try {
writer.flush();
writer.close();
} catch (IOException e) {
logger.error("下载模板文件的OutputStreamWriter对象关闭异常", e);
}
}
if (zos != null) {
try {
zos.flush();
// /zos.closeEntry();
zos.close();
} catch (IOException e) {
logger.error("下载模板文件的ZipOutputStream对象关闭异常", e);
}
}
if (out != null) {
try {
out.flush();
out.close();
} catch (IOException e) {
logger.error("下载模板文件的输出流关闭异常", e);
}
}
}
}
/**
* Description: 将文件打包上传的系统(该方法对list大小有限制,list超过47M会报内存溢出,建议用下面拆分的方法)<br>
* @taskId <br>
* @param zipName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @param 最后生成的文件路径为:sourcePath
* + File.separator + zipName +".zip" <br>
*/
public static void fileUpload(String zipName, String sourcePath, String[] headers, List<List<String>> dataList) {
List<HSSFWorkbook> workbookList = new ArrayList<HSSFWorkbook>();
String fileName = "";
try {
if (zipName.indexOf('_') != -1) {
fileName = zipName.substring(0, zipName.indexOf('_'));
}
workbookList = exportExcelNew(fileName, headers, dataList);
} catch (Exception e) {
logger.error("生成HSSFWorkbook集失败", e.getMessage(), e);
}
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
FileOutputStream out = null;
OutputStreamWriter writer = null;
ZipOutputStream zos = null;
ZipEntry zipEntry = null;
try {
out = new FileOutputStream(file);
zos = new ZipOutputStream(out);
writer = new OutputStreamWriter(zos);
String zipFileName = "";
int num = 1;
for (HSSFWorkbook wb : workbookList) {
if (num != workbookList.size()) {
zipFileName = zipName + "-" + num + ".xls";
} else {
zipFileName = zipName + "-" + "End.xls";
}
zipEntry = new ZipEntry(zipFileName);
zos.putNextEntry(zipEntry);
wb.write(zos);
wb.close();
num++;
}
} catch (Exception e) {
logger.error("下载文件异常", e);
} finally {
if (writer != null) {
try {
writer.flush();
writer.close();
} catch (IOException e) {
logger.error("下载文件异常", e);
}
}
if (out != null) {
try {
out.flush();
out.close();
} catch (IOException e) {
logger.error("下载文件异常", e);
}
}
if (zos != null) {
try {
zos.flush();
zos.closeEntry();
// zos.close();
} catch (IOException e) {
logger.error("下载文件异常", e);
}
}
}
}
/**
* Description: 将数据生成excel文件后打包上传的系统<br>
* @taskId <br>
* @param zipName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @param 最后生成的文件路径为:sourcePath
* + File.separator + zipName +".zip" <br>
*/
public static void dataUploadToFile(String zipName, String sourcePath) {
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
// 删除上一次遗留的文件
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
// 创建资源文件夹(没有就创建/有就照用)
File excelPathFile = new File(sourcePath + File.separator + zipName);
if (!excelPathFile.exists())
excelPathFile.mkdirs();
FileInputStream fis = null;
BufferedInputStream bis = null;
FileOutputStream fos = null;
ZipOutputStream zos = null;
try {
File[] sourceFiles = excelPathFile.listFiles();
if (null == sourceFiles || sourceFiles.length < 1) {
// logger.audit("下载文件异常", e);
} else {
fos = new FileOutputStream(file);
zos = new ZipOutputStream(new BufferedOutputStream(fos));
byte[] bufs = new byte[1024 * 10];
for (int i = 0; i < sourceFiles.length; i++) {
// 创建ZIP实体，并添加进压缩包
ZipEntry zipEntry = new ZipEntry(sourceFiles[i].getName());
zos.putNextEntry(zipEntry);
// 读取待压缩的文件并写进压缩包里
fis = new FileInputStream(sourceFiles[i]);
bis = new BufferedInputStream(fis, 1024 * 10);
int read = 0;
while ((read = bis.read(bufs, 0, 1024 * 10)) != -1) {
zos.write(bufs, 0, read);
}
bis.close();
fis.close();
}
}
} catch (FileNotFoundException e) {
logger.error("下载文件的FileNotFoundException对象关闭异常", e);
} catch (IOException e) {
logger.error("下载文件的FileNotFoundException对象关闭异常", e);
} finally {
// 关闭流
try {
if (null != bis) {
bis.close();
}
if (null != zos) {
zos.flush();
zos.closeEntry();
zos.close();
}
if (null != fis) {
fis.close();
}
if (null != fos) {
fos.close();
}
try {
deleteFile(excelPathFile);// 删除文件夹
} catch (Exception e) {
logger.error("下载文件的FileNotFoundException对象关闭异常", e);
}
} catch (IOException e) {
logger.error("下载文件的FileNotFoundException对象关闭异常", e);
}
}
}
/**
* Description: 将数据生成excel文件上传的系统<br>
* @taskId <br>
* @param zipName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @param page
* 第几个文件
* @param 最后生成的文件路径为:sourcePath
* + File.separator + fileName + File.separator + zipName +".zip"
* <br>
*/
public static void createExcel(String zipName, String sourcePath, String[] headers, List<List<String>> dataList,
Integer page, Map<String, String> dataTypeMap) {
String fileName = "";
if (zipName.indexOf('_') != -1) {
fileName = zipName.substring(0, zipName.indexOf('_'));
}
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
// 创建资源文件夹(没有就创建/有就照用)
File excelPathFile = new File(sourcePath + File.separator + zipName);
if (!excelPathFile.exists())
excelPathFile.mkdirs();
HSSFWorkbook workbook = null;
FileOutputStream os = null;
HSSFCellStyle style = null;
try {
workbook = new HSSFWorkbook();
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.createSheet();
workbook.setSheetName(0, fileName);
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
dataList.clear();
os.flush();
return;
}
// 第一行创建列目录
HSSFSheet sheet = createExcelModel(workbook, fileName, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
// dataList为空,就只生成下载模板对象
if (dataList.isEmpty()) {
// 创建列目录模板
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
// dataList.clear();
os.flush();
return;
}
// 创建导出数据单元格样式
style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
// Excel每一列单独设置一个样式
List<CellStyle> styleList = new ArrayList<>(headers.length);
if (dataTypeMap != null) {
for (int i = 0; i < headers.length; i++) {
String type = dataTypeMap.get(headers[i]);
if (StringUtils.isBlank(type)) {
styleList.add(null);
continue;
}
CellStyle styleColumn = workbook.createCellStyle();
Font headfont = workbook.createFont();
headfont.setFontName(YAHEI);
headfont.setFontHeightInPoints(FONTHEIGHT);// 字体大小
styleColumn.setFont(headfont);
styleColumn.setAlignment(HorizontalAlignment.LEFT);
DataFormat format = workbook.createDataFormat(); // 格式化单元格
if (StringUtils.equals("int", type)) {
styleColumn.setDataFormat(format.getFormat("0"));
styleList.add(styleColumn);
continue;
} else if (StringUtils.equals("number", type)) {
styleColumn.setDataFormat(format.getFormat("0.00"));
styleList.add(styleColumn);
continue;
} else if (StringUtils.equals("percent", type)) {
styleColumn.setDataFormat(format.getFormat("0.00%"));
styleList.add(styleColumn);
continue;
} else {
styleColumn.setDataFormat(format.getFormat("@"));
styleList.add(styleColumn);
}
}
}
HSSFRow rowN;
int rowIndex = 1;
for (List<String> list : dataList) {
// 将内容写入到单元格内
rowN = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : list) {
HSSFCell cell;
// 自定义数据类型
if (dataTypeMap != null) {
String type = dataTypeMap.get(headers[colNum]);
CellStyle columnStyle = styleList.get(colNum);
if (columnStyle == null) {
columnStyle = style;
}
if (StringUtils.isBlank(type)) {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
} else {
DataTypeEnum dataTypeEnum = getDataType(value);
if (StringUtils.equals("int", type)) {
if (dataTypeEnum.equals(DataTypeEnum.INT)) {
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
cell.setCellValue(Integer.parseInt(value));
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(columnStyle);
cell.setCellValue(value == null ? "" : value);
}
} else if (StringUtils.equals("number", type)) {
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
if (StringUtils.isBlank(value)) {
cell.setCellValue("");
} else {
cell.setCellValue(Double.parseDouble(value));
}
} else if (StringUtils.equals("percent", type)) {
if (dataTypeEnum.equals(DataTypeEnum.PERCENT)) {// 百分比
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
value = value.replaceAll("%", "");
cell.setCellValue(Double.parseDouble(value) / 100.00);
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
}
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
cell.setCellStyle(style);
colNum = colNum + 1;
}
rowIndex = rowIndex + 1;
}
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
dataList.clear();
os.flush();
} catch (FileNotFoundException e) {
logger.error("FileOutputStream对象生成出现异常", e);
} catch (Exception e) {
logger.error("文件生成出现异常", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.error("下载文件的FileOutputStream对象关闭异常", e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
}
public static void createExcelBySXFF(String zipName, String sourcePath, String[] headers,
List<List<String>> dataList, Integer page, Map<String, String> dataTypeMap) {
String fileName = "";
if (zipName.indexOf('_') != -1) {
fileName = zipName.substring(0, zipName.indexOf('_'));
}
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
// 创建资源文件夹(没有就创建/有就照用)
File excelPathFile = new File(sourcePath + File.separator + zipName);
if (!excelPathFile.exists())
excelPathFile.mkdirs();
SXSSFWorkbook workbook = null;
FileOutputStream os = null;
CellStyle style = null;
try {
workbook = new SXSSFWorkbook(2000);
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.createSheet();
workbook.setSheetName(0, fileName);
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xlsx");
workbook.write(os);
dataList.clear();
os.flush();
return;
}
// 第一行创建列目录
Sheet sheet = createExcelModelForSXSSF(workbook, fileName, headers, YAHEI, FONTHEIGHT, Boolean.TRUE,
CELLWEIGHT, 0);
// dataList为空,就只生成下载模板对象
if (dataList == null || dataList.isEmpty()) {
// 创建列目录模板
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xlsx");
workbook.write(os);
// dataList.clear();
os.flush();
return;
}
// 创建导出数据单元格样式
style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
// Excel每一列单独设置一个样式
List<CellStyle> styleList = new ArrayList<>(headers.length);
if (dataTypeMap != null) {
for (int i = 0; i < headers.length; i++) {
String type = dataTypeMap.get(headers[i]);
if (StringUtils.isBlank(type)) {
styleList.add(null);
continue;
}
CellStyle styleColumn = workbook.createCellStyle();
Font headfont = workbook.createFont();
headfont.setFontName(YAHEI);
headfont.setFontHeightInPoints(FONTHEIGHT);// 字体大小
styleColumn.setFont(headfont);
styleColumn.setAlignment(HorizontalAlignment.LEFT);
DataFormat format = workbook.createDataFormat(); // 格式化单元格
if (StringUtils.equals("int", type)) {
styleColumn.setDataFormat(format.getFormat("0"));
styleList.add(styleColumn);
continue;
} else if (StringUtils.equals("number", type)) {
styleColumn.setDataFormat(format.getFormat("0.00"));
styleList.add(styleColumn);
continue;
} else if (StringUtils.equals("percent", type)) {
styleColumn.setDataFormat(format.getFormat("0.00%"));
styleList.add(styleColumn);
continue;
} else {
styleColumn.setDataFormat(format.getFormat("@"));
styleList.add(styleColumn);
}
}
}
Row rowN;
int rowIndex = 1;
for (List<String> list : dataList) {
// 将内容写入到单元格内
rowN = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : list) {
Cell cell;
// 自定义数据类型
if (dataTypeMap != null) {
String type = dataTypeMap.get(headers[colNum]);
CellStyle columnStyle = styleList.get(colNum);
if (columnStyle == null) {
columnStyle = style;
}
if (StringUtils.isBlank(type)) {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
} else {
DataTypeEnum dataTypeEnum = getDataType(value);
if (StringUtils.equals("int", type)) {
if (dataTypeEnum.equals(DataTypeEnum.INT)) {
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
cell.setCellValue(Integer.parseInt(value));
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(columnStyle);
cell.setCellValue(value == null ? "" : value);
}
} else if (StringUtils.equals("number", type)) {
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
if (StringUtils.isBlank(value)) {
cell.setCellValue("");
} else {
cell.setCellValue(Double.parseDouble(value));
}
} else if (StringUtils.equals("percent", type)) {
if (dataTypeEnum.equals(DataTypeEnum.PERCENT)) {// 百分比
cell = rowN.createCell(colNum, CellType.NUMERIC);
cell.setCellStyle(columnStyle);
value = value.replaceAll("%", "");
cell.setCellValue(Double.parseDouble(value) / 100.00);
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
}
} else {
cell = rowN.createCell(colNum, CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
}
colNum = colNum + 1;
}
rowIndex = rowIndex + 1;
}
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xlsx");
workbook.write(os);
dataList.clear();
os.flush();
} catch (FileNotFoundException e) {
logger.error("FileOutputStream对象生成出现异常", e);
} catch (Exception e) {
logger.error("文件生成出现异常", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.error("下载文件的FileOutputStream对象关闭异常", e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
}
/**
* Description: 将数据生成excel文件上传的系统<br>
* @taskId <br>
* @param zipName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,list<Map<String,String>>,依次填入
* @param page
* 第几个文件
* @param 最后生成的文件路径为:sourcePath
* + File.separator + fileName + File.separator + zipName +".zip"
* <br>
*/
public static void createExcelByListMap(String zipName, String sourcePath, String[] headers,
List<Map<String, String>> dataList, Integer page) {
String fileName = "";
if (zipName.indexOf('_') != -1) {
fileName = zipName.substring(0, zipName.indexOf('_'));
}
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
// 创建资源文件夹(没有就创建/有就照用)
File excelPathFile = new File(sourcePath + File.separator + zipName);
if (!excelPathFile.exists())
excelPathFile.mkdirs();
HSSFWorkbook workbook = null;
FileOutputStream os = null;
HSSFCellStyle style = null;
try {
workbook = new HSSFWorkbook();
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.createSheet();
workbook.setSheetName(0, fileName);
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
// dataList.clear();
os.flush();
return;
}
// 第一行创建列目录
HSSFSheet sheet = createExcelModel(workbook, fileName, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
// dataList为空,就只生成下载模板对象
if (dataList == null || dataList.isEmpty()) {
// 创建列目录模板
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
// dataList.clear();
os.flush();
return;
}
// 创建导出数据单元格样式
style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
HSSFRow rowN;
int rowIndex = 1;
for (Map<String, String> map : dataList) {
// 将内容写入到单元格内
rowN = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : map.values()) {
HSSFCell cell = rowN.createCell(colNum);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
String ss = (value == null || "".equals(value)) ? "" : value;
cell.setCellValue(ss);
colNum = colNum + 1;
}
rowIndex = rowIndex + 1;
}
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + page + ".xls");
workbook.write(os);
// dataList.clear();
os.flush();
} catch (FileNotFoundException e) {
logger.error("FileOutputStream对象生成出现异常", e);
} catch (Exception e) {
e.printStackTrace();
logger.error("文件生成出现异常", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.error("下载文件的FileOutputStream对象关闭异常", e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
}
/**
* Description: 将数据生成一个excel文件上传的系统<br>
* @taskId <br>
* @param fileName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @param 最后生成的文件路径为:sourcePath
* + File.separator + fileName +".xls" <br>
*/
public static void createExcel(String fileName, String sourcePath, String[] headers, List<List<String>> dataList) {
String sheetName = "";
if (fileName.indexOf('_') != -1) {
sheetName = fileName.substring(0, fileName.indexOf('_'));
}
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
HSSFWorkbook workbook = null;
FileOutputStream os = null;
try {
workbook = exportExcelList(sheetName, headers, dataList);
String excelPath = sourcePath + File.separator + fileName;
os = new FileOutputStream(excelPath + ".xls");
workbook.write(os);
if (dataList != null) {
dataList.clear();
}
os.flush();
return;
} catch (FileNotFoundException e) {
logger.error("FileOutputStream对象生成出现异常", e);
} catch (Exception e) {
logger.error("文件生成出现异常", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.error("下载文件的FileOutputStream对象关闭异常", e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
}
/**
* Description: 将数据生成excel文件上传的系统<br>
* @taskId <br>
* @param zipName下载的文档名称
* @param sourcePath
* 放置的文件的路径
* @param headers
* 列目录
* @param dataList
* list数据,建议传入ArrayList,依次填入
* @param page
* 第几个文件
*/
public static void createExcel(String zipName, String sourcePath, String[] headers, List<List<String>> dataList,
Integer page, String name) {
String fileName = "";
if (zipName.indexOf('_') != -1) {
fileName = zipName.substring(0, zipName.indexOf('_'));
}
String zipPath = sourcePath + File.separator + zipName;
// 创建资源文件夹(没有就创建/有就照用)
File sourcePathFile = new File(sourcePath);
if (!sourcePathFile.exists())
sourcePathFile.mkdirs();
File file = new File(zipPath + ".zip");
if (file.exists()) {
file.delete();
}
// 创建资源文件夹(没有就创建/有就照用)
File excelPathFile = new File(sourcePath + File.separator + zipName);
if (!excelPathFile.exists())
excelPathFile.mkdirs();
HSSFWorkbook workbook = null;
FileOutputStream os = null;
HSSFCellStyle style = null;
try {
workbook = new HSSFWorkbook();
// 列目录为空就返回空文件对象
if (headers == null || headers.length == 0) {
workbook.createSheet();
workbook.setSheetName(0, fileName);
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + name + "-" + page + ".xls");
workbook.write(os);
dataList.clear();
os.flush();
return;
}
// 第一行创建列目录
HSSFSheet sheet = createExcelModel(workbook, fileName, headers, YAHEI, FONTHEIGHT, Boolean.TRUE, CELLWEIGHT,
0);
// dataList为空,就只生成下载模板对象
if (dataList == null || dataList.isEmpty()) {
// 创建列目录模板
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + name + "-" + page + ".xls");
workbook.write(os);
// dataList.clear();
os.flush();
return;
}
// 创建导出数据单元格样式
style = setStyleAndFont(workbook, "Arial", FONTHEIGHT, Boolean.FALSE, HorizontalAlignment.LEFT);
HSSFRow rowN;
int rowIndex = 1;
for (List<String> list : dataList) {
// 将内容写入到单元格内
rowN = sheet.createRow(rowIndex);
int colNum = 0;
for (String value : list) {
HSSFCell cell = rowN.createCell(colNum);
cell.setCellType(CellType.STRING);
cell.setCellStyle(style);
cell.setCellValue(value == null ? "" : value);
colNum = colNum + 1;
}
rowIndex = rowIndex + 1;
}
String excelPath = sourcePath + File.separator + zipName + File.separator + zipName;
os = new FileOutputStream(excelPath + "-" + name + "-" + page + ".xls");
workbook.write(os);
dataList.clear();
os.flush();
} catch (FileNotFoundException e) {
logger.error("FileOutputStream对象生成出现异常", e);
} catch (Exception e) {
logger.error("文件生成出现异常", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
logger.error("下载文件的FileOutputStream对象关闭异常", e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
}
/**
* Description:读取单元格数据(根据不同类型转成String) <br>
* @taskId <br>
* @param cell
* @return <br>
*/
@SuppressWarnings("deprecation")
private static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellType(CellType.STRING);
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* Description: 单元格样式样式,字体样式<br>
* @taskId <br>
* @param workbook
* 工作簿
* @param fontName
* 单元格字体名称//黑体,宋体,仿宋
* @param fontHeight
* 单元格字体大小//10
* @param isBold
* 单元格字体是否加粗,true加粗
* @param alignment
* 单元格的位置,左右居中,或者左对齐(HorizontalAlignment.CENTER,HorizontalAlignment.LEFT)
* @return <br>
*/
private static HSSFCellStyle setStyleAndFont(HSSFWorkbook workbook, String fontName, short fontHeight,
Boolean isBold, HorizontalAlignment alignment) {
HSSFCellStyle style = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat(); // 格式化单元格
style.setDataFormat(format.getFormat("@")); // 设置为文本格式
HSSFFont headfont = workbook.createFont();
headfont.setFontName(fontName);
headfont.setFontHeightInPoints(fontHeight);// 字体大小
if (isBold) {
headfont.setBold(true);
}
style.setFont(headfont);
style.setAlignment(alignment);
return style;
}
// 递归删除文件夹
public static void deleteFile(File file) {
try {
if (file.exists()) {// 判断文件是否存在
if (file.isFile()) {// 判断是否是文件
file.delete();// 删除文件
} else if (file.isDirectory()) {// 否则如果它是一个目录
File[] filess = file.listFiles();// 声明目录下所有的文件 files[];
if (filess.length != 0) {
for (int i = 0; i < filess.length; i++) {// 遍历目录下所有的文件
deleteFile(filess[i]);// 把每个文件用这个方法进行迭代
}
file.delete();// 删除文件夹
}
file.delete();// 删除文件夹
}
} else {
// logger.audit(ExcelUpAndDownUtil.class, "deleteFile",
// "所删除的文件不存在");
}
} catch (Exception e) {
logger.error("删除文件失败----------------------", e);
}
}
/**
*
* @param response
* @param httpRequest
* @param sheetTitle
* 工作表名称 (同时作为第一行的信息展示)
* @param datalist
* 信息内容 List<String>
*/
@SuppressWarnings({ "deprecation" })
public static void exportExcel(HttpServletResponse response, HttpServletRequest httpRequest, String sheetTitle,
List<String> datalist) {
SXSSFWorkbook workbook = new SXSSFWorkbook(5000);
// 字体
Font font = workbook.createFont();
font.setFontName(YAHEI);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
SXSSFSheet sheet2 = workbook.createSheet(sheetTitle);
sheet2.setDefaultColumnWidth((short) 20);
SXSSFRow row = sheet2.createRow((int) 0);
SXSSFCell cell;
cell = row.createCell(0);
cell.setCellType(SXSSFCell.CELL_TYPE_STRING);
cell.setCellValue(sheetTitle);
cell.setCellStyle(cellStyle);
for (int j = 0; j < datalist.size(); j++) {
row = sheet2.createRow(j + 1);
cell = row.createCell(0);
cell.setCellType(SXSSFCell.CELL_TYPE_STRING);
cell.setCellValue(datalist.get(j));
}
datalist.clear();
OutputStream out = null;
try {
String fileName = URLEncoder.encode(sheetTitle, StandardCharsets.UTF_8.name());
response.setContentType("application/download;charset=UTF-8");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setHeader("Content-disposition", "attachment;filename="" + fileName + ".xls"");
out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (Exception e) {
logger.warn("{}:{}", sheetTitle, e.getMessage(), e);
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
logger.error("下载文件的OutputStream对象关闭异常", e);
}
}
try {
workbook.close();
} catch (IOException e) {
logger.error("workbook对象关闭异常", e);
}
}
}
public static final DataTypeEnum getDataType(String value) {
String intRegex = "^[-\+]?[\d]*$";
String numRegex = "^(-?\d+)(\.\d+)?$";
String percent = "%";
if (StringUtils.isBlank(value)) {
return DataTypeEnum.STRING;
}
if (value.matches(intRegex)) {
return DataTypeEnum.INT;
}
if (value.matches(numRegex)) {
return DataTypeEnum.NUM;
}
if (value.contains(percent) && value.endsWith("%")) {
return DataTypeEnum.PERCENT;
}
return DataTypeEnum.STRING;
}
}