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);
}
}
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>
/// Opens the first worksheet of current workbook.
/// </summary>
public bool OpenFirstWorksheet()
{
if (spreadsheetDocument == null)
{
return(false);
}
sheetData = spreadsheetDocument.WorkbookPart.WorksheetParts.First().Worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
return(sheetData != null);
}
private void testChemicalListToolStripMenuItem_Click(object sender, EventArgs e)
{
List <DSSToxChemicals> chemicals = new List <DSSToxChemicals>();
// Open the document for editing.
string fileName = documentPath + "\\DSSTox_ToxCastRelease_20151019.xlsx";
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart wbPart = document.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = wbPart.WorksheetParts.First();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = wsPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text = string.Empty;
bool first = true;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
chemicals.Add(new DSSToxChemicals(text));
}
first = false;
text = string.Empty;
}
document.Close();
}
foreach (DSSToxChemicals chem in chemicals)
{
if (!string.IsNullOrEmpty(chem.Structure_SMILES))
{
ChemInfo.Molecule mol = new ChemInfo.Molecule(chem.Structure_SMILES);
if (mol != null)
{
foreach (ChemInfo.FunctionalGroup f in this.fGroups)
{
if ((f.Name != "ESTER-SULFIDE") || (f.Name != "KETENIMINE"))
{
mol.FindFunctionalGroup(f);
}
}
}
chem.AddFunctionalGroups(mol.FunctionalGroups);
}
}
fileName = documentPath + "\\chemicals.json";
System.IO.File.WriteAllText(fileName, Newtonsoft.Json.JsonConvert.SerializeObject(chemicals, Newtonsoft.Json.Formatting.Indented));
}
private void Button1_Click(object sender, EventArgs e)
{
// Open the document for editing.
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument =
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(@"C:\Users\fmogh\OneDrive\Desktop\Excel\Farzad.xlsx", false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = c.CellValue.Text;
Console.Write(text + " ");
}
}
}
}
/// <summary>
/// Opens specific worksheet of current workbook.
/// </summary>
public bool OpenWorksheet(int index)
{
if (spreadsheetDocument == null)
{
return(false);
}
sheetData = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = spreadsheetDocument.WorkbookPart.Workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>().ElementAt(index);
foreach (var worksheetPart in spreadsheetDocument.WorkbookPart.WorksheetParts)
{
if (spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart) == sheet.Id)
{
sheetData = worksheetPart.Worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
}
}
return(sheetData != null);
}
// The DOM approach.
// Note that the code below works only for cells that contain numeric values.
//
static void ReadExcelFileDOM(string fileName)
{
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = System.Linq.Enumerable.First(workbookPart.WorksheetParts);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = System.Linq.Enumerable.First(worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>());
string text;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = c.CellValue.Text;
System.Console.Write(text + " ");
}
}
System.Console.WriteLine();
System.Console.ReadKey();
}
}
//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();
}
/// <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);
}
}
}
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);
}
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();
}
private void OpenFunctionGroupExcelResource()
{
// Reads functional Groups from Excel file.
//List<string> functionalGroupStrs = new List<string>();// SustainableChemistry.Properties.Resources.Full_Functional_Group_List;
string fileName = "..\\..\\Data\\Full Functional Group List 20180731.xlsx";
FunctionalGroups.Columns.Add("Name", typeof(System.String));
FunctionalGroups.Columns.Add("Smart", typeof(System.String));
FunctionalGroups.Columns.Add("Image", typeof(System.Drawing.Image));
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart wbPart = document.WorkbookPart;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = GetWorkSheetFromSheet(wbPart, GetSheetFromName(wbPart, "Full Functional Group List")).Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text = string.Empty;
bool first = true;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
System.Data.DataRow row = FunctionalGroups.NewRow();
ChemInfo.FunctionalGroup temp = fGroups.Add(text, row);
FunctionalGroups.Rows.Add(row);
//string filename = "..\\..\\Images\\" + temp.Name.ToLower() + ".jpg";
//if (System.IO.File.Exists(filename)) temp.Image = System.Drawing.Image.FromFile(filename);
}
text = string.Empty;
first = false;
}
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(string.Format("Data Source=..\\..\\Data\\SustainableChemistry.sqlite;Version=3;;New=False;Compress=True;Max Pool Size=100;")))
//{
// con.Open();
// using (System.Data.SQLite.SQLiteTransaction transaction = con.BeginTransaction())
// {
// foreach (DataRow row in FunctionalGroups.Rows)
// {
// using (System.Data.SQLite.SQLiteCommand sqlitecommand = new System.Data.SQLite.SQLiteCommand("insert into table(fh,ch,mt,pn) values ('" + Convert.ToString(row[0]) + "','" + Convert.ToString(row[1]) + "','"
// + Convert.ToString(row[2]) + "','" + Convert.ToString(row[3]) + "')", con))
// {
// sqlitecommand.ExecuteNonQuery();
// }
// }
// transaction.Commit();
// //new General().WriteApplicationLog("Data successfully imported.");
// //return true;
// }
//}
sheetData = GetWorkSheetFromSheet(wbPart, GetSheetFromName(wbPart, "Reaction List")).Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
text = string.Empty;
first = true;
NamedReactions.Columns.Add("Name", typeof(System.String));
NamedReactions.Columns.Add("FunctionalGroup", typeof(System.String));
NamedReactions.Columns.Add("Image", typeof(System.Drawing.Image));
NamedReactions.Columns.Add("URL", typeof(System.String));
NamedReactions.Columns.Add("ReactantA", typeof(System.String));
NamedReactions.Columns.Add("ReactantB", typeof(System.String));
NamedReactions.Columns.Add("ReactantC", typeof(System.String));
NamedReactions.Columns.Add("Product", typeof(System.String));
NamedReactions.Columns.Add("Heat", typeof(System.String));
NamedReactions.Columns.Add("AcidBase", typeof(System.String));
NamedReactions.Columns.Add("Catalyst", typeof(System.String));
NamedReactions.Columns.Add("Solvent", typeof(System.String));
NamedReactions.Columns.Add("ByProducts", typeof(System.String));
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
System.Data.DataRow row = NamedReactions.NewRow();
fGroups.AddReaction(new ChemInfo.NamedReaction(text, row));
NamedReactions.Rows.Add(row);
}
text = string.Empty;
first = false;
}
document.Close();
}
//// This next line creates a list of strings that don't have images. Can be commented out!
//List<string> missingImages = new List<string>();
//// Creates the collection of functional groups.
//foreach (string line in functionalGroupStrs)
//{
// ChemInfo.FunctionalGroup temp = fGroups.Add(line);
// string filename = documentPath + "\\Images\\" + temp.Name.ToLower() + ".jpg";
// if (System.IO.File.Exists(filename)) temp.Image = System.Drawing.Image.FromFile(filename);
// //this line adds the missing image to the list of missing images. Can be commented out.
// else missingImages.Add(temp.Name);
//}
//// Writes the missing images to a file.
//// Write the string array to a new file named "WriteLines.txt".
//using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(documentPath + @"\MissingImages.txt"))
//{
// foreach (string line in missingImages)
// outputFile.WriteLine(line);
//}
//string[] imageFiles = System.IO.Directory.GetFiles(documentPath + "\\Images\\");
//string[] groupNames = fGroups.FunctionalGroups;
//List<string> extraImages = new List<string>();
//foreach (string name in imageFiles)
//{
// string temp = name.Replace(documentPath + "\\Images\\", string.Empty);
// temp = temp.Replace(".jpg", string.Empty);
// bool add = true;
// foreach (string gName in groupNames)
// {
// if (temp.ToUpper() == gName.ToUpper()) add = false;
// }
// if (add) extraImages.Add(temp);
//}
//// Write the string array to a new file named "WriteLines.txt".
//using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(documentPath + @"\ExtraImages.txt"))
//{
// foreach (string line in extraImages)
// outputFile.WriteLine(line);
//}
}
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);
}
}
}
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>
/// 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);
}
/// <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);
}
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);
}
}
}
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);
}
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));
}
/// <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);
}
}
}
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);
}
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));
}
}
}