B. Clay Shannon B. Clay Shannon - 1 year ago 231
C# Question

How can I prevent the left "gutter" (row number) column from printing (EPPlus)?

The "Print Preview" of a spreadsheet created with my EPPlus code shows the gutter/row number column (column 0, so to speak):

enter image description here

How can I programmatically prevent the gutter/row number ("crack?") column from printing?

My printing setup code is currently as follows:

private void ConfigureCustomerSheetForPrinting()
string columnName = GetExcelTextColumnName(customerWorksheet.Dimension.End.Column);
string printArea = string.Format("A1:{0}{1}", columnName, customerWorksheet.Dimension.End.Row);
customerWorksheet.PrinterSettings.PrintArea = customerWorksheet.Cells[printArea];
customerWorksheet.PrinterSettings.FitToPage = true;
customerWorksheet.PrinterSettings.Orientation = eOrientation.Landscape;
customerWorksheet.View.ZoomScale = 100;
customerWorksheet.PrinterSettings.FitToPage = true;
customerWorksheet.PrinterSettings.FitToHeight = 100;
customerWorksheet.PrinterSettings.Scale = 100;

customerWorksheet.PrinterSettings.LeftMargin = (decimal).5 / 2.54M;
customerWorksheet.PrinterSettings.RightMargin = (decimal).5 / 2.54M;
customerWorksheet.PrinterSettings.TopMargin = (decimal).5 / 2.54M;
customerWorksheet.PrinterSettings.BottomMargin = (decimal).5 / 2.54M;
customerWorksheet.PrinterSettings.HeaderMargin = (decimal).5 / 2.54M;
customerWorksheet.PrinterSettings.FooterMargin = (decimal).5 / 2.54M;

Answer Source

If I understand what you are asking for you want to show the Column headers ( say "Row 0" as "A", "B", "C", ...) but NOT show the Row headers (say "Column 0" as "1", "2" 3", etc.). If so, I have never seen a way to do it. You can hide both row/col header or show both but not either-or according to the DocumentFormat.OpenXml.Spreadsheet.PrintOptions. Basically the ShowHeaders option you and Richardo talked about.

The only decent work around I can think of is to fake it with something like this. This includes setting the first row as a repeat. I set the first row as frozen as well but this is optional:

using (var pck = new ExcelPackage(fi))
    var wb = pck.Workbook;
    var ws = wb.Worksheets.Add("Sheet1");

    //Make sure headers are not show
    ws.PrinterSettings.ShowHeaders = false;

    ws.Cells[1, 1].Value = "A";
    ws.Cells[1, 2].Value = "B";
    ws.Cells[1, 3].Value = "C";
    ws.Cells[1, 4].Value = "D";

    var headerrange = ws.Cells[1, 1, 1, 4];
    headerrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    headerrange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
    headerrange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
    headerrange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
    headerrange.Style.Border.Right.Style = ExcelBorderStyle.Thin;

    ws.PrinterSettings.RepeatRows = new ExcelAddress("$1:$1");

    //Some data > 1 page
    for (var i = 0; i < 1000; i++)
        ws.Cells[2 + i, 1].Value = DateTime.Now.AddDays(i);
        ws.Cells[2 + i, 2].Value = i;
        ws.Cells[2 + i, 3].Value = i*100;
        ws.Cells[2 + i, 4].Value = Path.GetRandomFileName();

    //Save it

Which gives this in the output:

enter image description here

And this in print preview (I scrolled down a few pages):

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download