Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.0 C# C#4.0
I am using windows Forms using C# Language,
 
I have listview, it contains some items when i click the save button the listview items will saves to Excel File. Below is my code of save listview details to Excel.
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
            xla.Visible = true;
           
            Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
            
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
 
            int i = 1;
            int j = 1;
           
            foreach (ListViewItem comp in listView_playlist.Items)
            {
                ws.Cells[i, j] = comp.Text.ToString();         
                //MessageBox.Show(comp.Text.ToString());

                foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
                {
                    ws.Cells[i, j] = drv.Text.ToString();
                    j++;
                }
                j = 1;
                i++;
            }  

My problem :
Here what my problem is, When i Click the Save button the listview details comes on the Excel page, The Excel page is open stage. I need to click save button in the excel page. Then only it will saved.
 
But I need to save default when i click the Save button. Just we need to enter the file name of save file dialogue box, it will save our local host. without visible the Excel sheet. How can i do this.
 
Regards
Vasanthakumar
Posted 18-Sep-12 0:43am
Edited 18-Sep-12 1:45am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Saving can be done by using the SaveAs method of the WorkBook class. Making Excel invisible can be done by setting the Application Visible property to false.
 
The SaveAs method has many parameters. Because they are optional you have not the supply them all. In the code below I have only supplied the filename (1st parameter) and the fileformat (2nd parameter). The example assume you have Excel 2007 (using XlFileFormat.xlExcel12 format).
 
Also take notice of the xla.Quit(); xla = null; statements. Don't forget these. Otherwise Excel will keep running in the background.
 
        private string GetFileName()
        {
            string filename = string.Empty;
            SaveFileDialog dialog = new SaveFileDialog();
 
            dialog.InitialDirectory = @"C:\"; // change this to your own default location. Save to the root of C:\ is not a good practice.
            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                filename = dialog.FileName;
            }
 
            return filename;
        }
 
        private void SaveWorkbook(Microsoft.Office.Interop.Excel.Workbook wb, string filename)
        {
            //Remove the extension from the filename. The SaveAs will add the approriate extension it self (at least it does with Excel 2007)
            string localfilename = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(filename), System.IO.Path.GetFileNameWithoutExtension(filename));
 
            // If you are sure to override the file 
            if (System.IO.File.Exists(localfilename))
                System.IO.File.Delete(localfilename);
            wb.SaveAs(localfilename, XlFileFormat.xlExcel12); //generates a xlsb file (Excel 2007)
            //wb.SaveAs(myfilename, XlFileFormat.xlExcel9795); //generates a excel 97 xls file
        }
 
        private void SaveButton_Click(object sender, EventArgs e)
        {
            string filename = GetFileName();
 
            //Exit code if no file was selected
            if (string.IsNullOrEmpty(filename))
                return;
 
            Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
            xla.Visible = false; //Change to false so Excel isn't visible

            Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
 
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;
 
            int i = 1;
            int j = 1;
 
            foreach (ListViewItem comp in listView_playlist.Items)
            {
                ws.Cells[i, j] = comp.Text.ToString();
 
                //MessageBox.Show(comp.Text.ToString());
                foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
                {
                    ws.Cells[i, j] = drv.Text.ToString();
                    j++;
                }
                j = 1;
                i++;
            }
 
            SaveWorkbook(wb, filename);
 
            //Close Excel (don't forget this otherwise Excel will keep running in memory)
            xla.Quit();
            xla = null;
 
            MessageBox.Show(string.Format("Data is save to {0}", filename));
        }
 
 
(improvement)
CSV has the advantage that you could use it on any system. To help you I have made some code to show how to save and load from a CSV file. The examples are quite basic and make some assumption. It will give you a start. If you want to learn more about CSV file (for example using the first line to store the field names, or making it more robust) you could search the CodeProject articles for CSV. There are som could articles here. Both basic and advanced.
 
To save your playlist to a CSV you could use the following code. To make the example work you need
using System.IO;
using System.Text.RegularExpressions;
 
        private void NewSaveButton_Click(object sender, EventArgs e)
        {
            string filename = GetFileName();
 
            //Exit code if no file was selected
            if (string.IsNullOrEmpty(filename))
                return;
 
            //make sure there is an extension. Because the GetFileName method doesn't do this 
            if (Path.GetExtension(filename).Length == 0)
                filename = filename + ".csv";
 
            WriteListViewToCsv(listView_playlist, filename, '|');
        }
 
        private void WriteListViewToCsv(ListView listview, string csvFilename, char seperator)
        {
            int i = 1;
            int j = 1;
 
            // The using clause will dispose the StreamWriter after use. Because a streamwriter is unmanaged code you should always dispose it
            using (StreamWriter sw = new StreamWriter(csvFilename, false)) 
            {
 
                foreach (ListViewItem comp in listview.Items)
                {
                    //sw.Write(comp.Text.ToString()); Don't need to save the item itself as the first subitem is the same as the item.
                    bool firstItem = true;
                    foreach (ListViewItem.ListViewSubItem drv in comp.SubItems)
                    {
                        if (firstItem)
                            firstItem = false;
                        else
                            sw.Write(seperator);
                        sw.Write(drv.Text.ToString());
                        j++;
                    }
 
                    sw.WriteLine(); // Write an enter to the list
                    j = 1;
                    i++;
                }
 
                sw.Flush(); //Make sure 
                sw.Close();
            }
        }
 
To read the saved playlist from the CSV file you could use the follow code. It uses regex to split the line.
        private void NewLoad_Click(object sender, EventArgs e)
        {
            string filename = OpenFileName();
 
            if (string.IsNullOrEmpty(filename))
                return;
 
            ImportCSV(listView_playlist, filename, '|');
        }
 
        private string OpenFileName()
        {
            string filename = string.Empty;
            OpenFileDialog dialog = new OpenFileDialog();
 
            dialog.InitialDirectory = @"C:\"; // change this to your own default location. Save to the root of C:\ is not a good practice.
            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                filename = dialog.FileName;
            }
 
            return filename;
        }
 
        private void ImportCSV(ListView listview, string filename, char seperator)
        {
            using (StreamReader sr = new StreamReader(filename))
            {
                while (!sr.EndOfStream)
                {
                    string line = sr.ReadLine();
                    // split the line into seperate values
                    string[] values = Regex.Split(line, @"\|");
 
                    ListViewItem item = listview.Items.Add(values[0]);
                    if (values.Count() > 1)
                    {
                        for (int c = 1; c < values.Count(); c ++)
                        {
                            item.SubItems.Add(values[c]);
                        }
                    }
                }
 
                sr.Close();
            }
        }
 
  Permalink  
v4
Comments
vasanthkumarmk at 18-Sep-12 7:58am
   
Thank you sir
Martijn Kok at 18-Sep-12 8:07am
   
You're welcome
vasanthkumarmk at 19-Sep-12 1:56am
   
How can use this coding for without have microsoft office in my system.
Martijn Kok at 19-Sep-12 2:17am
   
Saving to Excel without a reference to Excel (or without Excel installed on the computer you want to install your software) is not possible. Maybe there are commercial products which can save to an excel sheet without Excel installed.
 
You can look at alternatives if you ask yourself why you want to use Excel. If the answer is "I need to store my playlist somewhere", then there are other solutions. You could store it to a CSV file (text file a seperation character) or a XML file you define. These are free and simple to use. Another alternative is looking at using a database. Databases have advantages, but you need a database some where. The installed applications need to connect to the database. When you have more data and more complex data, databases are a very good alternative.
vasanthkumarmk at 20-Sep-12 1:49am
   
gud, How can I store CSV file and retrieve from CSV file.
Martijn Kok at 20-Sep-12 4:09am
   
I have added an example to store to CSV. The retrievement code will follow shortly
Espen Harlinn at 20-Sep-12 4:31am
   
NIce reply :-D

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 625
1 OriginalGriff 285
2 George Jonsson 225
3 CPallini 210
4 Fredrik Bornander 150
0 OriginalGriff 5,835
1 Sergey Alexandrovich Kryukov 5,263
2 CPallini 4,750
3 George Jonsson 3,227
4 Gihan Liyanage 2,487


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 20 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100