工作需要,改进一下C#中ListView导出xls的方法,部分代码为网上搜集,在此向原作者表示感谢!经修改,可直接将ListView中数据导出为Excel 2003格式,并自动分成多个工作表。
速度有点慢,数据量大的话,真得等会儿。。。不过倒是能用。。。
/// <summary>
/// 具体导出的方法
/// </summary>
/// <param name="listView">ListView</param>
/// <param name="strFileName">导出到的文件名</param>
private static void DoExport(ListView listView, string strFileName, bool OverWrite, bool AlertWhenComplete)
{
int rowNum = listView.Items.Count;
int columnNum = listView.Items[0].SubItems.Count;
int rowIndex = 1;
int columnIndex = 0;
if (rowNum == 0 || string.IsNullOrEmpty(strFileName))
{
return;
}
if (rowNum > 0)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建excel对象,可能您的系统没有安装excel");
return;
}
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
int SheetNum = rowNum / 65535 + 1;
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets.Add(Type.Missing, xlBook.Sheets[xlBook.Sheets.Count], SheetNum-1, Type.Missing);
//将ListView的列名导入Excel表第一行
for (int p = 0; p < SheetNum; p++)
{
columnIndex = 0;
foreach (ColumnHeader dc in listView.Columns)
{
columnIndex++;
xlBook.Sheets[p+1].Cells[rowIndex, columnIndex] = dc.Text;
}
}
//将ListView中的数据导入Excel中
int shtNum = 1;
for (int i = 0; i < rowNum; i++)
{
if (i > 0 && i % 65535 == 0)
{
rowIndex = 1;
shtNum++;
}
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
//在导出的时候使用正则表达式验证单元格内容,若为纯数字,在前面加“'”,避免导出的数据显示为科学计数法。
string ss = Convert.ToString(listView.Items[i].SubItems[j].Text);
if (Regex.IsMatch(ss, @"^\d+$"))
{
ss = "'" + ss;
}
xlBook.Worksheets[shtNum].Cells[rowIndex, columnIndex] = ss;
}
}
if (OverWrite)
{
xlApp.DisplayAlerts = false;
}
else
{
xlApp.DisplayAlerts = true;
}
xlBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlBook.Close();
xlApp.Quit();
xlBook = null;
xlApp = null;
if (AlertWhenComplete)
{
MessageBox.Show("数据导出完毕!", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
}
}