文件导出

刚哥哥不想说话
259 阅读2分钟

excel 文件导出

1.添加依赖

org.apache.poi poi-ooxml 3.16 ``` ``` 2.代码 ``` ``` package com.sunfitlink.zhcc.test;

import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.; import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.; import java.math.BigDecimal; import java.math.RoundingMode; import java.net.URL; import java.text.NumberFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set;

/**

  • Excel导入导出工具类 */ public class ExcelUtils {

    private static final String XLSX = ".xlsx"; private static final String XLS = ".xls"; public static final String ROW_MERGE = "row_merge"; public static final String COLUMN_MERGE = "column_merge"; private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss"; private static final String ROW_NUM = "rowNum"; private static final String ROW_DATA = "rowData"; private static final String ROW_TIPS = "rowTips"; private static final int CELL_OTHER = 0; private static final int CELL_ROW_MERGE = 1; private static final int CELL_COLUMN_MERGE = 2; private static final int IMG_HEIGHT = 30; private static final int IMG_WIDTH = 30; private static final char LEAN_LINE = '/'; private static final int BYTES_DEFAULT_LENGTH = 10240; private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    public static void export(HttpServletResponse response, String fileName, List<List> sheetDataList) { export(response, fileName, fileName, sheetDataList, null); }

    public static void export(HttpServletResponse response, String fileName, String sheetName, List<List> sheetDataList, Map<Integer, List> selectMap) { export(response, null, fileName, sheetName, sheetDataList, selectMap); }

    private static void export(HttpServletResponse response, File file, String fileName, String sheetName, List<List> sheetDataList, Map<Integer, List> selectMap) { // 整个 Excel 表格 book 对象 SXSSFWorkbook book = new SXSSFWorkbook(); // 每个 Sheet 页 Sheet sheet = book.createSheet(sheetName); Drawing<?> patriarch = sheet.createDrawingPatriarch(); // 设置表头背景色（灰色） CellStyle headStyle = book.createCellStyle(); headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index); headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headStyle.setAlignment(HorizontalAlignment.CENTER); headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); // 设置表身背景色（默认色） CellStyle rowStyle = book.createCellStyle(); rowStyle.setAlignment(HorizontalAlignment.CENTER); rowStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置表格列宽度（默认为15个字节） sheet.setDefaultColumnWidth(15); // 创建合并算法数组 int rowLength = sheetDataList.size(); int columnLength = sheetDataList.get(0).size(); int[][] mergeArray = new int[rowLength][columnLength]; for (int i = 0; i < sheetDataList.size(); i++) { // 每个 Sheet 页中的行数据 Row row = sheet.createRow(i); List rowList = sheetDataList.get(i); for (int j = 0; j < rowList.size(); j++) { // 每个行数据中的单元格数据 Object o = rowList.get(j); int v = 0; if (o instanceof URL) { // 如果要导出图片的话, 链接需要传递 URL 对象 setCellPicture(book, row, patriarch, i, j, (URL) o); } else { Cell cell = row.createCell(j); if (i == 0) { // 第一行为表头行，采用灰色底背景 v = setCellValue(cell, o, headStyle); } else { // 其他行为数据行，默认白底色 v = setCellValue(cell, o, rowStyle); } } mergeArray[i][j] = v; } } // 合并单元格 mergeCells(sheet, mergeArray); // 设置下拉列表 setSelect(sheet, selectMap); // 写数据 if (response != null) { // 前端导出 try { write(response, book, fileName); } catch (IOException e) { e.printStackTrace(); } } else { // 本地导出 FileOutputStream fos; try { fos = new FileOutputStream(file); ByteArrayOutputStream ops = new ByteArrayOutputStream(); book.write(ops); fos.write(ops.toByteArray()); fos.close(); } catch (Exception e) { e.printStackTrace(); } } }

    /**

    • 合并当前Sheet页的单元格
    • @param sheet 当前 sheet 页
    • @param mergeArray 合并单元格算法 */ private static void mergeCells(Sheet sheet, int[][] mergeArray) { // 横向合并 for (int x = 0; x < mergeArray.length; x++) { int[] arr = mergeArray[x]; boolean merge = false; int y1 = 0; int y2 = 0; for (int y = 0; y < arr.length; y++) { int value = arr[y]; if (value == CELL_COLUMN_MERGE) { if (!merge) { y1 = y; } y2 = y; merge = true; } else { merge = false; if (y1 > 0) { sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2)); } y1 = 0; y2 = 0; } } if (y1 > 0) { sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2)); } } // 纵向合并 int xLen = mergeArray.length; int yLen = mergeArray[0].length; for (int y = 0; y < yLen; y++) { boolean merge = false; int x1 = 0; int x2 = 0; for (int x = 0; x < xLen; x++) { int value = mergeArray[x][y]; if (value == CELL_ROW_MERGE) { if (!merge) { x1 = x; } x2 = x; merge = true; } else { merge = false; if (x1 > 0) { sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y)); } x1 = 0; x2 = 0; } } if (x1 > 0) { sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y)); } } }

    private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX; response.addHeader("Content-Disposition", "attachment;filename=" + name); ServletOutputStream out = response.getOutputStream(); book.write(out); out.flush(); out.close(); }

    private static int setCellValue(Cell cell, Object o, CellStyle style) { // 设置样式 cell.setCellStyle(style); // 数据为空时 if (o == null) { cell.setCellType(CellType.STRING); cell.setCellValue(""); return CELL_OTHER; } // 是否为字符串 if (o instanceof String) { String s = o.toString(); if (isNumeric(s)) { cell.setCellType(CellType.NUMERIC); cell.setCellValue(Double.parseDouble(s)); return CELL_OTHER; } else { cell.setCellType(CellType.STRING); cell.setCellValue(s); } if (s.equals(ROW_MERGE)) { return CELL_ROW_MERGE; } else if (s.equals(COLUMN_MERGE)) { return CELL_COLUMN_MERGE; } else { return CELL_OTHER; } } // 是否为字符串 if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) { cell.setCellType(CellType.NUMERIC); cell.setCellValue(Double.parseDouble(o.toString())); return CELL_OTHER; } // 是否为Boolean if (o instanceof Boolean) { cell.setCellType(CellType.BOOLEAN); cell.setCellValue((Boolean) o); return CELL_OTHER; } // 如果是BigDecimal，则默认3位小数 if (o instanceof BigDecimal) { cell.setCellType(CellType.NUMERIC); cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue()); return CELL_OTHER; } // 如果是Date数据，则显示格式化数据 if (o instanceof Date) { cell.setCellType(CellType.STRING); cell.setCellValue(formatDate((Date) o)); return CELL_OTHER; } // 如果是其他，则默认字符串类型 cell.setCellType(CellType.STRING); cell.setCellValue(o.toString()); return CELL_OTHER; }

    private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) { // 设置图片宽高 sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT)); // （jdk1.7版本try中定义流可自动关闭） try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { byte[] buff = new byte[BYTES_DEFAULT_LENGTH]; int rc; while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) { outputStream.write(buff, 0, rc); } // 设置图片位置 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1); // 设置这个，图片会自动填满单元格的长宽 anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE); patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); } catch (Exception e) { e.printStackTrace(); } }

    private static String formatDate(Date date) { if (date == null) { return ""; } SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT); return format.format(date); }

    private static void setSelect(Sheet sheet, Map<Integer, List> selectMap) { if (selectMap == null || selectMap.isEmpty()) { return; } Set<Entry<Integer, List>> entrySet = selectMap.entrySet(); for (Entry<Integer, List> entry : entrySet) { int y = entry.getKey(); List list = entry.getValue(); if (list == null || list.isEmpty()) { continue; } String[] arr = new String[list.size()]; for (int i = 0; i < list.size(); i++) { arr[i] = list.get(i); } DataValidationHelper helper = sheet.getDataValidationHelper(); CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y); DataValidationConstraint dvc = helper.createExplicitListConstraint(arr); DataValidation dv = helper.createValidation(dvc, addressList); if (dv instanceof HSSFDataValidation) { dv.setSuppressDropDownArrow(false); } else { dv.setSuppressDropDownArrow(true); dv.setShowErrorBox(true); } sheet.addValidationData(dv); } }

    private static boolean isNumeric(String str) { if ("0.0".equals(str)) { return true; } for (int i = str.length(); --i >= 0; ) { if (!Character.isDigit(str.charAt(i))) { return false; } } return true; }

    private static String getString(String s) { if (s == null) { return ""; } if (s.isEmpty()) { return s; } return s.trim(); }

    }

avatar
文章
阅读
粉丝