// Given a WorkbookPart, inserts a new worksheet.
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(newWorksheetPart);
}
/// <summary>
/// Crée une feuille.
/// </summary>
/// <param name="name">Le nom de la feuille.</param>
/// <returns>La feuille créée.</returns>
public WorksheetPart CreateSheet(string name)
{
var sheetPart = _package.WorkbookPart.AddNewPart <WorksheetPart>();
// Taille limite
name = new string(name.Take(30).ToArray());
Worksheet workSheet = new Worksheet();
workSheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workSheet.Append(new SheetData());
sheetPart.Worksheet = workSheet;
// Get a unique ID for the new worksheet.
Sheets sheets = _package.WorkbookPart.Workbook.Sheets;
uint sheetId = 1;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
var sheet = new Sheet()
{
Name = MakeValidSheetName(name), Id = _package.WorkbookPart.GetIdOfPart(sheetPart), SheetId = sheetId
};
_package.WorkbookPart.Workbook.Sheets.Append(sheet);
return(sheetPart);
}
public static WorksheetPart addWorksheetPart(SpreadsheetDocument document, string name)
{
// Open the document for editing.
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = name;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
return(newWorksheetPart);
}
private Sheet CreateNewWorkSheet(SpreadsheetDocument spreadSheet, string NewSheetName)
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = NewSheetName + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
newWorksheetPart.Worksheet.Save();
spreadSheet.WorkbookPart.Workbook.Save();
return(sheet);
}
public WorksheetPart InsertWorksheet()
{
if (this.workbook == null)
{
Console.WriteLine("Error: this spreadsheet has no workbookPart!");
return(null);
}
WorksheetPart newWorksheetPart = this.workbook.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>();
string relationshipID = this.workbook.GetIdOfPart(newWorksheetPart);
uint sheetID = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetID = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetID;
Sheet sheet = new Sheet()
{
Id = relationshipID, SheetId = sheetID, Name = sheetName
};
sheets.Append(sheet);
this.workbook.Workbook.Save();
return(newWorksheetPart);
}
// Given a document name, inserts a new worksheet.
public void InsertNewBlankWorksheet(string NewSheetName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true))
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = NewSheetName;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
}
}
/// <summary>
/// Inserts a new worksheet
/// </summary>
/// <param name="sheetName">Sheet name.</param>
private void InsertNewSheet(string sheetName)
{
// Add a blank WorksheetPart.
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(_filename, true))
{
WorksheetPart newWorksheetPart = doc.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = doc.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
//return this._sheetId;
}
}
// Inserting
public static WorksheetPart InsertBlankWorkSheet(SpreadsheetDocument ss, String newSheetName)
{
// Add a new worksheet part to the workbook.
WorkbookPart wbp = ss.WorkbookPart;
WorksheetPart new_wsp = wbp.AddNewPart <WorksheetPart>();
SheetData sheet_data = new SheetData();
new_wsp.Worksheet = new Worksheet(sheet_data);
new_wsp.Worksheet.Save();
Sheets sheets = wbp.Workbook.GetFirstChild <Sheets>();
String relationshipId = wbp.GetIdOfPart(new_wsp);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
String sheetName = newSheetName;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
wbp.Workbook.Save();
return(new_wsp);
}
internal static WorksheetPart AppendNewSheet(SpreadsheetDocument doc, WorksheetPart worksheetPart, string sheetName = null)
{
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets == null)
{
sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
}
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = $"Sheet{sheetId}";
}
sheets.AppendChild(new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = sheetName
});
return(worksheetPart);
}
private Worksheet CreateWorksheet(Workbook workbook, string name)
{
SheetData newSheetData = new SheetData();
WorksheetPart newWorksheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(newSheetData);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet newSheet = new Sheet()
{
Id = relationshipId,
Name = name,
SheetId = sheetId
};
sheets.Append(newSheet);
return(newWorksheetPart.Worksheet);
}
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(newWorksheetPart);
}
public void AddNewWorksheet()
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
CurrentWorksheetPart = newWorksheetPart;
//workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
//workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
/// <summary>
/// 엑셀 파일에 Sheet를 생성함.
/// </summary>
/// <param name="spreadsheet">SpreadSheet</param>
/// <param name="sheetName">생성할 sheet이름</param>
private void AddSheet(SpreadsheetDocument spreadsheet, string sheetName)
{
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
string relationshipId = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
}
/// <summary>
/// Duplicates the worksheet.
/// </summary>
/// <param name="workbookPart">The workbook part.</param>
/// <param name="srcWorksheetPart">The worksheet part.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>Dupplicated sheet</returns>
internal static Sheet DuplicateWorksheet(WorkbookPart workbookPart, WorksheetPart srcWorksheetPart, string sheetName)
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = (Worksheet)srcWorksheetPart.Worksheet.CloneNode(true);
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
return(sheet);
}
private Worksheet creaHoja(SpreadsheetDocument doc, string nombre)
{
//Crea el archivo de la hoja
WorksheetPart worksheetPart = doc.WorkbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
//Crea el apuntador al archivo
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = nombre
};
sheets.Append(sheet);
// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
return(worksheetPart.Worksheet);
}
public static void OpenAndAddToSpreadsheetStream(Stream stream)
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true))
{
// Add a WorksheetPart.
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
// Create Sheets object.
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "mySheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
}
// Caller must close the stream.
}
// Inserts a new worksheet for error.
private static WorksheetPart InsertErrorWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart errorWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
errorWorksheetPart.Worksheet = new Worksheet(new SheetData());
errorWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(errorWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = $"StudentImportErrors{DateTime.Now.Date.Day}{DateTime.Now.Date.Month}{DateTime.Now.Date.Year}{DateTime.Now.Hour}{DateTime.Now.Millisecond}";
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(errorWorksheetPart);
}
public void CreateSheet(string name)
{
Sheets sheets = this.document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
WorksheetPart newWorksheetPart = this.document.WorkbookPart.AddNewPart <WorksheetPart>();
string relationshipId = this.document.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = name;
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
this.part = newWorksheetPart;
}
private void ProcessWorkSheet(string filePath, DataTable tbl)
{
// get worksheetpart
using (Stream stream = File.Open(filePath, FileMode.Open))
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(stream, true);
WorkbookPart wbPart = spreadsheet.WorkbookPart;
// Assuming last id is , is id for worksheet .
Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
uint sheetId = 0;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() - 1;
}
WorksheetPart worksheetPart = wbPart.WorksheetParts.ToList()[Convert.ToInt32(sheetId)];
WriteDataTableToExcelWorksheet(tbl, worksheetPart, CultureInfo.InvariantCulture);
spreadsheet.WorkbookPart.Workbook.Save();
spreadsheet.Close();
}
}
private static void CreatePartsDT(DataTable dt, SpreadsheetDocument spreadsheet)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint sheetId = 1;
var newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
Sheets sheets = new Sheets();
spreadsheet.WorkbookPart.Workbook.AppendChild(sheets);
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new Sheet
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = sheetId,
Name = dt.TableName
});
spreadsheet.WorkbookPart.Workbook.Save();
}
/// <summary>
/// Добавляет лист в документ Excel
/// </summary>
/// <param name="sheetName">Имя добавляемого листа</param>
public void AddSpreadSheet(string sheetName)
{
_worksheetPart = _workbookPart.AddNewPart <WorksheetPart>();
_worksheetPart.Worksheet = new Worksheet(new SheetData());
_workSheet = _worksheetPart.Worksheet;
_workSheet.Save();
Sheets sheets = _workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = _workbookPart.GetIdOfPart(_worksheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId,
SheetId = sheetId,
Name = sheetName
};
sheets.Append(sheet);
//if (_worksheetPart.Worksheet.Elements<Sheet>().All(s => s.SheetId != sheetId))
// _worksheetPart.AddPart<Worksheet>(sheet);
_workbookPart.Workbook.Save();
}
public T GetWorksheetPartByName <T>(string sheetName)
{
// Check the type first;
if (typeof(T) != typeof(WorksheetPart) && typeof(T) != typeof(Sheet))
{
Console.WriteLine("Error: Invalid return type!");
return(default(T));
}
if (this.workbook == null || this.workbook.Workbook == null)
{
Console.WriteLine("Error: This spreadsheet has no workbook!");
return(default(T));
}
if (this.workbook.Workbook.Descendants <Sheets>().Count() == 0 ||
this.workbook.Workbook.Descendants <Sheet>().Count() == 0)
{
Console.WriteLine("Error: This spreadsheet has no sheets!");
return(default(T));
}
Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>();
if (sheets == null)
{
Console.WriteLine("Error: this workbook has no sheets elements");
return(default(T));
}
//Sheet selectedSheet = sheets.Elements<Sheet>().First();
//Console.WriteLine("Name: " + selectedSheet.Name);
Sheet selectedSheet = null;
if (sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).Count() > 0)
{
selectedSheet = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).FirstOrDefault();
}
if (selectedSheet == null)
{
Console.WriteLine("Error: this workbook has no '{0}'", sheetName);
return(default(T));
}
string selectedSheetID = selectedSheet.Id;
if (typeof(T) == typeof(WorksheetPart))
{
return((T)Convert.ChangeType(this.workbook.GetPartById(selectedSheetID), typeof(T)));
}
else if (typeof(T) == typeof(Sheet))
{
return((T)Convert.ChangeType(selectedSheet, typeof(T)));
}
else
{
Console.WriteLine("Error!");
return(default(T));
}
}
public byte[] Save()
{
using (var ms = new MemoryStream())
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = doc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
var stylesPart = doc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new ExcelWorkbookStylesheet().CreateStylesheet(this.sheets);
foreach (var item in this.sheets)
{
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workSheet = new Worksheet();
var sheetData = new SheetData();
foreach (var rowGroup in item.Cells.GroupBy(g => g.Value.Row))
{
var row = new Row()
{
RowIndex = Convert.ToUInt32(rowGroup.Key)
};
foreach (var cellItem in rowGroup)
{
row.Append(this.CreateCell(cellItem.Value));
}
sheetData.Append(row);
}
workSheet.AppendChild(sheetData);
worksheetPart.Worksheet = workSheet;
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet1 = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = item.SheetName
};
sheets.Append(sheet1);
}
doc.Close();
return(ms.ToArray());
}
}
public WorksheetPart AddNewSheet(string sheetName)
{
if (this.spreadsheet == null)
{
return(null);
}
//Only need get first because a document shall have ony 1 workbook
WorkbookPart workbookPart = this.spreadsheet.GetPartsOfType <WorkbookPart>().First();
if (workbookPart == null)
{
workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
}
//workbookPart.WorksheetParts.First();
// Add to workbook:
Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
uint newSheetID;
if (sheets == null)
{
sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
newSheetID = 1;
}
else
{
newSheetID = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
//uint sID = uint.Parse(sheet.GetAttribute("sheetID", sheet.NamespaceUri).ToString());
}
Sheet newSheet;
int isExist = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).Count();
// If not exist any worksheet with the name provided, create a new one;
if (isExist == 0)
{
// New sheet.xml file: which contains data of a sheet
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
newSheet = new Sheet()
{
Id = this.spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = newSheetID, Name = sheetName
};
sheets.Append(newSheet);
return(worksheetPart);
}
else
{
Sheet selectedSheet = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).FirstOrDefault();
string selectedSheetID = selectedSheet.Id;
return((WorksheetPart)this.workbook.GetPartById(selectedSheetID));
}
}
private uint GetUniqueSheetId(Sheets sheets)
{
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
return(sheetId);
}
private uint NewSheetId()
{
if (InnerSheets.Elements <Sheet>().Count() == 0)
{
return(1);
}
else
{
return(InnerSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1);
}
}
private Sheet GetRefSheet(string name, Sheets sheets)
{
var tmplate = sheets.Elements <Sheet>().FirstOrDefault(x => name.Contains(x.Name));
if (tmplate != null)
{
return(sheets.Elements <Sheet>().LastOrDefault(x => x.Name.ToString().Contains(tmplate.Name)));
}
return
(null);
}
public static WorksheetPart InsertSheet(SpreadsheetDocument document, string sheetName = "")
{
using (SAEONLogs.MethodCall(typeof(WorksheetPart)))
{
try
{
if (document is null)
{
throw new ArgumentNullException(nameof(document));
}
// Add a blank WorksheetPart.
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets is null)
{
sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
}
string relationshipId = document.WorkbookPart.GetIdOfPart(worksheetPart);
SAEONLogs.Verbose("relationshipId: {relationshipId}", relationshipId);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
SAEONLogs.Verbose("sheetId: {sheetId}", sheetId);
// Give the new worksheet a name.
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "Sheet" + sheetId;
}
SAEONLogs.Verbose("sheetName: {sheetName}", sheetName);
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
return(worksheetPart);
}
catch (Exception ex)
{
SAEONLogs.Exception(ex);
throw;
}
}
}
public string InitBookCreation(string TemplateFileName, string ResultFileName, string TemplateSheetsFileName)
{
//Assume Error
string result = "Cannot Create Result File";
result = CopyFile(templatefilepath + TemplateFileName, resultfilepath + ResultFileName);
if (result == "Copied file")
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + ResultFileName, true))
{
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == TemplateSheetsFileName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
// Create a new worksheet with name as TemplateSheetsFileName
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets wbsheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (wbsheets.Elements <Sheet>().Count() > 0)
{
sheetId = wbsheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = TemplateSheetsFileName;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
wbsheets.Append(sheet);
}
}
result = "Result file ready";
}
catch (Exception ex)
{
result = ex.Message;
}
}
return(result);
}
public static void AppendSpreadSheet(string filePath, string sheetName)
{
if (!System.IO.File.Exists(filePath))
{
throw new Exception("File Not Exists");
}
using (Stream stream = File.Open(filePath, FileMode.Open))
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(stream, true);
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (isSheetsAlreadyExists(sheets, sheetName))
{
spreadsheetDocument.Close();
stream.Close();
return;
}
// Add a new worksheet.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
//ready to write column header
spreadsheetDocument.WorkbookPart.Workbook.Save();
// Close the document handle.
spreadsheetDocument.Close();
stream.Close();
}
}
