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();
}
