jxl 依据xml映射配置 导入导出excel(2)

Excel工具类

public class ExcelUtil {
    /**
     * 
     * 方法说明 按照xml配置模版导出数据到excel
     * 
     * @param dataClass
     *            数据实体类类型
     * @param dataList
     *            数据集合
     * @param importKey
     *            xml配置路径枚举
     * @param response
     *            web响应对象
     */
    public static void exportExcel(Class<Object> dataClass, List<Object> dataList, ExcelToTableConfigEnum importKey, HttpServletResponse response)
            throws WriteException, NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, IOException,
            DocumentException {
        ExcelToTableConfig excelConfig = getTableConfig(importKey);
        OutputStream out = null;
        response.reset(); // 清空输出流
        response.setHeader("Content-disposition", "attachment; filename=" + excelConfig.getChineseName() + ".xls"); // 设定输出文件头
        response.setContentType("application/msexcel"); // 定义输出类型
        out = response.getOutputStream();
        // 创建工作簿
        WritableWorkbook workbook = null;
        workbook = Workbook.createWorkbook(out);
        int sheetRows = excelConfig.getMaxSheetRows();
        int sheetNumber = 1;
        if (dataList.size() % sheetRows > 0) {
            sheetNumber = dataList.size() / sheetRows + 1;
        } else {
            sheetNumber = dataList.size() / sheetRows;
        }
        for (int sn = 0; sn < sheetNumber && dataList.size() > 0; sn++) {
            // 创建工作页
            WritableSheet sheet = workbook.createSheet("sheet" + (sn + 1), sn);
            // 生成表头
            createExcelTitle(excelConfig, sheet);
            // 写入表数据
            writeExcelData(sn, sheet, dataClass, dataList, excelConfig);
        }
        workbook.write(); // 写入文件
        workbook.close();
        out.close();
    }
    /**
     * 
     * 方法说明 读取xml配置
     * 
     * @param excelToTableConfig
     * @return
     */
    public static ExcelToTableConfig getTableConfig(ExcelToTableConfigEnum excelToTableConfig) throws DocumentException, IOException {
        // 将解析结果存入HashMap中
        Map<String, Object> map = new HashMap<String, Object>();
        InputStream in = null;
        String path = Class.class.getClassLoader().getResource(excelToTableConfig.getConfigUrl()).getPath();
        in = new FileInputStream(path);
        // 读取输入流
        SAXReader reader = new SAXReader();
        Document document = null;
        document = reader.read(in);
        // 得到xml根元素
        Element table = document.getRootElement();
        ExcelToTableConfig config = new ExcelToTableConfig();
        config.setTableName(table.attributeValue("tableName"));
        config.setStartRow(Integer.parseInt(table.attributeValue("startRow")));
        config.setChineseName(table.attributeValue("chineseName"));
        // 得到所有子节点
        List<Element> colList = table.elements();
        // 便利所有子节点
        List<Column> columnList = new ArrayList<Column>();
        for (Element col : colList) {
            Column column = new Column();
            column.setIndex(Integer.parseInt(col.attributeValue("index")));
            column.setColName(col.attributeValue("colName"));
            column.setColType(col.attributeValue("colType"));
            column.setIsHidden(col.attributeValue("isHidden"));
            column.setIsWhere(col.attributeValue("isWhere"));
            column.setChineseName(col.attributeValue("chineseName"));
            column.setLength(Integer.parseInt(col.attributeValue("length")));
            columnList.add(column);
        }
        config.setColumnList(columnList);
        // 释放资源
        in.close();
        in = null;
        return config;
    }

    /**
     * 
     * 方法说明 导出数据到excel
     * 
     * @param sn
     *            第几个sheet
     * @param sheet
     *            当前sheet对象
     * @param dataClass
     *            数据实体类类型
     * @param dataList
     *            数据集合
     * @param excelConfig
     *            导出配置
     */
    private static void writeExcelData(int sn, WritableSheet sheet, Class<Object> dataClass, List<Object> dataList, ExcelToTableConfig excelConfig)
            throws WriteException, NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
        int maxSheetRows = excelConfig.getMaxSheetRows();
        int excelDataStartRow = excelConfig.getStartRow();
        for (int currentRow = excelDataStartRow; currentRow <= maxSheetRows && sn * (maxSheetRows - excelDataStartRow) + currentRow < dataList.size(); currentRow++) {// 行
            Object dataObj = dataList.get(sn * (maxSheetRows - excelDataStartRow) + currentRow - 1);
            // 写入行数据
            writeExcelRowData(currentRow, excelConfig, dataClass, dataObj, sheet);
        }
    }

    /**
     * 
     * 方法说明 生成excel行数据
     * 
     * @param currentRow
     *            当前行号
     * @param excelConfig
     *            导出配置
     * @param dataClass
     *            数据实体类类型
     * @param dataObj
     *            数据实体实例
     * @param sheet
     *            当前sheet
     */
    private static void writeExcelRowData(int currentRow, ExcelToTableConfig excelConfig, Class<Object> dataClass, Object dataObj, WritableSheet sheet)
            throws WriteException, NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                Colour.BLACK);
        List<Column> colList = excelConfig.getColumnList();
        for (int i = 0; i < colList.size(); i++) {
            Column col = colList.get(i);
            String fieldName = col.getFieldName();
            Field field = dataClass.getDeclaredField(fieldName);
            field.setAccessible(true);
            WritableCell label = null;
            if (YesOrNoEnum.Yes.name().equals(col.getIsHidden())) {
                sheet.setColumnView(i, 0);
            }
            Object value = field.get(dataObj);
            if (long.class.getName().equals(col.getColType())) {
                // 整型数字样式
                WritableCellFormat format = new WritableCellFormat(font, NumberFormats.INTEGER);
                format.setAlignment(Alignment.CENTRE);
                format.setVerticalAlignment(VerticalAlignment.CENTRE);
                format.setBorder(Border.ALL, BorderLineStyle.THIN);
                if (YesOrNoEnum.Yes.name().equals(col.getReadOnly())) {
                    format.setLocked(true);
                }
                label = new jxl.write.Number(i, currentRow, (long) value, format);
            } else if (String.class.getSimpleName().equals(col.getColType())) {
                WritableCellFormat format = new WritableCellFormat(font);
                format.setAlignment(Alignment.CENTRE);
                format.setVerticalAlignment(VerticalAlignment.CENTRE);
                format.setBorder(Border.ALL, BorderLineStyle.THIN);
                if (YesOrNoEnum.Yes.name().equals(col.getReadOnly())) {
                    format.setLocked(true);
                }
                label = new Label(i, currentRow, (String) value, format);
            }
            field.setAccessible(false);
            sheet.addCell(label);
        }
    }

    /**
     * 
     * 方法说明 导出excel表头
     * 
     * @param excelConfig 导出配置
     * @param sheet 当前sheet
     */
    public static void createExcelTitle(ExcelToTableConfig excelConfig, WritableSheet sheet) throws WriteException {
        // 表头文本样式
        // 字符串样式
        WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat format = new WritableCellFormat(font);
        format.setAlignment(Alignment.CENTRE);
        format.setVerticalAlignment(VerticalAlignment.CENTRE);
        format.setBackground(Colour.GREEN);
        format.setBorder(Border.ALL, BorderLineStyle.THIN);
        List<Column> colList = excelConfig.getColumnList();
        for (int i = 0; i < colList.size(); i++) {
            Column col = colList.get(i);
            // 设置 列宽
            sheet.setColumnView(i, col.getLength());// 第一列
            // 写入表头
            Label label = new Label(i, 0, col.getChineseName(), format);
            sheet.addCell(label);
        }
    }
    /**
     * 
     * 方法说明 生成一条更新语句
     * 
     * @param cols excel导入字段
     * @param tableName 表名
     * @param columnList 表字段配置
     * @return
     */
    private static String getOneUpateSql(Cell[] cols, String tableName, List<Column> columnList) {
        StringBuffer updateStatement = new StringBuffer("update " + tableName + " set ");
        StringBuffer whereStatement = new StringBuffer(" where ");
        for (int index = 0; index < cols.length; index++) {
            Cell cell = cols[index];
            String colValue = cell.getContents();
            Column column = null;
            for (Column col : columnList) {
                if (index == col.getIndex()) {
                    column = col;
                }
            }
            if (YesOrNoEnum.Yes.name().equals(column.getIsWhere())) {
                if (!" where ".equals(whereStatement.toString())) {
                    whereStatement.append(" and ");
                }
                getKeyValue(colValue, column, whereStatement);
            } else {
                if (!("update " + tableName + " set ").equals(updateStatement.toString())) {
                    updateStatement.append(",");
                }
                getKeyValue(colValue, column, updateStatement);
            }
        }
        return updateStatement.append(whereStatement.toString()).toString();
    }
    /**
     * 
     * 方法说明 生成 columnName=colValue 键值对
     * 
     * @param colValue 值
     * @param column 列
     * @param sql
     */
    private static void getKeyValue(String colValue, Column column, StringBuffer sql) {
        String colName = column.getColName();
        if (long.class.getName().equals(column.getColType())) {
            sql.append(colName + " = " + colValue);
        } else if (String.class.getName().equals(column.getColType())) {
            sql.append(colName + " = '" + colValue + "' ");
        }
    }
    /**
     * 
     * 方法说明 导入excel生成更新语句集
     * 
     * @param uploadfile excel文件
     * @param importKey excel对应xml配置
     * @return
     */
    public static List<String> getImportSql(MultipartFile uploadfile, ExcelToTableConfigEnum importKey) throws BiffException, IOException, DocumentException {
        Workbook book = null;
        InputStream in = null;
        List<String> sqlList = new ArrayList<String>();
        book = Workbook.getWorkbook(uploadfile.getInputStream());
        // 获得第一个工作表对象
        Sheet sheet = book.getSheet(0);
        int rows = sheet.getRows();
        ExcelToTableConfig tableConfig = getTableConfig(importKey);
        // 遍历每行
        String tableName = tableConfig.getTableName();
        int startRowNumber = tableConfig.getStartRow();
        List<Column> columnList = tableConfig.getColumnList();
        for (int i = startRowNumber; i < rows; i++) {
            Cell[] row = sheet.getRow(i);
            // 遍历每列
            String sql = getOneUpateSql(row, tableName, columnList);
            sqlList.add(sql);
        }
        if (book != null) {
            book.close();
        }
        return sqlList;
    }
}

转载于:https://my.oschina.net/u/1379199/blog/541989