private void SaveDocument(SpreadsheetDocument document)
{
SaveContext context = new SaveContext();
WorkbookPart workbookPart = document.AddWorkbookPart();
foreach (IdPartPair pair in workbookPart.Parts)
{
workbookPart.ChangeIdOfPart(pair.OpenXmlPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ??
document.AddNewPart <ExtendedFilePropertiesPart>(
context.RelIdGenerator.GetNext(RelType.Workbook));
GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);
CreateWorkbookContent(workbookPart, context);
WriteCustomProperties(document);
document.Save();
document.Close();
}
public static byte[] WriteExcel(this Dictionary <DateTime, string[, ]> lst, string excelFile)
{
var template = new FileInfo(excelFile);
bool hasData = true;
using (var templateStream = new MemoryStream())
{
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(excelFile, true))
{
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
Workbook workbook = workBookPart.Workbook;
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Workbook wb = new Workbook();
wb.Append(fileVersion);
Sheets sheets = null;
WorksheetPart sourceSheetPart = null;
// add sheet
foreach (KeyValuePair <DateTime, string[, ]> item in lst)
{
sheets = sheets ?? new Sheets();
var sheetId = sheets != null ? (uint)sheets.ChildElements.Count + 1 : 1;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(HttpContext.Current.Request.MapPath("~") + "TemplateFile\\Template.xlsx", true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string rId = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id;
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(rId);
try
{
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
catch
{
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "wsPart_" + sheetId;
sourceSheetPart = wsPart;
WorksheetPart worksheetPart = workBookPart.AddPart <WorksheetPart>(sourceSheetPart);
Sheet copiedSheet = new Sheet
{
Name = item.Key.ToString("dd-MM-yyyy"),
Id = workBookPart.GetIdOfPart(worksheetPart)
};
copiedSheet.SheetId = sheetId;
sheets.Append(copiedSheet);
worksheetPart.Worksheet.GetFirstChild <SheetData>().UpdateSheetData(item.Key, item.Value);
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
}
}
hasData = sheets != null;
if (sheets != null)
{
wb.Append(sheets);
//Save Changes
workBookPart.Workbook = wb;
wb.Save();
workBookPart.Workbook.Save();
}
spreadDocument.Close();
}
if (hasData)
{
byte[] templateBytes = File.ReadAllBytes(template.FullName);
templateStream.Write(templateBytes, 0, templateBytes.Length);
var result = templateStream.ToArray();
templateStream.Position = 0;
templateStream.Flush();
return(result);
}
else
{
return(null);
}
}
}
