Query about style

[dvd.vinay]

Hi,

I am copying style from a reference formatted cell on worksheet to destination cell. The reference formatted cell has a custom number format applied to it "#,##0_ ;[Red]-#,##0 ". This is just to facilitate value to turn red when user enters a negative value in the cell.

Problem is, when doing it at run time, if negative value is coming from database and populated in the destination cell (with style copied from reference cell) the value is not turning red but when I change the value to some other negative value it turns red.

I checked the format applied to the destination cell and see that format is copied properly. Am I missing some thing here?

Please find attached code for your reference. We are using licence version of Aspose.Cells 4.9.0.1

Regards, Vinay

[Amjad_Sahi]

Hi Vinay,

It is quite possible that your source database table’s field has string values (has string data type) instead of numeric which are being populated to the cells. I have tried your scenario with my simple sample code and it works fine, see the attached output file for your reference.

Sample code:
DataTable dt = new DataTable(“Customers”);
dt.Columns.Add(“C_FName”);
dt.Columns.Add(“C_LName”);
dt.Columns.Add(“Num”, typeof(decimal));
DataRow dr = dt.NewRow();
dr[“C_FName”] = “Richard”;
dr[“C_LName”] = “Milton”;
dr[“Num”] = -45;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[“C_FName”] = “Micheal”;
dr[“C_LName”] = “Dowdan”;
dr[“Num”] = 41;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[“C_FName”] = “ABC”;
dr[“C_LName”] = “DEF”;
dr[“Num”] = -139;
dt.Rows.Add(dr);

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding a new Style to the styles collection of the Excel object
int styleIndex = workbook.Styles.Add();
//Accessing the newly added Style to the Excel object
Style style = workbook.Styles[styleIndex];
StyleFlag styleFlag = new StyleFlag();

style.Custom = “#,##0_ ;[Red]-#,##0”;

//Apply your desired attributes on.
styleFlag.NumberFormat = true;

//Assigning the Style object to the third column.
worksheet.Cells.ApplyColumnStyle(2, style, styleFlag);

//Populate the data starting from A1 Cell.
worksheet.Cells.ImportDataTable(dt, false, 0, 0,false);

workbook.Save(“e:\test\MySpecificBookStyle.xls”);


If you still find any issue or confusion, kindly do create a console application as demo (or create a complete sample as I pasted above) to post us to reproduce the issue you have talked about. We will check it soon.

Thank you.

[dvd.vinay]

Yep, that solved the problem. But there is another one to add to (have attached sample input and output files) and Licence file is 4.9.0.1

I just want to copy the background color from a reference cell and its not working. Following is the code

private void button1_Click(object sender, EventArgs e)

{

Workbook workbook = new Workbook();

workbook.Open(@"C:\\Book1.xls", FileFormatType.Excel97To2003);

Worksheet sheet = workbook.Worksheets[0];

Style st = sheet.Cells["D6"].GetStyle();

Style st1 = sheet.Cells["E8"].GetStyle();

st1.BackgroundColor = st.BackgroundColor;

st1.ForegroundColor = st.ForegroundColor;

sheet.Cells["E8"].SetStyle(st1);

workbook.Save(@"C:\Users\vdwivedi\Desktop\Archives\Book2.xls");

}

[dvd.vinay]

Hi Amjad,

I've posted a thread with the subject line "Re: Query about style". We required urgent fix on that. We continued our development based on solution provided in thread "Adding data from datatable with parellel formatting" though it was just . Now this is a stale situation.

Can you please provide us a fix asap?

Thanks, Vinay

[Amjad_Sahi]

Hi,

Please add a line to your code:

Workbook workbook = new Workbook();

workbook.Open(@"C:\\Book1.xls", FileFormatType.Excel97To2003);

Worksheet sheet = workbook.Worksheets[0];

Style st = sheet.Cells["D6"].GetStyle();

Style st1 = sheet.Cells["E8"].GetStyle();

st1.BackgroundColor = st.BackgroundColor;

st1.ForegroundColor = st.ForegroundColor;

st1.Pattern = st.Pattern;

sheet.Cells["E8"].SetStyle(st1);

workbook.Save(@"C:\Users\vdwivedi\Desktop\Archives\MyBook2.xls");

I have tested it with the above code and it works fine. Attached is my output file.

I am using v4.9.0.x.

Thank you.

[dvd.vinay]

Thanks, this resolved the issue.