Excel工具类 支持 Excel 导入 导出

package com.ebase.utils.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

import com.ebase.utils.DateUtil;
import com.ebase.utils.ReflectUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Excel工具类
 *
 */
public class ExportExcelUtils {

   /** 列默认宽度 */
   private static final int DEFAUL_COLUMN_WIDTH = 4000;

   /**
    * 1.创建 workbook
0-  */
   private Workbook createWorkBook() {
      return new HSSFWorkbook();
   }

   /**
    * 1.创建 workbook
    0-     */
   private Workbook createWorkBook2() {
      return new XSSFWorkbook();
   }

   /**
    * 2.创建 sheet
    * 
    */
   private Sheet createSheet(Workbook workbook, String sheetName) {
      return workbook.createSheet(sheetName);
   }

   /**
    * 3.写入表头信息
    *
    *            {@link Workbook}
    *            {@link Sheet}
    * @param headers
    *            列标题，数组形式
    *            <p>
    *            如{"列标题1@beanFieldName1@columnWidth",
    *            "列标题2@beanFieldName2@columnWidth",
    *            "列标题3@beanFieldName3@columnWidth"}
    *            </p>
    *            <p>
    *            其中参数@columnWidth可选，columnWidth为整型数值
    *            </p>
    * @param title
    *            标题
    */
   private void writeHeader(Workbook workbook, Sheet sheet, String[] headers, String title) {

      // 头信息处理
      String[] newHeaders = headersHandler(headers);

      // 初始化标题和表头单元格样式
      CellStyle titleCellStyle = createTitleCellStyle(workbook);
      // 标题栏
      Row titleRow = sheet.createRow(0);
      titleRow.setHeight((short) 500);
      Cell titleCell = titleRow.createCell(0);
      
      // 设置标题文本
      titleCell.setCellValue(new HSSFRichTextString(title));
      // 设置单元格样式
      titleCell.setCellStyle(titleCellStyle);

      // 处理单元格合并，四个参数分别是：起始行，终止行，起始列，终止列
      sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) (newHeaders.length - 1)));

      // 设置合并后的单元格的样式
      titleRow.createCell(newHeaders.length - 1).setCellStyle(titleCellStyle);

      // 表头
      Row headRow = sheet.createRow(1);
      headRow.setHeight((short) 500);
      Cell headCell = null;
      String[] headInfo = null;
      // 处理excel表头
      for (int i = 0, len = newHeaders.length; i < len; i++) {
         headInfo = newHeaders[i].split("@");

         CellStyle titleCellStyleLine = lineColour(workbook, headInfo);

         headCell = headRow.createCell(i);
         headCell.setCellValue(headInfo[0]);
         headCell.setCellStyle(titleCellStyleLine);
         // 设置列宽度
         setColumnWidth(i, headInfo, sheet);
      }
   }


   /**
    * 3.写入表头信息
    *
    *            {@link Workbook}
    *            {@link Sheet}
    * @param headers
    *            列标题，数组形式
    *            <p>
    *            如{"列标题1@beanFieldName1@columnWidth",
    *            "列标题2@beanFieldName2@columnWidth",
    *            "列标题3@beanFieldName3@columnWidth"}
    *            </p>
    *            <p>
    *            其中参数@columnWidth可选，columnWidth为整型数值
    *            </p>
    * @param title
    *            标题
    */
   private void writeHeader2(Workbook workbook, Sheet sheet, String[] headers, String title) {

      // 头信息处理
      String[] newHeaders = headersHandler(headers);

      // 初始化标题和表头单元格样式
      CellStyle titleCellStyle = createTitleCellStyle(workbook);
      // 标题栏
      Row titleRow = sheet.createRow(0);
      titleRow.setHeight((short) 500);
      Cell titleCell = titleRow.createCell(0);

      // 设置标题文本
      titleCell.setCellValue(new XSSFRichTextString(title));
      // 设置单元格样式
      titleCell.setCellStyle(titleCellStyle);

      // 处理单元格合并，四个参数分别是：起始行，终止行，起始列，终止列
      sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) (newHeaders.length - 1)));

      // 设置合并后的单元格的样式
      titleRow.createCell(newHeaders.length - 1).setCellStyle(titleCellStyle);

      // 表头
      Row headRow = sheet.createRow(1);
      headRow.setHeight((short) 500);
      Cell headCell = null;
      String[] headInfo = null;
      // 处理excel表头
      for (int i = 0, len = newHeaders.length; i < len; i++) {
         headInfo = newHeaders[i].split("@");

         CellStyle titleCellStyleLine = lineColour(workbook, headInfo);

         headCell = headRow.createCell(i);
         headCell.setCellValue(headInfo[0]);
         headCell.setCellStyle(titleCellStyleLine);
         // 设置列宽度
         setColumnWidth(i, headInfo, sheet);
      }
   }

   /**
    * 3.写入表头信息
    *
    *            {@link Workbook}
    *            {@link Sheet}
    * @param headers
    *            列标题，数组形式
    *            <p>
    *            如{"列标题1@beanFieldName1@columnWidth",
    *            "列标题2@beanFieldName2@columnWidth",
    *            "列标题3@beanFieldName3@columnWidth"}
    *            </p>
    *            <p>
    *            其中参数@columnWidth可选，columnWidth为整型数值
    *            </p>
    * @param title
    *            标题
    */
   private void writeHeader3(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] headers, String title) {

      // 头信息处理
      String[] newHeaders = headersHandler(headers);

      // 初始化标题和表头单元格样式
      CellStyle titleCellStyle = createTitleCellStyle(workbook);
      // 标题栏
      SXSSFRow titleRow =sheet.createRow(0);
      titleRow.setHeight((short) 500);
      SXSSFCell titleCell = titleRow.createCell(0);

      // 设置标题文本
      titleCell.setCellValue(new XSSFRichTextString(title));
      // 设置单元格样式
      titleCell.setCellStyle(titleCellStyle);

      // 处理单元格合并，四个参数分别是：起始行，终止行，起始列，终止列
      sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) (newHeaders.length - 1)));

      // 设置合并后的单元格的样式
      titleRow.createCell(newHeaders.length - 1).setCellStyle(titleCellStyle);

      // 表头
      SXSSFRow headRow = sheet.createRow(1);
      headRow.setHeight((short) 500);
      SXSSFCell headCell = null;
      String[] headInfo = null;
      // 处理excel表头
      for (int i = 0, len = newHeaders.length; i < len; i++) {
         headInfo = newHeaders[i].split("@");

         CellStyle titleCellStyleLine = lineColour(workbook, headInfo);

         headCell = headRow.createCell(i);
         headCell.setCellValue(headInfo[0]);
         headCell.setCellStyle(titleCellStyleLine);
         // 设置列宽度
         setColumnWidth(i, headInfo, sheet);
      }
   }

   /**
    * 把字变红
    * @param workbook
    * @param headInfo
    * @return
    */
   private CellStyle lineColour(Workbook workbook, String[] headInfo) {

      // 生成一个字体
      Font font = workbook.createFont();
      //    font.setColor(HSSFColor.BLACK.index);


      CellStyle titleCellStyleLine = createTitleCellStyle(workbook);

      if(headInfo.length > 3){
         String colour = headInfo[3];
         if(StringUtils.isNotEmpty(colour)){  //设置了颜色

            Byte aByte = Byte.valueOf(colour);
            if(LineFormatColourEnum.RED.getCode().equals(aByte)){
               font.setColor(IndexedColors.RED.getIndex());

//