在Excel单元格中显示列表 [英] display a list into excel cells

问题描述

我想用c＃在excel单元格中显示一个列表。问题是只有列表的最后一项显示在excel表格中。任何帮助？这是我的代码：

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

推荐答案

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

我做了一些更改，现在可以使用了

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;

        }

我认为以下代码存在问题。

I think problem with the following code.

int i = 0;

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

每次用新值替换前一个值。所以最后你将得到最后一个价值。更改循环中的代码。确保单元格值不应再次重复。你需要将i值增加2.我不确定你为什么要在两个单元格中复制相同的值。

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.

这篇关于在Excel单元格中显示列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！