示例#1
0
        public void WorksheetMultipleStyleTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);

            style.IsItalic = true;
            style.SetBackgroundColor("FF0000");//(red)

            WorksheetWriter.PasteText(doc, worksheetPart, "B", 2, "Hello world");
            WorksheetWriter.SetStyle(style, doc, worksheetPart, "B", 2);

            style        = SpreadsheetReader.GetDefaultStyle(doc);
            style.IsBold = true;
            style.SetBackgroundColor("0000FF");//(blue)

            WorksheetWriter.SetStyle(style, doc, worksheetPart, "C", 3);
            WorksheetWriter.PasteText(doc, worksheetPart, "C", 3, "Hello world2");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled2.xlsx", GetOutputFolder()), stream);
        }
        /// <summary>
        /// Excutes the Command
        /// </summary>
        public override void Execute()
        {
            WorksheetWriter.MoveTo("A1");

            WorksheetWriter.WriteRow(header);
            WriteItems(hierarchy.Enterprise, 1);
        }
示例#3
0
        public void WorksheetSetStylesTest()
        {
            List <string> values  = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });
            List <string> values2 = new List <string>(new string[] { "echo", "foxtrot", "golf", "hotel" });

            //Write to the spreadsheet
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            writer.PasteValues("A2", values, CellValues.String);
            writer.PasteValues("A3", values2, CellValues.String);

            //The centre four styles should be aligned to center
            SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);

            style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
            writer.SetStyle(style, "B2", "C3");

            //Set style in non existing cells
            writer.SetStyle(style, "B5", "C6");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\stylerange.xlsx", GetOutputFolder()), stream);
        }
示例#4
0
        public void WorksheetAddStyleTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            SpreadsheetStyle defaultStyle = SpreadsheetReader.GetDefaultStyle(doc);

            defaultStyle.IsItalic    = true;
            defaultStyle.IsBold      = true;
            defaultStyle.IsUnderline = true;
            defaultStyle.SetColor("FF0000");
            //r = 255, g = 0, b = 0 (Red)
            defaultStyle.SetBackgroundColor("DDDDDD");
            //(light grey)
            defaultStyle.SetBorder("00FF00", BorderStyleValues.Medium);
            //(green medium border)

            WorksheetWriter.PasteText(doc, worksheetPart, "E", 5, "Hello world");
            WorksheetWriter.SetStyle(defaultStyle, doc, worksheetPart, "E", 5);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled.xlsx", GetOutputFolder()), stream);
        }
示例#5
0
        public void WorksheetDataTablePasteColumnsTemplate()
        {
            DataTable dt = new DataTable();

            //Manually create table for mockup purposes
            dt.TableName = "tblParameters";
            dt.Columns.Add("intID", typeof(int));
            dt.Columns.Add("vchrDescription", typeof(string));
            dt.Columns.Add("decPrice", typeof(float));
            dt.AcceptChanges();

            dt.Rows.Add(new object[] { 1, "Parts", 1.00F });
            dt.Rows.Add(new object[] { 2, "Cheque", 1.50F });
            dt.Rows.Add(new object[] { 3, "Products", 1.45F });
            dt.Rows.Add(new object[] { 4, "Gifts", 0.00F });
            dt.Rows.Add(new object[] { 5, "DealerRepair", 2.50F });

            //Write to the spreadsheet
            MemoryStream        stream        = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            writer.PasteDataTable(dt, "B3", new List <string>(new string[] { "vchrDescription" }));

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatabletemplate.xlsx", GetOutputFolder()), stream);
        }
示例#6
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            int      intValue  = 0;
            DateTime dateValue = new DateTime();

            if (strValue.Contains("$"))
            {
                strValue = strValue.Replace("$", "");
                strValue = strValue.Replace(",", "");

                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (int.TryParse(strValue, out intValue))
            {
                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (string.IsNullOrEmpty(strValue))
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
            //else if (DateTime.TryParse(strValue,out dateValue))
            //{
            //    workSheetWriter.PasteDate(cellLocation, dateValue);
            //}
            else
            {
                workSheetWriter.PasteText(cellLocation, strValue, SpreadsheetStyle.GetDefault(spreadSheet));
            }
        }
示例#7
0
        /// <summary>
        /// Write the values for the rows from headers.
        /// </summary>
        /// <param name="rowData">Excel row values.</param>
        /// <param name="headerData">Excel header values.</param>
        /// <param name="rowNum">Row number.</param>
        /// <param name="maxWidth">Max width.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteRowsFromHeaders(IEnumerable <T> rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            maxWidth = 0;

            foreach (object row in rowData)
            {
                int colNum = 0;
                foreach (string header in headerData)
                {
                    string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
                    strValue = ReplaceSpecialCharacters(strValue);
                    maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

                    string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

                    ExcelDocument <T> .WriteValues(cellLocation, strValue, spreadSheet, workSheet);

                    colNum++;
                }

                rowNum++;
            }
        }
示例#8
0
        public void WorksheetDataTablePasteTest()
        {
            DataTable dt = new DataTable();

            //Manually create table for mockup purposes
            dt.TableName = "tblParameters";
            dt.Columns.Add("intID", typeof(int));
            dt.Columns.Add("vchrDescription", typeof(string));
            dt.Columns.Add("dteValidFrom", typeof(DateTime));
            dt.Columns.Add("decPrice", typeof(float));
            dt.Columns.Add("bitFlag", typeof(Boolean));
            dt.AcceptChanges();

            dt.Rows.Add(new object[] { 1, "Parts", new DateTime(1974, 1, 2), 1.00F, true });
            dt.Rows.Add(new object[] { 2, "Cheque", new DateTime(1974, 2, 2), 1.50F, false });
            dt.Rows.Add(new object[] { 3, "Products", new DateTime(1974, 3, 2), 1.45F, true });
            dt.Rows.Add(new object[] { 4, "Gifts", new DateTime(1974, 4, 2), 0.00F, false });
            dt.Rows.Add(new object[] { 5, "DealerRepair", new DateTime(1974, 5, 2), 2.50F, true });

            //Write to the spreadsheet
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);

            style.IsBold = true;
            writer.PasteDataTable(dt, "B3", style);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatable.xlsx", GetOutputFolder()), stream);
        }
示例#9
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            int intValue = 0;

            if (strValue.Contains("$"))
            {
                strValue = strValue.Replace("$", "");
                strValue = strValue.Replace(",", "");

                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (int.TryParse(strValue, out intValue))
            {
                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (string.IsNullOrEmpty(strValue))
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
            else
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
        }
示例#10
0
        public ExcelWriter()
        {
            _stream = SpreadsheetReader.Create();
            _doc    = SpreadsheetDocument.Open(_stream, true);
            var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");

            _writer = new WorksheetWriter(_doc, worksheetPart);
        }
        /// <summary>
        /// 将查询出的DataSet导出为Excel。
        /// </summary>
        /// <param name="dataSet">要导出的DataSet数据集,可以包含多个DataTable数据,使用DataSetName作为导出的文件名</param>
        public static void ExportDataSet(DataSet dataSet)
        {
            if (dataSet == null)
            {
                throw new ArgumentException("dataSet");
            }
            if (dataSet.Tables.Count == 0)
            {
                return;
            }

            MemoryStream        stream = SpreadsheetReader.Create();
            SpreadsheetDocument doc    = SpreadsheetDocument.Open(stream, true);
            //首先清空原有的Sheet
            var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>();

            if (allSheet.Count() > 0)
            {
                doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
            }
            //构造Excel
            foreach (DataTable dt in dataSet.Tables)
            {
                WorksheetPart   sheetPart   = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName);
                WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart);
                //打印列名
                int colIndex = 0;//当前第几列,从0开始
                foreach (DataColumn col in dt.Columns)
                {
                    string reference = GetExcelColumnHeader(colIndex) + "1";
                    sheetWriter.PasteText(reference, col.ColumnName);
                    colIndex++;
                }
                sheetWriter.PasteDataTable(dt, "A2");
                SpreadsheetWriter.Save(doc);
            }
            //Write to response stream
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            context.Response.Clear();
            string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower();
            string FileName  = dataSet.DataSetName;

            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = context.Server.UrlEncode(FileName);
            }
            context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx"));
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            stream.WriteTo(context.Response.OutputStream);
            context.Response.End();
        }
示例#12
0
        public void WorksheetPasteDate()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            Cell cell = writer.PasteDate("D3", new DateTime(2009, 12, 20, 15, 23, 05));

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\date.xlsx", GetOutputFolder()), stream);
        }
示例#13
0
        public void WorksheetColumnSplitTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\columnstemplate.xlsx", Directory.GetCurrentDirectory()));
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.FindColumn("C").Width = 20;

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\columnsplit.xlsx", GetOutputFolder()), stream);
        }
示例#14
0
        public void WorksheetNumberFormatTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            SpreadsheetStyle    style         = SpreadsheetReader.GetDefaultStyle(doc);
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            style.FormatCode = "0.00";
            writer.PasteNumber("B3", "123", style);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\numberformat.xlsx", GetOutputFolder()), stream);
        }
示例#15
0
        public void WorksheetSharedTextTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteSharedText("B2", "Shared Text");
            writer.PasteSharedText("B3", "Shared Text");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\sharedtext.xlsx", GetOutputFolder()), stream);
        }
示例#16
0
        public void WorksheetPasteValuesTest()
        {
            List <string> values = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });

            //Write to the spreadsheet
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter.PasteValues(doc, worksheetPart, "A", 2, values, CellValues.String, null);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\pastevalues.xlsx", GetOutputFolder()), stream);
        }
示例#17
0
        public void WorksheetOverlappingBorderTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            //Also use coordinates that are reversed
            writer.DrawBorder("B4", "D2", "FF0000", BorderStyleValues.Medium);
            writer.DrawBorder("E5", "C3", "0000FF", BorderStyleValues.Medium);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\borderoverlap.xlsx", GetOutputFolder()), stream);
        }
示例#18
0
        public void WorksheetColumnWidthTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.FindColumn("A").Width = 20;
            writer.FindColumn(2).Width   = 20;
            writer.SetColumnWidth("C", 20);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\columnwidths.xlsx", GetOutputFolder()), stream);
        }
示例#19
0
        public void InsertSheetTest()
        {
            MemoryStream        stream = SpreadsheetReader.Create();
            SpreadsheetDocument doc    = SpreadsheetDocument.Open(stream, true);

            var worksheetPart = SpreadsheetWriter.InsertWorksheet(doc);

            Assert.IsNotNull(worksheetPart, "A worksheet part was not returned.");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("B2", "Hello World");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\createsheet.xlsx", GetOutputFolder()), stream);
        }
        /// <summary>
        /// Writes the items.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="depth">The depth.</param>
        private void WriteItems(IItem item, int depth)
        {
            List <string> row = new List <string> {
                Convert.ToString(depth), item.GetType().Name, string.Empty
            };

            for (int i = 1; i < depth; i++)
            {
                row.Add(string.Empty);
            }
            row.Add(item.Name);
            WorksheetWriter.WriteRow(row);
            foreach (IItem child in item.GetItems())
            {
                WriteItems(child, depth + 1);
            }
        }
        public bool SaveToExcel(string FileName)
        {
            try {
                using (ExcelFileWriter file = ExcelFileWriter.OpenExcelApplication()) {
                    if (file == null)
                    {
                        throw new NullReferenceException("Failed to open the Excel application.");
                    }
                    using (WorkbookWriter book = file.CreateNewWorkbook()) {
                        if (book == null)
                        {
                            throw new NullReferenceException("Failed to create a new Excel workbook.");
                        }
                        WorksheetWriter sheet = book.GetActiveWorksheet();
                        if (sheet == null)
                        {
                            throw new NullReferenceException("Failed to find the active worksheet.");
                        }
                        sheet.Name = "Data Acquisition";

                        int col = WorksheetWriter.MinColumn;
                        int row;
                        foreach (string cat in orderedCategories)
                        {
                            row             = WorksheetWriter.MinRow;
                            sheet[row, col] = cat;
                            sheet.Bold(row, col);

                            foreach (object data in allData[cat])
                            {
                                sheet[++row, col] = data.ToString();
                            }

                            col++;
                        }

                        sheet.AutoFitAllColumns();
                        return(book.Save(FileName + WorkbookWriter.DEFAULT_FILE_EXTENSION));
                    }
                }
            } catch (Exception) {
                Console.WriteLine("Unable to save data.");
                return(false);
            }
        }
示例#22
0
        public void WorksheetPrintAreaTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("A1", "Set print area to A1:B9");

            //Test setting the print area
            DefinedName area = writer.SetPrintArea("Sheet1", "A1", "B9");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\printarea.xlsx", GetOutputFolder()), stream);

            //Assert.IsTrue(area != null, "Print area reference not returned.");
        }
示例#23
0
        public void WorksheetAddAlignmentTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            SpreadsheetStyle    style         = SpreadsheetReader.GetDefaultStyle(doc);
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            style.IsWrapped = true;
            writer.PasteText("E5", "Wrapped text", style);

            style.IsWrapped = false;
            style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
            writer.PasteText("E7", "Aligned Test", style);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\wrapped.xlsx", GetOutputFolder()), stream);
        }
示例#24
0
        public void WorksheetPasteTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteNumber("D3", "2");
            writer.PasteNumber("D4", "3");
            writer.PasteNumber("D5", "4");

            //Add total without a calc chain
            writer.FindCell("D6").CellFormula = new CellFormula("SUM(D3:D5)");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\output.xlsx", GetOutputFolder()), stream);
        }
示例#25
0
        public void WorksheetDeleteRowsTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("D3", "Row 3");
            writer.PasteText("D4", "Row 4");
            writer.PasteText("D5", "Row 5");

            //Delete row 3
            writer.DeleteRows(3, 2);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\deleterows.xlsx", GetOutputFolder()), stream);
        }
示例#26
0
        private static void WriteRowsFromHeaders(System.Data.DataTable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            maxWidth = 0;

            for (int i = 0; i < rowData.Rows.Count; i++)
            {
                for (int j = 0; j < headerData.Count(); j++)
                {
                    int    colNum   = 0;
                    string getValue = rowData.Rows[i][headerData[j]] != null ? rowData.Rows[i][headerData[j]].ToString() : "";
                    string strValue = getValue;
                    strValue = ReplaceSpecialCharacters(strValue);
                    maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

                    string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

                    ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
                    colNum++;
                }
                rowNum++;
            }

            //foreach (object row in rowData)
            //{
            //    int colNum = 0;
            //    foreach (string header in headerData)
            //    {
            //        string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
            //        strValue = ReplaceSpecialCharacters(strValue);
            //        maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

            //        string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

            //        ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
            //        colNum++;
            //    }

            //    rowNum++;
            //}
        }
示例#27
0
        public void WorksheetBorderTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            //Fill a background cell to make sure it is not overwritten
            SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);

            style.SetBackgroundColor("C0C0C0");
            //(grey)
            writer.SetStyle(style, "B2");

            writer.DrawBorder("B2", "D4", "FF0000", BorderStyleValues.Medium);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\border.xlsx", GetOutputFolder()), stream);
        }
示例#28
0
        public void WorksheetFindCellTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            //Test that the findcell function can accurately add cells past z
            writer.PasteText("A1", "A1");
            writer.PasteText("Z1", "Z1");
            writer.PasteText("Z2", "Z2");
            writer.PasteText("AA1", "AA1");
            writer.PasteText("AA2", "AA2");
            writer.PasteText("BA1", "BA1");
            writer.PasteText("AA9", "AA9");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\findcell.xlsx", GetOutputFolder()), stream);
        }
示例#29
0
        private void DeleteRows(SpreadsheetDocument RecultDoc, WorksheetWriter RecultWriter, uint rowIndex, uint count)
        {
            RecultWriter.DeleteRows(rowIndex, count);

            if (RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().Count() > 0)
            {
                MergeCells mergeCells = RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().First();

                //Удаление  обединенных ячеек попавших в облать удаления
                for (int i = mergeCells.Elements <MergeCell>().Count() - 1; i >= 0; i--)
                {
                    string Range    = mergeCells.Elements <MergeCell>().ElementAt(i).Reference.Value;
                    UInt32 startRow = SpreadsheetReader.RowFromReference(FirstRefFromRange(Range));
                    UInt32 endRow   = SpreadsheetReader.RowFromReference(LastRefFromRange(Range));

                    if (startRow >= rowIndex && endRow <= (rowIndex + count - 1))
                    {
                        mergeCells.Elements <MergeCell>().ElementAt(i).Remove();
                    }
                }

                //Обновления ссылок всех ниже стоящих обединенных ячеек
                foreach (MergeCell mCell in mergeCells.Elements <MergeCell>())
                {
                    string Range    = mCell.Reference.Value;
                    string StartRef = FirstRefFromRange(Range);
                    string EndRef   = LastRefFromRange(Range);
                    UInt32 startRow = SpreadsheetReader.RowFromReference(StartRef);
                    UInt32 endRow   = SpreadsheetReader.RowFromReference(EndRef);

                    if (startRow > (rowIndex + count - 1))
                    {
                        string newRangeFirst = SpreadsheetReader.ColumnFromReference(StartRef) + (SpreadsheetReader.RowFromReference(StartRef) - count);
                        string newRangeEnd   = SpreadsheetReader.ColumnFromReference(EndRef) + (SpreadsheetReader.RowFromReference(EndRef) - count);
                        mCell.Reference.Value = newRangeFirst + ":" + newRangeEnd;
                    }
                }
            }
        }
示例#30
0
        public void WorksheetCopyTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteNumber("B3", "10");
            writer.PasteNumber("B4", "20");
            writer.PasteNumber("B5", "40");

            //Add total without a calc chain
            writer.FindCell("B6").CellFormula = new CellFormula("SUM(B3:B5)");

            //Change the print area from A1:I30
            writer.SetPrintArea("Sheet1", "A1", "D10");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\templatetest.xlsx", GetOutputFolder()), stream);
        }
示例#31
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

             int intValue = 0;
             DateTime dateValue = new DateTime();
             if (strValue.Contains("$"))
             {
            strValue = strValue.Replace("$", "");
            strValue = strValue.Replace(",", "");

            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (int.TryParse(strValue, out intValue))
             {
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (string.IsNullOrEmpty(strValue))
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
             //else if (DateTime.TryParse(strValue,out dateValue))
             //{
             //    workSheetWriter.PasteDate(cellLocation, dateValue);
             //}
             else
             {
            workSheetWriter.PasteText(cellLocation, strValue,SpreadsheetStyle.GetDefault(spreadSheet));
             }
        }
示例#32
0
        private static void WriteRowsFromHeaders(System.Data.DataTable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
              maxWidth = 0;

              for (int i = 0; i < rowData.Rows.Count; i++)
              {
              for (int j = 0; j < headerData.Count(); j++)
              {
                  int colNum = 0;
                  string getValue = rowData.Rows[i][headerData[j]] != null ? rowData.Rows[i][headerData[j]].ToString() : "";
                  string strValue = getValue;
                  strValue = ReplaceSpecialCharacters(strValue);
                  maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

                  string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

                  ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
                  colNum++;
              }
              rowNum++;
              }

              //foreach (object row in rowData)
              //{
              //    int colNum = 0;
              //    foreach (string header in headerData)
              //    {
              //        string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
              //        strValue = ReplaceSpecialCharacters(strValue);
              //        maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

              //        string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

              //        ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
              //        colNum++;
              //    }

              //    rowNum++;
              //}
        }
示例#33
0
        /// <summary>
        /// Write the values for the rows from headers.
        /// </summary>
        /// <param name="rowData">Excel row values.</param>
        /// <param name="headerData">Excel header values.</param>
        /// <param name="rowNum">Row number.</param>
        /// <param name="maxWidth">Max width.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
             maxWidth = 0;

             foreach (object row in rowData)
             {
            int colNum = 0;
            foreach (string header in headerData)
            {
               string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
               strValue = ReplaceSpecialCharacters(strValue);
               maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;

               string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);

               ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
               colNum++;
            }

            rowNum++;
             }
        }
示例#34
0
 private void Create(string fn)
 {
     _stream = SpreadsheetReader.Create();
     _doc = SpreadsheetDocument.Open(_stream, true);
     wsPart = SpreadsheetReader.GetWorksheetPartByName(_doc, wsNames[0]);
     _writer = new WorksheetWriter(_doc, wsPart);
     SpreadsheetWriter.Save(_doc);
 }
示例#35
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

             int intValue = 0;
             if (strValue.Contains("$"))
             {
            strValue = strValue.Replace("$", "");
            strValue = strValue.Replace(",", "");

            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (int.TryParse(strValue, out intValue))
             {
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (string.IsNullOrEmpty(strValue))
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
             else
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
        }