C#操作Excel进行读写

1、参考文献：

　　C#导出EXCEL的几种方法 - KJXY - 博客园 (cnblogs.com)

　　C#（com组件）操作Excel读写 - 红烧狮子头 - 博客园 (cnblogs.com)

2、添加Microsoft.Office.Interop.Excel引用

3、编写静态方法

public static bool SaveAsExcel(DataSet dataSet, bool isShowExcle)
        {
            System.Data.DataTable dataTable = dataSet.Tables[0];
            int rowNumber = dataTable.Rows.Count;//不包括字段名
            int columnNumber = dataTable.Columns.Count;
            int colIndex = 0;

            if (rowNumber == 0)
            {
                return false;
            }

            string path = Path.Combine(System.Environment.CurrentDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string filePath = Path.Combine(path, DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            if (!File.Exists(filePath))
            {
                //建立Excel对象
                Application excel = new Application();
                //excel.Application.Workbooks.Add(true);
                Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
                excel.Visible = isShowExcle;
                Range range;

                //生成字段名称
                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                object[,] objData = new object[rowNumber, columnNumber];

                for (int r = 0; r < rowNumber; r++)
                {
                    for (int c = 0; c < columnNumber; c++)
                    {
                        objData[r, c] = dataTable.Rows[r][c];
                    }
                }
                
                // 写入Excel
                range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
                //range.NumberFormat = "@";//设置单元格为文本格式
                range.Value2 = objData;
                workbook.Close(true, filePath, null);
                //退出Excel
                excel.Quit();
            }
            else
            {
                //通过指定路径打开xlms
                Application excelApp = new Application();
                Workbooks workbooks = excelApp.Workbooks;
                Workbook workbook = workbooks.Open(filePath) as Workbook;
                Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
                excelApp.Visible = isShowExcle;

                worksheet.Activate();
                int dtRows=dataSet.Tables[0].Rows.Count;
                int dtColumn = dataSet.Tables[0].Columns.Count;
                int row = worksheet.UsedRange.CurrentRegion.Rows.Count;
                int column = worksheet.UsedRange.CurrentRegion.Columns.Count;
                int Row = worksheet.Range["a1"].CurrentRegion.Rows.Count;
                int Column = worksheet.Range["a1"].CurrentRegion.Columns.Count;
                int actualColumn = Column == 1 ? dtColumn : Column;
                if(Column == 1)
                {
                    //添加标题
                    foreach (DataColumn col in dataTable.Columns)
                    {
                        excelApp.Cells[1, dataTable.Columns.IndexOf(col)+1] = col.ColumnName;
                    }
                }
                //指定从填充区域进行文本填充
                //Range range = worksheet.get_Range("A1", "C2");
                Range range=worksheet.get_Range(excelApp.Cells[Row+1, 1], excelApp.Cells[Row+dtRows, actualColumn]);
                object[,] objArr = new object[dataSet.Tables[0].Rows.Count, dataSet.Tables[0].Columns.Count];
                for(int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                {
                    for(int j = 0; j < dataSet.Tables[0].Columns.Count;j++)
                    {
                        objArr[i,j] = dataSet.Tables[0].Rows[i][j];
                    }
                }
                range.Value2 = objArr;
                workbook.Close(true, filePath, null);
                excelApp.Workbooks.Close();
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);//释放com对象，否则在任务管理器中一直运行
                excelApp = null;
            }
            //worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

            return true;
        }

4、添加ListView转DataSet方法

public static DataSet ConvertToDataTable(ListView lv)
        {
            DataSet ds=new DataSet();
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Clear();
            dt.Columns.Clear();
            //生成DataTable列头
            for (int i = 0; i < lv.Columns.Count; i++)
            {
                dt.Columns.Add(lv.Columns[i].Text.Trim(), typeof(string));
            }
            //添加每行内容
            for (int i = 0; i < lv.Items.Count; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < lv.Columns.Count; j++)
                {
                    dr[j] = lv.Items[i].SubItems[j].Text.Trim();
                }
                dt.Rows.Add(dr);
            }
            ds.Tables.Add(dt);
            return ds;
        }

5、测试调用

　　说明：我在WinForm中添加了一个ListView,id没改，直接就是默认的listView1,共有五列。

for (int i = 1; i < 10; i++)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.Text = i.ToString();
                lvi.SubItems.Add((i + 20).ToString());

                lvi.SubItems.Add((i).ToString());
                lvi.SubItems.Add("aaaaaaa");
                lvi.SubItems.Add("bbbbbb");
                listView1.Items.Add(lvi);
            }
            DataSet ds= ConvertToDataTable(listView1);
            string path = Path.Combine(System.Environment.CurrentDirectory, "Excel\\20220629.xlsx");
            CommonHelper.SaveAsExcel(ds, false);

    　}

演示操作

　　如果不通过System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);释放com对象，那么任务管理器中WPS应用一直存活，导致后面再次打开WPS时黑屏。所以要记得释放COM对象