ExcelToTxtConverter (.NET)

Nuget

You can find nuget package with name ExcelToTxtConverter

Introduction

.NET package for converting EXCEL to TXT

Benefits and Features

Optionally, sorts by column
Optionally, group by column
Supporting for Binary Excel files (2.0-2003 format; *.xls) and OpenXml Excel files (2007 format; *.xlsx, *.xlsb)
Supports .NET Standard 2.0, .NET Framework 4.6

Example

Assembly assembly = typeof(MyClass).GetTypeInfo().Assembly;
Stream definitionStream = assembly.GetManifestResourceStream(definitionFile));
XElement definitionElement = XElement.Load(definitionStream);
Converter converter = new Converter(definitionElement);
IDictionary<string, StringBuilder> stringBuildersResult = converter.Execute(excelBytes);

Example of Definition

<?xml version="1.0" encoding="utf-8" ?>
<Definition>
    <Table>
      <Column ExcelID="Codigo Cuenta" TxtColumnText="&lt;CodigoCuenta&gt;" TxtTextPosition="0"></Column>
      <Column ExcelID="Apertura Debe" TxtColumnText="&lt;AperturaDebe&gt;" TxtTextPosition="25" CellFormat="3"></Column>
      <Column ExcelID="Apertura Haber" TxtColumnText="&lt;AperturaHaber&gt;" TxtTextPosition="48" CellFormat="3"></Column>
      <Column ExcelID="tipo comprobante" TxtColumnText="&lt;TpoComp&gt;" TxtTextPosition="71"></Column>
      <Column ExcelID="Numero comprobante" TxtColumnText="&lt;NumComp&gt;" TxtTextPosition="84"></Column>
      <Column ExcelID="Fecha" TxtColumnText="&lt;FechaContable&gt;" TxtTextPosition="106" CellFormat="0"></Column>
      <Column ExcelID="Glosa Analisis" TxtColumnText="&lt;GlosaAnalisis&gt;" TxtTextPosition="124"></Column>
      <Column ExcelID="Rut" TxtColumnText="&lt;Rut&gt;" TxtTextPosition="248"></Column>
      <Column ExcelID="Nombre" TxtColumnText="&lt;Nombre&gt;" TxtTextPosition="262"></Column>
      <Column ExcelID="Tipo Documento" TxtColumnText="&lt;TpoDocum&gt;" TxtTextPosition="386"></Column>
      <Column ExcelID="Numero documento" TxtColumnText="&lt;Numero&gt;" TxtTextPosition="405"></Column>
      <Column ExcelID="Fecha Emision" TxtColumnText="&lt;FchEmision&gt;" TxtTextPosition="427"></Column>
      <Column ExcelID="Fecha Vcto" TxtColumnText="&lt;FchVencimiento&gt;" TxtTextPosition="441"></Column>
      <Column ExcelID="Glosa" TxtColumnText="&lt;Glosa&gt;" TxtTextPosition="461"></Column>
      <Column ExcelID="Ref" TxtColumnText="&lt;Ref&gt;" TxtTextPosition="495"></Column>
      <Column ExcelID="Debe" TxtColumnText="&lt;Debe&gt;" TxtTextPosition="510" CellFormat="3"></Column>
      <Column ExcelID="Haber" TxtColumnText="&lt;Haber&gt;" TxtTextPosition="542" CellFormat="3"></Column>
    </Table>
</Definition>

Sort by Column

Indicate in the XML definition the orderable column

<Column ExcelID="Codigo Cuenta" TxtColumnText="&lt;CodigoCuenta&gt;" TxtTextPosition="0" Orderable="int|string"></Column>

Group by Column

Indicate in the XML definition the grouper column

<Column ExcelID="Fecha" TxtColumnText="&lt;FechaContable&gt;" TxtTextPosition="44" CellFormat="0" GroupKey="true"></Column>

In this point its neccesary indicates a function that builds the group identifier

public class MyTxtWriter
{
    public static string RetrieveBuilderKey(int indexRecord, IList<ExcelToTxtConverter.ColumnHeadData> lceColumnList, System.Data.DataTable dataTable)
    {
        var col = lceColumnList.Where(o => o.GroupKey.Equals(true)).FirstOrDefault();
        if (null == col)
        {
            return "{guid}";
        }

        string cellValue = string.Empty;
        DateTime dateValue;
        try
        {

            var cell = dataTable.Rows[indexRecord][col.ColumnPosition];
            cellValue = cell?.ToString();

            if (col.CellFormat.Equals(ExcelToTxtConverter.DateCellFormatter.Identifier))
            {
                dateValue = DateTime.Parse(cellValue);
                return string.Format("{0}{1}" + dateValue.Year, dateValue.ToString("MM"));
            }
        }
        catch (Exception)
        {
            Console.WriteLine(string.Format("Error parsing date: {0}", cellValue));
            throw;
        }

        return "{guid}";
    }

    public static string MakeBuilderKey(string builderKey)
    {
        builderKey = builderKey.Replace("{guid}", Guid.NewGuid().ToString());
        return string.Format("{0}.txt", builderKey);
    }
}

Assembly assembly = typeof(MyClass).GetTypeInfo().Assembly;
Stream definitionStream = assembly.GetManifestResourceStream(definitionFile));
XElement definitionElement = XElement.Load(definitionStream);

Func<int, IList<ColumnHeadData>, System.Data.DataTable, string> retrieveGroupKeyFunction = new Func<int, IList<ColumnHeadData>, System.Data.DataTable, string>(MyTxtWriter.RetrieveBuilderKey);

Converter converter = new Converter(definitionElement, retrieveGroupKeyFunction);
IDictionary<string, StringBuilder> stringBuildersResult = converter.Execute(excelBytes);
var resultDictionary = new Dictionary<string, string>();
foreach (var generatedTxt in generatedTxts)
{
    resultDictionary.Add(MyTxtWriter.MakeBuilderKey(generatedTxt.Key), generatedTxt.Value.ToString());
}

MichaelHorta/ExcelToTextConverter