Listview1导出到excel

问题描述

我似乎无法获得listview1在excel中显示的列名

listview列从Private void GetData（）填充

I Cant seem to get the listview1 Column names to show in excel
the listview columns get populate from Private void GetData()

try
            {

                listView1.Items.Clear();
                listView1.Columns.Clear();
                listView1.Columns.Add("PID", 80);//0
                listView1.Columns.Add("Product", 100);//1
                listView1.Columns.Add("Category", 100);//2
                listView1.Columns.Add("Bin", 100);//3
                listView1.Columns.Add("Brand", 100);//4
                listView1.Columns.Add("Manufacturer", 100);//5
                listView1.Columns.Add("Stock", 100);//6
                listView1.Columns.Add("Stock Take", 100);//7
                listView1.Columns.Add("ShortFall", 100);//8
                listView1.Columns.Add("IN/OUT", 100);//9
                listView1.Columns.Add("Comment", 100);//10
                listView1.Columns.Add("Date", 100);//11

                string sql = @"Select * from tblStocktake where Product like '" + txtSearch.Text + "%'";
                cm = new SqlCommand(sql, cn);
                dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    lst = listView1.Items.Add(dr[0].ToString());
                    lst.SubItems.Add(dr[1].ToString());
                    lst.SubItems.Add(dr[2].ToString());
                    lst.SubItems.Add(dr[3].ToString());
                    lst.SubItems.Add(dr[4].ToString());
                    lst.SubItems.Add(dr[5].ToString());
                    lst.SubItems.Add(dr[6].ToString());
                    lst.SubItems.Add(dr[7].ToString());
                    lst.SubItems.Add(dr[8].ToString());
                    lst.SubItems.Add(dr[9].ToString());
                    lst.SubItems.Add(dr[10].ToString());
                    lst.SubItems.Add(dr[11].ToString());
                }
                dr.Close();
            }

我的尝试：

What I have tried:

using (SaveFileDialog sfds = new SaveFileDialog() {Filter = "Excel Workbook|*.xls",ValidateNames = true })
            {
                if (sfds.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.Application app1 = new Microsoft.Office.Interop.Excel.Application();
                    Workbook wb = app1.Workbooks.Add(XlSheetType.xlWorksheet);
                    Worksheet ws = (Worksheet)app1.ActiveSheet;
                    app1.Visible = false;
                   
                    ws.Cells[1, 1] = "PID";
                    ws.Cells[1, 2] = "Product";
                    ws.Cells[1, 3] = "Category";
                    ws.Cells[1, 4] = "Bin";
                    ws.Cells[1, 5] = "Brand";
                    ws.Cells[1, 6] = "Manufacturer";
                    ws.Cells[1, 7] = "Stock";
                    ws.Cells[1, 8] = "Stock Take";
                    ws.Cells[1, 9] = "Shortfall";
                    ws.Cells[1, 10] = "IN/Out";
                    ws.Cells[1, 11] = "Comment";
                    ws.Cells[1, 12] = "Date";
                    int i = 2;
                    int x = 1;
                    int x2 = 1;
                    int colNum = listView1.Columns.Count;
                    foreach (ColumnHeader ch in listView1.Columns)
                    {
                        ws.Cells[x2, x] = ch.Text;
                        x++;
                    }
                    foreach (ListViewItem item in listView1.Items)
                    {

                        ws.Cells[i = 1] = item.SubItems[0].Text;
                        ws.Cells[i = 2] = item.SubItems[1].Text;
                        ws.Cells[i = 3] = item.SubItems[2].Text;
                        ws.Cells[i = 4] = item.SubItems[3].Text;
                        ws.Cells[i = 5] = item.SubItems[4].Text;
                        ws.Cells[i = 6] = item.SubItems[5].Text;
                        ws.Cells[i = 7] = item.SubItems[6].Text;
                        ws.Cells[i = 8] = item.SubItems[7].Text;
                        ws.Cells[i = 9] = item.SubItems[8].Text;
                        ws.Cells[i = 10] = item.SubItems[9].Text;
                        ws.Cells[i = 11] = item.SubItems[10].Text;
                        ws.Cells[i = 12] = item.SubItems[11].Text;
                      
                        i++;
                    }
                    wb.SaveAs(sfds.FileName, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
                    app1.Quit();
                    MessageBox.Show("Your data has been successfully exported", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
            }

推荐答案

int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
    rowCount += 1;
    for (int i = 1; i < dt.Columns.Count + 1; i++)
    {
        // Add the header the first time through
        if (rowCount == 2)
        {
            ws.Cells[1, i] = dt.Columns[i - 1].ColumnName;
        }
        ws.Cells[rowCount, i] = dr[i - 1].ToString();
    }
}

这篇关于Listview1导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！