PoiUtil.java

package com.boco.nbd.wios.export;

import com.boco.nbd.cams.core.constant.CamsConstant;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.config.Configure;
import com.deepoove.poi.policy.HackLoopTableRenderPolicy;
import com.ihidea.core.util.DateUtilsEx;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * poi工具类
 * 
 * @author xgl
 * @version [v1.6, 2019年4月2日]
 */
public class PoiUtil {
    
    public static HSSFWorkbook initExcel(String[][] titles, String fileName) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle ztStyle = wb.createCellStyle();
        ztStyle.setAlignment(HorizontalAlignment.CENTER);
        ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font ztFont = wb.createFont();
        ztFont.setColor(Font.COLOR_NORMAL);
        ztFont.setFontHeightInPoints((short)14);
        ztFont.setBold(true);
        ztStyle.setFont(ztFont);
        
        HSSFSheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        for (int j = 0; j < titles.length; j++) {
            HSSFRow headRow = sheet.createRow(j);
            for (int k = 0; k < titles[j].length; k++) {
                HSSFCell headRowCell = headRow.createCell(k);
                headRowCell.setCellStyle(ztStyle);
                headRowCell.setCellValue(titles[j][k]);
            }
        }
        if (fileName.startsWith("评价结果")) {
            CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
            CellRangeAddress region1 = new CellRangeAddress(0, 1, 1, 1);
            CellRangeAddress region2 = new CellRangeAddress(0, 1, 2, 2);
            CellRangeAddress region3 = new CellRangeAddress(0, 1, 3, 3);
            CellRangeAddress region4 = new CellRangeAddress(0, 1, 4, 4);
            CellRangeAddress region5 = new CellRangeAddress(0, 0, 5, 8);
            CellRangeAddress region9 = new CellRangeAddress(0, 1, 9, 9);
            sheet.addMergedRegion(region);
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region4);
            sheet.addMergedRegion(region5);
            // sheet.addMergedRegion(region6);
            // sheet.addMergedRegion(region7);
            // sheet.addMergedRegion(region8);
            sheet.addMergedRegion(region9);
        }
        
        return wb;
    }
    
    public static void downloadExcelToWebSite(HSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
        // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-disposition",
            "attachment; filename=" + new String(
                (URLEncoder.encode(fileName, CamsConstant.UTF_8) + "-" + DateUtilsEx.formatToString(new Date(), "yyyyMMddHHmmss") + ".xls").getBytes(),
                "ISO-8859-1"));
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                // wb.dispose();
                wb.close();
            }
            if (os != null) {
                os.close();
            }
        }
    }
    
    public static void exportExcelToWebsite(HttpServletResponse response, String fileName, String[][] titles, String[] fields,
                                            List<?> dataList) throws Exception {
        
        // 初始化EXCEL
        HSSFWorkbook wb = initExcel(titles, fileName);
        exportExcel(response, fileName, titles, fields, dataList, wb);
    }
    
    public static void exportExcel(HttpServletResponse response, String fileName, String[][] titles, String[] fields, List<?> dataList,
                                   HSSFWorkbook wb) throws Exception {
        if (dataList != null) {
            int sheetCount = wb.getNumberOfSheets();
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            for (int i = 0; i < sheetCount; i++) {
                HSSFSheet eachSheet = wb.getSheetAt(i);
                for (int row = 0; row < dataList.size(); row++) {
                    Object rowData = dataList.get(row);
                    HSSFRow eachDataRow = eachSheet.createRow(row + titles.length);
                    for (int col = 0; col < titles[0].length; col++) {
                        Object valueObj = null;
                        if (rowData instanceof Map<?, ?>) {
                            // map类型
                            valueObj = ((Map<?, ?>)rowData).get(fields[col]);
                        } else {
                            // 实体类
                            PropertyDescriptor pd = new PropertyDescriptor(fields[col], rowData.getClass());
                            valueObj = pd.getReadMethod().invoke(rowData);
                        }
                        // if (valueObj != null && valueObj.toString().startsWith("https://")) {
                        // ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        // URL url = new URL(valueObj.toString());
                        // RenderedImage bufferImg = ImageIO.read(url);
                        // ImageIO.write(bufferImg, "jpg", byteArrayOut);
                        //
                        // HSSFPatriarch patriarch = eachSheet.createDrawingPatriarch();
                        // // anchor主要用于设置图片的属性
                        // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)col, row + 1, (short)(col +
                        // 1), (row + 2));
                        // anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
                        // // 插入图片
                        // patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(),
                        // HSSFWorkbook.PICTURE_TYPE_JPEG));
                        // } else {
                            HSSFCell cell = eachDataRow.createCell(col);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(valueObj == null ? "" : valueObj.toString());
                        // }
                    }
                }
                
            }
        }
        downloadExcelToWebSite(wb, response, fileName);
    }
    
    // @SuppressWarnings("unchecked")
    // public static void dynamicExportExcelToWebsite(HttpServletResponse response, String fileName, Map<String,
    // List<Object>> exportMap)
    // throws Exception {
    // List<Object> fieldsObjList = exportMap.get("fields");
    // List<Object> dataObjList = exportMap.get("data");
    // List<String> fields = new ArrayList<>();
    // List<String> titles = new ArrayList<>();
    // for (int i = 0; i < fieldsObjList.size(); i++) {
    // Object obj = fieldsObjList.get(i);
    // if (obj != null) {
    // Map<String, String> map = (Map<String, String>)obj;
    // fields.add(map.get("key"));
    // titles.add(map.get("name"));
    // }
    // }
    // String[] titlesArr = new String[titles.size()];
    // String[] fieldsArr = new String[fields.size()];
    // exportExcelToWebsite(response, fileName, titles.toArray(titlesArr), fields.toArray(fieldsArr), dataObjList);
    // }
    
    public static void exportOrderDocx(HttpServletResponse response, Map<String, Object> dataMap, String fileName,
                                       String templateName) throws Exception {
        HackLoopTableRenderPolicy policy = new HackLoopTableRenderPolicy();
        Configure config = Configure.newBuilder().bind("picFiles", policy).build();
        
        ClassPathResource resource = new ClassPathResource("static/templates/" + templateName + ".docx");
        InputStream inputStream = resource.getInputStream();
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        XWPFTemplate template = XWPFTemplate.compile(inputStream, config).render(dataMap);
        
        OutputStream out = response.getOutputStream();
        BufferedOutputStream bos = new BufferedOutputStream(out);
        template.write(bos);
        template.close();
        bos.flush();
        bos.close();
        out.flush();
        out.close();
    }
    
}