Transposer

gistfile1.txt

using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using static System.Console;

namespace XlsTransposer
{
    class Program
    {
        static void Main(string[] args)
        {
            var sw = new Stopwatch();
            sw.Start();
            Transpose(
                "/Users/elemarjr/Documents/fev_2.xlsx",
                "/Users/elemarjr/Documents/fev_transposed.xlsx"
                );
            Console.WriteLine($"Elapsed time {sw.ElapsedMilliseconds} ms");
            WriteLine("Done!");
        }

        public static void Transpose(string input, string output)
        {
            File.Copy(input, output, true);
            
            using (var source = SpreadsheetDocument.Open(output, true))
            {
                var sourceWorkbookPart = source.WorkbookPart;
                var sourceWorksheetPart = sourceWorkbookPart.WorksheetParts.FirstOrDefault();

                var destWorkbookPart = sourceWorkbookPart;
                var destWorksheetPart = destWorkbookPart.AddNewPart<WorksheetPart>();

                var dest = source;
                dest.WorkbookPart.Workbook.Sheets.AppendChild(new Sheet()
                {
                    Id = dest.WorkbookPart.GetIdOfPart(destWorksheetPart),
                    SheetId = 2,
                    Name = "Copy"
                });

                var readingData = false;
                var firstLine = true;
                bool silent = false;
                Cell cell = null;
                var line = 1;
                
                using (var reader = OpenXmlReader.Create(sourceWorksheetPart))
                using (var writer = OpenXmlWriter.Create(destWorksheetPart))
                {
                    while (reader.Read())
                    {
                         if (reader.ElementType == typeof(SheetData))
                        {
                            readingData = !reader.IsEndElement;
                            if (reader.IsEndElement)
                            {
                                writer.WriteEndElement();
                                continue;
                            }
                            else
                            {
                                writer.WriteStartElement(reader);
                            }
                        }

                        if (readingData)
                        {
                            if (firstLine)
                            {
                                firstLine = (!(reader.ElementType == typeof(Row) && reader.IsEndElement));
                            }
                            else
                            {
                                
                                if (reader.ElementType == typeof(Cell))
                                {
                                    if (!reader.IsEndElement)
                                    {
                                        var attCellReference = reader.Attributes.FirstOrDefault(a => a.LocalName == "r");
                                        silent = attCellReference.Value.StartsWith('J')
                                                 || attCellReference.Value.StartsWith('K')
                                                 || attCellReference.Value.StartsWith('P');

                                        if (!silent)
                                        {
                                            var shared =
                                                reader.Attributes.Any(a => a.LocalName == "t" && a.Value == "s");
                                            var c = attCellReference.Value[0];
                                            if (c > 'P') c--;
                                            if (c > 'K')
                                            {
                                                c--;
                                                c--;
                                            }

                                            cell = new Cell()
                                            {
                                                StyleIndex = 1U,
                                                //CellReference = $"{c}{line}"
                                            };

                                            if (shared)
                                            {
                                                cell.DataType = CellValues.SharedString;
                                            }
                                        }
                                    }
                                }
                                else if (!silent && reader.ElementType == typeof(CellValue))
                                {
                                    if (!reader.IsEndElement)
                                    {
                                        var value = new CellValue {Text = reader.GetText()};
                                        cell.Append(value);
                                    }
                                }
                                
                                if (!silent)
                                {
                                    if (reader.IsStartElement)
                                    {
                                        if (reader.ElementType != typeof(Cell) && reader.ElementType != typeof(CellValue))
                                        {
                                           writer.WriteStartElement(reader);
                                        }
                                    }
                                    else if (reader.IsEndElement)
                                    {
                                        if (reader.ElementType == typeof(Cell))
                                        {
                                            writer.WriteElement(cell);
                                            cell = null;
                                        }
                                        else if (reader.ElementType != typeof(CellValue))
                                        {
                                            writer.WriteEndElement();
                                        }
                                    }
                                }
                                
                                if (reader.ElementType == typeof(Cell))
                                {
                                    if (reader.IsEndElement)
                                    {
                                        silent = false;
                                    }
                                }

                                if (reader.ElementType == typeof(Row))
                                {
                                    if (reader.IsEndElement)
                                    {
                                        line++;
                                    }
                                }
                            }
                        }


                        if (readingData == false)
                        {
                            if (reader.IsStartElement)
                            {
                                writer.WriteStartElement(reader);
                            }
                            else if (reader.IsEndElement)
                            {
                                writer.WriteEndElement();
                            }
                        }
                    }
                    
                    
                    reader.Close();
                    writer.Close();
                }
            }
        }
    }
}