packagecom.wdy.store.utils;
importorg.apache.catalina.Group;
importorg.apache.catalina.Role;
importorg.apache.catalina.User;
importorg.apache.catalina.UserDatabase;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.hssf.util.HSSFColor;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.Font;
importorg.apache.poi.xssf.streaming.SXSSFCell;
importorg.apache.poi.xssf.streaming.SXSSFRow;
importorg.apache.poi.xssf.streaming.SXSSFSheet;
importorg.apache.poi.xssf.streaming.SXSSFWorkbook;
importorg.springframework.stereotype.Service;
importjava.io.*;
importjava.util.Iterator;
importjava.util.LinkedList;
importjava.util.List;
public classExcelUtils{
public static voidmain(String[] args) throwsIOException {
ExcelUtils e=newExcelUtils();
LinkedList users = newLinkedList<>();
users.add(newUser() {
@Override
publicString getFullName() {
return null;
}
@Override
public voidsetFullName(String s) {
}
@Override
publicIterator getGroups() {
return null;
}
@Override
publicString getPassword() {
return null;
}
@Override
public voidsetPassword(String s) {
}
@Override
publicIterator getRoles() {
return null;
}
@Override
publicUserDatabase getUserDatabase() {
return null;
}
@Override
publicString getUsername() {
return null;
}
@Override
public voidsetUsername(String s) {
}
@Override
public voidaddGroup(Group group) {
}
@Override
public voidaddRole(Role role) {
}
@Override
public booleanisInGroup(Group group) {
return false;
}
@Override
public booleanisInRole(Role role) {
return false;
}
@Override
public voidremoveGroup(Group group) {
}
@Override
public voidremoveGroups() {
}
@Override
public voidremoveRole(Role role) {
}
@Override
public voidremoveRoles() {
}
@Override
publicString getName() {
return null;
}
});
InputStream inputStream = e.export(users);
OutputStream outputStream = null;
// 打开目的输入流,不存在则会创建
outputStream = newFileOutputStream("d:\\out.xls");
// 将输入流is写入文件输出流fos中
intch = 0;
try{
while((ch = inputStream.read()) != -1) {
outputStream.write(ch);
}
} catch(IOException e1) {
e1.printStackTrace();
} finally{
//关闭输入流等(略)
outputStream.close();
inputStream.close();
}
}
public voiddoo(){
}
privateInputStream export(List list) {
ByteArrayOutputStream output = null;
InputStream inputStream1 = null;
SXSSFWorkbook wb = newSXSSFWorkbook(1000);// 保留1000条数据在内存中
SXSSFSheet sheet = wb.createSheet();
// 设置报表头样式
CellStyle header = headSytle(wb);// cell样式
CellStyle content = contentStyle(wb);// 报表体样式
// 每一列字段名
String[] strs = newString[] { "姓名", "性别", "年龄", "手机号", "地址","爱好"};
// 字段名所在表格的宽度
int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 };
// 设置表头样式
initTitleEX(sheet, header, strs, ints);
if(list != null&& list.size() > 0) {
for(inti = 0; i < list.size(); i++) {
User user = list.get(i);
SXSSFRow row = sheet.createRow(i + 1);
intj = 0;
SXSSFCell cell = row.createCell(j++);
cell.setCellValue(user.getName()); // 姓名
cell.setCellStyle(content);
cell = row.createCell(j++);
cell.setCellValue("1"); // 性别
cell.setCellStyle(content);
cell = row.createCell(j++);
cell.setCellValue("1"); // 年龄
cell.setCellStyle(content);
cell = row.createCell(j++);
cell.setCellValue("1"); // 手机号
cell.setCellStyle(content);
cell = row.createCell(j++);
cell.setCellValue("1"); // 地址
cell.setCellStyle(content);
cell = row.createCell(j++);
cell.setCellValue("1"); // 爱好
cell.setCellStyle(content);
}
}
try{
output = newByteArrayOutputStream();
wb.write(output);
inputStream1 = newByteArrayInputStream(output.toByteArray());
output.flush();
} catch(Exception e) {
e.printStackTrace();
} finally{
try{
if(output != null) {
output.close();
if(inputStream1 != null)
inputStream1.close();
}
} catch(IOException e) {
e.printStackTrace();
}
}
returninputStream1;
}
/**
* 设置报表头样式
* @param workbook
* @return
*/
public staticCellStyle headSytle(SXSSFWorkbook workbook){
// 设置style1的样式,此样式运用在第二行
CellStyle style1 = workbook.createCellStyle();// cell样式
// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font1 = workbook.createFont();// 创建一个字体对象
font1.setBoldweight((short) 10);// 设置字体的宽度
font1.setFontHeightInPoints((short) 10);// 设置字体的高度
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
style1.setFont(font1);// 设置style1的字体
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
returnstyle1;
}
/**
* 设置报表体样式
* @param wb
* @return
*/
public staticCellStyle contentStyle(SXSSFWorkbook wb){
// 设置style1的样式,此样式运用在第二行
CellStyle style1 = wb.createCellStyle();// cell样式
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
returnstyle1;
}
/**
* 设置报表标题样式
* @param workbook
* @return
*/
public staticHSSFCellStyle titleSytle(HSSFWorkbook workbook, shortcolor, shortfontSize){
// 设置style1的样式,此样式运用在第二行
HSSFCellStyle style1 = workbook.createCellStyle();// cell样式
// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
//style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
//short fcolor = color;
if(color != HSSFColor.WHITE.index){
style1.setFillForegroundColor(color);// 设置填充色
}
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font1 = workbook.createFont();// 创建一个字体对象
font1.setBoldweight(fontSize);// 设置字体的宽度
font1.setFontHeightInPoints(fontSize);// 设置字体的高度
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
style1.setFont(font1);// 设置style1的字体
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
returnstyle1;
}
/**
*设置表头
* @param sheet
*/
public static voidinitTitleEX(SXSSFSheet sheet, CellStyle header, String title[], inttitleLength[]) {
SXSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 800);
for(intj = 0;j
SXSSFCell cell = row0.createCell(j);
//设置每一列的字段名
cell.setCellValue(title[j]);
cell.setCellStyle(header);
sheet.setColumnWidth(j, titleLength[j]);
}
}
}