CODEWITHSUNDEEP
c#excelinterop
vality
vality

Reputation: 5

Highlighting cells/making them bold in Excel

In a group project I have to write a table to an Excel sheet. This I have done but I need to highlight the table area where the data goes, as can be seen on these pictures:

https://gyazo.com/51c57897d9a1ce8df000d6ff0f18de20

https://gyazo.com/bcc879cd7d1c5f12ccb853490dca22f2

The first picture shows how it should look without data, and the second picture shows my current file with the data loaded.

Is it possible to highlight like it is seen in the first picture where the data is supposed to be?

I have not been able to find any sources dealing with this online.

Here is the code that creates the Excel sheet. Take note that I have not looked into formatting the code properly, but it should be readable:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ExcelLibrary.SpreadSheet;
using System.Data;
using System.IO;
using System.Windows;

using Excel = Microsoft.Office.Interop.Excel;

namespace ProjectXstaal
{
    class Print
    {

        public void LoadToExcel(DataTable dt)
        {
        string filetest = "filetest.xlsx";
        double allHours = 0;
        double overtimeHours = 0;

        if (File.Exists(filetest))
        {
            File.Delete(filetest);
        }

        Excel.Application oApp;
        Excel.Worksheet oSheet;
        Excel.Workbook oBook;

        oApp = new Excel.Application();
        oBook = oApp.Workbooks.Add();
        Excel.Worksheet ws = (Excel.Worksheet)oApp.ActiveSheet;


        // Calculates the total hours worked and total overtime hours. not important for the question
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string overtimeTemp = dt.Rows[i][3].ToString();
            string temp = dt.Rows[i][2].ToString();

            double temp2 = Double.Parse(temp);
            double temp3 = Double.Parse(overtimeTemp);
            overtimeHours += temp3;
            allHours += temp2 + temp3;
            if (i >= 40)
            {
                break;
            }
        }
        // some formatting of the page
        ws.Cells[3, 1] = "Tidsperiode:";
        ws.Cells[3, 5] = "NAVN";
        ws.Cells[3, 6] = dt.Rows[1][7].ToString();
        ws.Cells[1, 5] = DateTime.Now.Year.ToString();
        ws.Cells[1, 3] = "ARBEJDSSEDDEL";
        ws.Cells[40, 1] = "SAMMENLAGT FOR UGEN";
        ws.Cells[40, 5] = allHours;
        ws.Cells[41, 1] = "UGENS TIMER FORDELES PÅ";
        ws.Cells[43, 5] = overtimeHours;
        ws.Cells[42, 5] = allHours - overtimeHours; 
        ws.Cells[42, 4] = "NORMALTIMER";
        ws.Cells[43, 4] = "OVERARBEJDE";

        ws.Cells[6, 1] = dt.Columns[1].ToString();
        ws.Cells[6, 2] = dt.Columns[5].ToString();
        ws.Cells[6, 3] = dt.Columns[0].ToString();
        ws.Cells[6, 4] = dt.Columns[4].ToString();
        ws.Cells[6, 5] = dt.Columns[2].ToString();
        ws.Cells[6, 6] = dt.Columns[3].ToString();
        ws.Cells[6, 7] = dt.Columns[6].ToString();

        //prints the datatable to the excel sheet and stops when it cant fit anymore information
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            ws.Cells[i + 7, 1] = dt.Rows[i][1].ToString();
            ws.Cells[i + 7, 2] = dt.Rows[i][5].ToString();
            ws.Cells[i + 7, 3] = dt.Rows[i][0].ToString();
            ws.Cells[i + 7, 4] = dt.Rows[i][4].ToString();
            ws.Cells[i + 7, 5] = dt.Rows[i][2].ToString();
            ws.Cells[i + 7, 6] = dt.Rows[i][3].ToString();
            ws.Cells[i + 7, 7] = dt.Rows[i][6].ToString();

            ws.Cells[i + 7, 1].Bold = true;

            if (i >= 32)
            {
                break;
            }
        }
        // Sets column width of the data.
        ws.Range["A6"].ColumnWidth = 11;
        ws.Range["B6"].ColumnWidth = 10;
        ws.Range["C6"].ColumnWidth = 6.7;
        ws.Range["D6"].ColumnWidth = 11;
        ws.Range["E6"].ColumnWidth = 11;
        ws.Range["F6"].ColumnWidth = 15;
        ws.Range["G6"].ColumnWidth = 15;

        oBook.SaveAs(filetest);
        oBook.Close();
        oApp.Quit();
        MessageBox.Show("Det virker måske");
    }


   }
}

Upvotes: 0

Views: 1462

Answers (1)

Tamwe
Tamwe

Reputation: 359

If you now the range which should be bold, use:

Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A1:A6","G1:G6");
range.Font.Bold = true;

In this example, the first 6 rows and the first 7 seven columns get bold, assuming the cell in the top-left corner is "A1".

I hope this helps!

Upvotes: 1

Related Questions