none
How to write in to Excel file from array list items RRS feed

  • Question

  • Hi All,

    How to open and write in Excel file from many array lists. its should be write in excel by order like one by one continuously. refer below snap for more info.

    Thanks to any one can assist me...

                 ArrayList arrai1 = new ArrayList();
                ArrayList arrai2 = new ArrayList();
                ArrayList arrai3 = new ArrayList();
                arrai1.Add("Anilkumar");
                arrai1.Add("Anish");
                arrai1.Add("Anandh");
                arrai1.Add("Anantha kumar");
                arrai1.Add("Aadhi");
                arrai1.Add("Aadhikesavan");
                arrai1.Add("Abiragam");
    
                arrai2.Add("Renila");
                arrai2.Add("Rosi");
                arrai2.Add("Renne");
                arrai2.Add("Ranikku");
                arrai2.Add("Rajaji");
                arrai2.Add("Ramani");
    
                arrai3.Add("Baskar");
                arrai3.Add("vincent");
                arrai3.Add("Shankar");
                arrai3.Add("Gogul");
                arrai3.Add("Gobu");
                arrai3.Add("gopaal");
    
                string ExcelFile = "D:\\Sample.xlsx";
    
                for (int i = 0; i > arrai1.Count; i++)
                { 
                
                
                
                }
                for (int j = 0; j > arrai2.Count; j++)
                {
    
    
    
                }
                for (int k = 0; k > arrai3.Count; k++)
                {
    
    
    
                }

    • Moved by CoolDadTx Friday, December 21, 2018 3:12 PM Office related
    Thursday, December 20, 2018 4:38 PM

Answers

  • Hi Anilkumar.Renila,

    Thank you for posting here.

    For your question, if you want to add arrayList to Excel using MS Excel, you could try the code below.

    First, add reference of Microsoft.Office.Interop.Excel dll.

    And then use it.

    using Excel = Microsoft.Office.Interop.Excel;
    ArrayList arrayList = new ArrayList();
                ArrayList arrai1 = new ArrayList();
                ArrayList arrai2 = new ArrayList();
                ArrayList arrai3 = new ArrayList();
                arrai1.Add("Anilkumar");
                arrai1.Add("Anish");
                arrai1.Add("Anandh");
                arrai1.Add("Anantha kumar");
                arrai1.Add("Aadhi");
                arrai1.Add("Aadhikesavan");
                arrai1.Add("Abiragam");
    
                arrai2.Add("Renila");
                arrai2.Add("Rosi");
                arrai2.Add("Renne");
                arrai2.Add("Ranikku");
                arrai2.Add("Rajaji");
                arrai2.Add("Ramani");
    
                arrai3.Add("Baskar");
                arrai3.Add("vincent");
                arrai3.Add("Shankar");
                arrai3.Add("Gogul");
                arrai3.Add("Gobu");
                arrai3.Add("gopaal");
    
                arrayList.AddRange(arrai1);
                arrayList.AddRange(arrai2);
                arrayList.AddRange(arrai3);
    
    
                Application excel = new Application
                {
                    //if you want to make excel visible           
                    Visible = true
                };
    
                //create a blank workbook
                var workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    
                string workbookPath = "list.xls";
                var sheet = (Worksheet)workbook.Sheets[1]; //indexing starts from 1
    
                //do something usefull: you select now an individual cell
                var range = sheet.get_Range("A1", "A1");
    
    
                //now the list
                string cellName;
                int counter = 1;
                foreach (var item in arrayList)
                {
                    cellName = "A" + counter.ToString();
                    range = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.ToString();
                    ++counter;
                }

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 21, 2018 8:29 AM

All replies

  • Hello,

    Look at SpreadSheetLight

    It's installed via NuGet and is totally free, does not require Excel to be installed.

    Basics, open the Excel file, specify the sheetname while opening. Once open get statistics to get the last row then iterate your array items to add items.

    Here is a sample 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 20, 2018 4:42 PM
  • Hi,

    In my company do not allow to install SpreadSheetLight. Because the application will use all users and the same time need to be install SpreadSheetLight  for all users. So, do you give me any sample for MS office Excel will be help full for me.


    Friday, December 21, 2018 4:22 AM
  • Hi Anilkumar.Renila,

    Thank you for posting here.

    For your question, if you want to add arrayList to Excel using MS Excel, you could try the code below.

    First, add reference of Microsoft.Office.Interop.Excel dll.

    And then use it.

    using Excel = Microsoft.Office.Interop.Excel;
    ArrayList arrayList = new ArrayList();
                ArrayList arrai1 = new ArrayList();
                ArrayList arrai2 = new ArrayList();
                ArrayList arrai3 = new ArrayList();
                arrai1.Add("Anilkumar");
                arrai1.Add("Anish");
                arrai1.Add("Anandh");
                arrai1.Add("Anantha kumar");
                arrai1.Add("Aadhi");
                arrai1.Add("Aadhikesavan");
                arrai1.Add("Abiragam");
    
                arrai2.Add("Renila");
                arrai2.Add("Rosi");
                arrai2.Add("Renne");
                arrai2.Add("Ranikku");
                arrai2.Add("Rajaji");
                arrai2.Add("Ramani");
    
                arrai3.Add("Baskar");
                arrai3.Add("vincent");
                arrai3.Add("Shankar");
                arrai3.Add("Gogul");
                arrai3.Add("Gobu");
                arrai3.Add("gopaal");
    
                arrayList.AddRange(arrai1);
                arrayList.AddRange(arrai2);
                arrayList.AddRange(arrai3);
    
    
                Application excel = new Application
                {
                    //if you want to make excel visible           
                    Visible = true
                };
    
                //create a blank workbook
                var workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    
                string workbookPath = "list.xls";
                var sheet = (Worksheet)workbook.Sheets[1]; //indexing starts from 1
    
                //do something usefull: you select now an individual cell
                var range = sheet.get_Range("A1", "A1");
    
    
                //now the list
                string cellName;
                int counter = 1;
                foreach (var item in arrayList)
                {
                    cellName = "A" + counter.ToString();
                    range = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.ToString();
                    ++counter;
                }

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 21, 2018 8:29 AM
  • Hi,

    In my company do not allow to install SpreadSheetLight. Because the application will use all users and the same time need to be install SpreadSheetLight  for all users. So, do you give me any sample for MS office Excel will be help full for me.


    Hello,

    You don't install SpreadSheetLight. When you install your application on a user's computer the DLL for SpreadSheetLight simply goes in the same folder as the executable of your application thus it does not need to be installed before installing your application. 

    I don't have a solution using MS-Excel as through the years have found way too many problems ranging from memory leaks when done wrong to versions differences on user's computers even when Excel is supposedly the same on each computer. 

    SpreadSheetLight is also faster than Excel automation. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 21, 2018 11:36 AM
  • Hi, you could write data from array list to Excel sheet easily using Free Spire.XLS. Here is the code for your reference.

    using Spire.Xls;
    using System.Collections;
    
    namespace test
    {
        class Program
        {
            static void Main(string[] args)
            {
                ArrayList arrai1 = new ArrayList();
                ArrayList arrai2 = new ArrayList();
                ArrayList arrai3 = new ArrayList();
                arrai1.Add("Anilkumar");
                arrai1.Add("Anish");
                arrai1.Add("Anandh");
                arrai1.Add("Anantha kumar");
                arrai1.Add("Aadhi");
                arrai1.Add("Aadhikesavan");
                arrai1.Add("Abiragam");
    
                arrai2.Add("Renila");
                arrai2.Add("Rosi");
                arrai2.Add("Renne");
                arrai2.Add("Ranikku");
                arrai2.Add("Rajaji");
                arrai2.Add("Ramani");
    
                arrai3.Add("Baskar");
                arrai3.Add("vincent");
                arrai3.Add("Shankar");
                arrai3.Add("Gogul");
                arrai3.Add("Gobu");
                arrai3.Add("gopaal");
    
                //create a new arraylist
                ArrayList newList = new ArrayList();
                newList.AddRange(arrai1);
                newList.AddRange(arrai2);
                newList.AddRange(arrai3);
    
                //create a workbook
                Workbook wb = new Workbook();
    
                //get the first worksheet
                Worksheet sheet = wb.Worksheets[0];
    
                //select the start row and column to insert arrar list to worksheet
                sheet.InsertArrayList(newList, 1, 1, true);
    
                //save to file
                wb.SaveToFile("output.xlsx", ExcelVersion.Version2016);
                System.Diagnostics.Process.Start("output.xlsx");
            }
        }
    }


    Monday, December 24, 2018 2:37 AM