我下面的代码是生成一个datagrid然后把数据一格一格填到Excel中,但是由于我的数据量太大了。每次运行储存到Excel中就很慢。有没有别的办法不要一格一格的填充。private System.Data.DataTable GetSystemData()
{
SqlDataAdapter da = new SqlDataAdapter ("SELECT
System_Info.SystemName,c.CityName,co.CountryName FROM System_Info ,Customer c,City s,Country co where System_Info.CustomerID = c.CustomerID and c.CityID = s.CityID and s.CountryCode = co.CountryCode", objconnection);
DataSet ds = new DataSet();
DataGrid dg = new DataGrid();
dg.DataSource=ds;
dg.DataMember="FSA";
try
{
da.Fill(ds,"FSA");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds.Tables[0];
}
DataTable table1=this.GetSystemData();foreach(DataColumn co in table1.Columns)
{
coIndex++;
excel.Cells[4,coIndex]=co.ColumnName;
}
foreach(DataRow row in table1.Rows)
{
account++;
rowIndex1++;
coIndex=1;
foreach(DataColumn co in table1.Columns)
{
coIndex++;
i++;
excel.Cells[rowIndex1,coIndex]=row[co.ColumnName].ToString();
}
}谢谢
{
SqlDataAdapter da = new SqlDataAdapter ("SELECT
System_Info.SystemName,c.CityName,co.CountryName FROM System_Info ,Customer c,City s,Country co where System_Info.CustomerID = c.CustomerID and c.CityID = s.CityID and s.CountryCode = co.CountryCode", objconnection);
DataSet ds = new DataSet();
DataGrid dg = new DataGrid();
dg.DataSource=ds;
dg.DataMember="FSA";
try
{
da.Fill(ds,"FSA");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds.Tables[0];
}
DataTable table1=this.GetSystemData();foreach(DataColumn co in table1.Columns)
{
coIndex++;
excel.Cells[4,coIndex]=co.ColumnName;
}
foreach(DataRow row in table1.Rows)
{
account++;
rowIndex1++;
coIndex=1;
foreach(DataColumn co in table1.Columns)
{
coIndex++;
i++;
excel.Cells[rowIndex1,coIndex]=row[co.ColumnName].ToString();
}
}谢谢
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("列1,列1,列1");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr[0]+"\t"+dr[1]+"\t"+dr[2]);
}
sw.Close();
int rowsumary = dset.Tables[i].Rows.Count;
int columnsumary = dset.Tables[i].Columns .Count;
//ToDO:将所有的数据先放在tmpdata里面
object startcell = excel.Cells[StartRowIndex, StartColumnIndex] as object ;
object endcell = excel.Cells[StartRowIndex+rowsumary, StartColumnIndex+columnsumary] as object;
Excel.Range tempRange = worksheet.get_Range(startcell, endcell);
object[,] tempdata = new object[rowsumary, columnsumary];
for (int l = 0; l < rowsumary; l++)
{
for (int k = 0; k < columnsumary; ++k)
{
tempdata[l,k]=dset.Tables[i].Rows[l][k];
}
}
tempRange.Value2 = tempdata;
tempdata = null;
这样速度比LZ的快很多
比数据库是 :2006-04-12
但到Excel后就变成38819
有何解决方法
Excel.Workbooks mobjBooks;
Excel._Workbook mobjBook; Excel.Sheets mobjSheets;
Excel._Worksheet mobjSheet;
Excel.Range mobjRange; Excel.QueryTables mobjTables;
Excel._QueryTable mobjTable;
try
{
//创建一个新的工作表
mobjExcel=new Excel.Application(); //关闭提示
mobjExcel.Application.DisplayAlerts=false; mobjBooks=(Excel.Workbooks)mobjExcel.Workbooks;
mobjBook=(Excel._Workbook)(mobjBooks.Add(Missing.Value)); //从A3的位置创建一个工作表
mobjSheets=(Excel.Sheets)mobjBook.Worksheets ;
mobjSheet=(Excel._Worksheet)mobjSheets.get_Item(1);
mobjRange=mobjSheet.get_Range("A3",Missing.Value);
mobjTables=mobjSheet.QueryTables; mobjTable=(Excel._QueryTable)mobjTables.Add("OLEDB; Provider="+_sysinfo.GetExcelProvider+";Data Source="+Application.StartupPath+@"\Data\smis.flw"+";",mobjRange,strSQL); mobjTable.RefreshStyle=Excel.XlCellInsertionMode.xlInsertEntireRows;
mobjTable.Refresh(false);