private static void ExportTable(System.Data.DataTable table, SpreadsheetDocument workbook)
{
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);
}
}
/// <summary>
/// Skips specified number of cells.
/// </summary>
/// <param name="count">Number of cells to skip.</param>
/// <returns>True if success.</returns>
public bool SkipCells(int count)
{
if (count <= 0)
{
throw new ArgumentOutOfRangeException("count", "Cell count cannot be zero or negative value");
}
for (int i = 0; i < count; i++)
{
if (currentCellPosition < currentCellIndex)
{
// This cell does not exist in worksheet (the cell is empty)
currentCellPosition++;
continue;
}
if (!reader.EOF && reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Cell) && reader.IsStartElement)
{
// Read next available cell
currentCell = (DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement();
currentCellIndex = ColumnNameToIndex(GetColumnNameFromCellReference(currentCell.CellReference));
currentCellPosition++;
// Move to the next cell
reader.ReadNextSibling();
}
else
{
return(false);
}
}
return(true);
}
private string GetExcelCellValue(DocumentFormat.OpenXml.Spreadsheet.Cell cell, DocumentFormat.OpenXml.Packaging.WorkbookPart wbpart)
{
string retVal = string.Empty;
if (cell.DataType != null)
{
if (cell.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
int id = -1;
if (Int32.TryParse(cell.InnerText, out id))
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringItem item = GetSharedStringItemById(wbpart, id);
if (item.Text != null)
{
retVal = item.Text.Text;
}
else if (item.InnerText != null)
{
retVal = item.InnerText;
}
else if (item.InnerXml != null)
{
retVal = item.InnerXml;
}
}
}
else
{
retVal = cell.InnerText;
}
}
return(retVal);
}
private void CleanupResources()
{
fileName = null;
currentCell = null;
if (reader != null)
{
reader.Close();
reader.Dispose();
reader = null;
}
if (spreadsheetDocument != null)
{
spreadsheetDocument.Close();
spreadsheetDocument.Dispose();
spreadsheetDocument = null;
}
if (fileStream != null)
{
fileStream.Close();
fileStream.Dispose();
fileStream = null;
}
sharedStrings = null;
}
/// <summary>
/// Reads decimal number from the next cell.
/// </summary>
/// <returns>Null value if cell is empty.</returns>
public decimal?ReadCellDecimalNumberValue(ExcelOpenXmlDecimalSeparator separator, bool convertSeparator)
{
decimal?result = null;
if (currentCell != null)
{
if (currentCellIndex == currentCellPosition)
{
DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;
string s = GetCurrentCellAsString();
if (s == null)
{
if (c.CellValue != null)
{
s = c.CellValue.InnerText;
}
}
if (s != null)
{
if (convertSeparator)
{
switch (separator)
{
case ExcelOpenXmlDecimalSeparator.Dot:
s = s.Replace(',', '.');
break;
case ExcelOpenXmlDecimalSeparator.Comma:
s = s.Replace('.', ',');
break;
}
}
try
{
result = decimal.Parse(s, System.Globalization.NumberStyles.Float, separator == ExcelOpenXmlDecimalSeparator.Dot ? dotNumberFormatInfo : commaNumberFormatInfo);
////result = Convert.ToDecimal(s, separator == ExcelOpenXmlDecimalSeparator.Dot ? dotNumberFormatInfo : commaNumberFormatInfo);
}
catch
{
throw new FormatException(string.Format(
"Cell value \"{0}\" at {1} is not a decimal number in \"{2}\"",
s,
GetCellReference(currentRowPosition, currentCellPosition),
fileName));
}
}
}
}
if (!SkipCells(1))
{
currentCell = null;
currentCellIndex = 0;
}
return(result);
}
private void ExportFollowSheet(DataSet ds, string destination, string nameSheet)
{
using (var workbook = SpreadsheetDocument.Open(destination, true))
{
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 = nameSheet
};
sheets.Append(sheet);
foreach (System.Data.DataTable table in ds.Tables)
{
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);
}
}
workbook.Clone();
}
}
/// <summary>
/// Reads string value from the next cell.
/// </summary>
/// <returns>Null value if cell is empty.</returns>
public string ReadCellStringValue()
{
string result = null;
if (currentCell != null)
{
if (currentCellIndex == currentCellPosition)
{
DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;
if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
// Shared string (stored in separate table)
////DocumentFormat.OpenXml.Spreadsheet.SharedStringItem ssi = this.spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>()
//// .ElementAt(int.Parse(c.CellValue.InnerText));
////result = ssi.InnerText;
result = sharedStrings[int.Parse(c.CellValue.InnerText)];
}
else
{
// Inline string (stored as child node)
if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.InlineString)
{
result = c.FirstChild.InnerText;
}
else
{
// Regular string
if (c.CellValue != null)
{
result = c.CellValue.InnerText;
}
}
}
}
}
if (!SkipCells(1))
{
currentCell = null;
currentCellIndex = 0;
}
return(result);
}
/// <summary>
/// Reads integral number from the next cell.
/// </summary>
/// <returns>Null value if cell is empty.</returns>
public int?ReadCellIntegralNumberValue()
{
int?result = null;
if (currentCell != null)
{
if (currentCellIndex == currentCellPosition)
{
DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;
string s = GetCurrentCellAsString();
if (s == null)
{
if (c.CellValue != null)
{
s = c.CellValue.InnerText;
}
}
if (s != null)
{
try
{
result = Convert.ToInt32(s, dotNumberFormatInfo);
}
catch
{
throw new FormatException(string.Format(
"Cell value \"{0}\" at {1} is not an integral number in \"{2}\"",
s,
GetCellReference(currentRowPosition, currentCellPosition),
fileName));
}
}
}
}
if (!SkipCells(1))
{
currentCell = null;
currentCellIndex = 0;
}
return(result);
}
private string GetCurrentCellAsString()
{
DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;
if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
// Shared string (stored in separate table)
DocumentFormat.OpenXml.Spreadsheet.SharedStringItem ssi = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>()
.ElementAt(int.Parse(c.CellValue.InnerText));
return(ssi.InnerText);
}
if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.InlineString)
{
// Inline string (stored as child node)
return(c.FirstChild.InnerText);
}
return(null);
}
private void ExportDataSet(DataSet ds, string destination, string filename, string sheetname)
{
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();
ds.Tables[0].TableName = sheetname;
ds.Tables[1].TableName = "Brand Wise NRV";
ds.AcceptChanges();
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();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
Int32 val;
Decimal val1;
if (Int32.TryParse(dsrow[col].ToString(), out val))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
}
else if (Decimal.TryParse(dsrow[col].ToString(), out val1))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
}
else
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
}
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbook.Close();
workbook.Dispose();
}
// byte[] byteArray = File.ReadAllBytes(destination);
// ViewState["sourceFile"] = "RPLScheme.xlsx";
// Do work here
//HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + filename);
//HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//HttpContext.Current.Response.BinaryWrite(byteArray);
//HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
//HttpContext.Current.Response.SuppressContent = true; // Gets or sets a value indicating whether to send HTTP content to the client.
//HttpContext.Current.ApplicationInstance.CompleteRequest();
}
/// <summary>
/// Adds row of cells to current worksheet (for reporting).
/// </summary>
public void AddRowToReport(ExcelCell[] cells)
{
if (sheetData == null)
{
return;
}
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
ExcelCellString stringCell;
ExcelCellBoolean booleanCell;
ExcelCellNumberIntegral numberIntegralCell;
ExcelCellNumberFractional numberFractionalCell;
ExcelCellPercentIntegral percentIntegralCell;
ExcelCellPercentFractional percentFractionalCell;
ExcelCellDate dateCell;
ExcelCellDateAndTime dateAndTimeCell;
foreach (ExcelCell excelCell in cells)
{
stringCell = excelCell as ExcelCellString;
if (stringCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(stringCell.Value);
row.AppendChild(cell);
continue;
}
booleanCell = excelCell as ExcelCellBoolean;
if (booleanCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = booleanFormatStyleIndex;
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
if (booleanCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(booleanCell.Value.Value ? "Да" : null);
}
row.AppendChild(cell);
continue;
}
numberIntegralCell = excelCell as ExcelCellNumberIntegral;
if (numberIntegralCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = numberIntegralFormatStyleIndex;
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
if (numberIntegralCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.Int32Value.FromInt32(numberIntegralCell.Value.Value));
}
row.AppendChild(cell);
continue;
}
numberFractionalCell = excelCell as ExcelCellNumberFractional;
if (numberFractionalCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = numberFractionalFormatStyleIndex;
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
if (numberFractionalCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(numberFractionalCell.Value.Value));
}
row.AppendChild(cell);
continue;
}
percentIntegralCell = excelCell as ExcelCellPercentIntegral;
if (percentIntegralCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = percentIntegralFormatStyleIndex;
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
if (percentIntegralCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(percentIntegralCell.Value.Value));
}
row.AppendChild(cell);
continue;
}
percentFractionalCell = excelCell as ExcelCellPercentFractional;
if (percentFractionalCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = percentFractionalFormatStyleIndex;
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
if (percentFractionalCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(percentFractionalCell.Value.Value));
}
row.AppendChild(cell);
continue;
}
dateCell = excelCell as ExcelCellDate;
if (dateCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = dateFormatStyleIndex;
cell.DataType = null;
if (dateCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DoubleValue.FromDouble(dateCell.Value.Value.ToOADate()));
}
row.AppendChild(cell);
continue;
}
dateAndTimeCell = excelCell as ExcelCellDateAndTime;
if (dateAndTimeCell != null)
{
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.StyleIndex = dateTimeFormatStyleIndex;
cell.DataType = null;
if (dateAndTimeCell.Value.HasValue)
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DoubleValue.FromDouble(dateAndTimeCell.Value.Value.ToOADate()));
}
row.AppendChild(cell);
continue;
}
}
sheetData.AppendChild(row);
}
public static MemoryStream Export(IEnumerable <T> entities)
{
var stream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(stream, 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 = entities.CopyToDataTableE();
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);
}
}
return(stream);
}
public FileResult ExportExcel(string fileName = "")
{
#region Init Data
var datasource = new System.Data.DataTable();
datasource.Columns.Add(new DataColumn("ID", typeof(Int32)));
datasource.Columns.Add(new DataColumn("Name", typeof(string)));
datasource.Columns.Add(new DataColumn("Score", typeof(Int32)));
datasource.Columns.Add(new DataColumn("Team", typeof(string)));
datasource.Rows.Add(10, "Bob", 12, "Xi'An");
datasource.Rows.Add(11, "Tommy", 6, "Xi'An");
datasource.Rows.Add(12, "Jaguar", 15, "Xi'An");
datasource.Rows.Add(2, "Phillip", 9, "BeiJing");
datasource.Rows.Add(3, "Hunter", 10, "BeiJing");
datasource.Rows.Add(4, "Hellen", 8, "BeiJing");
datasource.Rows.Add(5, "Jim", 9, "BeiJing");
DataSet ds = new DataSet();
ds.Tables.Add(datasource);
#endregion
MemoryStream stream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(stream, 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();
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);
}
}
}
stream.Seek(0, SeekOrigin.Begin);
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var donwloadFile = string.Format("attachment;filename={0}.xlsx;", string.IsNullOrEmpty(fileName) ? Guid.NewGuid().ToString() : WebUtility.UrlEncode(fileName));
return(File(bytes, contentType, donwloadFile));
}
protected void btnExport_Click(Object sender, EventArgs e)
{
//var invalidChars = Path.GetInvalidFileNameChars();
string path = Server.MapPath("~/");
DataBind();
path += @"Admin\Report.xlsx";
using (var workbook = SpreadsheetDocument.Create(path, 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 = ToDataTable(oppList);
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);
}
}
FileInfo file = new FileInfo(path);
if (file.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=Report.xlsx");
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
//LaunchFolderView(path);
}
//export excel
private void exportExcel(DataGridView grid, string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
var spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.
Create(filepath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
// Add Sheets to the Workbook.
var sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
var row_header = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = 1
};
for (var j = 0; j < grid.ColumnCount; j++)
{
if (!(grid.Columns[j] is DataGridViewTextBoxColumn))
{
continue;
}
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(grid.Columns[j].HeaderText),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
};
row_header.Append(cell);
}
sheetData.Append(row_header);
for (var i = 0; i < grid.RowCount; i++)
{
var row = new DocumentFormat.OpenXml.Spreadsheet.Row();
for (var j = 0; j < grid.ColumnCount; j++)
{
var grid_cell = grid.Rows[i].Cells[j];
if (grid_cell.Value == null)
{
continue;
}
var cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(grid_cell.Value.ToString()),
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
};
row.Append(cell);
}
sheetData.Append(row);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
spreadsheetDocument.Dispose();
}
private void ExportDataCommon(DataTable table, SpreadsheetDocument workbook, string SheetName, Boolean newDoc)
{
if (newDoc == true)
{
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 = SheetName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//DocumentFormat.OpenXml.Spreadsheet.Column columns2 = new DocumentFormat.OpenXml.Spreadsheet.Column();
/*
* //Columns columns2 = new Columns();
* Columns columns1 = sheet.GetFirstChild<Columns>();
* Column column1 = new Column() { Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 16D, CustomWidth = true };
* columns1.Append(column1);
* /*
* columns2.Append(new Column() { Min = 1, Max = 3, Width = 100, CustomWidth = true });
* columns2.Append(new Column() { Min = 4, Max = 4, Width = 100, CustomWidth = true });
* sheetData.AppendChild(columns2);
*/
// Construct column names
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);
}
// Add the row values to the excel sheet
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);
}
/*
* DocumentFormat.OpenXml.Spreadsheet.Columns cs = sheetPart.Worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Columns>();
* if (cs != null)
* {
* IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Column> ic = cs.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>();
* DocumentFormat.OpenXml.Spreadsheet.Column c = ic.First();
* c.Width = 100;
* }
*/
}
static void Main(string[] args)
{
Student myrecord = new Student {
StudentId = "200429019", FirstName = "Pranav", LastName = "Sharma"
};
List <Student> students = new List <Student>();
List <string> directories = FTP.GetDirectory(Constants.FTP.BaseUrl);
foreach (var directory in directories)
{
Student student = new Student()
{
AbsoluteUrl = Constants.FTP.BaseUrl
};
student.FromDirectory(directory);
Console.WriteLine(student);
string infoFilePath = student.FullPathUrl + "/" + Constants.Locations.InfoFile;
bool fileExists = FTP.FileExists(infoFilePath);
if (fileExists == true)
{
string csvPath = $@"/Users/pranavsharma/Desktop/Student Data/{directory}.csv";
// FTP.DownloadFile(infoFilePath, csvPath);
byte[] bytes = FTP.DownloadFileBytes(infoFilePath);
string csvData = Encoding.Default.GetString(bytes);
string[] csvlines = csvData.Split("\r\n", StringSplitOptions.RemoveEmptyEntries);
if (csvlines.Length != 2)
{
Console.WriteLine("Error in CSV format");
}
else
{
student.FromCSV(csvlines[1]);
}
Console.WriteLine("Found info file:");
student.MyRecord = "yes";
}
else
{
Console.WriteLine("Could not find info file:");
student.MyRecord = "No";
}
Console.WriteLine("\t" + infoFilePath);
string imageFilePath = student.FullPathUrl + "/" + Constants.Locations.ImageFile;
bool imageFileExists = FTP.FileExists(imageFilePath);
//try
//{
//var imageBytes = FTP.DownloadFileBytes(imageFilePath);
//Image myimage = Imaging.ByteArrayToImage(imageBytes);
//myimage.Save($@"/Users/pranavsharma/Desktop/student/{student.StudentId}.jpg");
//string base64 = Imaging.ImageToBase64(myimage, ImageFormat.Jpeg);
if (imageFileExists == true)
{
Console.WriteLine("Found image file : ");
}
else
{
Console.WriteLine("Could not find image file:");
}
//}
//catch (Exception )
//{
//}
Console.WriteLine("\t" + imageFilePath);
students.Add(student);
//Console.WriteLine(directory);
Console.WriteLine(" \t Count of student : {0}", students.Count);
Console.WriteLine(" \t Age of Student : {0} ", student.age);
Console.WriteLine(" \t Student Record : {0} ", student.MyRecord);
}
Student me = students.SingleOrDefault(x => x.StudentId == myrecord.StudentId);
Student meUsingFind = students.Find(x => x.StudentId == myrecord.StudentId);
var avgage = students.Average(x => x.age);
var minage = students.Min(x => x.age);
var maxage = students.Max(x => x.age);
Console.WriteLine(" \n\t Name Searched With Query: {0} ", meUsingFind);
Console.WriteLine(" \t Average of Student age is: {0} ", avgage);
Console.WriteLine(" \t Minimum of Student age is: {0} ", minage);
Console.WriteLine(" \t Maximum of Student age is: {0} ", maxage);
//save to csv
string studentsCSVPath = $"{Constants.Locations.DataFolder}//students.csv";
//Establish a file stream to collect data from the response
using (StreamWriter fs = new StreamWriter(studentsCSVPath))
{
foreach (var student in students)
{
fs.WriteLine(student.ToCSV());
}
}
string studentsWordPath = $"{Constants.Locations.DataFolder}//students.docx";
string studentsImagePath = $"{Constants.Locations.ImagesFolder}//myimage.jpg";
using (WordprocessingDocument wordDocument =
WordprocessingDocument.Create(studentsWordPath, WordprocessingDocumentType.Document))
{
// Add a main document part.
MainDocumentPart mainPart = wordDocument.AddMainDocumentPart();
// Create the document structure and add some text.
mainPart.Document = new Document();
Body body = mainPart.Document.AppendChild(new Body());
ImagePart imagePart = mainPart.AddImagePart(ImagePartType.Jpeg);
Paragraph para = body.AppendChild(new Paragraph());
Run run = para.AppendChild(new Run());
foreach (var student in students)
{
run.AppendChild(new Text("My name : "));
run.AppendChild(new Text(student.FirstName.ToString()));
run.AppendChild(new Text(" , "));
run.AppendChild(new Text("My Student id : "));
run.AppendChild(new Text(student.StudentId.ToString()));
run.AppendChild(new Text(" , "));
using (FileStream stream = new FileStream(studentsImagePath, FileMode.Open))
{
imagePart.FeedData(stream);
}
AddImageToBody(wordDocument, mainPart.GetIdOfPart(imagePart));
run.AppendChild(new Break()
{
Type = BreakValues.Page
});
}
}
// Create Excel sheet and fetch data from FTP
string studentsExcelPath = $"{Constants.Locations.DataFolder}//students.xlsx";
using SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(studentsExcelPath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// Add Sheets to the Workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// Append a new worksheet and associate it with the workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
var excelRows = sheetData.Descendants <DocumentFormat.OpenXml.Spreadsheet.Row>().ToList();
//var excelcolumns = sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Column>().ToList();
int rowindex = 1;
//int columnindex = 1;
foreach (var student in students)
{
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();
DocumentFormat.OpenXml.Spreadsheet.Columns cs = new DocumentFormat.OpenXml.Spreadsheet.Columns();
row.RowIndex = (UInt32)rowindex;
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(student.FirstName.ToString())
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell1 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(student.LastName.ToString())
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell2 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(student.StudentId.ToString())
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell3 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(student.MyRecord.ToString()))
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell4 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(student.age.ToString())
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell5 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(student.DateOfBirthDT.ToString()))
};
DocumentFormat.OpenXml.Spreadsheet.Cell cell6 = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(Guid.NewGuid().ToString()))
};
row.Append(cell);
row.Append(cell1);
row.Append(cell2);
row.Append(cell3);
row.Append(cell4);
row.Append(cell5);
row.Append(cell6);
sheetData.Append(row);
//how to write the data in cell
rowindex++;
}
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
string studentsxmlPath = $"{Constants.Locations.DataFolder}//students.xml";
//Establish a file stream to collect data from the response
using (StreamWriter fs = new StreamWriter(studentsxmlPath))
{
XmlSerializer x = new XmlSerializer(students.GetType());
x.Serialize(fs, students);
Console.WriteLine();
}
//4.Upload the files to My FTP
foreach (var student in students)
{
//FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429013 Pranav Sharma/students.csv");
FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429013 Pranav Sharma/students.xml");
FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429019 Pranav Sharma/students.word");
FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429019 Pranav Sharma/students.xlsx");
}
return;
}
/// <summary>
/// This method takes DataSet as input paramenter and it exports the same to excel
/// </summary>
/// <param name="ds">Data Set</param>
/// <returns>
/// byte array of excel file
/// </returns>
public static byte[] GetByteArrayExcel(DataSet ds)
{
try
{
byte[] returnBytes = null;
if (ds != null && ds.Tables.Count != 0)
{
using (MemoryStream mem = new MemoryStream())
{
var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
{
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
for (int i = ds.Tables.Count - 1; i >= 0; i--)
{
System.Data.DataTable table = ds.Tables[i];
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);
List <string> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
}
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);
}
}
}
workbook.Close();
workbook.Dispose();
returnBytes = mem.ToArray();
}
}
return(returnBytes);
}
catch (Exception ex)
{
Logger.Info("Error while generate LSMW Excel file - , Message : {0} ,Stack:{1}", ex.Message, ex.StackTrace);
return(null);
}
}
/// <summary>
/// Moves to the next row of current worksheet.
/// </summary>
/// <returns>True if success.</returns>
public bool MoveToNextRow(bool allowEmptyRow = false)
{
currentCellPosition = 1;
if (currentRowPosition < currentRowIndex)
{
// This row does not exist in worksheet (the row is empty)
currentRowPosition++;
return(true);
}
Type rowType = typeof(DocumentFormat.OpenXml.Spreadsheet.Row);
Type cellType = typeof(DocumentFormat.OpenXml.Spreadsheet.Cell);
while (reader.Read())
{
if (reader.IsStartElement && (reader.ElementType == rowType))
{
// Read next available row
DocumentFormat.OpenXml.OpenXmlAttribute attribute = reader.Attributes
.First(a => a.LocalName == "r");
currentRowIndex = Convert.ToInt32(attribute.Value);
// Fix: for first row with zero index (invalid)
if (currentRowIndex == 0)
{
currentRowIndex = 1;
}
currentRowPosition++;
currentCell = null;
currentCellIndex = 0;
currentCellPosition = 0;
if (reader.ReadFirstChild())
{
// Row contains cells
if (reader.IsStartElement && (reader.ElementType == cellType))
{
// Read first available cell
currentCell = (DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement();
currentCellIndex = ColumnNameToIndex(GetColumnNameFromCellReference(currentCell.CellReference));
currentCellPosition = 1;
// Move to the next cell
reader.ReadNextSibling();
return(true);
}
}
else
{
// Row is empty
return(allowEmptyRow);
}
}
}
return(false);
}
public static MemoryStream GetExcelSheetMemoryStream(DataTable dataTable)
{
DataSet ds = new DataSet();
ds.Tables.Add(dataTable);
MemoryStream memoryStream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(memoryStream, 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();
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);
}
}
}
// reset the position to the start of the stream
memoryStream.Seek(0, SeekOrigin.Begin);
return(memoryStream);
}
protected override Task WriteFileAsync(HttpResponse response, CancellationToken cancellation)
{
var enumerable = _model 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);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<string>();
var properties = typeof(User).GetProperties();
foreach (var property in properties)
{
columns.Add(property.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(property.Name);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in enumerable)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var header in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
var value = header.GetValue(item);
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Save();
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
FileContents = mem.ToArray();
return response.Body.WriteAsync(FileContents, 0, FileContents.Length, cancellation);
}
}
}
/// <summary>
/// Shortcut to construct an attachment object using Excel open document file formats
/// </summary>
/// <param name="report_data"></param>
/// <param name="attachment_filename"></param>
/// <returns></returns>
public static Attachment BuildAttachment(DataTable report_data, string attachment_filename)
{
string fn = Path.GetTempFileName() + ".xlsx";
try {
using (var workbook = SpreadsheetDocument.Create(fn, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true)) {
// Construct the workbook parts
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
var sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
workbook.WorkbookPart.Workbook.Sheets = sheets;
// Set up this sheet
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
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;
}
// Make this sheet a table
string name = report_data.TableName;
if (String.IsNullOrWhiteSpace(name))
{
name = Path.GetFileNameWithoutExtension(attachment_filename);
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = name
};
sheets.Append(sheet);
// Set up header row
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in report_data.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);
// Set up data rows
foreach (System.Data.DataRow dsrow in report_data.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);
}
// Close the workbook and save it
workbook.Close();
}
// Here's your attachment
byte[] bytes = File.ReadAllBytes(fn);
Attachment a = new Attachment(new MemoryStream(bytes), new System.Net.Mime.ContentType("application/vnd.ms-excel"));
a.Name = attachment_filename;
return(a);
// Clean up afterwards
} finally {
if (File.Exists(fn))
{
File.Delete(fn);
}
}
}
/// <summary>
/// Reads date and time value from the next cell.
/// </summary>
/// <returns>Null value if cell is empty.</returns>
public DateTime?ReadCellDateValue(bool allowNotaDate = false)
{
DateTime?result = null;
if (currentCell != null)
{
if (currentCellIndex == currentCellPosition)
{
DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;
string s = GetCurrentCellAsString();
if (!string.IsNullOrEmpty(s)) //!= null
{
if (allowNotaDate)
{
result = null;
}
else
{
throw new FormatException(string.Format(
"Cell value \"{0}\" at {1} is not a date in \"{2}\"",
s,
GetCellReference(currentRowPosition, currentCellPosition),
fileName));
}
}
if (c.CellValue != null)
{
try
{
result = DateTime.FromOADate(Convert.ToDouble(c.CellValue.InnerText, dotNumberFormatInfo));
}
catch
{
if (allowNotaDate)
{
result = null;
}
else
{
throw new FormatException(string.Format(
"Cell value \"{0}\" at {1} is not a date in \"{2}\"",
s,
GetCellReference(currentRowPosition, currentCellPosition),
fileName));
}
}
}
else
{
result = null;
}
}
}
if (!SkipCells(1))
{
currentCell = null;
currentCellIndex = 0;
}
return(result);
}
public void ExportDataSet(DataTable table, string excelFileName)
{
using (var workbook = SpreadsheetDocument.Create(excelFileName, 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 = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
// Construct column names
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);
}
// Add the row values to the excel sheet
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);
}
}
}
/// <summary>
/// Creates a Microsoft Excel Worksheet from the input DataSet, returning it as a MemoryStream.
/// </summary>
/// <param name="ds">DataSet of source data.</param>
/// <returns>MemoryStream</returns>
public static MemoryStream ExportDataSetToExcel(DataSet ds)
{
//Initial code source:
//https://accesspublic.wordpress.com/2014/02/22/c-export-dataset-to-excel-using-openxml/
MemoryStream stream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(stream, 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();
for (int tableIdx = 0; tableIdx < ds.Tables.Count; tableIdx++)
{
DataTable table = ds.Tables[tableIdx];
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);
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet();
sheet.Id = relationshipId;
sheet.SheetId = (uint)(tableIdx + 1); //If set to zero, Excel will display an error when opening the spreadsheet file.
sheet.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);
}
//DocumentFormat.OpenXml.Spreadsheet.Pane p = new DocumentFormat.OpenXml.Spreadsheet.Pane()
//{
// VerticalSplit = 9D,
// TopLeftCell = "A2",
// ActivePane = DocumentFormat.OpenXml.Spreadsheet.PaneValues.BottomLeft,
// State = DocumentFormat.OpenXml.Spreadsheet.PaneStateValues.Frozen
//};
//sheetData.AppendChild(p);
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();
switch (Type.GetTypeCode(table.Columns[col].DataType))
{
case System.TypeCode.Int16:
case System.TypeCode.Int32:
case System.TypeCode.Int64:
case System.TypeCode.UInt16:
case System.TypeCode.UInt32:
case System.TypeCode.UInt64:
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
break;
case System.TypeCode.Decimal:
case System.TypeCode.Double:
case System.TypeCode.Single:
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
break;
case System.TypeCode.DateTime:
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;
break;
default:
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
break;
}
//cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
return(stream);
}
/// <summary>
/// Exports a DataTable to an Excel (xlsx) spreadsheet format.
/// </summary>
/// <param name="dataTable">The DataTable to convert</param>
/// <param name="excelPath">The full path and filename of the Excel spreadsheet to create</param>
public static void ExportDataTableToExcel(DataTable dataTable, string excelPath)
{
using (var workbook = SpreadsheetDocument.Create(excelPath, SpreadsheetDocumentType.Workbook))
{
//// Add a new WorkbookPart
var workbookPart = workbook.AddWorkbookPart();
//// Add a new Workbook
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.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>().Max(s => s.SheetId.Value) + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet =
new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = dataTable.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (DataColumn column in dataTable.Columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell
{
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String,
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[i].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
private void ExportDSToExcel(DataTable table, string sFilePath, string sSheetName = "")
{
SpreadsheetDocument workbook;
if (sSheetName == "")
{
sSheetName = table.TableName;
}
if (File.Exists(sFilePath))
{
workbook = SpreadsheetDocument.Open(sFilePath, true);
}
else
{
workbook = SpreadsheetDocument.Create(sFilePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
}
uint sheetId = 1;
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);
DocumentFormat.OpenXml.Spreadsheet.Sheet oSheet = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(s => s.Name == sSheetName).FirstOrDefault();
if (oSheet != null)
{
oSheet.Remove();
}
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 = sSheetName
};
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);
}
workbook.Close();
}
protected override Task WriteFileAsync(HttpResponse response, CancellationToken cancellation)
{
var enumerable = _model 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);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
var properties = typeof(User).GetProperties();
foreach (var property in properties)
{
columns.Add(property.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(property.Name);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in enumerable)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (var header in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
var value = header.GetValue(item);
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Save();
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
FileContents = mem.ToArray();
return(response.Body.WriteAsync(FileContents, 0, FileContents.Length, cancellation));
}
}
}