ExcelUtil

package com.sf.novatar.tpl.util;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import com.sf.novatar.tpl.bean.BeanBase;/** * 描述： *  * <pre>HISTORY * **************************************************************************** *  ID   DATE           PERSON          REASON *  1    2015年9月2日                                                 Create * **************************************************************************** * </pre> * @author  * @since 1.0 */public class ExcelUtil {    public static final String EXP_COLUMN ="dataColumn";    public static final String EXP_LIST ="dataList";    public static final String EXP_TITLE ="title";    public static final String EXP_SHEETNAME ="sheetName";/** * 判断是否合并了列 * @param sheet * @param rowNo 行号 * @return * boolean */public static boolean isMergedRow(Sheet sheet, int rowNo) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstRow = range.getFirstRow();int lastRow = range.getLastRow();if(firstRow != lastRow){if(rowNo >= firstRow && rowNo <= lastRow){return true;}}}return false;}/** * 合并列数  * @param sheet * @param rowNo * @return * int */public static int getColspan(Sheet sheet, int rowNo ,int colNo) {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(rowNo == firstRow && colNo == firstColumn){return lastColumn-firstColumn + 1;}if((colNo >= firstColumn && colNo <= lastColumn) && (rowNo >= firstRow && rowNo <= lastRow) ){return 0;}}return 1;}/** * 合并行数 * @param sheet * @param colNo * @return * int */public static int getRowspan(Sheet sheet, int rowNo ,int colNo) {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(rowNo == firstRow && colNo == firstColumn){return lastRow-firstRow + 1;}if((colNo >= firstColumn && colNo <= lastColumn) && (rowNo >= firstRow && rowNo <= lastRow) ){return 0;}}return 1;}/** * 判断是否合并了行 * @param sheet * @param colNo 列号 * @return * boolean */public static boolean isMergedCol(Sheet sheet, int colNo) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();if(firstColumn != lastColumn){if(colNo >= firstColumn && colNo <= lastColumn){return true;}}}return false;}/** * copy 行 * @param rowno 下标0开始 * void */public static void copySingleRow(Sheet sheet, int rowno) {List<CellRangeAddress> sourceRG = new ArrayList<CellRangeAddress>(); int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);CellRangeAddress range1 = range.copy(); int firstRow = range1.getFirstRow();int lastRow = range1.getLastRow();if(firstRow == lastRow && firstRow ==  rowno){range1.setFirstRow(1 + firstRow);range1.setLastRow(1 + lastRow);sourceRG.add(range1);}}sheet.shiftRows(rowno+1, sheet.getLastRowNum()+1, 1,true,false);Row sourceRow = sheet.getRow(rowno);Row targetRow = sheet.createRow(rowno + 1 );Cell sourceCell = null;Cell targetCell = null;for (int m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {sourceCell = sourceRow.getCell(m);if(sourceCell == null){continue;}targetCell = targetRow.createCell(m);targetCell.setCellStyle(sourceCell.getCellStyle());targetCell.setCellType(sourceCell.getCellType());targetCell.setCellValue("");}for(CellRangeAddress address : sourceRG){sheet.addMergedRegion(address);}}/** * EXCEL导出数据 */@SuppressWarnings({ "unchecked", "unused" })public static void exportExcel(BeanBase bean, Map<String, Object> param) {Map<String, Object[]> maps = (Map<String, Object[]>) param.get("dataColumn");List<Map<String, Object>> list = (List<Map<String, Object>>) param.get("dataList");String title = String.valueOf(param.get("title"));String sheetName = param.get("sheetName") == null? "sheet1":String.valueOf(param.get("sheetName")) ;Map<String, Object> map = new HashMap<String, Object>();List<String[]> excel = new ArrayList<String[]>();//列标题String[] cellsTitle = new String[maps.size()];int t = 0;for (Map.Entry<String, Object[]> entry : maps.entrySet()) {if (entry.getValue() != null) {Object[] o = entry.getValue();cellsTitle[t] = (String) o[0];}t++;}final Map<Integer, String> header = new HashMap<Integer, String>(); // 报表头部header.put(cellsTitle.length, title);String[] cells = null;int length = maps.size();int row = 0;for (Map<String, Object> data : list) {cells = new String[length];int i = 0;for (Map.Entry<String, Object[]> entry : maps.entrySet()) {if (entry.getValue() != null) {Object[] o = entry.getValue();Map<String, String> objMap = (Map<String, String>) o[1];String cellValue = null;if (objMap != null) {String dataValue = String.valueOf(data.get(entry.getKey()));if (dataValue.indexOf(",") != -1) {String[] dataValues = dataValue.split(",");for (String value : dataValues) {dataValue = dataValue.replace(value,objMap.get(value));}cellValue = dataValue;} else {cellValue = objMap.get(String.valueOf(data.get(entry.getKey())));}} else {cellValue = String.valueOf(data.get(entry.getKey()));}cells[i++] = "null".equals(cellValue) ? "" : cellValue;}}excel.add(cells);row++;}map.put("orgCode", sheetName);// sheet名 必填map.put("data", excel);map.put("header", header); // 统计头部map.put("cellsTitle", cellsTitle);map.put("fileName",title+"_" + DateUtil.date2Str(new Date(), "yyyy-MM-dd"));bean.getResultData().put("excelData", map);}}