Please find the below code i have tried.
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Setting the name of the newly added worksheet
worksheet.Name = "Top_10_Errors";
worksheet.Cells.ImportDataTable(dt, true, "A2");
// Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
// Merge some Cells (C6:E7) into a single C6 Cell.
cells.Merge(0, 0, 1, 2);
// Input data into C6 Cell.
worksheet.Cells["A1"].PutValue("Top 10 Errors-EXCEL");
// Create a Style object to fetch the Style of C6 Cell.
Style style = worksheet.Cells["A1"].GetStyle();
// Create a Font object
Font font = style.Font;
// Set the name.
font.Name = "Times New Roman";
// Set the font size.
font.Size = 14;
// Set the font color
font.Color = System.Drawing.Color.Black;
// Bold the text
font.IsBold = true;
// Set the backgrond color of C6 Cell to Red
// style.ForegroundColor = System.Drawing.Color.Red;
//style.Pattern = BackgroundType.Solid;
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
//style.SetBorder(BorderType.BottomBorder, CellBorderType.DashDotDot, System.Drawing.Color.Black);
// Apply the Style to C6 Cell.
cells["A1"].SetStyle(style);
//Cells cells1 = worksheet.Cells;
Style style1 = cells["A2"].GetStyle();
// Create a Font object
Font font1 = style1.Font;
// Set the name.
font1.Name = "Times New Roman";
// Set the font size.
font1.Size = 14;
// Set the font color
font1.Color = System.Drawing.Color.Black;
// Bold the text
font1.IsBold = true;
style1.Pattern = BackgroundType.Solid;
//style1.BackgroundColor = System.Drawing.Color.LightBlue;
style1.ForegroundColor = System.Drawing.Color.LightBlue;
Style style2 = cells["B2"].GetStyle();
// Create a Font object
Font font2 = style2.Font;
// Set the name.
font2.Name = "Times New Roman";
// Set the font size.
font2.Size = 14;
// Set the font color
font2.Color = System.Drawing.Color.Black;
// Bold the text
font2.IsBold = true;
style1.Pattern = BackgroundType.Solid;
// style2.BackgroundColor = System.Drawing.Color.LightBlue;
style2.ForegroundColor = System.Drawing.Color.LightBlue;
cells["A2"].SetStyle(style1);
cells["B2"].SetStyle(style2);
//Cells cells2 = workbook.Worksheets[0].Cells;
Range range1 = cells.CreateRange("A1", "B12");
Style stl = workbook.CreateStyle();
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
StyleFlag flg = new StyleFlag();
flg.Borders = true;
range1.ApplyStyle(stl, flg);
worksheet.Cells["A14"].PutValue("Downloaded Date and Time:");
worksheet.Cells["B14"].PutValue(Convert.ToDateTime(DateTime.Now).ToString("dd-MMM-yyyy@HH.mm.ss"));
worksheet.Cells["A16"].PutValue("Downloaded By:");
worksheet.Cells["B16"].PutValue("Nagesh Kotha");
// Saving the Excel file
worksheet.AutoFitColumns();
workbook.Save(m_DownloadFolder + filename);
I have checked your desired file and compared with current output file by Aspose.Cells APIs. I found some data table contents are not the same (so you must be using some other DataTable having different “Count”). I guess this is not your issue. However, I found different row heights (starting from row 3 to row 12) and font name. Also, I found alignment settings of B3:B12 is different. I have used your data in your current output file (I exported it to fill the data table to be used in importing data) as a template file. I have added a new sheet to your output file to mimic the formatting of your manual file (you provided) for your reference. I have added some lines of code to your code segment to accomplish the task. See the complete sample code for your reference:
e.g. Sample code:
Workbook workbook = new Workbook("e:\\test2\\Aspose_cells_output_file.xls");
Worksheet sheet1 = workbook.Worksheets[0];
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[workbook.Worksheets.Add()];
ExportTableOptions options = new ExportTableOptions();
options.ExportColumnName = true;
//Store data of the sheet to a datatable.
DataTable dt = sheet1.Cells.ExportDataTable(1, 0, 11, 2,options);
// Setting the name of the newly added worksheet
worksheet.Name = "Top_10_Errors1";
ImportTableOptions importOptions = new ImportTableOptions();
importOptions.IsFieldNameShown = true;
worksheet.Cells.ImportData(dt, 1,0, importOptions);
// Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
// Merge some Cells (C6:E7) into a single C6 Cell.
cells.Merge(0, 0, 1, 2);
// Input data into C6 Cell.
worksheet.Cells["A1"].PutValue("Top 10 Errors-EXCEL");
// Create a Style object to fetch the Style of C6 Cell.
Style style = worksheet.Cells["A1"].GetStyle();
// Create a Font object
Aspose.Cells.Font font = style.Font;
// Set the name.
font.Name = "Times New Roman";
// Set the font size.
font.Size = 14;
// Set the font color
font.Color = System.Drawing.Color.Black;
// Bold the text
font.IsBold = true;
// Set the backgrond color of C6 Cell to Red
// style.ForegroundColor = System.Drawing.Color.Red;
//style.Pattern = BackgroundType.Solid;
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
//style.SetBorder(BorderType.BottomBorder, CellBorderType.DashDotDot, System.Drawing.Color.Black);
// Apply the Style to C6 Cell.
cells["A1"].SetStyle(style);
//Cells cells1 = worksheet.Cells;
Style style1 = cells["A2"].GetStyle();
// Create a Font object
Aspose.Cells.Font font1 = style1.Font;
// Set the name.
font1.Name = "Times New Roman";
// Set the font size.
font1.Size = 14;
// Set the font color
font1.Color = System.Drawing.Color.Black;
// Bold the text
font1.IsBold = true;
style1.Pattern = BackgroundType.Solid;
//style1.BackgroundColor = System.Drawing.Color.LightBlue;
style1.ForegroundColor = System.Drawing.Color.LightBlue;
Style style2 = cells["B2"].GetStyle();
// Create a Font object
Aspose.Cells.Font font2 = style2.Font;
// Set the name.
font2.Name = "Times New Roman";
// Set the font size.
font2.Size = 14;
// Set the font color
font2.Color = System.Drawing.Color.Black;
// Bold the text
font2.IsBold = true;
style2.Pattern = BackgroundType.Solid;
// style2.BackgroundColor = System.Drawing.Color.LightBlue;
style2.ForegroundColor = System.Drawing.Color.LightBlue;
cells["A2"].SetStyle(style1);
cells["B2"].SetStyle(style2);
//Cells cells2 = workbook.Worksheets[0].Cells;
Range range1 = cells.CreateRange("A1", "B12");
Style stl = workbook.CreateStyle();
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
StyleFlag flg = new StyleFlag();
flg.Borders = true;
range1.ApplyStyle(stl, flg);
//Set data alignment of B2:B12 range to certer aligned
Range rng = cells.CreateRange("B3:B12");
Style st = workbook.CreateStyle();
st.VerticalAlignment = TextAlignmentType.Center;
st.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag sf = new StyleFlag();
sf.Alignments = true;
rng.ApplyStyle(st, sf);
//Set row heights of the A2:B12 to 15.75
Range trng = cells.CreateRange("A3:B12");
trng.RowHeight = 15.75;
//Set the font of the range to "Calibri 12"
Style st1 = workbook.CreateStyle();
st1.Font.Name = "Calibri";
st1.Font.Size = 12;
StyleFlag sf1 = new StyleFlag();
sf1.FontName = true;
sf1.FontSize = true;
trng.ApplyStyle(st1, sf1);
worksheet.Cells["A14"].PutValue("Downloaded Date and Time:");
worksheet.Cells["B14"].PutValue(Convert.ToDateTime(DateTime.Now).ToString("dd-MMM-yyyy@HH.mm.ss"));
worksheet.Cells["A16"].PutValue("Downloaded By:");
worksheet.Cells["B16"].PutValue("Nagesh Kotha");
// Saving the Excel file
worksheet.AutoFitColumns();
workbook.Save("e:\\test2\\out1.xlsx");
Please also find attached the output file by using the above sample code. Please check the sheet “Top_10_Errors1”.
Let us know if you still have any issue or confusion. files1.zip (10.0 KB)