/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
{
// Associates base content to a new worksheet part
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart);
if (worksheetDocument.Root == null)
{
worksheetDocument.Add(
new XElement("root")
);
}
worksheetDocument.Root.ReplaceWith(worksheet.Root);
// Associates the worksheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
return(worksheetPart);
}
public Worksheet AddSheet(WorkbookPart workbookPart, string name)
{
var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
// add single Sheet
var workSheetpart = workbookPart.AddNewPart<WorksheetPart>();
workSheetpart.Worksheet = new Worksheet(new SheetData());
workSheetpart.Worksheet.Save();
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(workSheetpart),
SheetId = (uint)(workbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return workSheetpart.Worksheet;
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
/// <summary>
/// 插入worksheet
/// </summary>
/// <param name="workbookPart"></param>
/// <returns></returns>
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = null)
{
//創建一個新的WorkssheetPart（後面將用它來容納具體的Sheet）
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
//取得Sheet集合
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
if (sheets == null)
{
sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
}
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
//得到Sheet的唯一序號
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetTempName = "Sheet" + sheetId;
if (sheetName != null)
{
bool hasSameName = false;
//檢測是否有重名
foreach (var item in sheets.Elements<Sheet>())
{
if (item.Name == sheetName)
{
hasSameName = true;
break;
}
}
if (!hasSameName)
{
sheetTempName = sheetName;
}
}
//創建Sheet實例並將它與sheets關聯
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
// Given a WorkbookPart, inserts a new worksheet.
private WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// We need single sheet only, if there is a sheet, return
if (workbookPart.WorksheetParts.Count() > 0)
{
return workbookPart.WorksheetParts.FirstOrDefault<WorksheetPart>();
}
// 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;
}
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
{
Sheet foundSheet = null;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
if (sheet.Name == sheetName)
{
foundSheet = sheet;
break;
}
}
if (foundSheet == null)
{
// 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;
}
// 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;
}
else
{
return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
}
}
private void OpenExistingSpreadsheet(string SheetNameOnOpen)
{
xl = SpreadsheetDocument.Open(memstream, true);
wbp = xl.WorkbookPart;
IsNewSpreadsheet = false;
slwb = new SLWorkbook();
this.DocumentProperties = new SLDocumentProperties();
this.LoadDocumentProperties();
InitialiseAutoFitCache();
LoadBuiltInNumberingFormats();
InitialiseStylesheetWhatNots(SLThemeTypeValues.Office);
LoadSharedStringTable();
giWorksheetIdCounter = 0;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp))
{
SLWorkbookView wv;
Sheet s;
SLSheet sheet;
DefinedName dn;
SLDefinedName sldn;
while (oxr.Read())
{
if (oxr.ElementType == typeof(WorkbookView))
{
wv = new SLWorkbookView();
wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement());
slwb.WorkbookViews.Add(wv);
}
else if (oxr.ElementType == typeof(Sheet))
{
s = (Sheet)oxr.LoadCurrentElement();
sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown);
if (s.State != null) sheet.State = s.State.Value;
slwb.Sheets.Add(sheet);
if (sheet.SheetId > giWorksheetIdCounter)
{
giWorksheetIdCounter = (int)sheet.SheetId;
}
}
else if (oxr.ElementType == typeof(DefinedName))
{
dn = (DefinedName)oxr.LoadCurrentElement();
sldn = new SLDefinedName(dn.Name.Value);
sldn.FromDefinedName(dn);
slwb.DefinedNames.Add(sldn);
}
else if (oxr.ElementType == typeof(PivotCache))
{
// cache IDs supposed to be unique, so I'm not gonna check for the hash set
slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value);
}
}
}
if (wbp.Workbook.WorkbookProperties != null)
{
slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties);
}
if (wbp.CalculationChainPart != null)
{
int iCurrentSheetId = 0;
SLCalculationCell slcc = new SLCalculationCell(string.Empty);
CalculationCell cc;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculationCell))
{
cc = (CalculationCell)oxr.LoadCurrentElement();
if (cc.SheetId == null)
{
cc.SheetId = iCurrentSheetId;
}
else
{
if (cc.SheetId.Value != iCurrentSheetId)
iCurrentSheetId = cc.SheetId.Value;
}
slcc.FromCalculationCell(cc);
slwb.CalculationCells.Add(slcc.Clone());
}
}
}
}
// To determine the type of sheet. Do this before the part
// where the table and pivot table parts are set.
bool bFound = false;
string sRelID = string.Empty;
foreach (SLSheet sheet in slwb.Sheets)
{
bFound = false;
foreach (WorksheetPart wspFound in wbp.WorksheetParts)
{
sRelID = wbp.GetIdOfPart(wspFound);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Worksheet;
bFound = true;
break;
}
}
if (!bFound)
{
foreach (ChartsheetPart csp in wbp.ChartsheetParts)
{
sRelID = wbp.GetIdOfPart(csp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Chartsheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
{
sRelID = wbp.GetIdOfPart(dsp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.DialogSheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (MacroSheetPart msp in wbp.MacroSheetParts)
{
sRelID = wbp.GetIdOfPart(msp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Macrosheet;
bFound = true;
break;
}
}
}
}
WorksheetPart wsp;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
{
if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value))
slwb.TableIds.Add(tdp.Table.Id.Value);
if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value))
slwb.TableNames.Add(tdp.Table.Name.Value);
}
foreach (PivotTablePart ptp in wsp.PivotTableParts)
{
if (ptp.PivotTableDefinition.Name != null
&& !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value))
slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value);
// the cache ID should already be added, from the workbook part above.
// But we check again just to be sure. Cache IDs have to be unique throughout
// the workbook.
if (ptp.PivotTableDefinition.CacheId != null
&& !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value))
slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value);
}
}
}
string sWorksheetName = SLConstants.DefaultFirstSheetName;
int i = 1;
bool bCannotFind = true;
bool bIsLegit = true;
if (wbp.WorksheetParts.Count() == 0)
{
// no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit...
// come up with a legit worksheet name that's not already taken...
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
else
{
bFound = false;
// there's a given worksheet name
if (SheetNameOnOpen.Length > 0)
{
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase)
&& sheet.SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
else
{
// we try to get the "actively selected" worksheet already selected.
uint iActiveTab = 0;
if (slwb.WorkbookViews.Count > 0)
{
iActiveTab = slwb.WorkbookViews[0].ActiveTab;
}
// there should be at least *this* number of sheets (whether it's a worksheet
// chartsheet or whatnot).
if (slwb.Sheets.Count > iActiveTab
&& slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId;
gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name;
gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id;
bFound = true;
}
}
if (!bFound)
{
// we get here either if there's no given worksheet name (bFound is still false),
// or there's a given worksheet name but corresponding values weren't found.
// The given worksheet name must be that of a worksheet. A chartsheet name is
// considered "invalid".
// Either way, we use the first available worksheet as the selected worksheet.
wsp = wbp.WorksheetParts.First();
sRelID = wbp.GetIdOfPart(wsp);
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
if (bFound)
{
// A viable worksheet should be found by now. Otherwise, it's probably
// a corrupted spreadsheet...
LoadSelectedWorksheet();
IsNewWorksheet = false;
}
else
{
// why is it not found!?! The file is corrupted somehow... we'll try to recover
// by adding a new worksheet and selecting it. Same algorithm as above.
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
}
}
public void LoadData(string sheetName, uint sheetId, DataTable dt)
{
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
uint rowIndex = 1;
uint colIndex = 1;
string collName = string.Empty;
Row row = null;
Cell cell = null;
sheetData = new SheetData();
// Header Row
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
// Header Row cellls
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dc.ColumnName;
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
foreach (DataRow dr in dt.Rows)
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
colIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dr[dc.ColumnName].ToString();
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
}
wsPart = wbPart.AddNewPart <WorksheetPart>();
wsPart.Worksheet = new Worksheet(sheetData);
sheet = new Sheet();
sheet.Id = wbPart.GetIdOfPart(wsPart);
sheet.SheetId = sheetId;
sheet.Name = sheetName;
sheets = wbPart.Workbook.AppendChild(new Sheets());
sheets.Append(sheet);
}
/// <summary>
/// Creates a chartsheet part from given data
/// </summary>
/// <param name="chartType">Type of chart to generate</param>
/// <param name="values">Values to represent in the chart</param>
/// <param name="headerReference">Columns to be used as series</param>
/// <param name="categoryReference">Column to be used as category</param>
/// <returns>Chartsheet part with contents related</returns>
public OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
{
//Creates base content and associates it to a new chartsheet part
XDocument chartsheet = CreateEmptyChartsheet();
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>();
XDocument chartsheetDocument = parentDocument.GetXDocument(chartsheetPart);
XDocument newChartsheetDocument = CreateEmptyChartsheet();
if (chartsheetDocument.Root == null)
{
chartsheetDocument.Add(
newChartsheetDocument.Root
);
}
else
{
chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root);
}
//Creates a base drawings part and associates it to the chartsheet part
OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>();
XDocument drawingsDocument = parentDocument.GetXDocument(drawingsPart);
XDocument newDrawingDocument = CreateEmptyDrawing();
if (drawingsDocument.Root == null)
{
drawingsDocument.Add(
newDrawingDocument.Root
);
}
else
{
drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root);
}
//Adds content to chartsheet document to reference drawing document
chartsheetDocument
.Element(ns + "chartsheet")
.Add(
new XElement(ns + "drawing",
new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
)
);
//creates the chart part and associates it to the drawings part
OpenXmlSDK.ChartPart chartPart = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>();
XDocument chartDocument = parentDocument.GetXDocument(chartPart);
XDocument newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart();
if (chartDocument.Root == null)
{
chartDocument.Add(
newChartDocument.Root
);
}
else
{
chartDocument.Root.ReplaceWith(newChartDocument.Root);
}
//Adds content to drawing document to reference chart document
drawingsDocument
.Descendants(drawingns + "graphicData")
.First()
.Add(
new XAttribute("uri", chartns),
new XElement(chartns + "chart",
new XAttribute(XNamespace.Xmlns + "c", chartns),
new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
)
);
//Associates the chartsheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;
int chartsheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("chart")
)
.Count() + 1;
//Adds content to workbook document to reference chartsheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
)
);
return(chartsheetPart);
}
/// <summary>
/// Copies a sheet with a given sheet name and saves it with the clonedSheetName in the workbook
/// </summary>
/// <param name="docType">DocType to created for temp file in the method</param>
/// <param name="workbookPart">Workbook which the worksheet to be copied and the copied worksheet to be saved</param>
/// <param name="sheetName">Name of the worksheet to copy</param>
/// <param name="clonedSheetName">New name of the copied sheet</param>
private void copySheet(SpreadsheetDocumentType docType,
WorkbookPart workbookPart, string sheetName,
string clonedSheetName)
{
//Get the source sheet to be copied
WorksheetPart sourceSheetPart = getWorksheetPart(workbookPart,
sheetName);
//Take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet =
SpreadsheetDocument.Create(new MemoryStream(), docType);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart =
tempWorkbookPart.AddPart(sourceSheetPart);
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = workbookPart.AddPart(tempWorksheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts =
sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
_tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0) {
fixupTableParts(clonedSheet);
}
//There should only be one sheet that has focus
cleanView(clonedSheet);
//Add new sheet to main workbook part
var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
var copiedSheet = new Sheet();
copiedSheet.Name = clonedSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint) sheets.ChildElements.Count + 1;
sheets.Append(copiedSheet);
//Save Changes
workbookPart.Workbook.Save();
}
/// <summary>
/// Generate service order report
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
{
if (itemsource.IsSelectedServiceOrder)
{
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// The SheetData object will contain all the data.
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
Form serviceOrder = itemsource.ServiceOrderData;
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[9];
for (int n = 0; n < 9; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 9; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:E2";
mergeCells.Append(mergeCell);
currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:E4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));
worksheet.Append(columns);
int rowIndex = 8;
Row sectionRow;
foreach (var section in serviceOrder.Sections)
{
sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
mergeCell = new MergeCell();
mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
mergeCells.Append(mergeCell);
AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
sheetData.Append(sectionRow);
foreach (var element in section.FormElements)
{
rowIndex++;
//The current row is obtained for updating the value of the cell
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
switch (element.Field.FieldType)
{
case FieldType.Catalogue:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
if (!string.IsNullOrEmpty(element.Field.FieldValue))
{
string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
}
else
{
AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
}
break;
case FieldType.RegularExpressionText:
case FieldType.Time:
case FieldType.SingleTextLine:
case FieldType.MultipleTextLine:
case FieldType.Datepicker:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
case FieldType.Boolean:
string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
break;
case FieldType.Integer:
case FieldType.Decimal:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
default:
break;
}
sheetData.Append(rowData);
}
rowIndex+=2;
}
worksheet.Append(sheetData);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
/// <summary>
/// Generate inspection reports
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
foreach (var item in itemsource.InspectionReports)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = item.Value.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < item.Value.Captions.Count; i++)
{
var caption = item.Value.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var row in item.Value.DataRows)
{
int numberOfColumnsData = row.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the row
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = row.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
protected internal override void CreateDocument()
{
Report r = base.Report();
_workbookPart = _spreadSheet.AddWorkbookPart();
var openXmlExportHelper = new OpenXmlWriterHelper();
_styleSheet = openXmlExportHelper.CreateDefaultStylesheet();
_workbookPart.Workbook = _workbook;
var sheets = _workbook.AppendChild<Sheets>(new Sheets());
// create worksheet 1
var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name };
sheets.Append(sheet);
_workSheetSettings.Add(new WorkSheetSetting(r.Name));
_writer = OpenXmlWriter.Create(worksheetPart);
_writer.WriteStartElement(new Worksheet());
_writer.WriteStartElement(new SheetData());
_currentWorkSheet = _workSheetSettings.Last();
}
//private static void AddTextToWorkSheet(string path, string sheetName, string text, string columnName, uint rowIndex)
//{
// // Open the document for editing.
// using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
// {
// // Get the SharedStringTablePart. If it does not exist, create a new one.
// SharedStringTablePart shareStringPart;
// if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
// {
// shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
// }
// else
// {
// shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
// }
// // Insert the text into the SharedStringTablePart.
// int index = InsertSharedStringItem(spreadSheet.WorkbookPart, text);
// // Insert a new worksheet.
// WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart, sheetName);
// // Insert cell A1 into the new worksheet.
// Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
// // Set the value of cell A1.
// cell.CellValue = new CellValue(index.ToString());
// cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
// // Save the new worksheet.
// worksheetPart.Worksheet.Save();
// }
//}
public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
{
Sheet foundSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);
if (foundSheet == null)
{
// Add a new worksheet part to the workbook.
var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
var 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;
}
// Append the new worksheet and associate it with the workbook.
var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append((IEnumerable<OpenXmlElement>)sheet);
return newWorksheetPart;
}
return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
}
// Given a WorkbookPart, inserts a new worksheet.
public 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();
// GetFirstChild <T>: Find the first child element in type T
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>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
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;
}
