Paste or Ignore Data in Hidden Range

In This Topic

DsExcel allows you to choose whether to copy and paste the data in a hidden range. The parameters of overloaded Copy method can be used to specify the destination where the copied data needs to be pasted, whether to paste the data in a hidden range and various paste types.

The pasteOption parameter of Copy method belongs to the PasteOption class. This class provides the AllowPasteHiddenRange property which if true, will paste the data in a hidden range to the destination, else will ignore the hidden range. The default value is true.

The PasteOption class also provides PasteType property which can be used to specify various paste types by setting it to any PasteType enumeration value.

The below table explains different options which can be used to specify the paste type using PasteType enumeration:

Option	Description
Default	Pastes all the cell data to the destination range except the row heights and column widths.
Values	Pastes only the cell value to the destination.
Formulas	If you're working in a formula cell, it pastes the formula to the destination . However, for a non-formula cell, it pastes the cell value to the destination.
Formats	Pastes formats.
NumberFormats	Pastes number formats.
RowHeights	Pastes the row height to the destination.
ColumnWidths	Pastes the column width to the destination.

You can also combine two different paste type options. For example, if you want to paste values and number formats concurrently in a worksheet, you can use any of the below combinations:

PasteType.Values | PasteType.NumberFormats

PasteType.Formulas | PasteType.NumberFormats

Refer to the following example code to ignore and paste data in a hidden range. It also uses the combination of paste options while copying and pasting data.

C#	Copy Code
var workbook = new Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; object[,] data = new object[,]{ {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165}, {"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134}, {"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180}, {"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163}, {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176}, {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145} }; worksheet.Range["A1:F6"].Value = data; worksheet.Range["A:F"].ColumnWidth = 15; worksheet.Range["1:1"].Hidden = true; worksheet.Range["3:3"].Hidden = true; worksheet.Range["5:5"].Hidden = true; //Ignore pasting data in hidden range var worksheet2 = workbook.Worksheets.Add(); worksheet.Range["A1:F6"].Copy(worksheet2.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false }); //Paste data in hidden range var worksheet3 = workbook.Worksheets.Add(); worksheet.Range["A1:F6"].Copy(worksheet3.Range["A1:F6"]); //Ignore pasting data in hidden range and use PasteType options var worksheet4 = workbook.Worksheets.Add(); worksheet.Range["A1:F6"].Copy(worksheet4.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false, PasteType = PasteType.ColumnWidths \| PasteType.Values }); //save to an excel file workbook.Save("IgnoreorPasteDataHiddenRange.xlsx");

Copy Code

var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

object[,] data = new object[,]{
{"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165},
{"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134},
{"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163},
{"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176},
{"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145}
};

worksheet.Range["A1:F6"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["1:1"].Hidden = true;
worksheet.Range["3:3"].Hidden = true;
worksheet.Range["5:5"].Hidden = true;
        
//Ignore pasting data in hidden range
var worksheet2 = workbook.Worksheets.Add();
worksheet.Range["A1:F6"].Copy(worksheet2.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false });
       
//Paste data in hidden range
var worksheet3 = workbook.Worksheets.Add();
worksheet.Range["A1:F6"].Copy(worksheet3.Range["A1:F6"]);

//Ignore pasting data in hidden range and use PasteType options
var worksheet4 = workbook.Worksheets.Add();
worksheet.Range["A1:F6"].Copy(worksheet4.Range["A1:F6"], new PasteOption { AllowPasteHiddenRange = false, PasteType = PasteType.ColumnWidths | PasteType.Values });

//save to an excel file
workbook.Save("IgnoreorPasteDataHiddenRange.xlsx");