import java.awt.Color; 
import java.io.ByteArrayInputStream; 
import java.io.ByteArrayOutputStream; 
import java.io.File; 
import java.io.FileInputStream; 
import java.io.InputStream; 
import java.lang.reflect.Field; 
import java.text.DateFormat; 
import java.text.DecimalFormat; 
import java.text.SimpleDateFormat; 
import java.util.*; 
 
import javax.swing.text.AttributeSet; 
import javax.swing.text.Element; 
import javax.swing.text.html.CSS; 
import javax.swing.text.html.HTMLDocument; 
import javax.swing.text.html.HTMLEditorKit; 
 
import cn.vrview.dev.common.exception.BusinessException; 
import org.apache.commons.lang3.StringUtils; 
import org.apache.logging.log4j.LogManager; 
import org.apache.logging.log4j.Logger; 
import org.apache.poi.hssf.usermodel.*; 
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.usermodel.XSSFColor; 
import org.apache.poi.xssf.usermodel.XSSFFont; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
import org.springframework.web.util.HtmlUtils; 
 
import cn.vrview.dev.common.util.StringUtil; 
 
 
public class ExcelTools { 
 
 /** log */ 
 private static Logger log = LogManager.getLogger(); 
 
 /** 
 * 導(dǎo)出excel 
 * <p> 
 * 使用方法：<br> 
 * <code> List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();<br> 
 * is = ExcelTools.exportXLS(dataList,new String[] {"createTime:日期","name:名稱", "sex:性別", "remark:備注"}); 
 * </code> 
 * 
 * @param collect 
 *  待導(dǎo)出的數(shù)據(jù)集合 
 * @param header 
 *  要導(dǎo)出的列 
 * @return InputStream 返回文件流 
 */ 
 public static InputStream exportXLS(Collection<Map<String, Object>> collect, String[] header) { 
 ByteArrayOutputStream out = new ByteArrayOutputStream(); 
 HSSFWorkbook book = new HSSFWorkbook(); 
 try { 
  // 添加一個(gè)sheet 
  HSSFSheet sheet = book.createSheet("Sheet1"); 
  // 定義要導(dǎo)出的列名集合 
  Set<String> columns = new HashSet<String>(); 
 
  // 設(shè)置單元格背景色 
  HSSFCellStyle cellStyle = book.createCellStyle(); 
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
  cellStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex()); 
 
  // 生成表頭 
  HSSFRow row = sheet.createRow(0); 
  HSSFCell cell = row.createCell(0); 
  cell.setCellStyle(cellStyle); 
  cell.setCellValue("序號(hào)"); 
  // 列號(hào)從1開始 
  int n = 1; 
  // 解析頭字符串 
  for (String str : header) { 
  String[] arr = str.split(":"); 
  columns.add(n + "," + arr[0]);// 添加要導(dǎo)出的字段名并且與列號(hào)n綁定 
  cell = row.createCell(n); 
  cell.setCellStyle(cellStyle); 
  cell.setCellValue(arr[1]); 
  n++; 
  } 
 
  // 生成數(shù)據(jù)行從1開開始，0為表頭 
  int i = 1; 
  // 生成數(shù)據(jù)行列 
  for (Map<String, Object> map : collect) { 
  HSSFRow dataRow = sheet.createRow(i); 
 
  // 生成序號(hào) 
  dataRow.createCell(0).setCellValue(i); 
  // 生成其他列 
  for (String column : columns) { 
   // 用逗號(hào)分割獲得字段名，[0]為列號(hào)用于和表頭標(biāo)題對(duì)應(yīng)上 
   String columnName = column.split(",")[1]; 
   // 生成序號(hào)列 
   cell = dataRow.createCell(Integer.parseInt(column.split(",")[0])); 
   String value = ""; 
   value = map.get(columnName) + ""; 
   // 當(dāng)value為null 時(shí)轉(zhuǎn)換為"" 
   if ("null".equals(value)) { 
   value = ""; 
   } 
   RichTextString richTextString = processHtml(book, value); 
   cell.getCellStyle().setWrapText(false); 
   cell.setCellValue(richTextString); 
  } 
  i++; 
  } 
  book.write(out); 
  out.close(); 
 } catch (Exception e) { 
  e.printStackTrace(); 
 } 
 return new ByteArrayInputStream(out.toByteArray()); 
 } 
 
 /** 
 * 獲得excel文件數(shù)據(jù)<br> 
 * 用法：<br> 
 * SheetInfo sheetInfo = new ExcelTools().new SheetInfo();<br> 
 * sheetInfo.setRowTitle(0); List<String> sheets = new ArrayList<String>();<br> 
 * String sheetName = "Sheet1"; sheets.add(sheetName);<br> 
 * sheetInfo.setSheetNames(sheets); <br> 
 * sheetInfo.setColumnsMapping(new String[] { "prodName:商品名稱", 
 * "prodSpec:規(guī)格", "collectPrice:價(jià)格:" + {@link RegExpEnum} 
 * RegExpEnum.NOTEMPTY_ISNUMBER, "priceUnit:單位", "collectMarket:報(bào)價(jià)市場", 
 * "prodLevel:等級(jí)" }); <br> 
 * Map<String, List> data = ExcelTools.getExcel(new File(path), sheetInfo); 
 * 
 * @param 
 * 
 * @param sheetInfo 
 *  初始化信息 
 * @return Map {sheet1:List} 
 * @throws Exception 
 *  Exception 
 */ 
 @SuppressWarnings("rawtypes") 
 public static Map getExcel(File f, SheetInfo sheetInfo, String excelType) throws Exception { 
 return getExcel(new FileInputStream(f), sheetInfo, excelType); 
 } 
 
 @SuppressWarnings({ "rawtypes", "unchecked" }) 
 public static Map getExcel(InputStream in, SheetInfo sheetInfo, String excelType) throws Exception { 
 Map<String, String> columnsMap = new HashMap<String, String>(); 
 // 列驗(yàn)證表達(dá)式map 
 List<String> errMsg = new ArrayList<String>(); 
 int errNum = 0;// 錯(cuò)誤總數(shù) 
 int errLimit = 10;// 限制錯(cuò)誤提示數(shù) 
 /** 用于存儲(chǔ)Excel根據(jù)指定規(guī)則讀取的所有內(nèi)容 */ 
 Map excelInfo = new HashMap(); 
 Workbook book = null; 
 try { 
  if (excelType.equals("xls")) { 
  book = new HSSFWorkbook(in); 
  //throw new BusinessException("excel版本太低，請(qǐng)使用2007以上版本（擴(kuò)展名為：xlsx）"); 
  } else { 
  book = new XSSFWorkbook(in); 
  } 
 } catch (OutOfMemoryError e) { 
  throw new RuntimeException("當(dāng)前excel文件過大，請(qǐng)檢查各個(gè)sheet表格中是否有無效空數(shù)據(jù)，包括帶有格式和線框的行列數(shù)據(jù)，請(qǐng)刪除這些無效數(shù)據(jù)！正常excle文件大小【1M】以內(nèi)"); 
 } 
 // checkTitle(book, sheetInfo); 
 // 獲得工作表數(shù)量 
 int sheetNum = sheetInfo.getSheetNames().size(); 
 // 循環(huán)所有的工作表，讀取里面的數(shù)據(jù) 
 for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) { 
  // 獲得當(dāng)前工作表對(duì)象 
  String sheetName = HtmlUtils.htmlUnescape(sheetInfo.getSheetNames().get(sheetIndex)); 
  Map<String, String> validateMap = new HashMap<String, String>(); 
  for (String mapstr : sheetInfo.getColumnsMapping().get(sheetName)) { 
  String[] arr = mapstr.split(":"); 
  columnsMap.put(arr[1], arr[0]); 
  if (arr.length == 3) {// 若果驗(yàn)證表達(dá)式不為空，則添加到map中 
   validateMap.put(arr[1], arr[2]); 
  } 
  } 
  Sheet sheet = book.getSheet(sheetName); 
  if (null == sheet) { 
  throw new RuntimeException(String.format("獲取表失敗，請(qǐng)確認(rèn)Sheet《%s》是否存在于excel中", sheetName)); 
  } 
  // 用于存儲(chǔ)所工作表中的數(shù)據(jù)內(nèi)容 
  List sheetList = new ArrayList(); 
  // 獲取當(dāng)前表格的行數(shù) 
  int rows = sheet.getLastRowNum(); 
  // 獲取當(dāng)前表格的列數(shù) 
  int columns = sheet.getRow(sheetInfo.getRowTitle()).getLastCellNum(); 
  if (rows <= sheetInfo.getRowTitle()) {// 如果當(dāng)前表格沒有需要的數(shù)據(jù)就繼續(xù)下一次循環(huán) 
  continue; 
  } 
  // 獲得當(dāng)前工作表標(biāo)題內(nèi)容 
  List<String> titleList = new ArrayList<String>(); 
  // 循環(huán)每一行中的每一個(gè)單元格，讀取單元格內(nèi)的值 
  Row titleRow = sheet.getRow(sheetInfo.getRowTitle()); 
  for (int jj = 0; jj < columns; jj++) { 
  Cell cellTitle = titleRow.getCell(jj); 
  if (cellTitle != null) { 
   int row = cellTitle.getRowIndex(); 
   int column = cellTitle.getColumnIndex(); 
   if (isMergedRegion(sheet, row, column)) { 
   titleList.add(getMergedRegionValue(sheet, row, column)); 
   } else { 
   titleList.add(getCellValue(cellTitle)); 
   } 
  } else { 
   throw new RuntimeException("表頭讀取錯(cuò)誤,當(dāng)前設(shè)置為第" + (sheetInfo.getRowTitle() + 1) + "行<br/>表頭內(nèi)容為：" + titleRow + "，請(qǐng)檢查是否正確，如有異常，可刪除表頭格式，重新粘貼表頭！"); 
  } 
  } 
  // System.out.println(titleList); 
  // 驗(yàn)證表頭 
  String[] titles = sheetInfo.getColumnsMapping().get(sheetName); 
  for (String s : titles) { 
  String[] colArr = s.split(":"); 
  // 如果Excel表格中的表頭缺少該字段 
  boolean include = false; 
  for (String t : titleList) { 
   if (StringUtils.deleteWhitespace(t).equalsIgnoreCase(colArr[1])) { 
   include = true; 
   break; 
   } 
  } 
  if (!include) { 
   throw new RuntimeException("【" + colArr[1] + "】'列不存在！當(dāng)前Excel表頭：" + titleList); 
  } 
  } 
  // 開始循環(huán)每一行，讀取每一行的值,從標(biāo)題下面一行開始讀取 
  for (int i = sheetInfo.getRowTitle() + 1; i <= rows; i++) { 
  Map rowMap = new HashMap(); 
  Row dataRow = sheet.getRow(i); 
  if (dataRow == null) { 
   throw new RuntimeException(String.format("excel第[%d]行為空，請(qǐng)檢查!", i + 1)); 
  } 
  for (int j = 0; j < columns; j++) {// 循環(huán)每一行中的每一個(gè)單元格，讀取單元格內(nèi)的值 
   String columnTitle = titleList.get(j); 
   if ("".equals(columnTitle)) { 
   continue; 
   } else { 
   Cell cell = dataRow.getCell(j); 
   String value = ""; 
    
   String columnMapping = ""; 
   // 單元列對(duì)應(yīng)的entity屬性名 
   for (String title : columnsMap.keySet()) { 
    if (StringUtils.deleteWhitespace(columnTitle).equalsIgnoreCase(title)) { 
    columnMapping = columnsMap.get(title); 
    break; 
    } 
   } 
    
   if (null != cell) { 
    cell.setCellType(Cell.CELL_TYPE_STRING); 
    CellStyle cellStyle= cell.getCellStyle(); 
    //單元格背景顏色 
    if (excelType.equals("xls")) { 
    HSSFColor color=(HSSFColor) cellStyle.getFillForegroundColorColor(); 
    if (j==0 && color!=null) { 
     rowMap.put("rowColor", convertRGBToHex(color.getTriplet())); 
    } 
 
    } else { 
    XSSFColor color=(XSSFColor) cellStyle.getFillForegroundColorColor(); 
    if (j==0 && color!=null) { 
     rowMap.put("rowColor", color.getARGBHex().substring(2)); 
    } 
    } 
    
    
    value = filterStr(cell + ""); 
    int mergRow = getMergedRegionRow(sheet, cell); 
    if (mergRow > 0 && !StringUtil.isEmpty(value)) { 
    String rowspan=""; 
    if (rowMap.get("rowspan")!=null) { 
     rowspan=rowMap.get("rowspan")+","; 
    } 
    rowMap.put("rowspan", rowspan+columnMapping+"-"+value+"-"+(mergRow + 1)); 
    } 
    if ( cell.getCellComment()!=null) { 
    //System.out.println(columnMapping+"@comment:"+cell.getCellComment().getString()); 
    rowMap.put(columnMapping+"@comment", cell.getCellComment().getString()); 
    } 
   } 
 
   // String columnMapping = columnsMap.get(columnTitle); 
   String validateReg = ""; 
   String validateRegMsg = ""; 
   if (null != validateMap.get(columnTitle)) { 
    // 驗(yàn)證正則表達(dá)式 
    RegExpEnum eum = RegExpEnum.valueOf(validateMap.get(columnTitle)); 
    validateReg = eum.getValue(); 
    validateRegMsg = eum.getText(); 
   } 
   if (!StringUtil.isEmpty(validateReg)) { 
    if (value.matches(validateReg)) { 
    rowMap.put(columnMapping, value); 
    } else { 
    errNum++; 
    if (errNum <= errLimit) { 
     errMsg.add("第" + i + "行：【" + columnTitle + "】數(shù)據(jù)為:'" + value.trim() + "' 不匹配！【" + validateRegMsg + "】</br>\n"); 
    } 
    } 
   } else { 
    if (StringUtil.isEmpty(columnMapping)) { 
    continue; 
    } else { 
    //int row = cell.getRowIndex(); 
    ///int column = cell.getColumnIndex(); 
    //if (isMergedRegion(sheet, row, column)) { 
    // rowMap.put(columnMapping, getMergedRegionValue(sheet, row, column)); 
    //} else { 
     rowMap.put(columnMapping, value); 
    //} 
    } 
   } 
   } 
  } 
  sheetList.add(rowMap); 
  } 
  excelInfo.put(sheet.getSheetName(), sheetList); 
 } 
 in.close(); 
 
 if (errMsg.size() > 0) { 
  if (errNum > errLimit) { 
  errMsg.add("您導(dǎo)入的數(shù)據(jù)模板格式錯(cuò)誤過多(共" + errNum + "個(gè))，請(qǐng)仔細(xì)檢查模板數(shù)據(jù)是否正確！"); 
  } 
  throw new RuntimeException(errMsg.toString().replaceAll("\\[|\\]", "")); 
 } 
 // if (true) throw new RuntimeException("測(cè)試"); 
 return excelInfo; 
 } 
 
 
 public static List<HashMap<String, String>> getExcel(InputStream in, SheetInfo sheetInfo) throws Exception { 
 Map<String, String> columnsMap = new HashMap<String, String>(); 
 // 列驗(yàn)證表達(dá)式map 
 Map<String, String> validateMap = new HashMap<String, String>(); 
 List<String> errMsg = new ArrayList<String>(); 
 int errNum = 0;// 錯(cuò)誤總數(shù) 
 int errLimit = 10;// 限制錯(cuò)誤提示數(shù) 
 for (String mapstr : sheetInfo.getColumnsMapping().get("columns")) { 
  String[] arr = mapstr.split(":"); 
  columnsMap.put(arr[1], arr[0]); 
  if (arr.length == 3) {// 若果驗(yàn)證表達(dá)式不為空，則添加到map中 
  validateMap.put(arr[1], arr[2]); 
  } 
 } 
 /** 用于存儲(chǔ)Excel根據(jù)指定規(guī)則讀取的所有內(nèi)容 */ 
 List excelInfo = new ArrayList(); 
 Workbook book = WorkbookFactory.create(in); 
 // checkTitle(book, sheetInfo); 
 // 獲得工作表數(shù)量 
 int sheetNum = book.getNumberOfSheets(); 
 // 循環(huán)所有的工作表，讀取里面的數(shù)據(jù) 
 for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) { 
  // 獲得當(dāng)前工作表對(duì)象 
  Sheet sheet = book.getSheetAt(sheetIndex); 
  // 用于存儲(chǔ)所工作表中的數(shù)據(jù)內(nèi)容 
//  List sheetList = new ArrayList(); 
  // 獲取當(dāng)前表格的行數(shù) 
  int rows = sheet.getLastRowNum(); 
  // 獲取當(dāng)前表格的列數(shù) 
  Row titleRow = sheet.getRow(sheetInfo.getRowTitle()); 
  if (titleRow == null){ 
  throw new BusinessException("文件格式不正確，請(qǐng)重新選擇或者下載模板"); 
  } 
  int columns = titleRow.getLastCellNum(); 
  if (columns != sheetInfo.getColumnsMapping().get("columns").length){ 
  throw new BusinessException("文件格式不正確，請(qǐng)重新選擇或者下載模板"); 
  } 
  if (rows <= sheetInfo.getRowTitle()) {// 如果當(dāng)前表格沒有需要的數(shù)據(jù)就繼續(xù)下一次循環(huán) 
  throw new BusinessException("文件格式不正確，請(qǐng)重新選擇或者下載模板"); 
  } 
 
  // 獲得當(dāng)前工作表標(biāo)題內(nèi)容 
  List<String> titleList = new ArrayList<String>(); 
  // 循環(huán)每一行中的每一個(gè)單元格，讀取單元格內(nèi)的值 
  for (int jj = 0; jj < columns; jj++) { 
  titleList.add(titleRow.getCell(jj).getStringCellValue()); 
  } 
  // 驗(yàn)證表頭 
  String[] titles = sheetInfo.getColumnsMapping().get("columns"); 
  for (String s : titles) { 
  // 如果Excel表格中的表頭缺少該字段 
  if (!titleList.contains(s.split(":")[1])) { 
//   errMsg.add("該Excel表格的'" + sheet.getSheetName() + "'表的'" + s 
//    + "'列不存在！"); 
   throw new BusinessException("文件格式不正確，請(qǐng)重新選擇或者下載模板"); 
  } 
  } 
 
  // 開始循環(huán)每一行，讀取每一行的值,從標(biāo)題下面一行開始讀取 
  for (int i = sheetInfo.getRowTitle() + 1; i <= rows; i++) { 
  Map rowMap = new HashMap(); 
  Row dataRow = sheet.getRow(i); 
  for (int j = 0; j < columns; j++) {// 循環(huán)每一行中的每一個(gè)單元格，讀取單元格內(nèi)的值 
   String columnTitle = titleList.get(j); 
   if ("".equals(columnTitle)) { 
   continue; 
   } else { 
   Cell cell = dataRow.getCell(j); 
   String value = getCellValue(cell); 
   // 單元列對(duì)應(yīng)的entity屬性名 
   String columnMapping = columnsMap.get(columnTitle); 
   String validateReg = ""; 
   String validateRegMsg = ""; 
   if (null != validateMap.get(columnTitle)) { 
    // 驗(yàn)證正則表達(dá)式 
    RegExpEnum eum = RegExpEnum.valueOf(validateMap 
     .get(columnTitle)); 
    validateReg = eum.getValue(); 
    validateRegMsg = eum.getText(); 
   } 
   if (!StringUtils.isEmpty(validateReg)) { 
    if (value.matches(validateReg)) { 
    rowMap.put(columnMapping, value); 
    } else { 
    errNum++; 
    if (errNum <= errLimit) { 
     errMsg.add("第" + i + "行：【" + columnTitle 
      + "】數(shù)據(jù)為:'" + value.trim() 
      + "' 不匹配！【" + validateRegMsg 
      + "】</br>\n"); 
    } 
    } 
   } else { 
    rowMap.put(columnMapping, value); 
   } 
   } 
  } 
  excelInfo.add(rowMap); 
  } 
//  excelInfo.put(sheet.getSheetName(), sheetList); 
 } 
 in.close(); 
 if (errMsg.size() > 0) { 
//  if (errNum > errLimit) { 
//  errMsg.add("您導(dǎo)入的數(shù)據(jù)模板格式錯(cuò)誤過多(共" + errNum + "個(gè))，請(qǐng)仔細(xì)檢查模板數(shù)據(jù)是否正確！"); 
//  } 
  throw new RuntimeException(errMsg.toString().replaceAll("\\[|\\]", 
   "")); 
 } 
 return excelInfo; 
 } 
 
 /** 
 * 
 * 用于excel操作，表格初始化信息 
 * 
 * @author: 季樂 
 * @date: 2013-12-2 下午1:43:04 
 * @since: 1.0 
 */ 
 public class SheetInfo { 
 
 /** 標(biāo)題所在的行，起始行是0，不是1 */ 
 private int rowTitle = 1; 
 
 /** 需要讀取數(shù)據(jù)字段中文名對(duì)應(yīng)的entity屬性名 */ 
 private Map<String, String[]> columnsMapping; 
 
 /** 需要讀取數(shù)據(jù)的sheet的名字 */ 
 public List<String> sheetNames = new ArrayList<String>(); 
 
 public SheetInfo(List<String> sheetNames) { 
  // 假如沒有定義sheetNames，則給予其默認(rèn)值”Sheet1“ 
  if (null == sheetNames || sheetNames.size() == 0) { 
  this.sheetNames.add("Sheet1"); 
  } else { 
  this.sheetNames = sheetNames; 
  } 
 } 
 
 public SheetInfo() { 
  // 假如沒有定義sheetNames，則給予其默認(rèn)值”Sheet1“ 
  if (null == sheetNames || sheetNames.size() == 0) { 
  sheetNames.add("Sheet1"); 
  } 
 } 
 
 public int getRowTitle() { 
  return rowTitle; 
 } 
 
 public void setRowTitle(int rowTitle) { 
  this.rowTitle = rowTitle; 
 } 
 
 public Map<String, String[]> getColumnsMapping() { 
  return columnsMapping; 
 } 
 
 public void setColumnsMapping(Map<String, String[]> columnsMapping) { 
  this.columnsMapping = columnsMapping; 
 } 
 
 public List<String> getSheetNames() { 
  return sheetNames; 
 } 
 
 public void setSheetNames(List<String> sheetNames) { 
  this.sheetNames = sheetNames; 
 } 
 } 
 
 /** 
 * 
 * 內(nèi)部枚舉類 
 * 
 * @author: 季樂 
 * @date: 2013-12-2 下午1:43:24 
 * @since: 1.0 
 */ 
 public enum RegExpEnum { 
 /** 不為空 */ 
 NOTEMPTY("不能為空", "(?! +$).+"), 
 /** 必須為數(shù)字 */ 
 ISNUMBER("必須為數(shù)字", "\\d*"), 
 /** 不為空并且為數(shù)字 */ 
 NOTEMPTY_ISNUMBER("不能為空且必須為數(shù)字", "\\d+"); 
 
 /** text */ 
 private String text; 
 /** level */ 
 private String value; 
 
 public String getText() { 
  return text; 
 } 
 
 public String getValue() { 
  return value; 
 } 
 
 private RegExpEnum(String text, String value) { 
  this.text = text; 
  this.value = value; 
 } 
 } 
 
 /** 
 * 將html轉(zhuǎn)為 RichTextString 
 * 
 * @param wb 
 *  HSSFWorkbook 
 * @param html 
 *  html 
 * @return RichTextString 
 */ 
 @SuppressWarnings("unused") 
 private static RichTextString processHtml(HSSFWorkbook wb, String html) { 
 RichTextString rt = null; 
 HTMLEditorKit kit = new HTMLEditorKit(); 
 HTMLDocument doc = (HTMLDocument) kit.createDefaultDocument(); 
 try { 
  kit.insertHTML(doc, doc.getLength(), html, 0, 0, null); 
  StringBuffer sb = new StringBuffer(); 
  for (int lines = 0, lastPos = -1; lastPos < doc.getLength(); lines++) { 
  // if (lines > 0) { 
  // sb.append('\n'); 
  // } 
  Element line = doc.getParagraphElement(lastPos + 1); 
  lastPos = line.getEndOffset(); 
  for (int elIdx = 0; elIdx < line.getElementCount(); elIdx++) { 
   final Element frag = line.getElement(elIdx); 
   String subtext = doc.getText(frag.getStartOffset(), frag.getEndOffset() - frag.getStartOffset()); 
   if (!subtext.equals("\n")) { 
   sb.append(subtext); 
   } 
  } 
  } 
  CreationHelper ch = wb.getCreationHelper(); 
  rt = ch.createRichTextString(sb.toString()); 
  for (int lines = 0, lastPos = -1; lastPos < doc.getLength(); lines++) { 
  Element line = doc.getParagraphElement(lastPos + 1); 
  lastPos = line.getEndOffset(); 
  for (int elIdx = 0; elIdx < line.getElementCount(); elIdx++) { 
   final Element frag = line.getElement(elIdx); 
   Font font = getFontFromFragment(wb, frag); 
   rt.applyFont(frag.getStartOffset() + lines, frag.getEndOffset() + lines, font); 
 
  } 
  } 
 } catch (Exception e) { 
  log.warn(e.getMessage()); 
  // e.printStackTrace(); 
 } 
 return rt; 
 } 
 
 /** 
 * 獲取字體 
 * 
 * @param wb 
 *  Workbook 
 * @param frag 
 *  frag 
 * @return Font 
 * @throws Exception 
 *  Exception 
 */ 
 private static Font getFontFromFragment(Workbook wb, Element frag) throws Exception { 
 Font font = wb.createFont(); 
 final AttributeSet as = frag.getAttributes(); 
 final Enumeration<?> ae = as.getAttributeNames(); 
 
 while (ae.hasMoreElements()) { 
  final Object attrib = ae.nextElement(); 
 
  if (CSS.Attribute.COLOR.equals(attrib)) { 
  Field f = as.getAttribute(attrib).getClass().getDeclaredField("c"); 
  f.setAccessible(true); 
  Color c = (Color) f.get(as.getAttribute(attrib)); 
  if (font instanceof XSSFFont) { 
   ((XSSFFont) font).setColor(new XSSFColor(c)); 
  } else if (font instanceof HSSFFont && wb instanceof HSSFWorkbook) { 
   HSSFPalette pal = ((HSSFWorkbook) wb).getCustomPalette(); 
   HSSFColor col = pal.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue()); 
   ((HSSFFont) font).setColor(col.getIndex()); 
  } 
  } else if (CSS.Attribute.FONT_WEIGHT.equals(attrib)) { 
  if ("bold".equals(as.getAttribute(attrib).toString())) { 
   font.setBoldweight(Font.BOLDWEIGHT_BOLD); 
  } 
  } 
 } 
 
 return font; 
 } 
 
 public static int getMergedRegionRow(Sheet sheet, Cell cell) { 
 // 得到一個(gè)sheet中有多少個(gè)合并單元格 
 int sheetmergerCount = sheet.getNumMergedRegions(); 
 for (int i = 0; i < sheetmergerCount; i++) { 
  // 得出具體的合并單元格 
  CellRangeAddress ca = sheet.getMergedRegion(i); 
  // 得到合并單元格的起始行, 結(jié)束行, 起始列, 結(jié)束列 
  int firstC = ca.getFirstColumn(); 
  int lastC = ca.getLastColumn(); 
  int firstR = ca.getFirstRow(); 
  int lastR = ca.getLastRow(); 
  // 判斷該單元格是否在合并單元格范圍之內(nèi), 如果是, 則返回 true 
  if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) { 
  if (cell.getRowIndex() == firstR) { 
   return lastR - firstR; 
  } 
  } 
 } 
 return 0; 
 } 
 
 /** 
 * 獲取合并單元格的值 
 * 
 * @param sheet 
 * @param row 
 * @param column 
 * @return 
 */ 
 public static String getMergedRegionValue(Sheet sheet, int row, int column) { 
 int sheetMergeCount = sheet.getNumMergedRegions(); 
 
 for (int i = 0; i < sheetMergeCount; i++) { 
  CellRangeAddress ca = sheet.getMergedRegion(i); 
  int firstColumn = ca.getFirstColumn(); 
  int lastColumn = ca.getLastColumn(); 
  int firstRow = ca.getFirstRow(); 
  int lastRow = ca.getLastRow(); 
 
  if (row >= firstRow && row <= lastRow) { 
 
  if (column >= firstColumn && column <= lastColumn) { 
   Row fRow = sheet.getRow(firstRow); 
   Cell fCell = fRow.getCell(firstColumn); 
   return getCellValue(fCell); 
  } 
  } 
 } 
 
 return null; 
 } 
 
 /** 
 * 判斷指定的單元格是否是合并單元格 
 * 
 * @param sheet 
 * @param row 
 *  行下標(biāo) 
 * @param column 
 *  列下標(biāo) 
 * @return 
 */ 
 public static boolean isMergedRegion(Sheet sheet, int row, int column) { 
 int sheetMergeCount = sheet.getNumMergedRegions(); 
 for (int i = 0; i < sheetMergeCount; i++) { 
  CellRangeAddress range = sheet.getMergedRegion(i); 
  int firstColumn = range.getFirstColumn(); 
  int lastColumn = range.getLastColumn(); 
  int firstRow = range.getFirstRow(); 
  int lastRow = range.getLastRow(); 
  if (row >= firstRow && row <= lastRow) { 
  if (column >= firstColumn && column <= lastColumn) { 
   return true; 
  } 
  } 
 } 
 return false; 
 } 
 
 /** 
 * 判斷sheet頁中是否含有合并單元格 
 * 
 * @param sheet 
 * @return 
 */ 
 @SuppressWarnings("unused") 
 private boolean hasMerged(Sheet sheet) { 
 return sheet.getNumMergedRegions() > 0 ? true : false; 
 } 
 
 /** 
 * 合并單元格 
 * 
 * @param sheet 
 * @param firstRow 
 *  開始行 
 * @param lastRow 
 *  結(jié)束行 
 * @param firstCol 
 *  開始列 
 * @param lastCol 
 *  結(jié)束列 
 */ 
 @SuppressWarnings("unused") 
 private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { 
 sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); 
 } 
 
 /** 
 * 獲取單元格的值 
 * 
 * @param cell 
 * @return 
 */ 
 public static String getCellValue(Cell cell) { 
 
 if (cell == null) 
  return ""; 
 
 if (cell.getCellType() == Cell.CELL_TYPE_STRING) { 
 
  return cell.getStringCellValue(); 
 
 } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { 
 
  return String.valueOf(cell.getBooleanCellValue()); 
 
 } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { 
 
  return cell.getCellFormula(); 
 
 } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { 
 
  if (HSSFDateUtil.isCellDateFormatted(cell)) {// 處理日期格式、時(shí)間格式 
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
  Date date = cell.getDateCellValue(); 
  return String.valueOf(sdf.format(date)); 
  } else if (cell.getCellStyle().getDataFormat() == 31) { 
  // 處理自定義日期格式：yy年mm月dd日(通過判斷單元格的格式id解決，id的值是31) 
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
  double value = cell.getNumericCellValue(); 
  Date date = org.apache.poi.ss.usermodel.DateUtil 
   .getJavaDate(value); 
  return String.valueOf(sdf.format(date)); 
  } else { 
  double value = cell.getNumericCellValue(); 
  CellStyle style = cell.getCellStyle(); 
  DecimalFormat format = new DecimalFormat(); 
  return String.valueOf(format.format(value)); 
  } 
 } 
 return ""; 
 } 
 
 public static String filterStr(String str) { 
 str = str.replace(String.valueOf((char) 160), "").replace(String.valueOf((char) 65279), ""); 
 str = str.trim(); 
 return str; 
 } 
 
 public static void main(String[] args) { 
 System.out.println(convertRGBToHex(HSSFColor.YELLOW.triplet)); 
 System.out.println(new XSSFColor(Color.YELLOW).getARGBHex().substring(2)); 
 System.err.println(HtmlUtils.htmlUnescape("匯總（電視&盒子&路由器）")); 
 } 
 
 static String convertRGBToHex(short[] rgb) { 
 int r= rgb[0],g=rgb[1],b=rgb[2]; 
 String rFString, rSString, gFString, gSString, bFString, bSString, result; 
 int red, green, blue; 
 int rred, rgreen, rblue; 
 red = r / 16; 
 rred = r % 16; 
 if (red == 10) rFString = "A"; 
 else if (red == 11) rFString = "B"; 
 else if (red == 12) rFString = "C"; 
 else if (red == 13) rFString = "D"; 
 else if (red == 14) rFString = "E"; 
 else if (red == 15) rFString = "F"; 
 else rFString = String.valueOf(red); 
 
 if (rred == 10) rSString = "A"; 
 else if (rred == 11) rSString = "B"; 
 else if (rred == 12) rSString = "C"; 
 else if (rred == 13) rSString = "D"; 
 else if (rred == 14) rSString = "E"; 
 else if (rred == 15) rSString = "F"; 
 else rSString = String.valueOf(rred); 
 
 rFString = rFString + rSString; 
 
 green = g / 16; 
 rgreen = g % 16; 
 
 if (green == 10) gFString = "A"; 
 else if (green == 11) gFString = "B"; 
 else if (green == 12) gFString = "C"; 
 else if (green == 13) gFString = "D"; 
 else if (green == 14) gFString = "E"; 
 else if (green == 15) gFString = "F"; 
 else gFString = String.valueOf(green); 
 
 if (rgreen == 10) gSString = "A"; 
 else if (rgreen == 11) gSString = "B"; 
 else if (rgreen == 12) gSString = "C"; 
 else if (rgreen == 13) gSString = "D"; 
 else if (rgreen == 14) gSString = "E"; 
 else if (rgreen == 15) gSString = "F"; 
 else gSString = String.valueOf(rgreen); 
 
 gFString = gFString + gSString; 
 
 blue = b / 16; 
 rblue = b % 16; 
 
 if (blue == 10) bFString = "A"; 
 else if (blue == 11) bFString = "B"; 
 else if (blue == 12) bFString = "C"; 
 else if (blue == 13) bFString = "D"; 
 else if (blue == 14) bFString = "E"; 
 else if (blue == 15) bFString = "F"; 
 else bFString = String.valueOf(blue); 
 
 if (rblue == 10) bSString = "A"; 
 else if (rblue == 11) bSString = "B"; 
 else if (rblue == 12) bSString = "C"; 
 else if (rblue == 13) bSString = "D"; 
 else if (rblue == 14) bSString = "E"; 
 else if (rblue == 15) bSString = "F"; 
 else bSString = String.valueOf(rblue); 
 bFString = bFString + bSString; 
 result = rFString + gFString + bFString; 
 return result; 
 
 } 
} 

<body> 
<div> 
 <form id="mainform" action="${ctx}/bom/ciscaseaction/${action}" method="post" enctype="multipart/form-data"> 
 <input type="file" name="file"/> 
 
 <a href="${ctx}/static/案由導(dǎo)入模板.xls" rel="external nofollow" >下載模板</a> 
 </form> 
</div> 
<script type="text/javascript"> 
 $(function(){ 
 $('#mainform').form({ 
  onSubmit: function(){ 
  var isValid = $(this).form('validate'); 
  return isValid; // 返回false終止表單提交 
  }, 
  success:function(data){ 
  successTip(data,dg,d); 
  } 
 }); 
 }); 
 
</script> 
</body> 

//導(dǎo)入 
function importAction(){ 
 d=$("#dlg").dialog({ 
 title: '案由導(dǎo)入', 
 width: 500, 
 height: 500, 
 href:'${ctx}/bom/ciscaseaction/importAction/', 
 maximizable:true, 
 modal:true, 
 buttons:[{ 
  text:'導(dǎo)入', 
  handler:function(){ 
  $('#mainform').submit(); 
  } 
 },{ 
  text:'取消', 
  handler:function(){ 
  d.panel('close'); 
  } 
 }] 
 }); 
} 

/** 
 * 導(dǎo)入頁面 
 */ 
 @RequestMapping(value = "importAction", method = RequestMethod.GET) 
 public String importForm( Model model) { 
 model.addAttribute("action", "import"); 
 return "system/cisCaseActionImoportForm"; 
 } 
 
 /** 
 * 導(dǎo)入 
 */ 
 @RequestMapping(value = "import", method = RequestMethod.POST) 
 @ResponseBody 
 public String importForm(@RequestParam("file") MultipartFile multipartFile, Model model) throws Exception { 
 cisCaseActionService.upload(multipartFile); 
 return "success"; 
} 

/** 
 * 導(dǎo)入案由 
*/ 
 @SuppressWarnings({ "rawtypes", "unchecked" }) 
 public void upload(MultipartFile multipartFile) throws Exception { 
  InputStream inputStream = multipartFile.getInputStream(); 
  ExcelTools excelTools = new ExcelTools(); 
  ExcelTools.SheetInfo sheetInfo = excelTools.new SheetInfo(); 
  sheetInfo.setRowTitle(0); 
  Map columns = new HashMap(); 
  columns.put("columns",new String[]{"name:案由名稱", "violateLaw:違反法律", "punishBasis:處罰依據(jù)"}); 
  sheetInfo.setColumnsMapping(columns); 
  List<HashMap<String, String>> mapList = ExcelTools.getExcel(inputStream, sheetInfo); 
  for (int i = 0; i < mapList.size(); i++){ 
  HashMap<String, String> map = mapList.get(i); 
 
  String name = map.get("name"); 
  if (StringUtils.isEmpty(name)){ 
   throw new BusinessException("第" + (i+2) + "案由名稱不能為空"); 
  } 
  String violateLaw = map.get("violateLaw"); 
  String punishBasis = map.get("punishBasis"); 
  CisCaseAction cisCaseAction=new CisCaseAction(); 
  cisCaseAction.setName(name); 
  cisCaseAction.setViolateLaw(violateLaw); 
  cisCaseAction.setPunishBasis(punishBasis); 
  this.insert(cisCaseAction); //調(diào)用同一層的插入方法 
  } 
 }