ListView导出Excel, 并实现数据追加

 

public void ExportToExcel(ListView L_ListMember,ListView L_ListCN_Trade) 
{
                string saveFileName = "";   
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xls";
                saveDialog.Filter = "Excel(*.xls)|*.xls";
                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    saveFileName = saveDialog.FileName;
                    Excel.Application xlApp = new Excel.Application();
                    if (xlApp == null)
                    {
                        MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                        return;
                    }

                    Excel.Workbooks workbooks = xlApp.Workbooks;
                    Excel.Workbook workbook = workbooks.Add(true);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                    xlApp.Visible = false;
                    int rowIdx = 1;//行索引（第一行）

                    #region 1.导出第一批数据
                    #region 表头
                    for (int i = 0; i < L_ListMember.Columns.Count; i++)
                    {
                        worksheet.Cells[rowIdx, i + 1] = L_ListMember.Columns[i].Text.ToString();
                        ((Excel.Range)worksheet.Cells[rowIdx, i + 1]).Font.Bold = true;
                    }
                    #endregion

                    #region 数据行
                    for (int i = 0; i < L_ListMember.Items.Count; i++)
                    {
                        rowIdx++;//指向下一行
                        for (int j = 0; j < L_ListMember.Columns.Count; j++)
                        {
                            if (j == 0)
                            {
                                worksheet.Cells[rowIdx, j + 1] = L_ListMember.Items[i].Text.ToString();
                                ((Excel.Range)worksheet.Cells[rowIdx, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                            }
                            else
                            {
                                worksheet.Cells[rowIdx, j + 1] = L_ListMember.Items[i].SubItems[j].Text.ToString();
                                ((Excel.Range)worksheet.Cells[rowIdx, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                            }
                        }
                    }
                    #endregion
                    #endregion

                    #region 2.导出第二批数据,实现数据追加
                    if (L_ListCN_Trade.Items.Count > 0)
                    {
                        #region 表头

                        rowIdx = rowIdx + 3;//间隔两行再追加数据
                        for (int i = 0; i < L_ListCN_Trade.Columns.Count; i++)
                        {
                            worksheet.Cells[rowIdx, i + 1] = L_ListCN_Trade.Columns[i].Text.ToString();
                            ((Excel.Range)worksheet.Cells[rowIdx, i + 1]).Font.Bold = true;
                        }
                        #endregion

                        #region 数据行
                        for (int i = 0; i < L_ListCN_Trade.Items.Count; i++)
                        {
                            rowIdx++;//指向下一行
                            for (int j = 0; j < L_ListCN_Trade.Columns.Count; j++)
                            {
                                if (j == 0)
                                {
                                    worksheet.Cells[rowIdx, j + 1] = L_ListCN_Trade.Items[i].Text.ToString();
                                    ((Excel.Range)worksheet.Cells[rowIdx, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                                }
                                else
                                {
                                    worksheet.Cells[rowIdx, j + 1] = L_ListCN_Trade.Items[i].SubItems[j].Text.ToString();
                                    ((Excel.Range)worksheet.Cells[rowIdx, j + 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                                }
                            }
                        }
                        #endregion
                    }
                    #endregion

                    #region 保存Excel
                    object missing = System.Reflection.Missing.Value;
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                        MessageBox.Show("导出成功！");
                    }
                    catch (Exception e1)
                    {
                        MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + e1.Message);
                    }
                    finally
                    {
                        xlApp.Quit();
                        System.GC.Collect();
                    }
                    #endregion
                }
}