Java使用POI导出Excel工具类
自行导入poi3.9的jar包
工具类：
package com.cpic.caf.template.home.util;
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;
/**
*
* @author librag
* @version v1.0
* @param <T>
* 应用泛型，代表任意一个符合javabean风格的类
* 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
* byte[]表jpg格式的图片数据
*/
public class ExportExcel<T>{
public static final String Map = "Map";
public static final String List = "List";
private int List_Size = 0;
/*
public ExportExcel(int List_Size) {
this.List_Size = List_Size;
}
public ExportExcel() {
}*/
/**
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param dataset 允许的类型(实体类,List<List<Object>>,List<Map<String,Object>>)
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out
* 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern
* 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
Controller示例
//引用浏览器引擎下载
//使用本工具类创建对象
ExportExcel<对象> ex = new ExportExcel<对象>();
//创建列
String[] headers =
{ "主键", "从业者ID", "案例是作品集还是婚礼案例", "案例类型", "标题","案例地址","描述","置顶","删除状态","访问量","收藏量"};
//创建值列
List<对象> dataset = Service.方法名();
//调用工具类对象
ex.exportExcel(headers,dataset,response);
//提示
System.out.println("excel导出成功！");
Controller示例2
//直接下载到本地磁盘
//使用本工具类创建对象
ExportExcel<Map<String, Object>> ex = new ExportExcel<对象>();
//创建列
String[] headers =
{ "主键", "从业者ID", "案例是作品集还是婚礼案例", "案例类型", "标题","案例地址","描述","置顶","删除状态","访问量","收藏量"};
//创建值列
List<Map<String, Object>> dataset = Service.方法名();
//调用工具类对象
ex.exportExcel(headers,dataset,"E:\\文件夹名\\文件夹名");
//提示
System.out.println("excel导出成功！");
*/
/**
* 方法重载同时指向一个unchecked
* @param dataset Value列
* @param response 必须
* @param path (当传入指定path时 方法会使用IO流下载到指定地址(文件夹) || 反之 会引用浏览器的下载引擎)
*/
public void exportExcel(Collection<T> dataset,String path){
exportExcel("EXCEL文档", null, dataset, "yyyy-MM-dd",path);
}
public void exportExcel(String[] headers, Collection<T> dataset,String path) {
exportExcel("EXCEL文档", headers, dataset, "yyyy-MM-dd",path);
}
public void exportExcel(String[] headers, Collection<T> dataset, String pattern,String path){
exportExcel("EXCEL文档", headers, dataset, pattern,path);
}
public void exportExcel(Collection<T> dataset,HttpServletResponse response){
exportExcel("EXCEL文档", null, dataset, "yyyy-MM-dd",response);
}
public void exportExcel(String[] headers, Collection<T> dataset,HttpServletResponse response) {
exportExcel("EXCEL文档", headers, dataset, "yyyy-MM-dd",response);
}
public void exportExcel(String[] headers, Collection<T> dataset, String pattern,HttpServletResponse response){
exportExcel("EXCEL文档", headers, dataset, pattern,response);
}
public void exportExcel(String[] headers, List<List<Object>> dataset,HttpServletResponse response) {
exportExcel("EXCEL文档", headers, dataset, "yyyy-MM-dd",response);
}
public void MapExportExcel(String[] headers, List<Map<String, Object>> dataset,HttpServletResponse response) {
exportExcel("EXCEL文档", headers, dataset, "yyyy-MM-dd",response,"Map");
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers,Collection<T> dataset, String pattern,String path){
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try
{
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext())
{
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[]
{});
Object value = getMethod.invoke(t, new Object[]
{});
// 判断值的类型后进行强制类型转换
String textValue = null;
/* if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
textValue = new HSSFRichTextString(
String.valueOf(fValue));
cell.setCellValue(textValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
textValue = new HSSFRichTextString(
String.valueOf(dValue));
cell.setCellValue(textValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
}*/
if (value instanceof Boolean)
{
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue)
{
textValue = "女";
}
}
else if (value instanceof Date)
{
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
}
else if (value instanceof byte[])
{
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(
bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
else
{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}
else
{
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
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{
// 清理资源
}
//生成文件流
IOExcle(workbook,path);
}
/**
* (网络传输)
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers,Collection<T> dataset, String pattern,HttpServletResponse response){
response.reset(); // 清除buffer缓存
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=contacts" + getDateTimeName() + ".xls");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try
{
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()){
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[]
{});
Object value = getMethod.invoke(t, new Object[]
{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Boolean)
{
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue)
{
textValue = "女";
}
}
else if (value instanceof Date)
{
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
}
else if (value instanceof byte[])
{
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(
bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
else
{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}
else
{
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
}//while
}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{
// 清理资源
}
//生成文件流
HTTPExcle(workbook,response);
}
/**
* (网络传输)集合
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers,List<List<Object>> dataset, String pattern,HttpServletResponse response){
response.reset(); // 清除buffer缓存
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=contacts" + getDateTimeName() + ".xls");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try {
for (int i = 0; i < dataset.size(); i++) {
//创建外层集合行
HSSFRow createRow = sheet.createRow(i+1);
for (int j = 0; j < dataset.get(i).size(); j++) {
createRow.createCell(j).setCellValue(dataset.get(i).get(j).toString());;
}
}
}catch (Exception e){
e.printStackTrace();
}finally{
// 清理资源
}
//生成文件流
HTTPExcle(workbook,response);
}
/**
* (网络传输)集合Map
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers,List<Map<String, Object>> dataset, String pattern,HttpServletResponse response,String name){
response.reset(); // 清除buffer缓存
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=contacts" + getDateTimeName() + ".xls");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try {
dataset = nullToEmpty(dataset);
for (int i = 0; i < dataset.size(); i++) {
//创建外层集合行
HSSFRow createRow = sheet.createRow((i+1));
//第一種
Set<String> set = dataset.get(i).keySet(); //取出所有的key值
Iterator<String> it = set.iterator();
int j = 0;
while(it.hasNext()) {
/* String str = null;
if(it.next()!=null&&dataset.get(i)!=null&&dataset.get(i).get(it.next())!=null) {
System.out.println("----------------------------");
System.out.println(it.next());
System.out.println(dataset.get(i).get(it.next()));
str = dataset.get(i).get(it.next()).toString();
}*/
Object value = dataset.get(i).get(it.next());
String textValue = null;
if (value instanceof Boolean){
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue)
{
textValue = "女";
}
}else if (value instanceof Date){
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
}else if (value instanceof byte[]){
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
1023, 255, (short) 6, (i+1), (short) 6, (i+1));
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(
bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
}else{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
/*if(str!=null) {
}else {
createRow.createCell(j).setCellValue("");//取出元素
}
*/
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
createRow.createCell(j).setCellValue(Double.parseDouble(textValue));//取出元素
}
else
{
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLACK.index);
richString.applyFont(font3);
createRow.createCell(j).setCellValue(richString);
}
}
j++;
}//判断是否有下一个
}
}catch (Exception e){
e.printStackTrace();
}finally{
// 清理资源
}
//生成文件流
HTTPExcle(workbook,response);
}
/************************************************************(工具方法)***************************************************************/
public static void IOExcle(HSSFWorkbook workbook,String path) {
OutputStream out = null;
try {
out = new FileOutputStream(path.replaceAll("\\\\", "/")+getDateTimeName()+".xls");
workbook.write(out);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
};
}
public static void HTTPExcle(HSSFWorkbook workbook,HttpServletResponse response) {
OutputStream output;
try {
output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workbook.write(bufferedOutPut);
bufferedOutPut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getDateTimeName() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String format = sdf.format(new Date());
return format;
}
/**
* <p>方法名：nullToEmpty</p>
* <p>功能描述：map的value如果是null处理成空字符串</p>
*/
public static List<Map<String, Object>> nullToEmpty(List<Map<String, Object>> list) {
if(list !=null && !list.isEmpty()) {
for(Map<String, Object> map : list) {
Set<String> set = map.keySet();
if(set != null && !set.isEmpty()) {
for(String key : set) {
if(map.get(key) == null) {
map.put(key, "");
}
}
}
}
}
return list;
}
}
使用
package com.cpic.caf.template.home.controller;
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigInteger;
import java.net.URLDecoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.cpic.caf.template.home.business.ProtocolAppoint;
import com.cpic.caf.template.home.entity.OrderEO;
import com.cpic.caf.template.home.entity.ProtocolParam;
import com.cpic.caf.template.home.service.OrderService;
import com.cpic.caf.template.home.util.CommonFunction;
import com.cpic.caf.template.home.util.ExportExcel;
import com.cpic.caf.template.home.util.HomeSQLUtil;
import com.cpic.caf.template.home.util.OrderDSQLUtil;
import com.cpic.caf.workers.hall.entity.WorkUserEO;
import com.cpic.caf.workers.hall.entity.WorkersCaseuserEO;
import com.cpic.caf.workers.hall.service.OmnipotentSqlService;
import com.cpic.caf.workers.hall.service.WorkersCaseuserService;
/**
* 订单
*
* Title: 喜事码头 Description: 例子 Copyright: Copyright (c) 2018
* Company:河南意莱享网络科技有限公司
*
* @author changshenghui
* @version 1.1
* @Date 2018年6月12日
*/
@Controller
public class OrderController {
Logger logger = LoggerFactory.getLogger(OrderController.class);
@Autowired
OrderService orderService;
@Autowired
private OmnipotentSqlService omnipotent;
@Autowired
private WorkersCaseuserService WorkUserEO;
/**
* 获得订单信息列表（导出）
*
* @param model
* @param request
* @param pageRequest
* @return
*/
@RequestMapping(value = "/getEclise", method = RequestMethod.GET, produces = "application/json")
@ResponseBody
public void getOrderInfoList(Model model,HttpServletRequest request,HttpSession session,HttpServletResponse response) {
ExportExcel<Map<String, Object>> ex = new ExportExcel<Map<String, Object>>();
//创建列
String[] headers =
{ "主键", "从业者ID", "案例是作品集还是婚礼案例", "案例类型", "标题","案例地址","描述","置顶","删除状态","访问量","收藏量",
"排序","剩余可上传数","婚礼发生时间","创建时间","修改时间"};
//创建值列
List<Map<String, Object>> dataset = omnipotent.findsqlByParam("SELECT * FROM xsmt.ylx_workers_wuorder");
ex.MapExportExcel(headers,dataset,response);
System.out.println("excel导出成功！");
}
/**
* 获得订单信息列表
*
* @param model
* @param request
* @param pageRequest
* @return
*/
@RequestMapping(value = "/getOrderInfoList", method = RequestMethod.GET, produces = "application/json")
@ResponseBody
public Map<String, Object> getOrderInfoList(Model model,
HttpServletRequest request,
HttpSession session,
@RequestParam(value="PageIndex",required=false)Integer PageIndex,
@RequestParam(value="paramIndex",required=false)Integer paramIndex,
@RequestParam(value="start",required=false)Integer start,
@RequestParam(value="NO",required=false)String NO,
@RequestParam(value="DateParam",required=false)String DateParam,
@RequestParam(value="BIZ",required=false)String BIZ
) {
try {
WorkUserEO attribute =(WorkUserEO) session.getAttribute("worker");//获取登录对象
Integer WU_id = 1;
String NO_ = "";
String SQL = null;
String SQLCount = null;
if(attribute == null) {
System.out.println("- 登录对象为空 -");
}else {
WU_id = attribute.getId();
}
if(NO!=null) {
NO_ = NO;
}
logger.info("================================="+DateParam+"= 订单查询CYZid:"+WU_id+" into getOrderInfoList ==========================================");
Map<String, Object> map = new HashMap<String, Object>();
switch (paramIndex) {
case 1:
//查询全部订单
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,null,null,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,null,null,null,NO_,DateParam,BIZ);
System.out.println("查询全部订单:"+SQL);
break;
case 2:
//查询已支付
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,1,null,null,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,1,null,null,null,NO_,DateParam,BIZ);
System.out.println("查询已支付:"+SQL);
break;
case 3:
//查询待处理
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,5,null,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,5,null,null,NO_,DateParam,BIZ);
System.out.println("查询待处理:"+SQL);
break;
case 4:
//查询待已付款
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,null,1,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,null,1,null,NO_,DateParam,BIZ);
System.out.println("查询待已付款:"+SQL);
break;
case 5:
//查询已完成
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,6,null,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,6,null,null,NO_,DateParam,BIZ);
System.out.println("查询已完成:"+SQL);
break;
case 6:
//查询已取消
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,null,null,1,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,null,null,1,NO_,DateParam,BIZ);
System.out.println("查询已取消:"+SQL);
break;
default:
//查询全部订单
SQL = OrderDSQLUtil.getOrderListByParams(PageIndex,WU_id,null,null,null,null,NO_,DateParam,BIZ);
SQLCount = OrderDSQLUtil.getOrderListByCount(PageIndex,WU_id,null,null,null,null,NO_,DateParam,BIZ);
System.out.println("查询全部订单:"+SQL);
break;
}
// 查询数据
List<Map<String, Object>> findsqlByParam = omnipotent.findsqlByParam(SQL);
if(start !=null){
// 总条数
List<Map<String, Object>> findsqlByCount = omnipotent.findsqlByParam(SQLCount);
map.put("totalCount", findsqlByCount.get(0).get("count"));
}
map.put("resultList", findsqlByParam);
return map;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 增加订单信息
*
* @param model
* @param request
* @param pageRequest
* @return
*/
@RequestMapping(value = "/jsonAddOrderInfo", method = RequestMethod.POST)
@ResponseBody
public ProtocolParam addOrderInfo(Model model, HttpServletRequest request,
@RequestParam Map<String, Object> pageRequest) {
logger.info("======================================== JsonAddOrderInfo==================================");
// 获得要添加的字段
try {
String no = URLDecoder.decode((String) pageRequest.get("no"),
"utf-8");// 订单编号
String cust_name = URLDecoder.decode(
(String) pageRequest.get("custname"), "utf-8");// 客户姓名
// 创建实体对象
OrderEO order = new OrderEO();
// 订单编号
if (no != null && !"".equals(no)) {
order.setNo(CommonFunction.getRandomOrderNo());
}
// 客户名称
if (cust_name != null && !"".equals(cust_name)) {
order.setCust_name(cust_name);
}
// 生成流水号
order.setTrade_no(CommonFunction.GetNewOrderNo(no));
// 服务名称
order.setService_name("中式");
order.setCreation_time(new Date());
// 调用方法
String str = HomeSQLUtil.homeOrderAddSQL(order);
int count = orderService.add(str);
// orderService.addOrderInfo(order);
if (count > 0) {
return ProtocolAppoint.succeed("新增成功", null, null);
}
return null;
} catch (UnsupportedEncodingException e) {
return ProtocolAppoint.error("新增失败", null, null);
}
}
/**
* 删除
*
* @param pageRequest
* @return
*/
@RequestMapping(value = "/JsonDelOrderInfo", method = RequestMethod.POST)
@ResponseBody
public ProtocolParam delOrderInfo(
@RequestParam Map<String, Object> pageRequest) {
logger.info("=============================== JsonDelOrderInfo ===============================");
try {
int id = Integer.parseInt(URLDecoder.decode(
(String) pageRequest.get("id"), "utf-8"));
String sql = HomeSQLUtil.homeDelOrderInfo(id);
int count = orderService.delete(sql);
if (count > 0) {
return ProtocolAppoint.merry(200, "成功", null, null);
}
return null;
} catch (Exception e) {
return ProtocolAppoint.error("失败", null, null);
}
}
@RequestMapping(value="/ModifyOrderInfo",method=RequestMethod.POST)
@ResponseBody
public ProtocolParam modifyOrderInfo(@RequestParam Map<String, Object> pageRequest){
logger.info("============================= ModifyOrderInfo =========================================");
try {
int id=Integer.parseInt(URLDecoder.decode(
(String) pageRequest.get("id"), "utf-8"));
OrderEO order=new OrderEO();
order.setNo("NO201806201543");
order.setCust_name("青青");
order.setId(BigInteger.valueOf(id));
String sql=HomeSQLUtil.homeModifyOrderInfo(order);
int count =orderService.update(sql);
if(count>0){
return ProtocolAppoint.merry(200, "成功", null, null);
}
return null;
} catch (Exception e) {
return ProtocolAppoint.error("失败", null, null);
}
}
}
