コード例 #1
0
        public static void CreateXlsx(string path, string name, IDictionary <string, IEnumerable> sheets, Dictionary <string, IEnumerable <string> > propertyNames, Dictionary <string, Func <object, string, object> > getFields, Dictionary <string, Func <string, string> > getLabels, Dictionary <string, Func <string, CellDataType> > getCellTypes = null)
        {
            name = GetXlsxFileName(name);

            var workSheets = new List <WorksheetDfn>();

            foreach (var sheet in sheets)
            {
                var thesePropertyNames = propertyNames != null && propertyNames.ContainsKey(sheet.Key) ? propertyNames[sheet.Key] : throw new NullReferenceException("No Property Names For Sheet " + sheet.Key);
                var getField           = getFields != null && getFields.ContainsKey(sheet.Key) ? getFields[sheet.Key] : throw new NullReferenceException("No Get Field Method For Sheet " + sheet.Key);
                var getLabel           = getLabels != null && getLabels.ContainsKey(sheet.Key) ? getLabels[sheet.Key] : (s) => s;
                var getCellType        = getCellTypes != null && getCellTypes.ContainsKey(sheet.Key) ? getCellTypes[sheet.Key] : (s) => CellDataType.String;

                var workSheet = new WorksheetDfn();
                workSheet.Name           = sheet.Key.Replace(" ", "_");
                workSheet.TableName      = sheet.Key.Replace(" ", "_");
                workSheet.ColumnHeadings = thesePropertyNames.Select(p => new CellDfn()
                {
                    Bold  = true,
                    Value = getLabel(p)
                }).ToArray();

                var rows = new List <RowDfn>();
                foreach (var e in sheet.Value)
                {
                    rows.Add(new RowDfn
                    {
                        Cells = thesePropertyNames.Select(p => new CellDfn
                        {
                            Value        = getField(e, p),
                            CellDataType = getCellType(p)
                        }).ToArray()
                    });
                }
                workSheet.Rows = rows.ToArray();
                workSheets.Add(workSheet);
            }

            var wb = new WorkbookDfn();

            wb.Worksheets = workSheets.ToArray();
            var outXlsx = new FileInfo(Path.Combine(path, name));

            SpreadsheetWriter.Write(outXlsx.FullName, wb);
        }
コード例 #2
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();

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

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