JRTWeb对Excel的支持

之前实现的是Excel的客户端导出模板协议，有的情况需要从Web直接把数据弄成Excel文件下载，或者有时候需要导入Excel数据进入系统，为此需要一个List得到Excel文件的封装和一个Excel文件得到Json串供界面做解析的方法。

测试效果，把查询的打印元素数据存入Excel文件，再用API读取Excel数据到二维Json数组

测试List转Excel和Excel得到json

import JRT.Core.MultiPlatform.JRTContext;
import JRT.Core.Util.ExcelUtil;
import JRT.Model.Entity.JRTPrintTemplateEle;
import JRTBLLBase.BaseHttpHandlerNoSession;

import java.io.File;
import java.io.FileInputStream;
import java.util.List;

/**
 * 测试Excel生成和读取json
 */
public class ashExcelTest  extends BaseHttpHandlerNoSession {
    /**
     * 测试Excel生成和读取json
     * @return
     * @throws Exception
     */
    public String Test() throws Exception
    {
        //查询打印元素数据
        List<JRTPrintTemplateEle> eleList=EntityManager().FindAllSimple(JRTPrintTemplateEle.class,null);
        String savePath=JRTContext.MapPath("../FileService/zlz.xlsx");
        //把数据导出成Excel到指定路径
        ExcelUtil.ListExportExcel(eleList,null, savePath);
        File file = new File(savePath);
        FileInputStream fileInputStream = new FileInputStream(file);
        String json=ExcelUtil.GetExcelJson(fileInputStream,".xlsx");
        return json;
    }
}

Excel工具类

package JRT.Core.Util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Excel相关的工具类,list得到Excel文件或者Excel得到json串
 */
public class ExcelUtil {
    /**
     * 把列表数据导出成Excel文件
     *
     * @param datas
     * @param titleStr
     * @param savePath
     * @param <T>
     * @throws Exception
     */
    public static <T> void ListExportExcel(List<T> datas, List<String> titleStr, String savePath) throws Exception {
        InputStream stream = ListExportExcel(savePath, datas, titleStr);
        //创建输出流对象
        FileOutputStream output = null;
        if (stream != null) {
            File fi = new File(savePath);
            Path path = Paths.get(savePath).getParent();
            //没有父路径就创建
            if (!Files.exists(path)) {
                // 如果路径不存在，则创建路径
                Files.createDirectories(path);
            }
            try {
                int data;
                //创建输出流对象
                output = new FileOutputStream(savePath);
                while ((data = stream.read()) != -1) {
                    // 写入数据到目标位置
                    output.write(data);
                }

            } finally {
                if (stream != null) {
                    stream.close();
                }
                if (output != null) {
                    output.close();
                }
            }
        }
    }

    /**
     * 把列表数据得到Excel的文件流
     *
     * @param surffix  文件后缀.xlsx或.xls
     * @param datas
     * @param titleStr
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> InputStream ListExportExcel(String surffix, List<T> datas, List<String> titleStr) throws Exception {
        try {
            InputStream excelStream = null;
            Workbook hssfworkbook = null;
            if (surffix.indexOf(".xlsx") >= 0) {
                hssfworkbook = new SXSSFWorkbook(new XSSFWorkbook());
            }
            // 2003版本
            else if (surffix.indexOf(".xls") >= 0) {
                hssfworkbook = new XSSFWorkbook();
            } else {
                throw new Exception(surffix + "不是有效的Excel后缀!");
            }
            Sheet sheet = hssfworkbook.createSheet("表单1");
            Row row = null;
            int addIndex = 0;
            if (titleStr != null && titleStr.size() > 0) {
                row = sheet.createRow(0);
                //自动适应宽度
                //sheet.AutoSizeColumn(0);
                addIndex = 1;
                //遍历组装串
                for (int i = 0; i < titleStr.size(); i++) {
                    row.createCell(i).setCellValue(titleStr.get(i).replace("$r$n", ((char) (10)) + "" + ((char) (13))));
                }
            }
            if (datas != null && datas.size() > 0) {
                //提取属性列
                Class c = datas.get(0).getClass();
                Field[] filds = c.getFields();
                //遍历组装串
                for (int i = 0; i < datas.size(); i++) {
                    row = sheet.createRow(i + addIndex);
                    int index = 0;
                    T oneObj = datas.get(i);
                    //遍历属性的集合，创建DataTable
                    for (Field f : filds) {
                        row.createCell(index).setCellValue(ObjToString(f.get(oneObj)));
                        index++;
                    }
                }

            }
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            hssfworkbook.write(out);
            excelStream = new ByteArrayInputStream(out.toByteArray());
            out.close();
            hssfworkbook.close();
            return excelStream;
        } catch (Exception ex) {
            ex.printStackTrace();
            StringWriter stringWriter = new StringWriter();
            ex.printStackTrace(new PrintWriter(stringWriter));
            throw new Exception("往Excel写数据出错:" + stringWriter.toString());
        }
    }

    /**
     * 把Excel文件数据读取成json
     *
     * @param inputStream
     * @param surffix
     * @return
     * @throws Exception
     */
    public static String GetExcelJson(InputStream inputStream, String surffix) throws Exception {
        //存Json串
        StringBuilder sb = new StringBuilder();
        sb.append("[");
        Workbook workbook = null;
        //2007版本
        if (surffix.indexOf(".xlsx") >= 0) {
            workbook = new XSSFWorkbook(inputStream);
        }
        //2003版本
        else if (surffix.indexOf(".xls") >= 0) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new Exception(surffix + "不是有效的Excel后缀!");
        }
        //得到表单的个数
        int numSheets = workbook.getNumberOfSheets();
        int curSheetNum = 0;
        //遍历找到当前表单表单
        for (int i = 0; i < numSheets; i++) {
            String curName = workbook.getSheetAt(i).getSheetName();
            Sheet sheet = workbook.getSheet(curName);
            if (sheet == null) {
                continue;
            }
            //最后一列的标号
            int rowCount = sheet.getLastRowNum();
            if (rowCount == 0) {
                continue;
            }
            Row firstRow = sheet.getRow(0);
            if (curSheetNum == 0) {
                sb.append("[");
            } else {
                sb.append(",[");
            }
            curSheetNum++;
            //一行最后一个cell的编号 即总的列数
            int cellCount = 0;
            if (firstRow != null) {
                cellCount = firstRow.getLastCellNum();
            }
            int curRowNum = 0;
            for (int j = 0; j <= rowCount; j++) {
                Row curRow = sheet.getRow(j);
                if (curRow == null) {
                    continue;
                }
                if (firstRow == null) {
                    firstRow = curRow;
                    cellCount = firstRow.getLastCellNum();
                }
                if (curRowNum == 0) {
                    sb.append("{");
                } else {
                    sb.append(",{");
                }
                curRowNum++;
                int curCellNum = 0;
                for (int k = curRow.getFirstCellNum(); k < cellCount; k++) {
                    Cell cell = curRow.getCell(k);
                    if (cell == null) {
                        continue;
                    }
                    Object cellVal = null;
                    if (cell.getCellType() == CellType.NUMERIC) {
                        cellVal = cell.getNumericCellValue();
                    } else if (cell.getCellType() == CellType.STRING) {
                        cellVal = cell.getStringCellValue();
                    } else if (cell.getCellType() == CellType.BOOLEAN) {
                        cellVal = cell.getBooleanCellValue();
                    } else {
                        cellVal = "";
                    }
                    if (curCellNum == 0) {
                        sb.append("\"" + NumbertoString(k + 1) + "\":\"" + DealForJsonString(cellVal.toString()) + "\"");
                    } else {
                        sb.append(",\"" + NumbertoString(k + 1) + "\":\"" + DealForJsonString(cellVal.toString()) + "\"");
                    }
                    curCellNum++;
                }
                sb.append("}");
            }
            sb.append("]");
        }
        sb.append("]");
        return sb.toString();
    }

    /**
     * 处理json冲突符号
     *
     * @param str
     * @return
     */
    private static String DealForJsonString(String str) {
        if (str.isEmpty()) {
            return str;
        } else {
            return str.replace("\0", " ")
                    .replace("\\a", "\\a")
                    .replace("\b", "\\b")
                    .replace("\f", "\\f")
                    .replace("\t", "\\t")
                    .replace("\\v", "\\v")
                    .replace("\\", "\\\\")
                    .replace("\"", "\\\"")
                    .replace("\r", " ")
                    .replace("\n", " ");
        }
    }

    /**
     * 转换Excel列名
     *
     * @param colIndex
     * @return
     */
    private static String NumbertoString(int colIndex) {
        StringBuilder strResult = new StringBuilder();
        int once = colIndex / 26;
        int twice = colIndex % 26;
        strResult.append((char) (twice - 1 + 'A'));
        if (once > 26) {
            strResult.append(NumbertoString(once));
        } else if (once > 0) {
            strResult.append((char) (once - 1 + 'A'));
        }
        return strResult.toString();
    }


    /**
     * 保存文件
     *
     * @param fullName 全面
     * @param workbook 工作簿
     * @throws Exception
     */
    private static void Save(String fullName, Workbook workbook) throws Exception {
        File fi = new File(fullName);
        Path path = Paths.get(fullName).getParent();
        //没有父路径就创建
        if (!Files.exists(path)) {
            // 如果路径不存在，则创建路径
            Files.createDirectories(path);
        }
        FileOutputStream file = null;
        try {
            file = new FileOutputStream(fullName);
            // 把相应的Excel工作蒲存盘
            workbook.write(file);
        } finally {
            if (file != null) {
                file.close();
            }
        }
    }

    /**
     * 把对象转换成字符串
     *
     * @param obj
     * @return
     */
    private static String ObjToString(Object obj) {
        if (obj != null) {
            return obj.toString().replace("$r$n", ((char) (10)) + "" + ((char) (13)));
        }
        return "";
    }
}