Hello,
we would like to have the capability to create a chart in excel export using Telerik.Documents.Spreadsheet
Marcos
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));
}
}
}
Currently, the numbers in a CSV file are parsed as numbers, and the leading zeros are lost. In MS Excel, leading zeros could be preserved when the values are imported as text using the more sophisticated text import wizard (http://www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html ). Workaround: The values could be extracted using a third-party (or custom) CSV parser, and inserted manually into the model, using CellSelection.SetValueAsText method (http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-cells/cell-value-types.html ).
Add support for pivot tables.
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);