Example #1
0
        public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            UInt32 rowcount = 0;

            // Create the Excel document
            var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            var workbookPart = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var relId = workbookPart.GetIdOfPart(worksheetPart);

            var workbook = new Workbook();
            var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();

            // Add header to the sheet
            var row = new Row { RowIndex = ++rowcount };
            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();

            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row { RowIndex = ++rowcount };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();
            document.Close();
            return stream;
        }
Example #2
0
        public void Create(string fileName)
        {
            FileName = fileName;

            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = doc.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                //	Create Styles
                var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                Style = new CustomStylesheet();
                LoadCustomFonts();
                LoadCustomBorders();
                LoadCustomStyles();
                Style.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                //	create workbook and sheet
                var workbook = new Workbook();
                var worksheet = new Worksheet();
                var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                //	create columns
                var columns = new Columns();
                CreateColumns(columns);
                worksheet.Append(columns);

                //	create Sheet
                var sheets = new Sheets();
                var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
                sheets.Append(sheet);

                workbook.Append(fileVersion);
                workbook.Append(sheets);

                var sheetData = new SheetData();
                LoadData(sheetData);
                worksheet.Append(sheetData);

                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                doc.WorkbookPart.Workbook = workbook;
                doc.WorkbookPart.Workbook.Save();
                //doc.Close();
            }
        }
Example #3
0
        // Generates content of workbookPart1. 
        private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }
Example #4
0
        public WorkbookPart Do(SpreadsheetDocument package, params XlsxWorksheet[] worksheets)
        {
            var workbook = new Workbook {
                MCAttributes = new MarkupCompatibilityAttributes {
                    Ignorable = "x15"
                }
            };

            workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            workbook.Append(
                new FileVersion {
                ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420"
            },
                new WorkbookProperties {
                FilterPrivacy = true, DefaultThemeVersion = 124226U
            },
                new BookViews(
                    new WorkbookView {
                ActiveTab    = (uint)(worksheets.IndexOf(x => x.IsActive) ?? 0),
                XWindow      = 240,
                YWindow      = 105,
                WindowWidth  = 14805U,
                WindowHeight = 8010U,
                TabRatio     = 845U
            }
                    ),
                new Sheets(worksheets.Select((x, index) => new Sheet {
                Name    = x.Title,
                SheetId = (uint)index + 1,
                Id      = "rId" + index
            })),
                new CalculationProperties()
            {
                CalculationId = 122211U
            }
                );

            var workbookPart = package.AddWorkbookPart();

            workbookPart.Workbook = workbook;

            return(workbookPart);
        }
Example #5
0
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();

            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet  sheet1  = new Sheet()
            {
                Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
            };

            sheets1.Append(sheet1);
            workbook1.Append(sheets1);

            workbookPart1.Workbook = workbook1;
        }
Example #6
0
        public IActionResult Post([FromBody] ExcellData data)
        {
            MemoryStream ms = new MemoryStream();

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            WorkbookPart        workbookPart        = spreadsheetDocument.AddWorkbookPart();
            WorksheetPart       worksheetPart       = workbookPart.AddNewPart <WorksheetPart>();
            Workbook            workbook            = new Workbook();
            Worksheet           worksheet           = new Worksheet();

            WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();

            stylesPart.Stylesheet = CreateStylesheet();
            stylesPart.Stylesheet.Save();

            SheetData sheetData = new SheetData();

            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();

            UInt32Value currentRowIndex;

            currentRowIndex = CreateHeader(worksheet, sheetData, data);
            currentRowIndex = CreateDataTables(worksheet, sheetData, data, currentRowIndex);

            Sheets sheets = new Sheets();
            Sheet  sheet  = new Sheet
            {
                Name    = "Registro IVA corrispettivi",
                SheetId = 1,
                Id      = workbookPart.GetIdOfPart(worksheetPart)
            };

            sheets.Append(sheet);
            workbook.Append(sheets);

            spreadsheetDocument.WorkbookPart.Workbook = workbook;
            spreadsheetDocument.WorkbookPart.Workbook.Save();
            spreadsheetDocument.Close();


            byte[] content = ms.ToArray();
            ms.Seek(0, SeekOrigin.Begin);
            return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml", "corrispettivi.xlsx"));
        }
Example #7
0
        private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            Sheets   sheets1   = new Sheets();
            Sheet    sheet1    = new Sheet()
            {
                Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
            };
            Sheet sheet2 = new Sheet()
            {
                Name = "Sheet2", SheetId = (UInt32Value)1U, Id = "rId1"
            };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);
            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }
Example #8
0
        public XlWorksheet[] Wss;                            // массив экземпляров WS для заполненых worksheet с доп процедурами

        public Spreadsheet(Int32 sheetCount = 1)
        {
            memoryStream = new System.IO.MemoryStream();

            document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);

            workbookPart1          = document.AddWorkbookPart();
            workbook               = new Workbook();
            workbookPart1.Workbook = workbook;

            workbookStylesPart            = workbookPart1.AddNewPart <WorkbookStylesPart>();
            stylesheet                    = (new StylesPart()).GenerateWorkbookStylesPartContent();
            workbookStylesPart.Stylesheet = stylesheet;

            sharedStringTablePart = workbookPart1.AddNewPart <SharedStringTablePart>();
            sharedStringTable     = (new SstPart()).GenerateSharedStringTablePart();
            sharedStringTablePart.SharedStringTable = sharedStringTable;

            sheets = new Sheets();
            workbook.Append(sheets);

            worksheetParts = new WorksheetPart[sheetCount];
            Wss            = new XlWorksheet[sheetCount];
            for (UInt32 i = 0; i < sheetCount; i++)
            {
                // создаём новый лист со всеми частями (SheetViews, Columns, SheetData, MergeCells) (пока пустыми)
                Wss[i]    = new XlWorksheet(stylesheet, sharedStringTable);
                worksheet = Wss[i].GetWorksheet();

                // создаём новый WorksheetPart в workbookPart
                worksheetParts[i] = workbookPart1.AddNewPart <WorksheetPart>();
                // и добавляем новый лист в его корневой элемент Worksheet
                worksheetParts[i].Worksheet = worksheet;
                // ссылка на новый WorksheetPart
                String rId = workbookPart1.GetIdOfPart(worksheetParts[i]);

                // запись о новом листе в workbook
                sheet = new Sheet()
                {
                    Name = "Sheet" + (i + 1).ToString(), SheetId = (i + 1), Id = rId
                };
                sheets.Append(sheet);
            }
        }
Example #9
0
        protected override void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
        {
            Workbook workbook = new Workbook();
            Sheets   sheets   = new Sheets();

            Sheet sheet = new Sheet()
            {
                Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId0"
            };
            Sheet sheet2 = new Sheet()
            {
                Name = "ConnectData", SheetId = (UInt32Value)2U, Id = "rId1"
            };

            sheets.Append(sheet);
            sheets.Append(sheet2);

            workbook.Append(sheets);
            workbookPart.Workbook = workbook;
        }
Example #10
0
        private static void CreateParts(DataTable dt, SpreadsheetDocument document)
        {
            WorkbookPart workbookPart = document.AddWorkbookPart();
            Workbook     workbook     = new Workbook();

            workbookPart.Workbook = workbook;

            //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
            WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
            Stylesheet         stylesheet         = new Stylesheet();

            workbookStylesPart.Stylesheet = stylesheet;

            Sheets sheets = new Sheets();

            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
            uint worksheetNumber = 1;
            // foreach (DataTable dt in ds.Tables)
            // {
            //  For each worksheet you want to create
            string workSheetID   = "rId" + worksheetNumber.ToString();
            string worksheetName = "Table 1";

            WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(workSheetID);

            WriteDataTableToExcelWorksheet(dt, worksheetPart);

            Sheet sheet = new Sheet()
            {
                Name = worksheetName, SheetId = (UInt32Value)worksheetNumber, Id = workSheetID
            };

            sheets.Append(sheet);

            worksheetNumber++;
            // }

            workbook.Append(sheets);
        }
Example #11
0
        /// <summary>
        /// Generate all inspection reports
        /// </summary>
        /// <param name="templatePath">Path of template</param>
        /// <param name="itemsource">Item source</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateAllInspectionReports(ExportInspectionReportsModel itemsource,string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms,SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VestalisStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                int sheetId = 1;
                Sheets sheets = new Sheets();
                if (itemsource.IsSelectedServiceOrder)
                {
                    //Generate service order report
                    GenerateServiceOrder(itemsource, workbookPart, sheets,sheetId,logoPath);
                    sheetId++;
                }
                //Generate inspection reports
                GenerateAllInspectionReports(itemsource, workbookPart, sheets, sheetId, logoPath);

                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();

            }
            return ms;
        }
Example #12
0
    // Generates content of workbookPart1.
    private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
    {
        Workbook workbook1 = new Workbook()
        {
            MCAttributes = new MarkupCompatibilityAttributes()
            {
                Ignorable = "x15"
            }
        };

        workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
        workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
        workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
        Sheets sheets1 = new Sheets();
        Sheet  sheet1  = new Sheet()
        {
            Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
        };

        sheets1.Append(sheet1);
        workbook1.Append(sheets1);
        workbookPart1.Workbook = workbook1;
    }
Example #13
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

            Sheets sheets1 = new Sheets();
            sheets = sheets1;

            Sheet sheet1 = new Sheet() { Name = "oi_th", SheetId = (UInt32Value)1U, Id = "R3c8458136e1d4b6c" };
            sheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            sheet = sheet1;
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);

            workbookPart1.Workbook = workbook1;
        }
Example #14
0
        public static MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream   = new MemoryStream();
            UInt32       rowcount = 0;

            // Create the Excel document
            var document = SpreadsheetDocument.Create
                               (stream, SpreadsheetDocumentType.Workbook);
            var workbookPart  = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
            var relId         = workbookPart.GetIdOfPart(worksheetPart);

            var workbook    = new Workbook();
            var fileVersion = new FileVersion {
                ApplicationName = "Microsoft Office Excel"
            };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();

            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            var sheets = new Sheets();
            var sheet  = new Sheet {
                Name = "Sheet1", SheetId = 1, Id = relId
            };

            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();

            // Add header to the sheet
            var row = new Row {
                RowIndex = ++rowcount
            };

            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();

            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row {
                    RowIndex = ++rowcount
                };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount,
                                              dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();

            document.Close();
            return(stream);
        }
Example #15
0
        private void generateWorkbookPartContent(WorkbookPart workbookPart)
        {
            var workbook = new Workbook();
            workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            var fileVersion = new FileVersion { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
            var workbookProperties = new WorkbookProperties { DefaultThemeVersion = 124226 };

            var bookViews = new BookViews();
            var workbookView = new WorkbookView { XWindow = 630, YWindow = 600, WindowWidth = 27495, WindowHeight = 11955 };

            bookViews.Append(workbookView);

            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Документ", SheetId = 1, Id = "rId1" };

            sheets.Append(sheet);
            var calculationProperties = new CalculationProperties { CalculationId = 0 };

            workbook.Append(fileVersion);
            workbook.Append(workbookProperties);
            workbook.Append(bookViews);
            workbook.Append(sheets);
            workbook.Append(calculationProperties);

            workbookPart.Workbook = workbook;
        }
Example #16
0
File: Excel.cs Project: hieult5/MTN
        public static byte[] WriteExcel(this Dictionary <DateTime, string[, ]> lst, string excelFile)
        {
            var  template = new FileInfo(excelFile);
            bool hasData  = true;

            using (var templateStream = new MemoryStream())
            {
                using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(excelFile, true))
                {
                    WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                    Workbook     workbook     = workBookPart.Workbook;

                    var fileVersion = new FileVersion {
                        ApplicationName = "Microsoft Office Excel"
                    };
                    Workbook wb = new Workbook();
                    wb.Append(fileVersion);
                    Sheets sheets = null;

                    WorksheetPart sourceSheetPart = null;

                    // add sheet
                    foreach (KeyValuePair <DateTime, string[, ]> item in lst)
                    {
                        sheets = sheets ?? new Sheets();
                        var sheetId = sheets != null ? (uint)sheets.ChildElements.Count + 1 : 1;

                        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(HttpContext.Current.Request.MapPath("~") + "TemplateFile\\Template.xlsx", true))
                        {
                            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                            string       rId          = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id;

                            WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(rId);
                            try
                            {
                                workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
                            }
                            catch
                            {
                                workbookPart.ChangeIdOfPart(wsPart, "rId1");
                                workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
                                workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
                            }
                            workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "wsPart_" + sheetId;
                            sourceSheetPart = wsPart;

                            WorksheetPart worksheetPart = workBookPart.AddPart <WorksheetPart>(sourceSheetPart);

                            Sheet copiedSheet = new Sheet
                            {
                                Name = item.Key.ToString("dd-MM-yyyy"),
                                Id   = workBookPart.GetIdOfPart(worksheetPart)
                            };
                            copiedSheet.SheetId = sheetId;

                            sheets.Append(copiedSheet);
                            worksheetPart.Worksheet.GetFirstChild <SheetData>().UpdateSheetData(item.Key, item.Value);
                            workbookPart.ChangeIdOfPart(wsPart, "rId1");
                            workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
                        }
                    }
                    hasData = sheets != null;
                    if (sheets != null)
                    {
                        wb.Append(sheets);

                        //Save Changes
                        workBookPart.Workbook = wb;
                        wb.Save();
                        workBookPart.Workbook.Save();
                    }
                    spreadDocument.Close();
                }
                if (hasData)
                {
                    byte[] templateBytes = File.ReadAllBytes(template.FullName);
                    templateStream.Write(templateBytes, 0, templateBytes.Length);
                    var result = templateStream.ToArray();
                    templateStream.Position = 0;
                    templateStream.Flush();

                    return(result);
                }
                else
                {
                    return(null);
                }
            }
        }
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 240, YWindow = 45, WindowWidth = (UInt32Value)28320U, WindowHeight = (UInt32Value)12855U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();

            Sheet sheet1 = new Sheet(){ Name = "NOMBRE DE SOLAPA", SheetId = (UInt32Value)1U, Id = "rId1" };

            switch (_Estado.ToUpper())
            {
                case "ORDENADO":
                    {
                        sheet1.Name = "O" + _oCabecera.AnoMes + " " + _oCabecera.IdentifEspacio;
                        break;
                    }
                case "ESTIMADO":
                    {
                        sheet1.Name = "E" + _eCabecera.AnoMes + " V" + _eCabecera.Version + " " + _eCabecera.IdentifEspacio;
                        break;
                    }
                case "CERTIFICADO":
                    {
                        sheet1.Name = "C" + _cCabecera.AnoMes + " " + _cCabecera.IdentifEspacio + _cCabecera.IdentifOrigen;
                        break;
                    }
            }

            sheets1.Append(sheet1);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)124519U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }
Example #18
0
        public static void Create(string filename, DataSet ds)
        {
            // SpreadsheetDocument电子表格文档类
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);

            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            //workbookpart.SharedStringTablePart
            Workbook workbook = new Workbook();
            Sheets   sheets   = new Sheets();

            #region 创建多个 sheet 页

            //创建多个sheet
            for (int s = 0; s < ds.Tables.Count; s++)
            {
                DataTable dt    = ds.Tables[s];
                var       tname = dt.TableName;

                WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                Worksheet     worksheet     = new Worksheet();
                SheetData     sheetData     = new SheetData();

                //创建 sheet 页
                Sheet sheet = new Sheet()
                {
                    //页面关联的 WorksheetPart
                    Id      = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = UInt32Value.FromUInt32((uint)s + 1),
                    Name    = tname
                };
                sheets.Append(sheet);

                #region 创建sheet 行
                Row  row;
                uint rowIndex = 1;
                //添加表头
                row = new Row()
                {
                    RowIndex = UInt32Value.FromUInt32(rowIndex++)
                };
                sheetData.Append(row);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Cell newCell = new Cell();
                    newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
                    newCell.DataType  = new EnumValue <CellValues>(CellValues.String);
                    row.Append(newCell);
                }
                //添加内容
                object val = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = new Row()
                    {
                        RowIndex = UInt32Value.FromUInt32(rowIndex++)
                    };
                    sheetData.Append(row);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Cell newCell = new Cell();
                        val = dt.Rows[i][j];
                        newCell.CellValue = new CellValue(val.ToString().Trim(new char[] { '0', ':' }));
                        newCell.DataType  = new EnumValue <CellValues>(CellValues.String);

                        row.Append(newCell);
                    }
                }
                #endregion

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
            }
            #endregion

            workbook.Append(sheets);
            workbookpart.Workbook = workbook;

            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);

            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" };

            pivotCaches1.Append(pivotCache1);

            workbook1.Append(sheets1);
            workbook1.Append(pivotCaches1);

            workbookPart1.Workbook = workbook1;
        }
Example #20
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties();

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
            Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
            Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
            Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
            Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
            Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
            Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
            Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
            Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
            Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
            Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);
            sheets1.Append(sheet3);
            sheets1.Append(sheet4);
            sheets1.Append(sheet5);
            sheets1.Append(sheet6);
            sheets1.Append(sheet7);
            sheets1.Append(sheet8);
            sheets1.Append(sheet9);
            sheets1.Append(sheet10);
            sheets1.Append(sheet11);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
            definedName1.Text = "#N/A";
            DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
            definedName2.Text = "#N/A";
            DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
            definedName3.Text = "#N/A";
            DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
            definedName4.Text = "#N/A";
            DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
            definedName5.Text = "#N/A";
            DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
            definedName6.Text = "#N/A";
            DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
            definedName7.Text = "#N/A";
            DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
            definedName8.Text = "#N/A";
            DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
            definedName9.Text = "#N/A";

            definedNames1.Append(definedName1);
            definedNames1.Append(definedName2);
            definedNames1.Append(definedName3);
            definedNames1.Append(definedName4);
            definedNames1.Append(definedName5);
            definedNames1.Append(definedName6);
            definedNames1.Append(definedName7);
            definedNames1.Append(definedName8);
            definedNames1.Append(definedName9);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
            PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };

            pivotCaches1.Append(pivotCache1);
            pivotCaches1.Append(pivotCache2);

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
            workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();

            workbookExtension1.Append(workbookProperties2);

            WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
            workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences();
            X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" };
            X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" };
            X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" };
            X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" };
            X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" };
            X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" };
            X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" };
            X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" };
            X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" };

            timelineCacheReferences1.Append(timelineCacheReference1);
            timelineCacheReferences1.Append(timelineCacheReference2);
            timelineCacheReferences1.Append(timelineCacheReference3);
            timelineCacheReferences1.Append(timelineCacheReference4);
            timelineCacheReferences1.Append(timelineCacheReference5);
            timelineCacheReferences1.Append(timelineCacheReference6);
            timelineCacheReferences1.Append(timelineCacheReference7);
            timelineCacheReferences1.Append(timelineCacheReference8);
            timelineCacheReferences1.Append(timelineCacheReference9);

            workbookExtension2.Append(timelineCacheReferences1);

            WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
            workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };

            workbookExtension3.Append(workbookProperties3);

            workbookExtensionList1.Append(workbookExtension1);
            workbookExtensionList1.Append(workbookExtension2);
            workbookExtensionList1.Append(workbookExtension3);

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(alternateContent1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(definedNames1);
            workbook1.Append(calculationProperties1);
            workbook1.Append(pivotCaches1);
            workbook1.Append(workbookExtensionList1);

            workbookPart1.Workbook = workbook1;
        }
Example #21
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 0, YWindow = 45, WindowWidth = (UInt32Value)19155U, WindowHeight = (UInt32Value)11820U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            //Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            //Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
            //Sheet sheet3 = new Sheet() { Name = "Sheet3", SheetId = (UInt32Value)3U, Id = "rId3" };

            //sheets1.Append(sheet1);
            //sheets1.Append(sheet2);
            //sheets1.Append(sheet3);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
            definedName1.Text = "Sheet1!$B$2:$I$13";

            definedNames1.Append(definedName1);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };

            pivotCaches1.Append(pivotCache1);

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
            workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            X15.DataModel dataModel1 = new X15.DataModel();

            X15.ModelTables modelTables1 = new X15.ModelTables();
            X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };

            modelTables1.Append(modelTable1);

            dataModel1.Append(modelTables1);

            workbookExtension1.Append(dataModel1);

            WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };

            OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");

            workbookExtension2.Append(openXmlUnknownElement1);

            workbookExtensionList1.Append(workbookExtension1);
            workbookExtensionList1.Append(workbookExtension2);

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(alternateContent1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(definedNames1);
            workbook1.Append(calculationProperties1);
            workbook1.Append(pivotCaches1);
            workbook1.Append(workbookExtensionList1);

            workbookPart1.Workbook = workbook1;
        }
Example #23
0
        /// <summary>
        /// Generate an excel report dinamically
        /// </summary>
        /// <param name="model">Data source</param>
        public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
        {
            MemoryStream report = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VestalisStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;
                int numberOfColumnsCaption = model.Captions.Count;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", model.BusinessApplicationName, currentRowTitle, 5);

                string lastColumnName = excelColumnNamesTitle.Last() + "2";
                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:" + lastColumnName;
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
                //add the form name
                UpdateStringCellValue("B4", model.FormName, currentRowTitle,5);

                lastColumnName = lastColumnName.Replace("2", "4");
                //merge all cell in the form name
                mergeCell = new MergeCell();
                mergeCell.Reference = "B4:" + lastColumnName;
                mergeCells.Append(mergeCell);

                int rowIndex = 7;

                //get the names of the columns
                string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; n++)
                    excelColumnNamesCaptions[n] = GetExcelColumnName(n);

                Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                //build column names of the report
                Columns columns = new Columns();
                for (int i = 0; i < model.Captions.Count; i++)
                {
                    var caption = model.Captions[i];
                    AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
                    columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
                }
                sheetData1.Append(rowCaption);
                //add the new row with the name of the columns
                worksheet.Append(columns);
                rowIndex = 8;
                //write the data of the report
                foreach (var item in model.DataRows)
                {
                    int numberOfColumnsData = item.FieldValues.Count;
                    //get column names
                    string[] excelColumnNamesData = new string[numberOfColumnsData];
                    for (int n = 0; n < numberOfColumnsData; n++)
                        excelColumnNamesData[n] = GetExcelColumnName(n);

                    //build the data information
                    Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
                    {
                        DynamicDataRowValue col = item.FieldValues[colInx];
                        switch (col.FieldType)
                        {
                            case (int)FieldType.Catalogue:
                            case (int)FieldType.RegularExpressionText:
                            case (int)FieldType.Time:
                            case (int)FieldType.SingleTextLine:
                            case (int)FieldType.MultipleTextLine:
                            case (int)FieldType.Datepicker:
                            case (int)FieldType.Boolean:
                            case (int)FieldType.AutoComplete:
                            case (int)FieldType.StatusField:
                                AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            case (int)FieldType.Integer:
                            case (int)FieldType.Decimal:
                                AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            default:
                                break;
                        }
                    }
                    //add the new row to the report
                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }
            return report;
        }
Example #24
0
        /// <summary>
        /// Generate an excel file with the list of users
        /// </summary>
        /// <param name="dataSource">The list of users</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VocStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[4];
                for (int n = 0; n < 4; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 4; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "A2:D4";
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
                columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
                columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
                worksheet.Append(columns);

                int rowIndex = 8;

                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
                AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
                AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
                AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 9;

                //build the data
                foreach (var item in dataSource)
                {

                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };

                    AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
                    AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
                    AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);

                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }
            return ms;
        }
Example #25
0
        public static void Create(string filename, DataSet ds)
        {
            Regex regex = new Regex(@"^(-?\d+)(\.\d+)?$");

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);

            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            Workbook     workbook     = new Workbook();
            Sheets       sheets       = new Sheets();

            #region 创建多个 sheet 页

            //创建多个sheet
            for (int s = 0; s < ds.Tables.Count; s++)
            {
                DataTable dt    = ds.Tables[s];
                var       tname = dt.TableName;

                WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                Worksheet     worksheet     = new Worksheet();
                SheetData     sheetData     = new SheetData();

                //创建 sheet 页
                Sheet sheet = new Sheet()
                {
                    //页面关联的 WorksheetPart
                    Id      = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = UInt32Value.FromUInt32((uint)s + 1),
                    Name    = tname
                };
                sheets.Append(sheet);

                #region 创建sheet 行
                Row  row;
                uint rowIndex = 1;
                //添加表头
                row = new Row()
                {
                    RowIndex = UInt32Value.FromUInt32(rowIndex++)
                };
                sheetData.Append(row);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Cell newCell = new Cell();
                    newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
                    newCell.DataType  = new EnumValue <CellValues>(CellValues.String);
                    row.Append(newCell);
                }
                //添加内容
                object val = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = new Row()
                    {
                        RowIndex = UInt32Value.FromUInt32(rowIndex++)
                    };
                    sheetData.Append(row);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Cell newCell = new Cell();
                        val = dt.Rows[i][j];
                        var str = (val?.ToString() ?? "").Trim();
                        if (str.Length == 0 || regex.IsMatch(str))
                        {
                            newCell.CellValue = new CellValue(str);
                            newCell.DataType  = new EnumValue <CellValues>(CellValues.Number);
                        }
                        else
                        {
                            newCell.CellValue = new CellValue(val.ToString());
                            newCell.DataType  = new EnumValue <CellValues>(CellValues.String);
                        }
                        row.Append(newCell);
                    }
                }
                #endregion

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
            }
            #endregion

            workbook.Append(sheets);
            workbookpart.Workbook = workbook;

            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)152511U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }
Example #27
0
        /// <summary>
        /// Generate the report for catalogue categories
        /// </summary>
        /// <param name="templatePath">Path of the template</param>
        /// <param name="itemSource">Item source</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VestalisStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[2];
                for (int n = 0; n < 2; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 2; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", LanguageResource.CatalogueValuesReport, currentRowTitle, 5);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:B4";
                mergeCells.Append(mergeCell);
                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
                worksheet.Append(columns);

                int rowIndex = 8;
                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
                AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
                sheetData1.Append(rowData);

                rowIndex = 9;
                rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
                AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
                sheetData1.Append(rowData);

                rowIndex = 11;

                rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
                AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 12;

                foreach (var item in itemSource.SearchResult.Collection)
                {
                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }

            return ms;
        }
Example #28
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Slicer\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "Slicer_Column1" };
            definedName1.Text = "#N/A";
            DefinedName definedName2 = new DefinedName(){ Name = "Slicer_Column2" };
            definedName2.Text = "#N/A";
            DefinedName definedName3 = new DefinedName(){ Name = "Slicer_Column3" };
            definedName3.Text = "#N/A";

            definedNames1.Append(definedName1);
            definedNames1.Append(definedName2);
            definedNames1.Append(definedName3);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
            workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();

            workbookExtension1.Append(workbookProperties2);

            WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{46BE6895-7355-4a93-B00E-2C351335B9C9}" };
            workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            X15.SlicerCaches slicerCaches1 = new X15.SlicerCaches();
            slicerCaches1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.SlicerCache slicerCache1 = new X14.SlicerCache(){ Id = "rId2" };
            X14.SlicerCache slicerCache2 = new X14.SlicerCache(){ Id = "rId3" };
            X14.SlicerCache slicerCache3 = new X14.SlicerCache(){ Id = "rId4" };

            slicerCaches1.Append(slicerCache1);
            slicerCaches1.Append(slicerCache2);
            slicerCaches1.Append(slicerCache3);

            workbookExtension2.Append(slicerCaches1);

            WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
            workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };

            workbookExtension3.Append(workbookProperties3);

            workbookExtensionList1.Append(workbookExtension1);
            workbookExtensionList1.Append(workbookExtension2);
            workbookExtensionList1.Append(workbookExtension3);

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(alternateContent1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(definedNames1);
            workbook1.Append(calculationProperties1);
            workbook1.Append(workbookExtensionList1);

            workbookPart1.Workbook = workbook1;
        }
        //создает нову книгу с листами Excel
        protected virtual void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
        {
            Workbook workbook = new Workbook();

            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet() { Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets.Append(sheet);
            workbook.Append(sheets);
            workbookPart.Workbook = workbook;
        }
Example #30
0
        public void convertToExcel(string padXml)
        {
            //maak dataset en vul met ingevoerde xml
            DataSet dsXML = new DataSet();

            dsXML.ReadXml(padXml);
            DataTable tblXML = dsXML.Tables[2];

            //opslaan
            string padXlsx = Path.GetDirectoryName(padXml) + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + ".xlsx";

            //spreadsheet
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(padXlsx, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart  workbookPart  = spreadsheetDocument.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
                Workbook      workbook      = new Workbook();
                FileVersion   fv            = new FileVersion();
                fv.ApplicationName = "Microsoft Office Excel";
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();

                //styles
                WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                stylesPart.Stylesheet = new Stylesheet();
                stylesPart.Stylesheet.Save();

                //kolommen
                DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns();
                for (int i = 1; i < tblXML.Columns.Count; i++)
                {
                    Column column = new Column();
                    column.Min = Convert.ToUInt32(i);
                    column.Max = Convert.ToUInt32(i + 1);
                    int lengte = tblXML.Rows[13][i - 1].ToString().Length;
                    if (lengte < 10)
                    {
                        lengte = 10;
                    }
                    else if (lengte > 35)
                    {
                        lengte = 35;
                    }
                    else
                    {
                        lengte += 5;
                    }
                    column.Width   = lengte;
                    column.BestFit = true;
                    columns.Append(column);
                }
                worksheet.Append(columns);

                //header
                Row header = new Row();
                header.RowIndex = (UInt32)1;

                foreach (DataColumn columnInDataTable in tblXML.Columns)
                {
                    Cell headerCell = createTextCell(tblXML.Columns.IndexOf(columnInDataTable) + 1, 1, columnInDataTable.ColumnName);
                    //headerCell.StyleIndex = 1;
                    header.AppendChild(headerCell);
                }
                sheetData.AppendChild(header);

                // Add a row to the cell table.
                for (int i = 1; i < tblXML.Rows.Count; i++)
                {
                    Row row;
                    row = new Row()
                    {
                        RowIndex = Convert.ToUInt32(i) + 1
                    };

                    for (int j = 0; j < tblXML.Columns.Count; j++)
                    {
                        Cell newCell = new Cell()
                        {
                            CellReference = getColumnName(i),
                            DataType      = CellValues.Number,
                            //StyleIndex = 6,
                            CellValue = new CellValue(tblXML.Rows[i][j].ToString()),
                        };
                        row.Append(newCell);
                    }
                    sheetData.Append(row);
                }

                //autofilter
                string     laatsteKolom = zoekLaatsteKolom(tblXML);
                AutoFilter autoFilter   = new AutoFilter();
                autoFilter.Reference = "A1:" + laatsteKolom + "1";

                //aantal auto's
                Row aantalAutosRow = new Row();
                aantalAutosRow.RowIndex = Convert.ToUInt32((tblXML.Rows.Count) + 2);

                Cell aantalAutosCell = new Cell();
                aantalAutosCell.CellReference = getColumnName(1);
                aantalAutosCell.CellValue     = new CellValue("Hoi");
                aantalAutosCell.DataType      = CellValues.String;
                //CellFormula berekenAantalAutos = new CellFormula();
                //berekenAantalAutos.Text = "=COUNTA(A2:A" + tblXML.Rows.Count.ToString();

                //aantalAutosCell.Append(berekenAantalAutos);
                aantalAutosRow.Append(aantalAutosCell);
                sheetData.Append(aantalAutosRow);

                worksheet.Append(sheetData);
                worksheet.Append(autoFilter);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                Sheets sheets = new Sheets();
                Sheet  sheet  = new Sheet();
                sheet.Name    = "Voorraad";
                sheet.SheetId = 1;
                sheet.Id      = workbookPart.GetIdOfPart(worksheetPart);
                sheets.Append(sheet);
                workbook.Append(fv);
                workbook.Append(sheets);

                spreadsheetDocument.WorkbookPart.Workbook = workbook;
                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();
            }
        }
Example #31
0
        /// <summary>

        /// Call this method from the Page providing the desired information

        /// </summary>

        /// <param name="dataTable">The records to be written in excel</param>

        /// <param name="excelFilename">Name of the file</param>

        /// <param name="sheetName">Name of the sheet</param>

        /// <param name="filters">Search key and value based on which the datatable is generated</param>

        /// <param name="columnSize">column name and size</param>

        /// <returns></returns>

        public static bool CreateExcelDocument(DataTable dataTable, string excelFilename, string sheetName, Dictionary <string, string> filters, Dictionary <string, int> columnSize)

        {
            try

            {
                using (SpreadsheetDocument objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))

                {
                    int cellSize;

                    WorkbookPart wbp = objExcelDoc.AddWorkbookPart();

                    WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();

                    Workbook wb = new Workbook();

                    FileVersion fv = new FileVersion();

                    fv.ApplicationName = "Microsoft Office Excel";

                    Worksheet workSheet = new Worksheet();

                    WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();

                    wbsp.Stylesheet = CreateStylesheet();

                    wbsp.Stylesheet.Save();

                    Columns columns = new Columns();

                    for (int i = 1; i <= columnSize.Count(); i++)

                    {
                        columnSize.TryGetValue(columnSize.Keys.ElementAt(i - 1).ToString(), out cellSize);

                        columns.Append(CreateColumnData(Convert.ToUInt32(i), Convert.ToUInt32(i), cellSize));
                    }

                    workSheet.Append(columns);

                    SheetData sheetData = new SheetData();

                    for (UInt32 i = 2; i <= 1 + filters.Count(); i++)

                    {
                        sheetData.Append(CreateFilters(i, filters));
                    }

                    sheetData.Append(CreateColumnHeader(Convert.ToUInt32(filters.Count() + 3), columnSize));

                    UInt32 index = Convert.ToUInt32(filters.Count() + 4);

                    foreach (DataRow dr in dataTable.Rows)

                    {
                        sheetData.Append(CreateContent(index, dr, columnSize.Count()));

                        index++;
                    }

                    workSheet.Append(sheetData);

                    wsp.Worksheet = workSheet;

                    Sheets sheets = new Sheets();

                    Sheet sheet = new Sheet();

                    sheet.Name = sheetName;

                    sheet.SheetId = 1;

                    sheet.Id = wbp.GetIdOfPart(wsp);

                    sheets.Append(sheet);

                    wb.Append(fv);

                    wb.Append(sheets);

                    objExcelDoc.WorkbookPart.Workbook = wb;

                    objExcelDoc.WorkbookPart.Workbook.Save();

                    objExcelDoc.Close();
                }
            }

            catch (Exception ex)

            {
                throw;
            }

            return(true);
        }
Example #32
0
        public ActionResult ConvertToExcel()
        {
            MemoryStream        ms  = new MemoryStream();
            SpreadsheetDocument xl  = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart        wbp = xl.AddWorkbookPart();
            WorksheetPart       wsp = wbp.AddNewPart <WorksheetPart>();
            Workbook            wb  = new Workbook();
            FileVersion         fv  = new FileVersion();

            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();

            SheetData sd   = new SheetData();
            var       list = GetPerson();

            Row  r1 = new Row();
            Cell c1 = new Cell();
            Cell c2 = new Cell();
            Cell c3 = new Cell();
            Cell c4 = new Cell();


            c1.DataType = CellValues.String;
            c2.DataType = CellValues.String;
            c3.DataType = CellValues.String;
            c4.DataType = CellValues.String;

            c1.CellValue = new CellValue("Ad");
            c2.CellValue = new CellValue("Soyad");
            c3.CellValue = new CellValue("Adres");
            c4.CellValue = new CellValue("Email");
            r1.Append(c1);
            r1.Append(c2);
            r1.Append(c3);
            r1.Append(c4);

            sd.Append(r1);

            foreach (var item in list.Persons)
            {
                Row  r2 = new Row();
                Cell c5 = new Cell();
                Cell c6 = new Cell();
                Cell c7 = new Cell();
                Cell c8 = new Cell();

                c5.DataType = CellValues.String;
                c6.DataType = CellValues.String;
                c7.DataType = CellValues.String;
                c8.DataType = CellValues.String;

                c5.CellValue = new CellValue(item.Name);
                c6.CellValue = new CellValue(item.Surname);
                c7.CellValue = new CellValue(item.Address);
                c8.CellValue = new CellValue(item.Email);

                r2.Append(c5);
                r2.Append(c6);
                r2.Append(c7);
                r2.Append(c8);

                sd.Append(r2);
            }


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet  sheet  = new Sheet();

            sheet.Name    = "first sheet";
            sheet.SheetId = 1;
            sheet.Id      = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);

            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();

            string fileName = "testOpenXml.xlsx";

            Response.Clear();

            byte[] dt = ms.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
            Response.BinaryWrite(dt);
            Response.Flush();
            Response.End();

            return(View());
        }
        private void GenerateWorksheetAContentSubsectorPollutionSourceFieldSheet(WorksheetPart worksheetPart, Workbook workbook, string SheetName, int SheetOrdinal, bool ActivePollutionSource)
        {
            BaseEnumService      baseEnumService      = new BaseEnumService(_TaskRunnerBaseService._BWObj.appTaskModel.Language);
            TVItemService        tvItemService        = new TVItemService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
            PolSourceSiteService polSourceSiteService = new PolSourceSiteService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
            //PolSourceObservationService polSourceObservationService = new PolSourceObservationService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
            //MWQMSubsectorService mwqmSubsectorService = new MWQMSubsectorService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);

            MergeCells mergeCells = new MergeCells();
            Row        row        = new Row();
            Cell       cell       = new Cell();
            Hyperlinks hyperlinks = new Hyperlinks();
            string     Id         = XlsxBase.sheetNameAndIDList[SheetOrdinal].SheetID;

            XlsxBase.CurrentColumn     = 0;
            XlsxBase.CurrentRow        = 0;
            XlsxBase.CurrentColumnProp = 0;
            Worksheet worksheet = new Worksheet()
            {
                MCAttributes = new MarkupCompatibilityAttributes()
                {
                    Ignorable = "x14ac"
                }
            };

            worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            SheetViews sheetViews = new SheetViews();

            SheetView sheetView = new SheetView()
            {
                TabSelected = true, WorkbookViewId = (UInt32Value)0U
            };
            Selection selection = new Selection()
            {
                ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>()
                {
                    InnerText = "A1"
                }
            };

            sheetView.Append(selection);

            sheetViews.Append(sheetView);
            SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
            {
                DefaultRowHeight = 15D, DyDescent = 0.25D
            };

            Columns        columns         = new Columns();
            List <double?> columnWidthList = new List <double?>()
            {
                7D, 10D, 90D, 12D, 12D, 10D, 14D, 20D
            };

            foreach (double?width in columnWidthList)
            {
                Column colum = XlsxBase.AddColumnProp(columns, width);
            }

            SheetData sheetData = new SheetData();

            TVItemModel tvItemModelSubsector = tvItemService.GetTVItemModelWithTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);

            //MWQMSubsectorModel mwqmSubsectorModel = mwqmSubsectorService.GetMWQMSubsectorModelWithMWQMSubsectorTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);

            XlsxBase.CurrentRowHeight = 28D;
            XlsxBase.CurrentFontSize  = 24;
            XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
            XlsxBase.CurrentBorderStyleValue         = BorderStyleValues.Thin;
            XlsxBase.CurrentBottomBorder             = true;

            row = XlsxBase.AddRow();
            string URL = _TaskRunnerBaseService.GetUrlFromTVItem(tvItemModelSubsector);

            XlsxBase.CurrentFontColor = System.Drawing.Color.Blue;
            cell = XlsxBase.AddCellHyperlink(hyperlinks, row, tvItemModelSubsector.TVText, URL);
            XlsxBase.CurrentFontColor = null;
            //cell = XlsxBase.AddCellString(row, tvItemModel.TVText);
            cell = XlsxBase.AddCellString(row, null);
            sheetData.Append(row);

            MergeCell mergeCell = new MergeCell()
            {
                Reference = "A" + XlsxBase.CurrentRow.ToString() + ":H" + XlsxBase.CurrentRow.ToString()
            };

            mergeCells.Append(mergeCell);

            List <TVItemModel> tvItemModelPolSourceList = tvItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID, TVTypeEnum.PolSourceSite);

            XlsxBase.CurrentRowHeight                = 24D;
            XlsxBase.CurrentFontSize                 = 18;
            XlsxBase.CurrentBorderStyleValue         = BorderStyleValues.Thin;
            XlsxBase.CurrentBottomBorder             = true;
            XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;

            row = XlsxBase.AddRow();
            XlsxBase.CurrentFontColor = null;
            cell = XlsxBase.AddCellString(row, "Site");
            cell = XlsxBase.AddCellString(row, "Type");
            cell = XlsxBase.AddCellString(row, "Observation");
            cell = XlsxBase.AddCellString(row, "Lat");
            cell = XlsxBase.AddCellString(row, "Lng");
            cell = XlsxBase.AddCellString(row, "Active");
            cell = XlsxBase.AddCellString(row, "Update");
            cell = XlsxBase.AddCellString(row, "Civic Address");
            sheetData.Append(row);

            XlsxBase.CurrentRowHeight = 160D;
            XlsxBase.CurrentFontSize  = 12;
            XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;

            List <PolSourceSiteModel> polSourceSiteModelList = polSourceSiteService.GetPolSourceSiteModelListWithSubsectorTVItemIDDB(tvItemModelSubsector.TVItemID);

            int countPolSourceSite = 0;

            foreach (PolSourceSiteModel polSourceSiteModel in polSourceSiteModelList.OrderBy(c => c.Site).ToList())
            {
                bool IsActive = (from c in tvItemModelPolSourceList
                                 where c.TVItemID == polSourceSiteModel.PolSourceSiteTVItemID
                                 select c.IsActive).FirstOrDefault();

                if (ActivePollutionSource != IsActive)
                {
                    continue;
                }

                countPolSourceSite += 1;
                PolSourceObservationModel polSourceObservationModel = polSourceSiteService._PolSourceObservationService.GetPolSourceObservationModelLatestWithPolSourceSiteTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);

                List <MapInfoPointModel> mapInfoPointModelList = polSourceSiteService._MapInfoService._MapInfoPointService.GetMapInfoPointModelListWithTVItemIDAndTVTypeAndMapInfoDrawTypeDB(polSourceSiteModel.PolSourceSiteTVItemID, TVTypeEnum.PolSourceSite, MapInfoDrawTypeEnum.Point);

                row = XlsxBase.AddRow();

                if (countPolSourceSite % 5 == 0)
                {
                    XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
                    XlsxBase.CurrentBottomBorder     = true;
                }
                else
                {
                    XlsxBase.CurrentBorderStyleValue = null;
                    XlsxBase.CurrentBottomBorder     = false;
                }

                XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
                cell = XlsxBase.AddCellString(row, polSourceSiteModel.Site.ToString());

                if (polSourceObservationModel != null)
                {
                    List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);

                    string SelectedObservation = "Selected: \r\n";
                    int    PolSourceObsInfoInt = 0;
                    foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
                    {
                        List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
                        PolSourceObsInfoInt = observationInfoList.Where(c => (c > 10500 && c < 10599) || (c > 15200 && c < 15299)).FirstOrDefault();

                        foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
                        {
                            SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
                        }
                        SelectedObservation += "\r\n\r\n";
                    }
                    if (PolSourceObsInfoInt > 0)
                    {
                        cell = XlsxBase.AddCellString(row, baseEnumService.GetEnumText_PolSourceObsInfoEnum((PolSourceObsInfoEnum)PolSourceObsInfoInt));
                    }
                    else
                    {
                        cell = XlsxBase.AddCellString(row, "Empty");
                    }
                }

                XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;
                XlsxBase.WrapText = true;

                if (polSourceObservationModel != null)
                {
                    List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);

                    string SelectedObservation = "Selected: \r\n";
                    int    PolSourceObsInfoInt = 0;
                    foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
                    {
                        List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
                        foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
                        {
                            SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
                        }
                        SelectedObservation += "\r\n\r\n";
                    }
                    if (PolSourceObsInfoInt > 0)
                    {
                        cell = XlsxBase.AddCellString(row, "Written: \r\n" + (string.IsNullOrWhiteSpace(polSourceObservationModel.Observation_ToBeDeleted) ? "" : polSourceObservationModel.Observation_ToBeDeleted.ToString()) + "\r\n\r\n"
                                                      + SelectedObservation);
                    }
                    else
                    {
                        cell = XlsxBase.AddCellString(row, "Written: \r\n\r\n" + SelectedObservation);
                    }
                }

                XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
                Alignment alignment1 = new Alignment()
                {
                    WrapText = true
                };

                if (mapInfoPointModelList.Count > 0)
                {
                    cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lat.ToString("F5"));
                    cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lng.ToString("F5"));
                }
                else
                {
                    cell = XlsxBase.AddCellString(row, "");
                    cell = XlsxBase.AddCellString(row, "");
                }

                TVItemModel tvItemModelPolSource = tvItemService.GetTVItemModelWithTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);

                cell = XlsxBase.AddCellString(row, tvItemModelPolSource.IsActive.ToString());
                cell = XlsxBase.AddCellString(row, polSourceObservationModel.ObservationDate_Local.ToString("yyyy MMM dd"));
                cell = XlsxBase.AddCellString(row, "empty");

                sheetData.Append(row);
            }

            if (countPolSourceSite > 0)
            {
                DefinedNames definedNames1 = new DefinedNames();
                DefinedName  definedName1  = new DefinedName()
                {
                    Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true
                };
                definedName1.Text = "'" + SheetName + "'" + "!$A$2:$H$" + (countPolSourceSite + 2).ToString();

                definedNames1.Append(definedName1);
                workbook.Append(definedNames1);

                AutoFilter autoFilter = new AutoFilter()
                {
                    Reference = "a2:B" + (countPolSourceSite + 2).ToString()
                };
                worksheet.Append(autoFilter);
            }

            PageMargins pageMargins = new PageMargins()
            {
                Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
            };
            PageSetup pageSetup = new PageSetup()
            {
                Orientation = OrientationValues.Portrait, Id = "rId" + SheetOrdinal.ToString()
            };

            worksheet.Append(sheetViews);
            worksheet.Append(sheetFormatProperties);

            if (columns.ChildElements.Count > 0)
            {
                worksheet.Append(columns);
            }

            worksheet.Append(sheetData);

            mergeCells.Count = (UInt32Value)((UInt32)mergeCells.ChildElements.Count);
            if (mergeCells.ChildElements.Count > 0)
            {
                worksheet.Append(mergeCells);
            }


            if (XlsxBase.UsedHyperlinkList.Count > 0)
            {
                worksheet.Append(hyperlinks);
            }

            worksheet.Append(pageMargins);
            worksheet.Append(pageSetup);

            worksheetPart.Worksheet = worksheet;
        }
Example #34
0
        private void BuildWorkbook(string filename)
        {
            try
            {
                using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
                {
                    var wbp = xl.AddWorkbookPart();
                    var wsp = wbp.AddNewPart <WorksheetPart>();
                    var wb  = new Workbook();
                    var fv  = new FileVersion {
                        ApplicationName = "Microsoft Office Excel"
                    };
                    var ws = new Worksheet();
                    var sd = new SheetData();

                    var wbsp = wbp.AddNewPart <WorkbookStylesPart>();
                    wbsp.Stylesheet = CreateStylesheet();
                    wbsp.Stylesheet.Save();

                    var sImagePath = "polymathlogo.png";
                    var dp         = wsp.AddNewPart <DrawingsPart>();
                    var imgp       = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                    using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
                    {
                        imgp.FeedData(fs);
                    }

                    var nvdp = new NonVisualDrawingProperties
                    {
                        Id          = 1025,
                        Name        = "Picture 1",
                        Description = "polymathlogo"
                    };
                    var picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks
                    {
                        NoChangeAspect     = true,
                        NoChangeArrowheads = true
                    };
                    var nvpdp = new NonVisualPictureDrawingProperties
                    {
                        PictureLocks = picLocks
                    };
                    var nvpp = new NonVisualPictureProperties
                    {
                        NonVisualDrawingProperties        = nvdp,
                        NonVisualPictureDrawingProperties = nvpdp
                    };

                    var stretch = new DocumentFormat.OpenXml.Drawing.Stretch
                    {
                        FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
                    };

                    var blip = new DocumentFormat.OpenXml.Drawing.Blip
                    {
                        Embed            = dp.GetIdOfPart(imgp),
                        CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
                    };

                    var blipFill = new BlipFill
                    {
                        Blip            = blip,
                        SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle()
                    };
                    blipFill.Append(stretch);

                    var offset = new DocumentFormat.OpenXml.Drawing.Offset
                    {
                        X = 0,
                        Y = 0
                    };
                    var t2d = new DocumentFormat.OpenXml.Drawing.Transform2D
                    {
                        Offset = offset
                    };

                    var bm = Xwt.Drawing.Image.FromFile(sImagePath).ToBitmap();
                    //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                    //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                    //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                    var extents = new DocumentFormat.OpenXml.Drawing.Extents
                    {
                        Cx = (long)bm.Width * (long)((float)914400 / bm.PixelWidth),
                        Cy = (long)bm.Height * (long)((float)914400 / bm.PixelHeight)
                    };
                    bm.Dispose();
                    t2d.Extents = extents;
                    var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry
                    {
                        Preset          = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle,
                        AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList()
                    };
                    var sp = new ShapeProperties
                    {
                        BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto,
                        Transform2D    = t2d
                    };
                    sp.Append(prstGeom);
                    sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

                    var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
                    {
                        NonVisualPictureProperties = nvpp,
                        BlipFill        = blipFill,
                        ShapeProperties = sp
                    };

                    var pos = new Position {
                        X = 0, Y = 0
                    };
                    Extent ext = new Extent {
                        Cx = extents.Cx, Cy = extents.Cy
                    };
                    var anchor = new AbsoluteAnchor
                    {
                        Position = pos,
                        Extent   = ext
                    };
                    anchor.Append(picture);
                    anchor.Append(new ClientData());

                    var wsd = new WorksheetDrawing();
                    wsd.Append(anchor);
                    var drawing = new Drawing {
                        Id = dp.GetIdOfPart(imgp)
                    };

                    wsd.Save(dp);

                    UInt32 index;
                    Random rand = new Random();

                    sd.Append(CreateHeader(10));
                    sd.Append(CreateColumnHeader(11));

                    for (index = 12; index < 30; ++index)
                    {
                        sd.Append(CreateContent(index, ref rand));
                    }

                    ws.Append(sd);
                    ws.Append(drawing);
                    wsp.Worksheet = ws;
                    wsp.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet  sheet  = new Sheet
                    {
                        Name    = "Sheet1",
                        SheetId = 1,
                        Id      = wbp.GetIdOfPart(wsp)
                    };
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);

                    xl.WorkbookPart.Workbook = wb;
                    xl.WorkbookPart.Workbook.Save();
                    xl.Close();
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.ToString());
            }
        }
        public void Create <T>(
            string fileName,
            List <T> objects,
            string sheetName,
            List <string> headerNames)
        {
            //Open the copied template workbook.
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart  workbookPart  = myWorkbook.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();

                // Create Styles and Insert into Workbook
                WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Stylesheet         styles     = new CustomStylesheet();
                styles.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                Workbook    workbook    = new Workbook();
                FileVersion fileVersion = new FileVersion {
                    ApplicationName = "Microsoft Office Excel"
                };

                SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
                Worksheet worksheet = new Worksheet();

                int numCols = headerNames.Count;
                int width   = headerNames.Max(h => h.Length) + 10;

                Columns columns = new Columns();
                for (int col = 0; col < numCols; col++)
                {
                    if (col == 0)
                    {
                        width = 30;
                    }
                    else if (col == 1)
                    {
                        width = 30;
                    }
                    else if (col == 2)
                    {
                        width = 40;
                    }
                    else if (col == 3)
                    {
                        width = 72;
                    }
                    else if (col == 4)
                    {
                        width = 76;
                    }
                    else if (col == 5)
                    {
                        width = 12;
                    }
                    else if (col == 6)
                    {
                        width = 15;
                    }
                    else if (col == 7)
                    {
                        width = 12;
                    }
                    else if (col == 8)
                    {
                        width = 15;
                    }
                    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    columns.AppendChild(c);
                }
                worksheet.Append(columns);

                Sheets sheets = new Sheets();
                Sheet  sheet  = new Sheet {
                    Name = sheetName, SheetId = 1, Id = relId
                };
                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();


                myWorkbook.WorkbookPart.Workbook = workbook;
                myWorkbook.WorkbookPart.Workbook.Save();
                myWorkbook.Close();
            }
        }
Example #36
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="sheetName">generated sheet name, Do not set empty value for this parameters</param>
        /// <param name="headerNames"></param>
        /// <returns></returns>
        public Stream Do <T>(List <T> objects, string sheetName, ExcelHeaderList headerNames)
        {
            var stream = new MemoryStream();

            sheetName = string.IsNullOrEmpty(sheetName) ? "Mayhedi_Sheet" : sheetName;

            using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                var workbookPart  = document.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(sheetName);

                // Create Styles and Insert into Workbook
                var        stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Stylesheet styles     = new CustomStylesheet();
                styles.Save(stylesPart);

                var relId = workbookPart.GetIdOfPart(worksheetPart);

                var workbook    = new Workbook();
                var fileVersion = new FileVersion {
                    ApplicationName = "Microsoft Office Excel"
                };

                var data = objects.Select(o => (object)o).ToList();

                if (headerNames == null)
                {
                    headerNames = new ExcelHeaderList();
                    foreach (var o in ObjUtility.GetPropertyInfo(objects[0]))
                    {
                        headerNames.Add(o, o);
                    }
                }

                var sheetData = CreateSheetData(data, headerNames, stylesPart);

                var worksheet = new Worksheet();

                var numCols = headerNames.Count;
                var width   = 20;//headerNames.Max(h => h.Length) + 5;

                var columns = new Columns();
                for (var col = 0; col < numCols; col++)
                {
                    var c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    if (c != null)
                    {
                        columns.Append(c);
                    }
                }
                worksheet.Append(columns);

                var sheets = new Sheets();
                var sheet  = new Sheet {
                    Name = sheetName, SheetId = 1, Id = relId
                };

                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                document.WorkbookPart.Workbook = workbook;
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }

            return(stream);
        }
Example #37
0
        private void HandleSaveAndClose(bool save, bool close)
        {
            if (save)
            {
                // Create dialog
                SaveFileDialog saveFileDialog1 = new SaveFileDialog
                {
                    Filter = "Excel|*.xlsx",
                    Title  = "Save file"
                };

                // Open dialog
                saveFileDialog1.ShowDialog();

                // Save data when filename is present
                if (saveFileDialog1.FileName != "")
                {
                    if (File.Exists(saveFileDialog1.FileName))
                    {
                        File.Delete(saveFileDialog1.FileName);
                    }


                    using (FileStream fileStream = (FileStream)saveFileDialog1.OpenFile())
                        using (var spreadsheetDocument = SpreadsheetDocument.Create(fileStream, SpreadsheetDocumentType.Workbook))
                        {
                            WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                            Workbook     workbook     = new Workbook();
                            workbookPart.Workbook = workbook;
                            Sheets sheets = new Sheets();
                            workbook.Append(sheets);
                            Sheet sheet = new Sheet()
                            {
                                SheetId = 1, Id = "rId1", Name = "Events",
                            };
                            sheets.Append(sheet);
                            WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
                            Worksheet     worksheet     = new Worksheet();
                            SheetData     sheetData     = new SheetData();
                            worksheet.Append(sheetData);
                            worksheetPart.Worksheet = worksheet;

                            UInt32Value rowIndex = 1;
                            Row         row      = new Row()
                            {
                                RowIndex = rowIndex
                            };
                            row.Append(new Cell()
                            {
                                CellReference = $"A{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Timestamp")
                            });
                            row.Append(new Cell()
                            {
                                CellReference = $"B{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"ID")
                            });
                            row.Append(new Cell()
                            {
                                CellReference = $"C{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Type")
                            });
                            row.Append(new Cell()
                            {
                                CellReference = $"D{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"EventCode")
                            });
                            row.Append(new Cell()
                            {
                                CellReference = $"E{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"EventDescription")
                            });
                            row.Append(new Cell()
                            {
                                CellReference = $"F{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Information")
                            });

                            sheetData.Append(row);

                            foreach (var fiscalArchiveEvent in _fiscalArchiveEvents)
                            {
                                rowIndex++;

                                row = new Row()
                                {
                                    RowIndex = rowIndex
                                };
                                row.Append(new Cell()
                                {
                                    CellReference = $"A{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"{fiscalArchiveEvent.Timestamp:yyyy-MM-dd HH:mm:ss}")
                                });
Example #38
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\WorkBookPr\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
            workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            X15.WorkbookProperties workbookProperties2 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };

            workbookExtension1.Append(workbookProperties2);

            workbookExtensionList1.Append(workbookExtension1);

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(alternateContent1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);
            workbook1.Append(workbookExtensionList1);

            workbookPart1.Workbook = workbook1;
        }
        protected void ExcelDl_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            Chart1.RenderControl(hw);

            using (MemoryStream stream = new MemoryStream())
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";

                    WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
                    Worksheet ws = new Worksheet();
                    SheetData sd = new SheetData();

                    // add contents
                    List<string> values = new List<string>();

                    //values.Add(lblSectionFunctionSelected.Text);

                    uint i = 1;
                    foreach (var value in values)
                    {
                        UInt32Value rowIndex = UInt32Value.FromUInt32(i);
                        var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
                        sd.Append(row);

                        var cell = new Cell
                        {
                            CellValue = new CellValue(value),
                            DataType = new EnumValue<CellValues>(CellValues.String)
                        };
                        row.InsertAt(cell, 0);
                        i++;
                    }
                    // add image
                    DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
                    ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                    using (FileStream fs = new FileStream(fileName, FileMode.Open))
                    {
                        imgp.FeedData(fs);
                    }
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
                    nvdp.Id = 1025;
                    nvdp.Name = "Picture 1";
                    nvdp.Description = "Chart";
                    DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                    picLocks.NoChangeAspect = true;
                    picLocks.NoChangeArrowheads = true;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
                    nvpdp.PictureLocks = picLocks;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
                    nvpp.NonVisualDrawingProperties = nvdp;
                    nvpp.NonVisualPictureDrawingProperties = nvpdp;

                    DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                    stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill();
                    DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                    blip.Embed = dp.GetIdOfPart(imgp);
                    blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                    blipFill.Blip = blip;
                    blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                    blipFill.Append(stretch);

                    DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                    DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                    offset.X = 0;
                    offset.Y = 0;
                    t2d.Offset = offset;
                    Bitmap bm = new Bitmap(fileName);
                    //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                    //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                    //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
                    t2d.Extents = extents;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
                    sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                    sp.Transform2D = t2d;
                    DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                    prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                    prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                    sp.Append(prstGeom);
                    sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
                    picture.NonVisualPictureProperties = nvpp;
                    picture.BlipFill = blipFill;
                    picture.ShapeProperties = sp;

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();
                    pos.X = 0;
                    pos.Y = 10;
                    Extent ext = new Extent();
                    ext.Cx = extents.Cx;
                    ext.Cy = extents.Cy;
                    AbsoluteAnchor anchor = new AbsoluteAnchor();
                    anchor.Position = pos;
                    anchor.Extent = ext;
                    anchor.Append(picture);
                    anchor.Append(new ClientData());
                    WorksheetDrawing wsd = new WorksheetDrawing();
                    wsd.Append(anchor);
                    Drawing drawing = new Drawing();
                    drawing.Id = dp.GetIdOfPart(imgp);

                    wsd.Save(dp);

                    ws.Append(sd);
                    ws.Append(drawing);
                    wsp.Worksheet = ws;
                    wsp.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet();
                    sheet.Name = "history chart";
                    sheet.SheetId = 1;
                    sheet.Id = wbp.GetIdOfPart(wsp);
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);

                    spreadsheetDocument.WorkbookPart.Workbook = wb;
                    spreadsheetDocument.WorkbookPart.Workbook.Save();
                    spreadsheetDocument.Close();
                }
                File.WriteAllBytes(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx", stream.ToArray());
            }
            Response.WriteFile(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx");
            Response.Flush();
            Response.Close();
            Response.End();
        }
Example #40
0
        public static void CopySheet(string filename, string sheetName, string clonedSheetName, string destFileName)
        {
            //Open workbook
            using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
            {
                WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
                //Get the source sheet to be copied
                WorksheetPart         sourceSheetPart   = GetWorkSheetPart(workbookPart, sheetName);
                SharedStringTablePart sharedStringTable = workbookPart.SharedStringTablePart;
                //Take advantage of AddPart for deep cloning
                using (SpreadsheetDocument newXLFile = SpreadsheetDocument.Create(destFileName, SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart          newWorkbookPart      = newXLFile.AddWorkbookPart();
                    SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart <SharedStringTablePart>(sharedStringTable);
                    WorksheetPart         newWorksheetPart     = newWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
                    //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
                    int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
                    tableId = numTableDefParts;

                    //Clean up table definition parts (tables need unique ids)
                    if (numTableDefParts != 0)
                    {
                        FixupTableParts(newWorksheetPart, numTableDefParts);
                    }
                    //There should only be one sheet that has focus
                    CleanView(newWorksheetPart);

                    var fileVersion = new FileVersion {
                        ApplicationName = "Microsoft Office Excel"
                    };

                    //Worksheet ws = newWorksheetPart.Worksheet;
                    Workbook wb = new Workbook();
                    wb.Append(fileVersion);

                    //Add new sheet to main workbook part
                    Sheets sheets = null;
                    //int sheetCount = wb.Sheets.Count();
                    if (wb.Sheets != null)
                    {
                        sheets = wb.GetFirstChild <Sheets>();
                    }
                    else
                    {
                        sheets = new Sheets();
                    }

                    Sheet copiedSheet = new Sheet
                    {
                        Name = clonedSheetName,
                        Id   = newWorkbookPart.GetIdOfPart(newWorksheetPart)
                    };
                    if (wb.Sheets != null)
                    {
                        copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
                    }
                    else
                    {
                        copiedSheet.SheetId = 1;
                    }

                    sheets.Append(copiedSheet);
                    newWorksheetPart.Worksheet.Save();

                    wb.Append(sheets);
                    //Save Changes
                    newWorkbookPart.Workbook = wb;
                    wb.Save();
                    newXLFile.Close();
                }
            }
        }
Example #41
0
        /// <summary>
        /// Generate an excel file with the information of certificates
        /// </summary>
        /// <param name="dataSource">The list of certificates</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VocStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[4];
                for (int n = 0; n < 4; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 4; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }
                List<CertificateDocument> dataSource = model.Certificates.Collection;
                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the title
                UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);

                //set min date and max date in header
                Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
                string minDate, maxDate;
                //get dates
                if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
                {
                    minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
                    maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
                }
                else
                {
                    minDate = model.IssuanceDateFrom;
                    maxDate = model.IssuanceDateTo;
                }

                //write both dates
                UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
                UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "A2:D4";
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
                columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
                columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
                worksheet.Append(columns);

                int rowIndex = 8;

                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
                AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
                AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
                AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 9;

                //build the data
                foreach (var item in dataSource)
                {

                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };

                    AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
                    AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
                    AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);

                    sheetData1.Append(rowData);
                    rowIndex++;
                }
                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }
            return ms;
        }
        // Generates content of a Workbook Part.
        private void GenerateWorkbookPartContent(WorkbookPart workbookPart, List<string> sheetNames)
        {
            Workbook workbook = new Workbook();
            workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets = new Sheets();
            Sheet sheet;
            for(int ii = 0; ii < sheetNames.Count; ii++)
            {
                sheet = new Sheet() { Name = sheetNames[ii], SheetId = (UInt32Value)Convert.ToUInt32(ii + 1), Id = "rId" + (ii + 1) };
                sheets.Append(sheet);
            }

            //CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)145621U };

            //workbook.Append(fileVersion);
            //workbook.Append(workbookProperties);
            //workbook.Append(bookViews1);
            workbook.Append(sheets);
            //workbook.Append(calculationProperties1);

            workbookPart.Workbook = workbook;
        }
Example #43
0
        public void create(string fileName)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart  workbookPart;
                OpenXmlWriter writer;
                OpenXmlWriter writer2;

                workbookPart = document.AddWorkbookPart();

                WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();

                SharedStringTablePart stringTable = workbookPart.AddNewPart <SharedStringTablePart>();


                var workbook = new Workbook();
                workbookPart.Workbook = workbook;

                var fileVersion = new FileVersion()
                {
                    ApplicationName = "Microsoft Office Excel"
                };
                workbook.Append(fileVersion);

                var worksheetPart  = workbookPart.AddNewPart <WorksheetPart>();
                var relationshipId = workbookPart.GetIdOfPart(worksheetPart);
                var sheetPayment   = new Sheet {
                    Name = "Payment Upload", SheetId = 1, Id = relationshipId
                };

                var sheets = new Sheets();
                sheets.Append(sheetPayment);

                writer = OpenXmlWriter.Create(worksheetPart);

                writer.WriteStartElement(new Worksheet());
                writer.WriteStartElement(new SheetData());
                var worksheetPart2  = workbookPart.AddNewPart <WorksheetPart>();
                var relationshipId2 = workbookPart.GetIdOfPart(worksheetPart2);
                var sheetSuspense   = new Sheet {
                    Name = "Suspense Upload", SheetId = 2, Id = relationshipId2
                };

                sheets.Append(sheetSuspense);

                writer2 = OpenXmlWriter.Create(worksheetPart2);

                writer2.WriteStartElement(new Worksheet());
                writer2.WriteStartElement(new SheetData());

                workbook.Append(sheets);

                writer.WriteEndElement();      // SheetData
                writer2.WriteEndElement();

                writer.WriteEndElement();      // Worksheet
                writer2.WriteEndElement();

                writer.Close();
                writer2.Close();

                document.WorkbookPart.Workbook.Save();
                document.Close();
            }
        }
        /// <summary>
        /// Write excel file of a list of object as T
        /// Assume that maximum of 24 columns
        /// </summary>
        /// <typeparam name="T">Object type to pass in</typeparam>
        /// <param name="fileName">Full path of the file name of excel spreadsheet</param>
        /// <param name="objects">list of the object type</param>
        /// <param name="sheetName">Sheet names of Excel File</param>
        /// <param name="headerNames">Header names of the object</param>
        ///

        public void setColorforHeader <T>(string fileName,    //List<T> objects,
                                          string sheetName, List <string> headerNames)
        {
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart       workbookPart  = myWorkbook.AddWorkbookPart();
                WorksheetPart      worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
                SheetData          sheetData     = new SheetData();
                WorkbookStylesPart stylesPart    = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Workbook           workbook      = new Workbook();
                Stylesheet         styles        = new CustomStylesheet();
                string             relId         = workbookPart.GetIdOfPart(worksheetPart);
                FileVersion        fileVersion   = new FileVersion {
                    ApplicationName = "Microsoft Office Excel"
                };
                styles.Save(stylesPart);
                List <string> fields = GetPropertyInfo <T>();

                var         az      = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
                List <Char> headers = az.GetRange(0, fields.Count);

                //int numRows = objects.Count;
                int numCols = 2;//fields.Count;
                Row header  = new Row();
                int index   = 1;
                header.RowIndex = (uint)index;
                for (int col = 0; col < numCols; col++)
                {
                    //Cell c = CreateHeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet);
                    HeaderCell c = new HeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet, System.Drawing.Color.Aqua, 12, true);
                    header.Append(c);
                }
                sheetData.Append(header);
                Worksheet worksheet  = new Worksheet();
                int       numColumns = headerNames.Count;
                int       width      = 0;

                Columns columns = new Columns();
                for (int col = 0; col < numCols; col++)
                {
                    if (col == 0)
                    {
                        width = 50;
                    }
                    else if (col == 1)
                    {
                        width = 30;
                    }
                    else if (col == 2)
                    {
                        width = 40;
                    }
                    //else if (col == 3)
                    //{
                    //    width = 72;
                    //}
                    //else if (col == 4)
                    //{
                    //    width = 16;
                    //}
                    //else if (col == 5)
                    //{
                    //    width = 12;
                    //}
                    //else if (col == 6)
                    //{
                    //    width = 15;
                    //}

                    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    columns.Append(c);
                }


                Sheets sheets = new Sheets();
                Sheet  sheet  = new Sheet {
                    Name = sheetName, SheetId = 1, Id = relId
                };
                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);

                worksheet.Append(columns);
                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();



                myWorkbook.WorkbookPart.Workbook = workbook;
                myWorkbook.WorkbookPart.Workbook.Save();
                myWorkbook.Close();
            }
        }
        public void Save()
        {
            SpreadsheetDocument xl = SpreadsheetDocument.Create(FileOutputPath, SpreadsheetDocumentType.Workbook);

            WorkbookPart  wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
            Workbook      wb  = new Workbook();
            FileVersion   fv  = new FileVersion {
                ApplicationName = "Microsoft Office Excel"
            };
            Worksheet ws = new Worksheet();

            SheetData sd         = new SheetData();
            uint      rowIndex   = 1;
            Row       rowHeaders = new Row {
                RowIndex = rowIndex++
            };

            string[] headers = { "Codice Ordine", "Totale (€)", "Data & Ora" };

            foreach (string header in headers)
            {
                Cell cell = new Cell
                {
                    DataType  = CellValues.String,
                    CellValue = new CellValue(header)
                };
                rowHeaders.AppendChild(cell);
            }
            sd.AppendChild(rowHeaders);

            foreach (Order order in SavingOrders)
            {
                Row row = new Row {
                    RowIndex = rowIndex
                };

                Cell cell = new Cell
                {
                    DataType  = CellValues.Number,
                    CellValue = new CellValue(order.Id.ToString())
                };

                Cell cell2 = new Cell
                {
                    DataType   = CellValues.Number,
                    CellValue  = new CellValue(order.TotalPrice.ToString("##.##")),
                    StyleIndex = 4
                };
                Cell cell3 = new Cell
                {
                    DataType  = CellValues.Date,
                    CellValue = new CellValue(order.DateTime.ToString("dd-MM-yyyy HH:mm:ss"))
                };
                row.Append(cell, cell2, cell3);

                sd.AppendChild(row);
                rowIndex++;
            }

            Row rowFine = new Row {
                RowIndex = rowIndex
            };
            //Cell cellTextSum = new Cell
            //{
            //    DataType = CellValues.String,
            //    CellValue = new CellValue("Totale (€):"),

            //};
            Cell cellSum = new Cell
            {
                DataType      = CellValues.Number,
                CellValue     = new CellValue(SavingOrders.Sum(x => x.TotalPrice).ToString("##.##")),
                CellReference = "B" + rowIndex
            };

            rowFine.AppendChild(cellSum);
            sd.AppendChild(rowFine);

            ws.AppendChild(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();

            Sheets sheets = new Sheets();
            Sheet  sheet  = new Sheet
            {
                Name    = Path.GetFileNameWithoutExtension(FileOutputPath),
                SheetId = 1,
                Id      = wbp.GetIdOfPart(wsp)
            };

            sheets.AppendChild(sheet);
            wb.Append(fv, sheets);

            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }
Example #46
0
        /// <summary>
        /// Write excel file of a list of object as T
        /// Assume that maximum of 24 columns
        /// </summary>
        /// <typeparam name="T">Object type to pass in</typeparam>
        /// <param name="fileName">Full path of the file name of excel spreadsheet</param>
        /// <param name="objects">list of the object type</param>
        /// <param name="sheetName">Sheet names of Excel File</param>
        /// <param name="headerNames">Header names of the object</param>
        public void Create <T>(
            string fileName,
            List <T> objects,
            string sheetName,
            List <string> headerNames)
        {
            //Open the copied template workbook.
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart  workbookPart  = myWorkbook.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();

                // Create Styles and Insert into Workbook
                WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Stylesheet         styles     = new CustomStylesheet();
                styles.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                Workbook    workbook    = new Workbook();
                FileVersion fileVersion = new FileVersion {
                    ApplicationName = "Microsoft Office Excel"
                };

                SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
                Worksheet worksheet = new Worksheet();

                //PageMargins pageM = worksheet.GetFirstChild<PageMargins>();

                //SheetProtection sheetProtection = new SheetProtection();
                //sheetProtection.Sheet = true;
                //sheetProtection.Objects = true;
                //sheetProtection.Scenarios = true;

                ////add column C:Z to allow edit range, which means column A,B and after Z are locked
                //ProtectedRanges pRanges = new ProtectedRanges();
                //ProtectedRange pRange = new ProtectedRange();
                //ListValue<StringValue> lValue = new ListValue<StringValue>();
                //lValue.InnerText = "D1:Z1048576";
                //pRange.SequenceOfReferences = lValue;
                //pRange.Name = "AllowEditRange1";
                //pRanges.Append(pRange);

                //worksheet.InsertBefore(sheetProtection, pageM);
                //worksheet.InsertBefore(pRanges, pageM);


                int numCols = headerNames.Count;
                int width   = headerNames.Max(h => h.Length) + 5;

                Columns columns = new Columns();
                for (int col = 0; col < numCols; col++)
                {
                    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    if (col == 0)
                    {
                        c.Hidden = BooleanValue.FromBoolean(true);
                    }

                    columns.Append(c);
                }
                worksheet.Append(columns);

                Sheets sheets = new Sheets();
                Sheet  sheet  = new Sheet {
                    Name = sheetName, SheetId = 1, Id = relId
                };
                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();


                myWorkbook.WorkbookPart.Workbook = workbook;
                myWorkbook.WorkbookPart.Workbook.Save();
                myWorkbook.Close();
            }
        }
Example #47
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="fileStorageKeyName"></param>
        /// <param name="fileName"></param>
        /// <param name="ds"></param>
        /// <param name="hiddenColumnNames"></param>
        /// <param name="secretValues"></param>
        /// <param name="lockedColumns"></param>
        public void DataSetToExcel(string fileStorageKeyName, string fileName, DataSet ds, string[] hiddenColumnNames,
                                   string[] secretValues, bool isSheetProtect, string[] editableColumns)
        {
            IFileProvider fileProvider = new FileSystemProvider();
            DataTable     dt           = ds.Tables[0];

            // Generate filepath with file name
            // TODO: Change the blog path.
            string outputFile = fileProvider.GetFilePathToSave(fileStorageKeyName, fileName);
            // Create SpreadSheet document.
            SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(outputFile, SpreadsheetDocumentType.Workbook);

            try
            {
                // Add workbook part.
                WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();

                // Adding sheet part...
                WorksheetPart sheetPart = workbookPart.AddNewPart <WorksheetPart>();
                string        relId     = workbookPart.GetIdOfPart(sheetPart);

                Workbook  workbook  = new Workbook();
                SheetData sheetData = CreateSheetData(dt, secretValues);
                Worksheet Worksheet = new Worksheet();

                // Creates Columns object.
                Columns columns = CreateColumns(dt, hiddenColumnNames);
                Worksheet.Append(columns);

                // New Sheet create.
                Sheets sheets = new Sheets();
                Sheet  sheet  = new Sheet {
                    Name = dt.TableName, SheetId = 1, Id = relId
                };
                sheets.Append(sheet);

                workbook.Append(sheets);
                Worksheet.Append(sheetData);
                sheetPart.Worksheet = Worksheet;

                // Sheet Protect with password.
                if (isSheetProtect)
                {
                    PageMargins     pageM;
                    SheetProtection sheetProtection;
                    ProtectedRanges pRanges;

                    ProtectSheet(Worksheet, out pageM, out sheetProtection, out pRanges, editableColumns, dt);
                    Worksheet.InsertBefore(sheetProtection, pageM);
                    Worksheet.InsertBefore(pRanges, pageM);
                }

                // Save the worksheet.
                sheetPart.Worksheet.Save();

                // Assign the workbook and save then close.
                spreadSheetDocument.WorkbookPart.Workbook = workbook;
                spreadSheetDocument.WorkbookPart.Workbook.Save();
            }
            finally
            {
                spreadSheetDocument.Close();
                spreadSheetDocument.Dispose();
                GC.Collect();

                GC.Collect();
            }
        }
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties();

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 600, YWindow = 105, WindowWidth = (UInt32Value)13995U, WindowHeight = (UInt32Value)8190U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Hoja1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
            definedName1.Text = "Hoja1!$A$1:$AK$31";

            definedNames1.Append(definedName1);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)124519U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(definedNames1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }
Example #49
0
        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 360, YWindow = 150, WindowWidth = (UInt32Value)14355U, WindowHeight = (UInt32Value)4680U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }
        //OverRide
        public void saveXLS(String testName, DataSet ds)
        {
            SpreadsheetDocument xl = null;
            string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
            fullPath += ".xlsx";
            try
            {
                xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
            }
            catch (IOException)
            {
                MessageBox.Show("Another Copy of the Document is open, Please close the document and retry export");
                throw;
            }
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = GenerateStyleSheet();
            wbsp.Stylesheet.Save();

            //save the longest width for each column.
            string[] longestWordPerColumn = new string[35];

            int k = 0;

            //create and add header row.
            Row headerRow = new Row();
            //add headers for germline mutation export
            string[] germlineHeaders =  { "Chromosome", "Position", "Gene Name", "Ref", "Var", "Strand", "Ref Codon", "Var Codon", "Ref AA", "Var AA", "AA Name", "CDS Name", "Cosmic Details", "Shows", "History", "RefSNP", "Clinical Significance", "MAF", "Chromosome Sample Count",  "Alleles", "Allepe pop %" };
            foreach (string s in germlineHeaders)
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(s);
                cell.StyleIndex = 1;
                headerRow.AppendChild(cell);
                longestWordPerColumn[k] = s;
                k++;
            }
            sd.AppendChild(headerRow);
            List<Mutation> mutationList = new List<Mutation>();//remove this
            //create and add rows for each mutation.


            //Here we go through ds
            int tbllength = ds.Tables[0].Rows.Count;
            String[] infoString = new String[21];


            for (int rowNum = 0; rowNum < tbllength; rowNum++)//for each mutation m
            {
                Row newRow = new Row();
                for (int j = 0; j < 21; j++)
                {
                    infoString[j] = (string)ds.Tables[0].Rows[rowNum].ItemArray[j];
                }
                //string[] infoString = m.getInfoForExport();
                for (int i = 0; i < infoString.Length; i++)
                {
                    Cell cell1 = new Cell();
                    if (i == 1)
                        cell1.DataType = CellValues.Number;
                    else
                        cell1.DataType = CellValues.String;
                    cell1.CellValue = new CellValue(infoString[i]);
                    if (!infoString[12].Equals("-----"))//index 12 is the Cosmic Name postion so
                        cell1.StyleIndex = 2;
                    else
                        cell1.StyleIndex = 3;
                    newRow.AppendChild(cell1);
                    //if (longestWordPerColumn[i].Length < infoString[i].Length)
                    //    longestWordPerColumn[i] = infoString[i];
                }
                sd.AppendChild(newRow);
            }

            //Sets the column width to longest width for each column.
            Columns columns = new Columns();
            for (int i = 0; i < 21; i++)
            {
                columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
            }
            ws.Append(columns);


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "Sheet1";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);
            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }
Example #51
0
        //Main class function, export the mutationList to XLSX file, sets file name to testName.
        public static void saveXLS(String testName, List <Mutation> mutationList)
        {
            SpreadsheetDocument xl = null;
            string fullPath        = Properties.Settings.Default.ExportSavePath + @"\" + testName;

            fullPath += ".xlsx";
            try
            {
                xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
            }
            catch (IOException)
            {
                throw;
            }
            WorkbookPart  wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
            Workbook      wb  = new Workbook();
            FileVersion   fv  = new FileVersion();

            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet          ws   = new Worksheet();
            SheetData          sd   = new SheetData();
            WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();

            wbsp.Stylesheet = GenerateStyleSheet();
            wbsp.Stylesheet.Save();

            //save the longest width for each column.
            string[] longestWordPerColumn = new string[12];

            int k = 0;

            //create and add header row.
            Row headerRow = new Row();

            foreach (string s in Mutation.getHeaderForExport())
            {
                Cell cell = new Cell();
                cell.DataType   = CellValues.String;
                cell.CellValue  = new CellValue(s);
                cell.StyleIndex = 1;
                headerRow.AppendChild(cell);
                longestWordPerColumn[k] = s;
                k++;
            }
            sd.AppendChild(headerRow);

            //create and add rows for each mutation.
            foreach (Mutation m in mutationList)
            {
                Row      newRow     = new Row();
                string[] infoString = m.getInfoForExport();
                for (int i = 0; i < infoString.Length; i++)
                {
                    Cell cell1 = new Cell();
                    if (i == 1)
                    {
                        cell1.DataType = CellValues.Number;
                    }
                    else
                    {
                        cell1.DataType = CellValues.String;
                    }
                    cell1.CellValue = new CellValue(infoString[i]);
                    if (!m.CosmicName.Equals("-----"))
                    {
                        cell1.StyleIndex = 2;
                    }
                    else
                    {
                        cell1.StyleIndex = 3;
                    }
                    newRow.AppendChild(cell1);
                    if (longestWordPerColumn[i].Length < infoString[i].Length)
                    {
                        longestWordPerColumn[i] = infoString[i];
                    }
                }
                sd.AppendChild(newRow);
            }

            //Sets the column width to longest width for each column.
            Columns columns = new Columns();

            for (int i = 0; i < 12; i++)
            {
                columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
            }
            ws.Append(columns);


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet  sheet  = new Sheet();

            sheet.Name    = "Sheet1";
            sheet.SheetId = 1;
            sheet.Id      = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);
            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }
        //Main class function, export the mutationList to XLSX file, sets file name to testName.
        public static void saveXLS(String testName, List<Mutation> mutationList)
        {
            SpreadsheetDocument xl = null;
            string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
            fullPath += ".xlsx";
            try
            {
                xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
            }
            catch (IOException )
            {
                throw ;
            }
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = GenerateStyleSheet();
            wbsp.Stylesheet.Save();

            //save the longest width for each column.
            string[] longestWordPerColumn = new string[12];

            int k = 0;

            //create and add header row.
            Row headerRow = new Row();
            foreach (string s in Mutation.getHeaderForExport())
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(s);
                cell.StyleIndex = 1;
                headerRow.AppendChild(cell);
                longestWordPerColumn[k] = s;
                k++;
            }
            sd.AppendChild(headerRow);

            //create and add rows for each mutation.
            foreach (Mutation m in mutationList)
            {
                Row newRow = new Row();
                string[] infoString = m.getInfoForExport();
                for (int i = 0; i < infoString.Length; i++)
                {
                    Cell cell1 = new Cell();
                    if (i == 1)
                        cell1.DataType = CellValues.Number;
                    else
                        cell1.DataType = CellValues.String;
                    cell1.CellValue = new CellValue(infoString[i]);
                    if (!m.CosmicName.Equals("-----"))
                        cell1.StyleIndex = 2;
                    else
                        cell1.StyleIndex = 3;
                    newRow.AppendChild(cell1);
                    if (longestWordPerColumn[i].Length < infoString[i].Length)
                        longestWordPerColumn[i] = infoString[i];
                }
                sd.AppendChild(newRow);
            }

            //Sets the column width to longest width for each column.
            Columns columns = new Columns();
            for (int i = 0; i < 12; i++)
            {
                columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
            }
            ws.Append(columns);


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "Sheet1";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);
            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }