package com.hanboard.educloud.utils;
import com.wksc.commons.utils.db.UUIDUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.Color;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* <p>
* Excel 导出工具类
* </p>
*
* @author panyong Exp
* @version $FileName: ExportExcelUtil.java $Date: 2013-6-25
* @since 1.0
*/
public class ExportExcelUtil<T> {
// 2007 版本以上 最大支持1048576行
public final static String EXCEl_FILE_2007 = "2007";
// 2003 版本 最大支持65536 行
public final static String EXCEL_FILE_2003 = "2003";
/**
* <p>
* 导出无头部标题行Excel <br>
* 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
* </p>
*
* @param title 表格标题
* @param dataset 数据集合
* @param version 2003 或者 2007，不传时默认生成2003版本
*/
public static <T> void exportExcel(String title, String[] fields, Collection<T> dataset, HttpServletResponse response, String version) {
if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
ExportExcelUtil.exportExcel2003(title, null, fields, dataset, response,null, "yyyy-MM-dd hh:mm:ss", null, false);
} else {
ExportExcelUtil.exportExcel2007(title, null, fields, dataset, response,null, "yyyy-MM-dd hh:mm:ss", null, false);
}
}
/**
* <p>
* 导出带有头部标题行的Excel <br>
* 时间格式默认：yyyy-MM-dd hh:mm:ss <br>
* </p>
*
* @param title 表格标题
* @param headers 头部标题集合
* @param dataset 数据集合
* @param version 2003 或者 2007，不传时默认生成2003版本
*/
public static <T> void exportExcel(String title, String[] headers, String[] fields, Collection<T> dataset,
HttpServletResponse response, String version) {
if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
ExportExcelUtil.exportExcel2003(title, headers, fields, dataset, response, null,"yyyy-MM-dd hh:mm:ss", null, false);
} else {
ExportExcelUtil.exportExcel2007(title, headers, fields, dataset, response,null, "yyyy-MM-dd hh:mm:ss", null, false);
}
}
/**
* <p>
* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
* 此方法生成2003版本的excel,文件名后缀：xls <br>
* </p>
*
* @param title 表格标题名
* @param headers 表格头部标题集合
* @param fields 参数属性
* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
* JavaBean属性的数据类型有基本数据类型及String,Date
* @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
*/
@SuppressWarnings({"unchecked", "rawtypes"})
public static <T> void exportExcel2003(String title, String[] headers, String[] fields,
Collection<T> dataset, HttpServletResponse response, ServletOutputStream out,String pattern, HSSFWorkbook workbook, Boolean isMoreSheet) {
try {
if(out==null){
response.setContentType("application/octet-stream");
//转码防止乱码
response.addHeader("Content-Disposition",
"attachment;filename=" + new String(title.getBytes("gb2312"), "ISO8859-1") + ".xls");
out = response.getOutputStream();
}
// 声明一个工作薄(如果是空的)
// 不是多个,声明一个工作薄
if (!isMoreSheet) {
workbook = new HSSFWorkbook();
}
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(20);
//为标题设置样式
HSSFCellStyle titleStyle = getTitleStyle(workbook);//题目样式
HSSFCellStyle headStyle = getHeadStyle(workbook);//表头样式
HSSFCellStyle cellStyle = getCellStyle(workbook);//单元格样式
//在sheet里增加合并单元格
int length = headers.length;
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, length - 1);
sheet.addMergedRegion(cra);
Row rowtitle = sheet.createRow(0); //创建第一行（title）
rowtitle.setHeight((short) (6 * 100));//设置高度
//为标题创建单元格
Cell titleCell = rowtitle.createCell(0);
titleCell.setCellValue(title);
titleCell.setCellStyle(titleStyle);
HSSFRow row = sheet.createRow(1);
HSSFCell cellHeader;
for (int i = 0; i < headers.length; i++) {
cellHeader = row.createCell(i);
cellHeader.setCellStyle(headStyle);
cellHeader.setCellValue(new HSSFRichTextString(headers[i]));
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 1;
T t;
HSSFRichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
String getMethodName;
HSSFCell cell;
Class tCls;
Method getMethod;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
t = (T) it.next();
// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (int i = 0; i < fields.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
fieldName = fields[i];
if (StringUtils.isNotBlank(fieldName)) {
getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
tCls = t.getClass();
getMethod = tCls.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
}
//不是多个,直接输出 是多个不管
if (!isMoreSheet) {
workbook.write(out);
out.close();
}
} catch (IOException e) {
System.err.println(e.getMessage());
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* <p>
* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param title 表格标题名
* @param headers 表格头部标题集合
* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
* JavaBean属性的数据类型有基本数据类型及String,Date
* @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
*/
@SuppressWarnings({"unchecked", "rawtypes"})
public static <T> void exportExcel2007(String title, String[] headers, String[] fields,
Collection<T> dataset, HttpServletResponse response,ServletOutputStream out, String pattern, XSSFWorkbook workbook, Boolean isMoreSheet) {
try {
if(out==null){
response.setContentType("application/octet-stream");
//转码防止乱码
response.addHeader("Content-Disposition","attachment;filename=" + new String((title).getBytes("gb2312"), "ISO8859-1") + ".xls");
out = response.getOutputStream();
}
// 不是多个,声明一个工作薄
if (!isMoreSheet) {
workbook = new XSSFWorkbook();
}
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(20);
//为标题设置样式
XSSFCellStyle titleStyle = getTitleStyle(workbook);//题目样式
XSSFCellStyle headStyle = getHeadStyle(workbook);// 表头样式
XSSFCellStyle cellStyle = getCellStyle(workbook);// cell样式
//创建第一行数据
Row rowtitle = sheet.createRow(0); //创建第一行（title）
rowtitle.setHeight((short) (6 * 100));//设置高度
//为标题创建单元格
Cell titleCell = rowtitle.createCell(0);
titleCell.setCellValue(title);
titleCell.setCellStyle(titleStyle);
// 产生表格标题行
XSSFRow row = sheet.createRow(1);
XSSFCell cellHeader;
for (int i = 0; i < headers.length; i++) {
cellHeader = row.createCell(i);
cellHeader.setCellStyle(headStyle);
cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 1;
T t;
XSSFRichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
String getMethodName;
XSSFCell cell;
Class tCls;
Method getMethod;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
t = (T) it.next();
// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (int i = 0; i < fields.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
fieldName = fields[i];
if (StringUtils.isNotBlank(fieldName)) {
getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
tCls = t.getClass();
getMethod = tCls.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
}
//不是多个,直接输出 是多个不管
if (!isMoreSheet) {
workbook.write(out);
out.close();
}
} catch (IOException e) {
System.err.println(e.getMessage());
e.printStackTrace();
throw new RuntimeException(e);
}
}
@SuppressWarnings({"unchecked", "rawtypes"})
public static <T> void exportZipExcel2007(String title,String[] miniTitles, String[] headers, String[] fields, Integer[] widths,
Collection<T> dataset,String[] annotation, HttpServletResponse response, String pattern,String folderPath,String fileName) {
try {
// 不是多个,声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
//在sheet里增加合并单元格
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, headers.length - 1);
sheet.addMergedRegion(cra);
//为表头设置宽度
for (int i = 0; i < widths.length; i++) {
sheet.setColumnWidth(i, widths[i]);
}
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(20);
//为标题设置样式
XSSFCellStyle titleStyle = getTitleStyle(workbook);//题目样式
XSSFCellStyle miniTitleStyle = getMiniTitleStyle(workbook);//题目样式
XSSFCellStyle headStyle = getHeadStyle(workbook);// 表头样式
XSSFCellStyle cellStyle = getCellStyle(workbook);// cell样式
XSSFCellStyle annotationStyle = getAnnotationStyle(workbook);// cell样式
//创建第一行数据
Row rowtitle = sheet.createRow(0); //创建第一行（title）
rowtitle.setHeight((short) (6 * 100));//设置高度
//为标题创建单元格
Cell titleCell = rowtitle.createCell(0);
titleCell.setCellValue(title);
titleCell.setCellStyle(titleStyle);
//创建小标题行
CellRangeAddress item;
for (int i = 0; i < miniTitles.length; i++) {
//在sheet里增加合并单元格
item= new CellRangeAddress(i+1, i+1, 0, headers.length - 1);
sheet.addMergedRegion(item);
Row minitTitle = sheet.createRow(i + 1);//创建行（title）
minitTitle.setHeight((short) (4 * 100));//设置高度
//为标题创建单元格
Cell miniCell = minitTitle.createCell(0);
miniCell.setCellValue(miniTitles[i]);
miniCell.setCellStyle(miniTitleStyle);
}
// 产生表格标题行
XSSFRow row = sheet.createRow(miniTitles.length+1);
XSSFCell cellHeader;
for (int i = 0; i < headers.length; i++) {
cellHeader = row.createCell(i);
cellHeader.setCellStyle(headStyle);
cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = miniTitles.length+1;
T t;
XSSFRichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
String getMethodName;
XSSFCell cell;
Class tCls;
Method getMethod;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
int num=0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
t = (T) it.next();
// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (int i = 0; i < fields.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
fieldName = fields[i];
if(i==0){
cell.setCellValue(num++);
}
if (StringUtils.isNotBlank(fieldName)) {
getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
tCls = t.getClass();
getMethod = tCls.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
}
//创建小标题行
CellRangeAddress endItem;
for (int i = 0; i < annotation.length; i++) {
//在sheet里增加合并单元格
int rowIndex=index+i+1;
endItem= new CellRangeAddress(rowIndex, rowIndex, 0, headers.length - 1);
sheet.addMergedRegion(endItem);
Row minitTitle = sheet.createRow(rowIndex);//创建行（title）
minitTitle.setHeight((short) (4 * 100));//设置高度
//为标题创建单元格
Cell miniCell = minitTitle.createCell(0);
miniCell.setCellValue(annotation[i]);
miniCell.setCellStyle(annotationStyle);
}
//设置A4横向
XSSFPrintSetup printSetup = sheet.getPrintSetup();
printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
printSetup.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)
//设置列宽和边距
setSheetStyle(sheet);
//获取配置文件中保存对应excel文件的路径，本地也可以直接写成F：excel/stuInfoExcel路径
// String folderPath = "d:/excel/stuInfoExcel";
//创建上传文件目录
File folder = new File(folderPath);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
String savePath = folderPath + File.separator + fileName;
OutputStream fileOut = new FileOutputStream(savePath);
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
System.err.println(e.getMessage());
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 设置页边距
* @param sheet
*/
private static void setSheetStyle(Sheet sheet){
sheet.setMargin(XSSFSheet.BottomMargin,(double ) 0.5 );// 页边距（下）
sheet.setMargin(XSSFSheet.LeftMargin,(double ) 0 );// 页边距（左）
sheet.setMargin(XSSFSheet.RightMargin,(double ) 0 );// 页边距（右）
sheet.setMargin(XSSFSheet.TopMargin,(double ) 0.5 );// 页边距（上）
sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
}
/**
* title表头样式
*
* @param workbook
* @return
*/
private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);//字体剧中
HSSFFont titleFont = workbook.createFont();//字体设置
titleFont.setBold(true);//粗体显示
titleFont.setFontHeightInPoints((short) 15);
titleStyle.setFont(titleFont);//选择需要用到的字体格式
return titleStyle;
}
/**
* 获取小标题数据
* @param workbook
* @return
*/
private static HSSFCellStyle getMiniTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);//字体剧中
HSSFFont titleFont = workbook.createFont();//字体设置
titleFont.setBold(true);//粗体显示
titleFont.setFontHeightInPoints((short) 15);
titleStyle.setFont(titleFont);//选择需要用到的字体格式
return titleStyle;
}
/**
* title表头样式
*
* @param workbook
* @return
*/
private static XSSFCellStyle getTitleStyle(XSSFWorkbook workbook) {
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);//字体剧中
XSSFFont titleFont = workbook.createFont();//字体设置
titleFont.setBold(true);//粗体显示
titleFont.setFontHeightInPoints((short) 15);
titleStyle.setFont(titleFont);//选择需要用到的字体格式
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
titleStyle.setWrapText(true);//设置自动换行
return titleStyle;
}
/**
* title表头样式
*
* @param workbook
* @return
*/
private static XSSFCellStyle getMiniTitleStyle(XSSFWorkbook workbook) {
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);//字体剧中
XSSFFont titleFont = workbook.createFont();//字体设置
titleFont.setFontHeightInPoints((short) 15);
titleStyle.setFont(titleFont);//选择需要用到的字体格式
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
titleStyle.setWrapText(true);//设置自动换行
return titleStyle;
}
/**
* 设置表头样式
*
* @param workbook
* @return
*/
public static XSSFCellStyle getHeadStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();//单元格样式
// 设置这些样式
style.setFillForegroundColor(new XSSFColor(Color.WHITE));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
style.setWrapText(true);//设置自动换行
// 生成一个字体
XSSFFont font = workbook.createFont();//表头字体
font.setBold(true);
font.setFontName("宋体");
font.setColor(new XSSFColor(java.awt.Color.BLACK));
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 设置单元格样式
*
* @param workbook
* @return
*/
public static XSSFCellStyle getCellStyle(XSSFWorkbook workbook) {
// 生成并设置另一个样式
XSSFCellStyle cellStyle = workbook.createCellStyle();//单元格样式
cellStyle.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);//设置自动换行
// 生成另一个字体
XSSFFont cellFont = workbook.createFont();//单元格字体
cellFont.setBold(false);
// 把字体应用到当前的样式
cellStyle.setFont(cellFont);
return cellStyle;
}
/**
* 设置尾部数据样式
* @param workbook
* @return
*/
public static XSSFCellStyle getAnnotationStyle(XSSFWorkbook workbook) {
// 生成并设置另一个样式
XSSFCellStyle cellStyle = workbook.createCellStyle();//单元格样式
cellStyle.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);//设置自动换行
// 生成另一个字体
XSSFFont cellFont = workbook.createFont();//单元格字体
cellFont.setBold(false);
// 把字体应用到当前的样式
cellStyle.setFont(cellFont);
return cellStyle;
}
/**
* 设置表头样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
// 生成一个样式
HSSFCellStyle headStyle = workbook.createCellStyle();
// 设置这些样式
headStyle.setFillForegroundColor(HSSFColor.BLUE.index);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setBorderBottom(BorderStyle.THIN);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setColor(HSSFColor.WHITE.index);
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
headStyle.setFont(font);
return headStyle;
}
/**
* 设置单元格样式
*
* @param workbook
* @return
*/
public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
// 生成并设置另一个样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);//设置自动换行
// 生成另一个字体
HSSFFont cellFont = workbook.createFont();
cellFont.setBold(false);
// 把字体应用到当前的样式
cellStyle.setFont(cellFont);
return cellStyle;
}
/**
* 导出课程模板
* @param response
* @param title 标题
* @param columns 列名
* @param fields 属性值
* @param map 下拉
*/
public void exportCourseExcel(HttpServletResponse response, String title, String[] columns, String[] fields, Map<String,String[]> map) {
try {
//声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(20);
// 生成标题行样式
XSSFCellStyle headStyle = workbook.createCellStyle();
XSSFFont headFont = workbook.createFont();// 生成字体
headFont.setFontName("宋体");//字体
headFont.setFontHeightInPoints((short) 22);// 字体大小
headFont.setColor(new XSSFColor(java.awt.Color.BLACK));//颜色
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headStyle.setFont(headFont);// 把字体应用到当前的样式
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 列名样式
XSSFCellStyle columnStyle = workbook.createCellStyle();
XSSFFont columnFont = workbook.createFont();
columnFont.setFontName("宋体");
columnFont.setFontHeightInPoints((short) 12);
columnStyle.setFont(columnFont);
columnStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
columnStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 生成普通单元格样式
XSSFCellStyle unitStyle = workbook.createCellStyle();
XSSFFont unitFont = workbook.createFont();
unitFont.setFontName("宋体");
unitFont.setFontHeightInPoints((short) 12);
unitStyle.setFont(unitFont);
unitStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
unitStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 第一行表头列名
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.length - 1));//合并表头
XSSFRow row = sheet.createRow(0);//通用行
row.setHeight((short) 0x349);
XSSFCell cell = row.createCell(0);//通用列
cell.setCellStyle(headStyle);
cell.setCellValue(title);
// 第二行列名
row = sheet.createRow(1);
for (int i = 0; i < columns.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(columnStyle);
cell.setCellValue(columns[i]);
}
for (int i = 0; i < fields.length; i++) {
String fieldName = fields[i];
//特殊情况下拉选项
Set<Map.Entry<String,String[]>> entrys = map.entrySet();
for (Map.Entry<String,String[]> entry : entrys) {
if (fieldName.equals(entry.getKey())) {
//255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
sheet.addValidationData(setDataValidation(sheet, entry.getValue(), 2,502 , i, i)); //超过255个报错
}
}
}
String fileName = new String(title.getBytes("gb2312"), "ISO8859-1");
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}catch (Exception e){
System.err.println(e.getMessage());
e.printStackTrace();
}
}
private DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
//加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
constraint.setExplicitListValues(textList);
//设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
//数据有效性对象
DataValidation data_validation = helper.createValidation(constraint, regions);
return data_validation;
}
}
JAVA POI导出excel工具类
最新推荐文章于 2024-02-22 00:00:11 发布
3810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
