public void doExport(OutputStream out) throws JspException
{
try
{
out = toExcel(out);
HSSFWorkbook wb = new HSSFWorkbook();//下面注释为单个sheet,就是只有一个excel文件及一个sheet,小数据量则可以直接使用下面注释代码。
// sheet = wb.createSheet("sheet1");
//
// int rowNum = 0;
// int colNum = 0;
//
// if (this.header)
// {
// // Create an header row
// HSSFRow xlsRow = sheet.createRow(rowNum++);
//
// HSSFCellStyle headerStyle = wb.createCellStyle();
// headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
// headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
// HSSFFont bold = wb.createFont();
// bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// bold.setColor(HSSFColor.WHITE.index);
// headerStyle.setFont(bold);
//
// Iterator iterator = this.model.getHeaderCellList().iterator();
//
// while (iterator.hasNext())
// {
// HeaderCell headerCell = (HeaderCell) iterator.next();
//
// String columnHeader = headerCell.getTitle();
//
// if (columnHeader == null){
// columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
// }
// if(StringUtils.isNotEmpty(columnHeader)){
// HSSFCell cell = xlsRow.createCell((short) colNum++);
// //set a string value for the cell. Please note that if you are using full 16 bit unicode you should call setEncoding() first.
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(escapeColumnValue(columnHeader));
// cell.setCellStyle(headerStyle);
// }
// }
// }
//
// // get the correct iterator (full or partial list according to the exportFull field)
// RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
// // iterator on rows
//
// while (rowIterator.hasNext())
// {
// Row row = rowIterator.next();
// HSSFRow xlsRow = sheet.createRow(rowNum++);
// colNum = 0;
//
// // iterator on columns
// ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
//
// while (columnIterator.hasNext())
// {
// Column column = columnIterator.nextColumn();
// // Get the value to be displayed for the column
// Object value = column.getValue(this.decorated);
// String tile=column.getHeaderCell().getTitle();
// if(StringUtils.isNotEmpty(tile)){
// HSSFCell cell = xlsRow.createCell((short) colNum++);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(escapeColumnValue(value));
// }
// }
// }
wb.write(out);
out.close();
Thread.sleep(3000);
File files = new File("D:/exportData");
//压缩后,删除原有文件
deleteFile(files);
}
catch (Exception e)
{
throw new ExcelGenerationException(e);
}
}
private void deleteFile(File file){
if(file.exists()){
if(file.isFile()){
file.delete();
}else if(file.isDirectory()){
File files[] = file.listFiles();
for(int i=0;i<files.length;i++){
this.deleteFile(files[i]);
}
}
file.delete();
}else{
System.out.println("所删除的文件不存在!"+'\n');
}
}
public OutputStream toExcel(OutputStream out) throws Exception {
WritableWorkbook wwb = null;
BufferedOutputStream output = new BufferedOutputStream(out);
String fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyy-MM-dd HH_mm_ss_sss")+".xls";
fileName = new String(fileName.getBytes("gb2312"), "ISO-8859-1");
String os = System.getProperty("os.name").toLowerCase();
String fileDir = "";
if(os.startsWith("windows")){
//windows system
fileDir ="D:/exportData";
}else{
//unix
fileDir = "/exportData";
}
File directory = new File(fileDir);
if(directory.exists())
{
}
else
{
directory.mkdirs();
}
File file = new File(directory.getPath()+"/" + fileName);
if(!file.exists())
{
file.createNewFile();
}
// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(file);
int s = 0;
if (wwb != null) {
RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
while(s++<sheetTotal && rowIterator.hasNext() )
{
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet("sheet"+s, s);
if (this.header)
{
Iterator iterator = this.model.getHeaderCellList().iterator();
int j = 0;
while (iterator.hasNext())
{
HeaderCell headerCell = (HeaderCell) iterator.next();
String columnHeader = headerCell.getTitle();
if (columnHeader == null)
{
columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
}
if(StringUtils.isNotEmpty(columnHeader))
{
ws.addCell(new Label(j, 0, escapeColumnValue(columnHeader)));
}
j++;
}
}
int i = 1;
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
int k = 0;//下面则是迭代报表数据,具体数据获取则根据自己项目框架,此处压缩类为ant.jar下面的类,360压缩不可以,会导致路径不正确。
ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
while (columnIterator.hasNext())
{
Column column = columnIterator.nextColumn();
// Get the value to be displayed for the column
Object value = column.getValue(this.decorated);
String tile=column.getHeaderCell().getTitle();
if(StringUtils.isNotEmpty(tile))
{
ws.addCell(new Label(k++, i, escapeColumnValue(value)));
}
}
i++;
if(i>9)
{
break;
}
}
}
}
// 从内存中写入文件中
wwb.write();
// 关闭资源,释放内存
wwb.close();
//TODO zip
zip(directory.getPath()+".zip",new File(directory.getPath()));
//to page
BufferedInputStream in = new BufferedInputStream(new FileInputStream(new File(directory.getPath()+".zip")));
// FileUtils.;
byte[] b = new byte[30080];
int ii;
while((ii = in.read(b)) != -1)
{
output.write(b, 0, ii);
}
in.close();
new File(directory.getPath()+".zip").delete();
return output;
}
private void zip(String zipFileName,File inputFile)throws Exception
{
ZipOutputStream zipOut = new ZipOutputStream(new FileOutputStream(zipFileName));
zip(zipOut,inputFile,"");
zipOut.close();
}
private void zip(ZipOutputStream out ,File f,String base)throws Exception
{
if(f.isDirectory())
{
File[] fl = f.listFiles();
out.putNextEntry(new ZipEntry(base + "/"));
base = base.length() == 0 ?"":base+"/";
for(int i = 0 ; i < fl.length; i++)
{
zip(out,fl[i],base+fl[i]);
}
}else
{
out.putNextEntry(new ZipEntry(base));
FileInputStream in = new FileInputStream(f);
int b ;
System.out.println(base);
while((b = in.read())!=-1)
{
out.write(b);
}
in.close();
}
}
{
try
{
out = toExcel(out);
HSSFWorkbook wb = new HSSFWorkbook();//下面注释为单个sheet,就是只有一个excel文件及一个sheet,小数据量则可以直接使用下面注释代码。
// sheet = wb.createSheet("sheet1");
//
// int rowNum = 0;
// int colNum = 0;
//
// if (this.header)
// {
// // Create an header row
// HSSFRow xlsRow = sheet.createRow(rowNum++);
//
// HSSFCellStyle headerStyle = wb.createCellStyle();
// headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
// headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
// HSSFFont bold = wb.createFont();
// bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// bold.setColor(HSSFColor.WHITE.index);
// headerStyle.setFont(bold);
//
// Iterator iterator = this.model.getHeaderCellList().iterator();
//
// while (iterator.hasNext())
// {
// HeaderCell headerCell = (HeaderCell) iterator.next();
//
// String columnHeader = headerCell.getTitle();
//
// if (columnHeader == null){
// columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
// }
// if(StringUtils.isNotEmpty(columnHeader)){
// HSSFCell cell = xlsRow.createCell((short) colNum++);
// //set a string value for the cell. Please note that if you are using full 16 bit unicode you should call setEncoding() first.
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(escapeColumnValue(columnHeader));
// cell.setCellStyle(headerStyle);
// }
// }
// }
//
// // get the correct iterator (full or partial list according to the exportFull field)
// RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
// // iterator on rows
//
// while (rowIterator.hasNext())
// {
// Row row = rowIterator.next();
// HSSFRow xlsRow = sheet.createRow(rowNum++);
// colNum = 0;
//
// // iterator on columns
// ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
//
// while (columnIterator.hasNext())
// {
// Column column = columnIterator.nextColumn();
// // Get the value to be displayed for the column
// Object value = column.getValue(this.decorated);
// String tile=column.getHeaderCell().getTitle();
// if(StringUtils.isNotEmpty(tile)){
// HSSFCell cell = xlsRow.createCell((short) colNum++);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(escapeColumnValue(value));
// }
// }
// }
wb.write(out);
out.close();
Thread.sleep(3000);
File files = new File("D:/exportData");
//压缩后,删除原有文件
deleteFile(files);
}
catch (Exception e)
{
throw new ExcelGenerationException(e);
}
}
private void deleteFile(File file){
if(file.exists()){
if(file.isFile()){
file.delete();
}else if(file.isDirectory()){
File files[] = file.listFiles();
for(int i=0;i<files.length;i++){
this.deleteFile(files[i]);
}
}
file.delete();
}else{
System.out.println("所删除的文件不存在!"+'\n');
}
}
public OutputStream toExcel(OutputStream out) throws Exception {
WritableWorkbook wwb = null;
BufferedOutputStream output = new BufferedOutputStream(out);
String fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyy-MM-dd HH_mm_ss_sss")+".xls";
fileName = new String(fileName.getBytes("gb2312"), "ISO-8859-1");
String os = System.getProperty("os.name").toLowerCase();
String fileDir = "";
if(os.startsWith("windows")){
//windows system
fileDir ="D:/exportData";
}else{
//unix
fileDir = "/exportData";
}
File directory = new File(fileDir);
if(directory.exists())
{
}
else
{
directory.mkdirs();
}
File file = new File(directory.getPath()+"/" + fileName);
if(!file.exists())
{
file.createNewFile();
}
// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(file);
int s = 0;
if (wwb != null) {
RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
while(s++<sheetTotal && rowIterator.hasNext() )
{
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet("sheet"+s, s);
if (this.header)
{
Iterator iterator = this.model.getHeaderCellList().iterator();
int j = 0;
while (iterator.hasNext())
{
HeaderCell headerCell = (HeaderCell) iterator.next();
String columnHeader = headerCell.getTitle();
if (columnHeader == null)
{
columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
}
if(StringUtils.isNotEmpty(columnHeader))
{
ws.addCell(new Label(j, 0, escapeColumnValue(columnHeader)));
}
j++;
}
}
int i = 1;
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
int k = 0;//下面则是迭代报表数据,具体数据获取则根据自己项目框架,此处压缩类为ant.jar下面的类,360压缩不可以,会导致路径不正确。
ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
while (columnIterator.hasNext())
{
Column column = columnIterator.nextColumn();
// Get the value to be displayed for the column
Object value = column.getValue(this.decorated);
String tile=column.getHeaderCell().getTitle();
if(StringUtils.isNotEmpty(tile))
{
ws.addCell(new Label(k++, i, escapeColumnValue(value)));
}
}
i++;
if(i>9)
{
break;
}
}
}
}
// 从内存中写入文件中
wwb.write();
// 关闭资源,释放内存
wwb.close();
//TODO zip
zip(directory.getPath()+".zip",new File(directory.getPath()));
//to page
BufferedInputStream in = new BufferedInputStream(new FileInputStream(new File(directory.getPath()+".zip")));
// FileUtils.;
byte[] b = new byte[30080];
int ii;
while((ii = in.read(b)) != -1)
{
output.write(b, 0, ii);
}
in.close();
new File(directory.getPath()+".zip").delete();
return output;
}
private void zip(String zipFileName,File inputFile)throws Exception
{
ZipOutputStream zipOut = new ZipOutputStream(new FileOutputStream(zipFileName));
zip(zipOut,inputFile,"");
zipOut.close();
}
private void zip(ZipOutputStream out ,File f,String base)throws Exception
{
if(f.isDirectory())
{
File[] fl = f.listFiles();
out.putNextEntry(new ZipEntry(base + "/"));
base = base.length() == 0 ?"":base+"/";
for(int i = 0 ; i < fl.length; i++)
{
zip(out,fl[i],base+fl[i]);
}
}else
{
out.putNextEntry(new ZipEntry(base));
FileInputStream in = new FileInputStream(f);
int b ;
System.out.println(base);
while((b = in.read())!=-1)
{
out.write(b);
}
in.close();
}
}