1
Vote

Strange file error using package.Workbook.Worksheets[i].Hidden

description

First of all, i think the problem is in excel reading the file, but i have to detail a little.
I use this great library to open xlsx file from asp.net, add data and stream the file to the client with no update on disk; works like a charm except...
In some cases i have to hide a sheet depending on user selection and i used package.Workbook.Worksheets[1].Hidden = eWorkSheetHidden.Hidden or VeryHidden.
If i send the file with a sheet Hidden and open it, Excel show it but as i print preview the application crashes.
If instead of print preview i save the file to desktop, close excel and re-open it again and print preview, 90% cases no error.

Luckily (for me) i can delete the sheet instead of hiding, so i bypass the problem; however i wanted to detail it.

thanks,
Fabrizio

file attachments

comments

JanKallman wrote Jan 16, 2011 at 5:42 PM

Ok, i'll have a look at it.
(This could be related to your printer driver, so try to update it to the latest version or try it on a different printer type to see if you have the same problem.)

Faber75 wrote Jan 18, 2011 at 4:39 PM

Hi, thanks for your answer, i tried this on multiple printers and i get the same error, for testing purposes i paste the code and attach the 'model' i use (except some private labels/logos) to build the resulting file that crashes excel on print preview.
the logic of the function is that i have a file with a single sheet and i receive a datatable with as many rows as the pages i have to print; i use the main sheet as the model and paste it in the file filling it with the data of each row. In the end i hide the initial sheet.
Fabrizio
    public static string PraticaIntercalare(string NomeOutput, dsSession ds)
    {
        string err = "";
        try
        {
            FileInfo file = new FileInfo(HttpContext.Current.Request.PhysicalApplicationPath + "App_Doc\\StampaPraticheIntercalare.xlsx");
            HttpResponse response = HttpContext.Current.Response;
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[1];
                foreach (dsSession.PraticheIntercalareRow row in ds.PraticheIntercalare)
                {
                    ws = package.Workbook.Worksheets.Add("Intercalare" + package.Workbook.Worksheets.Count.ToString(),
                        package.Workbook.Worksheets[1]);

                    ws.Cells[11, 11].Value = row.Intercalare;
                    ws.Cells[22, 12].Value = row.CodCliente;
                    ws.Cells[22, 13].Value = row.RagSocCliente;
                    ws.Cells[28, 12].Value = row.OggettoPratica;
                    ws.Cells[44, 12].Value = row.CodProf1;
                    ws.Cells[44, 13].Value = row.CodPraticaAttiva;
                    ws.Cells[44, 15].Value = row.CodFascicolo;
                    ws.Cells[44, 16].Value = row.CodProf2;
                }

                package.Workbook.Worksheets[1].Hidden = eWorkSheetHidden.Hidden;
                MemoryStream ms = new MemoryStream();
                package.SaveAs(ms);
                response.Clear();
                response.AddHeader("Content-Disposition", "attachment; filename=\"" + NomeOutput + "\"");
                response.AddHeader("Content-Length", ms.Length.ToString());
                response.ContentType = "application/octet-stream";
                response.BinaryWrite(ms.ToArray());
            }
        }
        catch (Exception ex)
        {
            err = ex.ToString();
        }
        return err;
    }

DerekJarvis wrote Sep 14, 2014 at 12:04 AM

I compared XML code saved from EPPlus to Excel and found that <workbookView ...> in workbook.xml is missing firstSheet="1" activeTab="1".

When we hide anything but the first worksheet, Excel figures it out. But if we hide the first one - it doesn't understand. I added this into the saved EPPlus file manually and it fixed the bug.

I'll leave code implementation to the other developers.

GertVen wrote Mar 1 at 7:55 AM

I am also getting this error using EPPlus 4.1.0. Has a solution/workaround been implemented?

My code (VB.Net 2015):
        Dim tblData As DataTable = New DataTable
        tblData.Columns.Add("Test1")
        tblData.Columns.Add("Test2")
        tblData.Columns.Add("Test3")
        tblData.Rows.Add("1", "2", "3")
        tblData.Rows.Add("2", "3", "4")
        tblData.Rows.Add("3", "4", "5")

        ' Create the excel document
        Dim epContainer As New OfficeOpenXml.ExcelPackage
        epContainer.Workbook.CreateVBAProject()

        ' Create the hidden worksheet
        Dim wsPivot = epContainer.Workbook.Worksheets.Add("Hidden")
        wsPivot.Hidden = OfficeOpenXml.eWorkSheetHidden.VeryHidden

        ' Create the data worksheet
        Dim epWorksheet As OfficeOpenXml.ExcelWorksheet = epContainer.Workbook.Worksheets.Add("Table")
        Dim datarange = epWorksheet.Cells("A1").LoadFromDataTable(tblData, True, OfficeOpenXml.Table.TableStyles.Light15)
        epWorksheet.Cells(epWorksheet.Dimension.Address).AutoFitColumns()
        epWorksheet.Select()

        Dim sFileName As String = "C:\Users\Public\Test.xlsm"

        If File.Exists(sFileName) = True Then
            File.Delete(sFileName)
        End If

        Using sStream As System.IO.FileStream = New System.IO.FileStream(sFileName, FileMode.CreateNew)
            epContainer.SaveAs(sStream)
        End Using