Lucio Flores
2018-08-23T23:07:17Z

SimpleExcel.cs

| //Create excel file with DocumentFormat.OpenXML
|
| static void Main(string[] args)
| {
| //StyleExcel.EditFile();
| WorkbookPart wBookPart = null;
| using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("sample.xlsx", SpreadsheetDocumentType.Workbook))
| {
|
| wBookPart = spreadsheetDoc.AddWorkbookPart();
| wBookPart.Workbook = new Workbook();
| spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
| Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>();
| WorksheetPart wSheetPart = wBookPart.AddNewPart<WorksheetPart>();
|
| // Add a blank WorksheetPart.
| WorksheetPart newWorksheetPart = spreadsheetDoc.WorkbookPart.AddNewPart<WorksheetPart>();
| newWorksheetPart.Worksheet = new Worksheet(new SheetData());
|
| //add Styles
| WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
| stylesPart.Stylesheet = GenerateStyleSheet();
| stylesPart.Stylesheet.Save();
|
| string relationshipId = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart);
|
| // Get a unique ID for the new worksheet.
| uint sheetId = 1;
| if (sheets.Elements<Sheet>().Count() > 0)
| {
| sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
| }
|
| // Give the new worksheet a name.
|
| Sheet sheet = new Sheet() { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = "Hoja_" + sheetId };
| sheets.Append(sheet);
|
|
| SheetData sheetData = new SheetData();
| //sheetData.AppendChild(firstRow);
| newWorksheetPart.Worksheet = new Worksheet(sheetData);
|
| Row headerRow = new Row();
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Filtros usados:" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Hell string" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "cooments" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = null } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Ficha/ID" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Nombre(s)" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Apellido Paterno" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Apellido Materno" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Sexo" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Estado Civil" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Fecha de Registro" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "RFC" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "CURP" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Promotor" } });
| headerRow.Append(new Cell { DataType = CellValues.String, StyleIndex = 1, CellValue = new CellValue { Text = "Sección Sindical" } });
| sheetData.AppendChild(headerRow);
|
| for (int i = 0; i < 100; i++)
| {
|
| var helloString = "hello\v verticaltab";
| Row contentRow = new Row();
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = helloString.Replace("\v", string.Empty) } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = null } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Ficha/ID" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Nombre(s)" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Apellido Paterno" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Apellido Materno" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Sexo" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Estado Civil" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Fecha de Registro" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "RFC" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "CURP" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Promotor" } });
| contentRow.Append(new Cell { DataType = CellValues.String, CellValue = new CellValue { Text = "Sección Sindical" } });
| sheetData.AppendChild(contentRow);
| }
| }
| }
| }
|
|
|
|
|
| public static Stylesheet GenerateStyleSheet()
| {
| return new Stylesheet(
| new Fonts(
| new Font( // Index 0 - The default font.
| new FontSize() { Val = 11 },
| new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
| new FontName() { Val = "Calibri" }),
| new Font( // Index 1 - The bold font.
| new Bold(),
| new FontSize() { Val = 11 },
| new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
| new FontName() { Val = "Calibri" }),
| new Font( // Index 2 - The Italic font.
| new Italic(),
| new FontSize() { Val = 11 },
| new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
| new FontName() { Val = "Calibri" }),
| new Font( // Index 2 - The Times Roman font. with 16 size
| new FontSize() { Val = 16 },
| new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
| new FontName() { Val = "Times New Roman" })
| ),
| new Fills(
| new Fill( // Index 0 - The default fill.
| new PatternFill() { PatternType = PatternValues.None }),
| new Fill( // Index 1 - The default fill of gray 125 (required)
| new PatternFill() { PatternType = PatternValues.Gray125 }),
| new Fill( // Index 2 - The yellow fill.
| new PatternFill(
| new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
| )
| { PatternType = PatternValues.Solid })
| ),
| new Borders(
| new Border( // Index 0 - The default border.
| new LeftBorder(),
| new RightBorder(),
| new TopBorder(),
| new BottomBorder(),
| new DiagonalBorder()),
| new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
| new LeftBorder(
| new Color() { Auto = true }
| )
| { Style = BorderStyleValues.Thin },
| new RightBorder(
| new Color() { Auto = true }
| )
| { Style = BorderStyleValues.Thin },
| new TopBorder(
| new Color() { Auto = true }
| )
| { Style = BorderStyleValues.Thin },
| new BottomBorder(
| new Color() { Auto = true }
| )
| { Style = BorderStyleValues.Thin },
| new DiagonalBorder())
| ),
| new CellFormats(
| new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
| new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 1 - Bold
| new CellFormat() { FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 2 - Italic
| new CellFormat() { FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 3 - Times Roman
| new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }, // Index 4 - Yellow Fill
| new CellFormat( // Index 5 - Alignment
| new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
| )
| { FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
| new CellFormat() { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true } // Index 6 - Border
| )
| ); // return
| }