Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Friends,
I am using Visual studio 2019 and .netcore 3.1.
how to generate single excel sheet in an excel file from multiple datasets,
I am using DocumentFormat.OpenXml nuget package.
I want to first display the contents from table1 from dataset1.
Then I want to display the contents from table2 from dataset1.
Next I want to display the contents from table1 from dataset2.
next I want to display the contents from table2 from dataset2. and so on
This should be done in single excel sheet in single excel file.

Thanks in advance
George

What I have tried:

I am using DocumentFormat.OpenXml nuget package.
I have used the following code
private const string noRecordsToDisplay = "No records to display";
	
	public static byte[] ExportToExcelDownload(DataSet dataSet)
	{
		byte[] byteResult = null;
		if (dataSet == null) { return byteResult; }

		if (dataSet.Tables.Count > 0)
		{
			using (MemoryStream stream = new MemoryStream())
			{
				using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
				{
					// Add a WorkbookPart to the document.
					WorkbookPart workbookpart = AddWorkbookPart(spreadsheetDocument);
					AddSheet(spreadsheetDocument, out Sheets sheets, out uint currentSheetID);
					AddNewPartStyle(workbookpart);

					int rowIndexCount = 1;

					foreach (DataTable dt in dataSet.Tables)
					{
						// Add a WorksheetPart to the WorkbookPart.
						WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
						worksheetPart.Worksheet = new Worksheet();
						Columns columns = SetDefaultColumnWidth();
						worksheetPart.Worksheet.Append(columns);

						SheetData sheetData = new SheetData();
						worksheetPart.Worksheet.AppendChild(sheetData);

						// Append a new worksheet and associate it with the workbook.
						Sheet sheet = new Sheet()
						{
							Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
							SheetId = currentSheetID,
							Name = string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet" + currentSheetID : dt.TableName
						};

						if (dt.Rows.Count == 0)
						{
							//if table rows count is 0, create Excel Sheet with default message
							CreateDefaultWithMessage(rowIndexCount, sheetData);
						}
						else
						{
							int numberOfColumns = dt.Columns.Count;
							string[] excelColumnNames = new string[numberOfColumns];

							//Create Header
							Row SheetrowHeader = CreateHeader(rowIndexCount, dt, numberOfColumns, excelColumnNames);
							sheetData.Append(SheetrowHeader);
							++rowIndexCount;

							//Create Body
							rowIndexCount = CreateBody(rowIndexCount, dt, sheetData, excelColumnNames);
						}

						sheets.Append(sheet);

						++currentSheetID;

						rowIndexCount = 1;
					}

					workbookpart.Workbook.Save();

					// Close the document.
					//spreadsheetDocument.Close();

				}

				stream.Flush();
				stream.Position = 0;

				byteResult = new byte[stream.Length];
				stream.Read(byteResult, 0, byteResult.Length);
			}
		}
		return byteResult;
	}
Posted
Updated 21-Feb-23 3:42am
v3
Comments
OriginalGriff 21-Feb-23 9:30am    
And?
What have you tried?
Where are you stuck?
What help do you need?

Use the "Improve question" widget to edit your question and provide better information.
Graeme_Grant 21-Feb-23 10:25am    
"I am using Visual studio 2019 and .netcore 3.1." .... WHY??? Microsoft's official support for 3.1 ended in December last year. Read more here: .NET and .NET Core official support policy[^].

At least update to 6.0 for LTS or 7.0. If you're not locked in with corporate policies, then update to VS 2022 ... Community Edition is FREE.
[no name] 21-Feb-23 20:43pm    
Just keep adding more "CreateBody(...)" instead of creating more sheets.
george@84 24-Feb-23 6:16am    
This helped me to implement successfully

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900