/// <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;
}
