Excel formatting issue before downloading the created excel using aspose.cells

Aspose.Cells Product Family
, ,
1

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);

please refer uploaded the screenshot,actually i need like that
final_output_like_this.PNG (4.1 KB)

2

@knr,

Please zip the following two Excel files and attach the zipped archive containing these files:

  1. Your current output Excel file via Aspose.Cells APIs.
  2. Your expected file with your desired formatting, you can create the file in MS Excel manually.

After getting your files, we will evaluate your issue and help you to fix your issue (in code) soon.

3

Please find the attached required files:
Files.zip (8.7 KB)

Please provide the code.

4

@knr,

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)

5

Thank you very much to provided code

6

@knr,

Good to know that the suggested code works for your needs. In the event of further queries or issue, feel free to write us back.