从Excel Sheet检索数据到Listview [英] Retrieve datas from Excel Sheet to Listview
问题描述
我正在使用带有C＃代码的Windows应用程序。
我有ListView项目包含一些文件。 listview文件将使用下面的代码存储Excel Sheet。
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.
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++;
}
}
它将直接将listview数据存储到Excel表。
然后我关闭我的申请表。当单击按钮使用打开文件对话框打开Excel Sheet（哪个listview项数据保存Excel Sheet）。我需要再次检索已保存的Excel工作表数据到我的列表视图。
问候
Vasanthakumar
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
推荐答案
基本上阅读Excel工作表与编写工作表相反。您可以使用OpenFileDialog找到Excel工作表，然后调用如下函数。
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.
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;
}
代码加载Excel工作簿。我假设数据在第一个工作表上。接下来确定列数和行数。循环它们并将项目添加到ListView。 ListView不是我的最强点（我没有设法显示子项），因此您可能需要在代码中稍微更改一下以将数据添加到ListView。
顺便说一下，当我研究你的代码时，我可能发现了一个bug。首先，你将你的项目写入ws.Cells [i，j]，当你循环子项时，你将第一个子项写入同一个单元格（没有j递增）。如果项目和第一个子项包含相同的数据（这是我在示例中假设的那样），那么没有问题，否则您可能会丢失数据。
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.
这是检索项目的代码从Excel到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 ++）< br $> b $ b $
// for（int j = 1; j< = colCount; j ++）
// {
试试
{
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（例外ex）
{//MessageBox.Show(ex.Message +\ n+ ex.StackTrace）;
}
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);
}
请查看这篇文章：在WinForm中为C＃绑定和读取ListView的使用 [ ^ ]
Hi, Please check this article: Binding and Reading Usage of ListView in WinForm for C#[^]
这篇关于从Excel Sheet检索数据到Listview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
