com.yyl.common.utils.excel.ExcelTools.java Source code

Java tutorial

Introduction

Here is the source code for com.yyl.common.utils.excel.ExcelTools.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.yyl.common.utils.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import com.yyl.common.utils.DateUtils;
import com.yyl.common.utils.json.JacksonUtil;
import com.yyl.common.utils.string.StringUtil;

/**
 *
 * @author yangyl
 * @date 2017-1-7
 */
public class ExcelTools extends FileTools {

    public static final String OFFICE_EXCEL_2003_SUFFIX = "xls";

    public static final String OFFICE_EXCEL_2010_SUFFIX = "xlsx";

    /*--------------------------Read Excel Start------------------------------------------*/
    /**
     * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
     * @param inputStream ?urlurlinput?
     * @param FileName ???????excel
     * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
     * @throws IOException
     */
    public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
            throws IOException {

        System.out.println("excel2json....");

        // map
        Map<String, String> excelMap = new LinkedHashMap<>();

        // Excel??Excel
        CellStyle cellStyle;
        // ?Excel?
        Workbook wb;
        // 2007??Workbook?CellStyle
        if (FileName.endsWith("xlsx")) {
            System.out.println("2007?  xlsx");
            wb = new XSSFWorkbook(inputStream);
            XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
            cellStyle = wb.createCellStyle();
            // Excel?
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
        } else {
            System.out.println("2007  xls");
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            wb = new HSSFWorkbook(fs);
            HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
            cellStyle = wb.createCellStyle();
            // Excel?
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
        }

        // sheet
        int sheetsCounts = wb.getNumberOfSheets();
        // ???sheet
        for (int i = 0; i < sheetsCounts; i++) {
            Sheet sheet = wb.getSheetAt(i);
            System.out.println("" + i + "sheet:" + sheet.toString());

            // sheetList
            List list = new LinkedList();

            // jsonkey
            String[] cellNames;
            // ?key
            Row fisrtRow = sheet.getRow(0);
            // sheet
            if (null == fisrtRow) {
                continue;
            }
            // 
            int curCellNum = fisrtRow.getLastCellNum();
            System.out.println("" + curCellNum);
            // ???
            cellNames = new String[curCellNum];
            // ????JSONkey
            for (int m = 0; m < curCellNum; m++) {
                Cell cell = fisrtRow.getCell(m);
                // ?
                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ?
                cellNames[m] = getCellValue(cell);
            }
            for (String s : cellNames) {
                System.out.print("" + i + " sheet " + s + ",");
            }
            System.out.println();

            // ???
            int rowNum = sheet.getLastRowNum();
            System.out.println(" " + rowNum + " ");
            for (int j = 1; j < rowNum; j++) {
                // ?Map
                LinkedHashMap rowMap = new LinkedHashMap();
                // ??
                Row row = sheet.getRow(j);
                int cellNum = row.getLastCellNum();
                // ???
                for (int k = 0; k < cellNum; k++) {
                    Cell cell = row.getCell(k);

                    cell.setCellStyle(cellStyle);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    // ???
                    rowMap.put(cellNames[k], getCellValue(cell));
                }
                // ??List
                list.add(rowMap);
            }
            // sheet??keyListjson?Value
            excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
        }

        System.out.println("excel2json?....");

        return excelMap;
    }

    /*--------------------------Read Excel Start------------------------------------------*/
    /**
     * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
     * @param inputStream ?urlurlinput?
     * @param FileName ???????excel
     * @param headers list,String-->Arrays.asList();
     * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
     * @throws IOException
     */
    public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
            throws IOException {

        System.out.println("excel2json....");

        // map
        Map<String, String> excelMap = new LinkedHashMap<>();

        // Excel??Excel
        CellStyle cellStyle;
        // ?Excel?
        Workbook wb;
        // 2007??Workbook?CellStyle
        if (fileName.endsWith("xlsx")) {
            System.out.println("2007?  xlsx");
            wb = new XSSFWorkbook(inputStream);
            XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
            cellStyle = wb.createCellStyle();
            // Excel?
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
        } else {
            System.out.println("2007  xls");
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            wb = new HSSFWorkbook(fs);
            HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
            cellStyle = wb.createCellStyle();
            // Excel?
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
        }

        // sheet
        int sheetsCounts = wb.getNumberOfSheets();
        // ???sheet
        for (int i = 0; i < sheetsCounts; i++) {
            Sheet sheet = wb.getSheetAt(i);
            System.out.println("" + i + "sheet:" + sheet.toString());

            // sheetList
            List list = new LinkedList();

            // ?key
            Row fisrtRow = sheet.getRow(0);
            // sheet
            if (null == fisrtRow) {
                continue;
            }
            // 
            int curCellNum = fisrtRow.getLastCellNum();
            System.out.println("" + curCellNum);

            // ???
            int rowNum = sheet.getLastRowNum();
            System.out.println(" " + rowNum + " ");
            for (int j = 1; j < rowNum; j++) {
                // ?Map
                LinkedHashMap rowMap = new LinkedHashMap();
                // ??
                Row row = sheet.getRow(j);
                int cellNum = row.getLastCellNum();
                // ???
                for (int k = 0; k < cellNum; k++) {
                    Cell cell = row.getCell(k);
                    // ???
                    rowMap.put(headers.get(k), getCellValue(cell));
                }
                // ??List
                list.add(rowMap);
            }
            // sheet??keyListjson?Value
            excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
        }

        System.out.println("excel2json?....");

        return excelMap;
    }

    /**
     * ?OFFICE Excel???
     * @param file 
     * @param originFileName ??
     * @param keyMaps Mapkey.<String,Integer>--<???>
     * @return List<Map<String, String>>
     * @throws FileException
     * @throws IOException 
     */
    public static List<Map<String, String>> readExcel(InputStream inputStream, String originFileName,
            Map<String, Integer> keyMaps) throws Exception {
        if (StringUtil.isBlank(originFileName)) {
            throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR);
        } else {
            String suffix = getSuffix(originFileName);
            if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) {
                return readXLS(inputStream, keyMaps);
            } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) {
                return readXLSX(inputStream, keyMaps);
            } else {
                throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR);
            }
        }
    }

    /** ?Excel??
     *  
     * @param file
     * @param originFileName ??
     * @return List<List<Object>>
     * @throws FileException
     * @throws IOException
     */
    public static List<List<Object>> readExcelWithHeader(InputStream inputStream, String originFileName)
            throws FileException, IOException {
        if (StringUtil.isBlank(originFileName)) {
            throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR);
        } else {
            String suffix = getSuffix(originFileName);
            if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) {
                return readXLSWithHeader(inputStream);
            } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) {
                return readXLSXWithHeader(inputStream);
            } else {
                throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR);
            }
        }
    }

    /**
     * ?OFFICE Excel???
     * @param file
     * @param originFileName Excel??
     * @return List<List<Object>>
     * @throws FileException
     * @throws IOException 
     */
    public static List<List<Object>> readExcel(InputStream inputStream, String originFileName)
            throws FileException, IOException {
        if (StringUtil.isBlank(originFileName)) {
            throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR);
        } else {
            String suffix = getSuffix(originFileName);
            if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) {
                return readXLS(inputStream);
            } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) {
                return readXLSX(inputStream);
            } else {
                throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR);
            }
        }
    }

    /*--------------------------Read Excel End------------------------------------------*/
    /*--------------------------Write Excel Start------------------------------------------*/
    public static void writeToXLS(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out)
            throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet(sheetName);
        HSSFRow row = sheet1.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell;

        for (int i = 0; i < heads.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(heads.get(i));
            cell.setCellStyle(style);
        }
        if (data != null && data.size() != 0) {
            for (int n = 0; n < data.size(); n++) {
                row = sheet1.createRow(n + 1);
                ExcelRow datarow = data.get(n);
                for (int m = 0; m < datarow.size(); m++) {
                    cell = row.createCell(m);
                    cell.setCellValue(datarow.get(m));
                    cell.setCellStyle(style);
                }
            }
        }
        wb.write(out);
    }

    /**
     * ?xlsxExcel
     * @param heads 
     * @param data ?
     * @param sheetName Excel?
     * @param out ?
     * @return ByteArrayOutputStream
     * @throws IOException
     */
    public static void writeToXLSX(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out)
            throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet1 = wb.createSheet(sheetName);
        XSSFRow row = sheet1.createRow(0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        XSSFCell cell;
        for (int i = 0; i < heads.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(heads.get(i));
            cell.setCellStyle(style);
        }

        if (data != null && data.size() != 0) {
            for (int n = 0; n < data.size(); n++) {
                row = sheet1.createRow(n + 1);
                ExcelRow datarow = data.get(n);
                for (int m = 0; m < datarow.size(); m++) {
                    cell = row.createCell(m);
                    cell.setCellValue(datarow.get(m));
                    cell.setCellStyle(style);
                }
            }
        }
        wb.write(out);
    }

    /*--------------------------Write Excel End------------------------------------------*/
    private static List<List<Object>> readXLSWithHeader(InputStream inputStream) throws IOException {
        //        InputStream is = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = wb.getSheetAt(0);

        List<List<Object>> o = new ArrayList();
        List<Object> temp = null;
        if (sheet == null) {
            return null;
        }

        for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) {
            HSSFRow row = sheet.getRow(row_index);
            if (row == null) {
                continue;
            }
            temp = new ArrayList();
            for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
                temp.add(getCellValue(row.getCell(col_index)));
            }
            o.add(temp);
        }

        return o;
    }

    public static ResponseEntity<byte[]> export(ByteArrayOutputStream bOutputStream, String fileName) {
        try {
            fileName = new String((fileName + DateUtils.formatDate(new Date(), "yyyy-MM-dd") + ".xls").getBytes(),
                    "iso-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        HttpHeaders headers = new HttpHeaders();
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

        return new ResponseEntity<byte[]>(bOutputStream.toByteArray(), headers, HttpStatus.CREATED);
    }

    private static final String trim(String str) {
        return str == null ? "" : str.trim();
    }

    private static List<List<Object>> readXLSXWithHeader(InputStream inputStream) throws IOException {
        //        InputStream is = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = wb.getSheetAt(0);

        List<List<Object>> o = new ArrayList();
        List<Object> temp = null;
        if (sheet == null) {
            return null;
        }

        for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) {
            XSSFRow row = sheet.getRow(row_index);
            if (row == null) {
                continue;
            }
            temp = new ArrayList();
            for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
                temp.add(getCellValue(row.getCell(col_index)));
            }
            o.add(temp);
        }

        return o;
    }

    private static List<List<Object>> readXLS(InputStream inputStream) throws IOException {
        //        InputStream is = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = wb.getSheetAt(0);

        List<List<Object>> o = new ArrayList();
        List<Object> temp = null;
        if (sheet == null) {
            return null;
        }

        for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) {
            HSSFRow row = sheet.getRow(row_index);
            if (row == null) {
                continue;
            }
            temp = new ArrayList();
            for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
                temp.add(getCellValue(row.getCell(col_index)));
            }
            o.add(temp);
        }

        return o;
    }

    private static List<List<Object>> readXLSX(InputStream inputStream) throws IOException {
        //        InputStream is = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = wb.getSheetAt(0);

        List<List<Object>> o = new ArrayList();
        List<Object> temp = null;
        if (sheet == null) {
            return null;
        }

        for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) {
            XSSFRow row = sheet.getRow(row_index);
            if (row == null) {
                continue;
            }
            temp = new ArrayList();
            for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
                temp.add(getCellValue(row.getCell(col_index)));
            }
            o.add(temp);
        }

        return o;
    }

    private static List<Map<String, String>> readXLS(InputStream inputStream, Map<String, Integer> keyMaps)
            throws IOException {
        //        InputStream is = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(inputStream);

        List<Map<String, String>> list = new ArrayList();
        Map<String, String> temp = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet sheet = wb.getSheetAt(sheetIndex);
            if (sheet == null) {
                continue;
            }
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex);
                temp = new HashMap();
                if (row != null) {
                    for (Entry<String, Integer> entry : keyMaps.entrySet()) {
                        int index = entry.getValue();
                        HSSFCell cell = row.getCell(index);
                        temp.put(entry.getKey(), getCellValue(cell));
                    }
                }
                list.add(temp);
            }
        }
        return list;
    }

    private static List<Map<String, String>> readXLSX(InputStream inputStream, Map<String, Integer> keyMaps)
            throws IOException {
        //        InputStream is = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);

        List<Map<String, String>> list = new ArrayList();
        Map<String, String> temp = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);
            if (sheet == null) {
                continue;
            }
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                XSSFRow row = sheet.getRow(rowIndex);
                temp = new HashMap();
                if (row != null) {
                    for (Entry<String, Integer> entry : keyMaps.entrySet()) {
                        int index = entry.getValue();
                        XSSFCell cell = row.getCell(index);
                        temp.put(entry.getKey(), getCellValue(cell));
                    }
                }
                list.add(temp);
            }
        }
        return list;
    }

    /**
     *    ?cell
     * @param cell
     * @return
     */
    private static String getCellValue(HSSFCell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC: {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(cell.getNumericCellValue());
        }
        default:
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * ?excel
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        if (cell == null || cell.equals("") || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            System.out.println(cellValue);
            return cellValue;
        }
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            cellValue = cell.getRichStringCellValue().getString().trim();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                cellValue = cell.getDateCellValue().toString();
            } else {
                cellValue = df.format(cell.getNumericCellValue()).toString();
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
            break;
        default:
            cellValue = "";
        }
        return cellValue;
    }

    //    /**
    //    * excel? key??
    //    * 
    //    * @param file
    //    * @return
    //    * @throws BiffException
    //    * @throws IOException
    //    */
    //   public static final List<Map<String, String>> readSheet(File file) {
    //      Workbook workbook = null;
    //      List<Map<String, String>> result = null;
    //      try {
    //         workbook = Workbook.getWorkbook(file);
    //         jxl.Sheet sheet = workbook.getSheet(0);
    //         int rowSize = sheet.getRows();
    //         Cell[] headCell = sheet.getRow(0);
    //         result = new ArrayList<Map<String, String>>(rowSize - 1);
    //         Cell[] cells;
    //         for (int i = 1; i < rowSize; i++) {
    //            cells = sheet.getRow(i);
    //            Map<String, String> data = new HashMap<String, String>();
    //            for (int j = 0; j < headCell.length; j++) {
    //               if (cells.length > j) {
    //                  if (cells[j].getType() == CellType.DATE) { // TODO:?
    //                     DateCell dc = (DateCell) cells[j];
    //                     Date date = dc.getDate();
    //                     TimeZone zone = TimeZone.getTimeZone("GMT");
    //                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    //                     sdf.setTimeZone(zone);
    //                     String dateStr = sdf.format(date);
    //                     data.put(trim(headCell[j].getContents()), dateStr);
    //                  } else if (cells[j].getType() == CellType.NUMBER) { // ?
    //                     if (cells[j].getContents().indexOf(".") > 0) {
    //                        NumberCell nc = (NumberCell) cells[j];
    //                        double value = nc.getValue();
    //                        data.put(trim(headCell[j].getContents()), trim(String.valueOf(value)));
    //                     } else {
    //                        data.put(trim(headCell[j].getContents()), trim(cells[j].getContents()));
    //                     }
    //                  } else {
    //                     data.put(trim(headCell[j].getContents()), trim(cells[j].getContents()));
    //                  }
    //
    //               }
    //            }
    //            result.add(data);
    //         }
    //      } catch (BiffException e) {
    //            e.printStackTrace();
    //        } catch (IOException e) {
    //            e.printStackTrace();
    //        } catch (Exception e) {
    //            e.printStackTrace();
    //        }  finally {
    //         workbook.close();
    //      }
    //
    //      return result;
    //   }

    public static class ExcelException extends FileTools.FileException {

        private int row_index = -1;

        private int col_index = -1;

        public ExcelException(int row, int col, String ex, int code) {
            super(ex, code);
            this.row_index = row;
            this.col_index = col;
        }

        public ExcelException(int row, int col, Throwable ex, int code) {
            super(ex, code);
            this.row_index = row;
            this.col_index = col;
        }

        public static final int CELL_DATA_INVALID_ERROR = 75200;

        public static final int CELL_DATA_FORMAT_ERROR = 75201;

        public static final int CELL_DATA_MISMATCH_ERROR = 75202;

    }

    public static ExcelRow excelHeaders(String... headers) {
        ExcelRow row = new ExcelRow();
        for (int i = 0; i < headers.length; ++i)
            row.add(headers[i]);
        return row;
    }
}