导出excel文件

f前端代码

unction exportAllRecords(){

$('#exportBtn').val('导出ing...');
$('#exportBtn').attr("disabled", true);

var url = 'exportAllRecords.action';
var ifr;
    if (document.getElementById('downLoadIframe') == null) {
        ifr = document.createElement("iframe");
        ifr.id = "downLoadIframe";
        ifr.onreadystatechange = function(){
            if (obj.readyState == 'complete' || obj.readyState == 'interactive') {
}
        };
        ifr.style.display = "none";
        document.body.appendChild(ifr);
    }
    else {
        ifr = document.getElementById('downLoadIframe');
    }
    ifr.src = url;
    setTimeout(function(){
        $('#exportBtn').val('导出数据');
        $('#exportBtn').attr("disabled", false);
    }, 10000);   

}

后端代码

public  void exportAllRecords() throws Exception{
String sheetName = "后台配置视频参数";
String excelName = sheetName + ".xlsx";
List<Property> all = PremiereParametersManageService.getAllRecords();
List<List<String>> allRows =  new ArrayList<List<String>>();

for(Property property : all){
String uid = property.get("uid");
String hrl = property.get("hrl");
String vrl = property.get("vrl");
String operate_uid = property.get("operate_uid");
String operate_time = property.get("operate_time");

List<String> row = Lists.newArrayList();

row.add(uid);
row.add(getType(hrl));
row.add(getType(vrl));
row.add(operate_uid);
row.add(operate_time);

allRows.add(row);
}

OutputStream out = getResponse().getOutputStream();
renderExportFile(excelName, "application/vnd.ms-excel");
List<String> columnNames = new ArrayList<String>();
columnNames.add("主播UID");
columnNames.add("横屏开播");
columnNames.add("竖屏开播");
columnNames.add("创建人ID");
columnNames.add("创建时间");

try {
Workbook wb = new SXSSFWorkbook();
ExcelUtil.exportExcel2007(wb, sheetName, columnNames, allRows, out);
wb.write(out);
out.flush();
} catch (RuntimeException e) {
logger.warn("PremiereParametersManageAction.exportAllRecords,exportExcel2007 error:"+e.getMessage(), e);
} finally {
out.close();
}

}

/**
 * Copyright (c) 2011 YYMusic
 * All rights reserved.
 */
package com.duowan.yy.common.util;


import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;


import javax.imageio.ImageIO;


import org.apache.log4j.Logger;
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.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;




/**
 * Excel导出工具
 *
 * @author 陈高昌 修改日期：2011年11月10日
 */
public class ExcelUtil {

private static Logger m_logger = Logger.getLogger(ExcelUtil.class);

/**
* 根据Excel所有列名和行数据导出Excel2007
* @param wb
* @param sheetName
* @param columnNames
* @param allRows
* @param out
* @return
* @throws Exception
*/
public static OutputStream exportExcel2007(Workbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, OutputStream out) throws Exception{

CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet(sheetName);

short index = 0;
while(index < columnNames.size()) {
sheet.setColumnWidth(index, 6500);
index++;
}

Row row;
Cell cell;
row = sheet.createRow(0);
for(int j = 0; j < columnNames.size(); j ++){
cell = row.createCell(j);
cell.setCellValue(createHelper.createRichTextString(columnNames.get(j)));
}
for(int i = 1; i <= allRows.size(); i ++){
row = sheet.createRow(i);
List<String> rowData = allRows.get(i - 1);
for(int j = 0; j < rowData.size(); j ++){
cell = row.createCell(j);
String value = rowData.get(j);
if(CommUtil.isDouble(value)){
cell.setCellValue(Double.valueOf(value));
}else{
cell.setCellValue(createHelper.createRichTextString(value));
}
}
}
wb.write(out);
out.flush();
return out;
}

/**
* 根据Excel所有列名和行数据导出Excel2007
* @param wb
* @param sheetName
* @param columnNames
* @param allRows
* @param out
* @return
* @throws Exception
*/
public static OutputStream exportExcel2007new(Workbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, OutputStream out) throws Exception{

CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet(sheetName);

short index = 0;
while(index < columnNames.size()) {
sheet.setColumnWidth(index, 6500);
index++;
}

Row row;
Cell cell;
row = sheet.createRow(0);
for(int j = 0; j < columnNames.size(); j ++){
cell = row.createCell(j);
cell.setCellValue(createHelper.createRichTextString(columnNames.get(j)));
}
for(int i = 1; i <= allRows.size(); i ++){
row = sheet.createRow(i);
List<String> rowData = allRows.get(i - 1);
for(int j = 0; j < rowData.size(); j ++){
cell = row.createCell(j);
String value = rowData.get(j);
cell.setCellValue(createHelper.createRichTextString(value));
}
}
return out;
}

/**
* 根据Excel所有列名和行数据导出Excel
*
* @param wb 工作簿
* @param sheetName 工作簿中每一个Excel表格的名称
* @param columnNames Excel中所有列名
* @param allRows Excel列表中所有要显示的数据
* @param out 输出流
*
* @return Excel数据输出流
*
* @throws IOException
*/
public static OutputStream exportExcel(HSSFWorkbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, OutputStream out) throws IOException {


writeSheet(wb, sheetName, columnNames, allRows);


wb.write(out);
out.flush();


return out;
}


/**
* 根据Excel所有列名和行数据导出Excel
*
* @param wb 工作簿
* @param sheetName 工作簿中每一个Excel表格的名称
* @param columnNames Excel中所有列名
* @param allRows Excel列表中所有要显示的数据
* @param out 输出流
*
* @return Excel数据输出流
*
* @throws IOException
*/
public static OutputStream exportExcel(HSSFWorkbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, OutputStream out, String imgPath) throws IOException {




writeSheet(wb, sheetName, columnNames, allRows);


BufferedImage bufferImg =null;


// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        String filename = imgPath.substring(imgPath.indexOf("=")+1);
        bufferImg = ImageIO.read(new File(System.getProperty("java.io.tmpdir"), filename));
        ImageIO.write(bufferImg,"png",byteArrayOut);
HSSFPatriarch patriarch = wb.getSheet(sheetName).createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,600,255,(short) 1,allRows.size()+2,(short)8,20);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_PNG));
wb.write(out);
out.flush();


return out;
}


/**
* 对Excel构造所有列名和数据
*
* @param wb 工作簿
* @param sheetName 工作簿中每一个Excel表格的名称
* @param columnNames Excel中所有列名
* @param allRows Excel列表中所有要显示的数据
*/
public static void writeSheet(HSSFWorkbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows) {


HSSFSheet sheet = wb.createSheet(sheetName);
short index = 0;
for (String columnName : columnNames) {
//sheet.setColumnWidth((short) index, (short) 250);
sheet.setColumnWidth(index, 6500);
index++;
}

if(!BlankUtil.isBlank(columnNames))
writeColumnNames(wb, sheet, columnNames);
if(!BlankUtil.isBlank(allRows))
writeData(wb, sheet, allRows);
}


/**
* 把要显示的列名写进Excel
*
* @param wb 工作簿
* @param sheet 工作簿中每一个Excel表格
* @param columnNames Excel中所有列名
*/
public static void writeColumnNames(HSSFWorkbook wb, HSSFSheet sheet, List<String> columnNames) {
writeColumnNames(wb, sheet, columnNames,0);
}


public static void writeColumnNames(HSSFWorkbook wb, HSSFSheet sheet, List<String> columnNames,int rowIndex) {


HSSFRow row = sheet.createRow(rowIndex);
HSSFCellStyle headerStyle = ExcelUtil.headerStyle(wb);
short index = 0;


for (String columnName : columnNames) {


HSSFCellUtil.createCell(row, index, columnName, headerStyle);
index++;
}
}

/**
* 把要显示的数据写进Excel
*
* @param wb 工作簿
* @param sheet 工作簿中每一个Excel表格
* @param allRows Excel列表中所有要显示的数据
*/
public static void writeData(HSSFWorkbook wb, HSSFSheet sheet, List<List<String>> allRows) {
writeData(wb, sheet, allRows,1);
}


public static void writeData(HSSFWorkbook wb, HSSFSheet sheet, List<List<String>> allRows,int rowIndex) {


int rowCount = rowIndex;


for (List<String> everyRow : allRows) {


short index = 0;
HSSFRow row = sheet.createRow(rowCount);
row.setHeight((short)300);
HSSFCellStyle normalStyle = ExcelUtil.normalStyle(wb);


for (String cell : everyRow) {


HSSFCellUtil.createCell(row, index, cell, normalStyle);
index++;
}


rowCount++;
}
}

private static HSSFFont boldFont = null;

public static HSSFCellStyle headerStyle(HSSFWorkbook wb) {
// Table Header Ingfomation
boldFont = boldFont == null ? wb.createFont() : boldFont;
boldFont.setFontHeightInPoints((short) 12);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);


HSSFCellStyle boldStyle = wb.createCellStyle();
boldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
boldStyle.setFont(boldFont);


thinBlackBorder(boldStyle);


return boldStyle;
}


public static void thinBlackBorder(HSSFCellStyle titleStyle) {
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
}


private static HSSFFont normalFont = null;

public static HSSFCellStyle normalStyle(HSSFWorkbook wb) {
// House type header
normalFont = normalFont == null ? wb.createFont() : normalFont;
normalFont.setFontHeightInPoints((short) 12);
HSSFCellStyle normalStyle = wb.createCellStyle();
normalStyle.setFont(normalFont);
normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
normalStyle.setWrapText(true);
thinBlackBorder(normalStyle);


return normalStyle;
}


private static HSSFFont priceFont = null;

public static HSSFCellStyle priceValueStyle(HSSFWorkbook wb) {
// House type header
priceFont = priceFont == null ? wb.createFont() : priceFont;
priceFont.setFontHeightInPoints((short) 10);


HSSFCellStyle priceStyle = wb.createCellStyle();
priceStyle.setFont(priceFont);
priceStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
priceStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


thinBlackBorder(priceStyle);


return priceStyle;
}

private static HSSFFont noBorderFont = null;

public static HSSFCellStyle noBorderStyle(HSSFWorkbook wb) {
// House type header
noBorderFont = noBorderFont == null ? wb.createFont() : noBorderFont;
noBorderFont.setFontHeightInPoints((short) 10);


HSSFCellStyle noBorderStyle = wb.createCellStyle();
noBorderStyle.setFont(noBorderFont);
noBorderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
noBorderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


setNoBorder(noBorderStyle);


return noBorderStyle;
}

public static HSSFCellStyle setNoBorder(HSSFCellStyle style) {

style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
style.setBorderRight(HSSFCellStyle.BORDER_NONE);
style.setBorderTop(HSSFCellStyle.BORDER_NONE);
style.setBorderBottom(HSSFCellStyle.BORDER_NONE);


return style;
}

private HSSFCellStyle style = null;
private HSSFFont font = null;
public ExcelUtil(HSSFWorkbook wb) {
this.style = wb.createCellStyle();
this.font = wb.createFont();
}


public HSSFCellStyle style() {

font.setFontHeightInPoints((short) 10);


style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);


return style;
}

    public HSSFCellStyle noBorderStyle() {

font.setFontHeightInPoints((short) 10);


style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
style.setBorderRight(HSSFCellStyle.BORDER_NONE);
style.setBorderTop(HSSFCellStyle.BORDER_NONE);
style.setBorderBottom(HSSFCellStyle.BORDER_NONE);


return style;
}
    
    public HSSFCellStyle headStyle() {

font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);


style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);


return style;
}
    
    public HSSFCellStyle headNoBorderStyle() {

font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);


style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
style.setBorderRight(HSSFCellStyle.BORDER_NONE);
style.setBorderTop(HSSFCellStyle.BORDER_NONE);
style.setBorderBottom(HSSFCellStyle.BORDER_NONE);


return style;
    }

/**
* 设置合并区域的边框
* 
* @param sheet
* @param region
* @param cs
*/
public static void setRegionStyle(HSSFSheet sheet, Region region, HSSFCellStyle cs) {


for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
HSSFRow row = HSSFCellUtil.getRow(i, sheet);
for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}


/**
*  读取表内容
*  @author chengaochang
* @param workbook
* @param sheet
* @param startHead 头部始行
* @param startRow 记录开始行
*/
public static List<List> readExcel(final HSSFWorkbook workbook,final int startHead,final int startRow,String sheet){
HSSFSheet hssSheet = workbook.getSheet(sheet);//工作区间
int rowNum = hssSheet.getPhysicalNumberOfRows();//行数量
HSSFRow rowHead = hssSheet.getRow(startHead);
int columnNum = rowHead.getPhysicalNumberOfCells();//以头部为标准的列数量
HSSFRow row = null;
HSSFCell cell = null;
List <List> list=new ArrayList<List>();
List <String> rowList=null;
for (int r = startRow; r <rowNum; r++) {//从startRow行开始读取数据
rowList=new ArrayList<String>();
for (int c = 0; c < columnNum - 1; c++) {
row=hssSheet.getRow(r);
cell=row.getCell(c);
String cellValue="";
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
if(HSSFDateUtil.isCellDateFormatted(cell)){//日期读取
cellValue = String.valueOf(cell.getDateCellValue());
}else{
cellValue = String.valueOf((long)cell.getNumericCellValue());
}
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cellValue = String.valueOf((cell.getRichStringCellValue()));
}
rowList.add(cellValue);
}
list.add(rowList);
}
return list;
}
/**
* @author chengaochang
* 获取每行对应单元的值
* @param row 行
* @param index 列的索引
* @return
*/
public static String getCellValue(HSSFRow row,int index){
HSSFCell cell=row.getCell(index);
String cellValue="";
if(cell!=null){
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
if(HSSFDateUtil.isCellDateFormatted(cell)){//日期读取
cellValue = String.valueOf(cell.getDateCellValue());
}else{
cellValue = new BigDecimal(cell.getNumericCellValue()).toString();
}
}
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cellValue = String.valueOf((cell.getRichStringCellValue()));
}
}
return cellValue;
}


/**
* 创建一个空的excel文件
* 
* @param dir 文件目录
* @param fileName 文件名称
*/
public static boolean createExcelFile(String dir,String fileName) 
{
String fullFilePath = dir + File.separator + fileName;
fullFilePath = StringUtil.replace(fullFilePath,"\\", "/");
File excelFile = new File(fullFilePath);
if(excelFile.exists())
{
excelFile.delete();
}
try {
excelFile.createNewFile();
return true;
} catch (IOException e1) {
m_logger.error("createExcelFile()删除文件失败,fullFilePath---->" + fullFilePath);
return false;
}
}

/**
* 删除指定的excel文件
* 
* @param dir 文件目录
* @param fileName 文件名称
* 
* @return 成功：true  失败：false;
*/
public static boolean deleteExcelFile(String dir,String fileName) 
{
String fullFilePath = dir + File.separator + fileName;
fullFilePath = StringUtil.replace(fullFilePath,"\\", "/");
File excelFile = new File(fullFilePath);
try {
excelFile.delete();
return true;
} catch (Exception e1) {
m_logger.error("deleteExcelFile()删除文件失败,fullFilePath---->" + fullFilePath);
return false;
}
}


/**
* 重命名指定的excel文件
* 
* @param dir1 文件目录1
* @param fileName1 文件1名称
* @param dir2 文件目录2
* @param fileName2 文件2名称
* 
* @return 成功：true  失败：false;
*/
public static boolean renameExcelFile(String dir1,String fileName1,String dir2,String fileName2) 
{
String fullFilePath1 = dir1 + File.separator + fileName1;
fullFilePath1 = StringUtil.replace(fullFilePath1,"\\", "/");
File file1 = new File(fullFilePath1);

String fullFilePath2 = dir2 + File.separator + fileName2;
fullFilePath2 = StringUtil.replace(fullFilePath2,"\\", "/");
File file2 = new File(fullFilePath2);
try
{
file1.renameTo(file2);
return true;
}
catch(Exception ex)
{
m_logger.error("renameExcelFile()重命名文件失败,fullFilePath1---->" + fullFilePath1);
m_logger.error("renameExcelFile()重命名文件失败,fullFilePath2---->" + fullFilePath2);
return false;
}
}

/**
* 把Workbook对象的数据，写入指定的excel文件
* 
* @param wb HSSFWorkbook对象
* @param dir excel文件目录
* @param fileName excel文件名称
* 
* @throws Exception
*/
public static void exportExcelFile(HSSFWorkbook wb, String dir,String fileName) throws Exception 
{
String fullFilePath = dir + File.separator + fileName;
fullFilePath = StringUtil.replace(fullFilePath,"\\", "/");
try {
FileOutputStream fOut = new FileOutputStream(fullFilePath);
wb.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
m_logger.error("exportExcelFile()导出失败,fullFilePath---->" + fullFilePath);
throw new FileNotFoundException("生成导出Excel文件出错!");
} catch (IOException e) {
m_logger.error("exportExcelFile()导出失败,fullFilePath---->" + fullFilePath);
throw new IOException("写入Excel文件出错! ", e);
}
}

/**
* 根据Excel所有列名和行数据导出Excel
*
* @param wb 工作簿
* @param sheetName 工作簿中每一个Excel表格的名称
* @param columnNames Excel中所有列名
* @param allRows Excel列表中所有要显示的数据
* @param dir excel文件目录
* @param fileName excel文件名称
*
* @return Excel数据输出流
*
* @throws IOException
*/
public static void exportExcel(HSSFWorkbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, String dir,String fileName) throws Exception
{
ExcelUtil.writeSheet(wb, sheetName, columnNames, allRows);
ExcelUtil.exportExcelFile(wb, dir, fileName);
}

/** 根据Excel所有列名和行数据导出没有样式的Excel
* @param wb
* @param sheetName
* @param columnNames
* @param allRows
* @param dir
* @param fileName
* @throws Exception
*/
public static void exportExcelNoStyle(HSSFWorkbook wb, String sheetName, List<String> columnNames,
List<List<String>> allRows, String dir,String fileName) throws Exception
{
ExcelUtil.writeSheetNoStyle(wb, columnNames, allRows);
ExcelUtil.exportExcelFile(wb, dir, fileName);
}

public static void writeSheetNoStyle(HSSFWorkbook hssfworkbook,List<String> columnNames,
List<List<String>> allRows) throws Exception {

int maxSheetNum = 50000; //最大行数

hssfworkbook.createInformationProperties();
hssfworkbook.getDocumentSummaryInformation().setCompany("");
hssfworkbook.getSummaryInformation().setSubject("");


int sheetNum = (int) Math.ceil((double) allRows.size() / maxSheetNum);

if (sheetNum > 0) {//多个sheet
int curIndex = 0; //当前遍历总数中的第几行
for (int m = 0; m < sheetNum; m++) {//生成多个sheet
Sheet sheet = hssfworkbook.createSheet("第" + (m + 1) + "页");
int sheetIndex = 0; //当前sheet的当前行数
while(maxSheetNum + 1 > sheetIndex){ //小于每个sheet最大行数
Row row = sheet.createRow(sheetIndex);
if(sheetIndex == 0){ //表头
for(int j = 0;j < columnNames.size();j++){
row.createCell(j).setCellValue(columnNames.get(j));
}
}else{
if(allRows.size() > curIndex){//保证不越界
List<String> rs = allRows.get(curIndex);
for (int j = 0; j < columnNames.size(); j++){ //填充数据
row.createCell(j).setCellValue(rs.get(j));
}
curIndex++;
}else{ //allRows数据遍历结束
break;
}
}
sheetIndex++;
}
}
} else { //保证生成一个sheet
Sheet sheet = hssfworkbook.createSheet("第1页");
Row row = sheet.createRow(0);
for (int i = 0; i < columnNames.size(); i++){
row.createCell(i).setCellValue(columnNames.get(i));
}
}
}

}