关于poi的导出（POI）

快来打我* 2021-11-02 11:24 288阅读 0赞

很简单 我封装好的poi导出类 可以拿来直接用

package com.xst.common;

import java.io.IOException;  
import java.io.OutputStream;  
import java.lang.reflect.Field;  
import java.lang.reflect.Method;  
import java.text.SimpleDateFormat;  
import java.util.Collection;  
import java.util.Date;  
import java.util.Iterator;  
import java.util.regex.Matcher;  
import java.util.regex.Pattern;  
import org.apache.poi.ss.usermodel.CellStyle;  
import org.apache.poi.ss.usermodel.Font;  
import org.apache.poi.ss.usermodel.IndexedColors;  
import org.apache.poi.xssf.streaming.SXSSFCell;  
import org.apache.poi.xssf.streaming.SXSSFRow;  
import org.apache.poi.xssf.streaming.SXSSFSheet;  
import org.apache.poi.xssf.streaming.SXSSFWorkbook;  
  
public class newExcel <T,J>\{  
public static final String FILE\_SEPARATOR = System.getProperties().getProperty("file.separator");  
public void exportExcel(String title, String\[\] headers,Collection<?> dataList, Object sumData,OutputStream out, String pattern,int\[\] width) throws Exception\{  
exportSumMoneyExcel(title, headers, dataList,sumData,out,pattern,width);  
\}  
  
@SuppressWarnings("unchecked")  
private void exportSumMoneyExcel(String title, String\[\] headers,Collection<?> dataList, Object sumData,OutputStream out, String pattern,int\[\] width) throws Exception\{  
try \{  
//声明一个工作薄  
SXSSFWorkbook workbook=new SXSSFWorkbook(1000);  
//生成表格  
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title);  
  
for(int i = 0;i < width.length;i++)\{  
sheet.setColumnWidth(i,width\[i\]\*256+184);  
\}  
  
// 1.标题的样式  
CellStyle style = workbook.createCellStyle();  
//标题背景色为灰色  
//style.setFillForegroundColor(HSSFColor.GREY\_25\_PERCENT.index);  
style.setFillForegroundColor(IndexedColors.GREY\_25\_PERCENT.index);  
//前置背景色  
style.setFillPattern(CellStyle.SOLID\_FOREGROUND);  
//下边框样式  
style.setBorderBottom(CellStyle.BORDER\_THIN);  
//左边框样式  
style.setBorderLeft(CellStyle.BORDER\_THIN);  
//右边框样式  
style.setBorderRight(CellStyle.BORDER\_THIN);  
//上边框样式  
style.setBorderTop(CellStyle.BORDER\_THIN);  
//设置单元格内容水平居中  
style.setAlignment(CellStyle.ALIGN\_CENTER);  
// 标题的字体样式  
Font font = workbook.createFont();//生成字体  
//设置字体颜色  
font.setColor(IndexedColors.BLACK.index); //设置字体颜色为黑色  
//设置字体大小  
font.setFontHeightInPoints((short) 12);  
//font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);  
//设置字体加粗  
font.setBoldweight(Font.BOLDWEIGHT\_BOLD);  
// 把字体应用到当前的样式  
style.setFont(font);  
  
// 内容样式  
CellStyle style2 = workbook.createCellStyle();  
//内容背景色为黄色  
style2.setFillForegroundColor(IndexedColors.LIGHT\_YELLOW.index);  
style2.setFillPattern(CellStyle.SOLID\_FOREGROUND);  
style2.setBorderBottom(CellStyle.BORDER\_THIN);  
style2.setBorderLeft(CellStyle.BORDER\_THIN);  
style2.setBorderRight(CellStyle.BORDER\_THIN);  
style2.setBorderTop(CellStyle.BORDER\_THIN);  
style2.setAlignment(CellStyle.ALIGN\_CENTER);  
style2.setVerticalAlignment(CellStyle.VERTICAL\_CENTER);  
// 内容字体样式  
Font font2 = workbook.createFont();  
font2.setColor(IndexedColors.BLACK.index); //设置数据字体为黑色  
font2.setBoldweight(Font.BOLDWEIGHT\_NORMAL);  
// 把字体应用到当前的样式  
style2.setFont(font2);  
// 合计的样式  
CellStyle style3 = workbook.createCellStyle();  
//内容背景色为白色  
style3.setFillForegroundColor(IndexedColors.WHITE.index);  
style3.setFillPattern(CellStyle.SOLID\_FOREGROUND);  
style3.setBorderBottom(CellStyle.BORDER\_THIN);  
style3.setBorderLeft(CellStyle.BORDER\_THIN);  
style3.setBorderRight(CellStyle.BORDER\_THIN);  
style3.setBorderTop(CellStyle.BORDER\_THIN);  
style3.setAlignment(CellStyle.ALIGN\_CENTER);  
style3.setVerticalAlignment(CellStyle.VERTICAL\_CENTER);  
// 内容字体样式  
Font font3 = workbook.createFont();  
font3.setColor(IndexedColors.BLUE.index); //设置数据字体为绿色  
font3.setBoldweight(Font.BOLDWEIGHT\_NORMAL);  
// 把字体应用到当前的样式  
style3.setFont(font3);  
// 产生表格标题行  
SXSSFRow row = (SXSSFRow) sheet.createRow(0);  
//设置行高  
row.setHeight((short)20);  
//行高的像素数  
row.setHeightInPoints(20);  
for (short i = 0; i < headers.length; i++) \{  
SXSSFCell cell=(SXSSFCell) row.createCell(i);  
cell.setCellStyle(style);  
cell.setCellValue(headers\[i\]);  
\}  
// 遍历集合数据，产生数据行  
Iterator<?> it = dataList.iterator();  
int index = 0;  
while (it.hasNext()) \{  
index++;  
row = (SXSSFRow) sheet.createRow(index);  
T t = (T) it.next();  
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
Field\[\] fields = t.getClass().getDeclaredFields();  
for (short i = 0; i < fields.length; i++) \{  
SXSSFCell cell=(SXSSFCell) 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);  
try \{  
Class tCls = t.getClass();  
Method getMethod = tCls.getMethod(getMethodName,  
new Class\[\] \{\});  
Object value = getMethod.invoke(t, new Object\[\] \{\});  
if(value==null)\{  
value=" ";  
\}  
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+)?$");  
Pattern p = Pattern.compile("^(-?\\\\d+)(\\\\.\\\\d+)?$");  
Matcher matcher = p.matcher(textValue);  
if (matcher.matches() && textValue.length()<12) \{  
// 是数字当作double处理  
cell.setCellValue(Double.parseDouble(textValue));  
\} else \{  
cell.setCellValue(textValue);  
\}  
\}  
  
\}catch (Exception e) \{  
e.printStackTrace();  
\}finally \{  
// 清理资源  
\}  
\}//for  
\}//while  
if (sumData!=null) \{  
SXSSFSheet sheet2=(SXSSFSheet) workbook.getSheetAt(0); //获取到工作表，因为一个excel可能有多个工作表  
SXSSFRow row2=(SXSSFRow)sheet2.getRow(0); //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值  
row2=(SXSSFRow) sheet2.createRow((short)(sheet2.getLastRowNum()+1));  
SXSSFCell cell0=(SXSSFCell)row2.createCell(0);  
cell0.setCellStyle(style3);  
cell0.setCellValue("合计：");  
Field\[\] fields = sumData.getClass().getDeclaredFields();  
try \{  
for (short i = 0; i < fields.length; i++) \{  
SXSSFCell cell = (SXSSFCell)row2.createCell(i+1);  
cell.setCellStyle(style3);  
Field field = fields\[i\];  
String fieldName = field.getName();  
String getMethodName = "get"  
\+ fieldName.substring(0, 1).toUpperCase()  
\+ fieldName.substring(1);  
  
Class tCls = sumData.getClass();  
Method getMethod = tCls.getMethod(getMethodName,  
new Class\[\] \{\});  
Object value = getMethod.invoke(sumData, new Object\[\] \{\});  
if(value==null)\{  
value=" ";  
\}  
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+)?$");  
Pattern p = Pattern.compile("^(-?\\\\d+)(\\\\.\\\\d+)?$");  
Matcher matcher = p.matcher(textValue);  
if (matcher.matches() && textValue.length()<12) \{  
// 是数字当作double处理  
cell.setCellValue(Double.parseDouble(textValue));  
\} else \{  
cell.setCellValue(textValue);  
\}  
\}  
\}//for  
\} catch (Exception e) \{  
e.printStackTrace();  
\} finally \{  
// 清理资源  
\}  
\}//if  
workbook.write(out);  
\} catch (IOException e) \{  
e.printStackTrace();  
\}  
\}//方法  
\}//类

你们复制好直接用：接下来的代码就是你们每一个controller里面的代码了

Date date = new Date();  
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");  
String formatDate = sdf.format(date);  
String filename= new String((formatDate.replace("-", "") +" 销售物料管理.xls").getBytes(),"iso-8859-1");  
response.setContentType("application/octet-stream");  
response.setContentType("application/OCTET-STREAM;charset=UTF-8");  
response.setHeader("Content-Disposition", "attachment;filename="+filename);  
response.setContentType("application/msexcel");  
String\[\] headers = \{ "原料名称","价格(元)"\};  
int \[\] width=\{28,15\};  
String title = "销售物料管理";  
newExcel<Settb109ExtA,Settb109ExtA> excel = new newExcel<Settb109ExtA,Settb109ExtA>();  
OutputStream out = response.getOutputStream();  
excel.exportExcel(title, headers,settb109selectList, null, out, title, width);  
out.close();

中间的样式大家可以上官网上面进行查询和学习 今天我只分享了封装好的poi

转载于:https://www.cnblogs.com/490889763-qq/p/10873880.html