Why would cell contents set to align left sometimes align right (Aspose Cells)?

Question

I've got this code, which should left-align the contents of a particular column:

Cell memberItemCodeCell = customerWorksheet.Cells[rowToPopulate, MEMBERITEMCODE_COL];
memberItemCodeCell.PutValue(frbdbc.MemberItemCode, true);
var micStyle = memberItemCodeCell.GetStyle();
micStyle.Font.Name = fontForSheets;
micStyle.Font.Size = 11;
micStyle.HorizontalAlignment = TextAlignmentType.Left;
micStyle.IsTextWrapped = false;
memberItemCodeCell.SetStyle(micStyle, flag);

It works ... sometimes:

Why in the heck would right-aligning sometimes happen?

Any value which could be seen as an int (contains no alpha chars or dashes) right-aligns; but why would it not respect the explicit left-aligning, regardless of whether or not it "looked like" an int?

There is some "general" formatting that applies to the entire row after all the column-specific code:

CellsFactory cf = new CellsFactory();
Style style4 = cf.CreateStyle();
if (shipVarDbl >= 1.0) // fewer were shipped than were ordered
{
    style4.ForegroundColor = Color.LightGreen;
}
else if (shipVarDbl < 0.0) // more were shipped than were ordered
{
    style4.ForegroundColor = Color.PaleVioletRed;
}
style4.Font.Name = fontForSheets;
style4.Font.Size = 11;
style4.Pattern = BackgroundType.Solid;
rowRange.SetStyle(style4);

...but that shouldn't affect the aligning.

After the code shown first above runs as part of the PopulateCustomerSheet() method:

private void PopulateCustomerSheet()
{
    try
    {
        if (null == _fillRateByDistributorByCustomerList) return;
        foreach (FillRateByDistributorByCustomer frbdbc in _fillRateByDistributorByCustomerList)
        {
            AddCustomerRow(frbdbc);
        }
        AutoFitterOptions options = new AutoFitterOptions { OnlyAuto = true };
        customerWorksheet.AutoFitColumns(options);
    }
    catch (Exception ex)
    {
        RoboReporterConstsAndUtils.HandleException(ex);
    }
}

...it is borderized, configured for printing, and finally the sheet is written to disk:

BorderizeDataPortionOfCustomerSheet(); 
ConfigureCustomerSheetForPrinting();

// Write the file to disk
string fromAsYYYYMMDD = DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss");
RoboReporterConstsAndUtils.SetUniqueFolder(_unit);
String _uniqueFolder = RoboReporterConstsAndUtils.uniqueFolder;

var sharedFolder = String.Format(@"\\storageblade\cs\REPORTING\RoboReporter\{0}", _uniqueFolder);
RoboReporterConstsAndUtils.ConditionallyCreateDirectory(sharedFolder);

var filename = String.Format(@"{0}\{1} - Fill Rate - {2}.xlsx", sharedFolder, _unit, fromAsYYYYMMDD);
if (File.Exists(filename))
{
    File.Delete(filename);
}
workBook.Save(filename, SaveFormat.Xlsx);

I can't imagine the borderizing or print configuring changing the alignment of a particular column on the sheet, but just in case that's possible, here are those methods:

private void BorderizeDataPortionOfCustomerSheet()
{
    int rowsUsed = customerWorksheet.Cells.Rows.Count;
    int colsUsed = SHIPVARIANCE_COL;

    string bottomRightRange = string.Format("P{0}", rowsUsed);
    var range = customerWorksheet.Cells.CreateRange("A1", bottomRightRange);

    //Setting border for each cell in the range
    var style = workBook.CreateStyle();
    style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

    for (int r = range.FirstRow; r < range.RowCount; r++)
    {
        for (int c = range.FirstColumn; c < range.ColumnCount; c++)
        {
            Cell cell = customerWorksheet.Cells[r, c];
            cell.SetStyle(style, new StyleFlag()
            {
                TopBorder = true,
                BottomBorder = true,
                LeftBorder = true,
                RightBorder = true
            });
        }
    }

    //Setting outline border to range
    range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);

    customerWorksheet.FreezePanes(FIRST_DATA_ROW, SHORTNAME_COL, rowsUsed, colsUsed);
}

private void ConfigureCustomerSheetForPrinting()
{
    const double INCHES_TO_CENTIMETERS_FACTOR = 2.54;
    string lastColumn = GetExcelTextColumnName(customerWorksheet.Cells.Columns.Count);
    string printArea = String.Format("A1:{0}{1}", lastColumn, customerWorksheet.Cells.Rows.Count);
    customerWorksheet.PageSetup.PrintArea = printArea;
    customerWorksheet.PageSetup.Orientation = PageOrientationType.Landscape;

    // I don't know if this does anything; I would like to set it to 54%...
    customerWorksheet.PageSetup.IsPercentScale = true;

    customerWorksheet.PageSetup.FitToPagesWide = 1;
    customerWorksheet.PageSetup.FitToPagesTall = 0;

    customerWorksheet.PageSetup.LeftMargin = 0.5 * INCHES_TO_CENTIMETERS_FACTOR;
    customerWorksheet.PageSetup.RightMargin = 0.5 * INCHES_TO_CENTIMETERS_FACTOR;
    customerWorksheet.PageSetup.TopMargin = 0.5 * INCHES_TO_CENTIMETERS_FACTOR;
    customerWorksheet.PageSetup.BottomMargin = 0.5 * INCHES_TO_CENTIMETERS_FACTOR;
    customerWorksheet.PageSetup.HeaderMargin = 0;
    customerWorksheet.PageSetup.FooterMargin = 0;

    // Repeat rows
    string repeatableRowRange = "$1:$1";
    customerWorksheet.PageSetup.PrintTitleRows = repeatableRowRange;
}

How could this cause the column alignment to disrespect, disobey, and ignore the clear and present directive to left-align its contents?

UPDATE

Based on what I understood of the answer, I changed this:

rowRange.SetStyle(style4);

...to this:

var flag = new StyleFlag
{
    CellShading = true,
    FontName = true,
    FontSize = true,
    FontColor = true,
    FontBold = true,
    NumberFormat = true
};

rowRange.ApplyStyle(style4, flag);

...but it made no difference.

UPDATE 2

This is the code that explicitly sets the problem column:

Cell memberItemCodeCell = customerWorksheet.Cells[rowToPopulate, MEMBERITEMCODE_COL];
memberItemCodeCell.PutValue(frbdbc.MemberItemCode, true);
var micStyle = memberItemCodeCell.GetStyle();
micStyle.Font.Name = fontForSheets;
micStyle.Font.Size = 11;
micStyle.HorizontalAlignment = TextAlignmentType.Left;
micStyle.IsTextWrapped = false;
memberItemCodeCell.SetStyle(micStyle, flag);

So I'm not using a Range here, but a cell. Should I use a range, so that I can use ApplyStyle()? I tried doing that, but it doesn't seem to want to accept PutValue(), etc.

Besides, the value supplied (frbdbc.MemberItemCode) is a string, so shouldn't this prevent Excel from treating it like an int? What else must I do to let Excel know, "Hey, this is a string, just present it as-is."

UPDATE 3

I tried this code that was sent me by Aspose:

customerWorksheet.Cells.CreateRange(rangeBegin, rangeEnd).ApplyStyle(style4, new StyleFlag() { Font = true, CellShading = true });

In context:

string rangeBegin = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(SHORTNAME_COL + 1, rowToPopulate);
string rangeEnd = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(SHIPVARIANCE_COL + 1, rowToPopulate);

CellsFactory cf = new CellsFactory();
Style style4 = cf.CreateStyle();
if (shipVarDbl >= 1.0) // fewer were shipped than were ordered
{
    style4.ForegroundColor = Color.LightGreen;
}
else if (shipVarDbl < 0.0) // more were shipped than were ordered
{
    style4.ForegroundColor = Color.PaleVioletRed;
}
style4.Font.Name = fontForSheets;
style4.Font.Size = 11;
style4.Pattern = BackgroundType.Solid;
customerWorksheet.Cells.CreateRange(rangeBegin, rangeEnd).ApplyStyle(style4, new StyleFlag() { Font = true, CellShading = true });

...and it still makes no difference.

UPDATE 4

I thought this might work, which I found here:

micStyle.NumberFormat = 2;

(replacing the "2" with whatever number reprsents "Text"); but "NumberFormat" is not recognized. Is that an obsolete example?

UPDATE 5

Okay, there must be some logical reason why this is [not] happening. Here are the manipulations I make to that column, in order:

First, the header row is written:

private static readonly int MEMBERITEMCODE_COL = 4;
. . .
private void AddCustomerSheetHeaderRow()
{
    var flag = new StyleFlag
    {
        CellShading = true,
        FontName = true,
        FontSize = true,
        FontColor = true,
        FontBold = true,
        NumberFormat = true
    };

    . . .

    CellsFactory cfMemberItemCode = new CellsFactory();
    Cell MemberItemCodeCell = customerWorksheet.Cells[0, MEMBERITEMCODE_COL];
    MemberItemCodeCell.PutValue("Member Item Code");
    var styleMemberItemCode = cfMemberItemCode.CreateStyle();
    styleMemberItemCode.HorizontalAlignment = TextAlignmentType.Left;
    styleMemberItemCode.Font.Name = fontForSheets;
    styleMemberItemCode.Font.IsBold = true;
    styleMemberItemCode.Font.Size = 11;
    styleMemberItemCode.ForegroundColor = Color.LightBlue;
    styleMemberItemCode.Pattern = BackgroundType.Solid;
    MemberItemCodeCell.SetStyle(styleMemberItemCode, flag);

    . . .
}

So I align the column on the header row; it is index 4, IOW column "E"

Next, AddCustomerRow() is called multiple times, populating the "data" portion of the sheet (everything below the header row):

    private void AddCustomerRow(FillRateByDistributorByCustomer frbdbc) 
    {
        var flag = new StyleFlag
        {
            CellShading = true,
            FontName = true,
            FontSize = true,
            FontColor = true,
            FontBold = true,
            NumberFormat = true
        };

        . . .

        // This is sometimes seen as an int by Excel, and sports the green warning triangle
        // Fixed that with the second (true) arg to PutValue(), but it right-aligns int-like vals...?!@?
        Cell memberItemCodeCell = customerWorksheet.Cells[rowToPopulate, MEMBERITEMCODE_COL];
        memberItemCodeCell.PutValue(frbdbc.MemberItemCode, true);
        var micStyle = memberItemCodeCell.GetStyle();
        micStyle.Font.Name = fontForSheets;
        micStyle.Font.Size = 11;
        micStyle.HorizontalAlignment = TextAlignmentType.Left;
        micStyle.IsTextWrapped = false;
        memberItemCodeCell.SetStyle(micStyle, flag);

        . . .

        string rangeBegin = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(SHORTNAME_COL + 1, rowToPopulate);
        string rangeEnd = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(SHIPVARIANCE_COL + 1, rowToPopulate);

        CellsFactory cf = new CellsFactory();
        Style style4 = cf.CreateStyle();
        if (shipVarDbl >= 1.0) // fewer were shipped than were ordered
        {
            style4.ForegroundColor = Color.LightGreen;
        }
        else if (shipVarDbl < 0.0) // more were shipped than were ordered
        {
            style4.ForegroundColor = Color.PaleVioletRed;
        }
        style4.Pattern = BackgroundType.Solid;
        style4.Font.Name = fontForSheets;
        style4.Font.Size = 11;
        customerWorksheet.Cells.CreateRange(rangeBegin
|rangeEnd).ApplyStyle(style4, new StyleFlag() { Font = true,
CellShading = true });
    }

Here, too, the alignment is set to left. After setting up each column, a "general purpose row style" is created, to conditionally color the entire row. That works fine - the appropriate rows are colorized.

Then, because the colorizing of the header row doesn't work as tried previously (setting it to LightBlue doesn't work), I do it after the face like so:

private void RecolorizeTopRowOfCustomerSheet()
{
    . . .

    CellsFactory cfMemberItemCode = new CellsFactory();
    Cell MemberItemCodeCell = customerWorksheet.Cells[0,
MEMBERITEMCODE_COL];
    var styleMemberItemCode = cfMemberItemCode.CreateStyle();
    styleMemberItemCode.HorizontalAlignment = TextAlignmentType.Left;
    styleMemberItemCode.Font.Name = fontForSheets;
    styleMemberItemCode.Font.IsBold = true;
    styleMemberItemCode.Font.Size = 11;
    styleMemberItemCode.ForegroundColor = Color.LightBlue;
    styleMemberItemCode.Pattern = BackgroundType.Solid;
    MemberItemCodeCell.SetStyle(styleMemberItemCode);

    . . .

    // Give it borders
    Range _range;
    _range = customerWorksheet.Cells.CreateRange("A1", "P1");
    //Set the borders with hair lines style.
    _range.SetOutlineBorders(CellBorderType.Hair, Color.Black);
}

...Borders are also added to that row. Finally, borders are added to the data rows (everything past row 1):

private void BorderizeDataPortionOfCustomerSheet()
{
    int rowsUsed = customerWorksheet.Cells.Rows.Count;
    int colsUsed = SHIPVARIANCE_COL;

    string bottomRightRange = string.Format("P{0}", rowsUsed);
    var range = customerWorksheet.Cells.CreateRange("A1", bottomRightRange);

    //Setting border for each cell in the range
    var style = workBook.CreateStyle();
    style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
    style.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

    for (int r = range.FirstRow; r < range.RowCount; r++)
    {
        for (int c = range.FirstColumn; c < range.ColumnCount; c++)
        {
            Cell cell = customerWorksheet.Cells[r, c];
            cell.SetStyle(style, new StyleFlag()
            {
                TopBorder = true,
                BottomBorder = true,
                LeftBorder = true,
                RightBorder = true
            });
        }
    }

    //Setting outline border to range
    range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
    range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);

    customerWorksheet.FreezePanes(FIRST_DATA_ROW, SHORTNAME_COL, rowsUsed, colsUsed);
}

Then the file is saved to disk - with a mishmash of some items in the MemberItemCode colum left-aligned and others right-aligned. Why isn't there some call like:

Column[3].Alignment = AlignLeft;

...that would easily align everything in a particular column as desired; or is there? Since what I'm trying either doesn't work or is very temperamental, something like that would certainly be handy.

Answer 1

I believe that the said problem is caused because you are overwriting all aspects of the style with an object created from scratch. Please note that when you apply a style to a cell, you are getting the style from that cell first, which allows you to retain the number format applied to it. However, when you create a Style object from scratch and apply the style to a range while using the Range.SetStyle, every aspect of the newly created Style object is overwritten to the range, that causes the change in format to General for each cell in the range. I suggest you to use the Range.ApplyStyle method instead, that allows you to pass an instance of StyleFlag to the aforementioned method. This way, you can control the aspects of the Style object that you wish to overwrite.

Note: I am working as Developer Evangelist at Aspose.

Answer 2

The only way I could get this to work was to "brute force it" by explicitly setting all the cells in that column to left-aligned after everything else had been done:

private static readonly int MEMBERITEMCODE_COL = 4;
. . .
SetAllCustomerSheetColValsLeftAligned(MEMBERITEMCODE_COL);
BorderizeDataPortionOfCustomerSheet();    
. . .    
private void SetAllCustomerSheetColValsLeftAligned(int colIndex)
{
    int rowsUsed = customerWorksheet.Cells.Rows.Count;
    CellsFactory cf = new CellsFactory();
    Cell currentCell = null;
    Style currentStyle = null;
    for (int i = 2; i <= rowsUsed; i++)
    {
        currentCell = customerWorksheet.Cells[i, colIndex];
        currentStyle = cf.CreateStyle();
        currentStyle.HorizontalAlignment = TextAlignmentType.Left;
        currentStyle.Font.Name = fontForSheets;
        currentStyle.Font.Size = 11;
        currentCell.SetStyle(currentStyle);
    }
}