/*
* 文件名：ExcelUtils.java
* 版权：Copyright by aostarit
* 描述：excel表格工具类
*
*
*/
package com.aostar.ida.framework.util.excel;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;
import com.aostar.ida.framework.base.IdaConstants;
import com.aostar.ida.framework.base.exception.ServiceException;
import com.aostar.ida.framework.util.DateUtils;
/**
* 数据转excel和excel转数据
*
*/
public class ExcelUtils {
/**
* 日志对象
*/
private static final Logger LOGGER = Logger.getLogger(ExcelUtils.class);
/**
*
* 把java数据转成excel对象 调用方保证表头和数据的顺序对应，本方法不做检查
*
* @param datas
* 数据列表
* @param titles
* 表头 --单行
* @return excel对象
*/
public static HSSFWorkbook data2Excel(List<List<Object>> datas, List<String> titles, String sheetName) {
HSSFWorkbook book = new HSSFWorkbook();
HSSFCellStyle style = getCellStyle(book);
HSSFFont font = getFont(book);
// 把字体应用到样式
style.setFont(font);
// 创建sheet
HSSFSheet sheet = book.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
// sheet.setDefaultColumnWidth((short)15);
// 设置表头--单行
HSSFRow titleRow = sheet.createRow(0);
// 创建标题行
for (int i = 0; i < titles.size(); i++) {
HSSFCell cell = titleRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(titles.get(i));
}
// 写入数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行
HSSFRow dataRow = sheet.createRow(i + 1);
List<Object> rowDatas = datas.get(i);
if (CollectionUtils.isEmpty(rowDatas)) {
continue;
}
for (int j = 0; j < rowDatas.size(); j++) {
HSSFCell cell = dataRow.createCell(j);
setCellValue(cell, rowDatas.get(j));
}
}
return book;
}
public static HSSFWorkbook createWorkbook() {
HSSFWorkbook book = new HSSFWorkbook();
return book;
}
public static void addNewSheet(HSSFWorkbook book, List<List<Object>> datas, List<String> titles, String sheetName) {
HSSFCellStyle style = getCellStyle(book);
HSSFFont font = getFont(book);
// 把字体应用到样式
style.setFont(font);
// 创建sheet
HSSFSheet sheet = book.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
// sheet.setDefaultColumnWidth((short)15);
// 设置表头--单行
HSSFRow titleRow = sheet.createRow(0);
// 创建标题行
for (int i = 0; i < titles.size(); i++) {
HSSFCell cell = titleRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(titles.get(i));
}
// 写入数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行
HSSFRow dataRow = sheet.createRow(i + 1);
List<Object> rowDatas = datas.get(i);
if (CollectionUtils.isEmpty(rowDatas)) {
continue;
}
for (int j = 0; j < rowDatas.size(); j++) {
HSSFCell cell = dataRow.createCell(j);
setCellValue(cell, rowDatas.get(j));
}
}
}
// 表格样式
private static HSSFCellStyle getCellStyle(HSSFWorkbook book) {
HSSFCellStyle style = book.createCellStyle();
// 设置样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.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.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return style;
}
// 设置字体
private static HSSFFont getFont(HSSFWorkbook book) {
HSSFFont font = book.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
return font;
}
// 单元格填充数据
private static void setCellValue(HSSFCell cell, Object value) {
if (null == value) {
return;
}
if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue(DateUtils.dateToString((Date) value));
} else if (value instanceof Calendar) {
cell.setCellValue((Calendar) value);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof String) {
if (((String) value).length() > 32767) {
value = ((String) value).substring(0, 32767);
}
cell.setCellValue((String) value);
} else {
cell.setCellValue(value.toString());
}
}
public static ExcelVO byte2BigExcel(byte[] bytes, int type) throws IOException, OpenXML4JException, SAXException {
ExcelVO excelVO = null;
if (IdaConstants.EXCEL_TYPE_XLS == type) { // 处理excel2003文件
// ExcelXlsReader excelXls = new ExcelXlsReader();
// excelXls.process(fileName);
// 暂时2003用老的方式处理
return byte2Excel(bytes, type);
} else if (IdaConstants.EXCEL_TYPE_XLSX == type) {// 处理excel2007文件
ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
excelVO = excelXlsxReader.process(bytes);
} else {
return null;
}
return excelVO;
}
/**
*
* 将字节流转换为excel对象
*
* @param bytes
* 内容
* @param type
* 文件类型
* @return excel对象
*/
public static ExcelVO byte2Excel(byte[] bytes, int type) {
Workbook wb = null;
InputStream is = new ByteArrayInputStream(bytes);
if (IdaConstants.EXCEL_TYPE_XLS == type) {
try {
wb = new HSSFWorkbook(is);
} catch (IOException e) {
LOGGER.error(e.getMessage());
return null;
}
} else if (IdaConstants.EXCEL_TYPE_XLSX == type) {
try {
wb = new XSSFWorkbook(is);
} catch (IOException e) {
LOGGER.error(e.getMessage());
return null;
}
} else {
return null;
}
ExcelVO excelVO = new ExcelVO();
int sheetCount = wb.getNumberOfSheets();
if (sheetCount == 0) {
return excelVO;
}
List<SheetVO> sheets = new ArrayList<SheetVO>();
excelVO.setSheets(sheets);
// 计算公式
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < sheetCount; i++) {
SheetVO sheetVO = loadSheet(wb.getSheetAt(i), evaluator);
if (null == sheetVO || CollectionUtils.isEmpty(sheetVO.getTitles())) {
continue;
}
sheets.add(sheetVO);
}
return excelVO;
}
private static SheetVO loadSheet(Sheet sheet, FormulaEvaluator evaluator) {
SheetVO sheetVO = new SheetVO();
sheetVO.setName(sheet.getSheetName());
// 总行数
int rownum = sheet.getPhysicalNumberOfRows();
if (rownum == 0) {
return null;
}
// 获取第一行
Row titleRow = sheet.getRow(0);
// 列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
// 表头
List<String> titles = new ArrayList<>(coloumNum);
for (int i = 0; i < coloumNum; i++) {
String titleStr = titleRow.getCell(i).toString().trim();
// 处理特殊字符，全部替换为下划线，避免SQL报错,只保留汉字，字母，数字
String patten = "[^\u4e00-\u9fa5a-zA-Z0-9]";
titleStr = titleStr.replaceAll(patten, "_");
if (StringUtils.isBlank(titleStr)) {
throw new ServiceException("表格的第一行必须有值");
}
// 处理表头过长
if (titleStr.length() > 40) {
titleStr = titleStr.substring(0, 40);
}
titles.add(titleStr);
}
sheetVO.setTitles(titles);
// 只有表头，没有数据
if (rownum == 1) {
return sheetVO;
}
// 装数据
List<RowVO> datas = new ArrayList<RowVO>();
for (int i = 1; i < rownum; i++) {
Row dataRow = sheet.getRow(i);
if (null == dataRow) {
continue;
}
RowVO rowVO = new RowVO();
datas.add(rowVO);
List<CellVO> cells = new ArrayList<CellVO>();
rowVO.setCells(cells);
for (int j = 0; j < coloumNum; j++) {
String cellStr = "";
Cell cell = dataRow.getCell(j);
if (null == cell) {
cells.add(null);
continue;
}
CellVO cellVO = new CellVO();
int type = cell.getCellType();
if (Cell.CELL_TYPE_FORMULA == type) {
CellValue tempCellValue = evaluator.evaluate(cell);
double iCellValue = tempCellValue.getNumberValue();
cellStr = iCellValue + "";
}
if (Cell.CELL_TYPE_NUMERIC == type) {
if (DateUtil.isCellDateFormatted(cell)) {
cellVO.setDataType(CellVO.DATA_TYPE_DATE);
Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
cellStr = DateUtils.dateToString(date);
} else {
cellVO.setDataType(CellVO.DATA_TYPE_NUMERIC);
// 处理数字精度和科学计数法
BigDecimal bd = new BigDecimal(cell.toString());
cellStr = bd.toPlainString();
}
} else {
cellVO.setDataType(CellVO.DATA_TYPE_STRING);
cellStr = cell.toString();
}
cellVO.setValue(cellStr);
cells.add(cellVO);
}
}
sheetVO.setDatas(datas);
return sheetVO;
}
/**
*
* 获取excel表头和本地数据表字段名称映射
*
* @param sheetVO
* sheet页
* @return 映射关系 key:原始名称 value:本地存储表字段名称和数据类型
*/
public static Map<String, Map<String, String>> getColMapping(SheetVO sheetVO) {
List<String> titles = sheetVO.getTitles();
List<RowVO> datas = sheetVO.getDatas();
RowVO row = null;
if (CollectionUtils.isNotEmpty(datas)) {
row = datas.get(0);
}
Map<String, Map<String, String>> map = new HashMap<String, Map<String, String>>();
for (int i = 0; i < titles.size(); i++) {
Map<String, String> temMap = new HashMap<>();
String localCol = "col_" + i;
temMap.put("name", localCol);
String type = IdaConstants.DATA_TYPE_VARCHAR;
if (null != row) {
CellVO cell = row.getCells().get(i);
if (null != cell) {
int dataType = cell.getDataType();
if (CellVO.DATA_TYPE_NUMERIC == dataType) {
type = IdaConstants.DATA_TYPE_NUMERIC;
}
if (CellVO.DATA_TYPE_DATE == dataType) {
type = IdaConstants.DATA_TYPE_TIMESTAMP;
}
}
}
temMap.put("type", type);
map.put(titles.get(i), temMap);
}
return map;
}
// public static void main(String[] args) {
// File f = new File("e://安徽业绩考核综合指标2.xlsx");
// FileInputStream fs;
// try {
// fs = new FileInputStream(f);
// ExcelVO excel = byte2BigExcel(IOUtils.toByteArray(fs),
// IdaConstants.EXCEL_TYPE_XLSX);
// Map<String,Map<String,String>> da =
// getColMapping(excel.getSheets().get(0));
// System.out.println();
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}
