//new
public void ExportDSToExcel(DataSet ds, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
/// <summary>
/// Adds a new worksheet to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="name">Name of the worksheet</param>
/// <returns>True if succesful</returns>
public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name)
{
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;
// Add the worksheetpart
worksheetPart = spreadsheet.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
worksheetPart.Worksheet.Save();
// Add the sheet and make relation to workbook
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
spreadsheet.WorkbookPart.Workbook.Save();
return true;
}
/*private Row createContentRow(
DataRow dataRow,
int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}*/
private static SpreadsheetDocument CreateDoc(SheetData data)
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
doc.AddWorkbookPart();
doc.WorkbookPart.Workbook = new Workbook();
doc.WorkbookPart.Workbook.Save();
doc.WorkbookPart.Workbook.Sheets = new Sheets();
doc.WorkbookPart.Workbook.Save();
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild<Sheets>();
// Add the worksheetpart
WorksheetPart worksheetPart = doc.WorkbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(data);
worksheetPart.Worksheet.Save();
// Add the sheet and make relation to workbook
Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(doc.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = "sheet"
};
sheets.Append(sheet);
doc.WorkbookPart.Workbook.Save();
return doc;
}
}
public static void ExportDSToExcel(System.Data.DataSet ds, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
public static WorksheetPart WorkSheet(this SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet)
{
return(spreadsheet.WorkbookPart.GetPartById(oXmlSheet.Id) as WorksheetPart);
}
static public bool ExportarExcel(DataSet ds, string archivo)
{
try
{
using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
DateTime val;
string dato;
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
dato = dsrow[col].ToString();
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(archivo);
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(false);
}
}
static public bool ExportaListaAExcel2 <T>(List <T> lista, string archivo, string hoja)
{
try
{
using (var workbook = SpreadsheetDocument.Create(archivo, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = hoja
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
IList <PropertyInfo> campos = new List <PropertyInfo>(typeof(T).GetProperties());
foreach (PropertyInfo campo in campos)
{
columns.Add(campo.Name);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(campo.Name);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (T row in lista)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
DateTime val;
string dato;
foreach (String col in columns)
{
PropertyInfo campo = campos.ToList().Find(x => x.Name == col);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
object obj = campo.GetValue(row, null);
if (obj == null)
{
dato = "";
}
else
{
dato = obj.ToString();
}
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(archivo);
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(false);
}
}
public async override Task ExecuteResultAsync(ActionContext context)
{
var response = context.HttpContext.Request;
var enumerable = _data as System.Collections.IEnumerable;
if (enumerable == null)
{
throw new ArgumentException("IEnumerable type required");
}
byte[] FileContents = null;
using (MemoryStream mem = new MemoryStream()) {
using (var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) {
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart> ();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets> ();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Count() > 0)
{
sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = "Sheet1"
};
sheets.Append(sheet);
//add logo
string imgPath = "assets/logo.png";
var drawingsPart = sheetPart.AddNewPart <DrawingsPart> ();
if (!sheetPart.Worksheet.ChildElements.OfType <Drawing> ().Any())
{
sheetPart.Worksheet.Append(new Drawing {
Id = sheetPart.GetIdOfPart(drawingsPart)
});
}
if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
}
var worksheetDrawing = drawingsPart.WorksheetDrawing;
var imagePart = drawingsPart.AddImagePart(ImagePartType.Png);
using (var stream = new FileStream(imgPath, FileMode.Open)) {
imagePart.FeedData(stream);
}
Bitmap bm = new Bitmap(imgPath);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float)31440 / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float)31440 / bm.VerticalResolution);
bm.Dispose();
var colOffset = 2;
var rowOffset = 0;
int colNumber = 4;
int rowNumber = 1;
var nvps = worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ();
var nvpId = nvps.Count() > 0 ?
(UInt32Value)worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ().Max(p => p.Id.Value) + 1 :
1U;
var oneCellAnchor = new DocumentFormat.OpenXml.Drawing.Spreadsheet.OneCellAnchor(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker {
ColumnId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnId((colNumber - 1).ToString()),
RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((rowNumber - 1).ToString()),
ColumnOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnOffset(colOffset.ToString()),
RowOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset(rowOffset.ToString())
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Extent {
Cx = extentsCx, Cy = extentsCy
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties {
Id = nvpId, Name = "Picture " + nvpId, Description = imgPath
},
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks {
NoChangeAspect = true
})
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(
new DocumentFormat.OpenXml.Drawing.Blip {
Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
},
new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle())
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(
new DocumentFormat.OpenXml.Drawing.Transform2D(
new DocumentFormat.OpenXml.Drawing.Offset {
X = 0, Y = 0
},
new DocumentFormat.OpenXml.Drawing.Extents {
Cx = extentsCx, Cy = extentsCy
}
),
new DocumentFormat.OpenXml.Drawing.PresetGeometry {
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
}
)
),
new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData()
);
worksheetDrawing.Append(oneCellAnchor);
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
//company name
DocumentFormat.OpenXml.Spreadsheet.Row company = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell cellCompany = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
StyleIndex = (UInt32Value)1U
};
cellCompany.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cellCompany.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("FOOD, MEDICINE AND HEALTH CARE ADMINISTRATION AND CONTROL AUTHORITY OF ETHIOPIA");
company.AppendChild(cellCompany);
sheetData.AppendChild(company);
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
//header
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var column in _columns)
{
if (!(bool)column["IsVisible"])
{
continue;
}
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column["Title"].ToString());
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in enumerable)
{
IDictionary <string, object> row = (IDictionary <string, object>)item;
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var header in _columns)
{
if (!(bool)header["IsVisible"])
{
continue;
}
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
var value = row[header["FieldName"].ToString()];
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
//geenrated by
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
DocumentFormat.OpenXml.Spreadsheet.Row footer = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell footerCell = new DocumentFormat.OpenXml.Spreadsheet.Cell(new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset("4"));
footerCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
footerCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("Generated By eRIS");
footer.AppendChild(footerCell);
sheetData.AppendChild(footer);
sheetPart.Worksheet.Save();
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
FileContents = mem.ToArray();
var contentResult = new FileContentResult(FileContents, this.ContentType);
await contentResult.ExecuteResultAsync(context);
//await response.Body.WriteAsync(FileContents, 0, FileContents.Length);
}
}
}
public void X002_XlsxCreation()
{
FileInfo fi = new FileInfo(Path.Combine(s_TestFileLocation, Guid.NewGuid().ToString() + ".docx"));
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument doc = SpreadsheetDocument.Create(fi.FullName, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = doc.AddWorkbookPart();
workbookpart.Workbook = new S.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<S.Sheets>(new S.Sheets());
S.Sheet sheet = new S.Sheet()
{
Id = doc.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
var errs = v.Validate(doc);
Assert.Equal(0, errs.Count());
doc.Close();
if (TestUtil.DeleteTempFiles)
fi.Delete();
}
public void X003_XlsxCreation_Stream()
{
using (MemoryStream ms = new MemoryStream())
// By default, AutoSave = true, Editable = true, and Type = xlsx.
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = doc.AddWorkbookPart();
workbookpart.Workbook = new S.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<S.Sheets>(new S.Sheets());
S.Sheet sheet = new S.Sheet()
{
Id = doc.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
var errs = v.Validate(doc);
Assert.Equal(0, errs.Count());
}
}
public override byte[] Render(Base.Report report)
{
byte[] buffer;
using (var outputMemoryStream = new MemoryStream())
{
using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
{
// Add a WorkbookPart to the document.
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
// Add a WorkbookStylesPart to the WorkbookPart
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);
uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;
var styles = report.Select(o => o.Style).ToList();
//нельзя перенести в отдельный метод :(
foreach (var s in styles)
{
if (s is TableStyle)
{
var style = s as TableStyle;
var border = new DocumentFormat.OpenXml.Spreadsheet.Border();
var leftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
var rightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
var topBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();
leftBorder.Style = SetLineType(style.BorderLine);
rightBorder.Style = SetLineType(style.BorderLine);
topBorder.Style = SetLineType(style.BorderLine);
bottomBorder.Style = SetLineType(style.BorderLine);
leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(style.BorderColor)
};
border.LeftBorder = leftBorder;
border.RightBorder = rightBorder;
border.TopBorder = topBorder;
border.BottomBorder = bottomBorder;
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);
var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
{
BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
Rgb = ToHexBinaryValue(style.Background)
},
ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
Rgb = ToHexBinaryValue(style.Foreground)
},
PatternType = SetPatternType(style.PatternType)
};
var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
PatternFill = patternFill
};
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
else
{
stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
DocumentFormat.OpenXml.Spreadsheet.Font font;
if (s != null)
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font
{
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
Val = new StringValue {
Value = s.FontName
}
},
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
Val = s.FontSize
},
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = ToHexBinaryValue(s.FontColor)
}
};
foreach (var item in s.TextStyle)
{
switch (item)
{
case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
Val = true
}; break;
case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
Val = true
}; break;
case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
}; break;
case TextStyleType.Normal: break;
}
}
}
else
{
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
}
stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
}
if (s != null)
{
var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
{
BorderId = UInt32Value.ToUInt32(styleId),
FillId = UInt32Value.ToUInt32(styleId),
FontId = UInt32Value.ToUInt32(styleId)
};
if (s.Aligment != null)
{
var align = new Alignment()
{
TextRotation = new UInt32Value(s.Aligment.Rotation),
Horizontal = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
Vertical = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
};
cellFormat.Append(align);
}
stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
}
styleId = styleId + 1;
}
foreach (var x in report)
{
if (x is ComplexHeaderCell)
{
var element = x as ComplexHeaderCell;
if (Render(element, styles, shData, worksheetPart, workbookpart))
{
continue;
}
}
if (x is TextElement)
{
var element = x as TextElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is TableElement)
{
var element = x as TableElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
if (x is ImageElement)
{
var element = x as ImageElement;
if (Render(element, styles, shData, workbookpart))
{
continue;
}
}
}
//var dateLine = shData.AppendChild(new Spreadsheet.Row());
//dateLine.AppendChild(new Spreadsheet.Cell()
//{
// CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
// DataType = Spreadsheet.CellValues.String,
// StyleIndex = 0
//});
workbookpart.Workbook.Save();
}
buffer = new byte[outputMemoryStream.Position];
outputMemoryStream.Position = 0;
outputMemoryStream.Read(buffer, 0, buffer.Length);
}
return(buffer);
}
/// <summary>
/// Deletes the supplied sheet instance from the workbook part
/// </summary>
/// <param name="workbookPart">The workbook part to delete the sheet from</param>
/// <param name="sheet">The sheet to delete</param>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sheet
/// </exception>
public static void DeleteSheet(this WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
if (workbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
// get the id of the sheet for deletion
Int32Value sheetId = Int32Value.FromInt32((int)sheet.SheetId.Value);
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
worksheetPart.Worksheet.Save();
}
sheet.Remove();
// Delete the worksheet part.
workbookPart.DeletePart(worksheetPart);
// Get the CalculationChainPart
// Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
// workbook whose value is calculated from any formula
CalculationChainPart calChainPart = workbookPart.CalculationChainPart;
if (calChainPart != null)
{
List <CalculationCell> forRemoval = new List <CalculationCell>();
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().ToList();
foreach (CalculationCell item in calChainEntries)
{
if (item.SheetId == null)
{
item.Remove();
}
else if (item.SheetId.HasValue && item.SheetId.Value.Equals(sheetId))
{
item.Remove();
}
}
if (calChainPart.CalculationChain.Count() == 0)
{
workbookPart.DeletePart(calChainPart);
}
}
workbookPart.Workbook.Save();
}
private void Export()
{
if (ResultModel != null)
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Save Result";
saveDialog.Filter = "Excel 97-2003 WorkBook|*.xls|Excel WorkBook|*.xlsx|All Excel Files|*.xls;*.xlsx|All Files|*.*";
if ((bool)saveDialog.ShowDialog())
{
using (var workbook = SpreadsheetDocument.Create(saveDialog.FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
var table = ResultModel;
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
MessageBox.Show("File Saved Successfully");
}
}
}
public void DumpSheetRange <T>(T output, string filename, string sheetName, string firstCellName, string lastCellName) where T : TextWriter
{
// Create instance of OpenSettings
OpenSettings openSettings = new OpenSettings
{
// Add the MarkupCompatibilityProcessSettings
MarkupCompatibilityProcessSettings =
new MarkupCompatibilityProcessSettings(
MarkupCompatibilityProcessMode.ProcessAllParts,
FileFormatVersions.Office2013)
};
// Open the document with OpenSettings
using (SpreadsheetDocument excelDocument =
SpreadsheetDocument.Open(filename,
false,
openSettings))
{
var wbPart = excelDocument.WorkbookPart;
Sheets sheets = wbPart.Workbook.Sheets;
foreach (E sheetElem in sheets)
{
foreach (A attr in sheetElem.GetAttributes())
{
output.Write("{0}: {1}\t", attr.LocalName, attr.Value);
}
output.WriteLine();
}
S sheet = wbPart.Workbook.Descendants <S>().Where(s => s.Name == sheetName).First();
// Throw an exception if there is no sheet.
if (sheet == null)
{
throw new ArgumentException("sheetName is invalid for this workbook");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)wbPart.GetPartById(sheet.Id);
W worksheet = wsPart.Worksheet;
var stringTable =
wbPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
uint firstRowNum = ColumnComparer.GetRowIndex(firstCellName);
uint lastRowNum = ColumnComparer.GetRowIndex(lastCellName);
string firstColumn = ColumnComparer.GetColumnName(firstCellName);
string lastColumn = ColumnComparer.GetColumnName(lastCellName);
string headers = firstColumn;
while (ColumnComparer.CompareColumn(headers, lastColumn) <= 0)
{
output.Write("{0}\t", headers);
headers = ColumnComparer.NextColumn(headers);
}
output.WriteLine();
//// Iterate through the cells within the range and do whatever.
foreach (int row in Enumerable.Range((int)firstRowNum, (int)lastRowNum))
{
string col = firstColumn;
while (ColumnComparer.IsColumnInRange(firstColumn, lastColumn, col))
{
var cellRef = String.Format("{0}{1}", col, row);
var cell = wsPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
output.Write("{0}\t", DecodedCell(cell, stringTable));
col = ColumnComparer.NextColumn(col);
}
output.WriteLine();
}
}
}
/// <summary>
/// Adds a new worksheet to the spreadsheet
/// </summary>
/// <param name="spreadsheet">The spreadsheet</param>
/// <param name="name">name of the worksheet</param>
/// <returns>True if success</returns>
public bool AddWorkSheet(string name)
{
Sheets sheets = this._spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
Sheet sheet;
WorksheetPart worksheetPart;
//Add the worksheetpart
worksheetPart = this._spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Save();
// Add the sheet to workbook
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = this._spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(this._spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
this._spreadsheet.WorkbookPart.Workbook.Save();
return true;
}
/// <summary>
/// Updates a SpreadsheetDocument with new tabular data.
/// </summary>
/// <param name="ssdoc">
/// The excel document to update.</param>
/// <param name="json">
/// String in JSON format representing the tabular data for updating the Chart's cached data points.
/// The JSON object must contain a "fields" attribute as an array containing the field/column names.
/// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
/// This is the same data as the underlying Excel spreadsheet contents.</param>
/// <param name="sheetName">
/// The name of the Excel worksheet where the chart data originates from.
/// Used for updating the chart's cell references.</param>
/// <returns>
/// Returns the updated SpreadsheetDocument</returns>
public static SpreadsheetDocument Update(this SpreadsheetDocument ssdoc, string json, string sheetName)
{
if ((json == null) || (json == String.Empty))
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
}
//Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
dynamic input = JsonConvert.DeserializeObject <dynamic>(json);
if (input["rows"].Count == 0)
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
input = JsonConvert.DeserializeObject <dynamic>(json);
}
ss.Sheet sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().Where(s => s.Name.ToString() == sheetName).FirstOrDefault();
if (sheet == null)
{
sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().FirstOrDefault();
}
WorksheetPart worksheet = (WorksheetPart)ssdoc.WorkbookPart.GetPartById(sheet.Id);
ss.SheetData data = worksheet.Worksheet.GetFirstChild <ss.SheetData>();
//Remove all the rows after our column headers row. We'll replace them with new rows as the table is populated from the Splunk search results.
ss.Row firstRow = data.Elements <ss.Row>().First();
while (firstRow.NextSibling <ss.Row>() != null)
{
firstRow.NextSibling <ss.Row>().Remove();
}
ss.Row newHeader = new ss.Row();
newHeader.DyDescent = 0.25;
newHeader.RowIndex = 1;
var columnNames = input["fields"];
char startingHeaderColumn = 'A';
foreach (var column in columnNames)
{
string cellRef = startingHeaderColumn.ToString() + 1;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(column.ToString());
newCell.CellReference = cellRef;
newCell.DataType = ss.CellValues.String;
newCell.Append(cv);
newHeader.Append(newCell);
startingHeaderColumn++;
}
data.InsertAfter(newHeader, data.Elements <ss.Row>().Last());
data.RemoveChild(firstRow);
for (int i = 0; i < input["rows"].Count; i++)
{
char startingColumn = 'A';
int startingColumnVal = 1;
int endingColumnVal = 0;
//Set the Excel row index (Excel index starts at 1, not zero and row 1 has headers so add 2 to the for index)
uint rowIndex = Convert.ToUInt32(i) + 2;
ss.Row newRow = new ss.Row();
newRow.DyDescent = 0.25;
newRow.RowIndex = rowIndex;
Debug.WriteLine(String.Format("Writing Excel Row {0}", i + 1));
var row = input["rows"][i];
foreach (var cell in row)
{
string cellRef = startingColumn.ToString() + rowIndex;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(cell.ToString());
newCell.CellReference = cellRef;
if (startingColumn == 'A')
{
newCell.DataType = ss.CellValues.String;
}
newCell.Append(cv);
newRow.Append(newCell);
startingColumn++;
endingColumnVal++;
}
int numberOfCellsInRow = newRow.Descendants <ss.Cell>().Count();
ListValue <StringValue> spans = new ListValue <StringValue>();
spans.Items.Add(string.Format("{0}:{1}", startingColumnVal, endingColumnVal));
newRow.Spans = spans;
data.InsertAfter(newRow, data.Elements <ss.Row>().Last());
}
// Update Table Reference
Debug.WriteLine("Updating Table");
var table = worksheet.TableDefinitionParts.First().Table;
table.Reference = string.Format("A1:{0}{1}", GetExcelColumnName(input["fields"].Count), input["rows"].Count + 1);
table.TableColumns.RemoveAllChildren();
for (int i = 0; i < columnNames.Count; i++)
{
var newColumn = new ss.TableColumn();
newColumn.Id = new UInt32Value((uint)i + 1);
newColumn.Name = new StringValue(columnNames[i].ToString());
table.TableColumns.Append(newColumn);
}
return(ssdoc);
}