如何在C＃中写入excel文件时解决磁盘空间问题异常的异常？ [英] How to resolve the exception of the disk space issue exception while writing to a excel file in C#?

问题描述

在服务器中部署时，在c＃中写入excel文件时遇到以下异常。本地没有问题。



异常：



由于没有足够的可用内存或磁盘空间，Microsoft Excel无法打开或保存更多文档。



要提供更多可用内存，请关闭不再需要的工作簿或程序。



•要释放磁盘空间，请从您要保存的磁盘中删除不再需要的文件



我的尝试：



核心代码如下：

 _Application xlApp = new Microsoft.Office.Interop.Excel.Application（）; 
 object misValue = System.Reflection.Missing.Value; 
 _Workbook xlWorkBook = null; 
 _Worksheet xlWorkSheet = null; 
 string strFileName = string.Empty; 
 try 
 {
 
 string ExcelTemplateFullPath = string.Empty; 
 string ExcelExtension = string.Empty; 
 string DestinationFullPathExcel = string.Empty; 
 
 if（File.Exists（templatePath +CustomerTemplate+。xls））
 {
 Logger.Log（Started generated .xls File，LogFilename.ServiceLog ，3，MethodInfo.GetCurrentMethod（）。Name）; 
 ExcelExtension =。xls; 
 ExcelTemplateFullPath = templatePath +CustomerTemplate+ ExcelExtension; 
 strFileName = filename + ExcelExtension; 
 DestinationFullPathExcel = DestinationPDFPath + strFileName; 
 
} 
 else if（File.Exists（templatePath +CustomerTemplate+。xlsx））
 {
 Logger.Log（开始生成.xlsx文件，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 ExcelExtension =。xlsx; 
 ExcelTemplateFullPath = templatePath +CustomerTemplate+ ExcelExtension; 
 strFileName = filename + ExcelExtension; 
 DestinationFullPathExcel = DestinationPDFPath + strFileName; 
 
} 
 else 
 {
 Logger.Log（文件夹中不存在模板：+ templatePath，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod （）。名称）; 
返回strFileName; 
} 
 
 
 
 xlApp.DisplayAlerts = false; 
 // object misValue = System.Reflection.Missing.Value; 
 // _Workbook xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 Logger.Log（已启动excel模板，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 // xlWorkBook = xlApp.Workbooks.Open（ExcelTemplateFullPath，0，true，5，，，true，Microsoft.Office.Interop.Excel.XlPlatform.xlWindows，\ t，false， false，0，true，1,0）; 
 try 
 {
 xlWorkBook = xlApp.Workbooks.Add（ExcelTemplateFullPath）; 
} 
 catch（exception ex）
 {
 Logger.Log（无法打开ExcelTemplate，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 Logger.Log（例如，LogFilename.ServiceLog，1，MethodInfo.GetCurrentMethod（）。Name，false）; 
 
} 
 Logger.Log（打开的excel模板，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 // _Worksheet xlWorkSheet =（_ workheet）xlWorkBook.Worksheets.get_Item（1）; 
 try 
 {
 xlWorkSheet =（_ workheet）xlWorkBook.Worksheets.get_Item（1）; 
 for（int i = 0; i< Values.Length; i ++）
 {
 string [] val = Values [i] .Split（delimiter1）; 
 
 for（int j = 0; j< val.Length; j ++）
 {
 int k = 0; 
 k = i + 1; 
 //xlWorkSheet.Cells [k + 1，j + 1] ='+ val [j]; 
 xlWorkSheet.Cells [k + 1，j + 1] = val [j]; 
 
} 
 
} 
} 
 catch（exception ex）
 {
 Logger.Log（无法提取ExcelTemplate ，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 Logger.Log（例如，LogFilename.ServiceLog，1，MethodInfo.GetCurrentMethod（）。Name，false）; 
} 
 Logger.Log（Extracted excel Template，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 try 
 {
 xlWorkBook.SaveAs（DestinationFullPathExcel，XlFileFormat.xlWorkbookNormal，Type.Missing，Type.Missing，false，Type.Missing，XlSaveAsAccessMode.xlShared，Type.Missing，Type.Missing， Type.Missing，Type.Missing，Type.Missing）; 
} 
 catch（exception ex）
 {
 Logger.Log（无法保存生成的ExcelTemplate，LogFilename.ServiceLog，3，MethodInfo.GetCurrentMethod（）。Name）; 
 Logger.Log（例如，LogFilename.ServiceLog，1，MethodInfo.GetCurrentMethod（）。Name，false）; 
} 
 
 
 xlWorkBook.Close（true，misValue，misValue）; 
 xlApp.Quit（）; 
 
 releaseObject（xlWorkSheet）; 
 releaseObject（xlWorkBook）; 
 releaseObject（xlApp）; 
 
 
} 
 
 catch（exception ex）
 {
 xlWorkBook.Close（true，misValue，misValue）; 
 xlApp.Quit（）; 
 
 releaseObject（xlWorkSheet）; 
 releaseObject（xlWorkBook）; 
 releaseObject（xlApp）; 
 throw（ex）; 
} 
返回strFileName; 
 

解决方案

您已将此问题标记为ASP.NET; ASP.NET或任何其他非交互式进程不支持Office Interop：

关于服务器端Office自动化的思考 [ ^ ]

Microsoft目前不建议，也不支持从任何无人参与的非交互式客户端应用程序或组件（包括ASP，ASP.NET）自动化Microsoft Office应用程序，DCOM和NT服务），因为Office在此环境中运行时可能会出现不稳定的行为和/或死锁。

有多种方式在服务器上创建Excel电子表格而不使用Office互操作。例如：

• EPPlus [ ^ ];
• ClosedXML [ ^ ];
• OpenXML SDK [ ^ ];

I am getting the below exception while writing to a excel file in c# while deploying in a server.Locally no issue as such.

Exception:

Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space.

To make more memory available, close workbooks or programs you no longer need.

• To free disk space, delete files you no longer need from the disk you are saving to

What I have tried:

Core Code is as below:

_Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object misValue = System.Reflection.Missing.Value;
_Workbook xlWorkBook = null;
_Worksheet xlWorkSheet = null;
string strFileName = string.Empty;
try
{

    string ExcelTemplateFullPath = string.Empty;
    string ExcelExtension = string.Empty;
    string DestinationFullPathExcel = string.Empty;

    if (File.Exists(templatePath + "CustomerTemplate" + ".xls"))
    {
        Logger.Log("Started generating .xls File ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        ExcelExtension = ".xls";
        ExcelTemplateFullPath = templatePath + "CustomerTemplate" + ExcelExtension;
        strFileName = filename + ExcelExtension;
        DestinationFullPathExcel = DestinationPDFPath + strFileName;

    }
    else if (File.Exists(templatePath + "CustomerTemplate" + ".xlsx"))
    {
        Logger.Log("Started generating .xlsx File ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        ExcelExtension = ".xlsx";
        ExcelTemplateFullPath = templatePath + "CustomerTemplate" + ExcelExtension;
        strFileName = filename + ExcelExtension;
        DestinationFullPathExcel = DestinationPDFPath + strFileName;

    }
    else
    {
        Logger.Log("Template doesn't exists in the Folder: " + templatePath, LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        return strFileName;
    }



    xlApp.DisplayAlerts = false;
    //object misValue = System.Reflection.Missing.Value;
    // _Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    Logger.Log("Started excel Template ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
    //xlWorkBook = xlApp.Workbooks.Open(ExcelTemplateFullPath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    try
    {
        xlWorkBook = xlApp.Workbooks.Add(ExcelTemplateFullPath);
    }
    catch(Exception ex)
    {
        Logger.Log("Failed to open ExcelTemplate ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        Logger.Log(ex, LogFilename.ServiceLog, 1, MethodInfo.GetCurrentMethod().Name, false);

    }
    Logger.Log("Opened  excel Template ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
   // _Worksheet xlWorkSheet = (_Worksheet)xlWorkBook.Worksheets.get_Item(1);
    try
    {
        xlWorkSheet = (_Worksheet)xlWorkBook.Worksheets.get_Item(1);
        for (int i = 0; i < Values.Length; i++)
        {
            string[] val = Values[i].Split(delimiter1);

            for (int j = 0; j < val.Length; j++)
            {
                int k = 0;
                k = i + 1;
                //xlWorkSheet.Cells[k + 1, j + 1] = "'" + val[j];
                xlWorkSheet.Cells[k + 1, j + 1] = val[j];

            }

        }
    }
    catch (Exception ex)
    {
        Logger.Log("Failed to extract ExcelTemplate ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        Logger.Log(ex, LogFilename.ServiceLog, 1, MethodInfo.GetCurrentMethod().Name, false);
    }
    Logger.Log("Extracted  excel Template ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
    try
    {
        xlWorkBook.SaveAs(DestinationFullPathExcel, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    }
    catch (Exception ex)
    {
        Logger.Log("Failed to save generated ExcelTemplate ", LogFilename.ServiceLog, 3, MethodInfo.GetCurrentMethod().Name);
        Logger.Log(ex, LogFilename.ServiceLog, 1, MethodInfo.GetCurrentMethod().Name, false);
    }


    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);


}

catch (Exception ex)
{
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
    throw (ex);
}
return strFileName;

解决方案

You've tagged this question as ASP.NET; Office Interop is not supported in ASP.NET, or any other non-interactive process:

Considerations for server-side Automation of Office[^]

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

There are various ways to create Excel spreadsheets on the server without using Office interop. For example:

• EPPlus[^];
• ClosedXML[^];
• The OpenXML SDK[^];

这篇关于如何在C＃中写入excel文件时解决磁盘空间问题异常的异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！