/// <summary>
/// Using openxml
/// </summary>
private void ExportToExcel()
{
// Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(ExportFileLocation + ddlTables.SelectedItem.ToString() + ".xlsx", true))
{
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
// Get the first worksheet.
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(2);
WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(0);
// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
// Begining Row pointer
int index = 2;
Row row = new Row();
row.RowIndex = (UInt32)1;
#region Making headers
for (int i = 0; i < dt.Columns.Count; i++)
{
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
cell.CellReference = Convert.ToChar(65 + i).ToString() + "1";
// Create Text object
Text t = new Text();
t.Text = dt.Columns[i].ColumnName;
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
// Append Row to SheetData
sheetData.AppendChild(row);
#endregion
// For each item in the database, add a Row to SheetData.
foreach (DataRow dr in dt.Rows)
{
// New Row
row = new Row();
row.RowIndex = (UInt32)index;
for (int i = 0; i < dt.Columns.Count; i++)
{
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
cell.CellReference = Convert.ToChar(65 + i).ToString() + index;
// Create Text object
Text t = new Text();
t.Text = dr[i].ToString();
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
// Append Row to SheetData
sheetData.AppendChild(row);
// increase row pointer
index++;
}
// save
worksheetPart.Worksheet.Save();
myWorkbook.Dispose();
}
}
