文档章节

poi导出excel有用的工具类整理

o
 osc_fmg49rzg
发布于 2019/03/20 10:29
字数 3940
阅读 8
收藏 0

精选30+云产品,助力企业轻松上云!>>>

package com.sjdf.erp.common.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
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.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.sjdf.erp.common.annotation.ExcelDesc;
import com.sjdf.erp.common.annotation.IsDictionary;
import com.sjdf.erp.common.constant.CommonPlatformConstant;
import com.sjdf.erp.common.constant.ConstBusiness;
import com.sjdf.erp.common.constant.purchase.Msg;
import com.sjdf.erp.common.dictionary.bean.sys.PlatformConstant;
import com.sjdf.erp.common.dictionary.cache.ConfigManager;
import com.sjdf.erp.common.net.HttpSocket;
import com.sjdf.erp.common.vo.Message;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class ExcelUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    public static final String XLSX = ".xlsx";
    public static final String XLS=".xls";
    public static final int IMG_HEIGTH = 30; // 图片高度显示像素
    public static final int IMG_WIDTH = 30; // 图片宽度显示像素

    /**
     * 解析Excel文件(.xls和.xlsx都支持)
     * @param file Excel的File对象
     * @return 解析后的JSONArray对象
     * @throws Exception
     */
    public static Message readExcel(File file) throws Exception {
        if (file == null || file.getName() == null) {
            throw new NullPointerException(Msg.UPLOAD_FILE_NULL);
        }
        String fileName = file.getName().toLowerCase();
        Workbook book = null;
        if (fileName.endsWith(XLSX)) {
            book = new XSSFWorkbook(file);
        } else if (fileName.endsWith(XLS)) {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
            book = new HSSFWorkbook(poifsFileSystem);
        }
        if (book != null) {
            Message message = read(book);
            book.close();
            return message;
        }
        return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
    }

    /**
     * 解析Excel文件(.xls和.xlsx都支持)
     * @param file Excel的MultipartFile 对象
     * @return 解析后的JSONArray对象
     * @throws Exception
     */
    public static Message readExcel(MultipartFile file)throws Exception {
        if (file == null || file.getOriginalFilename() == null) {
            return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
        }
        Workbook book = null;
        String name = file.getOriginalFilename().toLowerCase();
        if (name.endsWith(XLSX)) {
            book =new XSSFWorkbook(file.getInputStream());
        } else if (name.endsWith(XLS)) {
            book = new HSSFWorkbook(file.getInputStream());
        }
        if (book != null) {
            Message message = read(book);
            book.close();
            return message;
        }
        return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
    }

    /**
     * 读取不定列excel
     * @param file
     * @return List<List<String>>
     * @throws Exception
     */
    public static Message readIndeterminateExcel(MultipartFile file)throws Exception {
        if (file == null || file.getOriginalFilename() == null) {
            return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
        }
        Workbook book = null;
        String name = file.getOriginalFilename().toLowerCase();
        if (name.endsWith(XLSX)) {
            book =new XSSFWorkbook(file.getInputStream());
        } else if (name.endsWith(XLS)) {
            book = new HSSFWorkbook(file.getInputStream());
        }
        if (book != null) {
            Message message = readIndeterminate(book);
            book.close();
            return message;
        }
        return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
    }

    private static Message readIndeterminate(Workbook book) throws IOException {
        Sheet sheet = book.getSheetAt(0); 
        int rowEnd = sheet.getLastRowNum(); // 尾行下标
        Row firstRow = sheet.getRow(0);
        if (firstRow == null) {
            return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
        }
        List<List<String>> data = new ArrayList<>();
        for(int i = 0; i <= rowEnd ; i++) {
            Row eachRow = sheet.getRow(i);
            if(eachRow == null) {
                continue;
            }
            int cellEnd = eachRow.getLastCellNum();
            List<String> rowData = new ArrayList<>();
            data.add(rowData);
            for (int k = 0; k < cellEnd; k++) {
                    String val = null;
                    try {
                        val = getValue(eachRow.getCell(k));
                    } catch (Exception e) {
                        LOGGER.error(i + "行" + k + "列获取值错误", e);
                    }
                    rowData.add(val);
            }
        }
        Message message = Message.createMessage();
        message.setReturnData(data);
        return message;
    }

    /**
     * 获取excel 表头
     * @param file Excel的MultipartFile 对象
     * @return 返回解析json
     * @throws IOException 
     */
    public static Message getExcelHead(MultipartFile file) throws IOException {
        if (file == null || file.getOriginalFilename() == null) {
            return Message.createMessage(Msg.UPLOAD_FILE_NULL, Collections.emptyList());
        }
        Workbook book = null;
        String name = file.getOriginalFilename().toLowerCase();
        if (name.endsWith(XLSX)) {
            book =new XSSFWorkbook(file.getInputStream());
        } else if (name.endsWith(XLS)) {
            book = new HSSFWorkbook(file.getInputStream());
        }
        if (book != null) {
            Sheet sheetAt = book.getSheetAt(0);
            int firstRowNum = sheetAt.getFirstRowNum();
            Row row = sheetAt.getRow(firstRowNum);
            if (row == null) {
                return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
            }
            int cellStart = row.getFirstCellNum();
            int cellEnd = row.getLastCellNum();
            JSONObject obj = new JSONObject();
            Integer num = 0;
            for (int j = cellStart; j < cellEnd; j++) {
                // 表头遇到空格跳过
                String val = getValue(row.getCell(j));
                if (val == null || val.trim().length() == 0) {
                    continue;
                }
                obj.put(num, val);
                num += 1;
            }
            book.close();
            if (obj.isEmpty()) {
                return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
            }
            Message message = Message.createMessage();
            message.setReturnData(obj);
            return message;
        }
        return Message.createMessage(Msg.FILE_MUST_BE_XLS_OR_XLSX, Collections.emptyList());
    }

    /**
     * 解析数据
     * @param sheet 表格sheet对象
     * @param book 用于流关闭
     * @return excel表转换后的JSONArray对象
     * @throws IOException
     */
    private static Message read(Workbook book) throws IOException {
        Sheet sheet = book.getSheetAt(0); 
        int rowStart = sheet.getFirstRowNum(); // 首行下标
        int rowEnd = sheet.getLastRowNum(); // 尾行下标
        // 获取第一行JSON对象键
        Row firstRow = sheet.getRow(rowStart);
        if (firstRow == null) {
            return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
        }
        int cellStart = firstRow.getFirstCellNum();
        int cellEnd = firstRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<Integer, String>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 表头遇到空格停止解析 
            String val = getValue(firstRow.getCell(j));
            if (val == null || val.trim().length() == 0) {
                cellEnd = j;
                break;
            }
            keyMap.put(j,val);
        }
        if (PlatformUtils.isEmpty(keyMap)) {
            return Message.createMessage(Msg.EXCEL_ERROR, Collections.emptyList());
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同,表明只有一行,返回表头数据
        if (rowStart == rowEnd) {
            JSONObject object = new JSONObject();
            for (int i : keyMap.keySet()) {
                object.put(keyMap.get(i), "");
            }
            array.add(object);
            Message message = Message.createMessage();
            message.setReturnData(array);
            return message;
        }
        for(int i = rowStart+1; i <= rowEnd ; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            StringBuffer sb = new StringBuffer();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    Cell cell = eachRow.getCell(k);
                    String val = getValue(cell);
                    sb.append(val); // 所有数据添加到里面,用于判断该行是否为空
                    obj.put(keyMap.get(k),val);
                }
            }
            if (sb.toString().length() > 0) {
                array.add(obj);
            }
        }
        Message message = Message.createMessage();
        message.setReturnData(array);
        return message;
    }

    

    /**
     * 获取每个单元格的数据
     * @param cell 单元格对象
     * @param isKey 是否为键:true-是,false-不是。 如果解析Json键,值为空时报错;如果不是Json键,值为空不报错
     * @return 该单元格数据
     * @throws IOException
     */
    private static String getValue(Cell cell) throws IOException {
        // 空白或空
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK ) {
            return "";
        }
        // 0. 数字 类型
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return df.format(date);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String val = cell.getStringCellValue()+"";
            return getEValue(val);
        }
        // 1. String类型
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return PlatformUtils.xssFilter(val.trim());
        }
        // 2. 公式 CELL_TYPE_FORMULA
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            try {
                double cellValue = cell.getNumericCellValue();
                return String.valueOf(cellValue);
            } catch (Exception e) {
                return "0.00";
            }
        }
        // 4. 布尔值 CELL_TYPE_BOOLEAN
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue()+"";
        }
        // 5. 错误 CELL_TYPE_ERROR
        return "";
    }

    public static String getEValue(String s){
        // 非科学技术方法,直接去掉
        if (!s.contains("E")) {
            return s;
        }
        BigDecimal b = null;
        try {
            b = new BigDecimal(s);
        } catch (Exception e) {
            return s;
        }
        int len = Integer.valueOf(s.split("E")[1]);
        if (len < 0) {
            // 小数情况(如:5.0000000000000001E-4)保留10位,去掉末尾的0
            return b.setScale(10, BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toString();
        } else {
            // 其他情况:5.236E8
            String num = s.split("E")[0];
            if (len > 20) { // len 大于20位的话,数字过大,直接返回字符串
                return s;
            }
            num = num + "00000000000000000000";
            // 小数点向后移动 len 位,不够添0
            return num.replace(".", "").substring(0, num.split("\\.")[0].length() + len);
        }
    }

    /**
     * 解析Excel,获取List<Bean>
     * @param c Bean对象类型
     * @param file 上传文件Excel
     * @return 数据合法的List<Bean>(前提是Bean属性做了字段验证)
     * @throws Exception 模版不正确异常
     */
    @SuppressWarnings("unchecked")
    public static <T> Message getBeanList(Class<T> c, MultipartFile file) {
        try {
            // 解析上传文件为JsonArray
            JSONArray arr = null;
            Message msg = readExcel(file);
            if (msg.hasErrorMessage()) {
                return msg;
            }
            arr = (JSONArray) msg.getReturnData();
            // 解析List<Bean>
            List<T> list = new ArrayList<T>();
            for (int i = 0; i < arr.size(); i++) {
                JSONObject obj = (JSONObject) arr.get(i);
                Message msg1 = getBean(c, obj);
                if (msg1.hasErrorMessage()) {
                    return msg1;
                } else {
                    list.add((T) msg1.getReturnData());
                }
            }
            Message msg2 = Message.createMessage();
            msg2.setReturnData(list);
            return msg2;
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            return Message.createMessage(e.getMessage(), Collections.emptyList());
        }
    }

    /**
     * 获取单个Bean
     * @param c
     * @param obj
     * @return
     * @throws Exception
     */
    private static <T> Message getBean(Class<T> c, JSONObject obj) throws Exception{
        T t = c.newInstance();
        List<Field> fields = ASMUtils.getDeclaredFieldsRecursionSupper(c);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            String fieldName = field.getName();
            // 获取ExcleDesc注解属性
            ExcelDesc excelDesc = field.getAnnotation(ExcelDesc.class);
            if (excelDesc != null) {
                String cname = excelDesc.cname();
                if (cname == null || cname.trim().length() == 0) { 
                    throw new NullPointerException(String.format("the field '%s' without cname value.", fieldName));
                }
                // 判断是否必须为必须字段,判断cname
                String val = null;
                if (obj.has(cname)) {
                    val = obj.getString(cname);
                } else {
                    if (excelDesc.isRequired()) {
                        return Message.createMessage(Msg.EXCEL_ERROR);
                    } else {
                        continue;
                    }
                }
                // 判断字段是否为空,判断iskey
                if (!PlatformUtils.hasText(val)) {
                    if (excelDesc.isKey()) {
                        return Message.createMessage(Msg.XXX_DATA_CAN_NOT_BE_NULL, cname);
                    }
                    // 非必须字段为空情况不继续解析
                    continue;
                }
                // 获取具体值
                field.setAccessible(true);
                // 获取IsDictionary注解属性
                IsDictionary dict = field.getAnnotation(IsDictionary.class);
                if (dict != null) {
                    int intVal = ConfigManager.getInstance().getAttrByName(dict.clazz(), dict.filed(), val);
                    field.set(t,intVal);
                    continue;
                }
                // 其余情况根据类型赋值
                String fieldClassName = field.getType().getSimpleName();
                try {
                    if ("String".equalsIgnoreCase(fieldClassName)) {
                        field.set(t, val);
                    } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
                        field.set(t, obj.getBoolean(cname));
                    } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
                        field.set(t, obj.getInt(cname));
                    } else if ("double".equalsIgnoreCase(fieldClassName)) {
                        field.set(t, obj.getDouble(cname));
                    } else if ("long".equalsIgnoreCase(fieldClassName)) {
                        field.set(t, obj.getLong(cname));
                    } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
                        field.set(t, new BigDecimal(val));
                    }
                } catch (Exception e) {
                    LOGGER.error(e.getMessage(), e);
                    return Message.createMessage(Msg.XXX_DATA_ERROR, cname);
                }
            }
        }
        Message message = Message.createMessage();
        message.setReturnData(t);
        return message;
    }

    /**
     * Excel导出
     * @param title 导出Excel文件名称
     * @param rowList 第一个List为表头,其余行为表数据
     * @param resp HttpServletResponse 对象。不要用注入的resp,用controler方法中声明的resp。否则会去寻找jsp
     * @throws IOException
     */
    public static void writeExcel(String title,List<List<Object>> rowList,HttpServletResponse resp) throws IOException{
        if (resp == null) {
            throw new NullPointerException("the HttpServletResponse is null");
        }
        SXSSFWorkbook book = warpSingleWorkbook(title, rowList);
        // 响应客户端
        String filename = new String(title.getBytes("UTF-8"), "ISO-8859-1");
        resp.reset();
        resp.setHeader("Content-disposition", "attachment; filename=" + filename +XLS);
        resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 输出Excel文件
        ServletOutputStream outputStream = resp.getOutputStream();
        book.write(outputStream);
        book.close();
        outputStream.close();
    }

    /**
     * Excel导出设置Workbook
     * @param title 导出Excel文件名称
     * @param rowList 第一个List为表头,其余行为表数据
     * @throws IOException
     */
    public static SXSSFWorkbook warpSingleWorkbook(String title,List<List<Object>> rowList) throws IOException {
        String filename = title;
        if (!PlatformUtils.hasText(title)) {
            filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
        }
        if (rowList == null || rowList.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 创建表
        setWorkBookData(book, filename, rowList);
        return book;
    }

    private static void setWorkBookData(SXSSFWorkbook book, String sheetName, List<List<Object>> rowList){
        Sheet sheet = book.createSheet(sheetName);
        Drawing patriarch = sheet.createDrawingPatriarch();
        // 设置表头样式
        CellStyle style = book.createCellStyle();
        // 设置居左
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 检测表头数据(表头不允许数据为空)
        List<Object> head = rowList.get(0);
        for (Object key : head) {
            if (!PlatformUtils.hasText(key.toString())) {
                throw new NullPointerException("there is a blank exist head row");
            }
        }
        // 写数据
        int size = rowList.get(0).size();
        for (int i = 0; i < rowList.size(); i++) {
            List<Object> row = rowList.get(i);
            if (row == null || row.isEmpty()) {
                throw new NullPointerException("the "+(i+1)+"th row is null");
            }
            if (size != row.size()) {
                throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
            }
            Row sr = sheet.createRow(i);
            for (int j = 0; j < row.size(); j++) {
                if (row.get(j) != null && row.get(j) instanceof URL) {
                    URL url = (URL)row.get(j);
                    sr.setHeight((short)(IMG_HEIGTH * IMG_WIDTH));
                    drawPictureIntoExcel(book, patriarch, i, j, url);
                } else {
                    setExcelValue(sr.createCell(j), row.get(j), style);
                }
            }
        }
    }

    /**
     * 导出多页Excel
     * @param sheetMap key为每个页的名称,value为表头行+数据行
     * @return
     * @throws IOException
     */
    public static SXSSFWorkbook warpSingleWorkbook(Map<String, List<List<Object>>> sheetMap) throws IOException {
        SXSSFWorkbook book = new SXSSFWorkbook();
        for (String key : sheetMap.keySet()) {
            String sheetName = Pattern.compile("[[/*#]]").matcher(key).replaceAll("");
            setWorkBookData(book, sheetName, sheetMap.get(key));
        }
        return book;
    }


    /**
     * Excel导出设置Workbook(表头自动换行)
     * @param title 导出Excel文件名称
     * @param rowList 第一个List为表头,其余行为表数据
     * @throws IOException
     */
    public static SXSSFWorkbook getSingleWorkbook(String title,List<List<Object>> rowList) throws IOException {
        String filename = title;
        if (!PlatformUtils.hasText(title)) {
            filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
        }
        if (rowList == null || rowList.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 创建表
        Sheet sheet = book.createSheet(filename);
        Font headFont = book.createFont();
        Drawing patriarch = sheet.createDrawingPatriarch();
        // 设置表头样式
        XSSFCellStyle style = (XSSFCellStyle)book.createCellStyle();
        CellStyle headStyle = book.createCellStyle();
        // 设置居左
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
        headStyle.setWrapText(true);// 自动换行
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
        headStyle.setFont(headFont);
        // 检测表头数据(表头不允许数据为空)
        List<Object> head = rowList.get(0);
        Row headRow = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            if (!PlatformUtils.hasText(head.get(i).toString())) {
                book.close();
                throw new NullPointerException("there is a blank exist head row");
            }
            setExcelValue(headRow.createCell(i), head.get(i), headStyle);
        }
        // 写数据
        style.setWrapText(true);
        createBodyData(rowList, book, sheet, style, patriarch, false);
        return book;
    }

    /**
     * Excel写网络图片
     * @param wb
     * @param patriarch
     * @param rowIndex
     * @param url
     */
    private static void drawPictureIntoExcel(SXSSFWorkbook wb,Drawing patriarch,int rowIndex, int cloumIndex, URL url){
        // rowIndex代表当前行
        try(InputStream is = url.openStream(); ByteArrayOutputStream swapStream = new ByteArrayOutputStream();) {
            byte[] buff = new byte[100];
            int rc = 0;
            while ((rc = is.read(buff, 0, 100)) > 0) {
                swapStream.write(buff, 0, rc);
            }
            // 设置图片位置
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cloumIndex, rowIndex, cloumIndex+1, rowIndex+1);
            anchor.setAnchorType(0);
            patriarch.createPicture(anchor, wb.addPicture(swapStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            swapStream.close();
        } catch (Exception e) {
            LOGGER.error("图片请求失败:" + url.toString());
        }
    }

    /**
     * Excel导出
     * @param title 导出Excel文件名称
     * @param sheets 包含多个模块的sheets也就是多个rowList,rowList 第一个List为表头,其余行为表数据 
     * object 是一个list<Object>
     * @param resp HttpServletResponse 对象
     * @throws IOException
     */
    public static void writeExcels(String title, List<List<Object>> sheets, HttpServletResponse resp) throws IOException {
        if (resp == null) {
            throw new NullPointerException("the HttpServletResponse is null");
        }
        String filename = title;
        if (!PlatformUtils.hasText(filename)) {
            filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
        }
        if (sheets == null || sheets.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        // 创建表
        HSSFWorkbook book = wrapMultWorkbook(filename, sheets);
        // 响应客户端
        filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
        resp.reset();
        resp.setHeader("Content-disposition", "attachment; filename=" + filename +XLS);
        resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 输出Excel文件
        book.write(resp.getOutputStream());
        book.close();
    }

    /**
     * Excel导出到项目目录下
     * @param title 导出Excel文件名称
     * @param sheets 包含多个模块的sheets也就是多个rowList,rowList 第一个List为表头,其余行为表数据 
     * object 是一个list<Object>
     * @param filePath 
     * @param resp HttpServletResponse 对象
     * @throws IOException
     */
    public static String writeExcelInWeb(String title, List<List<Object>> sheets, String filePath) throws IOException {
        String filename = title;
        if (!PlatformUtils.hasText(filename)) {
            filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
        }
        if (sheets == null || sheets.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        // 创建表
        String fileName = filename +".xls";
        HSSFWorkbook book = wrapMultWorkbook(filename, sheets);
        FileOutputStream stream = new FileOutputStream(filePath +"/" + fileName);
        book.write(stream);
        book.close();
        return fileName;
    }

    /**
     * 将数据写入多个Excel sheet中
     * @param title 标题
     * @param sheets sheet
     * @param book Excel对象
     * @throws IOException
     */
    public static HSSFWorkbook wrapMultWorkbook(String title, List<List<Object>> sheets) throws IOException {
        HSSFWorkbook book = new HSSFWorkbook();
        for (int p = 0; p < sheets.size(); p++) {
            HSSFSheet sheet = book.createSheet(title+"_"+p);
            sheet.setDefaultRowHeightInPoints(CommonPlatformConstant.INT_15);
            // 检测表头数据(表头不允许数据为空)
            @SuppressWarnings("unchecked")
            List<String> head = (List<String>) sheets.get(p).get(0);
            for (Object key : head) {
                if (!PlatformUtils.hasText(key.toString())) {
                    book.close();
                    throw new NullPointerException("there is a blank exist head row");
                }
            }
            // 写数据
            @SuppressWarnings("unchecked")
            List<Object> data = (List<Object>) sheets.get(p).get(0);
            int size = data.size();
            for (int i = 0; i < sheets.get(p).size(); i++) {
                @SuppressWarnings("unchecked")
                List<Object> row = (List<Object>) sheets.get(p).get(i);
                if (row == null || row.isEmpty()) {
                    book.close();
                    throw new NullPointerException("the "+(i+1)+"th row is null");
                }
                if (size != row.size()) {
                    book.close();
                    throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
                }
                HSSFRow sr = sheet.createRow(i);
                for (int j = 0; j < row.size(); j++) {
                    if (row.get(j) == null) {
                        sr.createCell(j).setCellValue("");
                    } else {
                        Object value = row.get(j);
                        if (value instanceof Integer) {
                            HSSFCell cell = sr.createCell(j);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(Double.valueOf(value.toString()));
                        } else if (value instanceof BigDecimal) {
                            HSSFCell cell = sr.createCell(j);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(((BigDecimal)value).setScale(CommonPlatformConstant.INT_3, RoundingMode.HALF_UP).doubleValue());
                        } else {
                            sr.createCell(j).setCellValue(value.toString());
                        }
                    }
                }
            }
            for (int i = 0; i < head.size(); i++) {
                sheet.autoSizeColumn(i);
            }
        }
        return book;
    }

    /**
     * 设置Excel浮点数可做金额等数据统计
     * @param cell 单元格类
     * @param value 传入的值
     */
    public static void setExcelValue(Cell cell,  Object value, CellStyle style){
        cell.setCellStyle(style);
        // 写数据
        if (value == null) {
            cell.setCellValue("");
        }else {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Long.valueOf(value.toString()));
            } else if (value instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal)value).setScale(CommonPlatformConstant.INT_3, RoundingMode.HALF_UP).doubleValue());
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }

    /**
     * 设置Excel表体数据
     * @param rowList 表数据 : index 为0 为表头
     * @param hasHead  是否包含表头
     */
    public static void createBodyData(List<List<Object>> rowList, SXSSFWorkbook book, Sheet sheet, XSSFCellStyle style, Drawing patriarch, Boolean hasHead) throws IOException{
        int size = rowList.get(0).size();
        int startNum = 1;
        if(hasHead) {
            startNum = 0;
        }
        for (int i = startNum; i < rowList.size(); i++) {
            List<Object> row = rowList.get(i);
            if (row == null || row.isEmpty()) {
                book.close();
                throw new NullPointerException("the "+(i+1)+"th row is null");
            }
            if (size != row.size()) {
                book.close();
                throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
            }
            Row sr = sheet.createRow(i);
            for (int j = 0; j < row.size(); j++) {
                if (patriarch != null && row.get(j) != null && row.get(j) instanceof URL) {
                    URL url = (URL)row.get(j);
                    sr.setHeight((short)(IMG_HEIGTH * IMG_WIDTH));
                    drawPictureIntoExcel(book, patriarch, i, j, url);
                } else {
                    setExcelValue(sr.createCell(j), row.get(j), style);
                }
            }
        }
    }

    /**
     * Excel单行设置值一般用于合并单元格
     * @param row 行对象
     */
    public static void writeSingleRowExcel(Row row, List<Object> rowList, CellStyle style){
        if (row == null) {
            throw new NullPointerException("the HSSFRow is null");
        }
        if (PlatformUtils.isEmpty(rowList)) {
            return;
        }
        // 设置行数据
        for (int i = 0; i < rowList.size(); i++) {
            setExcelValue(row.createCell(i), rowList.get(i), style);
        }
    }

    /**
     * 将Excel转化为输入流
     * @param book Excel对象
     * @return 输入流
     * @throws IOException
     */
    public static InputStream sxssfWorkbookToInputStream(SXSSFWorkbook book) throws IOException {
        ByteArrayInputStream bais = null;
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        book.write(baos);
        bais = new ByteArrayInputStream(baos.toByteArray());
        baos.close();
        book.close();
        return bais;
    }

    /**
     * 上传临时文件
     * @param url
     * @param fileName  文件名
     * @param is 文件输入流
     * @return 文件返回路径
     * @throws Exception
     */
    public static String uploadTempFile(String url, String fileName, InputStream is) throws Exception {
        HttpSocket socket = new HttpSocket();
        socket.setUrl(url);
        // 为了能使用到华为存储的自动删除功能, 将临时文件全部放到comId=1的目录下
        socket.addParameter(ConstBusiness.COM_ID, "1");
        socket.addParameter("savePath", ConfigManager.getInstance().getValue(PlatformConstant.class, PlatformConstant.PICTURE_SERVER_PATH));
        socket.addAttachment(fileName, fileName, "text/plain", is);
        socket.doPostMultipart();
        is.close();
        JSONObject result = JSONObject.fromObject(socket.getResponseData());
        if (result.getInt("code") > 0) {
            throw new RuntimeException(result.getString("msg"));
        }
        return result.getString("msg");
    }

    /**
     * Excel导出设置Workbook(包含下载图片)
     * @param title 导出Excel文件名称
     * @param rowList 第一个List为表头,其余行为表数据
     * @param downLoadPic 是否下载图片 (如果要下载图片,图片的信息放Excel维度的第一列)
     * @throws IOException
     */
    public static HSSFWorkbook wrapSingleWorkbook(String title,List<List<Object>> rowList, Boolean downLoadPic) throws IOException {
        String filename = title;
        if (!PlatformUtils.hasText(title)) {
            filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
        }
        if (rowList == null || rowList.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        HSSFWorkbook book = new HSSFWorkbook();
        // 创建表
        HSSFSheet sheet = book.createSheet(filename);
        // 设置单元格默认宽度为15个字符
        sheet.setDefaultColumnWidth(15);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 设置表头样式
        HSSFCellStyle style = book.createCellStyle();
        // 设置居左
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 这种自动换行
        style.setWrapText(true);
        // 检测表头数据(表头不允许数据为空)
        List<Object> head = rowList.get(0);
        for (Object key : head) {
            if (!PlatformUtils.hasText(key.toString())) {
                book.close();
                throw new NullPointerException("there is a blank exist head row");
            }
        }
        // 写数据
        int size = rowList.get(0).size();
        for (int i = 0; i < rowList.size(); i++) {
            List<Object> row = rowList.get(i);
            if (row == null || row.isEmpty()) {
                book.close();
                throw new NullPointerException("the "+(i+1)+"th row is null");
            }
            if (size != row.size()) {
                book.close();
                throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
            }
            HSSFRow sr = sheet.createRow(i);
            for (int j = 0; j < row.size(); j++) {
                // 在每行的第一个单元格插入图片
                if (downLoadPic && i > 0 && j == 0) {
                    sr.setHeight((short) (800));
                    drawPictureIntoExcel(book, patriarch, i, row.get(0).toString());
                } else {
                    HSSFCell cell = sr.createCell(j);
                    setExcelValue(cell, row.get(j), style);
                }
            }
        }
        return book;
    }

    /**将图片写入excel
     * @param wb
     * @param patriarch 
     * @param rowIndex 当前行数
     * @param pictureUrl 图片链接
     * 
     */
    private static void drawPictureIntoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, int rowIndex, String pictureUrl) {
        try {
            if (PlatformUtils.hasText(pictureUrl)) {
                URL url = new URL(pictureUrl);
                // 打开链接
                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                // 设置请求方式为"GET"
                conn.setRequestMethod("GET");
                // 超时响应时间为5秒
                conn.setConnectTimeout(5 * 1000);
                // 通过输入流获取图片数据
                InputStream inStream = conn.getInputStream();
                // 得到图片的二进制数据,以二进制封装得到数据
                byte[] data = readInputStream(inStream);
                // anchor主要用于设置图片的位置
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, rowIndex, (short) 1, rowIndex+1);
                // Sets the anchor type (图片在单元格的位置)
                // 0 = Move and size with Cells, 2 = Move but don't size with
                // cells, 3 = Don't move or size with cells.
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
            }
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

    private static byte[] readInputStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        // 创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        // 每次读取的字符串长度,如果为-1,代表全部读取完毕
        int len = 0;
        // 使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            // 用输出流往buffer里写入数据,中间参数代表从哪个位置开始读,len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        // 关闭输入流
        inStream.close();
        // 把outStream里的数据写入内存
        return outStream.toByteArray();
    }

}

 

上一篇: 守护线程的使用
下一篇: 有趣的东西
o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Java使用POI导出Excel表格

1.导入POI相关的依赖(注意版本需要一致) 1 <dependency> 2 <groupId>org.apache.poi</groupId> 3 <artifactId>poi</artifactId> 4 <version>3.9</version> 5 </dependency> 6 <dependency> 7......

osc_x0g6o8z1
2018/12/27
1
0
关于PoiUtil工具类整理过程中的一些问题

我的上一篇博文已经说了为什么会去整理PoiUtil这个工具类,整理的过程其实也是有趣又漫长(耗时差不多两天)的过程,期间遇到的一些问题做记录如下: 1、导出多sheet的excel,sheet样式丢失问...

彭苏云
2013/08/30
1.2K
0
easyExcel使用以及踩过的坑

easyExcel介绍: Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还...

osc_3s8xkap7
2019/05/24
7
0
Easy Poi入门

最近有一个需求,就是把excel中的内容,解析成Json对象格式的文件输出。 然后就上网找了一波资料,大神们都说用POI来做。但是我看了一下POI的解析过程,但是为了秉着高效的原则,花最少的时间...

osc_6lgm3hxt
2018/11/21
8
0
word的模板引擎

之前有用excel的模板来导出excel其实word也是可以用模板来导出的。poi-tl文档 Sayi/poi-tl Java Word 的模板引擎 poi-tl 几种解析Word文档的Java类库比较 easypoi...

miaojiangmin
2018/09/16
139
0

没有更多内容

加载失败,请刷新页面

加载更多

如果你失明了,你怎么编程? - How can you program if you're blind?

问题: Sight is one of the senses most programmers take for granted. 视觉是大多数程序员认为理所当然的感官之一。 Most programmers would spend hours looking at a computer monitor......

技术盛宴
今天
16
0
如何删除使用Python的easy_install安装的软件包? - How do I remove packages installed with Python's easy_install?

问题: Python's easy_install makes installing new packages extremely convenient. Python的easy_install使安装新包非常方便。 However, as far as I can tell, it doesn't implement th......

fyin1314
今天
11
0
如何将逗号分隔的字符串转换为数组? - How to convert a comma separated string to an array?

问题: I have a comma separated string that I want to convert into an array, so I can loop through it. 我有一个逗号分隔的字符串,我想将其转换成数组,因此可以循环遍历它。 Is the...

富含淀粉
今天
13
0
深源恒际:担心个人身份被冒用?不存在!

本文作者:c****t 近日,苟晶被冒名顶替身份参加高考的事件在社会各界掀起广泛热议。事件调查结果公布后,舆论风向逆转,吃瓜群众认为当事人夸大其词消费了公众情绪,一边对当事人所遭遇的不...

百度开发者中心
昨天
5
0
CKEditor 5 + SpringBoot实战(三):SpringData JPA数据持久化

在本系列的文章中,我将介绍如何在Spring Boot Application中使用CKEditor编辑器。介绍的内容包括基本环境的搭建,文件上传,SpringData JPA数据持久化,CKEditor5的安装,CKEditor图片上传,...

树下魅狐
今天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部