create excel without repeating Key columns

[xel_wise]

I have the following model with the key columns Id and Name. Bear in mind this is just an example. Real world example will have hundreds of rows

I would like to create an excel sheet where you only print these key columns once.

Code: Select all

Id   Name      Date                     Location
1   John Doe           01/01/2022               London
1   John Doe           02/01/2022               Paris
1   John Doe           03/01/2022                Milan

I want to achieve this:

Code: Select all

Id   Name        Date                           Location
1   John Doe         01/01/2022            London
                         02/01/2022               Paris
                         03/01/2022                Milan

Is this possible to achieve?

[Abel.He]

Hi,

Thanks for your inquiry.
According to the messages you provided, I simulated a excel file to test and attached it in attachment. For your requirement, you can refer to the following code:

Code: Select all

 // Create a new Workbook object
            Workbook workbook = new Workbook();

            // Load the Excel file from the specified path
            workbook.LoadFromFile(@"..\..\data\testData.xlsx");

            // Get the first worksheet in the workbook
            Worksheet sheet = workbook.Worksheets[0];

            // Iterate through each column in the worksheet
            for (int i = 1; i <= sheet.Columns.Length; i++)
            {
                int col = i;

                // Get the value of the cell at row 1 and column i
                string cellValue = sheet.Range[1, col].Value;

                // Check if the cell value is "id" or "name"
                if (cellValue == "id" || cellValue == "name")
                {
                    string target = "";
                    // Iterate through each row in the worksheet
                    for (int j = 1; j <= sheet.Rows.Length; j++)
                    {
                        Console.WriteLine(j);
                        // Check if the cell value at row j and column i is equal to the target value
                        if (sheet.Range[j, col].Value == target)
                        {
                            // If it is, set the cell value to an empty string
                            sheet.Range[j, col].Value = "";
                        }
                        else
                        {
                            // If it's not, update the target value with the current cell value
                            target = sheet.Range[j, col].Value;
                        }
                    }
                }
            }

            // Save the modified workbook to a new file
            workbook.SaveToFile(@"../../output/remove.xlsx", FileFormat.Version2013);

Sincerely
Abel
E-iceblue support team