Formatting issue in excel

swapnams2003 · April 16, 2011, 4:33am

Hi,

I am using Aspose.Cells to generate excel. I am facing some formatting issue. Text wrapping is not working for the header row. I have written the code for this purpose -

style.IsTextWrapped = true;

Also,Value in each cell is generated in text format. I need it in number format.

cells[count + 1, j + 1].Style.Number = 9;

Please find the below code that i am using for generating excel. Please help me in solving both these issues.

Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense(HttpContext.Current.Server.MapPath("~\\License\\Aspose.Total.lic"));

Workbook workBook = new Workbook();

Aspose.Cells.Worksheet wsContent = workBook.Worksheets[0];

try

{

wsContent.Name = "Monthly Statistics Report";

Aspose.Cells.Cells cells = wsContent.Cells;

wsContent.Cells.SetColumnWidthPixel(0, 100);

cells[0, 0].PutValue("Month");

cells[0, 1].PutValue("Sent");

cells[0, 2].PutValue("Replied");

cells[0, 3].PutValue("Responded %");

cells[0, 4].PutValue("Very Sat.");

cells[0, 5].PutValue("Moderately Sat.");

cells[0, 6].PutValue("Slightly Sat.");

cells[0, 7].PutValue("Slightly Dissat.");

cells[0, 8].PutValue("Moderately Dissat.");

cells[0, 9].PutValue("Very Dissat.");

cells[0, 10].PutValue("Satisfied %");

Aspose.Cells.Style style = workBook.Styles[workBook.Styles.Add()];

style.Font.Color = System.Drawing.Color.White;

style.ForegroundColor = System.Drawing.Color.SteelBlue;

style.Font.IsBold = true;

style.IsTextWrapped = true;

style.Pattern = BackgroundType.Solid;

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

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

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

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

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

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

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

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

style.HorizontalAlignment = TextAlignmentType.Center;

style.Number = 9;

for (int counter = 0; counter < 11; counter++)

{

wsContent.Cells.SetColumnWidthPixel(counter + 1, 70);

cells[0, counter].SetStyle(style);

cells[0, counter].Style.Font.Name = "Calibri";

cells[0, counter].Style.Font.Size = 11;

}

int count = 1;

foreach (GridViewRow grvRow in grdReport.Rows)

{

cells[count, 0].PutValue(grvRow.Cells[0].Text);

cells[count, 1].PutValue(grvRow.Cells[1].Text);

cells[count, 2].PutValue(grvRow.Cells[2].Text);

cells[count, 3].PutValue(grvRow.Cells[3].Text);

cells[count, 4].PutValue(grvRow.Cells[4].Text);

cells[count, 5].PutValue(grvRow.Cells[5].Text);

cells[count, 6].PutValue(grvRow.Cells[6].Text);

cells[count, 7].PutValue(grvRow.Cells[7].Text);

cells[count, 8].PutValue(grvRow.Cells[8].Text);

cells[count, 9].PutValue(grvRow.Cells[9].Text);

cells[count, 10].PutValue(grvRow.Cells[10].Text);

// &=cells[count,0].ToString()(numeric);

cells[count, 0].Style.HorizontalAlignment = TextAlignmentType.Left;

for (int j = 0; j < 11; j++)

{

cells[count, j].Style.Font.Name = "Calibri";

cells[count, j].Style.Font.Size = 11;

cells[count + 1, j + 1].Style.Number = 9;

cells[count, j + 1].Style.HorizontalAlignment = TextAlignmentType.Right;

cells[count, j].Style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;

cells[count, j].Style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;

cells[count, j].Style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;

cells[count, j].Style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;

cells[count, j].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

cells[count, j].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

cells[count, j].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

cells[count, j].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

}

count++;

}

workBook.Save("Monthly Statistics for " + Common.General.GetTravelAgencySubQuestion(Convert.ToInt32(ddlCategory.SelectedValue)) + ".xlsx", FileFormatType.Default, Aspose.Cells.SaveType.OpenInBrowser, this.Response);

HttpContext.Current.Response.End();

}

catch (Exception ex)

{

throw ex;

}

babar.raza · April 16, 2011, 8:08am

Hi,

Thanks for the source code. From your source code we noticed Two main points [regarding your issues]

You are using an older version of Aspose.Cells for .Net, cause many Properties/Methods from your source code are obsoleted now.
If you are Inserting / Updating a value in Cells like cells[count, 2].PutValue(grvRow.Cells[2].Text). then the value is considered as “String”. You may Convert the value to its native format before setting it to cell.

Please check the Aspose.Cells for .Net API Reference. You need to consult Style Class, StyleFlag Class and StyleCollection Class under Aspose.Cells Namespace.

We recommend you to try our latest version of the product in which we have made lots of enhancements and added many new features. Moreover, please check the Aspose.Cells for .NET documentation for your complete reference. This is important for you to change/update your existing code a bit according to our latest Aspose.Cells for .NET API.

Developer’s Guide:

For starter, we put-up some code together, so you can get familiar with new approach,

C#

Workbook workBook = new Workbook();

Aspose.Cells.Worksheet wsContent = workBook.Worksheets[0];

try

{

wsContent.Name = “TestSheet”;

Aspose.Cells.Cells cells = wsContent.Cells;

wsContent.Cells.SetColumnWidthPixel(0, 100);

cells[0, 0].PutValue(“Month”);

cells[0, 1].PutValue(“ColA”);

cells[0, 2].PutValue(“ColB”);

cells[0, 3].PutValue(“ColC”);

Aspose.Cells.Style Hstyle = new Style();

Hstyle.Font.Color = System.Drawing.Color.White;

Hstyle.ForegroundColor = System.Drawing.Color.SteelBlue;

Hstyle.Font.IsBold = true;

Hstyle.IsTextWrapped = true;

Hstyle.Pattern = BackgroundType.Solid;

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

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

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

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

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

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

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

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

Hstyle.HorizontalAlignment = TextAlignmentType.Center;

Hstyle.Number = 9;

for (int col = 0; col < 4; col++)

{

wsContent.Cells.SetColumnWidthPixel(col + 1, 70);

Hstyle.Font.Name = “Calibri”;

Hstyle.Font.Size = 11;

cells[0, col].SetStyle(Hstyle);

}

Aspose.Cells.Style style = new Style();

style.IsTextWrapped = true;

cells[1, 0].PutValue(“January”);

cells[1, 1].PutValue(“100”); //This value will behave as string

cells[1, 2].PutValue(100);

cells[1, 3].PutValue(“Hello World”); //Text is wrapped Fine

cells[1, 3].SetStyle(style);

workBook.Save(“C:\test.xlsx”);

swapnams2003 · April 17, 2011, 11:41pm

Hi,

I am using the Aspose.Cells Version 5.1.3.0. This won't work?

swapnams2003 · April 18, 2011, 1:14am

Hi,

I have tried with the evaluation version Aspose.Cells(5.3.2.0) and the sample code you have given. Still i am getting the same issue. Please find the attached excel. Also, I am not able to give the following code that you have suggested.

Aspose.Cells.Style Hstyle = new Style();

Getting the error - Cannot implicitly convert type 'System.Web.UI.WebControls.Style' to 'Aspose.Cells.Style'

So i have replaced with the following code.

Aspose.Cells.Style Hstyle = workBook.Styles[workBook.Styles.Add()];

But still i am facing the same issue. Could you please help me.

babar.raza · April 18, 2011, 1:56am

swapnams2003:

Aspose.Cells.Style Hstyle = new Style();

Getting the error - Cannot implicitly convert type 'System.Web.UI.WebControls.Style' to 'Aspose.Cells.Style'

So i have replaced with the following code.

Aspose.Cells.Style Hstyle = workBook.Styles[workBook.Styles.Add()];

Hi,

You are getting this error because lack of Fully Qualified Name on right side of the '=' sign. There is also a Namspace in System.Web.UI.WebControls by name of Style. So compiler threw this error.

That is my fault, I should have written it on first place as below.

Aspose.Cells.Style Hstyle = new Aspose.Cells.Style();

Please give it a try and let us know if you have any queries.

amjad.sahi · April 18, 2011, 1:59am

Hi,

Well, I think you may either extend the row(s) width accordingly or call AutoFit Row operation (either use AutoFitRow method or use AutoFitRows) method upon the row whose content (in the cells) are set for wrapping text to view them properly. e.g You can use a line of code before saving:

wsContent.AutoFitRows();
workBook.Save(“C:\test.xlsx”);

Thank you.

swapnams2003 · April 18, 2011, 11:29pm

Hi,

I have used the line of code "wsContent.AutoFitRows()" and the text wrapping is working fine. But one more issue is still pending. The content in my excel is numbers. I have applied the style.

style.Number=9;

But still it's coming as text. How to solve this issue....Please help me...

babar.raza · April 19, 2011, 12:36am

Hi,

It is working fine at our end, using the same code given in my previous post. Please confirm you are applying Number Type formatting on a cell having Number Type Contents.

My code is as below,

C#

Aspose.Cells.Style style = new Aspose.Cells.Style();

style.Number = 9;

cells[1, 2].SetStyle(style);

swapnams2003 · April 19, 2011, 2:19am

Hi,

I am not able to give the following code

Aspose.Cells.Style style = new Aspose.Cells.Style();

as i am getting an error, "The type 'Aspose.Cells.Style' has no constructors defined". Instead, I am using the code

Aspose.Cells.Style style = workBook.Styles[workBook.Styles.Add()];

Also, in the attached excel, the second column in still showing as text. I am attaching the screenshot. Please help me in converting the text to number...

babar.raza · April 19, 2011, 3:13am

Hi,

I have attached a ZIP archive of project that I was using to test your requirements. Aspose.Cells for .Net v5.3.2.2 assembly is also included in it.

Moreover, regarding the value in B2 cell, I have mentioned earlier in my post that if you update / insert cell value like cells[1, 1].PutValue(“100”); then the value 100 will behave like a string. You need to update / insert value like cells[1, 1].PutValue(100); or you can use a overloaded method of PutValue that takes another boolean argument by name “IsConverted”. Example is as below,

cells[1, 1].PutValue(“100”, true);

If “IsConverted” is set true, then the PutValue method will convert the first argument to it’s native format. In above case “100” string will be converted to integer 100.

swapnams2003 · April 19, 2011, 3:43am

Hi,

That worked fine. Thank you so much.