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

最新推荐文章于 2024-07-26 16:48:19 发布

小鸿叔

最新推荐文章于 2024-07-26 16:48:19 发布

阅读量6.1k

点赞数 5

分类专栏： C# 文章标签： DataTable 写入 Excel 极速快速

本文链接：https://blog.csdn.net/jyh_jack/article/details/99560851

版权

C# 专栏收录该内容

66 篇文章 3 订阅

订阅专栏

/// <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");