示例#1
0
        public void SW002_AllDataTypes()
        {
            Sw.WorkbookDfn wb = new Sw.WorkbookDfn
            {
                Worksheets = new Sw.WorksheetDfn[]
                {
                    new Sw.WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        ColumnHeadings = new Sw.CellDfn[]
                        {
                            new Sw.CellDfn
                            {
                                Value = "DataType",
                                Bold  = true,
                            },
                            new Sw.CellDfn
                            {
                                Value = "Value",
                                Bold  = true,
                                HorizontalCellAlignment = Sw.HorizontalCellAlignment.Right,
                            },
                        },
                        Rows = new Sw.RowDfn[]
                        {
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Boolean,
                                        Value        = true,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Boolean,
                                        Value        = false,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "String",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType            = Sw.CellDataType.String,
                                        Value                   = "A String",
                                        HorizontalCellAlignment = Sw.HorizontalCellAlignment.Right,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "int",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (int)100,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "int?",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (int?)100,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "int? (is null)",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = null,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "uint",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (uint)101,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "long",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = Int64.MaxValue,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "float",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (float)123.45,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "double",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (double)123.45,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "decimal",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (decimal)123.45,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Date,
                                        Value        = new DateTime(2012, 1, 8),
                                        FormatCode   = "mm-dd-yy",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType            = Sw.CellDataType.Date,
                                        Value                   = new DateTime(2012, 1, 9),
                                        FormatCode              = "mm-dd-yy",
                                        Bold                    = true,
                                        HorizontalCellAlignment = Sw.HorizontalCellAlignment.Center,
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Date,
                                        Value        = new DateTimeOffset(new DateTime(2012, 1, 8), TimeSpan.Zero),
                                        FormatCode   = "mm-dd-yy",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType            = Sw.CellDataType.Date,
                                        Value                   = new DateTimeOffset(new DateTime(2012, 1, 9), TimeSpan.Zero),
                                        FormatCode              = "mm-dd-yy",
                                        Bold                    = true,
                                        HorizontalCellAlignment = Sw.HorizontalCellAlignment.Center,
                                    },
                                }
                            },
                        }
                    }
                }
            };
            var outXlsx = new FileInfo(Path.Combine(Sw.TestUtil.TempDir.FullName, "SW002-DataTypes.xlsx"));

            Sw.SpreadsheetWriter.Write(outXlsx.FullName, wb);
            Validate(outXlsx);
        }
示例#2
0
        public void SW001_Simple()
        {
            Sw.WorkbookDfn wb = new Sw.WorkbookDfn
            {
                Worksheets = new Sw.WorksheetDfn[]
                {
                    new Sw.WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        TableName      = "NamesAndRates",
                        ColumnHeadings = new Sw.CellDfn[]
                        {
                            new Sw.CellDfn
                            {
                                Value = "Name",
                                Bold  = true,
                            },
                            new Sw.CellDfn
                            {
                                Value = "Age",
                                Bold  = true,
                                HorizontalCellAlignment = Sw.HorizontalCellAlignment.Left,
                            },
                            new Sw.CellDfn
                            {
                                Value = "Rate",
                                Bold  = true,
                                HorizontalCellAlignment = Sw.HorizontalCellAlignment.Left,
                            }
                        },
                        Rows = new Sw.RowDfn[]
                        {
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "Eric",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = 50,
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (decimal)45.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                            new Sw.RowDfn
                            {
                                Cells = new Sw.CellDfn[]
                                {
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.String,
                                        Value        = "Bob",
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = 42,
                                    },
                                    new Sw.CellDfn {
                                        CellDataType = Sw.CellDataType.Number,
                                        Value        = (decimal)78.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                        }
                    }
                }
            };
            var outXlsx = new FileInfo(Path.Combine(Sw.TestUtil.TempDir.FullName, "SW001-Simple.xlsx"));

            Sw.SpreadsheetWriter.Write(outXlsx.FullName, wb);
            Validate(outXlsx);
        }
示例#3
0
        public static void Write(string fileName, WorkbookDfn workbook)
        {
            try
            {
                if (fileName == null)
                {
                    throw new ArgumentNullException("fileName");
                }
                if (workbook == null)
                {
                    throw new ArgumentNullException("workbook");
                }

                FileInfo fi = new FileInfo(fileName);
                if (fi.Exists)
                {
                    fi.Delete();
                }

                // create the blank workbook
                char[] base64CharArray = _EmptyXlsx
                                         .Where(c => c != '\r' && c != '\n').ToArray();
                byte[] byteArray =
                    System.Convert.FromBase64CharArray(base64CharArray,
                                                       0, base64CharArray.Length);
                File.WriteAllBytes(fi.FullName, byteArray);

                // open the workbook, and create the TableProperties sheet, populate it
                using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true))
                {
                    WorkbookPart workbookPart = sDoc.WorkbookPart;
                    XDocument    wXDoc        = workbookPart.GetXDocument();
                    XElement     sheetElement = wXDoc
                                                .Root
                                                .Elements(S.sheets)
                                                .Elements(S.sheet)
                                                .Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")
                                                .FirstOrDefault();
                    if (sheetElement == null)
                    {
                        throw new SpreadsheetWriterInternalException();
                    }
                    string id = (string)sheetElement.Attribute(R.id);
                    sheetElement.Remove();
                    workbookPart.PutXDocument();

                    WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);
                    workbookPart.DeletePart(sPart);

                    XDocument appXDoc = sDoc
                                        .ExtendedFilePropertiesPart
                                        .GetXDocument();
                    XElement vector = appXDoc
                                      .Root
                                      .Elements(EP.TitlesOfParts)
                                      .Elements(VT.vector)
                                      .FirstOrDefault();
                    if (vector != null)
                    {
                        vector.SetAttributeValue(SSNoNamespace.size, 0);
                        XElement lpstr = vector.Element(VT.lpstr);
                        lpstr.Remove();
                    }
                    XElement vector2 = appXDoc
                                       .Root
                                       .Elements(EP.HeadingPairs)
                                       .Elements(VT.vector)
                                       .FirstOrDefault();
                    XElement variant = vector2
                                       .Descendants(VT.i4)
                                       .FirstOrDefault();
                    if (variant != null)
                    {
                        variant.Value = "1";
                    }
                    sDoc.ExtendedFilePropertiesPart.PutXDocument();

                    if (workbook.Worksheets != null)
                    {
                        foreach (var worksheet in workbook.Worksheets)
                        {
                            AddWorksheet(sDoc, worksheet);
                        }
                    }

                    workbookPart.WorkbookStylesPart.PutXDocument();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Unhandled exception: {0} in {1}",
                                  e.ToString(), e.Source);
                throw e;
            }
        }