public int write(List <List <IWritable> > dataList) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); continue; } // append section begins //_writer.WriteStartElement(new SheetData()); // beginning of sheetdata for (int rowIndex = 0; rowIndex < dataList.Count; rowIndex++) { Row row = new Row(); _writer.WriteStartElement(row); // begining of row for (int colIndex = 0; colIndex < dataList.Count; colIndex++) { Cell cell = new Cell(); cell.CellValue = new CellValue(dataList[rowIndex][colIndex].ToString()); _writer.WriteElement(cell); } _writer.WriteEndElement(); // end of row } _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
public int writeHeader(List <ChannelModel> channels, OperatorModel operatorParam, FacilityModel facilityParam) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsEndElement) { continue; } _writer.WriteStartElement(new SheetData()); // beginning of sheetdata // append section begins var labelRow = new Row(); // label row labelRow.RowIndex = 1; _writer.WriteStartElement(labelRow); // begining of row var operatorLabelCell = new Cell(); var facilityLabelCell = new Cell(); var creationDateLabelCell = new Cell(); operatorLabelCell.CellValue = new CellValue(" Operatör "); facilityLabelCell.CellValue = new CellValue(" Tesis "); creationDateLabelCell.CellValue = new CellValue(" Oluşturulma Tarihi "); _writer.WriteElement(operatorLabelCell); _writer.WriteElement(facilityLabelCell); _writer.WriteElement(creationDateLabelCell); _writer.WriteEndElement(); // end of label row var infoRow = new Row(); // info row infoRow.RowIndex = 2; _writer.WriteStartElement(infoRow); // begining of row var operatorInfoCell = new Cell(); var facilityInfoCell = new Cell(); var creationDateInfoCell = new Cell(); operatorInfoCell.CellValue = new CellValue(operatorParam.DisplayName); facilityInfoCell.CellValue = new CellValue(facilityParam.ListDisplay); creationDateInfoCell.CellValue = new CellValue(DateTime.Now.ToShortTimeString()); _writer.WriteElement(operatorInfoCell); _writer.WriteElement(facilityInfoCell); _writer.WriteElement(creationDateInfoCell); _writer.WriteEndElement(); // end of info row var channelListRow = new Row(); // channelListRow row channelListRow.RowIndex = 3; _writer.WriteStartElement(channelListRow); // begining of row for (int k = 0; k < channels.Count; k++) { var dataInfoCell = new Cell(); dataInfoCell.CellValue = new CellValue(channels[k].name); _writer.WriteElement(dataInfoCell); } _writer.WriteEndElement(); // end of channel info row _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
public void ReplaceTemplateValuesByToken(Dictionary <string, string> tokens, WorksheetPart workSheetPart) { List <string> sharedStrings = GetSharedStringList(); StartWritingWithTemplate(workSheetPart); while (_reader.Read()) { if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsEndElement) { continue; } //Reading template sheet data _writer.WriteStartElement(new SheetData()); _reader.Read(); while (true) { if (_reader.ElementType == typeof(SheetData) && _reader.IsEndElement) { break; } if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType.IsSubclassOf(typeof(OpenXmlLeafTextElement))) { string text; try { int index = Convert.ToInt32(_reader.GetText()); text = sharedStrings[index]; } catch { text = _reader.GetText(); } tokens.Keys.Where(x => text.IndexOf(x) != -1).ToList().ForEach(x => text = text.Replace(x, tokens[x])); _writer.WriteString(text); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } _reader.Read(); } _writer.WriteEndElement();//close sheet //_workbookPart.Workbook.Save(); //break; } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } } } EndWritingWithTemplate(); }