Multiple Sheet using OpenXML

[Anu Viswan]

Hi All


I am working on a code that will create a spreadsheet with multiple sheets. I am using following code for it, but I end up having same data in all sheets. Can you please help ?


public COpenXMLWrapper()
{
m_WorkSheetID.Value = 0;
m_SpreadSheetStream = new MemoryStream();
m_SpreadSheetDoc = SpreadsheetDocument.Create(m_SpreadSheetStream,SpreadsheetDocumentType.Workbook);
}

public void AddWorkbook()
{
// Add a WorkbookPart to the document
if (m_WorkBookPart == null)
{
m_WorkBookPart = m_SpreadSheetDoc.AddWorkbookPart();
m_WorkBookPart.Workbook = new Workbook();
}

}

public void CloseWorkbook()
{

m_SpreadSheetDoc.Close();
}

public void AddWorksheet(string WorksheetName)
{
// Add a WorksheetPart to theWorkbookPart

m_WorkSheetPart = m_WorkBookPart.AddNewPart<WorksheetPart>();
m_WorkSheetPart.Worksheet = new Worksheet();

m_SheetData = new SheetData();
if(m_Sheets == null)
m_Sheets = m_SpreadSheetDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

m_WorkSheetStylePart = m_WorkBookPart.AddNewPart<WorkbookStylesPart>();
m_WorkSheetStylePart.Stylesheet = CreateStylesheet();
m_WorkSheetStylePart.Stylesheet.Save();



Sheet sheet = new Sheet()
{
Id = m_SpreadSheetDoc.WorkbookPart.GetIdOfPart(m_WorkSheetPart),
SheetId = ++m_WorkSheetID.Value,
Name = WorksheetName
};
m_Sheets.Append(sheet);

}

/// <summary>
/// Save the Stream to File
/// </summary>
/// <param name="FullPathFilename </param>
public void SaveWorkbook(string FullPathFilename)
{
m_WorkSheetPart.Worksheet.Append(m_SheetData);
m_WorkBookPart.Workbook.Save();


this.CloseWorkbook();
m_SpreadSheetStream.WriteTo(new System.IO.FileStream(FullPathFilename, System.IO.FileMode.Create));
m_SpreadSheetStream.Flush();
m_SpreadSheetStream.Close();

}

public void AddRowToWorksheet(int RowID, string StartRange, string EndRange, string[] CellArr)
{
string cellId = string.Empty;
DateTime rowDate;
double rowNum;

Row CurrentRow = new Row();
Cell cellToAdd = null;

for (int colIdx = 0; colIdx < CellArr.Length; colIdx++)
{
cellId = string.Format("{0}{1}", this.DetermineEndCellPrefix(colIdx + 1), RowID); ;

if (double.TryParse(CellArr[colIdx],out rowNum))
{
cellToAdd = new Cell()
{
CellReference = cellId,
StyleIndex = 0
};
cellToAdd.Append(new CellValue(rowNum.ToString()));
}
else
if(DateTime.TryParse(CellArr[colIdx],out rowDate))
{
cellToAdd = new Cell()
{
CellReference = cellId,
StyleIndex = 1
};
cellToAdd.Append(new CellValue(rowDate.ToOADate().ToString()));
}
else
{
cellToAdd = new Cell()
{
CellReference = cellId,
StyleIndex = 6,
DataType = CellValues.String
};
cellToAdd.Append(new CellValue(CellArr[colIdx]));
}
CurrentRow.Append(cellToAdd);

}

m_SheetData.Append(CurrentRow);


//worksheetPart.Worksheet.Append(columns);

}


Anu Viswan : www.AnuViswan.blogspot.com

Continue reading...