Why would cell contents set to align left sometimes align right?

Aspose.Cells Product Family
1
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 (see screenshot).

Why in the heck would right-aligning sometimes happen?

Any value which could be seen as an int right-aligns (contains no alpha chars or dashes); 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?
2

Hi Clay,


Thank you for contacting Aspose support.

First of all, we humbly request you to always provide a simple executable console application along with input & output spreadsheets to replicate the said scenarios. This will simply ease the investigation process and we will be able to assist you more efficiently.

That said, 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. Please consider the following piece of code as well as the attached input & output spreadsheets for your reference.

Please amend you code according to the above suggestion. In case the problem persists, please provide an executable sample application as requested earlier in this post.

C#

Workbook book = new Workbook(dataDir + “book1.xlsx”);
Worksheet sheet = book.Worksheets[0];
CellsFactory cf = new CellsFactory();
Style style4 = cf.CreateStyle();
style4.ForegroundColor = Color.LightGreen;
style4.Font.Name = “Calibri”;
style4.Font.Size = 11;
style4.Pattern = Aspose.Cells.BackgroundType.Solid;
sheet.Cells.CreateRange(“A3:F3”).ApplyStyle(style4, new StyleFlag() { Font = true, CellShading = true});
book.Save(dataDir + “output2.xlsx”);