直入主题，上代码：
package com.alfri.poi;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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;
import com.esotericsoftware.reflectasm.MethodAccess;
/**
*
*
* Apache POI操作Excel对象
* HSSF：操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
* XSSF：操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF
* SXSSF:从POI3.8 beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
*
* 注意:
* 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
*
* 说明:
* .xls格式的excel(最大行数65536行,最大列数256列)
* .xlsx格式的excel(最大行数1048576行,最大列数16384列)
*/
public class PoiExcleUtils {
public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽
/**
*
* @Description: 批量导出
* @author: YL
* @param <T>
* @date: 2019-4-2上午11:35:18
* @param filename 输出路径
* @param headerlist 列表标题
* @param datalist 数据集合
*/
public static <T> void exportExcel(String filename ,List<HeaderDomain> headerlist, List<T> datalist) {
if(datalist!=null&&datalist.size()>0){
int minBytes = DEFAULT_COLUMN_WIDTH; //默认列宽度
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
// head样式 (第一行列标题)
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置颜色 白色
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
Font headerFont = workbook.createFont();//字体
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
/**
* 生成一个表格
*/
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
sheet.createFreezePane(0, 1, 0, 1);// (单独)冻结前一行
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[headerlist.size()];// 列宽数组 列个数
String[] headCode = new String[headerlist.size()];// headCode数组
String[] headName = new String[headerlist.size()];// headName数组
int i = 0;
for (HeaderDomain header : headerlist) {
//String index = header.getIndex();
headCode[i] = header.getCode();
headName[i] = header.getName();
int bytes = headCode[i].getBytes().length;
colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
i++;
}
/**
* 遍历数据集合，产生Excel行数据
*/
Class<?> cls = datalist.get(0).getClass();
MethodAccess methodaccess = MethodAccess.get(cls);
File file = new File(filename);
if (!file.exists()) {
file.getParentFile().mkdirs();
}
int rowIndex = 0;
for (Object obj : datalist) {
// 生成head信息(列名称)
if (rowIndex == 0) {
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);// head行 第一行
for (int j = 0; j < headName.length; j++) {
headerRow.createCell(j).setCellValue(headName[j]);//赋值
headerRow.getCell(j).setCellStyle(headerStyle); //设置样式
}
rowIndex = 1;
}
// 生成数据
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
for (int k = 0; k < headCode.length; k++) {
String getmethodname = "get"+ headCode[k].replaceFirst(headCode[k].substring(0, 1), headCode[k].substring(0, 1).toUpperCase());
Object o = methodaccess.invoke(obj, getmethodname,new Object[0]);
String cellValue = "";
if (o == null) {
cellValue = "";
} else {
cellValue = o.toString();
}
SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
}
rowIndex++;
}
BufferedOutputStream bos = null;
try {
bos = new BufferedOutputStream(new FileOutputStream(filename));
workbook.write(bos);
bos.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
} catch (IOException e) {
System.err.println("----excle输出流输出异常----");
}finally{
try {
if (bos != null) {
bos.close();// 关闭流
}
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
System.err.println("----excle输出流关闭异常----");
}
}
}else{
SXSSFWorkbook workbook = new SXSSFWorkbook();
BufferedOutputStream bos = null;
try {
bos = new BufferedOutputStream(new FileOutputStream(filename));
workbook.write(bos);
bos.flush();
} catch ( IOException e) {
System.err.println("----输出流输出异常----");
}finally{
try {
if (bos != null) {
bos.close();// 关闭流
}
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (Exception e) {
System.err.println("----输出流关闭异常----");
}
}
}
}
public static void main(String[] args) {
//准备数据
List<Student> list = new ArrayList<Student>();
for (int i = 0; i < 10; i++) {
list.add(new Student("张三"+i, "2"+i, i%2==0?"男":"女"));
}
System.err.println("---数据准备完成---");
//准备列标题
List<HeaderDomain> headerlist = new ArrayList<HeaderDomain>();
headerlist.add(new HeaderDomain("0", "name", "姓名"));
headerlist.add(new HeaderDomain("1", "age", "年龄"));
headerlist.add(new HeaderDomain("2", "sex", "性别"));
//导出路径
Date now = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
String datefile = dateFormat.format(now);
String filepath="D:"+ File.separator + "download" + File.separator + datefile;
String filename= now.getTime()+ ".xlsx";
File file = new File(filepath);
if(!file.exists()){
file.mkdirs();
}
System.err.println("---导出文件位于："+filepath + File.separator + filename);
exportExcel(filepath + File.separator + filename, headerlist, list);
System.err.println("---导出完成---");
}
}
/**
*
* @author 81937
* 列标题类
*/
class HeaderDomain {
private String index;
private String code;
private String name;
public HeaderDomain() {
}
public HeaderDomain(String index, String code, String name) {
this.setIndex(index);
this.code = code;
this.name = name;
}
public String getCode() {
return this.code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public String getIndex() {
return this.index;
}
public void setIndex(String index) {
this.index = index;
}
}
/**
*
* @author 81937
* 数据类
*/
class Student{
private String name;
private String age;
private String sex;
public Student() {
super();
}
public Student(String name, String age, String sex) {
super();
this.name = name;
this.age = age;
this.sex = sex;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}需要引入下列的jar包
扫一扫
2846
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
