Why EPPLUS removes my excel file?

Question

I am using epplus library to read/write an excel file. My program writes data to excel file every X seconds (automatically write to excel).

And a few days ago, when the number of data in excel up to approximately 2800, my excel file was deleted, I don't know why. I know because my program give me exception message, and I check my excel file was deleted.

Then I read epplus source code, there is a procedure to remove excel file in my opinion. This is the sample code

public void Save()
        {
            try
            {
                Workbook.Save();
                if (File == null)
                {
                    if(Encryption.IsEncrypted)
                    {
#if !MONO
                        var ms = new MemoryStream();
                        _package.Save(ms);
                        byte[] file = ms.ToArray();
                        EncryptedPackageHandler eph = new EncryptedPackageHandler();
                        var msEnc = eph.EncryptPackage(file, Encryption);
                        CopyStream(msEnc, ref _stream);
#endif
#if MONO
                        throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                    }
                    else
                    {
                        _package.Save(_stream);
                    }
                    _stream.Flush();
                    _package.Close();
                }
                else
                {
                    if (System.IO.File.Exists(File.FullName))
                    {
                        try
                        {
                            System.IO.File.Delete(File.FullName);
                        }
                        catch (Exception ex)
                        {
                            throw (new Exception(string.Format("Error overwriting file {0}", File.FullName), ex));
                        }
                    }

                    _package.Save(_stream);
                    _package.Close();
                    if (Stream is MemoryStream)
                    {
                        var fi = new FileStream(File.FullName, FileMode.Create);
                        //EncryptPackage
                        if (Encryption.IsEncrypted)
                        {
#if !MONO
                            byte[] file = ((MemoryStream)Stream).ToArray();
                            EncryptedPackageHandler eph = new EncryptedPackageHandler();
                            var ms = eph.EncryptPackage(file, Encryption);

                            fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
#endif
#if MONO
                            throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                        }
                        else
                        {                            
                            fi.Write(((MemoryStream)Stream).GetBuffer(), 0, (int)Stream.Length);
                        }
                        fi.Close();
                    }
                    else
                    {
                        System.IO.File.WriteAllBytes(File.FullName, GetAsByteArray(false));
                    }
                }
            }
            catch (Exception ex)
            {
                if (File == null)
                {
                    throw;
                }
                else
                {
                    throw (new InvalidOperationException(string.Format("Error saving file {0}", File.FullName), ex));
                }
            }
        }

So, was my excel file removed? And what is the reason epplus deletes excel files? (according to this sample code) These are some exception

System.InvalidOperationException: Error saving file E:\Test\Morning.xlsx --->
System.Xml.XmlException: There is an unclosed literal string. Line 1, position 8190.
at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
at System.Xml.XmlTextReaderImpl.ParseAttributeValueSlow(Int32 curPos, Char quoteChar, NodeData attr)
at System.Xml.XmlTextReaderImpl.ParseAttributes()
at System.Xml.XmlTextReaderImpl.ParseElement()
at System.Xml.XmlTextReaderImpl.ParseElementContent()
at OfficeOpenXml.ExcelWorksheet.LoadCells(XmlTextReader xr)
at OfficeOpenXml.ExcelWorksheet.CreateXml()
at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, 
ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, 
Int32 aheetID, Int32 positionID, eWorkSheetHidden hide)
at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage pck,
XmlNamespaceManager nsm, XmlNode topNode)
at OfficeOpenXml.ExcelWorkbook.get_Worksheets()
at OfficeOpenXml.ExcelWorkbook.Save()
at OfficeOpenXml.ExcelPackage.Save()
--- End of inner exception stack trace ---
at OfficeOpenXml.ExcelPackage.Save()
at Monitoring.FMain.SaveEPPLUS()

...

System.ArgumentOutOfRangeException: Index was out of range. 
Must be non-negative and less than the siza of the collection

You open, read, process, write the file. The total time it takes for this is X seconds. But what if you trigger this entire set of steps again before the first run is done and it takes X+1 seconds? You really need to get down the non-automation processing of all of this and set debugging output to narrow down your problem. Even consider what happens if the conversion errors out. Write to a log file and narrow the problem down. — Kerry Kobashi, Feb 15 '15 at 07:32
That looks pretty bad, deleting the original file and then saving almost inevitably causes data loss. You need to work around this by saving to a temporary file first. Same approach as [shown here](http://stackoverflow.com/a/28452524/17034). — Hans Passant, Feb 15 '15 at 10:35
@HansPassant: i don't delete file. i created epplus object, write data to epplus object, and then save it using epplus method. Always like that. My problem is, my file automatically deleted, when my data in excel up to approximately 2800 rows. So i read epplus source code, and i read there is a mechanism to delete file. My questions are, what the mechanism for? And what the reason my file was deleted. — Anti Mafia, Feb 15 '15 at 16:11
It doesn't sound like you understand the failure mode yet. You got in trouble with "read/write an excel file". When you read the file, make changes, save it back with the *same name* then you'll lose the file when the save failed. If you have no backup of the original then the data is irretrievably lost. The workaround is to *not* save it back with the same name. So you won't overwrite the original file. — Hans Passant, Feb 15 '15 at 16:43
@HansPassant: Oh okay i understand. But i am stil don't understand how the mechanism of temporary file, can you give me reference with C# sir? Or explain me a little :) — Anti Mafia, Feb 15 '15 at 16:53
What Hans said. It only appears that it is being deleted because EPP reads the file into a Stream, makes changes to Stream, deletes the file, and THEN writes a new file with same name. That last part is where it fails. But without more detail its hard to say what the problem is. What is the nature of the data in the excel file? At line ~2800 does it have data that is different then then the prior? XML literal which implies that there is something with the data itself causing the XML engine or EPPlus itself to error - XLSX files are just ZIP XML files so it implys content problem. — Ernie S, Feb 15 '15 at 16:55
@Ernie: What the means of different then the prior sir? I want to make software that can write and read data into COM port. The serial data which received, saved in excel file. I want test it before using the real hardware. i test it with 4 different data. Example write data to excel every 10second, at sec 10, 20, 30, 40 data will be different, at sec 50 data is same with at second 10 — Anti Mafia, Feb 15 '15 at 17:09
It doesnt sound to complex a datset. Could you post a unit test or some kind of method that reproduces the data and maybe even the error? — Ernie S, Feb 15 '15 at 18:00
i can write data up to +- 2800 sir. And after i got error, i create a new excel file, and then manually write data to excel up to +- 3000 rows, then i write to that file and success. If my method cause an error, i think i will get error when write data at first. Sir, in epplus source code, there is a mechanism to delete file right? I don't understand what the mecanism for — Anti Mafia, Feb 15 '15 at 18:17
my mean after manually write data to excel file up to +- 3000 rows, then i write to that file using my program (automatically using EPPLUS), and success. — Anti Mafia, Feb 15 '15 at 18:28
And the data in your test method is exactly the same as what the automated process is writing? If the data is not the problem then it has to be something why the automated process. Does adjusting the time between saves make a difference? Say, try 20 seconds, 5 seconds, etc? As for how does it delete, it says it right in the code you posted - it uses the native .NET IO method "System.IO.File.Delete(File.FullName);" — Ernie S, Feb 15 '15 at 18:44
Anti, what I am talking about is that if it takes say, 5 seconds to save a file, and if you have a loop going that ask it to save again say, in 3 seconds, you are going to run into a race condition (timing problem). You would have to increase the time between saves. Hope that makes sense. — Kerry Kobashi, Feb 15 '15 at 19:10

score 0 · Answer 1 · answered Feb 25 '17 at 05:06

I would say that the problem is obviously in this code:

if (System.IO.File.Exists(File.FullName))
                    {
                        try
                        {
                            System.IO.File.Delete(File.FullName);
                        }
                        catch (Exception ex)
                        {
                            throw (new Exception(string.Format("Error overwriting file {0}", File.FullName), ex));
                        }
                    }

                    _package.Save(_stream);
                    _package.Close();
                    if (Stream is MemoryStream)
                    {
                        var fi = new FileStream(File.FullName, FileMode.Create);
                        //EncryptPackage
                        if (Encryption.IsEncrypted)
                        {
#if !MONO
                            byte[] file = ((MemoryStream)Stream).ToArray();
                            EncryptedPackageHandler eph = new EncryptedPackageHandler();
                            var ms = eph.EncryptPackage(file, Encryption);

                            fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
#endif
#if MONO
                            throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                        }
                        else
                        {                            
                            fi.Write(((MemoryStream)Stream).GetBuffer(), 0, (int)Stream.Length);
                        }
                        fi.Close();
                    }
                    else
                    {
                        System.IO.File.WriteAllBytes(File.FullName, GetAsByteArray(false));
                    }

You are deleting your file first then rewriting it, the case is deletion was successful and rewriting the file failed because of index was out of the range (the code attempted to write empty or not complete data to excel).

System.ArgumentOutOfRangeException: Index was out of range. 
Must be non-negative and less than the siza of the collection

I would recommend that you do not delete the file using your code, you might always need old data anyway.

I would change the above deletion code to:

if (System.IO.File.Exists(File.FullName))
                    {
                        File.FullName=//FileName+date.now;
                    }

This way if the file already exists, instead of deleting it, create a new file with another name, so you can guarantee that you do not lose old data. I would also recommend adding some conditions on the data size to be written so your code does not cause index out of range exception again.

Why EPPLUS removes my excel file?

1 Answers1