1. 如何更快的將數據導入Excel
這篇文章將介紹3種方式將數據導入Excel：
1、基本方法：一格一格地拷貝
2、使用文件流StreamWriter對象：將流寫入文件
3、拷貝對象的方法：將數據復制到數組，然後直接粘貼到Excel的workbook。
基本方法
使用基本的拷貝方法將會花費大量的時間。使用文件文件流或者拷貝對象的方法將比基本方法快很多。
你必須添加一個Excel COM Object 的引用到你的應用程序之中。我將要聲明兩個對象，Ex為Excel.Application類型。Ws為Excel.Worksheet類型 ，然後設置Ws為workbook的第一個worksheet。
我們將寫代碼去循環Table每一列的標題來顯示標題。
我們使用列（索引），去要檢索列的標題，Caption或者ColumnName屬性。
對於全部的數據，我們將要使用兩個循環，一個循環row，另外一個循環column
代碼
Microsoft.Office.Interop.Excel.Application Ex = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Worksheet Ws ;
Ex.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Ws= (Microsoft.Office.Interop.Excel.Worksheet)Ex.Worksheets[1];
int Row = 0;
int Col = 0;
int i = 0;
int j = 0;
pb.Maximum = Ds.Tables[0].Rows.Count;
Row = 1;
Col = 1;
//'For Heading
lblCount.Text = "Generating Heading.";
this.Refresh();
for (i = 0; i <= Ds.Tables[0].Columns.Count - 1; i++)
{
Ws.Cells[Row, Col] = Ds.Tables[0].Columns[i].Caption;
Col += 1;
}
Row = 2;
Col = 1;
pb1.Maximum = Ds.Tables[0].Columns.Count;
lblCount.Text = "Preparing for Export Data.";
for (i = 0; i <= Ds.Tables[0].Rows.Count - 1; i++)
{
//
//FOR ALL DATA
//
pb1.Value = 0;
for (j = 0; j <= Ds.Tables[0].Columns.Count - 1; j++)
{
Ws.Cells[Row, Col] = Ds.Tables[0].Rows[i][j].ToString();
Col += 1;
pb1.Value += 1;
}
//'If data is more than 65500 then set ws to next sheet
if (Row == 65500)
{
Row = 1;
Ws = (Microsoft.Office.Interop.Excel.Worksheet)Ex.Worksheets[2];
}
Col = 1;
Row += 1;
lblCount.Text = i + 1 + " : Exported";
lblCount.Refresh();
pb.Value += 1;
}
pb.Value = 0;
Ex.Visible = true;
MessageBox.Show(Ds.Tables[0].Rows.Count + " : Records Exported. ");
Ex.Visible = true;
Ex.Quit();
Ex = null;
Ws = null;
使用StreamWriter：
這個方法比較簡短而且是將數據導入如何類型的文件一種比較快方式
在這個方法中，我將使用 System.IO 命名空間，我將編程去指定的.xls 或者 .doc等擴展來直接創建一個的文件路徑。
編碼以文件的路徑開始，這個路徑是Excel文件被創建和數據存儲的地方。現在，聲明一個指定了路徑的IO.StreamWriter對象。在這種方法中，每行的行值/列值被添加到以「|」作為分隔符的字元串中。現在，創建的文件包含以 "|" 分割的單列數據（CSV格式）。
代碼
string filePath = "c:\\SystemIO_Exported_Data_AsOn_" + DateTime.Now.ToShortDateString() + ".xls";
//Stream Writer object to write the stream to file
StreamWriter writer = new StreamWriter(File.Create(filePath));
string str = string.Empty;
//'For Heading
lblCount.Text = "Generating Heading.";
this.Refresh();
for (int i = 0; i <= Ds.Tables[0].Columns.Count - 1; i++)
{
str += Ds.Tables[0].Columns[i].Caption +Constants.vbTab;
}
//Write stream to file adding a new line to stream
str += Microsoft.VisualBasic.Constants.vbNewLine;
writer.Write(str);
writer.Flush();
pb.Maximum = Ds.Tables[0].Rows.Count + 1;
foreach (DataRow dRow in Ds.Tables[0].Rows)
{
str = "";
for (int col = 0; col <= Ds.Tables[0].Columns.Count - 1; col++)
{
string STR1 = "";
char c = Strings.Chr(32);
//char[] sep = " ";
string[] str2 = null;
str2 = dRow[col].ToString().Split(' ');
for (int z = 0; z <= str2.Length - 1; z++)
{
//replacing all spaces and tabs with '|' (pipe sign)
string y = str2[z].ToString().Replace(Strings.Chr(32), ' ').Replace(Strings.Chr(13), ' ').Replace(Strings.Chr(10), ' ').Replace(Strings.Chr(9), ' ').Replace("|", " ");
STR1 += y + " ";
}
str += STR1 + "| ";
pb.Value += 1;
}
str += Constants.vbNewLine;
writer.Write(str);
writer.Flush();
pb.Value = 0;
}
//Close the stream writer object
writer.Close();
pb.Value = 0;
MessageBox.Show("Data Exported Successfully.");
對象拷貝的方法：
這是另外一中將數據導入Excel的方法。
在代碼中，我們創建了二維數組：object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count]來包含數據表中的數據
一旦數據被存儲在一個數組當中，它將通過Excel Worksheet 的get_Range().value方法 ,將數據粘貼到一個 excel worksheet 之中。
代碼
if (Ds.Tables.Count > 3)
{
MessageBox.Show("There Are More than 3 data table. Data can not be exported.","提示");
return;
}
int sheetIndex = 0;
Microsoft.Office.Interop.Excel.Application Ex = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Worksheet Ws;
Microsoft.Office.Interop.Excel.Workbook Wb = Ex.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Ws = (Microsoft.Office.Interop.Excel.Worksheet)Ex.Worksheets[1];
// Copy each DataTable as a new Sheet
foreach (System.Data.DataTable dt in Ds.Tables)
{
//On Error Resume Next
int col = 0;
int row = 0;
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
lblCount.Text = "Copying Columns Name.";
this.Refresh();
// Copy the column names to the first row of the object array
pb1.Maximum = dt.Columns.Count + 1;
pb1.Value = 0;
for (col = 0; col <= dt.Columns.Count - 1; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName.ToUpper();
pb1.Value += 1;
}
lblCount.Text = "Copying Data";
this.Refresh();
pb1.Value = 0;
// Copy the values to the object array
pb.Maximum = dt.Rows.Count + 1;
pb.Value = 0;
for (col = 0; col <= dt.Columns.Count - 1; col++)
{
for (row = 0; row <= dt.Rows.Count - 1; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
pb.Value += 1;
}
pb.Value = 0;
pb1.Value += 1;
}
pb.Value = 0;
pb1.Value = 0;
lblCount.Text = "";
this.Refresh();
// Calculate the final column letter
string finalColLetter = string.Empty;
finalColLetter = ExcelColName(dt.Columns.Count);
//Generate Excel Column Name (Column ID)
sheetIndex += 1;
Ws = (Microsoft.Office.Interop.Excel.Worksheet)Wb.Worksheets[sheetIndex];
Ws.Name = dt.TableName;
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
Ws.get_Range(excelRange, Type.Missing).Value2 = rawData;
Ws = null;
}
Wb.SaveAs("C:\\.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Wb.Close(true, Type.Missing, Type.Missing);
Wb = null;
// Release the Application object
Ex.Quit();
Ex = null;
// Collect the unreferenced objects
GC.Collect();
Interaction.MsgBox("Exported Successfully.", MsgBoxStyle.Information,"提示");
我使用一個函數去找excel worksheet的列名