做一个项目,需要将数据导入到指定Excel模板里面,我用的是调用com组件(Excel)方法,但是当数据比较多时,速度会很慢。各位有没有好一点的方法?让数据快速导入到Excel里面?
我的代码如下:
Excel.Application app= new Excel.Application();
Excel.Workbook xbook=app.Workbooks._Open(@"D:\Sample.xlt",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
Excel.Worksheet xsheet=(Excel.Worksheet)xbook.Sheets[1];
xsheet = (Excel.Worksheet)app.ActiveSheet;
for(inti=0;i<datagridview1.RowCount;i++)
{//导入到Excel,从第五行开始写入数据。
app.cells[5+i,1] = datagridview1["Column1", i].Value.ToSteing();
app.cells[5+i,2] = datagridview1["Column2", i].Value.ToSteing();
app.cells[5+i,3] = datagridview1["Column3", i].Value.ToSteing();
app.cells[5+i,4] = datagridview1["Column4", i].Value.ToSteing();
app.cells[5+i,5] = datagridview1["Column5", i].Value.ToSteing();
app.cells[5+i,6] = datagridview1["Column6", i].Value.ToSteing();
app.cells[5+i,7] = datagridview1["Column7", i].Value.ToSteing();
app.cells[5+i,8] = datagridview1["Column8", i].Value.ToSteing();
}
message.box.Show("导入成功);
app.Visible=true;
当数据很多时,速度会很慢,望高人指点。给出一个好方法。最好有代码。谢谢!
我的代码如下:
Excel.Application app= new Excel.Application();
Excel.Workbook xbook=app.Workbooks._Open(@"D:\Sample.xlt",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
Excel.Worksheet xsheet=(Excel.Worksheet)xbook.Sheets[1];
xsheet = (Excel.Worksheet)app.ActiveSheet;
for(inti=0;i<datagridview1.RowCount;i++)
{//导入到Excel,从第五行开始写入数据。
app.cells[5+i,1] = datagridview1["Column1", i].Value.ToSteing();
app.cells[5+i,2] = datagridview1["Column2", i].Value.ToSteing();
app.cells[5+i,3] = datagridview1["Column3", i].Value.ToSteing();
app.cells[5+i,4] = datagridview1["Column4", i].Value.ToSteing();
app.cells[5+i,5] = datagridview1["Column5", i].Value.ToSteing();
app.cells[5+i,6] = datagridview1["Column6", i].Value.ToSteing();
app.cells[5+i,7] = datagridview1["Column7", i].Value.ToSteing();
app.cells[5+i,8] = datagridview1["Column8", i].Value.ToSteing();
}
message.box.Show("导入成功);
app.Visible=true;
当数据很多时,速度会很慢,望高人指点。给出一个好方法。最好有代码。谢谢!
http://www.cnblogs.com/hyqiang168/archive/2007/12/27/1017032.html
望指点。
在工具箱里。右键。选择项。找到dataGrid控件。打勾。。OK。
Dao是什么东西?能发一份具体给我看看吗?感谢。
这是一个包。。其实也不是什么东西。就是你实现的基本类。
不要像你原来那样每次循环都写到excel文件中,而是先将数据写到内存中,然后再一起写到Excel,并保存。
#region Access 2007
Console.WriteLine("Access 2007");
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\Database1.accdb;";
strConnection += "Persist Security Info=False";
using (OleDbConnection objConnection = new OleDbConnection(strConnection))
{
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from Couse", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "couse");
DataTable dt = ds.Tables["couse"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
Console.WriteLine(dt.Columns[2].ToString());
objConnection.Close();
}
#endregion
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LCL_data/archive/2009/05/06/4154255.aspx
/// <summary>
/// 根据模版标签 整合要填充的数据集信息方法
/// </summary>
/// <param name="srcDataTable">源数据表</param>
/// <param name="detailsRange">标签区域</param>
/// <returns>填充的二维数组</returns>
public object[,] MergerExcelData(System.Data.DataTable srcDataTable, Range detailsRange)
{
object[,] dataArray = new object[srcDataTable.Rows.Count, detailsRange.Cells.Count];
string [] columnList = new string[detailsRange.Cells.Count];
Range range = null;
string parm = string.Empty;
for (int i = 0; i < detailsRange.Cells.Count; i++)
{
range = (Range)detailsRange.Cells[0, i];
if (range.Value2 != null)
{
parm = range.Value2.ToString();
}
parm = ReportUtils.FillterString(parm);
if (parm.IndexOf(">.") > 0)
{
parm = parm.Substring(1, parm.IndexOf(">") - 1);
}
else if (parm.IndexOf(">") >= 2)
{
parm = parm.Substring(1, parm.Length - 2);
}
else
{
parm = string.Empty;
}
columnList[i] = parm ;
}
for (int i = 0; i < srcDataTable.Rows.Count; i++)
{
for (int j = 0; j < columnList.Count; j++)
{
dataArray[i, j] = ReportUtils.GetDataTableData(srcDataTable, i, columnList[j]);
}
}
return dataArray;
}
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/xingjunli/archive/2009/07/13/4345069.aspx
不过我来说明下我的发现(看别人+自己猜的) excel导入数据慢的原因是由于 你在用cell[i,j]等寻找单元格的函数或属性时浪费的时间 你的数据量越大 使用的cell[i,j]越多 使用时间越长
解决方法
int[,] datas = new int[1,3];
datas[0, 0] = " ";
datas[0, 1] = "0";
datas[0, 2] = "0";
xR = CurrWorkSheet.get_Range("A1", "A2");//CurrWorkSheet是Microsoft.Office.Interop.Excel._Worksheet对象
xR = xR = xR.get_Resize(1, 3);
xR.value2 = datas;
仅作产考(其中的列我没有设置格式 你可以找些资料自己设置列的格式)
自己修正代码 +分给我啊
if (dvData.Rows.Count >0 )
{
#region 向Excel写数据//===========================================
Excel.Application excel = new Excel.Application();
object oMissing = System.Reflection.Missing.Value;
Excel.Workbook xlsBook = excel.Workbooks.Open(filename + prdpz + ".xls", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true);
if (xlsBook.Sheets.Count < cobMachID.SelectedIndex + 1)
{
xlsBook.Sheets.Add(oMissing, xlsBook.Sheets[xlsBook.Sheets.Count], oMissing, oMissing);
}
Excel._Worksheet xlsSheet = (Excel._Worksheet)xlsBook.Worksheets[cobMachID.SelectedIndex + 1]; xlsSheet.Name = cobMachID.SelectedItem.ToString();
xlsSheet.Activate();
excel.Visible = false; int ExcelRC = xlsSheet.UsedRange.Cells.Rows.Count; //获取原有Excel中的行数 int totalnum = 0; for (int ii = 0; ii < dvData.Rows.Count; ii++)// dvData 是指datagridview
{
for (int j = 0; j < dvData.ColumnCount; j++)
{
if (dvData[j, ii].ValueType == typeof(string))
{
excel.Cells[ExcelRC + ii + 1 + totalnum, j + 1] = "'" + dvData[j, ii].Value.ToString();
}
else
{
try
{
excel.Cells[ExcelRC + ii + 1 + totalnum, j + 1] = dvData[j, ii].Value.ToString();
}
catch(Exception ex)
{
// swlog(ex);
}
} }
}
#endregion //============================================================
excel.Columns.EntireColumn.AutoFit(); //自动调整列宽
excel.Columns.EntireRow.AutoFit(); //自动调整行高
xlsBook.Save(); //保存
excel.Quit(); //退出
//KillProcess(); //
xlsBook = null;
xlsSheet = null;
excel = null;
}
else
{
mesgbox.show("没有数据可导出");
}
感谢各位高人,重点感谢3L,11L和13L。谢谢!