com.dituiba.excel.BaseExcelService.java Source code

Introduction

Here is the source code for com.dituiba.excel.BaseExcelService.java

Source

/*
 * Copyright 2015 www.hyberbin.com.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * Email:hyberbin@qq.com
 */
package com.dituiba.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
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.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Created with IntelliJ IDEA.
 * User: Hyberbin
 * Date: 13-12-3
 * Time: ?4:55
 */
public class BaseExcelService {
    protected Logger log = LoggerFactory.getLogger(getClass());
    /**Hash?*/
    public final static int HASH_ROW = 0;
    /***/
    public final static int TITLE_ROW = 1;
    /**??*/
    public final static int HIDDEN_FIELD_HEAD = TITLE_ROW + 1;
    /**??*/
    public final static int COLUMN_ROW = HIDDEN_FIELD_HEAD + 1;
    /**?*/
    public final static int START_ROW = COLUMN_ROW + 1;

    /**
     * ?
     * @param sheetRow
     * @param length
     * @return
     */
    public static boolean isEmpty(Row sheetRow, int length) {
        if (sheetRow != null) {
            for (int i = 0; i < length; i++) {
                Cell cell = sheetRow.getCell(i);
                if (!isEmpty(cell)) {
                    return false;
                }
            }
        }
        return true;
    }

    /***
     * ?hash?Excel
     * @param sheet
     * @param hashCode
     */
    public static void setHashVal(Sheet sheet, long hashCode) {
        Row sheetRow = sheet.getRow(HASH_ROW);
        Cell cell = sheetRow.createCell(0);
        cell.setCellValue(hashCode);
        sheetRow.setHeight(Short.valueOf("0"));
    }

    /**
     * ??hash?
     * @param sheet
     * @return
     */
    public static long getHashVal(Sheet sheet) {
        Row sheetRow = sheet.getRow(HASH_ROW);
        Cell cell = sheetRow.getCell(0);
        return ((Double) cell.getNumericCellValue()).longValue();
    }

    /**
     * ?
     * @param cell
     */
    public static void setErrorStyle(Cell cell) {
        if (cell != null) {
            CellStyle newstyle = cell.getSheet().getWorkbook().createCellStyle();
            CellStyle style = cell.getCellStyle();
            if (style != null) {
                newstyle.cloneStyleFrom(style);
            }
            newstyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            newstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            cell.setCellStyle(newstyle);
        }
    }

    /**
     * ??
     * @param cell
     * @return
     */
    public static boolean isEmpty(Cell cell) {
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (!ObjectHelper.isNullOrEmptyString(cell.getStringCellValue())) {
                    return false;
                }
            } else {
                return false;
            }
        }
        return true;
    }

    /**
     * sheet ?
     * @param sheet
     * @param row
     * @param length
     * @param data
     */
    public static void addTitle(Sheet sheet, int row, int length, String data) {
        if (data == null || data.equals("") || data.equals("null")) {
            return;
        }
        Row sheetRow = sheet.createRow(row);
        for (int i = 0; i < length; i++) {
            sheetRow.createCell(i);
        }
        CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 
        CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1);
        sheet.addMergedRegion(cellRangeAddress);
        Cell cell = sheetRow.getCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(data);
    }

    /**
     * ?
     * @param sheet
     * @param row
     * @param data
     * @return
     */
    public static Row addRow(Sheet sheet, int row, String[] data) {
        Row sheetRow = sheet.createRow(row);
        CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
        style.setWrapText(true);
        for (int i = 0; i < data.length; i++) {
            Cell cell = sheetRow.createCell(i);
            cell.setCellValue(data[i]);
            cell.setCellStyle(style);
        }
        return sheetRow;
    }

    /**
     * ?
     * @param sheet
     * @param row
     * @param length
     * @return
     */
    public static Row createRow(Sheet sheet, int row, int length) {
        Row sheetRow = sheet.createRow(row);
        for (int i = 0; i < length; i++) {
            sheetRow.createCell(i);
        }
        return sheetRow;
    }

    /**
     * ??
     * @param sheet
     * @param row
     * @param col
     * @return
     */
    public static Cell getCell(Sheet sheet, int row, int col) {
        return sheet.getRow(row).getCell(col);
    }

    /**
     * ??
     * @param sheetRow
     * @param col
     * @return
     */
    public static Cell getCell(Row sheetRow, int col) {
        Cell cell = sheetRow.getCell(col);
        if (cell == null) {
            cell = sheetRow.createCell(col);
        }
        return cell;
    }

    /**
     * ?
     * @param sheet
     * @param row
     * @return
     */
    public static Row getRow(Sheet sheet, int row) {
        return sheet.getRow(row);
    }

    /**
     * ??
     * @param cell
     * @return
     */
    public static String getString(Cell cell) {
        return cell.getStringCellValue();
    }

    /**
     * 
     * @return
     */
    public static Workbook createWorkbook() {
        return new HSSFWorkbook();
    }

    /**
     * 
     * @param workbook
     * @param name
     * @return
     */
    public static Sheet createSheet(Workbook workbook, String name) {
        return workbook.createSheet(name);
    }

    /**
     * ?
     * @param workbook
     * @param index
     * @return
     */
    public static Sheet getSheet(Workbook workbook, int index) {
        return workbook.getSheetAt(index);
    }

}