CODEWITHSUNDEEP
c#excelloops
Wigle
Wigle

Reputation: 106

c# excel format each row in rtf

I am working on an application that has to convert all the valuables from each row to an rtf document. The excel file that i am using has more than 10.000 rows but only 5 columns. so far i can read the file usingValue2.ToString method. what i would like to do is get the 5 values from each row, provide them with a header

firsttext= the text of cel a1

secondtext= the text of cel b1

thirdtext= the text of cel c1

fourthtext= the text of cel d1

fifttext= the text of cel e1

and do the same for cel a2, b2 etc etc. My code so far is

           //Create COM Objects. 
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\aaa.xlsx");
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;  
        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

        //iterate over the rows and columns and print to the console as it appears in the file
        //excel is not zero based!!
        for (int i = 1; i <= rowCount; i++)
        {
            for (int j = 1; j <= colCount; j++)
            {
                //new line
                if (j == 1)
                    Console.WriteLine("\r\n");

                //write the value to the console
                if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                    Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t", "\r\n");
      
            }
           
           // Console.ReadLine(); for testing purposes         
        }

        //cleanup

        GC.Collect();
        GC.WaitForPendingFinalizers();

        //  rule of thumb for releasing com objects:
        //  never use two dots, all COM objects must be referenced and released individually
        //  ex: [somthing].[something].[something] is bad

        //release com objects to fully kill excel process from running in the background
        Marshal.ReleaseComObject(xlRange);
        Marshal.ReleaseComObject(xlWorksheet);

        //close and release
        xlWorkbook.Close();
        Marshal.ReleaseComObject(xlWorkbook);

        //quit and release
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);
        Console.ReadLine();

any help would be appreciated

Upvotes: 0

Views: 439

Answers (1)

faceman
faceman

Reputation: 1328

I'm still not really sure what your problem is?

Are you just asking on how to add the headers?

//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        //new line
        if (j == 1)
            Console.WriteLine("\r\n");

        //write the value to the console
        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
           Console.Write("{0}= ", GetHeaderText(j));
           Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t", "\r\n");
    }
   // Console.ReadLine(); for testing purposes         
}

Get the header text via some helper method:

private string GetHeaderText(int colId)
{
    switch (colId)
    {
        case 1:
           return "firsttext";
        case 2:
           return "secondtext";
        case 3:
           return "thirdtext";
        case 4:
           return "fourthtext";
        case 5:
           return "fithtext";
        default:
           return "header not defined";
    }
}

Upvotes: 1

Related Questions