display a list into excel cells

Question

0.00/5 (No votes)

See more:

I want to display a list into excel cells with c#.The problem is that only the last item of the list is shown into the excel sheet. any help? here is my code:

Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;
           
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
           // xlWorkSheet.Cells[1, 1] = "Nb de fonction";
            //xlWorkSheet.Cells[1, 3] = "list de fonction supprimé";
            xlWorkSheet.Cells[1, 8] = "list de fonction ajouté";
            int i = 0;
            
            foreach (var item in listfunc1)
            {
                xlWorkSheet.Cells[3+i, 8] = item;
                xlWorkSheet.Cells[4+i, 8] = item;
                i++;
            }
            xlWorkSheet.Cells[1, 12] = "list de commentaire supprimé";
            xlWorkSheet.Cells[1, 16] = "list de commentaire ajouté";
            xlWorkSheet.Cells[1, 19] = "list d'entête supprimé";
            
            xlWorkSheet.Cells[1, 16] = "list d'entête ajouté";
           
            xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file c:\\Documents\\csharp-Excel.xls");

Posted 30-Jul-14 16:32pm

Member 10902944

Add a Solution

3 solutions

Solution 3

I've made some changes and it works now

int row = 3;
        int col = 8;
        foreach (var item in listfunc1)
        {
            xlWorkSheet.Cells[row, col] = item;
            row++;
            if (listfunc1.IndexOf(item) == listfunc1.Count - 1)
                col = 1;

        }

Posted 31-Jul-14 4:25am

Member 10902944

Solution 2

I think problem with the following code.

C#

int i = 0;

            foreach (var item in listfunc1)
            {
                xlWorkSheet.Cells[3+i, 8] = item;
                xlWorkSheet.Cells[4+i, 8] = item;
                i++;
            }

Every time you are replacing the previous value with the new value. So finally you will be getting the last value. Change the code within the loop. Make sure that the cell value shouldn't repeat again. You need to increment the i value by 2. And i'm not sure why you are copying the same value in two cells.

Posted 30-Jul-14 19:04pm

Kumarbs

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gihan Liyanage · Accepted Answer · 2014-07-30T19:00:00

C#

private void CreateXLS(string data)
   {
       Excel.Application xlApp;
       Excel.Workbook xlWorkBook;
       Excel.Worksheet xlWorkSheet;
       object misValue = System.Reflection.Missing.Value;

       string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\data.xls";

       xlApp = new Excel.Application();
       xlWorkBook = xlApp.Workbooks.Add(misValue);

       xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

       // List of lists containing each line split by part
       List<list><string>> dataList = new List<list><string>>();

       List<string> valuesA = new List<string>();

       // Building the list
       foreach (string line in data.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
       {
           List<string> partsLine = new List<string>();
           partsLine.AddRange(line.Split('\t'));
           dataList.Add(partsLine);
       }

       int row = 1;
       int col = 1;

       foreach (List<string> list in dataList)
       {
           foreach (string str in list)
           {
               xlWorkSheet.Cells[row, col] = str;

               if (list.IndexOf(str) == list.Count - 1)
                   col = 1;
               else
                   col++;
           }
           row++;
       }


       xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
       xlWorkBook.Close(true, misValue, misValue);
       xlApp.Quit();

       releaseObject(xlWorkSheet);
       releaseObject(xlWorkBook);
       releaseObject(xlApp);

       textBoxData.AppendText("\nData saved to .xls file (path : " + path + ")");