Search code examples
c#npoi

NPOI excel open issue

I'm struggling on an issue for many hours but have no idea where is the problem and how to fix it. Hope you could give me some hints. I would much appreciate.

The main idea of the following code is to read document in documentlist and open each document (excel format) and move sheets to destWorkbook, and finaly save destWorkbook into excel format. 

public bool GenerateMergedWorkbook(DocumentModelList documentlist)
{
    HSSFWorkbook destWorkbook = new HSSFWorkbook();
    IList<DocumentModel> downloadDocumentList = GetDownloadDocumentList(documentlist);
    foreach (var document in downloadDocumentList)
    {
        if (!string.IsNullOrEmpty(document.documentpath))
        {
            MergeSheet(document.documentpath, document.code, destWorkbook);
        }
    }

    string destDocumentPath = HttpContext.Current.Server.MapPath("~/UserTemplate/xxx.xls");
    destWorkbook.Write(new FileStream(destDocumentPath, FileMode.Create, FileAccess.ReadWrite));
    destWorkbook.Close();
    destWorkbook = null;
    return true;
}

private void MergeSheet(string documentPath, string code, HSSFWorkbook destWorkbook)
{
    string filePath = HttpContext.Current.Server.MapPath(documentPath);
    using (var fileStream = new FileStream(filePath, FileMode.Open))
    { 
            HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
            bool moveSheetFlag = false;

            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
                if (sheet.SheetName.IndexOf(code) >= 0)
                {
                    sheet.CopyTo(destWorkbook, sheet.SheetName, true, true);
                    moveSheetFlag = true;
                    break;
                }
            }
            workbook.Close();
            if (!moveSheetFlag)
            {
                string message = "xxxxxx";
                throw new FormatException(message);
            }
            fileStream.Close();
    }
}

and above code could generate the excel with the name 'xxx.xls', but when i'm trying to open the file, it will pop up the message like below, enter image description here enter image description here

I did the investigation for many hours, still no idea, could someone help me out. Thanks a lot.

Solution

  • I found the answer suddenly, 

    destWorkbook.Write(new FileStream(destDocumentPath, FileMode.Create, FileAccess.ReadWrite));

    should close stream like below,

     var stream = new FileStream(destDocumentPath, FileMode.Create, FileAccess.ReadWrite);
            destWorkbook.Write(stream);
            stream.Close();