Esempio n. 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);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// Skips specified number of cells.
        /// </summary>
        /// <param name="count">Number of cells to skip.</param>
        /// <returns>True if success.</returns>
        public bool SkipCells(int count)
        {
            if (count <= 0)
            {
                throw new ArgumentOutOfRangeException("count", "Cell count cannot be zero or negative value");
            }

            for (int i = 0; i < count; i++)
            {
                if (currentCellPosition < currentCellIndex)
                {
                    // This cell does not exist in worksheet (the cell is empty)
                    currentCellPosition++;
                    continue;
                }

                if (!reader.EOF && reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Cell) && reader.IsStartElement)
                {
                    // Read next available cell
                    currentCell      = (DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement();
                    currentCellIndex = ColumnNameToIndex(GetColumnNameFromCellReference(currentCell.CellReference));
                    currentCellPosition++;

                    // Move to the next cell
                    reader.ReadNextSibling();
                }
                else
                {
                    return(false);
                }
            }

            return(true);
        }
Esempio n. 3
0
        private string GetExcelCellValue(DocumentFormat.OpenXml.Spreadsheet.Cell cell, DocumentFormat.OpenXml.Packaging.WorkbookPart wbpart)
        {
            string retVal = string.Empty;

            if (cell.DataType != null)
            {
                if (cell.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
                {
                    int id = -1;
                    if (Int32.TryParse(cell.InnerText, out id))
                    {
                        DocumentFormat.OpenXml.Spreadsheet.SharedStringItem item = GetSharedStringItemById(wbpart, id);

                        if (item.Text != null)
                        {
                            retVal = item.Text.Text;
                        }
                        else if (item.InnerText != null)
                        {
                            retVal = item.InnerText;
                        }
                        else if (item.InnerXml != null)
                        {
                            retVal = item.InnerXml;
                        }
                    }
                }
                else
                {
                    retVal = cell.InnerText;
                }
            }
            return(retVal);
        }
Esempio n. 4
0
        private void CleanupResources()
        {
            fileName = null;

            currentCell = null;

            if (reader != null)
            {
                reader.Close();
                reader.Dispose();
                reader = null;
            }

            if (spreadsheetDocument != null)
            {
                spreadsheetDocument.Close();
                spreadsheetDocument.Dispose();
                spreadsheetDocument = null;
            }

            if (fileStream != null)
            {
                fileStream.Close();
                fileStream.Dispose();
                fileStream = null;
            }

            sharedStrings = null;
        }
Esempio n. 5
0
        /// <summary>
        /// Reads decimal number from the next cell.
        /// </summary>
        /// <returns>Null value if cell is empty.</returns>
        public decimal?ReadCellDecimalNumberValue(ExcelOpenXmlDecimalSeparator separator, bool convertSeparator)
        {
            decimal?result = null;

            if (currentCell != null)
            {
                if (currentCellIndex == currentCellPosition)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;

                    string s = GetCurrentCellAsString();
                    if (s == null)
                    {
                        if (c.CellValue != null)
                        {
                            s = c.CellValue.InnerText;
                        }
                    }

                    if (s != null)
                    {
                        if (convertSeparator)
                        {
                            switch (separator)
                            {
                            case ExcelOpenXmlDecimalSeparator.Dot:
                                s = s.Replace(',', '.');
                                break;

                            case ExcelOpenXmlDecimalSeparator.Comma:
                                s = s.Replace('.', ',');
                                break;
                            }
                        }

                        try
                        {
                            result = decimal.Parse(s, System.Globalization.NumberStyles.Float, separator == ExcelOpenXmlDecimalSeparator.Dot ? dotNumberFormatInfo : commaNumberFormatInfo);
                            ////result = Convert.ToDecimal(s, separator == ExcelOpenXmlDecimalSeparator.Dot ? dotNumberFormatInfo : commaNumberFormatInfo);
                        }
                        catch
                        {
                            throw new FormatException(string.Format(
                                                          "Cell value \"{0}\" at {1} is not a decimal number in \"{2}\"",
                                                          s,
                                                          GetCellReference(currentRowPosition, currentCellPosition),
                                                          fileName));
                        }
                    }
                }
            }

            if (!SkipCells(1))
            {
                currentCell      = null;
                currentCellIndex = 0;
            }

            return(result);
        }
Esempio n. 6
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();
            }
        }
Esempio n. 7
0
        /// <summary>
        /// Reads string value from the next cell.
        /// </summary>
        /// <returns>Null value if cell is empty.</returns>
        public string ReadCellStringValue()
        {
            string result = null;

            if (currentCell != null)
            {
                if (currentCellIndex == currentCellPosition)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;

                    if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
                    {
                        // Shared string (stored in separate table)
                        ////DocumentFormat.OpenXml.Spreadsheet.SharedStringItem ssi = this.spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>()
                        ////    .ElementAt(int.Parse(c.CellValue.InnerText));

                        ////result = ssi.InnerText;

                        result = sharedStrings[int.Parse(c.CellValue.InnerText)];
                    }
                    else
                    {
                        // Inline string (stored as child node)
                        if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.InlineString)
                        {
                            result = c.FirstChild.InnerText;
                        }
                        else
                        {
                            // Regular string
                            if (c.CellValue != null)
                            {
                                result = c.CellValue.InnerText;
                            }
                        }
                    }
                }
            }

            if (!SkipCells(1))
            {
                currentCell      = null;
                currentCellIndex = 0;
            }

            return(result);
        }
Esempio n. 8
0
        /// <summary>
        /// Reads integral number from the next cell.
        /// </summary>
        /// <returns>Null value if cell is empty.</returns>
        public int?ReadCellIntegralNumberValue()
        {
            int?result = null;

            if (currentCell != null)
            {
                if (currentCellIndex == currentCellPosition)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;

                    string s = GetCurrentCellAsString();
                    if (s == null)
                    {
                        if (c.CellValue != null)
                        {
                            s = c.CellValue.InnerText;
                        }
                    }

                    if (s != null)
                    {
                        try
                        {
                            result = Convert.ToInt32(s, dotNumberFormatInfo);
                        }
                        catch
                        {
                            throw new FormatException(string.Format(
                                                          "Cell value \"{0}\" at {1} is not an integral number in \"{2}\"",
                                                          s,
                                                          GetCellReference(currentRowPosition, currentCellPosition),
                                                          fileName));
                        }
                    }
                }
            }

            if (!SkipCells(1))
            {
                currentCell      = null;
                currentCellIndex = 0;
            }

            return(result);
        }
Esempio n. 9
0
        private string GetCurrentCellAsString()
        {
            DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;

            if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
            {
                // Shared string (stored in separate table)
                DocumentFormat.OpenXml.Spreadsheet.SharedStringItem ssi = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>()
                                                                          .ElementAt(int.Parse(c.CellValue.InnerText));

                return(ssi.InnerText);
            }

            if (c.DataType != null && c.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.InlineString)
            {
                // Inline string (stored as child node)
                return(c.FirstChild.InnerText);
            }

            return(null);
        }
        private void ExportDataSet(DataSet ds, string destination, string filename, string sheetname)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                ds.Tables[0].TableName = sheetname;
                ds.Tables[1].TableName = "Brand Wise NRV";
                ds.AcceptChanges();
                foreach (System.Data.DataTable table in ds.Tables)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = relationshipId, SheetId = sheetId, Name = table.TableName
                    };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List <String> columns = new List <string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);



                        headerRow.AppendChild(cell);
                    }


                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            Int32   val;
                            Decimal val1;
                            if (Int32.TryParse(dsrow[col].ToString(), out val))
                            {
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            }
                            else if (Decimal.TryParse(dsrow[col].ToString(), out val1))
                            {
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            }
                            else
                            {
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            }
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }

                workbook.Close();
                workbook.Dispose();
            }

            // byte[] byteArray = File.ReadAllBytes(destination);
            // ViewState["sourceFile"] = "RPLScheme.xlsx";
            // Do work here
            //HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + filename);
            //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //HttpContext.Current.Response.BinaryWrite(byteArray);
            //HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
            //HttpContext.Current.Response.SuppressContent = true;  // Gets or sets a value indicating whether to send HTTP content to the client.
            //HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
Esempio n. 11
0
        /// <summary>
        /// Adds row of cells to current worksheet (for reporting).
        /// </summary>
        public void AddRowToReport(ExcelCell[] cells)
        {
            if (sheetData == null)
            {
                return;
            }

            DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();

            DocumentFormat.OpenXml.Spreadsheet.Cell cell;

            ExcelCellString            stringCell;
            ExcelCellBoolean           booleanCell;
            ExcelCellNumberIntegral    numberIntegralCell;
            ExcelCellNumberFractional  numberFractionalCell;
            ExcelCellPercentIntegral   percentIntegralCell;
            ExcelCellPercentFractional percentFractionalCell;
            ExcelCellDate        dateCell;
            ExcelCellDateAndTime dateAndTimeCell;

            foreach (ExcelCell excelCell in cells)
            {
                stringCell = excelCell as ExcelCellString;
                if (stringCell != null)
                {
                    cell           = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(stringCell.Value);
                    row.AppendChild(cell);
                    continue;
                }

                booleanCell = excelCell as ExcelCellBoolean;
                if (booleanCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = booleanFormatStyleIndex;
                    cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    if (booleanCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(booleanCell.Value.Value ? "Да" : null);
                    }

                    row.AppendChild(cell);
                    continue;
                }

                numberIntegralCell = excelCell as ExcelCellNumberIntegral;
                if (numberIntegralCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = numberIntegralFormatStyleIndex;
                    cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                    if (numberIntegralCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.Int32Value.FromInt32(numberIntegralCell.Value.Value));
                    }

                    row.AppendChild(cell);
                    continue;
                }

                numberFractionalCell = excelCell as ExcelCellNumberFractional;
                if (numberFractionalCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = numberFractionalFormatStyleIndex;
                    cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                    if (numberFractionalCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(numberFractionalCell.Value.Value));
                    }

                    row.AppendChild(cell);
                    continue;
                }

                percentIntegralCell = excelCell as ExcelCellPercentIntegral;
                if (percentIntegralCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = percentIntegralFormatStyleIndex;
                    cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                    if (percentIntegralCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(percentIntegralCell.Value.Value));
                    }

                    row.AppendChild(cell);
                    continue;
                }

                percentFractionalCell = excelCell as ExcelCellPercentFractional;
                if (percentFractionalCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = percentFractionalFormatStyleIndex;
                    cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                    if (percentFractionalCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(percentFractionalCell.Value.Value));
                    }

                    row.AppendChild(cell);
                    continue;
                }

                dateCell = excelCell as ExcelCellDate;
                if (dateCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = dateFormatStyleIndex;
                    cell.DataType   = null;
                    if (dateCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DoubleValue.FromDouble(dateCell.Value.Value.ToOADate()));
                    }

                    row.AppendChild(cell);
                    continue;
                }

                dateAndTimeCell = excelCell as ExcelCellDateAndTime;
                if (dateAndTimeCell != null)
                {
                    cell            = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.StyleIndex = dateTimeFormatStyleIndex;
                    cell.DataType   = null;
                    if (dateAndTimeCell.Value.HasValue)
                    {
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(DocumentFormat.OpenXml.DoubleValue.FromDouble(dateAndTimeCell.Value.Value.ToOADate()));
                    }

                    row.AppendChild(cell);
                    continue;
                }
            }

            sheetData.AppendChild(row);
        }
Esempio n. 12
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);
        }
Esempio n. 13
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));
        }
Esempio n. 14
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);
        }
Esempio n. 15
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();
        }
Esempio n. 16
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;
             * }
             */
        }
Esempio n. 17
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;
        }
Esempio n. 18
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);
            }
        }
Esempio n. 19
0
        /// <summary>
        /// Moves to the next row of current worksheet.
        /// </summary>
        /// <returns>True if success.</returns>
        public bool MoveToNextRow(bool allowEmptyRow = false)
        {
            currentCellPosition = 1;

            if (currentRowPosition < currentRowIndex)
            {
                // This row does not exist in worksheet (the row is empty)
                currentRowPosition++;
                return(true);
            }

            Type rowType  = typeof(DocumentFormat.OpenXml.Spreadsheet.Row);
            Type cellType = typeof(DocumentFormat.OpenXml.Spreadsheet.Cell);

            while (reader.Read())
            {
                if (reader.IsStartElement && (reader.ElementType == rowType))
                {
                    // Read next available row
                    DocumentFormat.OpenXml.OpenXmlAttribute attribute = reader.Attributes
                                                                        .First(a => a.LocalName == "r");

                    currentRowIndex = Convert.ToInt32(attribute.Value);

                    // Fix: for first row with zero index (invalid)
                    if (currentRowIndex == 0)
                    {
                        currentRowIndex = 1;
                    }

                    currentRowPosition++;

                    currentCell         = null;
                    currentCellIndex    = 0;
                    currentCellPosition = 0;

                    if (reader.ReadFirstChild())
                    {
                        // Row contains cells
                        if (reader.IsStartElement && (reader.ElementType == cellType))
                        {
                            // Read first available cell
                            currentCell         = (DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement();
                            currentCellIndex    = ColumnNameToIndex(GetColumnNameFromCellReference(currentCell.CellReference));
                            currentCellPosition = 1;

                            // Move to the next cell
                            reader.ReadNextSibling();

                            return(true);
                        }
                    }
                    else
                    {
                        // Row is empty
                        return(allowEmptyRow);
                    }
                }
            }

            return(false);
        }
Esempio n. 20
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);
        }
Esempio n. 21
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);
                }
            }
    }
Esempio n. 22
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);
                }
            }
        }
Esempio n. 23
0
        /// <summary>
        /// Reads date and time value from the next cell.
        /// </summary>
        /// <returns>Null value if cell is empty.</returns>
        public DateTime?ReadCellDateValue(bool allowNotaDate = false)
        {
            DateTime?result = null;

            if (currentCell != null)
            {
                if (currentCellIndex == currentCellPosition)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell c = currentCell;

                    string s = GetCurrentCellAsString();
                    if (!string.IsNullOrEmpty(s)) //!= null
                    {
                        if (allowNotaDate)
                        {
                            result = null;
                        }
                        else
                        {
                            throw new FormatException(string.Format(
                                                          "Cell value \"{0}\" at {1} is not a date in \"{2}\"",
                                                          s,
                                                          GetCellReference(currentRowPosition, currentCellPosition),
                                                          fileName));
                        }
                    }

                    if (c.CellValue != null)
                    {
                        try
                        {
                            result = DateTime.FromOADate(Convert.ToDouble(c.CellValue.InnerText, dotNumberFormatInfo));
                        }
                        catch
                        {
                            if (allowNotaDate)
                            {
                                result = null;
                            }
                            else
                            {
                                throw new FormatException(string.Format(
                                                              "Cell value \"{0}\" at {1} is not a date in \"{2}\"",
                                                              s,
                                                              GetCellReference(currentRowPosition, currentCellPosition),
                                                              fileName));
                            }
                        }
                    }
                    else
                    {
                        result = null;
                    }
                }
            }

            if (!SkipCells(1))
            {
                currentCell      = null;
                currentCellIndex = 0;
            }

            return(result);
        }
        public void ExportDataSet(DataTable table, string excelFileName)
        {
            using (var workbook = SpreadsheetDocument.Create(excelFileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                {
                    Id = relationshipId, SheetId = sheetId, Name = table.TableName
                };
                sheets.Append(sheet);

                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();


                // Construct column names
                List <String> columns = new List <string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                // Add the row values to the excel sheet
                sheetData.AppendChild(headerRow);

                foreach (System.Data.DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }
            }
        }
Esempio n. 25
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);
        }
Esempio n. 26
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);
                }
            }
        }
Esempio n. 27
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);
        }
Esempio n. 28
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();
        }
Esempio n. 29
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));
            }
        }
    }