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