C#中ListView导出Excel

客官°小女子只卖身不卖艺 2021-11-14 00:06 493阅读 0赞

/// <summary>
            /// 把listView里面的数据保存成Excle
            /// </summary>
            /// <param name="lv"></param>
            public static void ExportToExcel(ListView pListView)
            {
                if (pListView.Items == null) return;
    
                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xls";
                saveDialog.Filter = "Excel文件|*.xls";
                saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd");
                saveDialog.ShowDialog();
                saveFileName = saveDialog.FileName;
                if (saveFileName.IndexOf(":") < 0)
                    return;
                //这里直接删除，因为saveDialog已经做了文件是否存在的判断
                if (File.Exists(saveFileName)) File.Delete(saveFileName);
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                    return;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                xlApp.Visible = false;
                //填充列
                for (int i = 0; i < pListView.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = pListView.Columns[i].Text.ToString();
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Font.Bold = true;
                }
                //填充数据（这里分了两种情况，1：lv带CheckedBox，2：不带CheckedBox）
    
                //带CheckedBoxes
                if (pListView.CheckBoxes == true)
                {
                    int tmpCnt = 0;
                    for (int i = 0; i < pListView.Items.Count; i++)
                    {
                        if (pListView.Items[i].Checked == true)
                        {
                            for (int j = 0; j < pListView.Columns.Count; j++)
                            {
                                if (j == 0)
                                {
                                    worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].Text.ToString();
                                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                                }
                                else
                                {
                                    worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
                                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                                }
                            }
                            tmpCnt++;
                        }
                    }
                }
                else //不带Checkedboxe
                {
                    for (int i = 0; i < pListView.Items.Count; i++)
                    {
                        for (int j = 0; j < pListView.Columns.Count; j++)
                        {
                            if (j == 0)
                            {
                                worksheet.Cells[2 + i, j + 1] = pListView.Items[i].Text.ToString();
                                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                            else
                            {
                                worksheet.Cells[2 + i, j + 1] = pListView.Items[i].SubItems[j].Text.ToString();
                                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                        }
                    }
                }
                object missing = System.Reflection.Missing.Value;
                try
                {
                    workbook.Saved = true;
                    workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                }
                catch (Exception e1)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + e1.Message);
                }
                finally
                {
                    xlApp.Quit();
                    System.GC.Collect();
                }
                MessageBox.Show("导出Excle成功！");
            }

转载于:https://www.cnblogs.com/Renxf/p/3658798.html