excel中附件的导出、导入解析

excel 导出(模板、非模板（动态表头合并表格）)与解析

开始
1：代码

    @Override
    public void getStatisticsDownloadInfo(String type, HttpServletResponse response,
                                               HttpServletRequest request) throws IOException {
     
        ExportMyWord emw = new ExportMyWord();
        Map<String, Object> exlParam = new HashMap<>();
        String templateName = "";
        if ("模板".equals(type)) {
            List<Map<String, Object>> resultList = new ArrayList<>();
            exlParam.put("resultList", resultList);
            templateName = "统计表.ftl";
        } else {
            dynamicTable(response, request);
            return;
        }
        InputStream input = null;
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("统计.xls", "UTF-8"));
        OutputStream outputStream = response.getOutputStream();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String filePath = File.separator +format.format(new Date())+".xls";
        try {
            emw.createWord(exlParam, templateName, filePath);
            byte[] bytes = new byte[1024 * 50];
            int len;
            File file = new File(filePath);
            input = new FileInputStream(file);
            while ((len = input.read(bytes, 0, 1024)) != -1) {
                outputStream.write(bytes, 0, len);
            }
        } finally {
            if (input != null) {
                input.close();
            }
            outputStream.close();
            FileUtil.clearFiles(filePath);
        }

    }
    private void dynamicTable(HttpServletResponse response, HttpServletRequest request) throws IOException {
        HSSFWorkbook hb = new HSSFWorkbook();
        List<String> headerList = new ArrayList<>();
        List<String> headerListB = new ArrayList<>();
        String sheetName = "";
        String fileName = null;
        List<Map<String, Object>> resultList = new ArrayList<>();
        headerList.add("表头A");
        headerList.add("表头B");
        headerList.add("表头C");
        resultList = new ArrayList<>();
        sheetName = "统计表";
        fileName = "统计表.xls";
        ExcelUtil.createExcelDetail(sheetName, headerList, headerListB, hb);
        int inetRowIndex = 3;
        for (Map<String, Object> map : resultList) {
            List<String> objects = new ArrayList<>();
            List<Map.Entry<String, Object>> list = new ArrayList<>(map.entrySet());

            hb = ExcelUtil.createExcelData(hb, objects, 0, inetRowIndex++, headerList.size());
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("filename", getFilename(fileName, request));
        OutputStream outputStream = response.getOutputStream();
        hb.write(outputStream);
        outputStream.close();
    }

    private String getFilename(String filename, HttpServletRequest request) throws UnsupportedEncodingException {
        String userAgent = request.getHeader("USER-AGENT");//获取浏览器版本
        if (StringUtils.contains(userAgent, "MSIE")) {//IE浏览器
            filename = URLEncoder.encode(filename, "UTF8");
        } else if (StringUtils.contains(userAgent, "Mozilla")) {//google,火狐浏览器
            filename = new String(filename.getBytes(), "ISO8859-1");
        } else {
            filename = URLEncoder.encode(filename, "UTF8");//其他浏览器
        }
        return filename;
    }

工具类

 

  import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 
 * @author xiaofeng
 * @history 修订历史（历次修订内容、修订人、修订时间等）
 **/
public class ExcelUtil {
 

    public static void createExcelDetail(String sheetName, List<String> row_second,
                                         List<String> row_third, HSSFWorkbook wb, StatisticsTableDto statisticsTableDto) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 13);
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        headerStyle.setLocked(true);
        headerStyle.setWrapText(false);
        headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
        headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
        headerStyle.setBorderTop(BorderStyle.THIN);//上边框
        headerStyle.setBorderRight(BorderStyle.THIN);//右边框

        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 400);
        Cell c00 = row.createCell(0);
        c00.setCellValue(sheetName);
        c00.setCellStyle(headerStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, row_second.size() - 1));

        Row r2 = sheet.createRow(1);

        r2.setHeight((short) 300);
        for (int i = 0; i < row_second.size(); i++) {
            Cell tempCell = r2.createCell(i);
            sheet.setColumnWidth(i, Math.max(row_second.get(i).getBytes().length * 256, 12 * 256));
            tempCell.setCellValue(row_second.get(i));
            tempCell.setCellStyle(headerStyle);
        }
         
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, row_second.size() - 1));
            
        Row r3 = sheet.createRow(2);
        r3.setHeight((short) 300);
        for (int i = 0; i < row_third.size(); i++) {
            Cell tempCell = r3.createCell(i);
            sheet.setColumnWidth(i, 12 * 256);
            tempCell.setCellValue(row_third.get(i));
            tempCell.setCellStyle(headerStyle);
        }


    }

    public static HSSFWorkbook createExcelData(HSSFWorkbook excel, List<String> excelData, int sheetAt, int rowIndex, int columnSum) {
        HSSFRow row = excel.getSheetAt(sheetAt).createRow(rowIndex);
        Font headerFont = excel.createFont();
        headerFont.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = excel.createCellStyle();
        cellStyle.setFont(headerFont);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        cellStyle.setLocked(true);
        cellStyle.setWrapText(false);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        for (int i = 0; i < columnSum; i++) {
            excel.getSheetAt(sheetAt).setColumnWidth(0, 3766);
            if (i >= excelData.size()) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue("");
            } else {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(excelData.get(i));
            }
        }
        return excel;
    }
}

老样子 解析见下篇 : 点我