package com.jianwu.util.excel;
import com.google.common.collect.Lists;
import com.jianwu.exception.MobileException;
import com.jianwu.util.excel.annotation.ExcelAttribute;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* poi工具类
* Created by tookbra on 2016/3/30.
*/
public class PoiUtil<T> {
private static final Logger logger = LoggerFactory.getLogger(PoiUtil.class);
// private static final String templatePath = "excel/template.txt";
private static String templatePath = "excel/template.txt";
Class<T> clazz;
public PoiUtil(Class<T> clazz) {
this.clazz = clazz;
}
public PoiUtil(Class<T> clazz, String templatePath) {
this.clazz = clazz;
this.templatePath = templatePath;
}
public static List excelList = null;
static {
if(excelList == null)
excelList = read(templatePath);
}
/**
* 配置文件的读取
*
* @param fileName
* 文件名称
* @return List 文件内容存放code,name格式
*/
public static List read(String fileName) {
File file = new File(PoiUtil.class.getClassLoader().getResource(PoiUtil.templatePath).getFile());
List list = Lists.newArrayList();
if (file != null) {
if (file.exists()) {
try {
BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
String str = null;
while ((str=br.readLine())!=null) {
list.add(str);
}
}
catch (Exception e) {
logger.error("读取excel文本错误:{}", e.getMessage());
}
}
else {
logger.error("读取excel文本错误，文件不存在");
System.out.println(fileName + "not exits");
}
}
return list;
}
public static String subText(String str,String field) {
try{
if (str == null || str.length() == 0 || field == null || field.length() == 0)
return str;
for(int i = 0;i<str.length();i++){
if(field.indexOf(str.charAt(i)) != -1){
return str.substring(0,i);
}
}
}catch(Exception ex){
logger.error("分割截取字符串时发生错误:{}", ex.getMessage());
}
return str;
}
/**
* 获取单元格内值
* @param cell 单元格
* @return
*/
public static String getCellValue(Cell cell) {
Object obj = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING :
obj = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC :
obj = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_ERROR :
obj = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_FORMULA :
obj = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN :
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
}
return String.valueOf(obj).trim();
}
public HSSFWorkbook exportExcel(List<T> list) throws IOException, IllegalAccessException {
// excel中每个sheet中最多有65536行
int sheetSize = 65536;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
int listSize = 0;
if (list != null && list.size() >= 0) {
listSize = list.size();
}
int endNo = Math.min(1 + sheetSize, listSize);
HSSFRow row;
// 得到所有定义字段
Field[] allFields = clazz.getDeclaredFields();
List<Field> fields = new ArrayList<Field>();
// 得到所有field并存放到一个list中
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelAttribute.class)) {
fields.add(field);
}
}
HSSFCell cell;// 产生单元格
row = sheet.createRow(0);// 产生一行
// 写入各个字段的列头名称
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
ExcelAttribute attr = field
.getAnnotation(ExcelAttribute.class);
int col = getExcelCol(attr.column());// 获得列号
cell = row.createCell(col);// 创建列
cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
cell.setCellValue(attr.value());// 写入列名
}
logger.info("导出{}条数据", endNo);
for (int i = 0; i < endNo; i++) {
row = sheet.createRow(i + 1);
// 得到导出对象.
T vo = (T) list.get(i);
for (int j = 0; j < fields.size(); j++) {
Field field = fields.get(j);// 获得field.
field.setAccessible(true);// 设置实体类私有属性可访问
ExcelAttribute attr = field.getAnnotation(ExcelAttribute.class);
if (attr.isExport()) {
cell = row.createCell(getExcelCol(attr.column()));// 创建cell
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(field.get(vo) == null ? ""
: String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
//sheet.autoSizeColumn(j);//自动列宽
}
}
}
return wb;
}
/**
*
* @param templatePath
* @param sheetName
* @param list
* @throws IOException
* @throws IllegalAccessException
*/
public HSSFWorkbook exportExcel(String templatePath, String sheetName, List<T> list)
throws IOException, IllegalAccessException {
// excel中每个sheet中最多有65536行
int sheetSize = 65536;
File file = new File(this.getClass().getClassLoader().getResource(templatePath).getFile());
if(!file.exists()){
System.out.println("模板文件:" + templatePath + "不存在!");
throw new MobileException("","");
}
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet(sheetName);
int listSize = 0;
if (list != null && list.size() >= 0) {
listSize = list.size();
}
int endNo = Math.min(2 + sheetSize, listSize);
HSSFRow row;
// 得到所有定义字段
Field[] allFields = clazz.getDeclaredFields();
List<Field> fields = new ArrayList<Field>();
// 得到所有field并存放到一个list中
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelAttribute.class)) {
fields.add(field);
}
}
HSSFCell cell;// 产生单元格
logger.info("导出{}条数据", endNo);
for (int i = 0; i < endNo; i++) {
row = sheet.createRow(i + 2);
// 得到导出对象.
T vo = (T) list.get(i);
for (int j = 0; j < fields.size(); j++) {
Field field = fields.get(j);// 获得field.
field.setAccessible(true);// 设置实体类私有属性可访问
ExcelAttribute attr = field.getAnnotation(ExcelAttribute.class);
if (attr.isExport()) {
cell = row.createCell(getExcelCol(attr.column()));// 创建cell
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(field.get(vo) == null ? ""
: String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
}
}
}
return wb;
}
/**
* 将EXCEL中A,B,C,D,E列映射成0,1,2,3
*
* @param col
*/
public static int getExcelCol(String col) {
col = col.toUpperCase();
// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
int count = -1;
char[] cs = col.toCharArray();
for (int i = 0; i < cs.length; i++) {
count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
}
return count;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
