GVKun编程网logo

七、SXSSFWorkbook生成大excle，避免内存溢出

8

如果您对七、SXSSFWorkbook生成大excle，避免内存溢出感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解七、SXSSFWorkbook生成大excle，避免内存溢出的各种细节，此外还

如果您对七、SXSSFWorkbook生成大excle，避免内存溢出感兴趣，那么这篇文章一定是您不可错过的。我们将详细讲解七、SXSSFWorkbook生成大excle，避免内存溢出的各种细节，此外还有关于.net单个sheet使用XSSFWorkbook导出excel表格、.net多个sheet使用XSSFWorkbook导出excel表、Apache poi workbookfactory 从 xlsx 创建 HSSFWorkbook，但我希望 XSSFWorkbook、HSSFWorkbook 导出excel java的实用技巧。

本文目录一览：

七、SXSSFWorkbook生成大excle，避免内存溢出

七、SXSSFWorkbook生成大excle，避免内存溢出

1.SXSSFWorkbook理解：
　　SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，SXSSFWorkbook专门处理大数据，对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。
注:HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)是1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据，甚至上千条数据就可以考虑使用SXSSFWorkbook了。
注意：首先需要引入依赖：注意：4.0.0版本的JDK需要1.8以上，如果JDK是1.7的，那么就使用3.9版本的依赖

2.数据过多使用SXSSFWorkbook也是会出现内存溢出的问题，主要出现的两个地方：
a.从数据库读取数据到内存时溢出。
    优化角度1：取数据时用分页的方法分批取数据，然后写入sheet中。这样就可以避免取数据时内存溢出;
    java.lang.OutOfMemoryError:GC overhead limit exceeded
b.FileOutputStream os = new FileOutputStream(path); wb.write(os);
    优化角度2：    创建Workbook时设置工作簿保存在内存中数据的条数，这样一旦这个Workbook中数据量超过1000就会写入到磁盘中，减少内存的使用量来提高速度和避免溢出。
    Workbook wb = new SXSSFWorkbook(1000);

3.通过分页查询实现海量excle数据的导出，避免发生OOM。代码如下：

@RequestMapping("/exportPurchaseInfo.do")
@ResponseBody
public void exportPurchaseGoodsInfo(HttpServletRequest request, HttpServletResponse response,QueryParamDto reqDto) {
    try {
            int pageSize = 1000;//分页的大小,即每次分页查询多少条记录开关
            String[] headers = {"序号","商品编号","商品名称","品类","品牌","采购金额（元）","采购数量（件）"};
            SXSSFWorkbook workbook = ExportExcelByPageUtil.makeSXSSFWorkbook(purchseReportExportExcelByPageService,reqDto, headers, pageSize);
            ExportExcelByPageUtil.exportExcel(request, response, workbook, "商品销售报表.xlsx");
            
        } catch (Exception e) {
            logger.error("导出商品销售报表异常",e);
        }

}
View Code

ExportExcelByPageUtil.java

public static <T> SXSSFWorkbook makeSXSSFWorkbook(ReportExportExcelByPageService<T> exportExcelByPageTyService, T queryDataBo, String[] headers, int pageSize){
        int rowAccessWindowSize = 100;
        //这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("sheet1");
        Map<String,CellStyle> cellStyles=getCellStyle(workbook);
        CellStyle cs = cellStyles.get("cs");
        CellStyle cs2 = cellStyles.get("cs2");

        //设置列名
        Row row = sheet.createRow((short) 0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cs);
        }

        String pattern = "yyyy-MM-dd HH:mm:ss";
        String beginTime = DateUtil.formatDate(new Date(), pattern);
        int allCount = exportExcelByPageTyService.queryAllCount(queryDataBo);
        if(allCount <= 0){
            return workbook;
        }

        //处理数据量超过一个sheet页最大量时异常，支持的最大导出数据量为10000
        int maxExportCount = 10000;
        allCount = allCount > maxExportCount ? maxExportCount : allCount;
        //page：查询总数据量为 allCount条记录时，每次生成pageSize条记录  需要page 次进行导出
        int page = (int) Math.ceil((double) allCount / (double) pageSize);
        //101条记录 aliquotFlag=false
        boolean aliquotFlag = allCount % pageSize == 0;

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for(int i = 0; i < page; i++){
            logger.error("第"+(i+1)+"次循环开始："+ DateUtil.formatDate(new Date(), pattern));
            int startIndex = i*pageSize;
            int maxCount = pageSize;
            //最后一次的最大记录数
            if(!aliquotFlag && i==(page-1)){
                maxCount = allCount - startIndex;
            }
            logger.error("导出分页：startIndex==>"+startIndex+", pageSize==>"+maxCount);
            List<String> ids = exportExcelByPageTyService.queryIdsInfo(queryDataBo, startIndex, maxCount);
            List<LinkedHashMap<String, Object>> dataList = exportExcelByPageTyService.queryDatas(ids);
            if(CollectionUtils.isNotEmpty(dataList)){
                Set<Map.Entry<String, Object>> set = null;
                Iterator<Map.Entry<String, Object>> iterator = null;
                int j = 0;
                for (int rowNum = 1, len = dataList.size() + 1; rowNum < len; rowNum++) {
                    Row row1 = sheet.createRow(startIndex+rowNum);
                    set = dataList.get(rowNum - 1).entrySet();
                    iterator = set.iterator();
                    j = 0;
                    while (iterator.hasNext()) {
                        Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iterator.next();
                        String cellValue = "";
                        if(entry.getValue()==null){
                            cellValue = "";
                        }else if(entry.getValue() instanceof Timestamp){
                            cellValue = sdf.format((Timestamp) entry.getValue());
                        } else {
                            cellValue = String.valueOf(entry.getValue());
                        }
                        Cell cell = row1.createCell(j);
                        cell.setCellValue(cellValue);
                        cell.setCellStyle(cs2);
                        j++;
                    }
                    if (rowNum % rowAccessWindowSize == 0) {
                        try {
                            sheet.flushRows();
                        } catch (IOException e) {
                            logger.error("sheet从内存写入本地硬盘失败", e);
                        }
                    }
                }
            }
            dataList = null;
            logger.error("第"+(i+1)+"次循环结束："+formatDate(new Date(), pattern));
        }
        logger.error("导出分页开始："+beginTime);
        logger.error("导出分页结束："+ formatDate(new Date(), pattern));
        return workbook;
    }
View Code 
public static boolean exportExcel(HttpServletRequest request, HttpServletResponse response,SXSSFWorkbook workbook, String fileName){
    OutputStream out = null;
    try {
        String userAgent = request.getHeader("USER-AGENT");
        fileName = dealChineseFileName(userAgent, fileName);
        out = response.getOutputStream();
        response.setContentType("application/x-download");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        workbook.write(out);
        out.flush();
    } catch (Exception e) {
        logger.error("导出Excel文件失败", e);
        return false;
    }finally {
        if (out != null) {
            try {
                out.close();
            } catch (IOException e) {
                logger.error("导出Excel文件关闭输出流失败", e);
            } finally {
                out = null;
            }
        }
        if(workbook!=null){
            try {
                workbook.dispose();
                workbook.close();
            } catch (IOException e) {
                logger.error("导出Excel文件关闭输出流失败", e);
            } finally {
                workbook = null;
            }
        }
    }
    return true;
}
View Code

ReportExportExcelByPageService.java

public interface ReportExportExcelByPageService<T> {
    /** 查满足条件记录数 */
    @Method(description = "查满足条件count")
    public int queryAllCount(T queryDataBo);
    /** 分页查满足条件Ids  */
    @Method(idempotent = true, retryTimes = 3, timeout = 30000, description = "分页查满足条件Ids")
    public List<String> queryIdsInfo(T queryDataBo, int startIndex, int pageSize);
    /** 根据Ids查数据 */
    @Method(idempotent = true, retryTimes = 3, timeout = 30000, description = "根据Ids查数据")
    public List<LinkedHashMap<String, Object>> queryDatas(List<String> ids);
}
View Code

ReportExportExcelByPageServiceImpl.java

public class ReportExportExcelByPageServiceImpl implements ReportExportExcelByPageService<QueryParamDto> {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Override
    public int queryAllCount(QueryParamDto queryDataBo) {
        //查询记录数业务代码
    }

    @Override
    public List<String> queryIdsInfo(QueryParamDto queryDataBo, int startIndex, int pageSize) {
        List<String> list = new ArrayList<String>();
        list.add(queryDataBo.getBeginTime());
        list.add(queryDataBo.getEndTime());
        list.add(queryDataBo.getUserCode());
        list.add(queryDataBo.getType());
        list.add(queryDataBo.getAllType());
        list.add(String.valueOf(startIndex));
        list.add(String.valueOf(pageSize));
        return list;
    }

    @Override
    public List<LinkedHashMap<String, Object>> queryDatas(final List<String> ids) {
        List<LinkedHashMap<String, Object>> result = new ArrayList<>();
        if (CollectionUtils.isEmpty(ids)) {
            return result;
        }
        QueryParamDto queryDataBo=new QueryParamDto();
        queryDataBo.setBeginTime( ids.get(0));
        queryDataBo.setEndTime(ids.get(1));
        queryDataBo.setUserCode(ids.get(2));
        queryDataBo.setType(ids.get(3));
        queryDataBo.setAllType(ids.get(4));
        queryDataBo.setStartIndex(Integer.parseInt(ids.get(5)));
        queryDataBo.setPageSize(Integer.parseInt(ids.get(6)));
        
        //根据分页的入参进行分页查询:返回结果为list
        List<QueryResultData>  commodityListInfo = ...;
        /*
         SELECT
                zp.commodity_code commodityCode,
                zp.commodity_name commodityName,
                zp.category_name categoryName,
                zp.brand brand,
                sum(zp.purchase_money) purchaseMoney,
                sum(zp.purchase_num) purchaseNum
         FROM
                tableName zp
         WHERE
            zp.purchase_day BETWEEN :beginTime AND :endTime
            and user_code =:userCode
            group by zp.commodity_code
            order by purchaseMoney desc
            <#if startIndex !=null && startIndex !="" && pageSize!=null && pageSize !="">
            LIMIT :startIndex ,:pageSize
            </#if>
        */
        
        //排名","商品编号","商品名称","品类","品牌","采购金额（元）","采购数量（件）
        if(org.apache.commons.collections.CollectionUtils.isNotEmpty(commodityListInfo)){
            for (int i = 0; i < commodityListInfo.size(); i++) {
                LinkedHashMap map = new LinkedHashMap();
                QueryResultData queryResultData = commodityListInfo.get(i);
                map.put("xuHao", (i+1) + "");
                map.put("commodityCode", queryResultData.getCommodityCode());
                map.put("commodityName", queryResultData.getCommodityName());
                map.put("categoryName", queryResultData.getCategoryName());
                map.put("brand", queryResultData.getBrand());
                map.put("purchaseMoney", queryResultData.getPurchaseMoney());
                 map.put("purchaseNum", queryResultData.getPurchaseNum());
                result.add(map);
            }
        }
            return result;
    }
}
View Code

DateUtil.java

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtil {
    public static final String YYYYMMDD = "yyyy-MM-dd";
    public static final String YYYYMMDDHHMMSS = "yyyy-MM-dd HH:mm:ss";
    public static final String YYYYMMDDHHmmss = "yyyy-MM-dd HH:mm:ss";

    private DateUtil() {
    
    }

    public static Date parseDate(String time, String pattern) {
        if (time == null) {
            return null;
        } else {
            SimpleDateFormat df = new SimpleDateFormat(pattern);

            try {
                return df.parse(time);
            } catch (ParseException var4) {
                return null;
            }
        }
    }

    public static String formatDate(Date time, String pattern) {
        if (time == null) {
            return "";
        } else {
            String result = null;
            DateFormat df = new SimpleDateFormat(pattern);
            result = df.format(time);
            return result;
        }
    }
}
View Code

 getCellStyle方法

public static Map<String,CellStyle> getCellStyle(SXSSFWorkbook workbook){
        Map<String,CellStyle> cellStyles=new HashMap<String,CellStyle>();

        // 创建两种单元格格式
        CellStyle cs = workbook.createCellStyle();
        CellStyle cs2 = workbook.createCellStyle();

        // 创建两种字体
        Font f = workbook.createFont();
        Font f2 = workbook.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyles.put("cs",cs);
        cellStyles.put("cs2",cs2);
        return cellStyles;
    }
View Code

.net单个sheet使用XSSFWorkbook导出excel表格

.net单个sheet使用XSSFWorkbook导出excel表格

1.添加引用

NPOI.dll

NPOI.OOXML.dll

2.引用

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;

3.新建一个excel模板放到项目中。例如：templete.xlsx

4.实现方式

自定义参数1：typeName

自定义参数2：dt，数据库查询内容

public string exportExcel(string typeName, DataTable dt)
{

if (dt == null || dt.Rows.Count == 0) return "";
string daochu_result = "";
try
{
string fileNameDir = System.Web.HttpContext.Current.Server.MapPath("~");
string filePath = fileNameDir + "\\Upload\\templete.xlsx";


//打开模板文件，得到WorkBook对象
using (FileStream excelstream = new FileStream(Path.GetFullPath(filePath), FileMode.Open))
{
XSSFWorkbook workBook = new XSSFWorkbook(excelstream);
//得到WorkSheet对象
XSSFSheet workSheet = workBook.GetSheetAt(0) as XSSFSheet;
//标题
IRow frow = workSheet.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}

if (!Directory.Exists(fileNameDir + "\\Upload\\SearchFile\\" + typeName))
{
Directory.CreateDirectory(fileNameDir + "\\Upload\\SearchFile\\" + typeName);
}
string saveFileName = fileNameDir + "\\Upload\\SearchFile\\" + typeName + "\\" +typeName + ".xlsx";


if (System.IO.File.Exists(saveFileName))
{
//如果存在则删除
System.IO.File.Delete(saveFileName);
}
FileStream wexcelstrem = new FileStream(Path.GetFullPath(saveFileName), FileMode.OpenOrCreate);
workBook.Write(wexcelstrem);
excelstream.Close();
wexcelstrem.Close();
daochu_result = "Success";
}

}
catch (Exception e)
{
daochu_result = "Error";
}
return daochu_result;
}

 

.net多个sheet使用XSSFWorkbook导出excel表

.net多个sheet使用XSSFWorkbook导出excel表

1.添加引用

NPOI.dll

NPOI.OOXML.dll

2.引用

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;

3.新加一个excel空表格作为模板。例如：templete.xlsx

4.代码实现

自定义参数1：typeName

public string exportExcelsheets(string typeName)
{
PostgreHelper helper = new PostgreHelper();
ProTools Dic = new ProTools();
string dic = Dic.ReturnDic();
JObject jo = (JObject)JsonConvert.DeserializeObject(dic);

 

string daochu_result = "";
try
{
string fileNameDir = System.Web.HttpContext.Current.Server.MapPath("~");
string filePath = fileNameDir + "\\Upload\\templete.xlsx";


//打开模板文件，得到WorkBook对象
using (FileStream excelstream = new FileStream(Path.GetFullPath(filePath), FileMode.Open))
{
XSSFWorkbook workBook = new XSSFWorkbook(excelstream);
excelstream.Close();
{
string sql = $"这里是SQL语句";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
//XSSFSheet workSheet = workBook.GetSheetAt(0) as XSSFSheet;
workBook.RemoveSheetAt(0);// 这个是必须得，要不然，一直存在sheet1
XSSFSheet workSheet1 = (XSSFSheet)workBook.CreateSheet("工作簿1");

//标题
IRow frow = workSheet1.CreateRow(0);
frow.CreateCell(0).SetCellValue( typeName + "- 结果导出");
workSheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet1.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());

}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet1.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}
}
}
{
string sql = $"这里是SQL语句''";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet2 = (XSSFSheet)workBook.CreateSheet("工作簿2");
//标题
IRow frow = workSheet2.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet2.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet2.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}
}

}
{
string sql = $"这里是sql语句''";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet3 = (XSSFSheet)workBook.CreateSheet("工作簿3");
//标题
IRow frow = workSheet3.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet3.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet3.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet3.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}
}

}
{
string sql = $"这里是sql语句";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet4 = (XSSFSheet)workBook.CreateSheet("工作簿4");
//标题
IRow frow = workSheet4.CreateRow(0);
frow.CreateCell(0).SetCellValue( typeName + "- 结果导出");
workSheet4.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet4.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());

}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet4.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}
}
}

if (!Directory.Exists(fileNameDir + "\\Upload\\SearchFile\\" + typeName))
{
Directory.CreateDirectory(fileNameDir + "\\Upload\\SearchFile\\" + typeName);
}
string saveFileName = fileNameDir + "\\Upload\\SearchFile\\" + typeName + "\\" + xqname + typeName + ".xlsx";


if (System.IO.File.Exists(saveFileName))
{
//如果存在则删除
System.IO.File.Delete(saveFileName);
}
FileStream wexcelstrem = new FileStream(Path.GetFullPath(saveFileName), FileMode.Create);
workBook.Write(wexcelstrem);
excelstream.Close();
wexcelstrem.Close();
daochu_result = "Success";
}

}
catch (Exception e)
{
daochu_result = "Error";
}
return daochu_result;
}

Apache poi workbookfactory 从 xlsx 创建 HSSFWorkbook，但我希望 XSSFWorkbook

Apache poi workbookfactory 从 xlsx 创建 HSSFWorkbook，但我希望 XSSFWorkbook

如何解决Apache poi workbookfactory 从 xlsx 创建 HSSFWorkbook，但我希望 XSSFWorkbook？

我正在尝试通过以下调用从 file.xlsx 创建 XSSFWorkbook：

1. XSSFWorkbookFactory.create(file) -> return hssfWorkbook
2. WorkbookFactory.createXSSFWorkbook(file) -> While trying to invoke ''createWorkbook'' on factory org.apache.poi.xssf.usermodel.XSSFWorkbookFactory and arguments [path to file.xlsx]
3. OPCPackage.open(file) -> The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg hssf instead of XSSF)
   XSSFWorkbook wb = new XSSFWorkbook(pkg);
4. XSSFWorkbookFactory.createWorkbook(new FileInputStream(file)) -> The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg hssf instead of XSSF)

我需要将文件读取为 XSSFWorkbook 以读取和设置图表的新参数。 任何想法，为什么我会发现此错误以及为什么从第 1 项开始返回 hssfWorkbook？

我检查了几次，文件的扩展名为 .xlsx。

解决方法

暂无找到可以解决该程序问题的有效方法，小编努力寻找整理中！

如果你已经找到好的解决方法，欢迎将解决方案带上本链接一起发送给小编。

小编邮箱:dio#foxmail.com (将#修改为@）

HSSFWorkbook 导出excel java

HSSFWorkbook 导出excel java

public String exportExcelList(){
//创建webbook,对应一个excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//在webbook中添加一个sheet,对应excel中的sheet
HSSFSheet sheet = wb.createSheet("检查单");
//在sheet中添加表头,添加一行
HSSFRow row = sheet.createRow(0);
//设置每一列的宽,此处可根据实际情况设置.
sheet.setColumnWidth(1, 20*256);
sheet.setColumnWidth(6, 20*256);
sheet.setColumnWidth(7, 20*256);
sheet.setColumnWidth(8, 20*256);
sheet.setColumnWidth(11, 20*256);
sheet.setColumnWidth(12, 20*256);
sheet.setColumnWidth(13, 20*256);
//设置表格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//内容向左靠齐
Font font = wb.createFont();//为字体设计样式
font.setFontHeightInPoints((short)12); //字体大小
font.setFontName("宋体");
style.setFont(font);//将字体加入到表格样式中
//设置列名称(第一列)
HSSFCell cell = row.createCell(0);
cell.setCellValue("id");//列名称
cell.setCellStyle(style);//列样式
//第二列
cell = row.createCell(1);
cell.setCellValue("syncDate");
cell.setCellStyle(style);
.........
//查出你要的数据
List laboratoryList = laboratoryInformationService.getInformationByTime(timeHelper);
//遍历数据
for(int i=0;i<laboratoryList.size();i++){
//创建行
row = sheet.createRow(i+1);
Map<String,Object> laboratoryMap = new HashMap<String,Object>();//临时变量
laboratoryMap = (Map<String, Object>) laboratoryList.get(i);
//给每一行设置单元格
//第一列
cell = row.createCell(0);//列索引
cell.setCellValue(laboratoryMap.get("id").toString());//单元格内容
cell.setCellStyle(style);//列的样式
//第二列
cell = row.createCell(1);
cell.setCellValue(laboratoryMap.get("syncDate").toString());
cell.setCellStyle(style);
............
}
//将excel的数据写入文件
HttpServletResponse res = ServletActionContext.getResponse();
res.setContentType("octets/stream");
String excelName = "检查单";//文件名字
//转码防止乱码
try {
res.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
OutputStream os;
try {
os = res.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();

}
System.out.println("excel导出成功");
return null;//此处一定要为null否则报错
}

 

今天关于七、SXSSFWorkbook生成大excle，避免内存溢出的介绍到此结束，谢谢您的阅读，有关.net单个sheet使用XSSFWorkbook导出excel表格、.net多个sheet使用XSSFWorkbook导出excel表、Apache poi workbookfactory 从 xlsx 创建 HSSFWorkbook，但我希望 XSSFWorkbook、HSSFWorkbook 导出excel java等更多相关知识的信息可以在本站进行查询。

本文标签：

上一篇基于 docker 搭建 wordpress 博客网站平台（docker部署wordpress）

下一篇662. Maximum Width of Binary Tree二叉树的最大宽度（二叉树最大宽度算法）

相关文章