C#读写excel信息到ListView控件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WindowsFormsApp3
{
    public partial class Form1 : Form
    {
        string sFilePath = "test.xlsx";
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //  Npio_excel npexcel = new Npio_excel();
            //   npexcel.excel_c();

        }
        //读excel文件
        private void button1_Click(object sender, EventArgs e)
        {
            //Readexcel re = new Readexcel();
            // re.read();

            //  Console.WriteLine("Hello, World!");
            var fileName = "test.xlsx";
            string allcell;
            if (!File.Exists(fileName))
            {
                return;
            }
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = WorkbookFactory.Create(fs);
            //尝试获取第一个sheet
            var sheet = workbook.GetSheetAt(0);
            //判断是否获取到 sheet
            if (sheet != null)
            {
                //获取第一行
                int a = sheet.PhysicalNumberOfRows;// 表的总行数
                                                   //  Console.WriteLine(mmm);
                                                   //   var a = sheet.LastRowNum;  //也是表的总行数，要加1
                for (int j = 0; j < a; j++)
                {
                    var row = sheet.GetRow(j);
                    for (int i = 0; i < row.Count(); i++)
                    {
                        ListViewItem listviewItem = new ListViewItem();
                        //   string s = array[i].ToString();
                        //这里没有对格式是否符合要求进行判断，如果内容被修改成其他格式则会出异常的
                        // string[] arr = s.Split(',');
                        listviewItem.Text = (j + 1).ToString();
                        //  listviewItem.Text = arr[0];
                        listviewItem.SubItems.Add(row.GetCell(i++).ToString());
                        listviewItem.SubItems.Add(row.GetCell(i++).ToString());
                        listviewItem.SubItems.Add(row.GetCell(i++).ToString());
                        listviewItem.SubItems.Add(row.GetCell(i++).ToString());
                        listView1.Items.Add(listviewItem);

                        // allcell =allcell+ row.GetCell(i).ToString();
                        //  MessageBox.Show(allcell);
                        //  Console.WriteLine(allcell);
                        //输出
                        //  Console.Write($"第{j + 1}行数据：第 {i + 1} 个数据值：{row.GetCell(i).ToString()}");
                    }
                }
            }
        }
        //向listview 加内容
        private void button2_Click(object sender, EventArgs e)
        {
            //if (txtMsg.Text.Trim().Replace(" ", "") == "")
            //{
            //    MessageBox.Show("备注不能为空值");
            //    return;
            //}
            int i = listView1.Items.Count + 1;
            ListViewItem listItem = listView1.Items.Add(i.ToString());    //((lstList.Items.Count + 1).ToString().Trim());
            listItem.SubItems.Add(textBox1.Text);                                                                              //  listItem.SubItems.Add(dtboxTime.Value.ToString("HH:mm:ss"));
            listItem.SubItems.Add(textBox2.Text);
            listItem.SubItems.Add(textBox3.Text);
            listItem.SubItems.Add(textBox4.Text);
        }
        //把listview 写入到excel
        private void button3_Click(object sender, EventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet worksheet = workbook.CreateSheet("工作表");

            for (int j = 0; j < listView1.Items.Count; j++)
            {
                IRow row = worksheet.CreateRow(j);//创建行
                for (int i = 0; i < listView1.Items[j].SubItems.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(listView1.Items[j].SubItems[i].Text);
                    //row.CreateCell(i).SetCellValue((j + 1).ToString() + "行" + (i + 1).ToString() + "列" + "的值");
                }
            }
            //第二行代码和下面两行代码效果相同
            //ICell cell = row.CreateCell(0);
            //cell.SetCellValue("行1列1的单元格的值");

            FileStream file = new FileStream(sFilePath, FileMode.Create);
            workbook.Write(file);
            file.Close();
        }
    }

}