Formatting Issues-Looping through rows and columns to format Data

kamesh_86 · October 29, 2015, 5:05am

Hi Aspose

Currently i am evaluating aspose.cells for our requirements to convert asp.net gridviews to excel

for that i have followed couple of steps

I converted to an HTML string and load it to aspose workbook

CodeSnippets to load aspose workbook

gvSearchResult.RenderControl(htmltextwrtter);

byte[] bytearray = Encoding.ASCII.GetBytes(strwritter.ToString());

MemoryStream Result = new MemoryStream(bytearray);

Aspose.Cells.LoadOptions lo = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Html);

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(Result, lo);

Styling for headers and dates along with style flag

Aspose.Cells.Style headerstyle = wb.CreateStyle();

//style.BackgroundColor = System.Drawing.Color.Aqua;

headerstyle.Pattern = Aspose.Cells.BackgroundType.Solid;

headerstyle.ForegroundColor = System.Drawing.Color.White;

headerstyle.Font.Name = “Arial”;

headerstyle.Font.Size = 12;

headerstyle.Font.Color = System.Drawing.Color.Black;

headerstyle.Font.IsBold = true;

headerstyle.HorizontalAlignment = TextAlignmentType.Left;

headerstyle.VerticalAlignment = TextAlignmentType.Top;

headerstyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

Aspose.Cells.StyleFlag hstyleFlag = new Aspose.Cells.StyleFlag();

hstyleFlag.HorizontalAlignment = true;

hstyleFlag.VerticalAlignment = true;

hstyleFlag.FontSize = true;

hstyleFlag.FontColor = true;

hstyleFlag.Borders = true;

hstyleFlag.FontBold = true;

Aspose.Cells.Style datastyle = wb.CreateStyle();

//style.BackgroundColor = System.Drawing.Color.Aqua;

datastyle.Pattern = Aspose.Cells.BackgroundType.Solid;

datastyle.ForegroundColor = System.Drawing.Color.White;

datastyle.Font.Name = “Arial”;

datastyle.Font.Size = 12;

datastyle.Font.Color = System.Drawing.Color.Black;

datastyle.HorizontalAlignment = TextAlignmentType.Left;

datastyle.VerticalAlignment = TextAlignmentType.Top;

datastyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

Aspose.Cells.StyleFlag dstyleFlag = new Aspose.Cells.StyleFlag();

dstyleFlag.FontBold = false;

dstyleFlag.HorizontalAlignment = true;

dstyleFlag.VerticalAlignment = true;

dstyleFlag.FontSize = true;

dstyleFlag.FontColor = true;

dstyleFlag.Borders = true;

Looping the rows and columns code snippet

foreach (Aspose.Cells.Worksheet sheet in wb.Worksheets)

{

for (int row = 0; row <= sheet.Cells.MaxDataRow; row++)

{

for (int col = 0; col <= sheet.Cells.MaxDataColumn; col++)

{

if (row == 0 && col == 0)

sheet.Cells[row, col].SetStyle(headerstyle);

else

{

if (col != 0 || col != 1 || col != 2 || col != 4 || col != 5)

sheet.Cells[row, col].SetStyle(datastyle);

}

}

}

sheet.Name = “ExportTransactionResults”;

sheet.AutoFitRows();

sheet.AutoFitColumns();

}

Export Code Snippet

MemoryStream Output = new MemoryStream();

wb.Save(Output, new XlsSaveOptions(SaveFormat.Xlsx));

byte[] outputarray = Output.ToArray();

Response.BinaryWrite(outputarray);

#endregion

Response.End();

Question 1-I need to format the sheet based on the header column name and not based on the index (as the column names are dynamic)

Question 2-how to format hyperlinks column as i already have fully formed hyperlink data.currently after export its shown as plain text.

babar.raza · October 29, 2015, 7:24am

Hi Kamesh,

kamesh_86:
Question 1-I need to format the sheet based on the header column name and not based on the index (as the column names are dynamic)

Your current code snippet suggests that you are looping over individual cells to apply the style. That is one way to do it, another way could be to create a range of consecutive cells using the Cells.CreateRange method and then apply the style to it using the Range.ApplyStyle method. This way, you will be able to apply the style to all cells in a range without looping over them. You may also consider applying the style to a complete row or a column, it would work similar to applying style to range cells.

Coming back to your original concerns, there is CellsHelper.ColumnNameToIndex method that allows you to convert the column names such as A, B, C, AA, AB, AC and so on to their respective indices. Please try it on your side to see if it fulfills your requirement. However, if you wish to format the columns as per their header names, for instance, if you have a table with row 0 as your column header then you can use the Find & Search feature to find a particular cell with specified value, and then get it's column index to format it according to you needs.

kamesh_86:
Question 2-how to format hyperlinks column as i already have fully formed hyperlink data.currently after export its shown as plain text.

Formatting cells with hyperlinks is no different then formatting ordinary cell values. You can use any of the above mentioned approaches to format the cells with hyperlinks. However, please note, if you are creating the style object with Workbook.CreateStyle then you have to format all aspects of the hyperlink such as color of the text, underline to the values and so on.

Hope this helps a bit.

kamesh_86 · October 29, 2015, 8:36am

Thanks raza for your detailed reply

my problem is if i export without any FOR loop mentioned above it is exported successfully.My hyperlinks are retained but no formatting

if i use the looping it again formats properly but for hyperlink columns it disappears and shows it like a string .is their any property in style object where i can set the styletype=“Hyperlink” so that during export the hyperlinks are retained

i also checked that their is something called formula =“Hyperlink()” but i do not need to build links as my links are already built in html string.

thanks in advance

babar.raza · October 29, 2015, 8:58am

Hi Kamesh,

Thank you for response.

Well, there is no property in Style class to specify a hyperlink. Also the Hyperlink formula can be used to create a new hyperlink so that will not suffice your requirement. I believe you are overwriting the hyperlink formats somewhere in your code that is why they are appearing as simple text. Could you please create a sample application with simplest scenario that may replicate the problem on our side? Moreover, we require your sample spreadsheet (input to the program) as well for thorough analysis.

Looking forward to above requested in order to assist you further in this regard.

babar.raza · October 29, 2015, 9:28am

Hi again,

We have noticed one point that could be causing the problem with hyperlink styling. Under the Looping the rows and columns code snippet, you are setting the style without using the StyleFlag object. If the cells with hyperlinks are getting processed within this loop then style of those cells are completely overwritten with the new style object. Please note, the StyleFlag class allows to to specify the aspects of the Style that you wish to overwrite. If you choose All then everything will be overwritten, however, choosing the appropriate flag will allow you to overwrite only the specific aspects.

Anyway, a sample application along with spreadsheet will allow us to thoroughly investigate the matter on our side.

kamesh_86 · October 29, 2015, 10:34pm

Hi Raza

thanks for the quick response

here is my sample

I have attached a sample with 2 excel one with formatting and other without

My HTML String is below

–HTML start

A	B.	C	D	E	F	H	G
1	SomeValue	15/10/2011	test1	SomeValue	30/10/2009	testuser2	Teststatus1
2	Somevalue2	13/09/2007	TestProcessName1	TestProcessaname2	01/09/1970	TestUser5	TestStatus3

–HTMLend

Code without formatting

string FilePath = “C:\Test1.html”;

HTMLLoadOptions opts = new HTMLLoadOptions(LoadFormat.Html);

Workbook workbook = new Workbook(FilePath, opts);

workbook.Save(“C:\ExportResultWithOutFormatting.xlsx”, SaveFormat.Xlsx);

Code with Formatting

string FilePath = “C:\Test1.html”;

HTMLLoadOptions opts = new HTMLLoadOptions(LoadFormat.Html);

Workbook workbook = new Workbook(FilePath, opts);

Aspose.Cells.Style headerstyle = workbook.CreateStyle();

//style.BackgroundColor = System.Drawing.Color.Aqua;

headerstyle.Pattern = Aspose.Cells.BackgroundType.Solid;

headerstyle.ForegroundColor = System.Drawing.Color.White;

headerstyle.Font.Name = “Arial”;

headerstyle.Font.Size = 12;

headerstyle.Font.Color = System.Drawing.Color.Black;

headerstyle.Font.IsBold = true;

headerstyle.HorizontalAlignment = TextAlignmentType.Left;

headerstyle.VerticalAlignment = TextAlignmentType.Top;

headerstyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

headerstyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;

headerstyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

Aspose.Cells.StyleFlag hstyleFlag = new Aspose.Cells.StyleFlag();

hstyleFlag.HorizontalAlignment = true;

hstyleFlag.VerticalAlignment = true;

hstyleFlag.FontSize = true;

hstyleFlag.FontColor = true;

hstyleFlag.Borders = true;

hstyleFlag.FontBold = true;

Aspose.Cells.Style datastyle = workbook.CreateStyle();

//style.BackgroundColor = System.Drawing.Color.Aqua;

datastyle.Pattern = Aspose.Cells.BackgroundType.Solid;

datastyle.ForegroundColor = System.Drawing.Color.White;

datastyle.Font.Name = “Arial”;

datastyle.Font.Size = 12;

datastyle.Font.Color = System.Drawing.Color.Black;

datastyle.HorizontalAlignment = TextAlignmentType.Left;

datastyle.VerticalAlignment = TextAlignmentType.Top;

datastyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

datastyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;

datastyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

Aspose.Cells.StyleFlag dstyleFlag = new Aspose.Cells.StyleFlag();

dstyleFlag.FontBold = false;

dstyleFlag.HorizontalAlignment = true;

dstyleFlag.VerticalAlignment = true;

dstyleFlag.FontSize = true;

dstyleFlag.FontColor = true;

dstyleFlag.Borders = true;

foreach (Aspose.Cells.Worksheet sheet in workbook.Worksheets)

{

for (int row = 0; row <= sheet.Cells.MaxDataRow; row++)

{

for (int col = 0; col <= sheet.Cells.MaxDataColumn; col++)

{

if (row == 0)

sheet.Cells[row, col].SetStyle(headerstyle);

else

{

sheet.Cells[row, col].SetStyle(datastyle);

}

}

}

sheet.Name = “ExportResultWithFormatting”;

sheet.AutoFitRows();

sheet.AutoFitColumns();

}

workbook.Save(“C:\ExportResultWithFormatting.xlsx”, SaveFormat.Xlsx);

Hope this helps

Regards

kamesh

kamesh_86 · October 29, 2015, 10:36pm

Thanks for your reply

so how do we set the style flag for hyperlink columns

I checked their are no properties in styleflag object which sets hyperlinks=“true”;

Added Later:-I will try to apply with explicit style flag to check if hyperlinks get overwritten.

babar.raza · October 30, 2015, 12:46am

Hi Kamesh,

Please try the following piece of code that applies style as per your requirement. I tries to keep the code simple as possible for better understanding.

C#

babar.raza · October 30, 2015, 1:05am

Hi again,

Please also check the following piece of code that uses lesser number of Style objects to achieve the same goal.

C#

var book = new Workbook(“C:/temp/ExportResult.WithoutFormatting.xlsx”);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;

var gridStyle = book.CreateStyle();
gridStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
gridStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
gridStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
gridStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
gridStyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
gridStyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
gridStyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
gridStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

gridStyle.Pattern = Aspose.Cells.BackgroundType.Solid;
gridStyle.ForegroundColor = System.Drawing.Color.White;
gridStyle.Font.Name = “Arial”;
gridStyle.Font.Size = 12;
gridStyle.Font.IsBold = true;
gridStyle.HorizontalAlignment = TextAlignmentType.Left;
gridStyle.VerticalAlignment = TextAlignmentType.Top;

//Creating larger range of all cells with values
var range = cells.CreateRange(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);

//Applying borders to complete range
range.ApplyStyle(gridStyle, new StyleFlag() { Borders = true, CellShading = true, FontName = true, FontSize = true, HorizontalAlignment = true, VerticalAlignment = true });

//Applying style to header row
cells.Rows[0].ApplyStyle(gridStyle, new StyleFlag() { FontBold = true });

sheet.AutoFitColumns();
book.Save(“C:/temp/output.xlsx”);

kamesh_86 · October 30, 2015, 1:22am

Hi Raza

Thanks a lot.It worked, your second solution is much better

Also you were right ,it was style flag =all was overwriting the hyperlinks properties

thanks again for your help.

Regards

Karmugilan BJ

babar.raza · October 30, 2015, 1:36am

Hi Karmugilan,

It is good to know that you are up & running. Please feel free to contact us back in case you need our further assistance with Aspose APIs.