package cn.cmodes.common.utils.poi;
import cn.cmodes.common.utils.DateUtils;
import cn.cmodes.common.utils.DictUtils;
import cn.cmodes.common.utils.StringUtils;
import cn.cmodes.framework.aspectj.lang.annotation.Excel;
import cn.cmodes.framework.config.SystemConfig;
import cn.cmodes.framework.web.domain.AjaxResult;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;/**
* Excel相关处理*/
public class ExcelUtil{private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);public Classclazz;public ExcelUtil(Classclazz) {this.clazz =clazz;
}/**
* 对excel表单默认第一个索引名转换成list
*
* @param input 输入流
* @return 转换后集合*/
public ListimportExcel(InputStream input) throws Exception {returnimportExcel(StringUtils.EMPTY, input);
}/**
* 对excel表单指定表格索引名转换成list
*
* @param sheetName 表格索引名
* @param input 输入流
* @return 转换后集合*/
public ListimportExcel(String sheetName, InputStream input) throws Exception {
List list = new ArrayList();
Workbook workbook=WorkbookFactory.create(input);
Sheet sheet= null;if(StringUtils.isNotEmpty(sheetName)) {//如果指定sheet名,则取指定sheet中的内容.
sheet =workbook.getSheet(sheetName);
}else{//如果传入的sheet名不存在则默认指向第1个sheet.
sheet = workbook.getSheetAt(0);
}if (sheet == null) {throw new IOException("文件sheet不存在");
}int rows =sheet.getPhysicalNumberOfRows();if (rows > 0) {//默认序号
int serialNum = 0;//有数据时才处理 得到类的所有field.
Field[] allFields =clazz.getDeclaredFields();//定义一个map用于存放列的序号和field.
Map fieldsMap = new HashMap();for (int col = 0; col < allFields.length; col++) {
Field field=allFields[col];//将有注解的field存放到map中.
if (field.isAnnotationPresent(Excel.class)) {//设置类的私有字段属性可访问.
field.setAccessible(true);
fieldsMap.put(++serialNum, field);
}
}for (int i = 1; i < rows; i++) {//从第2行开始取数据,默认第一行是表头.
Row row =sheet.getRow(i);int cellNum =serialNum;
T entity= null;for (int j = 0; j < cellNum; j++) {
Cell cell=row.getCell(j);if (cell == null) {continue;
}else{//先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
row.getCell(j).setCellType(CellType.STRING);
cell=row.getCell(j);
}
String c=cell.getStringCellValue();if(StringUtils.isEmpty(c)) {continue;
}//如果不存在实例则新建.
entity = (entity == null ?clazz.newInstance() : entity);//从map中得到对应列的field.
Field field = fieldsMap.get(j + 1);//取得类型,并根据对象类型设置值.
Class> fieldType =field.getType();if (String.class ==fieldType) {
field.set(entity, String.valueOf(c));
}else if ((Integer.TYPE == fieldType) || (Integer.class ==fieldType)) {
field.set(entity, Integer.parseInt(c));
}else if ((Long.TYPE == fieldType) || (Long.class ==fieldType)) {
field.set(entity, Long.valueOf(c));
}else if ((Float.TYPE == fieldType) || (Float.class ==fieldType)) {
field.set(entity, Float.valueOf(c));
}else if ((Short.TYPE == fieldType) || (Short.class ==fieldType)) {
field.set(entity, Short.valueOf(c));
}else if ((Double.TYPE == fieldType) || (Double.class ==fieldType)) {
field.set(entity, Double.valueOf(c));
}else if (Character.TYPE ==fieldType) {if ((c != null) && (c.length() > 0)) {
field.set(entity, Character.valueOf(c.charAt(0)));
}
}else if (java.util.Date.class ==fieldType) {if (cell.getCellTypeEnum() ==CellType.NUMERIC) {
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.format(cell.getNumericCellValue()));
c=sdf.format(cell.getNumericCellValue());
}else{
c=cell.getStringCellValue();
}
}else if (java.math.BigDecimal.class ==fieldType) {
c=cell.getStringCellValue();
}
}if (entity != null) {
list.add(entity);
}
}
}returnlist;
}/**
* 对list数据源将其里面的数据导入到excel表单
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果*/
public AjaxResult exportExcel(Listlist, String sheetName) {
OutputStreamout = null;
HSSFWorkbook workbook= null;try{//得到所有定义字段
Field[] allFields =clazz.getDeclaredFields();
List fields = new ArrayList();//得到所有field并存放到一个list中.
for(Field field : allFields) {if (field.isAnnotationPresent(Excel.class)) {
fields.add(field);
}
}//产生工作薄对象
workbook = newHSSFWorkbook();//excel2003中每个sheet中最多有65536行
int sheetSize = 65536;//取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() /sheetSize);for (int index = 0; index <= sheetNo; index++) {//产生工作表对象
HSSFSheet sheet =workbook.createSheet();if (sheetNo == 0) {
workbook.setSheetName(index, sheetName);
}else{//设置工作表的名称.
workbook.setSheetName(index, sheetName +index);
}
HSSFRow row;
HSSFCell cell;//产生单元格//产生一行
row = sheet.createRow(0);//写入各个字段的列头名称
for (int i = 0; i < fields.size(); i++) {
Field field= fields.get(i);
Excel attr= field.getAnnotation(Excel.class);//创建列
cell =row.createCell(i);//设置列中写入内容为String类型
cell.setCellType(CellType.STRING);
HSSFCellStyle cellStyle=workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);if (attr.name().indexOf("注：") >= 0) {
HSSFFont font=workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
sheet.setColumnWidth(i,6000);
}else{
HSSFFont font=workbook.createFont();//粗体显示
font.setBold(true);//选择需要用到的字体格式
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());//设置列宽
sheet.setColumnWidth(i, 3766);
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);//写入列名
cell.setCellValue(attr.name());//如果设置了提示信息则鼠标放上去提示.
if(StringUtils.isNotEmpty(attr.prompt())) {//这里默认设了2-101列提示.
setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);
}//如果设置了combo属性则本列只能选择不能输入
if (attr.combo().length > 0) {//这里默认设了2-101列只能选择不能输入.
setHSSFValidation(sheet, attr.combo(), 1, 100, i, i);
}
}int startNo = index *sheetSize;int endNo = Math.min(startNo +sheetSize, list.size());//写入各条记录,每条记录对应excel表中的一行
HSSFCellStyle cs =workbook.createCellStyle();
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);for (int i = startNo; i < endNo; i++) {
row= sheet.createRow(i + 1 -startNo);//得到导出对象.
T vo = (T) list.get(i);for (int j = 0; j < fields.size(); j++) {//获得field.
Field field = fields.get(j);//设置实体类私有属性可访问
field.setAccessible(true);
Excel attr= field.getAnnotation(Excel.class);try{//根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if(attr.isExport()) {//创建cell
cell =row.createCell(j);
cell.setCellType(CellType.NUMERIC);try{if (vo == null) {//如果数据存在就填入,不存在填入空格.
cell.setCellValue("");
}else{if(StringUtils.isNotBlank(attr.dictType())) {
cell.setCellValue(field.get(vo) == null ? "" : DictUtils.getDictName(field.get(vo).toString(), attr.dictType(), ""));
}else{
Object val= field.get(vo);//如果数据存在就填入,不存在填入空格.
String cellFormatString = "@";if(val instanceof String) {
cell.setCellValue((String) val);
cell.setCellType(CellType.STRING);
}else if(val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString= "0";
}else if(val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString= "0";
}else if(val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString= "0.00";
}else if(val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString= "0.00";
}else if(val instanceof Date) {
cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", (Date) val));
cellFormatString= "yyyy-MM-dd HH:mm";
cell.setCellType(CellType.STRING);
}else{
cell.setCellType(CellType.STRING);
cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));
}
cs.setDataFormat(workbook.createDataFormat().getFormat(cellFormatString));
}
}
cell.setCellStyle(cs);
}catch(Exception e) {
e.printStackTrace();
}
}
}catch(Exception e) {
log.error("导出Excel失败{}", e.getMessage());
}
}
}
}
String filename=encodingFilename(sheetName);out = newFileOutputStream(getAbsoluteFile(filename));
workbook.write(out);returnAjaxResult.success(filename);
}catch(Exception e) {
log.error("导出Excel异常{}", e.getMessage());return AjaxResult.error("导出Excel失败，请联系网站管理员！");
}finally{if (workbook != null) {try{
workbook.close();
}catch(IOException e1) {
e1.printStackTrace();
}
}if (out != null) {try{out.close();
}catch(IOException e1) {
e1.printStackTrace();
}
}
}
}/**
* 对list数据源将其里面的数据导入到excel表单
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果*/
public void exportExcel(Listlist, String sheetName, HttpServletResponse response) {
OutputStreamout = null;
HSSFWorkbook workbook= null;try{//得到所有定义字段
Field[] allFields =clazz.getDeclaredFields();
List fields = new ArrayList();//得到所有field并存放到一个list中.
for(Field field : allFields) {if (field.isAnnotationPresent(Excel.class)) {
fields.add(field);
}
}//产生工作薄对象
workbook = newHSSFWorkbook();//excel2003中每个sheet中最多有65536行
int sheetSize = 65536;//取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() /sheetSize);for (int index = 0; index <= sheetNo; index++) {//产生工作表对象
HSSFSheet sheet =workbook.createSheet();if (sheetNo == 0) {
workbook.setSheetName(index, sheetName);
}else{//设置工作表的名称.
workbook.setSheetName(index, sheetName +index);
}
HSSFRow row;
HSSFCell cell;//产生单元格//产生一行
row = sheet.createRow(0);//写入各个字段的列头名称
for (int i = 0; i < fields.size(); i++) {
Field field= fields.get(i);
Excel attr= field.getAnnotation(Excel.class);//创建列
cell =row.createCell(i);//设置列中写入内容为String类型
cell.setCellType(CellType.STRING);
HSSFCellStyle cellStyle=workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);if (attr.name().indexOf("注：") >= 0) {
HSSFFont font=workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
sheet.setColumnWidth(i,6000);
}else{
HSSFFont font=workbook.createFont();//粗体显示
font.setBold(true);//选择需要用到的字体格式
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());//设置列宽
sheet.setColumnWidth(i, 3766);
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);//写入列名
cell.setCellValue(attr.name());//如果设置了提示信息则鼠标放上去提示.
if(StringUtils.isNotEmpty(attr.prompt())) {//这里默认设了2-101列提示.
setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);
}//如果设置了combo属性则本列只能选择不能输入
if (attr.combo().length > 0) {//这里默认设了2-101列只能选择不能输入.
setHSSFValidation(sheet, attr.combo(), 1, 100, i, i);
}
}int startNo = index *sheetSize;int endNo = Math.min(startNo +sheetSize, list.size());//写入各条记录,每条记录对应excel表中的一行
HSSFCellStyle cs =workbook.createCellStyle();
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);for (int i = startNo; i < endNo; i++) {
row= sheet.createRow(i + 1 -startNo);//得到导出对象.
T vo = (T) list.get(i);for (int j = 0; j < fields.size(); j++) {//获得field.
Field field = fields.get(j);//设置实体类私有属性可访问
field.setAccessible(true);
Excel attr= field.getAnnotation(Excel.class);try{//根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if(attr.isExport()) {//创建cell
cell =row.createCell(j);
cell.setCellType(CellType.NUMERIC);try{if (vo == null) {//如果数据存在就填入,不存在填入空格.
cell.setCellValue("");
}else{if(StringUtils.isNotBlank(attr.dictType())) {
cell.setCellValue(field.get(vo) == null ? "" : DictUtils.getDictName(field.get(vo).toString(), attr.dictType(), ""));
}else{
Object val= field.get(vo);//如果数据存在就填入,不存在填入空格.
String cellFormatString = "@";if(val instanceof String) {
cell.setCellValue((String) val);
cell.setCellType(CellType.STRING);
}else if(val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString= "0";
}else if(val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString= "0";
}else if(val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString= "0.00";
}else if(val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString= "0.00";
}else if(val instanceof Date) {
cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", (Date) val));
cellFormatString= "yyyy-MM-dd HH:mm";
cell.setCellType(CellType.STRING);
}else{
cell.setCellType(CellType.STRING);
cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));
}
cs.setDataFormat(workbook.createDataFormat().getFormat(cellFormatString));
}
}
cell.setCellStyle(cs);
}catch(Exception e) {
e.printStackTrace();
}
}
}catch(Exception e) {
log.error("导出Excel失败{}", e.getMessage());
}
}
}
}
String filename=encodingFilename(sheetName);
filename= URLEncoder.encode(filename,"UTF-8");out = newFileOutputStream(getAbsoluteFile(filename));
response.addHeader("Content-Disposition", "attachment;filename=" +filename);//1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("multipart/form-data");out =response.getOutputStream();
workbook.write(out);
}catch(Exception e) {
log.error("导出Excel异常{}", e.getMessage());
}finally{if (workbook != null) {try{
workbook.close();
}catch(IOException e1) {
e1.printStackTrace();
}
}if (out != null) {try{out.close();
}catch(IOException e1) {
e1.printStackTrace();
}
}
}
}/**
* 设置单元格上提示
*
* @param sheet 要设置的sheet.
* @param promptTitle 标题
* @param promptContent 内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.*/
public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, intfirstRow,int endRow, int firstCol, intendCol) {//构造constraint对象
DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");//四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = newCellRangeAddressList(firstRow, endRow, firstCol, endCol);//数据有效性对象
HSSFDataValidation dataValidationView = newHSSFDataValidation(regions, constraint);
dataValidationView.createPromptBox(promptTitle, promptContent);
sheet.addValidationData(dataValidationView);returnsheet;
}/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.*/
public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, intendRow,int firstCol, intendCol) {//加载下拉列表内容
DVConstraint constraint =DVConstraint.createExplicitListConstraint(textlist);//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = newCellRangeAddressList(firstRow, endRow, firstCol, endCol);//数据有效性对象
HSSFDataValidation dataValidationList = newHSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidationList);returnsheet;
}/**
* 编码文件名*/
publicString encodingFilename(String filename) {return filename + ".xls";
}/**
* 获取下载路径
*
* @param filename 文件名称*/
publicString getAbsoluteFile(String filename) {
String downloadPath= SystemConfig.getProfile() +filename;
File desc= newFile(downloadPath);if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}returndownloadPath;
}
}
494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
