Gembox.Spreadsheet autofit not working with first two columns C#

Question

using System;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using GemBox.Spreadsheet;


namespace ExcelSortingAutomation
{

    public class Program
    {
        [STAThread]
        public static void Main(string[] args)
        {

            SpreadsheetInfo.SetLicense("License");
            ExcelFile ef = new ExcelFile();
            ExcelWorksheet ws = ef.Worksheets.Add("Error Spreadsheet info Elmah");

            int i = 0;
            var checkTime = DateTime.Now;

            var files = Directory.GetFiles("C:\\ErrorsMay2017", "*.xml");
            foreach (string file in files)
            {
                var tagElement = XElement.Load(file);

                var errors = from tagAttribute in tagElement.DescendantsAndSelf("error").OrderBy(x => x.Attribute("type"))
                             select new
                             {
                                 errorID = (string)tagAttribute.Attribute("errorId"),
                                 type = (string)tagAttribute.Attribute("type"),
                                 message = (string)tagAttribute.Attribute("message"),
                                 time = (string)tagAttribute.Attribute("time"),

                                 PathInfo = tagAttribute.Elements("serverVariables").Descendants("item").Where(x => x.Attribute("name").Value == "PATH_INFO")
                                 .Select(x => x.Descendants("value").First().Attribute("string").Value).SingleOrDefault(),

                                  HttpHost = tagAttribute.Elements("serverVariables").Descendants("item").Where(x => x.Attribute("name").Value == "HTTP_HOST")
                                 .Select(x => x.Descendants("value").First().Attribute("string").Value).SingleOrDefault()

                             };
                int columnCount = ws.CalculateMaxUsedColumns();

                foreach (var error in errors)
                {
                    // generates table head 
                    ws.Cells[0, 0].Value = "errorID";
                    ws.Cells[0, 1].Value = "type";
                    ws.Cells[0, 2].Value = "message";
                    ws.Cells[0, 3].Value = "time";
                    ws.Cells[0, 4].Value = "Http_Host";
                    ws.Cells[0, 5].Value = "Path_Info";


                    if (i < columnCount)
                    {
                        ws.Columns[i].AutoFit(1, ws.Rows[1], ws.Rows[ws.Rows.Count - 1]);
                    }

                    Console.Write(error);

                    ws.Cells[i, 0].Value = error.errorID;
                    ws.Cells[i, 1].Value = error.type;
                    ws.Cells[i, 2].Value = error.message;
                    ws.Cells[i, 3].Value = error.time;
                    ws.Cells[i, 4].Value = error.PathInfo;
                    ws.Cells[i, 5].Value = error.HttpHost;

                    i++;
                }
                ef.Save("C:\\ErrorsMay2017\\errorlog " + checkTime.ToString("MM-dd-yyyy-HH-mm") + ".xls");


            }
        }
    }
}

In the above example, I have a simple console application that parses elmah error logs, separates out a few key components, and then prints them into a series of cells in Gembox.spreadsheet. My current issue is that, with the Autofit setting, I am getting correct styling on Cells 2-5, but not on 0, or 1. When changing the Autofit(1) to AutoFit(0) it printed the first cell correctly, but removed all the others. Can someone please explain, or provide me with insight as to how to print all cells with Auto format?

Mario Z · Accepted Answer · 2017-06-15T14:35:43.303

I see several issues with that code:

For every file in files a new XLS file is saved and this can very easily end up rewriting the previous XLS file.
The i variable represents error record's index, in other words it represents row index, so it doesn't make sense to use it to select some "random column" which is auto fitted.
For every error the table's head is rewritten.
On second iteration the table's head will overwrite the first error which was previously written on 0 indexed row.
Etc.

Nevertheless, please try the following:

SpreadsheetInfo.SetLicense("License");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Error Spreadsheet info Elmah");

// Write header row.
ws.Cells[0, 0].Value = "Error ID";
ws.Cells[0, 1].Value = "Type";
ws.Cells[0, 2].Value = "Message";
ws.Cells[0, 3].Value = "Time";
ws.Cells[0, 4].Value = "Path Info";
ws.Cells[0, 5].Value = "HTTP Host";

int rowIndex = 1;
string folder = @"C:\ErrorsMay2017";

// I presume you want to write data from all XML files into a single spreadsheet.
foreach (string file in Directory.GetFiles(folder, "*.xml"))
{
    // Get errors records.
    var errors = from tagAttribute in XElement.Load(file).DescendantsAndSelf("error").OrderBy(x => x.Attribute("type"))
                 select new
                 {
                     ErrorID = (string)tagAttribute.Attribute("errorId"),
                     Type = (string)tagAttribute.Attribute("type"),
                     Message = (string)tagAttribute.Attribute("message"),
                     Time = (string)tagAttribute.Attribute("time"),

                     PathInfo = tagAttribute.Elements("serverVariables").Descendants("item").Where(x => x.Attribute("name").Value == "PATH_INFO")
                     .Select(x => x.Descendants("value").First().Attribute("string").Value).SingleOrDefault(),

                     HttpHost = tagAttribute.Elements("serverVariables").Descendants("item").Where(x => x.Attribute("name").Value == "HTTP_HOST")
                     .Select(x => x.Descendants("value").First().Attribute("string").Value).SingleOrDefault()
                 };

    // Write errors rows.
    foreach (var error in errors)
    {
        ws.Cells[rowIndex, 0].Value = error.ErrorID;
        ws.Cells[rowIndex, 1].Value = error.Type;
        ws.Cells[rowIndex, 2].Value = error.Message;
        ws.Cells[rowIndex, 3].Value = error.Time;
        ws.Cells[rowIndex, 4].Value = error.PathInfo;
        ws.Cells[rowIndex, 5].Value = error.HttpHost;
        ++rowIndex;
    }
}

// AutoFit columns after writing all spreadsheet's data.
int columnCount = ws.CalculateMaxUsedColumns();
for (int columnIndex = 0; columnIndex < columnCount; ++columnIndex)
    ws.Columns[columnIndex].AutoFit();

// Save to newer Excel format (.xlsx).
ef.Save(Path.Combine(folder,
    string.Format("errorlog {0}.xlsx", DateTime.Now.ToString("MM-dd-yyyy-HH-mm"))));

I hope this helps.

Thank you Mario, I had actually tried writing the table headers outside the loop, but because I was iterating over them, it didn't display correctly. I very much appreciate your answer, and it seems to work perfectly. — jpears, Jun 15 '17 at 16:30

Gembox.Spreadsheet autofit not working with first two columns C#

1 Answers1