Spreadsheet with template

I've run the following source code for paste my DataTable on a spreadsheet (Xlsx file)
Before do it, i copy my destinazione Xlsx file from My template (where i set some colours and particular format on cells)

After process is terminate, opening Xlsx file with Excel 2007 i get an alert pop-up that give me a warning about troubles on Xml files:
the message is:

EXcel found unreadable content in 'MyTest.xlsx'.
Do you want recover the contents of this workbook? If you trust the source of this workbook, click yes.

This problem exist only with a Template file with some settings (colours, Format, etc) but not with a Blank Template file (just a blank Xlsx file with general format on all cells)

I read some articles and documentation but i do not found a acceptable answer... someone can give me a hint...
or a right way to do a correct use of Template for Spreadsheet?
A great thank.

(Source Code)


String GeneralFilename = "c:\\temp\\MyExcelFile.xlsx";
String TemplateFilename = "c:\\temp\\MyTemplateFile.xlsx";

// Setting ProcessCreateExcel
bool FgDataHeader = false;
bool FgDataBody = true;
uint StartRowindexHeader = 1;
uint StartRowIndexBody = 3;
string WorkSheetName = "";

//Make a copy of the template file +
File.Copy(TemplateFilename, GeneralFilename, true);

//Open up the copied template workbook
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(GeneralFilename, true))
{
//Access the main Workbook part, which contains all references
WorkbookPart workbookPart = myWorkbook.WorkbookPart;

// Choose a certain WorkSheets (by name)
WorksheetPart worksheetPart;
if (this.txtsheetname.Text.Length != 0)
WorkSheetName = txtsheetname.Text;
worksheetPart = GetWorksheetPart(WorkSheetName, workbookPart);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();

// DataTable: insert and populate Datatable (Local) Disconnected
DataTable dt = new DataTable();
// populate Datatable (Local)
//DataTable dt = new DataTable();
dt.Columns.Add("valore1");
dt.Columns.Add("valore2");
dt.Columns.Add("valore3");
dt.Columns.Add("valore4");
dt.Columns.Add("valore5");
dt.Columns.Add("valore6");
string[] array = new string[6];
for (int i = 0; i
{
array[0] = "c.1 " + i.ToString();
array[1] = i.ToString();
array[2] = "c.2 " + i.ToString();
array[3] = "c.3 " + i.ToString();
array[4] = "c.4 " + i.ToString();
array[5] = "c.5 " + i.ToString();
dt.Rows.Add(array);
}


// Insert header ---------------------------------------------------------
if (FgDataHeader)
{
uint indexColumn = 0;
uint indexHeader = StartRowindexHeader;
if (this.txtRigaHeader.Text.Length != 0)
indexHeader = uint.Parse(txtRigaHeader.Text);

Row row1 = new Row();
//My Header starts at row indexHeader
row1.RowIndex = (UInt32Value)indexHeader;
uint j = indexHeader;
foreach (DataColumn dc in dt.Columns)
{
row1.Append(CreateRowContent(indexColumn, j, dc));
indexColumn++;
}
sheetData.Append(row1);
}


// Insert Body ---------------------------------------------------------
if (FgDataBody)
{
//My data starts at row StartRowIndexBody
uint index = StartRowIndexBody;
if (this.txtRigaData.Text.Length != 0)
index = uint.Parse(txtRigaData.Text);

//For each row in my database add a row to my spreadsheeet
foreach (DataRow dr in dt.Rows)
{
Row row2 = new Row();
row2.RowIndex = (UInt32Value)index;
uint i = 0;
foreach (object item in dr.ItemArray)
{
row2.Append(CreateRowContentItem(i, index, item));
i++;
}
index++;
sheetData.AppendChild(row2);
}
}
#endregion
workbookPart.Workbook.Save();

// Close the document.
myWorkbook.Close();
}

private Cell CreateRowContent(uint indexColumn, uint indexRow, DataColumn dc)
{
Cell c = new Cell();
c.CellReference = headerColumns[indexColumn] + indexRow;
c.DataType = CellValues.InlineString;
InlineString inlineString1 = new InlineString();
Text text1 = new Text();
text1.Text = dc.ColumnName.ToString();
inlineString1.Append(text1);
c.Append(inlineString1);
return c;
}

private Cell CreateRowContentItem(uint indexColumn, uint indexRow, Object item)
{
Cell c = new Cell();
c.CellReference = headerColumns[indexColumn] + indexRow;
c.DataType = CellValues.InlineString;
InlineString inlineString1 = new InlineString();
Text text1 = new Text();
text1.Text = item.ToString();
inlineString1.Append(text1);
c.Append(inlineString1);
return c;
}

Thanks for you answer.
I solve my problem in another way: my initial souce code definitively do not work...
So i start to implement Spreadsheet with template using Excel Package library: it works fine
but the performances are very bad, in fact for create about 11.000 rows ... occurs hours.

Any working code for manage inserting rows with template file (native open xml)? ...in a acceptable time?

Thanks.

Zimonec

Hi Zimonec

To troubleshoot your generated document's problem I would suggest first trying the Open XML SDK v2.0 Productivity Tool's Validate to see if you get any more detail.  Secondly, you can use the OpenXmlValidator which almost always provides great detail for what markup needs to be corrected.

If you get any progress with these validators you could post it up for any further guidance then :)