If a rule is added with no formatting (for example, in the case when we want to add a rule which preserves the default formatting in some cases) the exported document is corrupted.
DifferentialFormatting formatting2 = new DifferentialFormatting();
ContainsBlanksRule rule2 = new ContainsBlanksRule(formatting2);
rule2.Formatting = formatting2;
ConditionalFormatting conditionalFormatting2 = new ConditionalFormatting(rule2);
worksheet.Cells[1, 2, 12, 2].AddConditionalFormatting(conditionalFormatting2);
A possible workaround is to add formatting to the rule, which copies the default:
DifferentialFormatting formatting2 = new DifferentialFormatting();
formatting2.Fill = new PatternFill(PatternType.Solid, Colors.Transparent, Colors.Transparent);
CellBorder border = new CellBorder(CellBorderStyle.Thin, new ThemableColor(Color.FromRgb(212, 212, 212)));
formatting2.LeftBorder = border;
formatting2.TopBorder = border;
formatting2.RightBorder = border;
formatting2.BottomBorder = border;
Applying text format to a column also sets the format to the next column.
This happens when the columns have a size different from the default and two consecutive columns with different format also have the same size. This can be worked around with a slight change in the column sizes:
int columnCount = worksheet.UsedCellRange.ColumnCount;
for(int i = 1; i < columnCount; i++)
{
ColumnWidth colWidth = worksheet.Columns[i].GetWidth().Value;
if(colWidth.Value != SpreadsheetDefaultValues.DefaultColumnWidth)
{
ColumnWidth previousColWidth = worksheet.Columns[i - 1].GetWidth().Value;
if(colWidth.Equals(previousColWidth))
{
worksheet.Columns[i].SetWidth(new ColumnWidth(colWidth.Value + 1, colWidth.IsCustom));
}
}
}
The Value property of DefinedName (implementing ISpreadsheetName) always returns a result with a General format, which is very impractical when the value is for example a date. There should be an option to get a formatted result as well.
A possible workaround for this missing functionality would be to parse the RefersTo value and find where it points in order to grab the format:
Workbook workbook = new Workbook();
Worksheet ws = workbook.Worksheets.Add();
ws.Cells[0, 1].SetValue("2/1/2013");
ws.Names.Add("MyField", "=Sheet1!$B$1", new CellIndex(0, 0), "My Field");
string value = ws.Names["MyField"].Value;
CellRange range;
bool success = NameConverter.TryConvertCellRangeNameToCellRange(ws.Names["MyField"].RefersTo, out range);
CellValueFormat format = ws.Cells[range].GetFormat().Value;
string result = ws.Cells[range].GetValue().Value.GetResultValueAsString(format);
When a file uses shared formulas, if the cell references are not explicitly written in the cell element, the formulas will not be shown correctly. This will manifest like a file that has a succession of slightly differing formulas in Excel and in RadSpreadsheet or RadSpreadProcessing they will be a succession of the same formula instead.