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();
}
private static void ParseViaSAX(string physicalPath)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(physicalPath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var sheetCount = workbookPart.Workbook.Sheets.Count();
foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
{
var columnCount = wsp.Worksheet.Descendants <Column>().Count();
var rowCount = wsp.Worksheet.Descendants <Row>().Count();
var cellCount = wsp.Worksheet.Descendants <Cell>().Count();
OpenXmlReader reader = OpenXmlReader.Create(wsp);
string text;
while (reader.Read())
{
text = reader.GetText();
System.Diagnostics.Debug.WriteLine(text + "");
//if (reader.ElementType == typeof(CellValue))
//{
// text = reader.GetText();
// Console.Write(text + " ");
// System.Diagnostics.Debug.WriteLine(text + "");
//}
}
}
}
}
public static void WriteStartElement(XmlWriter writer, OpenXmlReader reader)
{
if (string.IsNullOrEmpty(reader.Prefix) || reader.Prefix.Equals("x", StringComparison.Ordinal))
{
var prolog = writer.WriteState == WriteState.Prolog;
writer.WriteStartElement(reader.LocalName, reader.NamespaceUri);
if (prolog)
{
writer.WriteAttributeString("xmlns", null, @"http://www.w3.org/2000/xmlns/", reader.NamespaceUri);
}
}
else
{
writer.WriteStartElement(reader.Prefix, reader.LocalName, reader.NamespaceUri);
}
WriteNamespace(writer, reader.NamespaceDeclarations);
WriteAttributes(writer, reader.Attributes);
var text = reader.GetText();
if (!string.IsNullOrEmpty(text))
{
writer.WriteString(text);
}
}
/// <summary>
/// Test OpenXmlReader GetText() method, and verify the result with XmlReader
/// </summary>
/// <param name="reader">the OpenXmlReader that will be tested</param>
/// <param name="XTreader">the corresponding XmlReader used to verify the result</param>
private void TestGetText(OpenXmlReader reader, XmlReader Xreader)
{
Log.Comment("Test GetText()");
if (!reader.EOF &&
reader.ElementType != typeof(OpenXmlMiscNode) &&
reader.ElementType != typeof(OpenXmlUnknownElement) &&
Activator.CreateInstance(reader.ElementType) is OpenXmlLeafTextElement)
{
var Text = reader.GetText();
Log.VerifyTrue(Xreader.ReadContentAsString() == Text, "expected: '{0}' <> actual: '{1}'", Xreader.Value, Text);
Read(reader);
}
else
{
var Text = reader.GetText();
Assert.Equal(string.Empty, Text);
}
}
static void ReadExcelFileSAX(string fileName)
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheet.WorkbookPart;
WorksheetPart worksheet = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheet);
string text;
while (reader.Read())
{
if (reader.ElementType == typeof(CellValue))
{
text = reader.GetText();
Console.WriteLine(text);
}
}
}
}
private static List <string> GetSharedStrings(string xlsxFile)
{
List <string> dicionario = new List <string>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(xlsxFile, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//Get the string List, which is stored in a separate file
var sharedStringPart = workbookPart.SharedStringTablePart;
OpenXmlReader reader = OpenXmlReader.Create(sharedStringPart);
while (reader.Read())
{
if (reader.IsStartElement && reader.ElementType == typeof(Text))
{
dicionario.Add(reader.GetText());
}
}
}
return(dicionario);
}
/// <summary>
/// This method reads a excel file
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
string ReadXlsx(string path)
{
StringBuilder builder = new StringBuilder();
string text = builder.ToString();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.ElementType == typeof(CellValue))
{
builder.Append(reader.GetText());
Console.Write(builder + " ");
}
}
Console.WriteLine();
Console.ReadKey();
}
return(text);
}
// The SAX approach.
public static List <string> ReadExcelFileSAX(string fileName)
{
List <string> values = new List <string>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string text;
while (reader.Read())
{
if (reader.ElementType == typeof(CellValue))
{
text = reader.GetText();
values.Add(text + StringResources.wSpaceChar);
}
}
return(values);
}
}
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();
}
///<summary>
///TestReaderOnPragraph.
///</summary>
private void TestReaderOnParagraph(OpenXmlReader targetReader)
{
bool moved = false;
Assert.False(targetReader.EOF);
Assert.NotNull(targetReader.Attributes);
Assert.Single(targetReader.Attributes);
Assert.True(targetReader.HasAttributes);
Assert.True(targetReader.IsStartElement);
Assert.False(targetReader.IsEndElement);
Assert.False(targetReader.IsMiscNode);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal(typeof(Paragraph), targetReader.ElementType);
moved = targetReader.Read(); // read to <w:r>
Assert.True(moved);
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(Run), targetReader.ElementType);
moved = targetReader.ReadFirstChild(); // read to <w:t>
Assert.True(moved);
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.Equal(typeof(Text), targetReader.ElementType);
Assert.False(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal("Run Text.", targetReader.GetText());
moved = targetReader.Read(); // read to </w:t>
Assert.True(moved);
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(Text), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
moved = targetReader.ReadNextSibling(); // read to <w:t>
Assert.True(moved);
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.Equal(typeof(Text), targetReader.ElementType);
Assert.False(string.IsNullOrEmpty(targetReader.GetText()));
Assert.Equal("Run 2.", targetReader.GetText());
moved = targetReader.ReadNextSibling(); // read to </w:r>, skip the </w:t>
Assert.False(moved);
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(Run), targetReader.ElementType);
Assert.True(string.IsNullOrEmpty(targetReader.GetText()));
moved = targetReader.Read(); // read to </w:p>
Assert.True(moved);
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()));
}
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);
}
/// <summary>
/// Creates a CSV from the sheet number sheetId of the xlsxFile, using the separator
/// </summary>
/// <param name="xlsxFile">xlsx file to read from</param>
/// <param name="sheetId">Index of the sheet, base 1</param>
/// <param name="csvFile">Destination csv file</param>
/// <param name="separator">csv separator. Default ','</param>
public static void CreateCsv(string xlsxFile, int sheetId, string csvFile, char separator = ',')
{
int LastRow = -1;
int LastColumn = -1;
int FirstRow = -1;
int FirstColumn = -1;
List <string> dicionario = GetSharedStrings(xlsxFile);
var relationships = GetWorksheetsIds(xlsxFile);
var id = relationships.FirstOrDefault(x => x.SheetId == sheetId);
if (id == null)
{
throw new ArgumentException($"The sheet {sheetId} doesn't exists");
}
using (StreamWriter sw = new StreamWriter(csvFile, false))
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(xlsxFile, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(
relationships.First(x => x.SheetId == sheetId).RelationshipId
);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//Get sheet dimensions
while (reader.Read())
{
if (reader.IsStartElement && reader.ElementType == typeof(SheetDimension))
{
int[] dim = TranslateAddress(reader.Attributes[0].Value);
FirstRow = dim[0];
FirstColumn = dim[1];
//Range as the sheet dimension
if (dim.Length == 4)
{
LastRow = dim[2];
LastColumn = dim[3];
}
//Cell as the sheet dimension (only 1 cell has been initialized)
else
{
LastRow = dim[0];
LastColumn = dim[1];
}
break;
}
}
int ll = 0;
string[] currentRow = new string[LastColumn];
while (reader.Read())
{
if (reader.IsStartElement)
{
if (reader.ElementType == typeof(Row))
{
//Initialize a new row
currentRow = new string[LastColumn];
//Add rows that aren't in the file (unitialized rows aren't saved in xlsx)
var line = int.Parse(reader.Attributes.First(x => x.LocalName == "r").Value);
while (++ll < line)
{
var lin = string.Join(separator.ToString(), currentRow);
sw.WriteLine(lin);
}
}
else if (reader.ElementType == typeof(Cell))
{
//Pick the data type of the Cell. s for sharedString, str for string,
//b for bool and absent for numbers
var tipo = reader.Attributes.FirstOrDefault(x => x.LocalName == "t").Value;
var endereco = TranslateAddress(
reader.Attributes.First(x => x.LocalName == "r").Value
);
//Search for the CellValue or quit if it find the closing Cell tag
while (reader.ElementType != typeof(CellValue) ||
(reader.ElementType != typeof(Cell) && reader.IsEndElement))
{
reader.Read();
}
if (reader.ElementType == typeof(CellValue))
{
switch (tipo)
{
//Shared string
case "s":
currentRow[endereco[1] - 1] =
dicionario[int.Parse(reader.GetText())];
break;
//Number
case null:
//String
case "str":
currentRow[endereco[1] - 1] = reader.GetText();
break;
//Boolean
case "b":
currentRow[endereco[1] - 1] =
reader.GetText() == "1" ? "true" : "false";
break;
}
}
}
}
else
{
//Add the created row when found the closing Row tag
if (reader.ElementType == typeof(Row))
{
sw.WriteLine(string.Join(separator.ToString(), currentRow));
}
}
}
}
}
}
/// <summary>
/// Creates a DataTable from the sheet sheetName of the file xlsxFile
/// </summary>
/// <param name="xlsxFile">xlsx file to read</param>
/// <param name="sheetId">Index of the sheet, base 1</param>
/// <returns>DataTable with column names as Excel string column address</returns>
public static DataTable CreateDataTable(string xlsxFile, int sheetId)
{
DataTable dt = new DataTable();
int LastRow = -1;
int LastColumn = -1;
int FirstRow = -1;
int FirstColumn = -1;
List <string> dicionario = GetSharedStrings(xlsxFile);
var relationships = GetWorksheetsIds(xlsxFile);
var id = relationships.FirstOrDefault(x => x.SheetId == sheetId);
if (id == null)
{
throw new ArgumentException($"The sheet {sheetId} doesn't exists");
}
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(xlsxFile, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(
relationships.First(x => x.SheetId == sheetId).RelationshipId
);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//Get sheet dimensions
while (reader.Read())
{
if (reader.IsStartElement && reader.ElementType == typeof(SheetDimension))
{
int[] dim = TranslateAddress(reader.Attributes[0].Value);
FirstRow = dim[0];
FirstColumn = dim[1];
//Range as the sheet dimension
if (dim.Length == 4)
{
LastRow = dim[2];
LastColumn = dim[3];
}
//Cell as the sheet dimension (only 1 cell has been initialized)
else
{
LastRow = dim[0];
LastColumn = dim[1];
}
break;
}
}
//Define the columns. The column name is in Excel format (A,B,C...)
for (int c = 1; c <= LastColumn; c++)
{
dt.Columns.Add(ColumnName(c));
}
int ll = 0;
object[] currentRow = new object[LastColumn];
while (reader.Read())
{
if (reader.IsStartElement)
{
if (reader.ElementType == typeof(Row))
{
//Add rows that aren't in the file (unitialized rows aren't saved in xlsx)
var line = int.Parse(reader.Attributes.First(x => x.LocalName == "r").Value);
while (++ll < line)
{
dt.Rows.Add(dt.NewRow());
}
//Initialize a new row
currentRow = new object[LastColumn];
}
else if (reader.ElementType == typeof(Cell))
{
//Pick the data type of the Cell. s for sharedString, str for string,
//b for bool and absent for numbers
var tipo = reader.Attributes.FirstOrDefault(x => x.LocalName == "t").Value;
var endereco = TranslateAddress(
reader.Attributes.First(x => x.LocalName == "r").Value
);
//Search for the CellValue or quit if it find the closing Cell tag
while (reader.ElementType != typeof(CellValue) ||
(reader.ElementType != typeof(Cell) && reader.IsEndElement))
{
reader.Read();
}
if (reader.ElementType == typeof(CellValue))
{
switch (tipo)
{
//Shared string
case "s":
currentRow[endereco[1] - 1] =
dicionario[int.Parse(reader.GetText())];
break;
//Number
case null:
if (int.TryParse(reader.GetText(), out int saida))
{
currentRow[endereco[1] - 1] = saida;
}
else
{
currentRow[endereco[1] - 1] =
Convert.ToDouble(reader.GetText(),
CultureInfo.InvariantCulture);
}
break;
//String
case "str":
currentRow[endereco[1] - 1] = reader.GetText();
break;
//Boolean
case "b":
currentRow[endereco[1] - 1] =
reader.GetText() == "1" ? true : false;
break;
}
}
}
}
else
{
//Add the created row when found the closing Row tag
if (reader.ElementType == typeof(Row))
{
dt.Rows.Add(currentRow);
}
}
}
}
return(dt);
}
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();
}
/// <summary>
/// Converting a XLSX File to a CSV File
/// </summary>
/// <param name="excelFilePath">Full xlsx file path</param>
public static bool Converter_XlsxToCsv(string excelFilePath)
{
var fileToWrite = System.IO.Path.Combine(Path.GetDirectoryName(excelFilePath), System.IO.Path.GetFileNameWithoutExtension(excelFilePath) + ".csv");
var outputFile = new StreamWriter(fileToWrite, false, Encoding.UTF8);
try
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(excelFilePath, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.GetPartById("rId1") as WorksheetPart;
int dateIndex = -1;
int styleIndex = 0;
List <int> countDateForm = new List <int>();
foreach (CellFormat format in workbookPart.WorkbookStylesPart.Stylesheet.CellFormats)
{
//if (format.NumberFormatId == 14 )
//{
// dateIndex = styleIndex;
// break;
//}
if (format.NumberFormatId == 14)
{
countDateForm.Add(styleIndex);
}
++styleIndex;
}
var sharedDico = new Dictionary <string, string>();
int i = 0;
workbookPart.SharedStringTablePart.SharedStringTable.ChildElements.ToList().ForEach(e => sharedDico.Add(i++.ToString(), e.InnerText.Replace(";", ":").Replace("\n", "").Replace("'", " ")));
StringBuilder lineBuilder = null;
bool isSharedString = false;
char previousColumn = default(char);
bool mustAddValue = false;
string[] Col;
int nb_column = 0;
int column = 0;
bool firstDetec = false;
bool isDate = false;
bool isCellFormul = false;
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
while (reader.Read())
{
if (reader.LocalName == "row")
{
if (reader.IsStartElement)
{
lineBuilder = new StringBuilder();
mustAddValue = false;
}
else
{
Col = lineBuilder.ToString().Split(';');
column = Col.Count();
if (firstDetec == false)
{
nb_column = column;
firstDetec = true;
}
if (nb_column == column && firstDetec == true)
{
outputFile.WriteLine(lineBuilder.ToString());
}
else if (nb_column > column && firstDetec == true)
{
int nbColMissing = nb_column - column;
string sep = null;
for (int ajoutCol = 0; ajoutCol < nbColMissing; ajoutCol++)
{
sep += ';';
}
outputFile.WriteLine(lineBuilder.ToString() + sep);
}
else if (nb_column < column && firstDetec == true)
{
int nbColMissing = column - nb_column;
int countLastChar = 0;
for (int ajoutCol = lineBuilder.Length - 1; ajoutCol >= 0; ajoutCol--)
{
if (lineBuilder[ajoutCol] != ';')
{
break;
}
countLastChar++;
}
if (countLastChar == nbColMissing)
{
outputFile.WriteLine(lineBuilder.ToString().Substring(0, lineBuilder.Length - nbColMissing));
}
else
{
outputFile.WriteLine(lineBuilder.ToString());
}
}
else
{
outputFile.WriteLine(lineBuilder.ToString());
}
}
continue;
}
if (reader.IsEndElement)
{
continue;
}
if (reader.LocalName == "c")
{
if (mustAddValue)
{
lineBuilder.Append(";");
}
isSharedString = reader.Attributes.Any(a => a.LocalName == "t");
//isDate = reader.Attributes.Any(a => a.LocalName == "s" && a.Value == dateIndex.ToString());
isDate = reader.Attributes.Any(a => a.LocalName == "s" && countDateForm.Contains(Convert.ToInt32(a.Value)));
OpenXmlAttribute codeCellule = reader.Attributes.FirstOrDefault(a => a.LocalName == "r");
if (codeCellule != null)
{
char cellColumn = Regex.Replace(codeCellule.Value, "[0-9]", "").Last();
int indexCol = ALPHABET.IndexOf(cellColumn);
int indexPreviousCol = ALPHABET.IndexOf(previousColumn);
int diff = indexCol - indexPreviousCol;
if (diff > 1)
{
for (int j = 1; j < diff; j++)
{
lineBuilder.Append(";");
}
}
previousColumn = cellColumn;
}
mustAddValue = true;
continue;
}
if (reader.LocalName == "f")
{
isCellFormul = true;
}
if (reader.LocalName == "v")
{
string value = reader.GetText();
if (value.StartsWith("#"))
{
lineBuilder.Append(string.Empty);
}
else if (isSharedString && !isCellFormul)
{
lineBuilder.Append(sharedDico[value]);
}
else if (isSharedString && !isDate)
{
lineBuilder.Append(value);
}
else if (isDate && !value.Contains('-') && !value.Contains('+') && !value.Contains('.') && !value.Contains(','))
{
lineBuilder.Append(DateTime.FromOADate(double.Parse(value)).ToShortDateString());
}
else if (value.Contains("E-") || value.Contains("E+"))
{
lineBuilder.Append(decimal.Parse(value.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture));
}
else
{
if (value.Contains('.') || value.Contains(','))
{
lineBuilder.Append(double.Parse(value, NumberStyles.Any, CultureInfo.InvariantCulture));
}
else
{
lineBuilder.Append(value);
}
}
lineBuilder.Append(";");
isCellFormul = false;
mustAddValue = false;
continue;
}
}
}
outputFile.Close();
return(true);
}
catch (OleDbException)
{
return(false);
}
}
private void HandleCellValueStartElement(OpenXmlReader reader)
{
string text = "";
hasCellValue = true;
if (isSharedString)
{
text = sharedStringItems[Convert.ToInt32(reader.GetText())].InnerText;
}
else
{
text = reader.GetText();
}
text = text.Replace("\n", " ");
text = text.Replace("\"", "'");
if (numberFormatToApply > -1)
{
DateTime testDateTime;
// TODO Add new number format handlers when faced
switch (numberFormatToApply)
{
case 14:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString(CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern);
break;
case 15:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("d-MMM-yy");
break;
case 16:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("d-MMM");
break;
case 17:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("MMM-yy");
break;
case 18:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm tt");
break;
case 19:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm:ss tt");
break;
case 20:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm");
break;
case 21:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString("h:mm:ss");
break;
case 22:
testDateTime = DateTime.FromOADate(Convert.ToDouble(text));
text = testDateTime.ToString(string.Format("{0} h:mm", CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern));
break;
default:
break;
}
}
// For header row, sometimes it is a requirement to have unique names
// If it is not, this part and the headers dictionary can be removed
if (rowCount == 0)
{
// if header text is empty, do not write this column
if (text == "")
{
columnsToSkip.Add(currColumnIndex);
}
if (!headers.ContainsKey(text))
{
headers.Add(text, 1);
}
else
{
headers[text] = headers[text] + 1;
text = text + headers[text];
}
}
if (!columnsToSkip.Contains(currColumnIndex))
{
lineBuilder.Append(string.Format("{0}{1}{0}", textDelimiter, text));
// check end of row
if (currColumnIndex != sheetEndColumnIndex)
{
lineBuilder.Append(columnDelimiter);
}
}
}
