导出excel规避导出文件失败与导出文件无法打开问题
1.首先在controller层需要获取 HttpServletResponse
@ApiOperation(value = "导出", notes = "导出")
@RequestMapping(value = "/exportInfo", method = RequestMethod.POST)
public void exportOperLogInfo(@RequestBody InfoReq infoReq , HttpServletResponse response) throws Exception{
//1.获取请求实体的参数数据存入HashMap()
Map<String, Object> map = new HashMap<String, Object>();
//此处代码省略
//2.进行导出
exportService.export(map,response);
}
2.实现类里进行数据查询与导出操作
public void exportMenuButtonOperLog(Map<String, Object> paramMap, HttpServletResponse response){
//获取数据存入List<Map>
//数据来源省略
List<Map> list= new ArrayList();
//设置路径
String filePath = "";
//文件名
String fileName = "info.xlsx";
//设置表头
String[] header = new String[]{"表头1", "表头2"};
List<String[]> excelData = new ArrayList<>();
//遍历list获取每个map中的数据，根据key对应不同的列
for (Map map : list) {
String[] data = new String[]{
(String) map.get("字段一"),
(String) map.get("字段二")
};
excelData.add(data);
}
ExcelUtil.toExport(filePath, fileName, header, excelData, response);
}
3.使用ExcelUtil中的方法进行导出，以下是ExcelUtil类
public class ExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
public static void buildExcel(SXSSFWorkbook workbook, String[] titles, String sheetName, int sheetNum, List<Map<String, Object>> list,File csvFile) throws Exception {
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(sheetName);
Row sheetRow = sheet.createRow(0);
if (titles != null) {
for (int i = 0; i < titles.length; i++) {
sheetRow.createCell(i).setCellValue(titles[i]);
}
}
if (list != null && list.size() > 0) {
logger.info("===buildExcel====22222222222222========="+list.size());
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
Row listRow = sheet.createRow(i + 1);
int num = 0;
for (String key : map.keySet()) {
listRow.createCell(num++).setCellValue(String.valueOf(map.get(key)));
}
}
list.clear();
}
logger.info("===buildExcel====33333333333333333=========");
writeToFile(workbook, csvFile);
}
public static void createExcel(SXSSFWorkbook workbook, String filePath, String fileName) {
try {
FileOutputStream fout = new FileOutputStream(filePath + fileName);
workbook.write(fout);
fout.flush();
fout.close();
workbook.dispose();
Process pro = Runtime.getRuntime().exec("chmod 777 " + filePath + fileName);
pro.waitFor();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
}
/**
* 读取本地文件
*
* @param workbook
* @param fileName
* @return
* @throws Exception
*/
public static ResponseEntity<byte[]> getResponseEntity(SXSSFWorkbook workbook, String fileName) throws Exception {
fileName = fileName + ".xlsx";
String filePath = "/slview/iot-ssp-server/iot-server/sspfile/tempfile/";
File dir = new File(filePath);
if (!dir.exists()) {
dir.mkdirs();
}
File file = new File(filePath + fileName);
if (!file.exists()) {
file.createNewFile();
}
ExcelUtil.createExcel(workbook, filePath, fileName);
Thread.sleep(1000);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", fileName);
ResponseEntity<byte[]> re = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
if (file.exists()) {
// file.delete();
}
return re;
}
/**
* excel
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public static List getListByExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
// 创建excel工作簿
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
// if(!sheet.getSheetName().equals("结果表")){
// continue;
// }
// 滤过第一行标题
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
List<Object> li = new ArrayList<>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getCellValue(cell));
}
list.add(li);
}
}
work.close();
return list;
}
public static Map<String, List> getSheetListByExcel(InputStream in, String fileName) throws Exception {
Map<String, List> result = new LinkedHashMap<>();
// 创建excel工作簿
Workbook work = getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
List list = new ArrayList<>();
String sheetName = work.getSheetName(i);
// if(!sheet.getSheetName().equals("结果表")){
// continue;
// }
// 滤过第一行标题
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
List<Object> li = new ArrayList<>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getCellValue(cell));
}
list.add(li);
}
result.put(sheetName, list);
}
work.close();
return result;
}
public static String getCellValue(Cell cell) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String cellValue = "";
if (cell == null) {
return cellValue;
}
CellType cellType = cell.getCellTypeEnum();
// 把数字当成String来读，避免出现1读成1.0的情况
// if (cellType == CellType.NUMERIC) {
// cell.setCellType(CellType.STRING);
// }
// 判断数据的类型
switch (cellType) {
case NUMERIC: // 数字、日期
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); // 日期型
} else {
cell.setCellType(CellType.STRING);
cellValue = String.valueOf(cell.getStringCellValue());
// cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
if (cellValue.contains("E")) {
cellValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
}
}
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空值
cellValue = cell.getStringCellValue();
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 判断文件格式
*
* @param in
* @param fileName
* @return
*/
private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
Workbook book = null;
String filetype = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(filetype)) {
book = new HSSFWorkbook(in);
} else if (".xlsx".equals(filetype)) {
book = new XSSFWorkbook(in);
} else {
throw new Exception("请上传excel文件！");
}
return book;
}
/**
* 导出excel，文件名和sheet页名称相同使用该方法
*
* @param fileName
* @param titles
* @param dataList
* @return
*/
public static void toExport(String filePath, String fileName, final String[] titles,
final List<String[]> dataList, HttpServletResponse response) {
try {
toExport(filePath, fileName, null, titles, null, dataList, response);
} catch (Exception e) {
logger.error("导出Excel文件失败！", e);
throw new RuntimeException(e);
}
}
/**
* 创建一个excel文件
*
* @return
*/
private static File createFile(String filePath, String fileName) {
if (StringUtils.isBlank(fileName)) {
synchronized (ExcelUtil.class) {
fileName = new Long(System.currentTimeMillis()).toString();
}
}
File excelFile = new File(filePath + fileName);
if (excelFile != null && !excelFile.exists()) {
try {
File fileParent = excelFile.getParentFile();
if (!fileParent.exists()) {
fileParent.mkdirs();
}
excelFile.createNewFile();
} catch (IOException e) {
logger.error("创建Excel文件失败！", e);
}
}
return excelFile;
}
/**
* 新建sheet页
*
* @param workbook
* @param sheetName
* @return
*/
private static Sheet createSheet(Workbook workbook, String sheetName) {
Sheet sheet = workbook.createSheet(sheetName);
return sheet;
}
/**
* 设置标题
*
* @param workbook
* @param sheet
* @param titles
*/
private static void setTitleCells(Workbook workbook, Sheet sheet, String[] titles, int[] titlesWidth) {
if (workbook != null) {
if (titlesWidth == null || titlesWidth.length != titles.length) {
titlesWidth = new int[titles.length];
for (int i = 0; i < titles.length; i++) {
titlesWidth[i] = 20;
}
}
Row header = sheet.createRow(0);// 第一行为标题行，从0开始
// CellStyle style = getTitleCellStyle(workbook);
for (int i = 0; i < titles.length; i++) {
sheet.setColumnWidth(i, titlesWidth[i] * 256);// in units of 1/256th of a character width),默认为20个字符
Cell cell = header.createCell(i);
// cell.setCellStyle(style);
cell.setCellValue(titles[i]);
}
}
}
/**
* 设置标题
*
* @param workbook
* @param sheet
*/
private static void setAITitleCells(XSSFWorkbook workbook, Sheet sheet) {
if (workbook != null) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
XSSFRow row0 = (XSSFRow) sheet.createRow(0);
row0.createCell(0).setCellValue("稽核账期");
row0.createCell(1).setCellValue("省份");
XSSFCell cell = row0.createCell(2);
cell.setCellStyle(cellStyle);
cell.setCellValue("稽核场景");
XSSFRow row1 = (XSSFRow) sheet.createRow(1);
row1.createCell(2).setCellValue("错误类型");
XSSFCell cell1 = row1.createCell(3);
cell1.setCellStyle(cellStyle);
cell1.setCellValue("关键局数据配置");
XSSFCell cell2 = row1.createCell(6);
cell2.setCellStyle(cellStyle);
cell2.setCellValue("H码号段配置");
XSSFCell cell3 = row1.createCell(9);
cell3.setCellStyle(cellStyle);
cell3.setCellValue("定向流量配置");
XSSFCell cell4 = row1.createCell(12);
cell4.setCellStyle(cellStyle);
cell4.setCellValue("数通设备");
XSSFRow row2 = (XSSFRow) sheet.createRow(2);
row2.createCell(3).setCellValue("错误行数");
row2.createCell(4).setCellValue("确认行数");
row2.createCell(5).setCellValue("确认率");
row2.createCell(6).setCellValue("错误行数");
row2.createCell(7).setCellValue("确认行数");
row2.createCell(8).setCellValue("确认率");
row2.createCell(9).setCellValue("错误行数");
row2.createCell(10).setCellValue("确认行数");
row2.createCell(11).setCellValue("确认率");
row2.createCell(12).setCellValue("错误行数");
row2.createCell(13).setCellValue("确认行数");
row2.createCell(14).setCellValue("确认率");
sheet.addMergedRegion(new CellRangeAddress(0,2,0,0));
sheet.addMergedRegion(new CellRangeAddress(0,2,1,1));
sheet.addMergedRegion(new CellRangeAddress(0,0,2,14));
sheet.addMergedRegion(new CellRangeAddress(1,2,2,2));
sheet.addMergedRegion(new CellRangeAddress(1,1,3,5));
sheet.addMergedRegion(new CellRangeAddress(1,1,6,8));
sheet.addMergedRegion(new CellRangeAddress(1,1,9,11));
sheet.addMergedRegion(new CellRangeAddress(1,1,12,14));
}
}
/**
* 设置数据信息
*
* @param workbook
* @param sheet
* @param dataList
*/
private static void setDataCells(Workbook workbook, Sheet sheet, List<String[]> dataList) {
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i + 1);// 第二行开始
String[] data = dataList.get(i);
for (int j = 0; j < data.length; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(data[j]);
}
}
}
private static String divide(String num, String row) {
String orderRate = "";
if (StringUtils.isEmpty(num) || "0".equals(num)) {
return orderRate;
} else {
row = StringUtils.isEmpty(row) ? "0" : row;
Integer numint = Integer.parseInt(num);
Integer rowint = Integer.parseInt(row);
DecimalFormat df = new DecimalFormat();
df.setMaximumFractionDigits(2); //设置数值的小数部分允许的最大位数。
df.setMinimumFractionDigits(2); //设置数值的小数部分允许的最小位数。
orderRate = df.format(rowint * 100.00 / numint) + "%";
}
return orderRate;
}
/**
* 写入数据到本地excel文件中
*
* @param workbook
* @return
*/
public static void writeToFile(Workbook workbook, File excelFile) {
FileOutputStream fileOut = null;
logger.info("===buildExcel====6666666666666=========");
try {
fileOut = new FileOutputStream(excelFile);
workbook.write(fileOut);
logger.info("===buildExcel====999999999999999999=========");
} catch (FileNotFoundException e) {
logger.error("Excel文件不存在！");
} catch (IOException e) {
logger.error("数据写入到Excel文件失败！");
} finally {
if (fileOut != null) {
try {
fileOut.flush();
fileOut.close();
} catch (IOException e) {
logger.error("关闭文件输出流失败！");
}
}
}
}
/**
* 导出Excel, fileName为空根据当前系统时间自动生成一个文件名，
* sheetName为空，如果fileName不为空，则根据fileName生成sheet页名称，否则生成默认的sheet页名称
*
* @param fileName excel文件名
* @param sheetName excel sheet页名称
* @param titles sheet页列表名称
* @param dataList sheet页列表数据，对应title
* @return 返回excel下载路径。相对于前台根目录
*/
public static void toExport(String filePath, String fileName, String sheetName, final String[] titles,
final int[] titlesWidth, final List<String[]> dataList, HttpServletResponse response) {
FileInputStream myStream = null;
try {
// Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook();
Workbook workbook = null;
String filetype = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(filetype)) {
workbook = new HSSFWorkbook();
} else if (".xlsx".equals(filetype)) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
if (fileName != null && sheetName == null) {
sheetName = fileName;
}
Sheet sheet = createSheet(workbook, sheetName);
// 标题
setTitleCells(workbook, sheet, titles, titlesWidth);
setDataCells(workbook, sheet, dataList);
File excelFile = createFile(filePath, fileName);
if (excelFile != null) {
writeToFile(workbook, excelFile);
}
response.setContentType("application/vnd.ms-excel");
response.setContentLength((int) excelFile.length());
String headerKey = "Content-Disposition";
String headerValue = String.format("attachment; filename=\"%s\"", "" + fileName);
response.setHeader(headerKey, headerValue);
myStream = new FileInputStream(excelFile);
IOUtils.copy(myStream, response.getOutputStream());
response.flushBuffer();
} catch (Exception e) {
logger.error("导出Excel文件失败！", e);
throw new RuntimeException(e);
} finally {
if (myStream != null) {
try {
myStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
