C# DataTable 导出excel 同时生成多个Sheet

m0_58873884

已于 2022-11-25 16:48:26 修改

阅读量1k

点赞数 2

文章标签： c# excel 开发语言

于 2022-11-25 16:12:09 首次发布

本文链接：https://blog.csdn.net/m0_58873884/article/details/128039747

版权

此篇博客介绍了如何使用C# DataTable实现大数据量下分页导出Excel，并创建多个工作簿，支持不同sheet。通过MemoryStream和HSSFWorkbook处理数据，适用于高效处理和管理大量数据。

摘要由CSDN通过智能技术生成

C# DataTable 导出excel 同时生成多个Sheet

数据量大的时候数据导出分页实现

public void ToExcel(DataTable table, string FileName)
	{
		SaveFileDialog saveFileDialog = new SaveFileDialog();
		//设置文件标题
		saveFileDialog.Title = "导出Excel文件";
		//设置文件类型
		saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
		//设置默认文件类型显示顺序  
		saveFileDialog.FilterIndex = 1;
		//是否自动在文件名中添加扩展名
		saveFileDialog.AddExtension = true;
		//是否记忆上次打开的目录
		saveFileDialog.RestoreDirectory = true;
		//设置默认文件名
		saveFileDialog.FileName = FileName;

		MemoryStream ms = new MemoryStream();

		//按下确定选择的按钮  
		if (saveFileDialog.ShowDialog() == DialogResult.OK)
		{
			//获得文件路径 
			string localFilePath = saveFileDialog.FileName.ToString();

			IWorkbook workbook;
			string FileExt = Path.GetExtension(localFilePath).ToLower();
			if (FileExt == ".xlsx")
			{
				workbook = new HSSFWorkbook();
			}
			else if (FileExt == ".xls")
			{
				workbook = new HSSFWorkbook();
			}
			else
			{
				workbook = null;
			}
			if (workbook == null)
			{
				return;
			}
	
			//string title = "";
			int sheetexportnum = 60000;
			FileStream fs = new FileStream(localFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
			IWorkbook workBook = new HSSFWorkbook();
			int count = table.Rows.Count;
			int sheetcount = 0;
			if (table.Rows.Count % sheetexportnum == 0)
			{
				sheetcount = Convert.ToInt16(Math.Floor(Convert.ToDouble(table.Rows.Count / sheetexportnum)));
			}
			else
			{
				sheetcount = Convert.ToInt16(Math.Floor(Convert.ToDouble(table.Rows.Count / sheetexportnum))) + 1;
			}
			
			for (int m = 1; m <= sheetcount; m++)
			{
				ISheet sheet = workBook.CreateSheet("sheet" + m.ToString());
                //处理表格标题
                IRow row = sheet.CreateRow(0);
                //row.CreateCell(0).SetCellValue(title);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                //row.Height = 500;

 

           //ICellStyle cellStyle = workBook.CreateCellStyle();
            //IFont font = workBook.CreateFont();
            //font.FontName = "微软雅黑";
            //font.FontHeightInPoints = 17;
            //cellStyle.SetFont(font);
            //row.Cells[0].CellStyle = cellStyle;
                string headname = "";
				//处理表格列头
				row = sheet.CreateRow(0);
				for (int i = 0; i < table.Columns.Count; i++)
				{
					headname = table.Columns[0].ColumnName;

					row.CreateCell(i).SetCellValue(headname);
					row.Height = 350;
					sheet.AutoSizeColumn(i);

				}
				int number = count - (sheetexportnum * (m - 1)) >= 60000 ? sheetexportnum : count - (sheetexportnum * (m - 1));
				//处理数据内容
				for (int i = 0; i < number; i++)
				{
					row = sheet.CreateRow(1 + i);
					row.Height = 250;
					for (int j = 0; j < table.Columns.Count; j++)
					{
						row.CreateCell(j).SetCellValue(table.Rows[sheetexportnum * (m - 1) + i][j].ToString());
						sheet.SetColumnWidth(j, 256 * 15);
					}
				}

			}
			//写入数据流
			workBook.Write(fs);
			fs.Flush();
			fs.Close();

			if (MessageBox.Show("导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
			{
				System.Diagnostics.Process.Start(localFilePath);
			}
		}

	
	}