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...
以下代码实现：
把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
您可能感兴趣的文章:
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。