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(); } }
private static void ExportDataTableCollection(System.Data.DataTableCollection WorksheetCollection, SpreadsheetDocument spreadsheet) { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; // Create the Excel file contents. This function is used when creating an Excel file either writing // to a file, or writing to a MemoryStream. spreadsheet.AddWorkbookPart(); spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) spreadsheet.WorkbookPart.Workbook.Append(new DocumentFormat.OpenXml.Spreadsheet.BookViews(new DocumentFormat.OpenXml.Spreadsheet.WorkbookView())); // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles"); DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet(); workbookStylesPart.Stylesheet = stylesheet; // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint worksheetNumber = 1; DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); foreach (System.Data.DataTable dt in WorksheetCollection) { // For each worksheet you want to create string worksheetName = dt.TableName; // Create worksheet part, and add it to the sheets collection in workbook WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName }; // If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData // http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/ sheets.Append(sheet); // Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems WriteDataTableToExcelWorksheet(dt, newWorksheetPart); worksheetNumber++; } spreadsheet.WorkbookPart.Workbook.Save(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; }
public static void GetSheetInfo(string fileName) { // Open file as read-only. using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(fileName, false)) { S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets; // For each sheet, display the sheet information. foreach (E sheet in sheets) { foreach (A attr in sheet.GetAttributes()) { Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value); } } } }
/// <summary> /// Adds a new worksheet to the workbook /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="name">Name of the worksheet</param> /// <returns>True if succesful</returns> public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name) { DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); DocumentFormat.OpenXml.Spreadsheet.Sheet sheet; DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart; // Add the worksheetpart worksheetPart = spreadsheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>(); worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); worksheetPart.Worksheet.Save(); // Add the sheet and make relation to workbook sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1), Name = name }; sheets.Append(sheet); spreadsheet.WorkbookPart.Workbook.Save(); return(true); }
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> /// 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(); }
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; * } */ }
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); }
/// <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); } } }
static void Main(string[] args) { Student myrecord = new Student { StudentId = "200429013", FirstName = "Priyanka", LastName = "Garg" }; List <string> directories = FTP.GetDirectory(Constants.FTP.BaseUrl); List <Student> students = new List <Student>(); 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/priyankagarg/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(" \t Age of Student is: {0} ", student.age); } Console.WriteLine("Found info file:"); } else { Console.WriteLine("Could not find info file:"); } Console.WriteLine("\t" + infoFilePath); string imageFilePath = student.FullPathUrl + "/" + Constants.Locations.ImageFile; bool imageFileExists = FTP.FileExists(imageFilePath); if (imageFileExists == true) { Console.WriteLine("Found image file:"); } else { Console.WriteLine("Could not find image file:"); } Console.WriteLine("\t" + imageFilePath); students.Add(student); Console.WriteLine(directory); Console.WriteLine(" \t Count of student is: {0}", students.Count); Console.WriteLine(" \t Age of Student is: {0} ", student.age); } 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 studentsImagePath1 = $"{Constants.Locations.ImagesFolder}//images.jpg"; // Create a document by supplying the filepath. 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()); Paragraph para = body.AppendChild(new Paragraph()); Run run = para.AppendChild(new Run()); foreach (var student in students) { run.AppendChild(new Text("My name is : ")); //run.AppendChild(new Text(student.ToString())); run.AppendChild(new Text(student.FirstName.ToString())); run.AppendChild(new Text(" , ")); run.AppendChild(new Text("My Student id is: ")); run.AppendChild(new Text(student.StudentId.ToString())); run.AppendChild(new Text(" , ")); run.AppendChild(new Break() { Type = BreakValues.Page }); } } string studentsjsonPath = $"{Constants.Locations.DataFolder}//students.json"; //Establish a file stream to collect data from the response using (StreamWriter fs = new StreamWriter(studentsjsonPath)) { foreach (var student in students) { string Student = Newtonsoft.Json.JsonConvert.SerializeObject(student); fs.WriteLine(Student.ToString()); //Console.WriteLine(jStudent); } } string studentsExcelPath = $"{Constants.Locations.DataFolder}//students.xlsx"; 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" }; sheets.Append(sheet); workbookpart.Workbook.Save(); 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(studentsCSVPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.csv"); FTP.UploadFile(studentsjsonPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.json"); FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.xml"); } return; }
/// <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 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)); }
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(); }
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); }