/// <summary> /// Initialize the worksheet with required details /// </summary> /// <returns></returns> private Worksheet SetWorksheetData() { Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Columns columns = new Columns(); Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 15U, CustomWidth = true }; Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; Column column3 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; Column column4 = new Column() { Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; columns.Append(column1); columns.Append(column2); columns.Append(column3); columns.Append(column4); SheetData sheetData = new SheetData(); SheetProtection sheetProtection = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true }; worksheet.Append(columns); worksheet.Append(sheetData); worksheet.Append(sheetProtection); return(worksheet); }
public static void Main(string[] args) { Simple.Run(); MultipleSheet.Run(); NumberFormats.Run(); ColumnFormatting.Run(); RowFormatting.Run(); Alignment.Run(); Border.Run(); DataValidation.Run(); RightToLeft.Run(); Zip64Small.Run(); SheetProtection.Run(); Large.Run(); StyledLarge.Run(); StyledLargeCreateStyles.Run(); Zip64Huge.Run(); }
/// <summary> /// Protect the sheet. /// </summary> /// <param name="Worksheet"></param> /// <param name="pageM"></param> /// <param name="sheetProtection"></param> /// <param name="pRanges"></param> /// <param name="lockedColumns"></param> private void ProtectSheet(Worksheet Worksheet, out PageMargins pageM, out SheetProtection sheetProtection, out ProtectedRanges pRanges, string[] editableColumns, DataTable dt) { pageM = Worksheet.GetFirstChild <PageMargins>(); sheetProtection = new SheetProtection(); sheetProtection.Sheet = true; sheetProtection.Objects = true; sheetProtection.Scenarios = true; // Set the password. sheetProtection.Password = new HexBinaryValue() { Value = "CC1A" }; pRanges = new ProtectedRanges(); if (editableColumns.Length > 0) { int i = 0; foreach (string columnName in editableColumns) { i++; ProtectedRange pRange = new ProtectedRange(); ListValue <StringValue> lValue = new ListValue <StringValue>(); // Get Excel Column Number. string columnindex = GetExcelColumnNumber(columnName, dt); lValue.InnerText = columnindex + "1:" + columnindex + (dt.Rows.Count + 1).ToString(); // Assign the editable columns. pRange.SequenceOfReferences = lValue; pRange.Name = "Editable_" + i.ToString(); pRanges.Append(pRange); } } }
public async Task <System.Net.Http.HttpResponseMessage> GenerateTemplate() { Contract.Ensures(Contract.Result <Task <HttpResponseMessage> >() != null); // get template entities var templateEntities = await this.GetNamedEntities(cTemplateEntityName); if (templateEntities == null) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } #region Generate Excel File OpenXMLSDK-MOT string fileSaveLocation = Path.GetTempFileName(); var properties = templateEntities.ElementType.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(p => p.Name != "ID"); using (var workbook = SpreadsheetDocument.Create(fileSaveLocation, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = CreateStylesheet(); stylesPart.Stylesheet.Save(); workbook.WorkbookPart.Workbook = new Workbook { Sheets = new Sheets() }; #region Sheet 1 WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>(); Worksheet workSheet1 = new Worksheet(); SheetData sheetData1 = new SheetData(); // the data for sheet 1 //Row rowInSheet1 = new Row(); //Cell emptyCell = CreateTextCell(cellHeader, index, ""); //rowInSheet1.Append(emptyCell); //sheetData1.Append(rowInSheet1); workSheet1.AppendChild(sheetData1); worksheetPart1.Worksheet = workSheet1; Sheet sheet1 = new Sheet() { Id = workbook.WorkbookPart.GetIdOfPart(worksheetPart1), SheetId = 1, Name = m_ExcelTemplateSheetOneName }; workbook.WorkbookPart.Workbook.Sheets.Append(sheet1); #endregion #region Sheet 2 WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>(); Worksheet workSheet2 = new Worksheet(); SheetFormatProperties sheetFormatProperties = new SheetFormatProperties() { DefaultColumnWidth = 25.00D, DefaultRowHeight = 0D }; workSheet2.SheetFormatProperties = sheetFormatProperties; SheetData sheetData2 = new SheetData(); // the data for sheet 2 Row headerRow = new Row(); List <String> columns = new List <string>(); foreach (PropertyInfo column in properties) { columns.Add(column.Name); Cell cell = new Cell { DataType = CellValues.String, CellValue = new CellValue(column.Name), StyleIndex = Convert.ToUInt32(1) }; headerRow.AppendChild(cell); } sheetData2.AppendChild(headerRow); foreach (var entity in templateEntities) { Row newRow = new Row(); foreach (string col in columns) { Cell cell = new Cell { DataType = CellValues.String }; object value = templateEntities.ElementType.GetProperty(col).GetValue(entity); cell.CellValue = new CellValue(value == null ? string.Empty : value.ToString()); newRow.AppendChild(cell); } sheetData2.AppendChild(newRow); } workSheet2.AppendChild(sheetData2); worksheetPart2.Worksheet = workSheet2; Sheet sheet2 = new Sheet() { Id = workbook.WorkbookPart.GetIdOfPart(worksheetPart2), SheetId = 2, Name = m_ExcelTemplateSheetTwoName }; workbook.WorkbookPart.Workbook.Sheets.Append(sheet2); #region Protection SheetProtection sheetProtection = new SheetProtection { Sheet = true, Objects = true, Scenarios = true, Password = GetSheetPassword(m_ExcelTemplateSheetPassword) }; worksheetPart2.Worksheet.InsertAfter(sheetProtection, sheetData2); #endregion #endregion } var bytes = File.ReadAllBytes(fileSaveLocation); File.Delete(fileSaveLocation); var stream = new MemoryStream(bytes); if (stream == null) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } #endregion var mediaNameStr = "Template.xlsx"; var mediaTypeStr = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var mediaType = new MediaTypeHeaderValue(mediaTypeStr); // get the range and stream media type var range = this.Request.Headers.Range; HttpResponseMessage response; if (range == null) { // if the range header is present but null, then the header value must be invalid if (this.Request.Headers.Contains("Range")) { return(this.Request.CreateErrorResponse(HttpStatusCode.RequestedRangeNotSatisfiable, "GenerateTemplate")); } // if no range was requested, return the entire stream response = this.Request.CreateResponse(HttpStatusCode.OK); response.Headers.AcceptRanges.Add("bytes"); response.Content = new StreamContent(stream); response.Content.Headers.ContentType = mediaType; response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); mediaNameStr = System.Web.HttpUtility.UrlEncode(mediaNameStr); response.Content.Headers.ContentDisposition.FileName = mediaNameStr; return(response); } var partialStream = EnsureStreamCanSeek(stream); response = this.Request.CreateResponse(HttpStatusCode.PartialContent); response.Headers.AcceptRanges.Add("bytes"); try { // return the requested range(s) response.Content = new ByteRangeStreamContent(partialStream, range, mediaType); } catch (InvalidByteRangeException exception) { DynamicLogger.Instance.WriteLoggerLogError("GenerateTemplate", exception); response.Dispose(); return(Request.CreateErrorResponse(exception)); } // change status code if the entire stream was requested if (response.Content.Headers.ContentLength.Value == partialStream.Length) { response.StatusCode = HttpStatusCode.OK; } return(response); }
internal SheetProtection ToSheetProtection() { var sp = new SheetProtection(); if (AlgorithmName != null) { sp.AlgorithmName = AlgorithmName; } if (HashValue != null) { sp.HashValue = HashValue; } if (SaltValue != null) { sp.SaltValue = SaltValue; } if (SpinCount != null) { sp.SpinCount = SpinCount.Value; } if (Password != null) { sp.Password = Password; } if ((Sheet != null) && Sheet.Value) { sp.Sheet = Sheet.Value; } if (!AllowEditObjects) { sp.Objects = !AllowEditObjects; } if (!AllowEditScenarios) { sp.Scenarios = !AllowEditScenarios; } if (!AllowFormatCells != true) { sp.FormatCells = !AllowFormatCells; } if (!AllowFormatColumns != true) { sp.FormatColumns = !AllowFormatColumns; } if (!AllowFormatRows != true) { sp.FormatRows = !AllowFormatRows; } if (!AllowInsertColumns != true) { sp.InsertColumns = !AllowInsertColumns; } if (!AllowInsertRows != true) { sp.InsertRows = !AllowInsertRows; } if (!AllowInsertHyperlinks != true) { sp.InsertHyperlinks = !AllowInsertHyperlinks; } if (!AllowDeleteColumns != true) { sp.DeleteColumns = !AllowDeleteColumns; } if (!AllowDeleteRows != true) { sp.DeleteRows = !AllowDeleteRows; } if (!AllowSelectLockedCells) { sp.SelectLockedCells = !AllowSelectLockedCells; } if (!AllowSort != true) { sp.Sort = !AllowSort; } if (!AllowAutoFilter != true) { sp.AutoFilter = !AllowAutoFilter; } if (!AllowPivotTables != true) { sp.PivotTables = !AllowPivotTables; } if (!AllowSelectUnlockedCells) { sp.SelectUnlockedCells = !AllowSelectUnlockedCells; } return(sp); }
internal void FromSheetProtection(SheetProtection sp) { SetAllNull(); if (sp.AlgorithmName != null) { AlgorithmName = sp.AlgorithmName.Value; } if (sp.HashValue != null) { HashValue = sp.HashValue.Value; } if (sp.SaltValue != null) { SaltValue = sp.SaltValue.Value; } if (sp.SpinCount != null) { SpinCount = sp.SpinCount.Value; } if (sp.Password != null) { Password = sp.Password.Value; } if (sp.Sheet != null) { Sheet = sp.Sheet.Value; } if (sp.Objects != null) { AllowEditObjects = !sp.Objects.Value; } if (sp.Scenarios != null) { AllowEditScenarios = !sp.Scenarios.Value; } if (sp.FormatCells != null) { AllowFormatCells = !sp.FormatCells.Value; } if (sp.FormatColumns != null) { AllowFormatColumns = !sp.FormatColumns.Value; } if (sp.FormatRows != null) { AllowFormatRows = !sp.FormatRows.Value; } if (sp.InsertColumns != null) { AllowInsertColumns = !sp.InsertColumns.Value; } if (sp.InsertRows != null) { AllowInsertRows = !sp.InsertRows.Value; } if (sp.InsertHyperlinks != null) { AllowInsertHyperlinks = !sp.InsertHyperlinks.Value; } if (sp.DeleteColumns != null) { AllowDeleteColumns = !sp.DeleteColumns.Value; } if (sp.DeleteRows != null) { AllowDeleteRows = !sp.DeleteRows.Value; } if (sp.SelectLockedCells != null) { AllowSelectLockedCells = !sp.SelectLockedCells.Value; } if (sp.Sort != null) { AllowSort = !sp.Sort.Value; } if (sp.AutoFilter != null) { AllowAutoFilter = !sp.AutoFilter.Value; } if (sp.PivotTables != null) { AllowPivotTables = !sp.PivotTables.Value; } if (sp.SelectUnlockedCells != null) { AllowSelectUnlockedCells = !sp.SelectUnlockedCells.Value; } }
internal SheetProtection ToSheetProtection() { SheetProtection sp = new SheetProtection(); if (this.AlgorithmName != null) sp.AlgorithmName = this.AlgorithmName; if (this.HashValue != null) sp.HashValue = this.HashValue; if (this.SaltValue != null) sp.SaltValue = this.SaltValue; if (this.SpinCount != null) sp.SpinCount = this.SpinCount.Value; if (this.Password != null) sp.Password = this.Password; if (this.Sheet != null && this.Sheet.Value != false) sp.Sheet = this.Sheet.Value; if (!this.AllowEditObjects != false) sp.Objects = !this.AllowEditObjects; if (!this.AllowEditScenarios != false) sp.Scenarios = !this.AllowEditScenarios; if (!this.AllowFormatCells != true) sp.FormatCells = !this.AllowFormatCells; if (!this.AllowFormatColumns != true) sp.FormatColumns = !this.AllowFormatColumns; if (!this.AllowFormatRows != true) sp.FormatRows = !this.AllowFormatRows; if (!this.AllowInsertColumns != true) sp.InsertColumns = !this.AllowInsertColumns; if (!this.AllowInsertRows != true) sp.InsertRows = !this.AllowInsertRows; if (!this.AllowInsertHyperlinks != true) sp.InsertHyperlinks = !this.AllowInsertHyperlinks; if (!this.AllowDeleteColumns != true) sp.DeleteColumns = !this.AllowDeleteColumns; if (!this.AllowDeleteRows != true) sp.DeleteRows = !this.AllowDeleteRows; if (!this.AllowSelectLockedCells != false) sp.SelectLockedCells = !this.AllowSelectLockedCells; if (!this.AllowSort != true) sp.Sort = !this.AllowSort; if (!this.AllowAutoFilter != true) sp.AutoFilter = !this.AllowAutoFilter; if (!this.AllowPivotTables != true) sp.PivotTables = !this.AllowPivotTables; if (!this.AllowSelectUnlockedCells != false) sp.SelectUnlockedCells = !this.AllowSelectUnlockedCells; return sp; }
internal void FromSheetProtection(SheetProtection sp) { this.SetAllNull(); if (sp.AlgorithmName != null) this.AlgorithmName = sp.AlgorithmName.Value; if (sp.HashValue != null) this.HashValue = sp.HashValue.Value; if (sp.SaltValue != null) this.SaltValue = sp.SaltValue.Value; if (sp.SpinCount != null) this.SpinCount = sp.SpinCount.Value; if (sp.Password != null) this.Password = sp.Password.Value; if (sp.Sheet != null) this.Sheet = sp.Sheet.Value; if (sp.Objects != null) this.AllowEditObjects = !sp.Objects.Value; if (sp.Scenarios != null) this.AllowEditScenarios = !sp.Scenarios.Value; if (sp.FormatCells != null) this.AllowFormatCells = !sp.FormatCells.Value; if (sp.FormatColumns != null) this.AllowFormatColumns = !sp.FormatColumns.Value; if (sp.FormatRows != null) this.AllowFormatRows = !sp.FormatRows.Value; if (sp.InsertColumns != null) this.AllowInsertColumns = !sp.InsertColumns.Value; if (sp.InsertRows != null) this.AllowInsertRows = !sp.InsertRows.Value; if (sp.InsertHyperlinks != null) this.AllowInsertHyperlinks = !sp.InsertHyperlinks.Value; if (sp.DeleteColumns != null) this.AllowDeleteColumns = !sp.DeleteColumns.Value; if (sp.DeleteRows != null) this.AllowDeleteRows = !sp.DeleteRows.Value; if (sp.SelectLockedCells != null) this.AllowSelectLockedCells = !sp.SelectLockedCells.Value; if (sp.Sort != null) this.AllowSort = !sp.Sort.Value; if (sp.AutoFilter != null) this.AllowAutoFilter = !sp.AutoFilter.Value; if (sp.PivotTables != null) this.AllowPivotTables = !sp.PivotTables.Value; if (sp.SelectUnlockedCells != null) this.AllowSelectUnlockedCells = !sp.SelectUnlockedCells.Value; }
/// <summary> /// Initialize the worksheet with required details /// </summary> /// <returns></returns> private Worksheet SetWorksheetData() { Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Columns columns = new Columns(); Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 15U, CustomWidth = true }; Column column2 = new Column() { Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; Column column3 = new Column() { Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; Column column4 = new Column() { Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = (Double)ConversionSettings.ColumnWidth, CustomWidth = true }; columns.Append(column1); columns.Append(column2); columns.Append(column3); columns.Append(column4); SheetData sheetData = new SheetData(); SheetProtection sheetProtection = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true }; worksheet.Append(columns); worksheet.Append(sheetData); worksheet.Append(sheetProtection); return worksheet; }