Search code examples
excelalignmentasposeaspose-cells

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

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:

enter image description here

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.

Solution

  • 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);
    }
}