Java Excel 导出

package com.touchbox.service;

import com.touchbox.domain.util.JSR310DateConverters;
import com.touchbox.service.util.ExportConvertCallback;
import com.touchbox.web.rest.util.CommonUtil;
import com.touchbox.web.rest.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.stream.Stream;

/**
 * Created by tengjuye on 16/1/13.
 * 用于批量导入导出数据库和Excel文件
 */
@Slf4j
@Service
public class ExportService {

    @Deprecated
    public File exportToFile(Stream<?> stream,char delimiter, String filePath, List<String> requireAtts) {
        File f;
        try {
            OutputStreamWriter write = new OutputStreamWriter(new FileOutputStream(filePath), "UTF-8");
            BufferedWriter writer = new BufferedWriter(write);
            CSVFormat csvFormat = CSVFormat.newFormat(delimiter).withRecordSeparator('\n');
            CSVPrinter csvFilePrinter = new CSVPrinter(writer, csvFormat);
            csvFilePrinter.printRecord(requireAtts);
            stream.forEach(object -> {
                List<String> records = new ArrayList<String>(requireAtts.size());
                for (String attr : requireAtts) {
                    try {
                        records.add((PropertyUtils.getProperty(object, attr)).toString());
                    } catch (Exception e) {
                        records.add("");
                    }
                }

                try {
                    csvFilePrinter.printRecord(records);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });
            write.flush();
            writer.close();
            f = new File(filePath);
        } catch (IOException e) {
            log.info("Exception occurred " + e.getMessage(), e);
            throw new RuntimeException("Exception occurred while exporting results", e);
        }
        return f;
    }


    public Iterator<CSVRecord> readCSVFileTo(File file,char delimiter, String[] headers) throws IOException {
        //check BOM
        FileReader fileReader = new FileReader(file);

        if (StringUtil.isFileWithBOM(file)){
            fileReader.skip(1);//remove bom
        }

        CSVFormat csvFileFormat = CSVFormat.DEFAULT.withSkipHeaderRecord(false).withHeader(headers);
        CSVParser csvFileParser = new CSVParser(fileReader, csvFileFormat);
        return csvFileParser.iterator();
    }

    public <T> List readListFromExcel(File file) {
        return readListFromExcel(file, null);
    }

    public <T> List readListFromExcel(File file, ExportConvertCallback<T> callback) {
        return readListFromExcel(file, 0, 0, callback);
    }

    public <T> List readListFromExcel(File file, int rowNum, ExportConvertCallback<T> callback) {
        return readListFromExcel(file, 0, rowNum, callback);
    }

    /**
     * 从excel文件中读取对象列表
     * @param file 文件
     * @param sheetNum 第几张表
     * @param rowNum 共有多少行
     * @param callback 对象转换回调方法
     * @return
     */
    public <T> List readListFromExcel(File file, int sheetNum, int rowNum, ExportConvertCallback<T> callback) {
        List<T> objList = new ArrayList<>();
        if (file == null) {
            throw new RuntimeException("请指定要读取的文件");
        }
        if (!file.exists()) {
            throw new RuntimeException("文件不存在");
        }

        String fileName = file.getName();
        String fileType = fileName.split("\\.")[1];
        try {
            InputStream is = new FileInputStream(file);
            Workbook workbook = null;
            if (fileType.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(is);
            } else if (fileType.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                throw new RuntimeException("不支持该文件格式，请用excel文件！");
            }
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 第一行不读取 2016.12.7
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                List<String> list = optRow(sheet, i, rowNum);
                if (list == null) {
                    continue;
                }
                T t = callback.convert(list);
                if (t != null) {
                    objList.add(t);
                }
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }

        return objList;
    }

    private List<String> optRow(Sheet sheet, int row) {
        return optRow(sheet, row, 0);
    }

    /**
     * 读取指定行的数据
     * @param sheet 表
     * @param row 行
     * @param readColumnNum 要读取的列数
     * @return
     */
    private List<String> optRow(Sheet sheet, int row, int readColumnNum) {
        Row rowLine = sheet.getRow(row);
        if (rowLine == null) {
            log.info("---------------------------------> blank row number is : " + row);
            return null;
        }
        // 列数
        int filledColumns = rowLine.getLastCellNum();
        if (filledColumns == 0) {
            return null;
        }

        if (readColumnNum > 0) {
            filledColumns = readColumnNum;
        }

        List<String> list = new ArrayList<>();
        String cellValue = "";
        boolean allBlank = true;
        for(int i = 0; i < filledColumns; i++) {
            Cell cell = rowLine.getCell(i);
            if (null != cell) {
                // 判断当前cell的type
                try {
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getStringCellValue().replaceAll("\'", "\"");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            // 是否为日期

                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                cellValue = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue();
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        default:
                            cellValue = cell.getStringCellValue();
                    }
                } catch (Exception e) {
                    log.error(cell + ": " + e.getMessage(), e);
                }
                list.add(cellValue);
                allBlank = false;
            } else {
                list.add("");
            }
        }
        if (allBlank) {
            return null;
        }
        return list;
    }

    public void exportObjectToExcel(String title,String[] requireAtts, String[]headers, Stream<?> stream, OutputStream outputStream) {
        exportObjectToExcel(title, requireAtts, headers, null, stream, outputStream);
    }

    public void exportObjectToExcel(String title,String[] requireAtts, String[]headers, short[] columnWidths, Stream<?> stream, OutputStream outputStream) {
        headers = title==null?requireAtts:headers;
        Stream<List<String>> stringStream = stream.map(object -> {
            List<String> records = new ArrayList<>(requireAtts.length);
            for (String attr : requireAtts) {
                try {
                    records.add((PropertyUtils.getProperty(object, attr)).toString());
                } catch (Exception e) {
                    records.add("");
                }
            }
            return records;
        });

        exportExcel(title, headers, columnWidths, stringStream, outputStream);
    }

    public void exportExcel(String title, String[] headers, Stream<List<String>> dataset, OutputStream out) {
        exportExcel(title, headers, null, dataset, out);
    }

    public void exportExcel(String title, String[] headers, short[] columnWidths, Stream<List<String>> dataset, OutputStream out) {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);


        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for(int i = 0; i < headers.length; i++){
            HSSFCell cell = row.createCell(i);
            if (columnWidths != null && columnWidths.length > 0) {
                sheet.setColumnWidth(i, columnWidths[i]);
            }
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);//把数据放到单元格中
        }

        HSSFCellStyle dateStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd hh:mm:ss"));
        //遍历集合数据,产生数据行
        Iterator<List<String>> it = dataset.iterator();
        int index = 0;
        while(it.hasNext()){
            index++;
            row = sheet.createRow(index);
            List<String> t = it.next();
            //利用反射，根据javabean属性的先后顺序，动态的调用getXxx()方法得到属性值
            for(int i = 0; i < t.size(); i++){
                HSSFCell cell = row.createCell(i);
                String v = t.get(i);
                boolean isDate = false;
                Date d = null;
                if (v != null && v.length() == 19 && v.indexOf("-") == 4 && v.indexOf(":") == 13 && v.indexOf(" ") == 10) {
                    try {
                        LocalDateTime localDateTime = LocalDateTime.parse(v, CommonUtil.defaultDateTimeFormatter);
                        d = JSR310DateConverters.LocalDateTimeToDateConverter.INSTANCE.convert(localDateTime);
                        isDate = true;
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                }
                if (isDate) {
                    cell.setCellStyle(dateStyle);
                    cell.setCellValue(d);
                } else {
                    cell.setCellValue(v);
                }
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}