public void DomReaderBasicTest()
{
bool moved = false;
string paragraphOuterXml = "<w:p w:rsidP=\"001\" xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>";
Paragraph para = new Paragraph(paragraphOuterXml);
OpenXmlReader targetReader = OpenXmlReader.Create(para);
targetReader.Read();
TestReaderOnParagraph(targetReader);
moved = targetReader.Read(); // should be false
Assert.False(moved);
Assert.True(targetReader.EOF);
// Assert.NotNull(targetReader.Attributes);
// Assert.Equal(0, targetReader.Attributes.Count);
// Assert.False(targetReader.HasAttributes);
// Assert.False(targetReader.IsStartElement);
// Assert.False(targetReader.IsEndElement);
// Assert.False(targetReader.IsMiscNode);
// Assert.Equal(typeof(Paragraph), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
targetReader.Close();
targetReader = OpenXmlReader.Create(para);
targetReader.Read();
moved = targetReader.ReadFirstChild(); // read to <w:r>
OpenXmlElement element = targetReader.LoadCurrentElement();
// at the end of the run
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Equal(0, targetReader.Attributes.Count);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Run), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
// loaded element is Run
Assert.NotNull(element);
Assert.IsType(typeof(Run), element);
Run run = (Run)element;
Assert.Equal("Run Text.", (run.FirstChild as Text).Text);
Assert.Equal("Run 2.", (run.LastChild as Text).Text);
targetReader.Close();
}
public int close()
{
_reader.Close();
_reader.Dispose();
_doc.Close();
_doc.Dispose();
_doc = null;
return(1);
}
public void Dispose()
{
if (_reader != null)
{
_reader.Close();
_reader.Dispose();
_reader = null;
}
if (_document != null)
{
_document.Dispose();
_document = null;
}
}
public void PartReaderMiscNodeTest()
{
string partText = "<w:document xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\">" +
"<!-- start body -->" +
"<w:body>" +
"<w:p w:rsidP=\"001\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>" +
"</w:body>" +
"</w:document>";
UTF8Encoding utf8Encoding = new UTF8Encoding();
Stream stream = new MemoryStream(utf8Encoding.GetBytes(partText), false);
//======== new test with a new reader ========
OpenXmlReader reader = OpenXmlReader.Create(stream, true); // read misc nodes
Assert.False(reader.EOF);
bool moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.True(reader.GetLineInfo().HasLineInfo());
Assert.Equal(1, reader.GetLineInfo().LineNumber);
Assert.Equal(2, reader.GetLineInfo().LinePosition);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.True(reader.GetLineInfo().HasLineInfo());
Assert.Equal(1, reader.GetLineInfo().LineNumber);
Assert.Equal(88, reader.GetLineInfo().LinePosition);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
}
private void EndWritingWithTemplate()
{
_reader.Close();
_writer.Close();
//HandleMergedCells();
Sheet sheet = _workbookPart.Workbook.Sheets.Cast <Sheet>().Where(x => x.Id.Value.Equals(originalPartId)).FirstOrDefault();
if (sheet != null)
{
sheet.Id.Value = replacementPartId;
}
WorksheetPart originalPart = (WorksheetPart)_workbookPart.GetPartById(originalPartId);
_workbookPart.DeletePart(originalPart);
}
private static void displayControlDetails(WordprocessingDocument doc, StringValue controlId)
{
string classId, type, value;
OpenXmlPart part = doc.MainDocumentPart.GetPartById(controlId);
OpenXmlReader reader = OpenXmlReader.Create(part.GetStream());
reader.Read();
OpenXmlElement controlDetails = reader.LoadCurrentElement();
classId = controlDetails.GetAttribute("classid", controlDetails.NamespaceUri).Value;
switch (classId)
{
case textBoxId:
type = "TextBox";
break;
case radioButtonId:
type = "Radio Button";
break;
case checkBoxId:
type = "CheckBox";
break;
default:
type = "Not known";
break;
}
value = "No value attribute"; //displays this if there is no "value" attribute found
foreach (OpenXmlElement child in controlDetails.Elements())
{
if (child.GetAttribute("name", controlDetails.NamespaceUri).Value == "Value")
{
//we've found the value typed by the user in this control
value = child.GetAttribute("value", controlDetails.NamespaceUri).Value;
}
}
reader.Close();
Console.WriteLine("Class id: {0}", classId);
Console.WriteLine("Control type: {0}", type);
Console.WriteLine("Control value: {0}", value);
}
private void ExportWorksheetPart(WorksheetPart worksheetPart, string filename)
{
currColumnIndex = 0;
rowCount = 0;
headers.Clear();
columnsToSkip.Clear();
using (StreamWriter writer = new StreamWriter(filename))
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
#region SheetDimension
if (reader.LocalName == "dimension")
{
if (reader.IsStartElement)
{
HandleSheetDimensionStartElement(reader);
continue;
}
else
{
continue;
}
}
#endregion SheetDimension
#region Row
if (reader.LocalName == "row")
{
if (reader.IsStartElement)
{
continue;
}
else
{
HandleRowEndElement(reader, writer);
continue;
}
}
#endregion Row
#region Cell
if (reader.LocalName == "c")
{
if (reader.IsStartElement)
{
HandleCellStartElement(reader);
continue;
}
else
{
HandleCellEndElement(reader);
continue;
}
}
#endregion Cell
#region CellValue
if (reader.LocalName == "v")
{
if (reader.IsStartElement)
{
HandleCellValueStartElement(reader);
continue;
}
else
{
continue;
}
}
#endregion CellValue
#region InlineString
if (reader.LocalName == "is")
{
if (reader.IsStartElement)
{
HandleInlineStringStartElement(reader);
continue;
}
else
{
continue;
}
}
#endregion InlineString
}
reader.Close();
}
}
public void PartReaderBasicTest()
{
string partText = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" +
"<w:document xmlns:w=\"http://schemas.openxmlformats.org/wordprocessingml/2006/main\">" +
"<w:body>" +
"<w:p w:rsidP=\"001\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>" +
"<w:p w:rsidP=\"001\"><w:r><w:t>Run Text.</w:t><w:t>Run 2.</w:t></w:r></w:p>" +
"</w:body>" +
"</w:document>";
UTF8Encoding utf8Encoding = new UTF8Encoding();
Stream stream = new MemoryStream(utf8Encoding.GetBytes(partText), false);
OpenXmlReader targetReader = OpenXmlReader.Create(stream);
targetReader.Read();
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
Assert.False(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal(typeof(Document), targetReader.ElementType);
targetReader.ReadFirstChild(); // to <w:body>
targetReader.Read(); // to <w:p>
TestReaderOnParagraph(targetReader);
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Paragraph), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.True(targetReader.GetLineInfo().HasLineInfo());
Assert.Equal(1, targetReader.GetLineInfo().LineNumber);
Assert.Equal(216, targetReader.GetLineInfo().LinePosition);
targetReader.ReadNextSibling(); // next <w:p>
OpenXmlElement element = targetReader.LoadCurrentElement();
// at </w:p>
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Empty(targetReader.Attributes);
Assert.False(targetReader.HasAttributes);
Assert.False(targetReader.IsStartElement);
Assert.True(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.Equal(typeof(Paragraph), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal(1, targetReader.GetLineInfo().LineNumber);
Assert.Equal(295, targetReader.GetLineInfo().LinePosition);
// loaded element is Run
Assert.NotNull(element);
Assert.IsType <Paragraph>(element);
Run run = (Run)element.FirstChild;
Assert.Equal("Run Text.", (run.FirstChild as Text).Text);
Assert.Equal("Run 2.", (run.LastChild as Text).Text);
targetReader.Close();
}
public void OpenXMLReadValues_PortefeuilleModele2()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_JMOINS1");
DataTable excelDataTable = null;
List <string> columns = new List <string>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"C:\DATA\Portefeuille modèle.xlsm", false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, "modele_€");
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string text;
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
// SheetData sd = (SheetData)reader.LoadCurrentElement();
reader.ReadFirstChild();
do// while : Skip to the next row
{
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
Console.WriteLine("rowNum: " + rowNum);
}
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
do// while: next Cell
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else if (c.CellValue != null)
{
cellValue = c.CellValue.InnerText;
}
else
{
cellValue = "Empty";
}
Console.WriteLine("{0}: {1} ", c.CellReference, cellValue);
}
} while (reader.ReadNextSibling());// while: next Cell
}
Console.WriteLine("END ROW");
} while (reader.ReadNextSibling());// while : Skip to the next row
Console.WriteLine("END ROW");
}
if (reader.ElementType != typeof(Worksheet))
{
reader.Skip();
}
}
reader.Close();
}
// s.bulkcopyData(dt: excelDataTable, nomTableDestination : "PTF_FGA");
}
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 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);
}
/// <summary>
/// Load file and add it to Excel class.
/// </summary>
public void Load(string fileName)
{
FileNameAdd(fileName);
//
SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false);
//
Dictionary <string, string> sheetIdToName = new Dictionary <string, string>();
foreach (Sheet sheet in document.WorkbookPart.Workbook.Sheets)
{
sheetIdToName.Add(sheet.Id, sheet.Name);
SheetNameAdd(fileName, sheet.Name);
}
//
foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
string sheetId = document.WorkbookPart.GetIdOfPart(worksheetPart);
string sheetName = sheetIdToName[sheetId];
SharedStringItem[] sharedStringList = new SharedStringItem[0];
if (document.WorkbookPart.SharedStringTablePart != null)
{
sharedStringList = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
}
//
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
Row row = (Row)reader.LoadCurrentElement();
string rowName = row.RowIndex;
foreach (Cell cell in row.Elements <Cell>().ToArray())
{
string text = null;
bool isText = false;
bool isError = false;
string columnName = cell.CellReference.Value.Substring(0, cell.CellReference.Value.Length - rowName.Length);
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.Boolean:
text = cell.CellValue.Text;
break;
case CellValues.SharedString:
isText = true;
int index = int.Parse(cell.CellValue.InnerText);
SharedStringItem item = sharedStringList[index];
if (item.Text != null)
{
text = item.Text.Text;
}
else
{
text = item.InnerText; // For cell text with formating. (Formating is removed when read like this).
}
break;
case CellValues.String:
isText = true;
text = cell.CellValue.Text;
break;
case CellValues.Error:
isError = true;
text = cell.CellValue.Text;
break;
default:
throw new Exception("Type unknown!");
}
}
else
{
if (cell.CellValue != null)
{
text = cell.CellValue.Text; // Number.
}
}
//
if (text != null)
{
text = text.Trim();
}
if (!string.IsNullOrEmpty(text))
{
CellSet(fileName, sheetName, rowName, columnName, new ExcelCell(text, isText, isError));
}
}
}
}
reader.Close();
}
document.Close();
}
public void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
WorksheetPart replacementPart =
workbookPart.AddNewPart <WorksheetPart>();
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
Row r = new Row();
Cell c = new Cell();
CellFormula f = new CellFormula();
f.CalculateCell = true;
f.Text = "RAND()";
c.Append(f);
CellValue v = new CellValue();
c.Append(v);
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
if (reader.IsEndElement)
{
continue;
}
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else
{
if (reader.IsStartElement)
{
writer.WriteStartElement(reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
}
reader.Close();
writer.Close();
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>()
.Where(s => s.Id.Value.Equals(origninalSheetId)).First();
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
}
/// <summary>
/// read the XMLX File and fill the DataTable in the DataSet
/// </summary>
/// <param name="dataSet"></param>
/// <param name="dataTableName"></param>
/// <returns></returns>
public new int Fill(DataSet dataSet, string dtTableName = "Table")
{
string dataTableName = dtTableName;
System.Data.DataTable dt = this.PrepareDataTable(dataSet, ref dataTableName);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.XMLFilePath, false))
{
// Output
string[] headers = new string[(int)(this.EndCell_Column - this.StartCell_Column + 1)];
int nbRows = 0;
//Input: open the worksheet
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart;
if (this.WorksheetName != null)
{
worksheetPart = GetWorksheetPartByName(spreadsheetDocument, WorksheetName);
}
else
{
worksheetPart = GetFirstWorksheetPart(spreadsheetDocument);
}
// Starting
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
// SheetData sd = (SheetData)reader.LoadCurrentElement();
reader.ReadFirstChild();
do// while : Skip to the next row
{
if (reader.ElementType == typeof(Row))
{
rowNum = null;
DataRow row = null;
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
}
if (rowNum != null)
{
reader.ReadFirstChild();
do// while: next Cell
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
// Calculate the transformation between Cell reference inside the excel file and the output datatable
double[] coord; // coordinate in (int,int)
double[] outputColumnRow = this.GetRecordedCellXY(c.CellReference, out coord);
//1) if the cell is in the area defined by startCell/endCell
if (outputColumnRow != null)
{
string value = this.GetCellValue(c, workbookPart);
if (this.WithHeaderOnFirstRow && outputColumnRow[1] == 0)
{
KeyValuePair <string, Type> headerName = this.GetMappingColumnName(dtTableName, value);
headers[(int)outputColumnRow[0]] = headerName.Key;
dt.Columns.Add(headerName.Key, headerName.Value /*Type of column*/);
}
else
{
if (row == null)
{
row = dt.NewRow();
}
string columnName = headers[(int)outputColumnRow[0]];
if (value != null && value.Length > 0)
{
if (dt.Columns[columnName].DataType == System.Type.GetType("System.DateTime"))
{
double d;
if (double.TryParse(value, out d))
{
row.SetField <DateTime>(columnName, DateTime.FromOADate(d));
}
else
{
row.SetField <Object>(columnName, value);
}
}
else
{
row.SetField <Object>(columnName, value);
}
}
else
{
row.SetField <Object>(columnName, null);
}
}
}
KeyValuePair <string, Type> cn;
// 2) if there are Column Filling with value of the cell
if (this.columnsFilling.TryGetValue(c.CellReference, out cn))
{
string value = this.GetCellValue(c, workbookPart);
Object v;
if (cn.Value.Equals(typeof(System.DateTime)))
{
v = Convert.ToDateTime(value);
}
else
{
v = Convert.ChangeType(value, cn.Value);
}
columnsFillingConstant.Add(cn.Key, v);
}
}
} while (reader.ReadNextSibling());// while: next Cell
}
if (row != null)
{
dt.Rows.Add(row);
nbRows++;
}
}
//
} while (reader.ReadNextSibling());// while : Skip to the next row
}
if (reader.ElementType != typeof(Worksheet))
{
reader.Skip();
}
}
reader.Close();
dt.AddConstantInColumn(this.columnsFillingConstant);
return(nbRows);
}
}
public void DomReaderMiscNodeTest()
{
Body body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
body.PrependChild(new OpenXmlMiscNode(System.Xml.XmlNodeType.Comment, "<!-- start body -->"));
//======== new test with a new reader ========
OpenXmlReader reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
bool moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("body", reader.LocalName);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("#comment", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// test case: for ReadFirstChild
reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
moved = reader.Read();
Assert.False(reader.EOF);
moved = reader.ReadFirstChild();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("#comment", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
OpenXmlElement miscNode = body.RemoveChild(body.FirstChild);
body.AppendChild(miscNode);
reader = OpenXmlReader.Create(body, true); // read misc nodes
Assert.False(reader.EOF);
moved = reader.Read();
Assert.False(reader.EOF);
moved = reader.ReadFirstChild();
reader.Skip();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// test case: root element is misc node
reader = OpenXmlReader.Create(new OpenXmlMiscNode(System.Xml.XmlNodeType.ProcessingInstruction, "<?pi test?>"), true);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.True(reader.IsMiscNode);
Assert.Equal("pi", reader.LocalName);
Assert.Equal(string.Empty, reader.Prefix);
Assert.Equal(string.Empty, reader.NamespaceUri);
reader.Close();
// case bug #253890
body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
miscNode = body.AppendChild(new OpenXmlMiscNode(System.Xml.XmlNodeType.Comment, "<!-- start body -->"));
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("p", reader.LocalName);
reader.Skip();
Assert.True(reader.EOF);
reader.Close();
// case bug #253890
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
reader.ReadNextSibling();
Assert.True(reader.EOF);
reader.Close();
// case bug #253890
reader = OpenXmlReader.Create(body.FirstChild, true);
moved = reader.Read();
moved = reader.Read();
moved = reader.ReadNextSibling();
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.False(reader.IsStartElement);
Assert.True(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.Equal("p", reader.LocalName);
// call ReadNextSibling() on EndElement
reader.ReadNextSibling();
Assert.True(reader.EOF);
}
public void DomReaderRootCaseTest()
{
Body body = new Body(new Paragraph(new ParagraphProperties(), new Run(new Text("test"))));
//======== new test with a new reader ========
OpenXmlReader reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
bool moved = reader.Read();
Assert.True(moved);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
// ReadNextSibling on root start
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.True(reader.EOF);
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
moved = reader.Read();
moved = reader.ReadFirstChild();
Assert.True(moved);
Assert.True(reader.IsStartElement);
Assert.False(reader.IsEndElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(1, reader.Depth);
Assert.Equal("p", reader.LocalName);
moved = reader.ReadNextSibling();
Assert.False(moved); // no more sibling
Assert.True(reader.IsEndElement); // should on Body end now.
Assert.False(reader.IsStartElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.Equal("body", reader.LocalName);
// ReadNextSibling on root end
moved = reader.ReadNextSibling();
Assert.False(moved);
Assert.True(reader.EOF); // should be EOF now.
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
moved = reader.Read();
// bug #248145
// call Skip on root start
reader.Skip();
Assert.True(reader.EOF);
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
moved = reader.Read();
moved = reader.ReadFirstChild();
reader.Skip();
Assert.True(reader.IsEndElement); // should on Body end now.
Assert.False(reader.IsStartElement);
Assert.False(reader.IsMiscNode);
Assert.False(reader.EOF);
Assert.Equal(0, reader.Depth);
Assert.Equal("body", reader.LocalName);
// call Skip on root end
reader.Skip();
Assert.True(reader.EOF);
reader.Close();
//======== new test with a new reader ========
reader = OpenXmlReader.Create(body);
Assert.False(reader.EOF);
moved = reader.Read();
moved = reader.ReadFirstChild();
reader.Skip();
// call Read on root end
moved = reader.Read();
Assert.False(moved);
Assert.True(reader.EOF);
// additional operations should return false.
moved = reader.Read();
Assert.False(moved);
moved = reader.ReadFirstChild();
Assert.False(moved);
moved = reader.ReadNextSibling();
Assert.False(moved);
reader.Skip();
reader.Close();
}
public static void GetDataResultsAdjust(IList <RecordAdjust> lstRecord, string filePath, string templatePath, string status, string fromdate, string todate)
{
if ((!File.Exists(templatePath)))
{
//If we do not have a template file, we need to create one
CreateSpreadsheetWorkbook(templatePath);
}
//Copy the template file to the output file location
File.Copy(templatePath, filePath, true);
//Open the copied file
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true))
{
//Navigate to the workbook part
WorkbookPart workbookPart = myDoc.WorkbookPart;
//open the first worksheet
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
//Get the id of this sheet. We need this because we are going to add a new
//worksheet to this workbook, then we are going to delete this worksheet
//This ID will tell us which one to delete
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
//Add the new worksheet
WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>();
//This is the ID of the new worksheet
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
//We are going to read from the original worksheet to get the
//templated items that we added to the worksheet in the traditional way
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//We are goint to copy the items from the original worksheet by using
//an XML writer, this overcomes the memory limitations of having
//an extremely large dataset.
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
//The template does not have any data so we will be creating new rows and cells
//Then writing them using XML.
Row r = new Row();
Cell c = new Cell();
while ((reader.Read()))
{
//This iterates through the sheet data and copies it
if ((object.ReferenceEquals(reader.ElementType, typeof(SheetData))))
{
//Exit the loop if we hit a sheetdata end element
if ((reader.IsEndElement))
{
break; // TODO: might not be correct. Was : Exit While
}
//We create a new sheetdata element (basically this is the reoot container for a sheet)
writer.WriteStartElement(new SheetData());
if (status == "0")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN THAY THẾ", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
else if (status == "1")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN ĐIỀU CHỈNH", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
int rowIndextieudea = 3;
r.RowIndex = (UInt32)rowIndextieudea;
writer.WriteStartElement(r);
c = CreateCell(0, "Từ ngày:" + fromdate, rowIndextieudea);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudeb = 4;
r.RowIndex = (UInt32)rowIndextieudeb;
writer.WriteStartElement(r);
c = CreateCell(0, "Đến ngày:" + todate, rowIndextieudeb);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudec = 5;
r.RowIndex = (UInt32)rowIndextieudec;
writer.WriteStartElement(r);
c = CreateCell(0, "Tổng số hóa đơn:" + lstRecord.Count(), rowIndextieudec);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndex = 6;
r.RowIndex = (UInt32)rowIndex;
//Start the first row.
writer.WriteStartElement(r);
//Iterate through the columns to write a row conaining the column names
c = CreateCell(0, "STT", rowIndex);
writer.WriteElement(c);
c = CreateCell(1, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(2, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(3, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(4, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(5, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(6, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(7, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(8, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(9, "Người thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(10, "Ngày thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(11, "Trạng thái", rowIndex);
writer.WriteElement(c);
//End first row
writer.WriteEndElement();
foreach (var item in lstRecord)
{
if (item == null)
{
continue;
}
rowIndex = rowIndex + 1;
r.RowIndex = (UInt32)rowIndex;
writer.WriteStartElement(r);
//iterate through the columns to write their data
c = CreateCell(0, item.stt.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(1, item.patternOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(2, item.serialOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(3, string.Format("{0:0000000}", Convert.ToInt32(item.noOlder)), rowIndex); writer.WriteElement(c);
//c = CreateCell(4, item.cusnameOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(5, item.patternNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(6, item.serialNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(7, string.Format("{0:0000000}", Convert.ToInt32(item.noNew)), rowIndex); writer.WriteElement(c);
c = CreateCell(8, item.cusnameNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(9, item.username.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(10, String.Format("{0:dd/MM/yyyy}", DateTime.Parse(item.proccessdate)), rowIndex); writer.WriteElement(c);
if (item.status == 1)
{
c = CreateCell(11, "Lập hóa đơn thay thế", rowIndex); writer.WriteElement(c);
}
else if (item.status == 2)
{
c = CreateCell(11, "Lập hóa đơn điều chỉnh", rowIndex); writer.WriteElement(c);
}
//write the end of the row
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else if ((reader.IsStartElement))
{
//Start elements are directly copied
writer.WriteStartElement(reader);
}
else if ((reader.IsEndElement))
{
//End elements are directly copied
writer.WriteEndElement();
}
}
//Close the reader and writer
reader.Close();
writer.Close();
//Get the newly created sheet (same id as the old sheet, but it is the first one)
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
//Assign it the new sheet id
sheet.Id.Value = replacementPartId;
//remove the old sheet
workbookPart.DeletePart(worksheetPart);
//Done
myDoc.Close();
//Delete template file
if (File.Exists(templatePath))
{
File.Delete(templatePath);
}
}
}
/// <summary>
/// Disposes of open resources
/// </summary>
public void Dispose()
{
reader.Close();
}
/// <summary>
/// Add the table of contents
/// </summary>
/// <param name="document"></param>
/// <param name="tableOfContents"></param>
public static void AddToC(MainDocumentPart documentPart, TableOfContents tableOfContents)
{
StringBuilder tocParameters = new StringBuilder();
if (tableOfContents.StylesAndLevels.Any())
{
StringBuilder tocParametersFr = new StringBuilder();
//English
tocParameters.Append(@"TOC \h \z \t ");
//French
tocParametersFr.Append(@"TOC \h \z \t ");
foreach (Tuple <string, string> styleAndLevel in tableOfContents.StylesAndLevels)
{
//English
tocParameters.Append(string.Join(",", styleAndLevel.Item1, styleAndLevel.Item2));
tocParameters.Append(",");
//French
tocParametersFr.Append(string.Join(";", styleAndLevel.Item1, styleAndLevel.Item2));
tocParametersFr.Append(";");
}
tocParameters.Append(" ");
tocParameters.Append(tocParametersFr);
}
else
{
//default switches
tocParameters.Append(@"TOC \o '1-3' \h \z \u");
}
string xmlString =
@"<w:sdt xmlns:w='http://schemas.openxmlformats.org/wordprocessingml/2006/main'>
<w:sdtPr>
<w:docPartObj>
<w:docPartGallery w:val='Table of Contents'/>
<w:docPartUnique/>
</w:docPartObj>
</w:sdtPr>
<w:sdtEndPr>
<w:rPr>
<w:rFonts w:asciiTheme='minorHAnsi' w:cstheme='minorBidi' w:eastAsiaTheme='minorHAnsi' w:hAnsiTheme='minorHAnsi'/>
<w:color w:val='auto'/>
<w:sz w:val='22'/>
<w:szCs w:val='22'/>
<w:lang w:eastAsia='en-US'/>
</w:rPr>
</w:sdtEndPr>
<w:sdtContent>";
if (!string.IsNullOrEmpty(tableOfContents.Title))
{
xmlString += @"
<w:p>
<w:pPr>
<w:pStyle w:val='" + tableOfContents.TitleStyleId + @"'/>
</w:pPr>
<w:r>
<w:t>" + tableOfContents.Title + @"</w:t>
</w:r>
</w:p>";
}
xmlString += @"
<w:p>
<w:pPr>
<w:rPr>
<w:noProof/>
</w:rPr>
</w:pPr>
<w:pPr>
<w:tabs>
<w:tab w:val='right' w:leader='" + tableOfContents.LeaderCharValue.ToString() + @"'/>
</w:tabs>
<w:rPr>
<w:noProof/>
</w:rPr>
</w:pPr>
<w:r>
<w:fldChar w:fldCharType='begin' w:dirty='true'/>
</w:r>
<w:r>
<w:instrText xml:space='preserve'> " + tocParameters + @" </w:instrText>
</w:r>
<w:r>
<w:fldChar w:fldCharType='separate'/>
</w:r>
</w:p>
<w:p>
<w:r>
<w:rPr>
<w:b/>
<w:bCs/>
<w:noProof/>
</w:rPr>
<w:fldChar w:fldCharType='end'/>
</w:r>
</w:p>
</w:sdtContent>
</w:sdt>";
using (StreamWriter sw = new StreamWriter(new MemoryStream()))
{
sw.Write(xmlString);
sw.Flush();
sw.BaseStream.Seek(0, SeekOrigin.Begin);
OpenXmlReader re = OpenXmlReader.Create(sw.BaseStream);
re.Read();
OpenXmlElement oxe = re.LoadCurrentElement();
documentPart.Document.Body.AppendChild(oxe);
re.Close();
}
}