I am trying to create an application that can extract a ton of data from a txt file 3000 lines.
I have already filtered the data etc and I am able to write everything into the excel spreadsheet in the correct way. My problem is that I want to split the data over multiple sheets and limit each sheet to a specific number of data rows (The exact value is a number chosen between 100-1000)
I have ended up with an output that creates the correct number of sheets, but it outputs all the lines of data into all the sheets instead of splitting them up over all the sheets.
For clarity: Example: I have 950 lines of data. Thus the output should be 100 data rows in 8 sheets and the 9th should have the last 50.
What I currently get is: 950 data rows in all 9 sheets.
Here is my code that controls the Excel creation.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var workSheet = worksheetPart.Worksheet;
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
for (int i = 0; i < Amount; i )
{
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = ((uint)(i 1)),
Name = "Data" (i 1)
};
sheets.Append(sheet);
var HeadRow = workSheet.GetFirstChild<SheetData>().AppendChild(new Row());//new Row
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Type"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Description"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Time"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Date"), DataType = new EnumValue<CellValues>(CellValues.String) });
for (int j = 0 (i * (int)OrgAmount); j < ((i 1) * (int)OrgAmount); j )//Rows
{
if (j == LineCount)
{
break;
}
var row = workSheet.GetFirstChild<SheetData>().AppendChild(new Row());//new Row
var cell1 = row.AppendChild(new Cell());//Type
var cell2 = row.AppendChild(new Cell());//Description
var cell3 = row.AppendChild(new Cell());//Time
var cell4 = row.AppendChild(new Cell());//Date
cell1.CellValue = new CellValue(LineType[j]);
cell1.DataType = new EnumValue<CellValues>(CellValues.String);
cell2.CellValue = new CellValue(LineDesc[j]);
cell2.DataType = new EnumValue<CellValues>(CellValues.String);
cell3.CellValue = new CellValue(LineTime[j]);
cell3.DataType = new EnumValue<CellValues>(CellValues.String);
cell4.CellValue = new CellValue(LineDate[j]);
cell4.DataType = new EnumValue<CellValues>(CellValues.String);
}
workbookPart.Workbook.Save();
}
spreadsheetDocument.Close();
}
Please help me since I don't know where I messed up.
CodePudding user response：
I got it fixed myself in the end.
Here is what I did:
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
//Create workbook, workbookPart and sheets
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
for (int i = 0; i < Amount; i )
{
//New worksheetPart, worksheet and sheet for each sheet to be addressed seperately
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var workSheet = worksheetPart.Worksheet;
//Dynamically create a sheet according to options chosen in form
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = ((uint)(i 1)),
Name = "Data" (i 1)
};
//Header row creation
var HeadRow = workSheet.GetFirstChild<SheetData>().AppendChild(new Row());//new Row
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Type"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Description"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Time"), DataType = new EnumValue<CellValues>(CellValues.String) });
HeadRow.AppendChild(new Cell() { CellValue = new CellValue("Date"), DataType = new EnumValue<CellValues>(CellValues.String) });
for (int j = 0 (i * (int)OrgAmount); j < ((i 1) * (int)OrgAmount); j )//Rows
{
if (j == LineCount)//If final line is reached before loop end, then break loop
{
break;
}
//Initialise new row cells
var row = workSheet.GetFirstChild<SheetData>().AppendChild(new Row());//new Row
var cell1 = row.AppendChild(new Cell());//Type
var cell2 = row.AppendChild(new Cell());//Description
var cell3 = row.AppendChild(new Cell());//Time
var cell4 = row.AppendChild(new Cell());//Date
//Write data to cells, extracted from text file
cell1.CellValue = new CellValue(LineType[j]);
cell1.DataType = new EnumValue<CellValues>(CellValues.String);
cell2.CellValue = new CellValue(LineDesc[j]);
cell2.DataType = new EnumValue<CellValues>(CellValues.String);
cell3.CellValue = new CellValue(LineTime[j]);
cell3.DataType = new EnumValue<CellValues>(CellValues.String);
cell4.CellValue = new CellValue(LineDate[j]);
cell4.DataType = new EnumValue<CellValues>(CellValues.String);
}
sheets.Append(sheet);//Append the written sheet to workbook sheets
}
workbookPart.Workbook.Save();//save workbook
spreadsheetDocument.Close();//Close document
}