以下是网上找的一段代码,可是数据稍稍大一些,确实很慢
请问各位高手有什么好的方法呢,谢谢~~这个类能实现ListView数据导出成Excel的功能,但是还是有些美中不足,就是效率不是很好,如果数据量比较大的话,建议用其他方法,不过我也没有找到比较好的办法,哪位仁兄有好的方法记得共享一份给小弟,先谢谢啦。
ListView数据导出成Excel#region ListView数据导出成Excel
/**//// <summary>
/// ListView数据导出成Excel
/// </summary>
/// <param name="listviewname">ListView控件ID</param>
public static void ExpToExcel(ListView listviewname)
{
if (listviewname.Items.Count == 0) return;
Excel.Application excel = new Excel.Application();
Excel.Workbooks workbooks = excel.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Sheets worksheets = workbook.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet) worksheets.get_Item(1);
excel.Visible = true; Excel.Range range;
excel.Cells.Select();
excel.Cells.RowHeight = 30;
excel.Cells.Font.Size = 10;
excel.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; int rowPos = 2;
range = excel.get_Range(sheet.Cells[rowPos,1],sheet.Cells[rowPos,1]);
range.Select(); for(int i=1;i<=listviewname.Columns.Count;i++)
{
range = excel.get_Range(sheet.Cells[rowPos,i],sheet.Cells[rowPos,i]);
range.Borders.LineStyle = 1;
range.Font.Name = "华文仿宋";
range.Font.Size = 16;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.ColumnWidth = 18;
sheet.Cells[rowPos,i] = listviewname.Columns[i-1].Text.ToString(); }
rowPos++; foreach(ListViewItem item in listviewname.Items)
{
for(int i=1;i<=listviewname.Columns.Count;++i)
{
range = excel.get_Range(sheet.Cells[rowPos,i],sheet.Cells[rowPos,i]);
range.Borders.LineStyle = 1;
range.Font.Name = "华文仿宋";
range.Font.Size = 12;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// range.NumberFormat = Excel.xlColumnDataType.xlTextFormat ;
range.NumberFormat = Excel.XlParameterDataType.xlParamTypeUnknown ;
sheet.Cells[rowPos,i] = item.SubItems[i-1].Text;
}
rowPos++;
}
GC.Collect() ;
}
#endregion
请问各位高手有什么好的方法呢,谢谢~~这个类能实现ListView数据导出成Excel的功能,但是还是有些美中不足,就是效率不是很好,如果数据量比较大的话,建议用其他方法,不过我也没有找到比较好的办法,哪位仁兄有好的方法记得共享一份给小弟,先谢谢啦。
ListView数据导出成Excel#region ListView数据导出成Excel
/**//// <summary>
/// ListView数据导出成Excel
/// </summary>
/// <param name="listviewname">ListView控件ID</param>
public static void ExpToExcel(ListView listviewname)
{
if (listviewname.Items.Count == 0) return;
Excel.Application excel = new Excel.Application();
Excel.Workbooks workbooks = excel.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Sheets worksheets = workbook.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet) worksheets.get_Item(1);
excel.Visible = true; Excel.Range range;
excel.Cells.Select();
excel.Cells.RowHeight = 30;
excel.Cells.Font.Size = 10;
excel.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; int rowPos = 2;
range = excel.get_Range(sheet.Cells[rowPos,1],sheet.Cells[rowPos,1]);
range.Select(); for(int i=1;i<=listviewname.Columns.Count;i++)
{
range = excel.get_Range(sheet.Cells[rowPos,i],sheet.Cells[rowPos,i]);
range.Borders.LineStyle = 1;
range.Font.Name = "华文仿宋";
range.Font.Size = 16;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.ColumnWidth = 18;
sheet.Cells[rowPos,i] = listviewname.Columns[i-1].Text.ToString(); }
rowPos++; foreach(ListViewItem item in listviewname.Items)
{
for(int i=1;i<=listviewname.Columns.Count;++i)
{
range = excel.get_Range(sheet.Cells[rowPos,i],sheet.Cells[rowPos,i]);
range.Borders.LineStyle = 1;
range.Font.Name = "华文仿宋";
range.Font.Size = 12;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// range.NumberFormat = Excel.xlColumnDataType.xlTextFormat ;
range.NumberFormat = Excel.XlParameterDataType.xlParamTypeUnknown ;
sheet.Cells[rowPos,i] = item.SubItems[i-1].Text;
}
rowPos++;
}
GC.Collect() ;
}
#endregion
http://www.codeproject.com/dotnet/ExportToExcel.asp
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel != null)
{
excel.Visible = true;
excel.Application.Workbooks.Add(true);
int col = list.Columns.Count;
int row = list.Items.Count;
for(int i=1;i<=col;i++)
{
excel.Cells[1, i] = list.Columns[i - 1].Text;
} for (int i = 0; i < row; i++)
for (int j = 0; j < col; j++)
if (j == 0)
excel.Cells[i + 2, j + 1] = list.Items[i].Text;
else
excel.Cells[i + 2, j + 1] = list.Items[i].SubItems[j].Text; }
excel.Application.Save("c:\\123.xls");
//excel.SaveWorkspace
excel.Quit();
public void WriteToExcel(DataTable table)
{
try
{
string strFilePath = @"C:\book.xls";
System.IO.StreamWriter sw = new System.IO.StreamWriter(strFilePath, false, System.Text.Encoding.Default);
object[] values = new object[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; ++i)
{
sw.Write(table.Columns[i].Caption.ToString());
sw.Write('\t');
}
sw.Write("\r\n");
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < values.Length; ++j)
{
sw.Write(table.Rows[i][j].ToString());
sw.Write('\t');
}
sw.Write("\r\n");
}
sw.Flush();
sw.Close();
}
catch (Exception ex)
{
throw ex;
}
}
建议此操作领开一个后台处理线程,这样前台不会没有响应.
private void listView1_ColumnClick(object sender, ColumnClickEventArgs e)
{
if (this.listView1.Columns[e.Column].Tag == null)
this.listView1.Columns[e.Column].Tag = true;
bool tabK = (bool)this.listView1.Columns[e.Column].Tag;
if (tabK)
this.listView1.Columns[e.Column].Tag = false;
else
this.listView1.Columns[e.Column].Tag = true;
this.listView1.ListViewItemSorter = new ListViewSort(e.Column, this.listView1.Columns[e.Column].Tag);
//指定排序器并传送列索引与升序降序关键字
this.listView1.Sort();//对列表进行自定义排序
}
排序类的定义:///
///自定义ListView控件排序函数
///class ListViewSort : IComparer
{
private int col;
private bool descK; public ListViewSort()
{
col = 0;
}
public ListViewSort(int column, object Desc)
{
descK = (bool)Desc;
col = column; //当前列,0,1,2...,参数由ListView控件的ColumnClick事件传递
}
public int Compare(object x, object y)
{
int tempInt = String.Compare(((ListViewItem)x).SubItems[col].Text, ((ListViewItem)y).SubItems[col].Text);
if (descK) return -tempInt;
else return tempInt;
}
}
上面的ListView控件的自定义排列,即单击ListView控件的标题时进行排序
下面将实现ListView控件的最后一列的去除,即自动调整合适的大小
首先写一个调整ListView控件列宽的函数
///
///自动调整listView控件最后一列的列宽
///private void 调整LV列宽()
{
listView1.ColumnWidthChanged -= new ColumnWidthChangedEventHandler(listView1_ColumnWidthChanged);
备注.AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
listView1.ColumnWidthChanged += new ColumnWidthChangedEventHandler(listView1_ColumnWidthChanged);
}
上面的备注列是listview控件的最后一列的名称,而listview控件的实例名为listView1
然后订阅ListView控件的ColumnWidthChanged事件,即列宽改变时自动调整列宽
///
/// listview列宽改变事件函数
///void listView1_ColumnWidthChanged(object sender, ColumnWidthChangedEventArgs e)
{
调整LV列宽();
} 再订阅ListView控件的Size_Change事件,即窗口大小被改变时调整列宽
void listView1_SizeChanged(object sender, EventArgs e)
{
调整LV列宽();
} 最后在窗体的Shown事件中调整ListView控件的列宽,即第一次显示的时候马上调整列宽
private void 商品管理_Shown(object sender, EventArgs e)
{
调整LV列宽();
}
看下面的代码示例:object[,] cache = new object[listView1.Items.Count + 1, listView1.Columns.Count];
for (int i = 0; i < listView1.Columns.Count; i++)
{
cache[0, i] = listView1.Columns[i].Text;
for (int j = 0; j < listView1.Items.Count; j++)
{
cache[j + 1, i] = listView1.Items[j].SubItems[i].Text;
}
}
Excel.Application app1 = new Excel.Application();
Excel.Workbook book1 = app1.Workbooks.Add(Type.Missing);
Excel.Worksheet sheet1 = (Excel.Worksheet)book1.Sheets[1];
string sRange = string.Format("A1:{0}{1}", (char)('A' + (listView1.Columns.Count - 1)), listView1.Items.Count + 1);
sheet1.get_Range(sRange, Type.Missing).Value2 = cache;
app1.Visible = true;
http://www.tup.tsinghua.edu.cn/book/SHOWBOOK.asp?cpbh=023623-01
我们完全没有必要逐个单元格的进行操作,而可以在数组和单元格区域中进行整体转换。
Range对象的Value属性为object类型,但可以把一个二维数组赋值给该属性,这就完成了对整个区域的一次性写入