Beispiel #1
1
        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);
            }
        }
Beispiel #2
0
        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();
            }
        }
Beispiel #3
0
        /// <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);
        }
Beispiel #4
0
        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 + " ");
             }
         }
     }
 }
Beispiel #6
0
        /// <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);
        }
Beispiel #7
0
        // 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();
            }
        }
Beispiel #8
0
        //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();
        }
Beispiel #9
0
        /// <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);
                }
            }
        }
Beispiel #10
0
        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();
        }
Beispiel #12
0
        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);
            //}
        }
Beispiel #13
0
    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);
                }
            }
    }
Beispiel #14
0
        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;
             * }
             */
        }
Beispiel #15
0
        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;
        }
Beispiel #16
0
        /// <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);
            }
        }
Beispiel #17
0
        /// <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);
        }
Beispiel #18
0
        /// <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);
                }
            }
        }
Beispiel #20
0
        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);
        }
Beispiel #21
0
        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));
        }
Beispiel #22
0
        /// <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);
                }
            }
        }
Beispiel #23
0
        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);
        }
Beispiel #24
0
        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));
            }
        }
    }