private static StringValue createNewSheet(WorkbookPart workbookPart, string sheetName)
{
Sheets sheets = workbookPart.Workbook.Descendants <Sheets>().FirstOrDefault();
// will Sheets ever be null ever on an existing workbook? No.
uint currentMaxSheetId;
if (sheets.Count() == 0)
{
currentMaxSheetId = 0;
}
else
{
currentMaxSheetId =
(from t in sheets.Elements()
select((uint)((Sheet)t).SheetId)).Max();
}
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
StringValue relationshipId = workbookPart.GetIdOfPart(worksheetPart);
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId,
SheetId = (UInt32Value)currentMaxSheetId + 1,
Name = sheetName
};
sheets.Append(sheet);
return(relationshipId);
}
/// <summary>
/// Adds a new empty sheet in the document
/// </summary>
public static SheetData AddSheet(this SpreadsheetDocument document, string sheetName)
{
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
var data = new SheetData();
worksheetPart.Worksheet = new Worksheet(data);
worksheetPart.Worksheet.Save();
var workbookPart = document.WorkbookPart;
// get or create colelction of sheets
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>() ?? workbookPart.Workbook.AppendChild(new Sheets());
// create a new one
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (UInt32)(sheets.Count()) + 1,
Name = sheetName
};
// add to collection and save
sheets.Append(sheet);
document.WorkbookPart.Workbook.Save();
return(data);
}
/// <summary>
/// 将DataTable转化为XML输出
/// </summary>
/// <param name="dataSet">dataSet</param>
/// <param name="fileName">文件名称</param>
//public void DataTableToXML(DataTable dataTable, string fileName)
public void DataSetToXML(DataSet dataSet, string filePath)
{
Dictionary <String, List <OpenXmlElement> > sets = ToSheets(dataSet);
using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = package.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());
foreach (KeyValuePair <String, List <OpenXmlElement> > set in sets)
{
WorksheetPart worksheetpart = workbookpart.AddNewPart <WorksheetPart>();
worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
worksheetpart.Worksheet.Save();
Sheet sheet = new Sheet()
{
Id = workbookpart.GetIdOfPart(worksheetpart),
SheetId = (uint)(sheets.Count() + 1),
Name = set.Key
};
sheets.AppendChild(sheet);
}
workbookpart.Workbook.Save();
}
}
private Worksheet GetWorksheet(WorkbookPart workbookPart, Sheets sheets, int index)
{
if (index > 0 && index <= sheets.Count())
{
return(GetWorksheet(workbookPart, sheets.ChildElements[index - 1] as Sheet));
}
return(null);
}
private void CalculateSum()
{
decimal sum = 0;
for (int i = 0; i < Sheets.Count(); i++)
{
sum += Sheets[i].Amount;
}
Sum = Money.FromDeciaml(sum * _rate);
}
private void WriteWorksheet(WorkbookPart workbookPart, Sheets sheets, DotForm form)
{
//Add data to first sheet
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
Columns columns = new Columns();
columns.Append(new Column {
Min = 1, Max = 2, Width = 4, CustomWidth = true
});
columns.Append(new Column {
Min = 3, Max = 7, Width = 12, CustomWidth = true
});
columns.Append(new Column {
Min = 8, Max = 8, Width = 40, CustomWidth = true
});
columns.Append(new Column {
Min = 9, Max = 9, Width = 20, CustomWidth = true
});
columns.Append(new Column {
Min = 10, Max = 11, Width = 4, CustomWidth = true
});
var sheetData = new SheetData();
var dataValidations = new DataValidations();
worksheet.Append(columns);
worksheet.Append(sheetData);
var mergeCells = CreateOrGetMergeCells(worksheet);
worksheet.Append(dataValidations);
Sheet sheet = new Sheet
{
Name = form.EventTreeName,
SheetId = (uint)sheets.Count() + 1,
Id = workbookPart.GetIdOfPart(worksheetPart),
};
sheets.Append(sheet);
WriteHeader(sheetData, mergeCells);
WriteEventHeader(form, sheetData, mergeCells);
WriteExpertInformation(form, sheetData);
WriteElicitationCodeInformation(sheetData, mergeCells);
AddImage(form.GetFileStream, worksheetPart);
var rowNumber = WriteNodes(form, sheetData, dataValidations);
WriteSheetBottom(sheetData, rowNumber);
worksheetPart.Worksheet.Save();
}
public ITabularPage AddPageAfter(int workSheetIndex)
{
Sheets sheets = _workbook.GetFirstChild<Sheets>();
Sheet worksheet = SetupWorksheet(sheets);
if (sheets.Count() <= workSheetIndex)
sheets.Append(worksheet);
else
sheets.InsertAt<Sheet>(worksheet, workSheetIndex + 1);
_workbook.Save();
return GetPage(worksheet.Name);
}
WorksheetPart AddEmptySheet(WorkbookPart workbookPart, string sheetName, Sheets sheets)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// craete new sheet ad append to file
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheets != null ? (UInt32)sheets.Count() + 1 : 1,
Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(worksheetPart);
}
internal WorksheetPart WorksheetPart_FromName(WorkbookPart workbookPart, string sheetName = "")
{
//if (sheetName == "") return workbookPart.WorksheetParts.Last();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
var total = sheets.Count();
if (total == 0)
{
return(null); // The specified worksheet does not exist.
}
IEnumerable <Sheet> sheetsName = sheets.Elements <Sheet>().Where(s => s.Name == sheetName);
var sheet1 = sheetsName.FirstOrDefault();
if (sheet1 == null)
{
if (sheetName != "")
{
($"Error! Worksheet with name '{sheetName}' was not found!").zException_Show();
}
// The Sheet name was not found; return the first visible sheet
foreach (Sheet sheet in sheets.Elements <Sheet>())
{
if (sheet.State == null || (sheet.State != null && sheet.State.HasValue && sheet.State.Value == SheetStateValues.Visible))
{
sheet1 = sheet;
break;
}
}
}
// if (sheet1 == null) return null; // <==================[ Unit test required for this condition
string relationshipId = sheet1.Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);
return(worksheetPart);
}
private void WriteWorksheets(WorkbookPart workbookPart, SaveContext context)
{
XlSharedStringsTable stringsTable = new XlSharedStringsTable(workbookPart, context);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
foreach (XlWorksheet sheet in Worksheets)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet(sheetData);
worksheetPart.Worksheet = worksheet;
if (
!worksheet.NamespaceDeclarations.Contains(new KeyValuePair <String, String>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
var sheetProperties = new SheetProperties {
TabColor = new TabColor()
};
string colorString = sheet.TabColor.HtmlColor;
sheetProperties.TabColor.Rgb = colorString;
worksheet.SheetProperties = sheetProperties;
AddTableData(sheet, sheetData, stringsTable);
worksheet.Save();
Sheet s = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(sheets.Count() + 1),
Name = sheet.Name
};
sheets.AppendChild(s);
workbookPart.Workbook.Save();
}
}
public void AddDataToSheet(string sheetName, IEnumerable <string> columnNames, IEnumerable <Array> data)
{
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet(sheetData);
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = worksheet;
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
// Append the new worksheet and associate it with the workbook.
uint sheetId = (uint)sheets.Count() + 1;
sheetName = String.IsNullOrEmpty(sheetName) ? "Sheet " + sheetId : sheetName;
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName
};
Columns columns = new Columns();
Row headerRow = new Row {
RowIndex = 1
};
List <string> keys = new List <string>();
for (int i = 0; i < columnNames.Count(); i++)
{
var columnName = columnNames.ElementAt(i);
Column column = new Column()
{
Min = (uint)i, Max = (uint)i, CustomWidth = false
};
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(columnName);
/*** set the columnWith does not work **/
/*
* double columnWith = 8.43;
*
* columnWith = Math.Max(columnWith, 0.94 * (columnName.Length+1));
* column.Width = columnWith;
*/
headerRow.Append(cell);
columns.Append(column);
}
sheetData.Append(headerRow);
for (int counter = 0; counter < data.Count(); counter++)
{
var rowData = data.ElementAt(counter);
Row row = new Row {
RowIndex = (uint)counter + 2
};
for (int i = 0; i < rowData.Length; i++)
{
var value = rowData.GetValue(i);
Cell cell = new Cell();
cell.DataType = GetCellDataType(value);
string cellValue = (value == null ? "" : value.ToString());
cell.CellValue = new CellValue(cellValue);
row.Append(cell);
}
sheetData.Append(row);
}
sheets.Append(sheet);
}
public void AddDataToSheet <T>(string sheetName, Dictionary <string, string> fieldMapping, IEnumerable <T> data)
{
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet(sheetData);
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = worksheet;
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
// Append the new worksheet and associate it with the workbook.
uint sheetId = (uint)sheets.Count() + 1;
sheetName = String.IsNullOrEmpty(sheetName) ? "Sheet " + sheetId : sheetName;
if (sheetName.Length > 30)
{
sheetName = sheetName.Substring(0, 30);
}
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName
};
Type type = null;
IEnumerable <PropertyInfo> propertyInfos = new List <PropertyInfo>();
if (data.Count() > 0)
{
var obj = data.First();
type = obj.GetType();
propertyInfos = this.getPropertyInfo(type);
// if field mapping is null
// then we want to export all properties
if (fieldMapping == null)
{
fieldMapping = this.getPropertyFieldMappings(type);
}
}
Columns columns = new Columns();
Row headerRow = new Row {
RowIndex = 1
};
List <string> keys = new List <string>();
if (fieldMapping != null)
{
keys = fieldMapping.Keys.ToList();
for (uint i = 0; i < keys.Count; i++)
{
Column column = new Column()
{
Min = i, Max = i, CustomWidth = false
};
columns.Append(column);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(fieldMapping[keys[(int)i]]);
headerRow.Append(cell);
}
}
//worksheet.InsertAt(columns, 0);
sheetData.Append(headerRow);
for (uint counter = 0; counter < data.Count(); counter++)
{
var obj = data.ElementAt((int)counter);
Row row = new Row {
RowIndex = counter + 2
};
for (int i = 0; i < keys.Count; i++)
{
string key = keys[i];
Cell cell = new Cell();
try {
var value = getPropertyValue(obj, key.Split('.'));
CellValues cellDataType = GetCellDataType(value);
cell.DataType = cellDataType;
if (cellDataType == CellValues.Date)
{
var d = (DateTime)value;
cell.DataType = CellValues.Number;
if (d.TimeOfDay.Seconds == 0)
{
cell.StyleIndex = UInt32Value.FromUInt32((int)CustomCellFormats.ISODateFormat);
}
else
{
cell.StyleIndex = UInt32Value.FromUInt32((int)CustomCellFormats.ISODateTimeFormat);
}
cell.CellValue = new CellValue(Convert.ToString(d.ToOADate()));
}
else if (cellDataType == CellValues.Boolean)
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(Convert.ToString(value));
}
else
{
cell.CellValue = new CellValue(Convert.ToString(value));
}
} catch (PropertyNotFoundException ex) {
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(ex.Message);
}
row.Append(cell);
}
sheetData.Append(row);
}
sheets.Append(sheet);
}
/// <summary>
/// Copy sheet to another document
/// </summary>
/// <param name="sourceDoc">Source document</param>
/// <param name="srcSheetName">Name of source sheet</param>
/// <param name="targetDoc">Spreadsheet document to copied</param>
/// <param name="targetIndex">Index of copied sheet in target document</param>
public static void CopyWorksheet(SpreadsheetDocument sourceDoc, String srcSheetName, SpreadsheetDocument targetDoc, uint targetIndex)
{
// Locate the source sheet
if (sourceDoc.WorkbookPart == null)
{
throw new InvalidOperationException("WorkbookPart is not exist in sourceDoc!");
}
if (sourceDoc.WorkbookPart.Workbook.Sheets == null)
{
throw new InvalidOperationException("No sheets exist in sourceDoc!");
}
var srcSheet =
sourceDoc.WorkbookPart.Workbook.Sheets.Descendants <Sheet>()
.FirstOrDefault(a => a.Name == srcSheetName);
if (srcSheet == null)
{
throw new InvalidOperationException(String.Format("No sheet found with name {0}!", srcSheetName));
}
var srcSheetPart = sourceDoc.WorkbookPart.GetPartById(srcSheet.Id) as WorksheetPart;
if (srcSheetPart == null)
{
throw new InvalidOperationException(String.Format("Cannot find worksheet part with Id {0}!", srcSheet.Id));
}
var srcWorkSheet = srcSheetPart.Worksheet;
if (srcWorkSheet == null)
{
throw new InvalidOperationException("Worksheet not exist in source worksheet part!");
}
// Locate the position of target sheet
WorkbookPart tgtWbPart = targetDoc.WorkbookPart ?? targetDoc.AddWorkbookPart();
Sheets tgtSheets = tgtWbPart.Workbook.Sheets ?? tgtWbPart.Workbook.AppendChild <Sheets>(new Sheets());
if (targetIndex > tgtSheets.Count())
{
targetIndex = (uint)tgtSheets.Count();
}
// Create a new worksheet and clone data from original worksheet
var newSheetPart = tgtWbPart.AddNewPart <WorksheetPart>();
newSheetPart.Worksheet = new Worksheet(); //srcWorkSheet.Clone() as Worksheet;
// Create a unique ID for the new worksheet.
uint sheetId = 1;
if (tgtSheets.Elements <Sheet>().Any())
{
sheetId = tgtSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Add cloned worksheet to target workbook
var newSheet = new Sheet()
{
Id = tgtWbPart.GetIdOfPart(newSheetPart),
SheetId = sheetId,
Name = srcSheet.Name
};
tgtSheets.InsertAt(newSheet, (int)targetIndex);
// Import data from source sheet to target sheet
ImportWorksheet(sourceDoc, srcWorkSheet, targetDoc, newSheetPart.Worksheet);
// Import all necessary resources into target document that referenced by cloned sheet
//ImportResources(sourceDoc, newSheetPart.Worksheet, targetDoc);
// Save it
tgtWbPart.Workbook.Save();
}
