How do I format data while I export to Excell

Aspose.Cells Product Family
1

Hi,

Can any one helpo me how to format data while exporting to Excell.

Any quick help will be highly appreciated.

Thanks & Regards,
Hirak

2

Hi Hirak,


Welcome to the Aspose Forums and thank you for choosing Aspose as your File Format Expert.

We will be glad to assist you, if you tell us what platform [.Net or JAVA] are you using? Also please elaborate your question a little further by providing us your scenario.

Note: You can always select “Platform” from dropdown list while Creating the thread to avoid several initial investigative questions.
3

Hi,

I am working on Excell export using c# in .Net platform.

My requirement is I am displaying data from two grids in excel and I have to display in the following manner:

Grid1.
ColumnName1 ColumName2 displayed in Bold and in Yellow Background Color.

Grid2.

ColumnName1 ColumName2 displayed in Bold and in Yellow Background Color.

How do I achieve this functionality.

Any quick solution with a sample code will be highly appreciated.

Thanks & Regards,
Hirak

4

Hi Hirak,

Please see the following documents on how to set formatting for cells:
Data Formatting
Dealing with Font Settings
Cells Formatting

Here is a simple sample code on how to apply formatting to cells/range:
Sample code:

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

Cells cells = sheet.Cells;
cells[“A1”].PutValue(“Column1”);
cells[“B1”].PutValue(“Column2”); 

Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Color = Color.Red;
style.Font.IsBold = true; 
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
StyleFlag flag = new StyleFlag();
flag.FontColor = true;
flag.FontBold = true;
flag.CellShading = true;

Range range = sheet.Cells.CreateRange(“A1”, “B1”);
range.ApplyStyle(style, flag);

workbook.Save(“e:\test2\range_style_book.xlsx”);

Thank you.

5

Hi,

Thanks for your reply.

But my requirement is I want Row No to be in Bold and
Row No 23 to be in bold for all contents in those two rows only.

How do I set it and also if a cell contains data like 100000, how to format that to Rs.1,00,0,00 and if a cell contains data like 99.99997 how to format it to 99.99%.

Any quick help on this will be highly appreciated.

Thanks & Regards,
Hirak

6

Hi,

To format a complete row or a column, you may use Row/Column.ApplyStyle() method.
See the document for your help:
Applying Style on a Row or Column

To format numbers or custom numbers formatting, you may see the document:
Data Formatting

Aspose.Cells works in the same way as MS Excel, it follows MS Excel standards. If you could not evaluate how to apply custom numbers formatting, please create a simple Excel file into MS Excel with your desired numbered or other formatting and post it here, we will let you know how to do it with Aspose.Cells API

Note: I am not sure if RS. is accepted in custom numbers formatting in MS Excel, if you find a way to do it in MS Excel, let us know. Also, I think you may try custom formatting e.g “[$PKR] #,##0.00” setting Style.Custom attribute.

Thank you.

7

Hi,

Please see my code below:

private void ExportToExcelCommandExecute(object objParameter)
{

string vExportFileName = ExportHelper.CreateExportFileName(“AssetClassAllocation”, this.SelectedTradeWorkItem.PortfolioId, ExportType.Excel);

SaveFileDialog dialog = new SaveFileDialog()
{
DefaultExt = “xls”,
Filter = String.Format("{1} files (.{0})|.{0}|All files (.)|.", “xls”, “Excel”),
FilterIndex = 1
};

if (dialog.ShowDialog() == true)
{
using (Stream stream = dialog.OpenFile())
{
try
{

int count = this.AssetClassInfoModels.Count;
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn(“Asset Class”);
dt.Columns.Add(dc1);
DataColumn dc2 = new DataColumn(“Position Value”);
dt.Columns.Add(dc2);
DataColumn dc3 = new DataColumn(“Total Min”);
dt.Columns.Add(dc3);
DataColumn dc4 = new DataColumn(“Total Max”);
dt.Columns.Add(dc4);
DataColumn dc5 = new DataColumn(“Total Target”);
dt.Columns.Add(dc5);
DataColumn dc6 = new DataColumn(“Total Actual(incl. trades)”);
dt.Columns.Add(dc6);
DataColumn dc7 = new DataColumn(“Total Variance”);
dt.Columns.Add(dc7);
DataColumn dc8 = new DataColumn(“Total Range”);
dt.Columns.Add(dc8);
DataColumn dc9 = new DataColumn(“Total Actual(incl. pending trades)”);
dt.Columns.Add(dc9);
DataColumn dc10 = new DataColumn(“Total Variance(incl. pending trades)”);
dt.Columns.Add(dc10);

decimal totalPositionValue = 0;
decimal totalTargetValue = 0;
decimal totalActualValue=0;
decimal totalVarianceValue =0;
decimal totalActualPendingTradesValue=0;
decimal totalVariancePendingTradesValue=0;
foreach (AssetClassInfoModel objModel in this.AssetClassInfoModels)
{
DataRow dr = dt.NewRow();
dr[“Asset Class”] = objModel.AssetClassName;
dr[“Position Value”] = objModel.PositionValue;
totalPositionValue += objModel.PositionValue.Value;
dr[“Total Min”] = objModel.TargetMin;
dr[“Total Max”] = objModel.TargetMax;
dr[“Total Target”] = objModel.TargetTotal;
totalTargetValue += objModel.TargetTotal.Value;
dr[“Total Actual(incl. trades)”] = objModel.Actual;
totalActualValue += objModel.Actual.Value;
dr[“Total Variance”] = objModel.Variance;
totalVarianceValue+= objModel.Variance.Value;
dr[“Total Range”] = objModel.Range;
dr[“Total Actual(incl. pending trades)”] = objModel.ActualWithPendingTrades;
totalActualPendingTradesValue += objModel.ActualWithPendingTrades.Value;
dr[“Total Variance(incl. pending trades)”] = objModel.VarianceWithPendingTrades;
totalVariancePendingTradesValue += objModel.VarianceWithPendingTrades.Value;
dt.Rows.Add(dr);
}

//Add the Footer contents
DataRow drAssetClassTotal = dt.NewRow();
drAssetClassTotal[“Asset Class”] = “Asset Class Total”;
drAssetClassTotal[“Position Value”] = totalPositionValue;
drAssetClassTotal[“Total Target”] = totalTargetValue;
drAssetClassTotal[“Total Actual(incl. trades)”] = totalActualValue;
drAssetClassTotal[“Total Variance”] = totalVarianceValue;
drAssetClassTotal[“Total Actual(incl. pending trades)”] = totalActualPendingTradesValue;
drAssetClassTotal[“Total Variance(incl. pending trades)”] = totalVariancePendingTradesValue;
dt.Rows.Add(drAssetClassTotal);

DataTable dt2 = new DataTable();
DataColumn dc21 = new DataColumn(“Investment Category”);
dt2.Columns.Add(dc21);
DataColumn dc22 = new DataColumn(“Value”);
dt2.Columns.Add(dc22);
DataColumn dc23 = new DataColumn(“Adjusted Value”);
dt2.Columns.Add(dc23);
DataColumn dc24 = new DataColumn(“Total Min”);
dt2.Columns.Add(dc24);
DataColumn dc25 = new DataColumn(“Total Max”);
dt2.Columns.Add(dc25);
DataColumn dc26 = new DataColumn(“Total Target”);
dt2.Columns.Add(dc26);
DataColumn dc27 = new DataColumn(“Total Actual”);
dt2.Columns.Add(dc27);
DataColumn dc28 = new DataColumn(“Total Variance”);
dt2.Columns.Add(dc28);
DataColumn dc29 = new DataColumn(“Total Range”);
dt2.Columns.Add(dc29);


decimal totalValue = 0;
decimal adjustedValue = 0;
decimal totalTarget = 0;
decimal totalActual = 0;
decimal totalVariance = 0;
foreach (AssetClassInvestmentCategoryInfoModel objModel in this.AssetClassInvestmentCategoryInfoModels)
{
DataRow dr2 = dt2.NewRow();
dr2[“Investment Category”] = objModel.InvestmentCategoryName;
dr2[“Value”] = objModel.PositionValue;
totalValue += objModel.PositionValue.Value;
dr2[“Adjusted Value”] = objModel.AdjustedValue;
adjustedValue += objModel.AdjustedValue.Value;
dr2[“Total Min”] = objModel.TotalMin;
dr2[“Total Max”] = objModel.TotalMax;
dr2[“Total Target”] = objModel.Target;
totalTarget += objModel.Target.Value;
dr2[“Total Actual”] = objModel.Actual;
totalActual += objModel.Actual.Value;
dr2[“Total Variance”] = objModel.Variance;
totalVariance += objModel.Variance.Value;
dr2[“Total Range”] = objModel.Range;
dt2.Rows.Add(dr2);
}


//Add the Footer contents
DataRow drInvestmentCategoryTotal = dt2.NewRow();
drInvestmentCategoryTotal[“Investment Category”] = “Total”;
drInvestmentCategoryTotal[“Value”] = totalValue;
drInvestmentCategoryTotal[“Adjusted Value”] = adjustedValue;
drInvestmentCategoryTotal[“Total Target”] = totalTarget;
drInvestmentCategoryTotal[“Total Actual”] = totalActual;
drInvestmentCategoryTotal[“Total Variance”] = totalVariance;
dt2.Rows.Add(drInvestmentCategoryTotal);




Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

sheet.Name = vExportFileName;

StyleFlag styleflag;

Aspose.Cells.Style style;

// Loop through all the columns in the worksheet and unlock them.
//for (int i = 0; i <= 255; i++)
//{

// style = sheet.Cells.Columns[(byte)i].Style;
// style.IsLocked = false;
// styleflag = new StyleFlag();
// styleflag.Locked = true;
// sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
//}

for (int i = 0; i <= 65536; i++)
{

style = sheet.Cells.Rows[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Rows[(byte)i].ApplyStyle(style, styleflag);
}

Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

Aspose.Cells.Style style1 = workbook.Styles[0];
style1.IsLocked = true;
style1.BackgroundColor = System.Drawing.Color.White;
style1.Font.Color = System.Drawing.Color.Red;
style1.Font.IsBold = true;
style1.Font.Size = 10;
style1.IsTextWrapped = true;


style1.ForegroundColor = System.Drawing.Color.Yellow;
style1.Pattern = BackgroundType.Solid;
StyleFlag styleflag1 = new StyleFlag();
styleflag1.Locked = true;
styleflag1.FontBold = true;
styleflag1.FontSize = true;
styleflag1.FontColor = true; 
styleflag1.CellShading = true;
styleflag1.WrapText = true;


cells.ApplyRowStyle(0, style1, styleflag1);

//Test



//Aspose.Cells.Row row = cells.Rows[2];

//row.ApplyStyle(style1, styleflag1);





//Accessing the newly added Style to the Excel object

Aspose.Cells.Style style2 = workbook.Styles[0];



//Setting the vertical alignment of the text in the “A1” cell

style2.VerticalAlignment = TextAlignmentType.Center;



//Setting the horizontal alignment of the text in the “A1” cell

style2.HorizontalAlignment = TextAlignmentType.Center;



//Setting the font color of the text in the “A1” cell

style2.Font.Color = Color.Green;


//Setting the bottom border color of the cell to red

style2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = Color.Red;



//Setting the bottom border type of the cell to medium

style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Medium;



//Creating StyleFlag

StyleFlag styleFlag2 = new StyleFlag();

styleFlag2.HorizontalAlignment = true;

styleFlag2.VerticalAlignment = true;



styleFlag2.Borders = true;

styleFlag2.FontColor = true;



//Accessing a row from the Rows collection

Aspose.Cells.Row row = sheet.Cells.Rows[23];



//Assigning the Style object to the Style property of the row

row.ApplyStyle(style2, styleFlag2);

//Test


// Loop through all the columns & set the column width
for (int i = 0; i <= 255; i++)
{
cells.SetColumnWidth(i, 25);
}

//cells.SetColumnWidth(0, 25);
cells.SetRowHeight(0, 30);
cells.Clear();

cells.ImportDataTable(dt, true, 0, 0, true, true);


cells.ImportDataTable(dt2, true, count + 3, 0, true,true);

workbook.Save(stream, Aspose.Cells.FileFormatType.Excel2007Xlsx);


}
catch
{
return;
}
}
}

}

In the above I am unable to format the 23rd Row’s data through Aspose API.

Any quick help will be highly usefull.

Thanks & Regards,
Hirak

Save Edit
cancel

8

Hi,

After some investigation, I found there is a culprit line i.e. cells.Clear()l --> it clears all the cells with their formatting, please delete this line of code.
I used the following sample code with my own data tables to import data to the cells first. Create some styles with their attributes and finally apply the styles to some rows it works fine. I saved as an xlsx file.
Please refer to the code (I updated your code a bit), it would give you complete insight for your needs.

Sample code:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
sheet.Name = “MySheet”;
StyleFlag styleflag;
Aspose.Cells.Style style;

//browse all the columns.
for (int i = 0; i <= 256; i++)
{

style = workbook.Styles[workbook.Styles.Add()];
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[i].ApplyStyle(style, styleflag);
}

Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

DataTable table = new DataTable();
table.Columns.Add(“Id”);
table.Columns.Add(“Name”);

for (int i = 0; i < 20; i++)
{
DataRow row = table.NewRow();
row[0] = i;
row[1] = “Name” + i;
table.Rows.Add(row);
}

cells.ImportDataTable(table,true, 0,0,false,true);

DataTable table2 = new DataTable();
table2.Columns.Add(“MId”);
table2.Columns.Add(“MName”);

for (int i = 0; i < 20; i++)
{
DataRow row = table2.NewRow();
row[0] = i+100;
row[1] = “MName” + i;
table2.Rows.Add(row);
}

cells.ImportDataTable(table2, true, cells.MaxDataRow +3, 0, false, true);

Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.IsLocked = true;
//style1.BackgroundColor = System.Drawing.Color.White;
style1.Font.Color = System.Drawing.Color.Red;
style1.Font.IsBold = true;
style1.Font.Size = 10;
style1.IsTextWrapped = true;


style1.ForegroundColor = System.Drawing.Color.Yellow;
style1.Pattern = BackgroundType.Solid;
StyleFlag styleflag1 = new StyleFlag();
styleflag1.Locked = true;
styleflag1.FontBold = true;
styleflag1.FontSize = true;
styleflag1.FontColor = true;
styleflag1.CellShading = true;
styleflag1.WrapText = true;


cells.ApplyRowStyle(0, style1, styleflag1);


//Accessing the newly added Style to the Excel object
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];

//Setting the vertical alignment of the text in the “A1” cell
style2.VerticalAlignment = TextAlignmentType.Center;

//Setting the horizontal alignment of the text in the “A1” cell
style2.HorizontalAlignment = TextAlignmentType.Center;

//Setting the font color of the text in the “A1” cell
style2.Font.Color = Color.Green;

//Setting the bottom border color of the cell to red
style2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = Color.Red;

//Setting the bottom border type of the cell to medium
style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

//Creating StyleFlag
StyleFlag styleFlag2 = new StyleFlag();
styleFlag2.HorizontalAlignment = true;
styleFlag2.VerticalAlignment = true;
styleFlag2.Borders = true;
styleFlag2.FontColor = true;


//Accessing a row from the Rows collection
Aspose.Cells.Row crow = sheet.Cells.Rows[23];
//Assigning the Style object to the Style property of the row
crow.ApplyStyle(style2, styleFlag2);

//Test
// Loop through all the columns & set the column width
for (int i = 0; i <= 255; i++)
{
cells.SetColumnWidth(i, 25);
}


cells.SetRowHeight(0, 30);

workbook.Save(“e:\test2\formattedrows_andimport.xlsx”, SaveFormat.Xlsx);;

Please also find attached the output Excel file for the your reference.

Thank you.

9

Hi,

Thanks for your prompt response.

I am following your guidelines.

One more issue which needs to be solved is how do I display data in C1 column like 20%, 30%,78.23% and so on.

Please, provide me a quick solution to this formatting issue in your above explained code or in my code how to implement that.

Thanks & Regards,
Hirak

10

Hi,

Please see my updated code segment with addition of some lines (regarding percentage formatting) for your reference:

Sample code:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

sheet.Name = "MySheet";

StyleFlag styleflag;

Aspose.Cells.Style style;

//browse all the columns.

for (int i = 0; i <= 256; i++)

{

style = workbook.Styles[workbook.Styles.Add()];

style.IsLocked = false;

styleflag = new StyleFlag();

styleflag.Locked = true;

sheet.Cells.Columns[i].ApplyStyle(style, styleflag);

}

Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

DataTable table = new DataTable();

table.Columns.Add("Id");

table.Columns.Add("Name");

table.Columns.Add("Percentage", typeof(double));

Random random = new Random();

for (int i = 0; i < 20; i++)

{

DataRow row = table.NewRow();

row[0] = i;

row[1] = "Name" + i;

row[2] = random.NextDouble();

table.Rows.Add(row);

}

cells.ImportDataTable(table,true, 0,0,false,true);

DataTable table2 = new DataTable();

table2.Columns.Add("MId");

table2.Columns.Add("MName");

for (int i = 0; i < 20; i++)

{

DataRow row = table2.NewRow();

row[0] = i+100;

row[1] = "MName" + i;

table2.Rows.Add(row);

}

cells.ImportDataTable(table2, true, cells.MaxDataRow +3, 0, false, true);

Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];

style1.IsLocked = true;

style1.BackgroundColor = System.Drawing.Color.White;

style1.Font.Color = System.Drawing.Color.Red;

style1.Font.IsBold = true;

style1.Font.Size = 10;

style1.IsTextWrapped = true;

style1.ForegroundColor = System.Drawing.Color.Yellow;

style1.Pattern = BackgroundType.Solid;

StyleFlag styleflag1 = new StyleFlag();

styleflag1.Locked = true;

styleflag1.FontBold = true;

styleflag1.FontSize = true;

styleflag1.FontColor = true;

styleflag1.CellShading = true;

styleflag1.WrapText = true;

cells.ApplyRowStyle(0, style1, styleflag1);

//Accessing the newly added Style to the Excel object

Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];

//Setting the vertical alignment of the text in the "A1" cell

style2.VerticalAlignment = TextAlignmentType.Center;

//Setting the horizontal alignment of the text in the "A1" cell

style2.HorizontalAlignment = TextAlignmentType.Center;

//Setting the font color of the text in the "A1" cell

style2.Font.Color = Color.Green;

//Setting the bottom border color of the cell to red

style2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = Color.Red;

//Setting the bottom border type of the cell to medium

style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

//Creating StyleFlag

StyleFlag styleFlag2 = new StyleFlag();

styleFlag2.HorizontalAlignment = true;

styleFlag2.VerticalAlignment = true;

styleFlag2.Borders = true;

styleFlag2.FontColor = true;

//Accessing a row from the Rows collection

Aspose.Cells.Row crow = sheet.Cells.Rows[23];

//Assigning the Style object to the Style property of the row

crow.ApplyStyle(style2, styleFlag2);

//Test

// Loop through all the columns & set the column width

for (int i = 0; i <= 255; i++)

{

cells.SetColumnWidth(i, 25);

}

Style styleper = workbook.Styles[workbook.Styles.Add()];

styleper.Custom = "0.00%";

StyleFlag flag3 = new StyleFlag();

flag3.NumberFormat = true;

//Apply the style to C column

cells.ApplyColumnStyle(2, styleper,flag3);

cells.SetRowHeight(0, 30);

workbook.Save("e:\\test2\\formattedrows_andimport.xlsx", SaveFormat.Xlsx);

11

Hi,

Your reply was excellent. I could achieve all most what I needed.

Now, in the format of columns I have a new issue.

In the 3rd column of my excell Data is displayed as 500.00%.

How do I format it so that it displays 5.00%.

Any quick help will be appreciable.

Thanks & Regards,
Hirak
12

Hi,

Well, Aspose.Cells works in the same way as MS Excel does. For your information 5% means --> =5/100, please make sure that your base values (for percentages) should be placed accordingly. You may confirm this in MS Excel manually. For example, in a cell, place 5, now when you set percentage formatting (using Format Cells dialog box), it would come as "500.00%". So, Aspose.Cells works the same way as MS Excel does.

You have to adjust/set the source values for percentage columns. Check the following for your reference.

e.g

=5/100 or 0.05----> when formatted as percentage the value would come out as: 5.00%

Thank you.

13

Hi,

Thanks once again for your prompt reply.

But, here there is one more issue for the 2nd table in the same column it should be displayed as 5,00 instead of 5.00%.

How to separate the formatting for the 2nd table in the excell sheet using Aspose API.

Any quick reply is appreciated.

Thanks & Regards,
Hirak

14

Hi,

“But, here there is one more issue for the 2nd table in the same column it should be displayed as 5,00 instead of 5.00%.”

Check my above reply for your complete reference: How do I format data while I export to Excell

“How to separate the formatting for the 2nd table in the excell sheet using Aspose API.”

I am not sure about your need. If you need to apply specific formatting for the second data table (that was imported into the sheet), you may create your desired range based on the cells (of the second table), create your desired style with formatting attributes, create corresponding style flag and on the related properties. Now, you may apply style to the range. See the document:
Using Named Ranges

Thank you.

15

Hi,


As Amjad wrote, what you are looking for can be achieved using NameRanges. Please review the documentation shared in earlier post.
Below is the code that creates two Ranges and formats them separately. See code in Bold.
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();

Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

sheet.Name = "MySheet";

StyleFlag styleflag;

Aspose.Cells.Style style;

//browse all the columns.
for
(int i = 0; i <= 256; i++)
{
style = workbook.Styles[workbook.Styles.Add()];

style.IsLocked = false;

styleflag = new StyleFlag();

styleflag.Locked = true;

sheet.Cells.Columns[i].ApplyStyle(style, styleflag);
}

Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

DataTable table = new DataTable();

table.Columns.Add("Id");

table.Columns.Add("Name");

table.Columns.Add("Percentage", typeof(double));

Random random = new Random();
for
(int i = 0; i < 20; i++)
{
DataRow row = table.NewRow();

row[0] = i;

row[1] = "Name" + i;

row[2] = random.NextDouble();

table.Rows.Add(row);
}

cells.ImportDataTable(table, true, 0, 0, false, true);

DataTable table2 = new DataTable();

table2.Columns.Add("MId");

table2.Columns.Add("MName");

table2.Columns.Add("AnotherValue");

for
(int i = 0; i < 20; i++)
{
DataRow row = table2.NewRow();

row[0] = i + 100;

row[1] = "MName" + i;

row[2] = (i + 100)*10;

table2.Rows.Add(row);
}

cells.ImportDataTable(table2, true, cells.MaxDataRow + 3, 0, false, true);

Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];

style1.IsLocked = true;

style1.BackgroundColor = System.Drawing.Color.White;

style1.Font.Color = System.Drawing.Color.Red;

style1.Font.IsBold = true;

style1.Font.Size = 10;

style1.IsTextWrapped = true;

style1.ForegroundColor = System.Drawing.Color.Yellow;

style1.Pattern = BackgroundType.Solid;

StyleFlag styleflag1 = new StyleFlag();

styleflag1.Locked = true;

styleflag1.FontBold = true;

styleflag1.FontSize = true;

styleflag1.FontColor = true;

styleflag1.CellShading = true;

styleflag1.WrapText = true;

cells.ApplyRowStyle(0, style1, styleflag1);

//Accessing the newly added Style to the Excel object
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];

//Setting the vertical alignment of the text in the "A1" cell
style2.VerticalAlignment = TextAlignmentType.Center;

//Setting the horizontal alignment of the text in the "A1" cell
style2.HorizontalAlignment = TextAlignmentType.Center;

//Setting the font color of the text in the "A1" cell
style2.Font.Color = Color.Green;

//Setting the bottom border color of the cell to red
style2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = Color.Red;

//Setting the bottom border type of the cell to medium
style2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

//Creating StyleFlag
StyleFlag styleFlag2 = new StyleFlag();

styleFlag2.HorizontalAlignment = true;

styleFlag2.VerticalAlignment = true;

styleFlag2.Borders = true;

styleFlag2.FontColor = true;

//Accessing a row from the Rows collection
Aspose.Cells.Row crow = sheet.Cells.Rows[23];

//Assigning the Style object to the Style property of the row
crow.ApplyStyle(style2, styleFlag2);
// Loop through all the columns & set the column width
for
(int i = 0; i <= 255; i++)
{
cells.SetColumnWidth(i, 25);
}

Range range1 = cells.CreateRange("C2:C21");

Range range2 = cells.CreateRange("C25:C44");

Style styleper = workbook.Styles[workbook.Styles.Add()];

styleper.Custom = "0.00%";

StyleFlag flag3 = new StyleFlag();

flag3.NumberFormat = true;

Style stylenum = workbook.Styles[workbook.Styles.Add()];
stylenum.Number = 3;

range1.ApplyStyle(styleper, flag3);
range2.ApplyStyle(stylenum, flag3);

cells.SetRowHeight(0, 30);

workbook.Save("C:\\formattedrows_andimport.xlsx", SaveFormat.Xlsx);
16

Hi,

Thanks for your prompt response, I was able to export my data correctly with formatting.

My last question in this regard is how do I put a label which will say DataTable1 Content above my DataTable1 cell display and DataTable2 Content above my DataTable2 cell display.

Please, provide me a quick solution for this labelling issue.

Thanks & Regards,
Hirak

17

Hi,

Please use Cell.PutValue() method to insert your desired text into the specified cells, see the documents for your reference:
Accessing Cells in a Worksheet
Add Data in Cells

Thank you.