使用C#把DataTable内資料寫入Excel的方法收集。
一、使用OpenXml方式
//以下是由OpenXml方式把DataTable生成Excel的例子:
public void ExportDataTable(
DataTable table,
string exportFile)
{
//create the empty spreadsheet template and save the file
//using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0;i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
private Cell createTextCell(
int columnIndex,
int rowIndex,
object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private Row createContentRow(
DataRow dataRow,
int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
private string getColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend – 1) % 26;
columnName =
Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend – modifier) / 26);
}
return columnName;
}
注意:該方法需要微軟的OpenXML SDK 2.0支援。
二、OleDB方式
public string ExportTable2ExcelFile(DataTable dt, string strWorkSheetName)//, string ModelFile)
{
if (File.Exists(this.FileName) == false)
{
return "指定檔案不存在!";
}
if (dt == null)
{
return "資料不能為空!";
}
if (strWorkSheetName.ToString() == "")
{
return "資料表名不可以為空!";
}
dt.TableName = strWorkSheetName;
int iRows = dt.Rows.Count;
int iCols = dt.Columns.Count;
StringBuilder stringBuilder;
if (iRows == 0)
{
return "沒有可導入資料!";
}
stringBuilder = new StringBuilder();
//先檢視此Excel中是否有相關Table,如果有的話就删除,然後導入新的。
//生成建立表的腳本
stringBuilder.Append("CREATE TABLE ");
stringBuilder.Append(dt.TableName + " ( ");
for (int i = 0; i < iCols; i++)
{
//此處是本版本改進中最實用的地方
string strType = GetOleDataType(dt.Columns[i]);
if (i < iCols - 1)
stringBuilder.Append(string.Format("{0} {1},", dt.Columns[i].ColumnName, strType));
else
stringBuilder.Append(string.Format("{0} {1})", dt.Columns[i].ColumnName, strType));
}
using (this.Connection)
{
//控制進度條
int iCount = 1, iTotal = dt.Rows.Count + 4;
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = this.Connection;
//插入新表
objCmd.CommandText = stringBuilder.ToString();
try
{
this.Connection.Open();
//插入新表
objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
return "在Excel中建立表失敗!錯誤資訊:" + e.Message;
}
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("INSERT INTO ");
stringBuilder.Append(dt.TableName + " ( ");
//先插入标頭
for (int i = 0; i < iCols; i++)
{
if (i < iCols - 1)
stringBuilder.Append(dt.Columns[i].ColumnName + ",");
else
stringBuilder.Append(dt.Columns[i].ColumnName + ") values (");
}
for (int i = 0; i < iCols; i++)
{
if (i < iCols - 1)
stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");
else
stringBuilder.Append("@" + dt.Columns[i].ColumnName + ")");
}
//建立插入動作的Command
objCmd.CommandText = stringBuilder.ToString();
OleDbParameterCollection oleParam = objCmd.Parameters;
oleParam.Clear();
for (int i = 0; i < iCols; i++)
{
OleDbType oleDbType = GetRefOleDataType(dt.Columns[i]);
//此處是本版本改進中最實用的地方
oleParam.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, oleDbType));
}
//周遊DataTable将資料插入建立的Excel檔案中
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < oleParam.Count; i++)
{
oleParam[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
if (this.Connection.State == ConnectionState.Open)
{
this.Connection.Close();
}
return "資料已成功導入Excel!";
}
}
該方法中的OleDB連接配接Connection的具體實作和 Excel檔案名我就沒有貼出來了,朋友們可以自己去實作。
注意:該方法不能自動建立Excel檔案,是以使用該方法需要先建立好Excel檔案才能使用。
三、使用Com元件實作Excel資料寫入
該方法在網上列子較多,由于時間關系,這裡我就不代碼列舉了(以後有時間再來補上)。
注意:該方法需要引用Com元件(Microsoft Office 12.0 Object Library :Office 2007;Microsoft Office 14.0 Object Library :Office 2010;
Microsoft Office 8.0 Object Library:Office2003)。
引用一下連結:http://blog.csdn.net/tider_ys/article/details/6590060 (.net 3種自動導出帶圖檔的excel方法)可以借鑒學習。