c# 保存listView数据为excel文件的二种方法

发布时间:2020-01-25编辑:脚本学堂
本文分享下,c#实现将listview数据保存为excel文件的方法,这里提供两种方法供大家参考。

以下代码实现:
把listView中的数据保存成Excle。

方法1,保存listView数据为excel文件。
代码:
 

复制代码 代码示例:

using System.IO;
using Microsoft.Office.Interop.Excel;
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成功!");
}

方法2,保存listView数据为excel文件。
代码:
 

复制代码 代码示例:

using Microsoft.Office.Interop.Excel;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
using System.Reflection;

public void TurnToExcel(ListView listView, string stname)
{
    string Sheetname = stname;
    ListView listView1 = listView;
    if (listView1.Items.Count < 1)
    return;
    try
    {
      ExcelApplication MyExcel = new ExcelApplication();
      MyExcel.Visible = true;
      if (MyExcel == null)
      {
         return;
      }
Workbooks MyWorkBooks = (Workbooks)MyExcel.Workbooks;
Workbook MyWorkBook = (Workbook)MyWorkBooks.Add(Missing.Value);
Worksheet MyWorkSheet = (Worksheet)MyWorkBook.Worksheets[1];

Range MyRange = MyWorkSheet.get_Range("A1", "H1");
MyRange = MyRange.get_Resize(1, listView1.Columns.Count);
object[] MyHeader = new object[listView1.Columns.Count];
for (int i = 0; i < listView1.Columns.Count; i++)
{
    MyHeader.SetValue(listView1.Columns[i].Text, i);
}
MyRange.Value2 = MyHeader;
MyWorkSheet.Name = Sheetname;

if (listView1.Items.Count > 0)
{
    MyRange = MyWorkSheet.get_Range("A2", Missing.Value);

    object[,] MyData = new Object[listView1.Items.Count, listView1.Columns.Count];
    for (int j = 0; j < listView1.Items.Count; j++)
    {
ListViewItem lvi = listView.Items[j];
for (int k = 0; k < listView1.Columns.Count; k++)
{
 MyData[j, k] = lvi.SubItems[k].Text;
}
}
MyRange = MyRange.get_Resize(listView1.Items.Count, listView1.Columns.Count);
MyRange.Value2 = MyData;
MyRange.EntireColumn.AutoFit();
}
MyExcel = null;
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
}

备注:
以上代码记得添加Excel引用:Microsoft.Office.Interop.Excel