Click here to Skip to main content
15,868,420 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using Windows Application with C# Code.

I have ListView item contains some of the files. The listview files will store the Excel Sheet using below code.
C#
private void btn_PLLoad_Click_2(object sender, EventArgs e)
        {

            StringBuilder sb = new StringBuilder();

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

            }

            

        }

It will directly stores the listview datas to Excel sheet.

Then I Close my Application. When the click the button using open file dialogue to open that Excel Sheet (Which listview item data saved Excel Sheet). I need to retrieve again saved Excel Sheet datas to my listview.

Regards
Vasanthakumar
Posted
Updated 16-Sep-12 20:03pm
v2

Basicly reading an Excel sheet is the reverse from writing it. You could use an OpenFileDialog to locate the Excel sheet, and then call a function like below.

C#
private void ImportExcel(string filename)
{
    Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
    xla.Visible = true;

    // Load the workbook
    Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Open(filename);

    // Get the worksheet
    Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets[1];

    // Retrieve number of columns, number of rows
    int numberOfColumns = GetNumberOfColumns(ws);
    int numberOfRows = GetNumberOfRows(ws);

    for (int r = 1; r <= numberOfRows; r++)
    {
        // Put your own code here to add the listviewitem
        ListViewItem item = listView_playlist.Items.Add(ws.Cells[r, 1].Value);

        for (int c = 1; c <= numberOfColumns; c++)
        {
            // Put your own code here to add the subitems
            item.SubItems.Add(ws.Cells[r, c].Value.ToString());
        }
    }

    // Quit Excel
    xla.Quit();
    xla = null;
}

/// <summary>
/// Gets the number of rows by searching the first null row
/// </summary>
/// <param name="ws">worksheet to search</param>
/// <returns>number of rows</returns>
/// <remarks>there probably is a better way to do this</remarks>
private int GetNumberOfRows(Microsoft.Office.Interop.Excel.Worksheet ws)
{
    int numberOfRows = 1;
    while (ws.Cells[1, numberOfRows].Value != null)
    {
        numberOfRows += 1;
    }
    numberOfRows -= 1; // substract 1 to get the last filled column

    return numberOfRows;
}

/// <summary>
/// Gets the number of columns by searching the first null row
/// </summary>
/// <param name="ws">worksheet to search</param>
/// <returns>number of columns</returns>
/// <remarks>there probably is a better way to do this</remarks>
private int GetNumberOfColumns(Microsoft.Office.Interop.Excel.Worksheet ws)
{
    int numberOfColumns = 1;
    while (ws.Cells[1, numberOfColumns].Value != null)
    {
        numberOfColumns += 1;
    }
    numberOfColumns -= 1; // substract 1 to get the last filled column

    return numberOfColumns;
}


The code Loads the Excel workbook. I assume the data is on the first worksheet. Next the number of columns and rows are determined. Loop them and add items to your ListView. ListView are not my strongest point (I didn't manage to show the subitems), so you might need to change a little in the code to add the data to the ListView.

By the way when I studied your code I might have discovered a bug. First you write your item to ws.Cells[i, j] and when you loop the subitems you write the first subitem to the same cell (without j is being incremented). If the item and the first subitem contain the same data (that's what I assumed in the example), then there is no problem otherwise you might loose data.
 
Share this answer
 
This is the code for retrieve items from Excel to listview :

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtbx_Excel.Text);//
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

for (int i = 0; i <= rowCount; i++)
{

//for (int j = 1; j <= colCount; j++)
//{


try
{


lvitem = new ListViewItem();
lvitem.Text = xlRange.Cells[i, 1].Value2.ToString();
lvitem.SubItems.Add(xlRange.Cells[i, 2].Value2.ToString());
lvitem.SubItems.Add(xlRange.Cells[i, 3].Value2.ToString());
listView_playlist.Items.Add(lvitem);

}
catch (Exception ex)
{ //MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
}
 
Share this answer
 
 
Share this answer
 
Comments
vasanthkumarmk 17-Sep-12 2:13am    
ok
vasanthkumarmk 17-Sep-12 2:17am    
I need the data will stores from EXCEL to LISTVIEW without any backend intraction
Pandvi 17-Sep-12 2:25am    
ASP.NET service? Or Winform? You wish directly import Excel to listview?
vasanthkumarmk 17-Sep-12 3:11am    
WinForm

Pandvi 17-Sep-12 2:29am    
Here is one solution that may help: http://www.vbforums.com/showthread.php?312310-Importing-a-Excel-sheet-to-listview. but It seems to be in VB6 code. please see.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900