SetStyle removes leading zeros

Aspose.Cells Product Family
1

Hey folks,

I am importing an html table, doing some formatting and then saving to xls format with some weird results.
(I could be doing something wrong.)

See code below: (The variable “Content” is a string consisting of the attached txt file contents.)
The code below works okay until I un-comment the last section, at this point my alphanumeric columns loose their leading zeroes. With it commented they are displayed correctly. Is the “dataStyle” overriding something? Why is this happening? Is there a workaround?

using (var stream = new MemoryStream(Encoding.ASCII.GetBytes(Content)))

{

var loadOptions = new LoadOptions(LoadFormat.Html)

{

ConvertNumericData = false

};

var workbook = new Workbook(stream, loadOptions);

<span style="color:#93c763;">var</span> sheet <span style="color:#e8e2b7;">=</span> workbook<span style="color:#e8e2b7;">.</span>Worksheets[<span style="color:#ec7600;">"Sheet1"</span>];
sheet<span style="color:#e8e2b7;">.</span>AutoFitColumns();
sheet<span style="color:#e8e2b7;">.</span>AutoFitRows();
sheet<span style="color:#e8e2b7;">.</span>DisplayZeros <span style="color:#e8e2b7;">=</span> <span style="color:#93c763;">true</span>;

workbook<span style="color:#e8e2b7;">.</span>ChangePalette(<span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>FromArgb(<span style="color:#ffcd22;">238</span>, <span style="color:#ffcd22;">238</span>, <span style="color:#ffcd22;">238</span>), <span style="color:#ffcd22;">55</span>);

<span style="color:#93c763;">var</span> headerStyle <span style="color:#e8e2b7;">=</span> workbook<span style="color:#e8e2b7;">.</span>Styles[workbook<span style="color:#e8e2b7;">.</span>Styles<span style="color:#e8e2b7;">.</span>Add()];
headerStyle<span style="color:#e8e2b7;">.</span>ForegroundColor <span style="color:#e8e2b7;">=</span> <span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>FromArgb(<span style="color:#ffcd22;">238</span>, <span style="color:#ffcd22;">238</span>, <span style="color:#ffcd22;">238</span>);
headerStyle<span style="color:#e8e2b7;">.</span>Pattern <span style="color:#e8e2b7;">=</span> <span style="color:#678cb1;">BackgroundType</span><span style="color:#e8e2b7;">.</span>Solid;
headerStyle<span style="color:#e8e2b7;">.</span>Font<span style="color:#e8e2b7;">.</span>IsBold <span style="color:#e8e2b7;">=</span> <span style="color:#93c763;">true</span>;
headerStyle<span style="color:#e8e2b7;">.</span>SetBorder(<span style="color:#678cb1;">BorderType</span><span style="color:#e8e2b7;">.</span>TopBorder, <span style="color:#678cb1;">CellBorderType</span><span style="color:#e8e2b7;">.</span>Thin, <span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>Black);
headerStyle<span style="color:#e8e2b7;">.</span>SetBorder(<span style="color:#678cb1;">BorderType</span><span style="color:#e8e2b7;">.</span>BottomBorder, <span style="color:#678cb1;">CellBorderType</span><span style="color:#e8e2b7;">.</span>Thin, <span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>Black);
headerStyle<span style="color:#e8e2b7;">.</span>SetBorder(<span style="color:#678cb1;">BorderType</span><span style="color:#e8e2b7;">.</span>LeftBorder, <span style="color:#678cb1;">CellBorderType</span><span style="color:#e8e2b7;">.</span>Thin, <span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>Black);
headerStyle<span style="color:#e8e2b7;">.</span>SetBorder(<span style="color:#678cb1;">BorderType</span><span style="color:#e8e2b7;">.</span>RightBorder, <span style="color:#678cb1;">CellBorderType</span><span style="color:#e8e2b7;">.</span>Thin, <span style="color:#678cb1;">Color</span><span style="color:#e8e2b7;">.</span>Black);
headerStyle<span style="color:#e8e2b7;">.</span>HorizontalAlignment <span style="color:#e8e2b7;">=</span> <span style="color:#678cb1;">TextAlignmentType</span><span style="color:#e8e2b7;">.</span>Center;

<span style="color:#93c763;">var</span> firstRow <span style="color:#e8e2b7;">=</span> sheet<span style="color:#e8e2b7;">.</span>Cells<span style="color:#e8e2b7;">.</span>CreateRange(<span style="color:#ec7600;">"A1"</span>, sheet<span style="color:#e8e2b7;">.</span>Cells<span style="color:#e8e2b7;">.</span>EndCellInRow(<span style="color:#ffcd22;">0</span>)<span style="color:#e8e2b7;">.</span>Name);
firstRow<span style="color:#e8e2b7;">.</span>SetStyle(headerStyle);

<span style="color:#66747b;">//var dataCells = sheet.Cells.CreateRange(1, 0, sheet.Cells.EndCellInColumn(0).Row, sheet.Cells.EndCellInRow(0).Column + 1);</span>
<span style="color:#66747b;">//var dataStyle = workbook.Styles[workbook.Styles.Add()];</span>
<span style="color:#66747b;">//dataStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);</span>
<span style="color:#66747b;">//dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);</span>
<span style="color:#66747b;">//dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);</span>
<span style="color:#66747b;">//dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);</span>

<span style="color:#66747b;">//dataCells.SetStyle(dataStyle);</span>

<span style="color:#93c763;">using</span> (<span style="color:#93c763;">var</span> ms <span style="color:#e8e2b7;">=</span> <span style="color:#93c763;">new</span> <span style="color:#678cb1;">MemoryStream</span>())
{
    workbook<span style="color:#e8e2b7;">.</span>Save(ms, <span style="color:#678cb1;">SaveFormat</span><span style="color:#e8e2b7;">.</span>Excel97To2003);
    ms<span style="color:#e8e2b7;">.</span>Position <span style="color:#e8e2b7;">=</span> <span style="color:#ffcd22;">0</span>;
    ms<span style="color:#e8e2b7;">.</span>WriteTo(context<span style="color:#e8e2b7;">.</span>HttpContext<span style="color:#e8e2b7;">.</span>Response<span style="color:#e8e2b7;">.</span>OutputStream);
}



}
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to observe this issue. When dataCells.SetStyle(dataStyle); is commented, leading zeros are preserved. However, when dataCells.SetStyle(dataStyle); is uncommented, leading zeros are not preserved.

We have attached the screenshot illustrating this problem for a reference.

We have logged this issue in our database. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41980.

C#


string Content = @“F:\Shak-Data-RW\Downloads\table.txt”;


using (var stream = new MemoryStream(Encoding.ASCII.GetBytes(Content)))

{

var loadOptions = new LoadOptions(LoadFormat.Html)

{

ConvertNumericData = false

};

var workbook = new Workbook(“F:\Shak-Data-RW\Downloads\table1.htm”, loadOptions);


var sheet = workbook.Worksheets[“Sheet1”];

sheet.AutoFitColumns();

sheet.AutoFitRows();

sheet.DisplayZeros = true;


workbook.ChangePalette(Color.FromArgb(238, 238, 238), 55);


var headerStyle = workbook.Styles[workbook.Styles.Add()];

headerStyle.ForegroundColor = Color.FromArgb(238, 238, 238);

headerStyle.Pattern = BackgroundType.Solid;

headerStyle.Font.IsBold = true;

headerStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

headerStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);

headerStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

headerStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);

headerStyle.HorizontalAlignment = TextAlignmentType.Center;


var firstRow = sheet.Cells.CreateRange(“A1”, sheet.Cells.EndCellInRow(0).Name);

firstRow.SetStyle(headerStyle);


var dataCells = sheet.Cells.CreateRange(1, 0, sheet.Cells.EndCellInColumn(0).Row, sheet.Cells.EndCellInRow(0).Column + 1);

var dataStyle = workbook.Styles[workbook.Styles.Add()];

dataStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);

dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);

dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);


dataCells.SetStyle(dataStyle);


workbook.Save(Content + “.out.xls”, SaveFormat.Excel97To2003);


}


Screenshot:
3

Hi,


I think you may use Range.ApplyStyle(style, styleflag) overloaded method for your needs, it works fine and preserves the existing formattings, see the updated code segment for your reference:

Sample code:
//…
string Content = @“e:\test2\table.txt”;
using (var stream = new FileStream(“e:\test2\table1.htm”,FileMode.OpenOrCreate))
{
var loadOptions = new LoadOptions(LoadFormat.Html)
{
ConvertNumericData = false
};
var workbook = new Workbook(stream, loadOptions);


var sheet = workbook.Worksheets[“Sheet1”];
sheet.AutoFitColumns();
sheet.AutoFitRows();
sheet.DisplayZeros = true;

workbook.ChangePalette(Color.FromArgb(238, 238, 238), 55);

var headerStyle = workbook.Styles[workbook.Styles.Add()];
headerStyle.ForegroundColor = Color.FromArgb(238, 238, 238);
headerStyle.Pattern = BackgroundType.Solid;
headerStyle.Font.IsBold = true;
headerStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
headerStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
headerStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
headerStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
headerStyle.HorizontalAlignment = TextAlignmentType.Center;

var firstRow = sheet.Cells.CreateRange(“A1”, sheet.Cells.EndCellInRow(0).Name);
firstRow.SetStyle(headerStyle);

var dataCells = sheet.Cells.CreateRange(1, 0, sheet.Cells.EndCellInColumn(0).Row, sheet.Cells.EndCellInRow(0).Column + 1);
var dataStyle = workbook.Styles[workbook.Styles.Add()];
dataStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
dataStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
dataStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
dataStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);

var dataFlag = new StyleFlag();
dataFlag.Borders = true;
dataCells.ApplyStyle(dataStyle,dataFlag);

workbook.Save(“e:\test2\2setstyleleadingzeros.xls”, SaveFormat.Excel97To2003);
}

//…


Thank you.

4

Thank you,. thank you and thank you!

This worked, you da man!
5
Hi,

Good to know that your issue is resolved. Feel free to contact us any time if you have further queries or issue(s), we will be happy to assist you soon.

Thank you.