All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.rx.util.Helper Maven / Gradle / Ivy

Go to download 

package org.rx.util;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;

import static org.rx.core.Contract.toJsonString;

@Slf4j
public class Helper {
    @SneakyThrows
    public static Map> readExcel(InputStream in, boolean skipColumn) {
        return readExcel(in, skipColumn, false);
    }

    @SneakyThrows
    public static Map> readExcel(InputStream in, boolean skipColumn, boolean keepNullRow) {
        Map> data = new LinkedHashMap<>();
        try (HSSFWorkbook workbook = new HSSFWorkbook(in)) {
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                List rows = new ArrayList<>();
                HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                for (int rowIndex = skipColumn ? 1 : sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (row == null) {
                        if (keepNullRow) {
                            rows.add(null);
                        }
                        continue;
                    }
                    List cells = new ArrayList<>();
                    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                        HSSFCell cell = row.getCell(i);
                        if (cell == null) {
                            cells.add(null);
                            continue;
                        }
                        Object value;
                        switch (cell.getCellTypeEnum()) {
                            case NUMERIC:
                                value = cell.getNumericCellValue();
                                break;
                            case BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            default:
                                value = cell.getStringCellValue();
                                break;
                        }
                        cells.add(value);
                    }
                    if (cells.contains(null)) {
                        log.debug(String.format("current=%s offset=%s count=%s -> %s/%s", toJsonString(cells),
                                row.getFirstCellNum(), row.getLastCellNum(), rowIndex, sheetIndex));
                    }
                    rows.add(cells.toArray());
                }
                data.put(sheet.getSheetName(), rows);
            }
        }
        return data;
    }

    public static void writeExcel(OutputStream out, Map> data) {
        writeExcel(out, data, null);
    }

    @SneakyThrows
    public static void writeExcel(OutputStream out, Map> data, Function onRow) {
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            for (Map.Entry> entry : data.entrySet()) {
                HSSFSheet sheet = workbook.getSheet(entry.getKey());
                if (sheet == null) {
                    sheet = workbook.createSheet(entry.getKey());
                }
                List rows = entry.getValue();
                for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (row == null) {
                        row = sheet.createRow(rowIndex);
                    }
                    Object[] cells = rows.get(rowIndex);
                    for (int i = 0; i < cells.length; i++) {
                        HSSFCell cell = row.getCell(i);
                        if (cell == null) {
                            cell = row.createCell(i);
                        }
                        Object val = cells[i];
                        if (val == null) {
                            continue;
                        }
                        String value;
                        if (val instanceof Date) {
                            value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(val);
                        } else {
                            value = String.valueOf(val);
                        }
                        cell.setCellValue(value);
                    }
                    if (onRow != null) {
                        if (row.getRowStyle() == null) {
                            row.setRowStyle(workbook.createCellStyle());
                        }
                        onRow.apply(row);
                    }
                }
            }
            workbook.write(out);
        }
    }
}
 















© 2015 - 2024 Weber Informatics LLC | Privacy Policy