java POI excel解析

原创

终极码农 2016-11-09 10:52:14 ©著作权

©著作权归作者所有：来自51CTO博客作者终极码农的原创作品，请联系作者获取转载授权，否则将追究法律责任

/*

*/
package com.***.mapp.materialmanage.impl.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;
import com.ygsoft.ecp.service.tool.StringUtil;

/**
* PoiExcel工具类. 
*
* @author mapengfei 
* @version 1.0.0 2016年7月21日 
* @see
* @since JDK 1.5.0
*/
public class PoiExcelUtil {
 /**
 * 日志文件
 */
 private static final IEcpLog LOG = EcpLogFactory.getLog(PoiExcelUtil.class);

 /**
 * 默认的开始读取的行位置为第一行（索引值为0）
 */
 private final static int READ_START_POS = 0;

 /**
 * 默认结束读取的行位置为最后一行（索引值=0，用负数来表示倒数第n行）
 */
 private final static int READ_END_POS = 0;

 /**
 * 默认Excel内容的开始比较列位置为第一列（索引值为0）
 */
 private final static int COMPARE_POS = 0;

 /**
 * 默认多文件合并的时需要做内容比较（相同的内容不重复出现）
 */
 private final static boolean NEED_COMPARE = true;

 /**
 * 默认多文件合并的新文件遇到名称重复时，进行覆盖
 */
 private final static boolean NEED_OVERWRITE = true;

 /**
 * 默认只操作一个sheet
 */
 private final static boolean ONLY_ONE_SHEET = true;

 /**
 * 默认读取第一个sheet中（只有当ONLY_ONE_SHEET = true时有效）
 */
 private final static int SELECTED_SHEET = 0;

 /**
 * 默认从第一个sheet开始读取（索引值为0）
 */
 private final static int READ_START_SHEET = 0;

 /**
 * 默认在最后一个sheet结束读取（索引值=0，用负数来表示倒数第n行）
 */
 private final static int READ_END_SHEET = 0;

 /**
 * 默认打印各种信息
 */
 private final static boolean PRINT_MSG = true;

 /**
 * Excel文件路径
 */
 private String excelPath = "D:/excel/test.xls";

 /**
 * 设定开始读取的位置，默认为0
 */
 private int startReadPos = READ_START_POS;

 /**
 * 设定结束读取的位置，默认为0，用负数来表示倒数第n行
 */
 private int endReadPos = READ_END_POS;

 /**
 * 设定开始比较的列位置，默认为0
 */
 private int comparePos = COMPARE_POS;

 /**
 * 设定汇总的文件是否需要替换，默认为true
 */
 private boolean isOverWrite = NEED_OVERWRITE;

 /**
 * 设定是否需要比较，默认为true(仅当不覆写目标内容是有效，即isOverWrite=false时有效)
 */
 private boolean isNeedCompare = NEED_COMPARE;
 /**
 * 取得样式
 */
 private static CellStyle cellStyle = null;

 /**
 * 获取style.
 *
 * @return the style
 */
 public CellStyle getStyle() {
 return cellStyle;
 }

 /**
 * 设置style.
 *
 * @param newStyle
 * the style to set
 */
 public void setStyle(final CellStyle newStyle) {
 cellStyle = newStyle;
 }

 /**
 * 设定是否只操作第一个sheet
 */
 private boolean onlyReadOneSheet = ONLY_ONE_SHEET;

 /**
 * 设定操作的sheet在索引值
 */
 private int selectedSheetIdx = SELECTED_SHEET;

 /**
 * 设定操作的sheet的名称
 */
 private String selectedSheetName = "";

 /**
 * 设定开始读取的sheet，默认为0
 */
 private int startSheetIdx = READ_START_SHEET;

 /**
 * 设定结束读取的sheet，默认为0，用负数来表示倒数第n行
 */
 private int endSheetIdx = READ_END_SHEET;

 /**
 * 设定是否打印消息
 */
 private boolean printMsg = PRINT_MSG;

 public PoiExcelUtil() {

 }

 public PoiExcelUtil(final String excelPath) {
 this.excelPath = excelPath;
 }

 /**
 * 还原设定（其实是重新new一个新的对象并返回）
 *
 * @return
 */
 public PoiExcelUtil RestoreSettings() {
 PoiExcelUtil instance = new PoiExcelUtil(this.excelPath);
 return instance;
 }

 /**
 * 自动根据文件扩展名，调用对应的读取方法
 *
 * @Title: writeExcel
 * @param xlsPath
 * @throws IOException
 */
 public List<Row> readExcel() {
 try {
 return readExcel(this.excelPath);
 } catch (IOException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 return null;

 }

 /**
 * 自动根据文件扩展名，调用对应的读取方法
 *
 * @Title: writeExcel
 * @param xlsPath 文本
 * @throws IOException
 */
 public List<Row> readExcel(final String xlsPath) throws IOException {
 // 扩展名为空时，
 if (xlsPath.equals("")) {
 throw new IOException("文件路径不能为空！");
 } else {
 File file = new File(xlsPath);
 if (!file.exists()) {
 throw new IOException("文件不存在！");
 }
 }
 // 获取扩展名
 String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);
 try {

 if ("xls".equals(ext)) { // 使用xls方式读取
 return readExcel_xls(xlsPath);
 } else if ("xlsx".equals(ext)) { // 使用xlsx方式读取
 return readExcel_xlsx(xlsPath);
 } else { // 依次尝试xls、xlsx方式读取
 out("您要操作的文件没有扩展名，正在尝试以xls方式读取...");
 try {
 return readExcel_xls(xlsPath);
 } catch (IOException e1) {
 out("尝试以xls方式读取，结果失败！，正在尝试以xlsx方式读取...");
 try {
 return readExcel_xlsx(xlsPath);
 } catch (IOException e2) {
 out("尝试以xls方式读取，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
 throw e2;
 }
 }
 }
 } catch (IOException e) {
 throw e;
 }
 }

 /**
 * 自动根据文件扩展名，调用对应的写入方法
 *
 * @Title: writeExcel
 * @param rowList
 * @throws IOException
 */
 public void writeExcel(final List<Row> rowList) throws IOException {
 writeExcel(rowList, excelPath);
 }

 /**
 * 自动根据文件扩展名，调用对应的写入方法
 *
 * @Title: writeExcel
 * @param rowList
 * @param xlsPath
 * @throws IOException
 */
 public void writeExcel(final List<Row> rowList, final String xlsPath) throws IOException {
 // 扩展名为空时，
 if (xlsPath.equals("")) {
 throw new IOException("文件路径不能为空！");
 }
 // 获取扩展名
 String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);
 try {
 if ("xls".equals(ext)) { // 使用xls方式写入
 writeExcel_xls(rowList, xlsPath);
 } else if ("xlsx".equals(ext)) { // 使用xlsx方式写入
 writeExcel_xlsx(rowList, xlsPath);
 } else { // 依次尝试xls、xlsx方式写入
 out("您要操作的文件没有扩展名，正在尝试以xls方式写入...");
 try {
 writeExcel_xls(rowList, xlsPath);
 } catch (IOException e1) {
 out("尝试以xls方式写入，结果失败！，正在尝试以xlsx方式读取...");
 try {
 writeExcel_xlsx(rowList, xlsPath);
 } catch (IOException e2) {
 out("尝试以xls方式写入，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
 throw e2;
 }
 }
 }
 } catch (IOException e) {
 throw e;
 }
 }

 /**
 * 修改Excel（97-03版，xls格式）
 *
 * @Title: writeExcel_xls
 * @param rowList
 * @param dist_xlsPath
 * @throws IOException
 */
 public void writeExcel_xls(final List<Row> rowList, final String dist_xlsPath) throws IOException {
 writeExcel_xls(rowList, excelPath, dist_xlsPath);
 }

 /**
 * 修改Excel（97-03版，xls格式）
 *
 * @Title: writeExcel_xls
 * @param rowList
 * @param src_xlsPath
 * @param dist_xlsPath
 * @throws IOException
 */
 public void writeExcel_xls(final List<Row> rowList, final String src_xlsPath, final String dist_xlsPath)
 throws IOException {
 // 判断文件路径是否为空
 if (dist_xlsPath == null || dist_xlsPath.equals("")) {
 out("文件路径不能为空");
 throw new IOException("文件路径不能为空");
 }
 // 判断文件路径是否为空
 if (src_xlsPath == null || src_xlsPath.equals("")) {
 out("文件路径不能为空");
 throw new IOException("文件路径不能为空");
 }
 // 判断列表是否有数据，如果没有数据，则返回
 if (rowList == null || rowList.size() == 0) {
 out("文档为空");
 return;
 }
 try {
 HSSFWorkbook wb = null;
 // 判断文件是否存在
 File file = new File(dist_xlsPath);
 if (file.exists()) {
 // 如果复写，则删除后
 if (isOverWrite) {
 file.delete();
 // 如果文件不存在，则创建一个新的Excel
 // wb = new HSSFWorkbook();
 // wb.createSheet("Sheet1");
 wb = new HSSFWorkbook(new FileInputStream(src_xlsPath));
 } else {
 // 如果文件存在，则读取Excel
 wb = new HSSFWorkbook(new FileInputStream(file));
 }
 } else {
 // 如果文件不存在，则创建一个新的Excel
 // wb = new HSSFWorkbook();
 // wb.createSheet("Sheet1");
 wb = new HSSFWorkbook(new FileInputStream(src_xlsPath));
 }
 // 将rowlist的内容写到Excel中
 writeExcel(wb, rowList, dist_xlsPath);
 } catch (IOException e) {
 e.printStackTrace();
 }
 }

 /**
 * 修改Excel（97-03版，xls格式）
 *
 * @Title: writeExcel_xls
 * @param rowList
 * @param dist_xlsPath
 * @throws IOException
 */
 public void writeExcel_xlsx(final List<Row> rowList, final String dist_xlsPath) throws IOException {
 writeExcel_xls(rowList, excelPath, dist_xlsPath);
 }

 /**
 * 修改Excel（2007版，xlsx格式）
 *
 * @Title: writeExcel_xlsx
 * @param rowList
 * @param xlsPath
 * @throws IOException
 */
 public void writeExcel_xlsx(final List<Row> rowList, final String src_xlsPath, final String dist_xlsPath)
 throws IOException {
 // 判断文件路径是否为空
 if (dist_xlsPath == null || dist_xlsPath.equals("")) {
 out("文件路径不能为空");
 throw new IOException("文件路径不能为空");
 }
 // 判断文件路径是否为空
 if (src_xlsPath == null || src_xlsPath.equals("")) {
 out("文件路径不能为空");
 throw new IOException("文件路径不能为空");
 }
 // 判断列表是否有数据，如果没有数据，则返回
 if (rowList == null || rowList.size() == 0) {
 out("文档为空");
 return;
 }
 try {
 // 读取文档
 XSSFWorkbook wb = null;

 // 判断文件是否存在
 File file = new File(dist_xlsPath);
 if (file.exists()) {
 // 如果复写，则删除后
 if (isOverWrite) {
 file.delete();
 // 如果文件不存在，则创建一个新的Excel
 // wb = new XSSFWorkbook();
 // wb.createSheet("Sheet1");
 wb = new XSSFWorkbook(new FileInputStream(src_xlsPath));
 } else {
 // 如果文件存在，则读取Excel
 wb = new XSSFWorkbook(new FileInputStream(file));
 }
 } else {
 // 如果文件不存在，则创建一个新的Excel
 // wb = new XSSFWorkbook();
 // wb.createSheet("Sheet1");
 wb = new XSSFWorkbook(new FileInputStream(src_xlsPath));
 }
 // 将rowlist的内容添加到Excel中
 writeExcel(wb, rowList, dist_xlsPath);

 } catch (IOException e) {
 e.printStackTrace();
 }
 }

 /**
 * //读取Excel 2007版，xlsx格式
 *
 * @Title: readExcel_xlsx
 * @return
 * @throws IOException
 */
 public List<Row> readExcel_xlsx() throws IOException {
 return readExcel_xlsx(excelPath);
 }

 /**
 * //读取Excel 2007版，xlsx格式
 *
 * @Title: readExcel_xlsx
 * @return
 * @throws Exception
 */
 public List<Row> readExcel_xlsx(final String xlsPath) throws IOException {
 // 判断文件是否存在
 File file = new File(xlsPath);
 if (!file.exists()) {
 throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
 }
 XSSFWorkbook wb = null;
 List<Row> rowList = new ArrayList<Row>();
 try {
 FileInputStream fis = new FileInputStream(file);
 // 去读Excel
 wb = new XSSFWorkbook(fis);

 // 读取Excel 2007版，xlsx格式
 rowList = readExcel(wb);

 } catch (IOException e) {
 e.printStackTrace();
 }
 return rowList;
 }

 /***
 * 读取Excel(97-03版，xls格式)
 *
 * @throws IOException
 *
 * @Title: readExcel
 */
 public List<Row> readExcel_xls() throws IOException {
 return readExcel_xls(excelPath);
 }

 /***
 * 读取Excel(97-03版，xls格式)
 *
 * @throws Exception
 *
 * @Title: readExcel
 */
 public List<Row> readExcel_xls(final String xlsPath) throws IOException {
 // 判断文件是否存在
 File file = new File(xlsPath);
 if (!file.exists()) {
 throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
 }
 HSSFWorkbook wb = null;// 用于Workbook级的操作，创建、删除Excel
 List<Row> rowList = new ArrayList<Row>();
 try {
 // 读取Excel
 wb = new HSSFWorkbook(new FileInputStream(file));
 // 读取Excel 97-03版，xls格式
 rowList = readExcel(wb);

 } catch (IOException e) {
 e.printStackTrace();
 }
 return rowList;
 }

 /***
 * 读取单元格的值
 *
 * @Title: getCellValue
 * @param cell
 * @return
 */
 private String getCellValue(final Cell cell) {
 Object result = "";
 if (cell != null) {
 switch (cell.getCellType()) {
 case Cell.CELL_TYPE_STRING:
 result = cell.getStringCellValue();
 break;
 case Cell.CELL_TYPE_NUMERIC:
 result = cell.getNumericCellValue();
 break;
 case Cell.CELL_TYPE_BOOLEAN:
 result = cell.getBooleanCellValue();
 break;
 case Cell.CELL_TYPE_FORMULA:
 result = cell.getCellFormula();
 break;
 case Cell.CELL_TYPE_ERROR:
 result = cell.getErrorCellValue();
 break;
 case Cell.CELL_TYPE_BLANK:
 break;
 default:
 break;
 }
 }
 return result.toString();
 }

 /**
 * 通用读取Excel
 *
 * @Title: readExcel
 * @param wb
 * @return
 */
 private List<Row> readExcel(final Workbook wb) {
 List<Row> rowList = new ArrayList<Row>();
 int sheetCount = 1;// 需要操作的sheet数量
 Sheet sheet = null;
 if (onlyReadOneSheet) { // 只操作一个sheet
 // 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
 sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
 } else { // 操作多个sheet
 sheetCount = wb.getNumberOfSheets();// 获取可以操作的总数量
 }

 // 获取sheet数目
 for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
 // 获取设定操作的sheet
 if (!onlyReadOneSheet) {
 sheet = wb.getSheetAt(t);
 }

 // 获取最后行号
 int lastRowNum = sheet.getLastRowNum();

 if (lastRowNum > 0) { // 如果>0，表示有数据
 out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
 }

 Row row = null;
 // 循环读取
 for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
 row = sheet.getRow(i);
 if (row != null) {
 rowList.add(row);
 //out("第" + (i + 1) + "行：", false);
 // 获取每一单元格的值
 for (int j = 0; j < row.getLastCellNum(); j++) {
 String value = getCellValue(row.getCell(j));
 if (!value.equals("")) {
 //out(value + " | ", false);
 }
 }
 //out("");
 }
 }
 }
 return rowList;
 }

 /**
 * 修改Excel，并另存为
 *
 * @Title: WriteExcel
 * @param wb
 * @param rowList
 * @param xlsPath
 */
 private void writeExcel(final Workbook wb, final List<Row> rowList, final String xlsPath) {
 if (wb == null) {
 out("操作文档不能为空！");
 return;
 }
 Sheet sheet = wb.getSheetAt(0);// 修改第一个sheet中的值
 // 如果每次重写，那么则从开始读取的位置写，否则果获取源文件最新的行。
 int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
 int t = 0;// 记录最新添加的行数
 out("要添加的数据总条数为：" + rowList.size());
 for (Row row : rowList) {
 if (row == null)
 continue;
 // 判断是否已经存在该数据
 int pos = findInExcel(sheet, row);

 Row r = null;// 如果数据行已经存在，则获取后重写，否则自动创建新行。
 if (pos >= 0) {
 sheet.removeRow(sheet.getRow(pos));
 r = sheet.createRow(pos);
 } else {
 r = sheet.createRow(lastRowNum + t++);
 }

 // 用于设定单元格样式
 CellStyle newstyle = wb.createCellStyle();

 // 循环为新行创建单元格
 for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
 Cell cell = r.createCell(i);// 获取数据类型
 cell.setCellValue(getCellValue(row.getCell(i)));// 复制单元格的值到新的单元格
 // cell.setCellStyle(row.getCell(i).getCellStyle());//出错
 if (row.getCell(i) == null)
 continue;
 copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // 获取原来的单元格样式
 cell.setCellStyle(newstyle);// 设置样式
 // sheet.autoSizeColumn(i);//自动跳转列宽度
 }
 }

 out("其中检测到重复条数为:" + (rowList.size() - t) + " ，追加条数为：" + t);
 // 统一设定合并单元格
 setMergedRegion(sheet);
 try {
 // 重新将数据写入Excel中
 FileOutputStream outputStream = new FileOutputStream(xlsPath);
 wb.write(outputStream);
 outputStream.flush();
 outputStream.close();
 } catch (Exception e) {
 out("写入Excel时发生错误！ ");
 e.printStackTrace();
 }
 }

 /**
 * 查找某行数据是否在Excel表中存在，返回行数。
 *
 * @Title: findInExcel
 * @param sheet
 * @param row
 * @return
 */
 private int findInExcel(final Sheet sheet, final Row row) {
 int pos = -1;

 try {
 // 如果覆写目标文件，或者不需要比较，则直接返回
 if (isOverWrite || !isNeedCompare) {
 return pos;
 }
 for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) {
 Row r = sheet.getRow(i);
 if (r != null && row != null) {
 String v1 = getCellValue(r.getCell(comparePos));
 String v2 = getCellValue(row.getCell(comparePos));
 if (v1.equals(v2)) {
 pos = i;
 break;
 }
 }
 }
 } catch (Exception e) {
 e.printStackTrace();
 }
 return pos;
 }

 /**
 * 复制一个单元格样式到目的单元格样式
 *
 * @param fromStyle
 * @param toStyle
 */
 public static void copyCellStyle(final CellStyle fromStyle, final CellStyle toStyle) {
 toStyle.setAlignment(fromStyle.getAlignment());
 // 边框和边框颜色
 toStyle.setBorderBottom(fromStyle.getBorderBottom());
 toStyle.setBorderLeft(fromStyle.getBorderLeft());
 toStyle.setBorderRight(fromStyle.getBorderRight());
 toStyle.setBorderTop(fromStyle.getBorderTop());
 toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
 toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
 toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
 toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

 // 背景和前景
 toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
 toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

 // 数据格式
 toStyle.setDataFormat(fromStyle.getDataFormat());
 toStyle.setFillPattern(fromStyle.getFillPattern());
 // toStyle.setFont(fromStyle.getFont(null));
 toStyle.setHidden(fromStyle.getHidden());
 toStyle.setIndention(fromStyle.getIndention());// 首行缩进
 toStyle.setLocked(fromStyle.getLocked());
 toStyle.setRotation(fromStyle.getRotation());// 旋转
 toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
 toStyle.setWrapText(fromStyle.getWrapText());

 }

 /**
 * 获取合并单元格的值
 *
 * @param sheet
 * @param row
 * @param column
 * @return
 */
 public void setMergedRegion(final Sheet sheet) {
 int sheetMergeCount = sheet.getNumMergedRegions();

 for (int i = 0; i < sheetMergeCount; i++) {
 // 获取合并单元格位置
 CellRangeAddress ca = sheet.getMergedRegion(i);
 int firstRow = ca.getFirstRow();
 if (startReadPos - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面，则跳过。
 continue;
 }
 int lastRow = ca.getLastRow();
 int mergeRows = lastRow - firstRow;// 合并的行数
 int firstColumn = ca.getFirstColumn();
 int lastColumn = ca.getLastColumn();
 // 根据合并的单元格位置和大小，调整所有的数据行格式，
 for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
 // 设定合并单元格
 sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
 j = j + mergeRows;// 跳过已合并的行
 }

 }
 }

 /**
 * 打印消息，
 *
 * @param msg
 * 消息内容
 * @param tr
 * 换行
 */
 private void out(final String msg) {
 if (printMsg) {
 out(msg, true);
 }
 }

 /**
 * 打印消息，
 *
 * @param msg
 * 消息内容
 * @param tr
 * 换行
 */
 private void out(final String msg, final boolean tr) {
 if (printMsg) {
 System.out.print(msg + (tr ? "\n" : ""));
 }
 }

 public String getExcelPath() {
 return this.excelPath;
 }

 public void setExcelPath(final String excelPath) {
 this.excelPath = excelPath;
 }

 public boolean isNeedCompare() {
 return isNeedCompare;
 }

 public void setNeedCompare(final boolean isNeedCompare) {
 this.isNeedCompare = isNeedCompare;
 }

 public int getComparePos() {
 return comparePos;
 }

 public void setComparePos(final int comparePos) {
 this.comparePos = comparePos;
 }

 public int getStartReadPos() {
 return startReadPos;
 }

 public void setStartReadPos(final int startReadPos) {
 this.startReadPos = startReadPos;
 }

 public int getEndReadPos() {
 return endReadPos;
 }

 public void setEndReadPos(final int endReadPos) {
 this.endReadPos = endReadPos;
 }

 public boolean isOverWrite() {
 return isOverWrite;
 }

 public void setOverWrite(final boolean isOverWrite) {
 this.isOverWrite = isOverWrite;
 }

 public boolean isOnlyReadOneSheet() {
 return onlyReadOneSheet;
 }

 public void setOnlyReadOneSheet(final boolean onlyReadOneSheet) {
 this.onlyReadOneSheet = onlyReadOneSheet;
 }

 public int getSelectedSheetIdx() {
 return selectedSheetIdx;
 }

 public void setSelectedSheetIdx(final int selectedSheetIdx) {
 this.selectedSheetIdx = selectedSheetIdx;
 }

 public String getSelectedSheetName() {
 return selectedSheetName;
 }

 public void setSelectedSheetName(final String selectedSheetName) {
 this.selectedSheetName = selectedSheetName;
 }

 public int getStartSheetIdx() {
 return startSheetIdx;
 }

 public void setStartSheetIdx(final int startSheetIdx) {
 this.startSheetIdx = startSheetIdx;
 }

 public int getEndSheetIdx() {
 return endSheetIdx;
 }

 public void setEndSheetIdx(final int endSheetIdx) {
 this.endSheetIdx = endSheetIdx;
 }

 public boolean isPrintMsg() {
 return printMsg;
 }

 public void setPrintMsg(final boolean printMsg) {
 this.printMsg = printMsg;
 }

 /**
 * 初始化保存地址
 *
 * @param savePath
 */
 static void initPash(final String savePath) {
 String path = null;
 if (StringUtil.isNotEmptyString(savePath) && savePath.indexOf(".") > 0) {
 path = savePath.substring(0, savePath.lastIndexOf("/"));
 } else {
 if (LOG.isInfoEnabled()) {
 LOG.info("请确保你传过来的excel保存路径不为null，同时路径中包含了文件名");
 }
 }
 File file = new File(path);
 if (!file.isDirectory()) {
 file.mkdirs();
 if (LOG.isInfoEnabled()) {
 LOG.info("已为你成功创建路径为" + path + "的文件路径");
 }
 }
 }

 /**
 * 创建列表类型的Sheet
 *
 * @param wb
 * 记事博
 * @param sheetName
 * sheet名称
 * @param index
 * sheet坐标
 * @param titles
 * 标题
 * @param sources
 * 数据源
 */
 static void createSheet(final HSSFWorkbook wb, final String sheetName, final int index,
 final List<HeaderModel> titles, final List<ExcelModel> sources, final int rowNum) {
 HSSFSheet sheet = wb.createSheet();
 wb.setSheetName(index, sheetName);
 Font font1 = createFonts(wb, Font.BOLDWEIGHT_NORMAL, "宋体", false, (short) 200);
 cellStyle = wb.createCellStyle();
 // 设置表头
 setSheetTitle(wb, titles, sheet, rowNum);

 /**
 * 标颜色
 */
 CellStyle style = null;
 style = wb.createCellStyle();
 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

 /**
 * 表内容处理逻辑
 */
 CellStyle style1 = wb.createCellStyle();
 if (sources != null && sources.size() > 0) {
 int no = 1;
 if (rowNum > 1) {
 no = rowNum;
 }
 for (int i = 1; i < sources.size() + 1; i++) {
 Row r = sheet.createRow(i+(no-1)); // 取得行
 List<ExcelModel> ls = sources.get(i - 1).getDataList();
 if (ls != null && ls.size() > 0) {
 for (int j = 0; j < ls.size(); j++) {
 setStyleToKeyValueObject(style1);
 createCell(wb, r, j, String.valueOf(ls.get(j).getData()), font1); // 设置第二列字体
 r.getCell(j).setCellStyle(style1);
 if (ls.get(j).isHighlight()) { // 高亮
 r.getCell(j).setCellStyle(style);
 if (LOG.isDebugEnabled()) {
 LOG.debug((i - 1) + "行" + j + "列需要高亮");
 }
 }
 }
 } else {
 if (LOG.isDebugEnabled()) {
 LOG.debug("行对应的列为空了。");
 }
 }
 }
 } else {
 if (LOG.isDebugEnabled()) {
 LOG.debug("待导出的数据为空了。");
 }
 }
 }

 /**
 * 设置表头
 *
 * @param wb
 * @param titles
 * @param sheet
 * @param rowNum
 * 表头行数(表头占的行数)
 */
 private static void setSheetTitle(final HSSFWorkbook wb, final List<HeaderModel> titles, final HSSFSheet sheet,
 final int rowNum) {

 Row row = sheet.createRow(0);
 row.setHeightInPoints(20); // 行高
 Row row1 = null;
 if (rowNum == 2) {
 row1 = sheet.createRow(1);
 }
 Row row2 = null;
 if(rowNum == 3){
 row1 = sheet.createRow(1);
 row2 = sheet.createRow(2);
 }
 Font font0 = createFonts(wb, Font.BOLDWEIGHT_BOLD, "宋体", false, (short) 200);
 /**
 * 表头处理逻辑
 */
 if (titles != null && titles.size() > 0) {
 for (HeaderModel header : titles) {
 // 表示根据font0样式在wb页的第row行，n列写入gettitle值
 sheet.addMergedRegion(new CellRangeAddress(header.getRow_start(), header.getRow_end(),
 header.getStart(), header.getEnd()));
 createCell(wb, row, header.getStart(), header.getTitle(), font0);
 sheet.setColumnWidth(header.getStart(), 20 * 256); // 行宽
 List<HeaderModel> headers = header.getHeaders();
 if (headers != null && headers.size() > 0 && rowNum > 1) {
 int j = header.getStart();
 for (HeaderModel head : headers) {
 createCell(wb, row1, head.getStart(), head.getTitle(), font0); // 设置第二列字体
 sheet.setColumnWidth(j, 20 * 256); // 行宽
 sheet.addMergedRegion(new CellRangeAddress(head.getRow_start(), head.getRow_end(),
 head.getStart(), head.getEnd())); // 合并单元格
 j++;
 // 第三层
 List<HeaderModel> heads = head.getHeaders();
 if (heads != null && heads.size() > 0 && rowNum > 2) {
 int k = head.getStart();
 for (HeaderModel h : heads) {
 createCell(wb, row2, k, h.getTitle(), font0); // 设置第二列字体
 sheet.setColumnWidth(j, 20 * 256); // 行宽
 sheet.addMergedRegion(new CellRangeAddress(h.getRow_start(), h.getRow_end(),
 h.getStart(), h.getEnd())); // 合并单元格
 k++;
 }

 }
 }
 } else {
 if (LOG.isDebugEnabled()) {
 LOG.debug("二级标题为空了");
 }
 }
 }
 } else {
 if (LOG.isInfoEnabled()) {
 LOG.info("titles不能为空");
 }
 }
 }

 /**
 * 创建表单类型的Sheet
 *
 * @param wb
 * @param sheetName
 * @param index
 * @param list
 */
 static void createSheet(final HSSFWorkbook wb, final String sheetName, final int index,
 final List<ExcelSheetOfBill> list) {
 HSSFSheet sheet = wb.createSheet();
 wb.setSheetName(index, sheetName);
 cellStyle = wb.createCellStyle();
 if (list != null && list.size() > 0) {
 for (int i = 0; i < list.size(); i++) {
 ExcelSheetOfBill model = list.get(i); // 行
 setSheetOfKeyValue(wb, sheet, i, model);
 }
 }
 }

 /**
 * @param wb
 * @param sheet
 * @param i
 * @param model
 */
 private static void setSheetOfKeyValue(final HSSFWorkbook wb, final HSSFSheet sheet, final int i,
 final ExcelSheetOfBill model) {
 if (model != null) {
 Font font0 = createFonts(wb, Font.BOLDWEIGHT_BOLD, "宋体", false, (short) 200);
 Font font1 = createFonts(wb, Font.BOLDWEIGHT_NORMAL, "宋体", false, (short) 200);
 Row row = sheet.createRow(i);
 row.setHeightInPoints(20); // 行高
 List<ExcelSheetOfBill> sunList = model.getSheetBill();
 if (sunList != null && sunList.size() > 0) {
 for (ExcelSheetOfBill m : sunList) {
 sheet.setColumnWidth(m.getX(), 20 * 256); // 行宽
 if (m.isTitle()) { // 标题
 createCell(wb, row, m.getX(), m.getContext(), font0);
 } else {
 CellStyle style = wb.createCellStyle();
 setStyleToKeyValueObject(style);
 createCell(wb, row, m.getX(), m.getContext(), font1);
 row.getCell(m.getX()).setCellStyle(style);
 }
 }
 }
 }
 }

 /**
 * 设置样式
 *
 * @param style
 */
 private static void setStyleToKeyValueObject(final CellStyle style) {
 style.setFillForegroundColor(HSSFColor.WHITE.index);
 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
 style.setBorderRight(HSSFCellStyle.BORDER_THIN);
 style.setBorderTop(HSSFCellStyle.BORDER_THIN);
 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
 style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平
 }

 /**
 * 设置Cell
 *
 * @param wb
 * @param row
 * @param column
 * @param value
 * @param font
 */
 public static void createCell(final Workbook wb, final Row row, final int column, final String value,
 final Font font) {
 Cell cell = row.createCell(column);
 cell.setCellValue(value);
 cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
 cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
 cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
 cellStyle.setFont(font);
 cell.setCellStyle(cellStyle);
 }

 /**
 * 创建字体
 *
 * @param wb
 * @param bold
 * @param fontName
 * @param isItalic
 * @param hight
 * @return
 */
 public static Font createFonts(final Workbook wb, final short bold, final String fontName, final boolean isItalic,
 final short hight) {
 Font font = wb.createFont();
 font.setFontName(fontName);
 font.setBoldweight(bold);
 font.setItalic(isItalic);
 font.setFontHeight(hight);
 font.setFontHeightInPoints((short) 10);
 return font;
 }

 /**
 * @param args
 */
 public static void main(final String[] args) {
 // String savePath = "D:/excel/t8/tr/test.xls";
 long sDate = System.currentTimeMillis();
 // List<SheetModel> models = new ArrayList<SheetModel>();
 //
 // SheetModel model = new SheetModel(); // 表单sheet
 // List<ExcelSheetOfBill> ls = new ArrayList<ExcelSheetOfBill>();
 // createBillTypeOfTitle(ls);
 // model.setSheetName("固定资产卡片基本信息变更单");
 // model.setKeyOrValue(createBillHeadTitle(getTitles(3), getData3(), new
 // int[] { 0, 1 }));
 // model.setType(SHEET_TYPE_BILL);
 // models.add(model);
 //
 // SheetModel model1 = new SheetModel();
 // List<HeaderModel> list = new ArrayList<HeaderModel>();
 // createListTypeOfTitle(list);
 // model1.setSheetName("基本信息");
 // model1.setTitles(createHeaderModel(getTitles(2)));
 // model1.setSources(getData2());
 // model1.setType(SHEET_TYPE_LIST);
 // models.add(model1);

 // List<CustomizedSheetModel> sheets = new
 // ArrayList<CustomizedSheetModel>();
 // CustomizedSheetModel cm = new CustomizedSheetModel();
 // cm.setSheetName("固定资产卡片基本信息变更单");
 // cm.setContexts(getData3());
 // cm.setSheetType(SHEET_TYPE_BILL);
 // cm.setTitles(getTitles(3));
 // sheets.add(cm);
 //
 // CustomizedSheetModel cm1 = new CustomizedSheetModel();
 // cm1.setSheetName("基本信息");
 // cm1.setSheetType(SHEET_TYPE_LIST);
 // cm1.setTitles(getTitles(2));
 // cm1.setContexts(getData4());
 // sheets.add(cm1);
 //
 // exportExcelData(sheets, savePath);

 // 请参考该用例进行组装数据
 // exportExcelForDatas(models, savePath);
 // exportExcelData(getTitles(), getData(), savePath);
 long eDate = System.currentTimeMillis();
 System.out.println("15000条记录创建excel的时间为:" + (eDate - sDate));

 // 测试压缩
 // PoiExcelUtil.zipMultiFile("D:/excel/t8/tr/");
 // PoiExcelUtil.zipMultiFile("D:/excel/t8/tr",
 // "D:/excel/t8/tr/test.zip");

 // u.getTestDataList();
 // List<Row> row = u.readExcel();
 // System.out.println(row);
 }

}

/****************HeaderModel*****************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.util.List;

/**
* TODO 在此写上类的相关说明. 
* @author mapengfei 
* @version 1.0.0 2016年7月21日 
* @see
* @since JDK 1.5.0
*/
public class HeaderModel {
 /**
 * 标题头信息
 */
 private String title;

 /**
 * 行合并起始
 */
 private int row_start;
 /**
 * 行合并结束
 */
 private int row_end;
 /**
 * 获取row_start.
 * @return the row_start
 */
 public int getRow_start() {
 return row_start;
 }
 /**
 * 设置row_start.
 * @param newRow_start the row_start to set
 */
 public void setRow_start(final int newRow_start) {
 row_start = newRow_start;
 }
 /**
 * 获取row_end.
 * @return the row_end
 */
 public int getRow_end() {
 return row_end;
 }
 /**
 * 设置row_end.
 * @param newRow_end the row_end to set
 */
 public void setRow_end(final int newRow_end) {
 row_end = newRow_end;
 }
 /**
 * 单元格合并开始
 */
 private int start;
 /**
 * 单元格合并结束
 */
 private int end;
 /**
 * 获取start.
 * @return the start
 */
 public int getStart() {
 return start;
 }
 /**
 * 设置start.
 * @param newStart the start to set
 */
 public void setStart(final int newStart) {
 start = newStart;
 }
 /**
 * 获取end.
 * @return the end
 */
 public int getEnd() {
 return end;
 }
 /**
 * 设置end.
 * @param newEnd the end to set
 */
 public void setEnd(final int newEnd) {
 end = newEnd;
 }
 /**
 * 子标题头信息列表
 */
 private List<HeaderModel> headers;
 /**
 * 获取title.
 * @return the title
 */
 public String getTitle() {
 return title;
 }
 /**
 * 设置title.
 * @param newTitle the title to set
 */
 public void setTitle(final String newTitle) {
 title = newTitle;
 }
 /**
 * 获取headers.
 * @return the headers
 */
 public List<HeaderModel> getHeaders() {
 return headers;
 }
 /**
 * 设置headers.
 * @param newHeaders the headers to set
 */
 public void setHeaders(final List<HeaderModel> newHeaders) {
 headers = newHeaders;
 }

}

/**************************ExcelModel***************************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.util.List;

/**
* 在此写上类的相关说明. 
* @author mapengfei 
* @version 1.0.0 2016年7月21日 
* @see
* @since JDK 1.5.0
*/

public class ExcelModel {
 /**
 * 数据
 */
 private Object data;
 /**
 * 对象KEY
 */
 private String key;
 /**
 * 获取key.
 * @return the key
 */
 public String getKey() {
 return key;
 }

 /**
 * 设置key.
 * @param newKey the key to set
 */
 public void setKey(final String newKey) {
 key = newKey;
 }

 /**
 * 高亮
 */
 private boolean highlight;
 /**
 * 获取highlight.
 * @return the highlight
 */
 public boolean isHighlight() {
 return highlight;
 }

 /**
 * 设置highlight.
 * @param newHighlight the highlight to set
 */
 public void setHighlight(final boolean newHighlight) {
 highlight = newHighlight;
 }

 /**
 * 模型数据集合
 */
 private List<ExcelModel> dataList;

 /**
 * 获取dataList.
 * @return the dataList
 */
 public List<ExcelModel> getDataList() {
 return dataList;
 }

 /**
 * 设置dataList.
 * @param newDataList the dataList to set
 */
 public void setDataList(final List<ExcelModel> newDataList) {
 dataList = newDataList;
 }

 /**
 * 获取data.
 * @return the data
 */
 public Object getData() {
 return data;
 }

 /**
 * 设置data.
 * @param newData the data to set
 */
 public void setData(final Object newData) {
 data = newData;
 }

}

/*************************ExcelSheetOfBill*************************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.util.List;

/**
* TODO 在此写上类的相关说明. 
* @author mapengfei 
* @version 1.0.0 2016年9月29日 
* @see
* @since JDK 1.5.0
*/
public class ExcelSheetOfBill {
 /**
 * x 横坐标,默认为0
 */
 private int x;
 /**
 * 内容
 */
 private String context;
 /**
 * 键值对数组
 */
 private List<ExcelSheetOfBill> sheetBill;

 /**
 * 是标题
 */
 private boolean isTitle;

 /**
 * 获取x.
 * @return the x
 */
 public int getX() {
 return x;
 }

 /**
 * 设置x.
 * @param newX the x to set
 */
 public void setX(final int newX) {
 x = newX;
 }

 /**
 * 获取context.
 * @return the context
 */
 public String getContext() {
 return context;
 }

 /**
 * 设置context.
 * @param newContext the context to set
 */
 public void setContext(final String newContext) {
 context = newContext;
 }

 /**
 * 获取sheetBill.
 * @return the sheetBill
 */
 public List<ExcelSheetOfBill> getSheetBill() {
 return sheetBill;
 }

 /**
 * 设置sheetBill.
 * @param newSheetBill the sheetBill to set
 */
 public void setSheetBill(final List<ExcelSheetOfBill> newSheetBill) {
 sheetBill = newSheetBill;
 }

 /**
 * 获取isTitle.
 * @return the isTitle
 */
 public boolean isTitle() {
 return isTitle;
 }

 /**
 * 设置isTitle.
 * @param newIsTitle the isTitle to set
 */
 public void setTitle(final boolean newIsTitle) {
 isTitle = newIsTitle;
 }

}

/**********************************************ExcelExportOfData*************/

/*
*/
package com.***.mapp.materialmanage.impl.util;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;
import com.ygsoft.ecp.service.tool.StringUtil;

/**
* 供应商发票信息汇总导出excel. 
*
* @author mapengfei 
* @version 1.0.0 2016年10月8日 
* @see
* @since JDK 1.5.0
*/
public class ExcelExportOfData {

 /**
 * 日志文件
 */
 private static final IEcpLog LOG = EcpLogFactory.getLog(ExcelExportOfData.class);

 /**
 * 表单类型
 */
 private final static String SHEET_TYPE_BILL = "bill";
 /**
 * 列表类型
 */
 private final static String SHEET_TYPE_LIST = "list";

 /**
 * @param args
 */
 public static void main(final String[] args) {
 String savePath = "D:/excel/t8/tr/test.xls";
 // TODO Auto-generated method stub
 List<CustomizedSheetModel> sheets = new ArrayList<CustomizedSheetModel>();
// CustomizedSheetModel cm = new CustomizedSheetModel();
// cm.setSheetName("固定资产卡片基本信息变更单");
// cm.setContexts(getData3());
// cm.setSheetType(SHEET_TYPE_BILL);
// cm.setTitles(getTitles(3));
// sheets.add(cm);

 CustomizedSheetModel cm1 = new CustomizedSheetModel();
 cm1.setSheetName("基本信息");
 cm1.setSheetType(SHEET_TYPE_LIST);
 cm1.setTitles(getTitles(2));
 cm1.setContexts(getData4());
 sheets.add(cm1);

 exportExcelData(sheets, savePath);

 // exportExcelData(getTitles(1), null, savePath);
 }

 /**
 * 导出(不含数据合并单元格表头) 定制
 *
 * @param titles
 * 标题集合
 * @param list
 * 数据集合
 * @param savePath
 * 保存路径
 */

 @SuppressWarnings({ "rawtypes", "unchecked" })
 public static void exportExcelData(final String[] titles, final List<LinkedHashMap> list, final String savePath) {
 if (LOG.isInfoEnabled()) {
 LOG.info("你调用了exportExcelData方法");
 }
 long sDate = System.currentTimeMillis();
 List<HeaderModel> headers = new ArrayList<HeaderModel>();
 HeaderModel headModel = null;
 int i = 0;
 for (String head : titles) {
 headModel = new HeaderModel();
 headModel.setTitle(head);
 headModel.setStart(i);
 headModel.setEnd(i);
 headers.add(headModel);
 i++;
 }
 List<ExcelModel> excels = new ArrayList<ExcelModel>();
 ExcelModel y_index = null;

 if (list != null && list.size() > 0) {
 for (LinkedHashMap<String, Object> o : list) { // y轴
 y_index = new ExcelModel();
 getYindex(excels, y_index, o);
 }
 }
 List<SheetModel> models = new ArrayList<SheetModel>();
 SheetModel model = new SheetModel();
 model.setSheetName("sheet");
 model.setTitles(headers);
 model.setSources(excels);
 models.add(model);
 GenericExcelExport.exportExcelForDatas(models, 1, savePath);

 long eDate = System.currentTimeMillis();
 if (LOG.isDebugEnabled()) {
 LOG.info("15000条记录创建excel的时间为:" + (eDate - sDate));
 }
 }

 /**
 * 复杂组合的Excel导出
 *
 * @param sheets
 * sheet对象(包含了每一个sheet的内容)
 * @param savePath
 * 生成文件保存路径
 */
 public static void exportExcelData(final List<CustomizedSheetModel> sheets, final String savePath) {
 if (sheets != null && sheets.size() > 0) {
 long sDate = System.currentTimeMillis();
 long totalsize = 0;
 List<SheetModel> models = new ArrayList<SheetModel>();
 for (CustomizedSheetModel sheet : sheets) {
 String sheetType = sheet.getSheetType();
 if (StringUtil.isNotEmptyString(sheetType) && sheetType.equals(SHEET_TYPE_BILL)) { // 表单
 SheetModel model = new SheetModel(); // 表单sheet
 model.setSheetName(sheet.getSheetName());
 model.setKeyOrValue(
 createBillHeadTitle(sheet.getTitles(), sheet.getContexts(), new int[] { 0, 1 }));
 model.setType(SHEET_TYPE_BILL);
 models.add(model);

 } else { // 列表
 SheetModel model1 = new SheetModel();
 model1.setSheetName(sheet.getSheetName());
 model1.setTitles(createHeaderModel(sheet.getTitles()));

 List<LinkedHashMap<String, Object>> datas = sheet.getContexts();
 List<ExcelModel> ts = null;
 if (datas != null && datas.size() > 0) {
 totalsize = datas.size();
 ts = new ArrayList<ExcelModel>();
 ExcelModel m = null;
 for (LinkedHashMap<String, Object> data : datas) {
 m = new ExcelModel();
 getYindex(ts, m, data);
 }
 }
 model1.setSources(ts);
 model1.setType(SHEET_TYPE_LIST);
 models.add(model1);

 }
 }
 // 调用基础服务的方法
 GenericExcelExport.exportExcelForDatas(models, 2, savePath);
 long eDate = System.currentTimeMillis();
 // if (LOG.isDebugEnabled()) {
 LOG.info(totalsize + "条记录创建excel的时间为:" + (eDate - sDate));
 // }
 }
 }

 /**
 * 含有业务的Excel复杂表头处理
 *
 * @param titles
 * @return
 */
 private static List<HeaderModel> createHeaderModel(final String[] titles) {
 List<HeaderModel> hms = null;
 if (titles != null && titles.length > 0) {
 hms = new ArrayList<HeaderModel>();
 int i = 0;
 for (String title : titles) {
 boolean isIndex = true;
 if (StringUtil.isNotEmptyString(title)) {
 HeaderModel m = new HeaderModel();
 if (title.indexOf("##") > -1) {
 String[] ts = title.split("##");
 List<HeaderModel> list = new ArrayList<HeaderModel>();
 for (int k = 0; k < ts.length; k++) {
 if (k == 0) {
 m.setStart(i);
 m.setEnd(i + (ts.length - 1) - 1);
 m.setTitle(ts[0]);
 } else {
 HeaderModel hm = new HeaderModel();
 hm.setTitle(ts[k]);
 hm.setStart(i);
 hm.setEnd(i);
 list.add(hm);
 i++;
 isIndex = false;
 }
 }
 m.setHeaders(list);
 } else {
 setHeaderModel(i, title, m);
 }
 hms.add(m);
 }
 if (isIndex) {
 i++;
 }
 }
 }
 return hms;
 }

 /**
 * 设置表头信息
 *
 * @param i
 * x坐标
 * @param title
 * @param m
 */
 private static void setHeaderModel(final int i, final String title, final HeaderModel m) {
 m.setTitle(title);
 m.setStart(i);
 m.setEnd(i);
 m.setRow_start(0);
 m.setRow_end(1);
 }

 /**
 * 构造BIll表单数据结构
 *
 * @param titles
 * @param values
 * @param y_count
 * @return
 */
 private static List<ExcelSheetOfBill> createBillHeadTitle(final String[] titles,
 final List<LinkedHashMap<String, Object>> values, final int... y_count) {
 List<ExcelSheetOfBill> ls = null;
 if ((titles != null && values != null) && (titles.length > 0 && values.size() > 0)) {
 ls = new ArrayList<ExcelSheetOfBill>();
 if (y_count != null && y_count.length > 0) {
 ExcelSheetOfBill sheet = null;
 for (int y : y_count) {
 sheet = new ExcelSheetOfBill();
 List<ExcelSheetOfBill> bs = new ArrayList<ExcelSheetOfBill>();
 int x = 0; // X轴坐标
 for (int i = 0; i < titles.length; i++) {
 if (titles[i].indexOf("##") > -1) {
 String[] t = titles[i].split("##");
 LinkedHashMap<String, Object> v = values.get(0); // 表单有且只能存在一条记录
 if (y == Integer.valueOf(t[1])) {
 String key = t[0] != null ? t[0].toString() : "";
 ExcelSheetOfBill x_title = new ExcelSheetOfBill();
 x_title.setX(x);
 x_title.setContext(key);
 x_title.setTitle(true);
 bs.add(x_title);
 ExcelSheetOfBill x_value = new ExcelSheetOfBill();
 x_value.setX(x + 1);
 x_value.setTitle(false);
 x_value.setContext(v.get(titles[i]).toString());
 bs.add(x_value);
 } else {
 continue;
 }
 } else {
 if (LOG.isDebugEnabled()) {
 LOG.info("数据异常，请检查数据格式是否正确（aa##0代表数据填充在第一行）");
 }
 }
 x = x + 2;
 }
 sheet.setSheetBill(bs);
 ls.add(sheet);
 }
 }

 } else {
 if (LOG.isDebugEnabled()) {
 LOG.info("bill单据格式的sheet必须保证键值一对一，请检查titles 和 values");
 }
 }
 return ls;

 }

 /**
 * 构建X坐标
 *
 * @param excels
 * @param y_index
 * @param o
 */
 private static void getYindex(final List<ExcelModel> excels, final ExcelModel y_index,
 final LinkedHashMap<String, Object> o) {
 List<ExcelModel> exs = new ArrayList<ExcelModel>();
 ExcelModel x_index = null;
 List<String> columns = new ArrayList<String>();
 Set<Entry<String, Object>> set = o.entrySet();
 for (Iterator<Entry<String, Object>> iter = set.iterator(); iter.hasNext();) { // X轴
 x_index = new ExcelModel();
 Map.Entry entry = (Map.Entry) iter.next();
 String key = (String) entry.getKey();
 if (StringUtil.isNotEmptyString(key) && key.indexOf("_m") > 0) { // 含有M的
 Object value = entry.getValue(); // 取得具体的值
 if (value != null && String.valueOf(value).equals("0")) {
 columns.add(key.substring(0, key.lastIndexOf("_m")));
 }
 } else { // 不含有M的
 x_index = new ExcelModel();
 Object value = entry.getValue(); // 取得具体的值
 if (value == null || value == "null") {
 value = " ";
 }
 x_index.setData(value);
 x_index.setKey(key);
 exs.add(x_index); //
 }
 }
 setMark(exs, columns);
 y_index.setDataList(exs);
 excels.add(y_index);
 }

 /**
 * 设置标记
 *
 * @param exs
 * @param columns
 */
 private static void setMark(final List<ExcelModel> exs, final List<String> columns) {
 for (ExcelModel excelM : exs) {
 for (String c : columns) {
 if (StringUtil.isNotEmptyString(excelM.getKey()) && excelM.getKey().indexOf(c) >= 0) {
 excelM.setHighlight(true);
 }
 }
 }
 }

 /**
 * 模拟测试标题
 *
 * @return
 */
 private static String[] getTitles(final int type) {
 String[] list_1 = { "BT0", "BT1", "BT2", "BT3", "BT4", "BT5", "BT6", "BT7", "BT8", "BT9", "BT11", "BT12",
 "BT13", "BT14", "BT15", "BT16", "BT17", "BT18", "BT19", "BT20" };
 String[] list_2 = { "BT0", "BT1", "BT2", "BT3##SN1##SN2##SN3##SN4", "BT4", "BT5##SN1##SN2", "BT5", "BT6" };
 String[] list_3 = { "BT0##0", "BT1##0", "BT2##0", "BT3##1" };
 if (type == 1) {
 return list_1;
 } else if (type == 2) {
 return list_2;
 } else if (type == 3) {
 return list_3;
 }
 return null;
 }

 /**
 * 构造表单数据结构
 *
 * @return
 */
 private static List<LinkedHashMap<String, Object>> getData3() {
 List<LinkedHashMap<String, Object>> datamapList = new ArrayList<LinkedHashMap<String, Object>>();
 LinkedHashMap<String, Object> m = new LinkedHashMap<String, Object>();
 m.put("BT0##0", "sdfsdfs11");
 m.put("BT1##0", "ewrerfw21");
 m.put("BT2##0", "sdfsdf311");
 m.put("BT3##1", "sdf41");
 datamapList.add(m);
 return datamapList;
 }

 /**
 * 构造list列表数据
 *
 * @return
 */
 private static List<LinkedHashMap<String, Object>> getData4() {
 List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
 for (int k = 1; k < 10000; k++) {
 LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
 for (int i = 0; i < 12; i++) {
 map.put("T" + i, "该记录为性能测试数据，以每格45个字进行测试,测得的结果为导出excel的基础速度" + i + "毫秒");
 }
 list.add(map);
 }
 return list;

 }
}

/*********************************8CustomizedSheetModel*************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.util.LinkedHashMap;
import java.util.List;

/**
* TODO 在此写上类的相关说明. 
*
* @author mapengfei 
* @version 1.0.0 2016年9月30日 
* @see
* @since JDK 1.5.0
*/
public class CustomizedSheetModel {
 /**
 * 标题
 */
 String[] titles;
 /**
 * 内容
 */
 List<LinkedHashMap<String, Object>> contexts;
 /**
 * sheet名称
 */
 String sheetName;
 /**
 * sheet类型
 */
 String sheetType;
 /**
 * 获取titles.
 * @return the titles
 */
 public String[] getTitles() {
 return titles;
 }
 /**
 * 设置titles.
 * @param newTitles the titles to set
 */
 public void setTitles(final String[] newTitles) {
 titles = newTitles;
 }

 /**
 * 获取sheetName.
 * @return the sheetName
 */
 public String getSheetName() {
 return sheetName;
 }
 /**
 * 设置sheetName.
 * @param newSheetName the sheetName to set
 */
 public void setSheetName(final String newSheetName) {
 sheetName = newSheetName;
 }
 /**
 * 获取contexts.
 * @return the contexts
 */
 public List<LinkedHashMap<String, Object>> getContexts() {
 return contexts;
 }
 /**
 * 设置contexts.
 * @param newContexts the contexts to set
 */
 public void setContexts(final List<LinkedHashMap<String, Object>> newContexts) {
 contexts = newContexts;
 }
 /**
 * 获取sheetType.
 * @return the sheetType
 */
 public String getSheetType() {
 return sheetType;
 }
 /**
 * 设置sheetType.
 * @param newSheetType the sheetType to set
 */
 public void setSheetType(final String newSheetType) {
 sheetType = newSheetType;
 }
}

/**************************SheetModel*************************/

/*
* Copyright 2000-2020 YGSoft.Inc All Rights Reserved.
*/
package com.ygsoft.gris.mapp.materialmanage.impl.util;

import java.util.List;

/**
* SheetModel. 
* @author mapengfei 
* @version 1.0.0 2016年9月29日 
* @see
* @since JDK 1.5.0
*/
public class SheetModel {
 /**
 * sheet标题
 */
 private List<HeaderModel> titles;
 /**
 * 数据源
 */
 private List<ExcelModel> sources;
 /**
 * 键值
 */
 private List<ExcelSheetOfBill> keyOrValue;
 /**
 * 获取keyOrValue.
 * @return the keyOrValue
 */
 public List<ExcelSheetOfBill> getKeyOrValue() {
 return keyOrValue;
 }
 /**
 * 设置keyOrValue.
 * @param newKeyOrValue the keyOrValue to set
 */
 public void setKeyOrValue(final List<ExcelSheetOfBill> newKeyOrValue) {
 keyOrValue = newKeyOrValue;
 }
 /**
 * 记事薄
 */
 private String sheetName;
 /**
 * 类型
 */
 private String type;
 /**
 * 获取type.
 * @return the type
 */
 public String getType() {
 return type;
 }
 /**
 * 设置type.
 * @param newType the type to set
 */
 public void setType(final String newType) {
 type = newType;
 }
 /**
 * 获取titles.
 * @return the titles
 */
 public List<HeaderModel> getTitles() {
 return titles;
 }
 /**
 * 设置titles.
 * @param newTitles the titles to set
 */
 public void setTitles(final List<HeaderModel> newTitles) {
 titles = newTitles;
 }
 /**
 * 获取sources.
 * @return the sources
 */
 public List<ExcelModel> getSources() {
 return sources;
 }
 /**
 * 设置sources.
 * @param newSources the sources to set
 */
 public void setSources(final List<ExcelModel> newSources) {
 sources = newSources;
 }
 /**
 * 获取sheetName.
 * @return the sheetName
 */
 public String getSheetName() {
 return sheetName;
 }
 /**
 * 设置sheetName.
 * @param newSheetName the sheetName to set
 */
 public void setSheetName(final String newSheetName) {
 sheetName = newSheetName;
 }
}