package com.eacheart.admin.util;
import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
import java.util.Map.Entry;
/**
* 公共导入
*
* @author V
*
*/
public class ExcelImportUtils {
public static <T> List<T> excelToList(InputStream in, String sheetName,
Class<T> entityClass, LinkedHashMap<String, String> fieldMap,
String[] uniqueFields, Map<String, Integer> valueMap) throws Exception {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据Excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(in);
// 获取工作表
//Sheet sheet = wb.getSheet(sheetName);
Sheet sheet = null;
Sheet[] sheets = wb.getSheets();
for (Sheet s : sheets) {
if (s.getName().equals(sheetName)) {
sheet = s;
break;
}
}
if (sheet == null) {
throw new Exception("sheet:" + sheetName + "为空");
}
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null
|| "".equals(currentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new Exception("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel中的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
String notExistName = "";
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.values()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
notExistName = cnName;
break;
}
}
// 如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
throw new Exception("Excel中缺少必要的字段，或字段名称有误，notExistName: " + notExistName);
}
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
if (null != uniqueFields) {
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
for (int j = 0; j < uniqueFields.length; j++) {
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet.findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1].getRow(), true);
if (sameCell != null) {
nullCols++;
}
}
if (nullCols == uniqueFields.length) {
throw new Exception("Excel中有重复行，请检查");
}
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fieldMap.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getValue();
// 获取英文字段名
String enNormalName = entry.getKey();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents()
.toString().trim();
// 给对象赋值
setFieldValueByName(enNormalName, sheet, col, i, entity, valueMap);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("导入失败！【"+e.getMessage()+"】");
}
return resultList;
}
/**
* 根据字段名给对象的字段赋值
*
* @param fieldName 字段名
* 字段值
* @param o 对象
* @throws Exception 异常
*/
public static void setFieldValueByName(String fieldName, Sheet sheet, int col, int i, Object o, Map<String, Integer> valueMap) throws Exception {
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
String fieldValue = sheet.getCell(col, i).getContents().toString().trim();
// 获取字段类型
Class<?> fieldType = field.getType();
// 根据字段类型给字段赋值
if (valueMap != null && valueMap.containsKey(fieldValue)) {
field.set(o, valueMap.get(fieldValue));
} else if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null)
&& (fieldValue.toString().length() > 0)) {
field.set(o,
Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
Cell cell = sheet.getCell(col, i);
DateCell dc = (DateCell) cell;
field.set(o, dc.getDate());
} else {
field.set(o, fieldValue);
}
} else {
/* throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);*/
}
}
/**
* 根据字段名获取字段
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
// add by xiaoguang chang 2020 02 19
public static <T> List<T> excelToListNew(InputStream in, String sheetName,
Class<T> entityClass, LinkedHashMap<String, String> fieldMap,
String[] uniqueFields) throws Exception {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据Excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(in);
// 获取工作表
//Sheet sheet = wb.getSheet(sheetName);
Sheet sheet = null;
Sheet[] sheets = wb.getSheets();
for (Sheet s : sheets) {
if (s.getName().equals(sheetName)) {
sheet = s;
break;
}
}
if (sheet == null) {
throw new Exception("sheet:" + sheetName + "为空");
}
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null
|| "".equals(currentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new Exception("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel中的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
String notExistName = "";
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.values()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
notExistName = cnName;
break;
}
}
// 如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
throw new Exception("Excel中缺少必要的字段，或字段名称有误，notExistName: " + notExistName);
}
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
if (null != uniqueFields) {
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
for (int j = 0; j < uniqueFields.length; j++) {
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet.findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1].getRow(), true);
if (sameCell != null) {
nullCols++;
}
}
if (nullCols == uniqueFields.length) {
throw new Exception("Excel中有重复行，请检查");
}
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fieldMap.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getValue();
// 获取英文字段名
String enNormalName = entry.getKey();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents()
.toString().trim();
// 给对象赋值
setFieldValueByNameNew(enNormalName, sheet, col, i, entity);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("导入失败！【"+e.getMessage()+"】");
}
return resultList;
}
/**
* 根据字段名给对象的字段赋值
*
* @param fieldName 字段名
* 字段值
* @param o 对象
* @throws Exception 异常
*/
public static void setFieldValueByNameNew(String fieldName, Sheet sheet, int col, int i, Object o) throws Exception {
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
String fieldValue = sheet.getCell(col, i).getContents().toString().trim();
// 获取字段类型
Class<?> fieldType = field.getType();
// 根据字段类型给字段赋值
if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null)
&& (fieldValue.toString().length() > 0)) {
field.set(o,
Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
Cell cell = sheet.getCell(col, i);
DateCell dc = (DateCell) cell;
field.set(o, dc.getDate());
} else {
field.set(o, fieldValue);
}
} else {
/* throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "
+ fieldName);*/
}
}
// add end 2020 02 19
}
上面是工具类
如此调用：
public Result importExcelInsertBid(@RequestParam(value = "file") MultipartFile multipartFile) {
//Excel里的头部信息
String[] uniqueFields = {"日期","省份","城市","项目名称","采购范围及内容","服务期限","中标单位","中标金额","招标预算","采购单位","采购单位联系方式"};
//BidImportDTO.class 返回的类的映射
List<BidImportDTO> excel2List = ExcelImportUtils.excelToListNew(multipartFile.getInputStream(), "Sheet1", BidImportDTO.class, ImportExportConfig.importBidInfo(),uniqueFields);
return Result.error(PlatformCodeEnum.SAVE_ERROR);
}ImportExportConfig.importBidInfo()：
public static LinkedHashMap<String, String> importBidInfo(){
LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
map.put("bidDate", "日期");
map.put("province", "省份");
map.put("city", "城市");
map.put("projectName", "项目名称");
map.put("purchasingScopeContent", "采购范围及内容");
map.put("deadline", "服务期限");
map.put("bidCompany", "中标单位");
map.put("bidPrice", "中标金额");
map.put("bidBudget", "招标预算");
map.put("procurementUnits", "采购单位");
map.put("procurementUnitsPhone", "采购单位联系方式");
return map;
}
导出功能
调用方法
private Export<RecyclePriceStatisticsVo> recyclePriceStatisticsVo = new RecyclePriceStatisticsExport();
public void statisticsCollectorPriceRestExcel(@ModelAttribute StatisticsCollectorPriceExportDto statisticsCollectorPriceExportDto, HttpServletResponse resp, HttpServletRequest request ){
try{
log.info("------------>"+statisticsCollectorPriceExportDto);
String fileName = "收集员数据回收统计"+".xlsx";
fileName = FileUtil.decodeFileName(fileName, request);
resp.setHeader("Content-Disposition", "attachment; filename=" + fileName);
resp.setCharacterEncoding("UTF-8");
statisticsCollectorPriceExportDto.setStartTime(URLDecoder.decode(statisticsCollectorPriceExportDto.getStartTime(),"UTF-8"));
statisticsCollectorPriceExportDto.setEndTime(URLDecoder.decode(statisticsCollectorPriceExportDto.getEndTime(),"UTF-8"));
statisticsCollectorPriceExportDto.setCode(URLDecoder.decode(statisticsCollectorPriceExportDto.getCode(),"UTF-8"));
statisticsCollectorPriceExportDto.setPageNum(null);
statisticsCollectorPriceExportDto.setPageSize(null);
//staticesAllQueryDTO.setOrderType(URLDecoder.decode(staticesAllQueryDTO.getOrderType(),"UTF-8"));
StatisticsCollectorPriceDto statisticsCollectorPriceDto=new StatisticsCollectorPriceDto();
BeanUtils.copyProperties(statisticsCollectorPriceExportDto,statisticsCollectorPriceDto);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
statisticsCollectorPriceDto.setEndTime(sf.parse(statisticsCollectorPriceExportDto.getEndTime()));
statisticsCollectorPriceDto.setStartTime(sf.parse(statisticsCollectorPriceExportDto.getStartTime()));
List<RecyclePriceStatisticsVo> data = statisticsCollectorPriceRest(statisticsCollectorPriceDto).getData();
recyclePriceStatisticsVo.tenantExport(fileName, data, resp);
}catch (Exception e){
log.error(e.getMessage());
}调用方法
public class RecyclePriceStatisticsExport implements Export<RecyclePriceStatisticsVo> {
@Override
public void tenantExport(String fileName, List<RecyclePriceStatisticsVo> result, HttpServletResponse resp) {
List<String> header = new ArrayList<>();
List<List<String>> excelBody = new ArrayList<>();
header.add("收集员");
header.add("收集户数");
header.add("其他桶数");
header.add("易腐桶数");
header.add("收集总桶数");
if (CollectionUtils.isNotEmpty(result)){
ArrayList dataList;
for (RecyclePriceStatisticsVo recyclePriceStatisticsVo:result) {
dataList = new ArrayList();
dataList.add(Export.setCellStringValue(String.valueOf(recyclePriceStatisticsVo.getName())));
dataList.add(Export.setCellIntegerValue(recyclePriceStatisticsVo.getCollectedNum()));
dataList.add(Export.setCellStringValue(String.valueOf(recyclePriceStatisticsVo.getOtherBarrels())));
dataList.add(Export.setCellStringValue(String.valueOf(recyclePriceStatisticsVo.getPerishableBarrels())));
dataList.add(Export.setCellStringValue(String.valueOf(recyclePriceStatisticsVo.getNumSum())));
excelBody.add(dataList);
}
}
try{
ExcelUtils.getInstance().exportObjects2Excel(excelBody, header,resp.getOutputStream());
}catch (Exception e){
e.printStackTrace();
}
}
}export接口
public interface Export <T> {
/**
* 导出
* @param fileName 文件名
* @param result 实体
* @param resp
*/
void tenantExport(String fileName, List<T> result, HttpServletResponse resp);
static String setCellStringValue(String value){
String result = "";
if(value != null){
result = value;
}
return result;
}
static String setCellLongValue(Long value){
String result = "";
if(value != null){
result = value.toString();
}
return result;
}
static String setCellIntegerValue(Integer value){
String result = "";
if(value != null){
result = value.toString();
}
return result;
}
static String setCellDateValue(Date value){
String result = "";
if(value != null){
SimpleDateFormat format = new SimpleDateFormat(DateUtil.FORMAT_TIME);
result = format.format(value);
}
return result;
}
static String setCellNumberValue(BigDecimal value){
String result = "";
if(value != null){
result = String.valueOf(value);
}
return result;
}
}
ExcelUtils
package com.eacheart.admin.util.excel;
import com.eacheart.admin.util.excel.handler.ExcelHeader;
import com.eacheart.admin.util.excel.handler.ExcelTemplate;
import com.eacheart.admin.util.excel.utils.Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
public class ExcelUtils {
static private ExcelUtils excelUtils = new ExcelUtils();
private ExcelUtils() {
}
public static ExcelUtils getInstance() {
return excelUtils;
}
/*----------------------------------------读取Excel操作基于注解映射---------------------------------------------*/
/* 一. 操作流程 ： */
/* 1) 读取表头信息,与给出的Class类注解匹配 */
/* 2) 读取表头下面的数据内容, 按行读取, 并映射至java对象 */
/* 二. 参数说明 */
/* *) excelPath => 目标Excel路径 */
/* *) InputStream => 目标Excel文件流 */
/* *) clazz => java映射对象 */
/* *) offsetLine => 开始读取行坐标(默认0) */
/* *) limitLine => 最大读取行数(默认表尾) */
/* *) sheetIndex => Sheet索引(默认0) */
public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int limitLine, int
sheetIndex) throws Exception {
Workbook workbook = WorkbookFactory.create(new File(excelPath));
return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
}
public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int offsetLine, int limitLine, int
sheetIndex) throws Exception {
Workbook workbook = WorkbookFactory.create(is);
return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
}
public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int sheetIndex)
throws Exception {
return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, sheetIndex);
}
public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz)
throws Exception {
return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, 0);
}
public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int sheetIndex)
throws Exception {
return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, sheetIndex);
}
public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz)
throws Exception {
return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, 0);
}
private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
int sheetIndex) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetIndex);
Row row = sheet.getRow(offsetLine);
List<T> list = new ArrayList<>();
Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
if (maps == null || maps.size() <= 0)
throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");
int maxLine = sheet.getLastRowNum() > (offsetLine + limitLine) ? (offsetLine + limitLine) : sheet
.getLastRowNum();
for (int i = offsetLine + 1; i <= maxLine; i++) {
row = sheet.getRow(i);
T obj = clazz.newInstance();
for (Cell cell : row) {
int ci = cell.getColumnIndex();
ExcelHeader header = maps.get(ci);
if (null == header)
continue;
String filed = header.getFiled();
String val = Utils.getCellValue(cell);
Object value = Utils.str2TargetClass(val, header.getFiledClazz());
BeanUtils.copyProperty(obj, filed, value);
}
list.add(obj);
}
return list;
}
/*----------------------------------------读取Excel操作无映射--------------------------------------------------*/
/* 一. 操作流程 ： */
/* *) 按行读取Excel文件,存储形式为 Cell->String => Row->List<Cell> => Excel->List<Row> */
/* 二. 参数说明 */
/* *) excelPath => 目标Excel路径 */
/* *) InputStream => 目标Excel文件流 */
/* *) offsetLine => 开始读取行坐标(默认0) */
/* *) limitLine => 最大读取行数(默认表尾) */
/* *) sheetIndex => Sheet索引(默认0) */
public List<List<String>> readExcel2List(String excelPath, int offsetLine, int limitLine, int sheetIndex)
throws Exception {
Workbook workbook = WorkbookFactory.create(new File(excelPath));
return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
}
public List<List<String>> readExcel2List(InputStream is, int offsetLine, int limitLine, int sheetIndex)
throws Exception {
Workbook workbook = WorkbookFactory.create(is);
return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
}
public List<List<String>> readExcel2List(String excelPath, int offsetLine)
throws Exception {
Workbook workbook = WorkbookFactory.create(new File(excelPath));
return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0);
}
public List<List<String>> readExcel2List(InputStream is, int offsetLine)
throws Exception {
Workbook workbook = WorkbookFactory.create(is);
return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0);
}
public List<List<String>> readExcel2List(String excelPath)
throws Exception {
Workbook workbook = WorkbookFactory.create(new File(excelPath));
return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0);
}
public List<List<String>> readExcel2List(InputStream is)
throws Exception {
Workbook workbook = WorkbookFactory.create(is);
return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0);
}
private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine, int
sheetIndex)
throws Exception {
List<List<String>> list = new ArrayList<>();
Sheet sheet = workbook.getSheetAt(sheetIndex);
int maxLine = sheet.getLastRowNum() > (offsetLine + limitLine) ? (offsetLine + limitLine) : sheet
.getLastRowNum();
for (int i = offsetLine; i <= maxLine; i++) {
List<String> rows = new ArrayList<>();
Row row = sheet.getRow(i);
for (Cell cell : row) {
String val = Utils.getCellValue(cell);
rows.add(val);
}
list.add(rows);
}
return list;
}
/*--------------------------------------------基于模板、注解导出excel-------------------------------------------*/
/* 一. 操作流程 ： */
/* 1) 初始化模板 */
/* 2) 根据Java对象映射表头 */
/* 3) 写入数据内容 */
/* 二. 参数说明 */
/* *) templatePath => 模板路径 */
/* *) sheetIndex => Sheet索引(默认0) */
/* *) data => 导出内容List集合 */
/* *) extendMap => 扩展内容Map(具体就是key匹配替换模板#key内容) */
/* *) clazz => 映射对象Class */
/* *) isWriteHeader => 是否写入表头 */
/* *) targetPath => 导出文件路径 */
/* *) os => 导出文件流 */
public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap,
Class clazz, boolean isWriteHeader, String targetPath) throws Exception {
exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)
.write2File(targetPath);
}
public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap,
Class clazz, boolean isWriteHeader, OutputStream os) throws Exception {
exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)
.write2Stream(os);
}
public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
boolean isWriteHeader, String targetPath) throws Exception {
exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, targetPath);
}
public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
boolean isWriteHeader, OutputStream os) throws Exception {
exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, os);
}
public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
String targetPath) throws Exception {
exportObjects2Excel(templatePath, 0, data, extendMap, clazz, false, targetPath);
}
public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
OutputStream os) throws Exception {
exportObjects2Excel(templatePath, 0, data, extendMap, clazz, false, os);
}
public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, String targetPath)
throws Exception {
exportObjects2Excel(templatePath, 0, data, null, clazz, false, targetPath);
}
public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, OutputStream os)
throws Exception {
exportObjects2Excel(templatePath, 0, data, null, clazz, false, os);
}
private ExcelTemplate exportExcelByModuleHandler(String templatePath, int sheetIndex, List<?> data,
Map<String, String> extendMap, Class clazz, boolean isWriteHeader)
throws Exception {
ExcelTemplate templates = ExcelTemplate.getInstance(templatePath, sheetIndex);
templates.extendData(extendMap);
List<ExcelHeader> headers = Utils.getHeaderList(clazz);
if (isWriteHeader) {
// 写标题
templates.createNewRow();
for (ExcelHeader header : headers) {
templates.createCell(header.getTitle(), null);
}
}
for (Object object : data) {
templates.createNewRow();
templates.insertSerial(null);
for (ExcelHeader header : headers) {
templates.createCell(BeanUtils.getProperty(object, header.getFiled()), null);
}
}
return templates;
}
/*---------------------------------------基于模板、注解导出Map数据----------------------------------------------*/
/* 一. 操作流程 ： */
/* 1) 初始化模板 */
/* 2) 根据Java对象映射表头 */
/* 3) 写入数据内容 */
/* 二. 参数说明 */
/* *) templatePath => 模板路径 */
/* *) sheetIndex => Sheet索引(默认0) */
/* *) data => 导出内容Map集合 */
/* *) extendMap => 扩展内容Map(具体就是key匹配替换模板#key内容) */
/* *) clazz => 映射对象Class */
/* *) isWriteHeader => 是否写入表头 */
/* *) targetPath => 导出文件路径 */
/* *) os => 导出文件流 */
public void exportObject2Excel(String templatePath, int sheetIndex, Map<String, List> data,
Map<String, String> extendMap, Class clazz, boolean isWriteHeader, String targetPath)
throws Exception {
exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)
.write2File(targetPath);
}
public void exportObject2Excel(String templatePath, int sheetIndex, Map<String, List> data, Map<String, String>
extendMap, Class clazz, boolean isWriteHeader, OutputStream os) throws Exception {
exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)
.write2Stream(os);
}
public void exportObject2Excel(String templatePath, Map<String, List> data, Map<String, String> extendMap,
Class clazz, String targetPath) throws Exception {
exportExcelByModuleHandler(templatePath, 0, data, extendMap, clazz, false)
.write2File(targetPath);
}
public void exportObject2Excel(String templatePath, Map<String, List> data, Map<String, String> extendMap,
Class clazz, OutputStream os) throws Exception {
exportExcelByModuleHandler(templatePath, 0, data, extendMap, clazz, false)
.write2Stream(os);
}
private ExcelTemplate exportExcelByModuleHandler(String templatePath, int sheetIndex, Map<String, List> data,
Map<String, String> extendMap, Class clazz, boolean isWriteHeader)
throws Exception {
ExcelTemplate templates = ExcelTemplate.getInstance(templatePath, sheetIndex);
templates.extendData(extendMap);
List<ExcelHeader> headers = Utils.getHeaderList(clazz);
if (isWriteHeader) {
// 写标题
templates.createNewRow();
for (ExcelHeader header : headers) {
templates.createCell(header.getTitle(), null);
}
}
for (Map.Entry<String, List> entry : data.entrySet()) {
for (Object object : entry.getValue()) {
templates.createNewRow();
templates.insertSerial(entry.getKey());
for (ExcelHeader header : headers) {
templates.createCell(BeanUtils.getProperty(object, header.getFiled()), entry.getKey());
}
}
}
return templates;
}
/*----------------------------------------无模板基于注解导出---------------------------------------------------*/
/* 一. 操作流程 ： */
/* 1) 根据Java对象映射表头 */
/* 2) 写入数据内容 */
/* 二. 参数说明 */
/* *) data => 导出内容List集合 */
/* *) isWriteHeader => 是否写入表头 */
/* *) sheetName => Sheet索引名(默认0) */
/* *) clazz => 映射对象Class */
/* *) isXSSF => 是否Excel2007以上 */
/* *) targetPath => 导出文件路径 */
/* *) os => 导出文件流 */
public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF,
String targetPath) throws Exception {
FileOutputStream fos = new FileOutputStream(targetPath);
exportExcelNoModuleHandler(data, clazz, isWriteHeader, sheetName, isXSSF).write(fos);
}
public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF,
OutputStream os) throws Exception {
exportExcelNoModuleHandler(data, clazz, isWriteHeader, sheetName, isXSSF).write(os);
}
public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String targetPath)
throws Exception {
FileOutputStream fos = new FileOutputStream(targetPath);
exportExcelNoModuleHandler(data, clazz, isWriteHeader, null, true).write(fos);
}
public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, OutputStream os)
throws Exception {
exportExcelNoModuleHandler(data, clazz, isWriteHeader, null, true).write(os);
}
private Workbook exportExcelNoModuleHandler(List<?> data, Class clazz, boolean isWriteHeader, String sheetName,
boolean isXSSF) throws Exception {
Workbook workbook;
if (isXSSF) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
Sheet sheet;
if (null != sheetName && !"".equals(sheetName)) {
sheet = workbook.createSheet(sheetName);
} else {
sheet = workbook.createSheet();
}
Row row = sheet.createRow(0);
List<ExcelHeader> headers = Utils.getHeaderList(clazz);
if (isWriteHeader) {
// 写标题
for (int i = 0; i < headers.size(); i++) {
row.createCell(i).setCellValue(headers.get(i).getTitle());
}
}
// 写数据
Object _data;
for (int i = 0; i < data.size(); i++) {
row = sheet.createRow(i + 1);
_data = data.get(i);
for (int j = 0; j < headers.size(); j++) {
row.createCell(j).setCellValue(BeanUtils.getProperty(_data, headers.get(j).getFiled()));
}
}
for (int k = 0 ; k < data.size() ; k++){
sheet.autoSizeColumn((short)k); //调整第一列宽度
}
return workbook;
}
/*-----------------------------------------无模板无注解导出----------------------------------------------------*/
/* 一. 操作流程 ： */
/* 1) 写入表头内容(可选) */
/* 2) 写入数据内容 */
/* 二. 参数说明 */
/* *) data => 导出内容List集合 */
/* *) header => 表头集合,有则写,无则不写 */
/* *) sheetName => Sheet索引名(默认0) */
/* *) isXSSF => 是否Excel2007以上 */
/* *) targetPath => 导出文件路径 */
/* *) os => 导出文件流 */
public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF, String
targetPath) throws Exception {
exportExcelNoModuleHandler(data, header, sheetName, isXSSF).write(new FileOutputStream(targetPath));
}
public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF,
OutputStream os) throws Exception {
exportExcelNoModuleHandler(data, header, sheetName, isXSSF).write(os);
}
public void exportObjects2Excel(List<?> data, List<String> header, String targetPath) throws Exception {
exportExcelNoModuleHandler(data, header, null, true)
.write(new FileOutputStream(targetPath));
}
public void exportObjects2Excel(List<?> data, List<String> header, OutputStream os) throws Exception {
exportExcelNoModuleHandler(data, header, null, true).write(os);
}
public void exportObjects2Excel(List<?> data, String targetPath) throws Exception {
exportExcelNoModuleHandler(data, null, null, true)
.write(new FileOutputStream(targetPath));
}
public void exportObjects2Excel(List<?> data, OutputStream os) throws Exception {
exportExcelNoModuleHandler(data, null, null, true).write(os);
}
private Workbook exportExcelNoModuleHandler(List<?> data, List<String> header, String sheetName, boolean isXSSF)
throws Exception {
Workbook workbook;
if (isXSSF) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
Sheet sheet;
if (null != sheetName && !"".equals(sheetName)) {
sheet = workbook.createSheet(sheetName);
} else {
sheet = workbook.createSheet();
}
int rowIndex = 0;
if (null != header && header.size() > 0) {
// 写标题
Row row = sheet.createRow(rowIndex);
for (int i = 0; i < header.size(); i++) {
row.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(header.get(i));
}
rowIndex++;
}
for (Object object : data) {
Row row = sheet.createRow(rowIndex);
if (object.getClass().isArray()) {
for (int j = 0; j < Array.getLength(object); j++) {
row.createCell(j, Cell.CELL_TYPE_STRING).setCellValue(Array.get(object, j).toString());
}
} else if (object instanceof Collection) {
Collection<?> items = (Collection<?>) object;
int j = 0;
for (Object item : items) {
row.createCell(j, Cell.CELL_TYPE_STRING).setCellValue(item.toString());
j++;
}
} else {
row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(object.toString());
}
rowIndex++;
}
for (int k = 0 ; k < data.size() ; k++){
//调整第一列宽度
sheet.autoSizeColumn((short)k);
}
return workbook;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String fileName) {
return fileName.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String fileName) {
return fileName.matches("^.+\\.(?i)(xlsx)$");
}
public static Workbook getWorkBookByFileName(String fileName) {
if (isExcel2003(fileName)) {
return new HSSFWorkbook();
}
if (isExcel2007(fileName)) {
return new XSSFWorkbook();
}
return null;
}
}
扫一扫
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
