C#：将DataTable里的内容极速写入到EXCEL中并保存

 

/// <summary>
/// 将DataTable里面的内容，写到EXCEL，极速写入。
/// </summary>
/// <param name="DTs">DataTable们</param>
/// <param name="SheetNames">Sheet的命名</param>
/// <param name="FileName">保存的文件名</param>
/// <returns></returns>
private bool DataTable2Excel(DataTable[] DTs,string[] SheetNames,string FileName)
{
	if (DTs.Length != SheetNames.Length)
	{
		return false;
	}

	Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
	if (xlApp == null)
	{
		SetHint("可能机器未安装Excel！", HintType.Error);
		return false;
	}

	Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
	Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
	//增加Sheet
	for (int iSht = 1; iSht < DTs.Length; iSht++)
	{
		xlBook.Sheets.Add();
	}

	xlApp.Visible = true;

	for (int iArr = 0; iArr < DTs.Length; iArr++)
	{
		Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[iArr+1];
		//数据写数组
		object[,] objData = new object[DTs[iArr].Rows.Count + 1, DTs[iArr].Columns.Count];
		//--ColumnName
		for (int iDT = 0; iDT < DTs[iArr].Columns.Count; iDT++)
		{
			objData[0, iDT] = DTs[iArr].Columns[iDT].ColumnName;
		}
		//--Data
		for (int iRow = 0; iRow < DTs[iArr].Rows.Count; iRow++)
		{
			for (int iColumn = 0; iColumn < DTs[iArr].Columns.Count; iColumn++)
			{
				objData[iRow + 1, iColumn] = DTs[iArr].Rows[iRow][iColumn];
			}
		}

		//Excel Column Name
		string startCol = "A";
		int iCnt = (DTs[iArr].Columns.Count / 26);
		string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
		string endCol = endColSignal + ((char)('A' + DTs[iArr].Columns.Count - iCnt * 26 - 1)).ToString();

		Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(
			startCol + "1", endCol + (DTs[iArr].Rows.Count - iCnt * 26 + 1).ToString()
			);
		range.NumberFormatLocal = "@";
		range.Value = objData;
		range.EntireColumn.AutoFit(); //设定Excel列宽度自适应  
		//Excel文件列名 字体设定为Bold  
		xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1;
		xlSheet.Name = SheetNames[iArr];
	}

	xlApp.DisplayAlerts = false;
	xlApp.AlertBeforeOverwriting = false;
	xlBook.SaveAs(FileName);
	xlApp.Quit();

	return true;
}

调用

string sql = "Select * from Table1";
DataTable dt = sdh.getDataSet(sql).Tables[0];
sql = "Select * from Table2";
DataTable dt2 = sdh.getDataSet(sql).Tables[0];
DataTable2Excel(new DataTable[] { dt, dt2 }, new string[] { "Sheet1", "Sheet2"},"C:\aaa.xlsx");