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)); } } }