/// <summary> /// Write xlsx spreadsheet file of a list of T objects /// Maximum of 24 columns /// </summary> /// <typeparam name="T">Type of objects passed in</typeparam> /// <param name="fileName">Full path filename for the new spreadsheet</param> /// <param name="def">A sheet definition used to create the spreadsheet</param> public static void Create <T>( string fileName, SheetDefinition <T> def) { // open a template workbook using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // create workbook part var workbookPart = myWorkbook.AddWorkbookPart(); // add stylesheet to workbook part var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet styles = new CustomStylesheet(); styles.Save(stylesPart); // create workbook var workbook = new Workbook(); // add work sheet var sheets = new Sheets(); sheets.AppendChild(CreateSheet(1, def, workbookPart)); workbook.AppendChild(sheets); // add workbook to workbook part myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }
/// <summary> /// 将DataTable转化为XML输出 /// </summary> /// <param name="dataSet">dataSet</param> /// <param name="fileName">文件名称</param> //public void DataTableToXML(DataTable dataTable, string fileName) public void DataSetToXML(DataSet dataSet, string filePath) { Dictionary <String, List <OpenXmlElement> > sets = ToSheets(dataSet); using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = package.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets()); foreach (KeyValuePair <String, List <OpenXmlElement> > set in sets) { WorksheetPart worksheetpart = workbookpart.AddNewPart <WorksheetPart>(); worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value)); worksheetpart.Worksheet.Save(); Sheet sheet = new Sheet() { Id = workbookpart.GetIdOfPart(worksheetpart), SheetId = (uint)(sheets.Count() + 1), Name = set.Key }; sheets.AppendChild(sheet); } workbookpart.Workbook.Save(); } }
/// <summary> /// Write xlsx spreadsheet file of a list of T objects /// Maximum of 24 columns /// </summary> /// <typeparam name="T">Type of objects passed in</typeparam> /// <param name="fileName">Full path filename for the new spreadsheet</param> /// <param name="defs">A list of sheet definitions used to create the spreadsheet</param> public static void Create <T>( string fileName, IEnumerable <SheetDefinition <T> > defs) { // open a template workbook using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // create workbook part var workbookPart = myWorkbook.AddWorkbookPart(); // add stylesheet to workbook part var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet styles = new CustomStylesheet(); styles.Save(stylesPart); // create workbook var workbook = new Workbook(); // add work sheets var sheets = new Sheets(); var list = defs.ToList(); for (var i = 0; i < list.Count(); i++) { sheets.AppendChild(CreateSheet(i + 1, list[i], workbookPart)); } workbook.AppendChild(sheets); // add workbook to workbook part myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }
internal static WorksheetPart AppendNewSheet(SpreadsheetDocument doc, WorksheetPart worksheetPart, string sheetName = null) { Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets()); } uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } if (string.IsNullOrEmpty(sheetName)) { sheetName = $"Sheet{sheetId}"; } sheets.AppendChild(new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName }); return(worksheetPart); }
public static Stream ToXLSX2(TranslationModule project) { MemoryStream xlsStream = new MemoryStream(); var worksheet = new MemoryWorksheet(); Export.ToIWorksheet(project, worksheet); using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myDoc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Sheet sheet = new Sheet() { Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.AppendChild(sheet); int cellIdx = 0; for (uint row = 0; row < worksheet.Rows; row++) { var r = new Row(); r.RowIndex = row; for (int col = 0; col < worksheet.Columns; col++) { /* * var c = new Cell(); * c.DataType = CellValues.String; * //string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : ""; * string val = "test;"; * c.CellValue = new CellValue(val); * * r.InsertAt(c, col); */ var c = CreateTextCell(ColumnLetter(cellIdx++), row, "test"); r.AppendChild(c); } sheetData.AppendChild(r); } worksheetPart.Worksheet.Save(); workbookPart.Workbook.Save(); myDoc.Close(); } return(xlsStream); }
public void CreateExcelDoc(string fileName) { using (SpreadsheetDocument document = SpreadsheetDocument .Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); List <Employee> employees = Employees.EmployeesList; Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" }; //Construct header Row row = new Row(); row.Append( ConstructCell("Id", CellValues.String), ConstructCell("Name", CellValues.String), ConstructCell("Birth Date", CellValues.String), ConstructCell("Salary", CellValues.String)); sheetData.AppendChild(row); //Add data foreach (var employee in employees) { row = new Row(); row.Append( ConstructCell(employee.Id.ToString(), CellValues.Number), ConstructCell(employee.Name, CellValues.String), ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String), ConstructCell(employee.Salary.ToString(CultureInfo.CurrentCulture), CellValues.Number)); sheetData.AppendChild(row); } sheets.AppendChild(sheet); //workbookPart.Workbook.Save(); worksheetPart.Worksheet.Save(); } }
/// <summary> /// Write xlsx spreadsheet file of a list of T objects /// Maximum of 24 columns /// </summary> /// <typeparam name="T">Type of objects passed in</typeparam> /// <param name="fileName">Full path filename for the new spreadsheet</param> /// <param name="def">A sheet definition used to create the spreadsheet</param> public static void Create <T>(string fileName, SheetDefinition <T>[] defs) { // open a template workbook using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // create workbook part var workbookPart = myWorkbook.AddWorkbookPart(); // add stylesheet to workbook part var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet styles = new CustomStylesheet(); //styles.Append(new CellFormats()); //styles.CellFormats.Append(new CellFormat()); //styles.CellFormats.Append(new CellFormat() { ApplyNumberFormat = true, NumberFormatId = 22 }); styles.Save(stylesPart); //var cc = myWorkbook.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ToList(); // create workbook var workbook = new Workbook(); int sheetIndex = 1; var sheets = new Sheets(); foreach (var def in defs) { // add work sheet sheets.AppendChild(CreateSheet(sheetIndex, def, workbookPart)); sheetIndex++; } workbook.AppendChild(sheets); CreateShareStringPart(myWorkbook); // add workbook to workbook part myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }
private void WriteWorksheets(WorkbookPart workbookPart, SaveContext context) { XlSharedStringsTable stringsTable = new XlSharedStringsTable(workbookPart, context); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); foreach (XlWorksheet sheet in Worksheets) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(context.RelIdGenerator.GetNext(RelType.Workbook)); SheetData sheetData = new SheetData(); Worksheet worksheet = new Worksheet(sheetData); worksheetPart.Worksheet = worksheet; if ( !worksheet.NamespaceDeclarations.Contains(new KeyValuePair <String, String>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } var sheetProperties = new SheetProperties { TabColor = new TabColor() }; string colorString = sheet.TabColor.HtmlColor; sheetProperties.TabColor.Rgb = colorString; worksheet.SheetProperties = sheetProperties; AddTableData(sheet, sheetData, stringsTable); worksheet.Save(); Sheet s = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)(sheets.Count() + 1), Name = sheet.Name }; sheets.AppendChild(s); workbookPart.Workbook.Save(); } }
private OpenXmlWriter CreateWriter() { var workbookPart = document.AddWorkbookPart(); var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); var sheets = new Sheets(); var sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName }; sheets.AppendChild(sheet); var workBook = new Workbook(); workbookPart.Workbook = workBook; workBook.AppendChild(sheets); return(OpenXmlWriter.Create(worksheetPart, Encoding.UTF8)); }
public ActionResult UsersExcel() { UsersRetrieveReq req = null; RequestParameter para = new RequestParameter(); para.Load(Request); req = new UsersRetrieveReq(); JsonConvert.PopulateObject(para.Item("json"), req); var memoryStream = new MemoryStream(); using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); #region Sheet1 SheetData sheetData = new SheetData(); Row row; #region header row = new Row(); //header row.Append( new Cell() { CellValue = new CellValue("序號") }, new Cell() { CellValue = new CellValue("帳號") }, new Cell() { CellValue = new CellValue("姓名") }, new Cell() { CellValue = new CellValue("密碼") }, new Cell() { CellValue = new CellValue("電子郵件") }, new Cell() { CellValue = new CellValue("狀態") }, new Cell() { CellValue = new CellValue("備註") }, new Cell() { CellValue = new CellValue("建檔時間") }, new Cell() { CellValue = new CellValue("建檔人員") }, new Cell() { CellValue = new CellValue("異動時間") }, new Cell() { CellValue = new CellValue("異動人員") } ); sheetData.AppendChild(row); #endregion #region data UsersRetrieveRes res = new KYL_CMS.Models.BusinessLogic.Users("SCC").ReportData(req); foreach (USERS data in res.USERS) //data { row = new Row(); row.Append( new Cell() { CellValue = new CellValue(data.SN.ToString()) }, new Cell() { CellValue = new CellValue(data.ID) }, new Cell() { CellValue = new CellValue(data.NAME.ToString()) }, new Cell() { CellValue = new CellValue(data.PASSWORD.ToString()) }, new Cell() { CellValue = new CellValue(data.EMAIL == null ? "" : data.EMAIL) }, new Cell() { CellValue = new CellValue(data.MODE.ToString()) }, new Cell() { CellValue = new CellValue(data.MEMO == null ? "" : data.MEMO) }, new Cell() { CellValue = new CellValue(data.CDATE.ToString()) }, new Cell() { CellValue = new CellValue(data.CUSER.ToString()) }, new Cell() { CellValue = new CellValue(data.MDATE.ToString()) }, new Cell() { CellValue = new CellValue(data.MUSER.ToString()) } ); sheetData.AppendChild(row); } #endregion Worksheet worksheet = new Worksheet(); worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; //add a Worksheet to the WorksheetPart Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); sheets.AppendChild(new Sheet() { Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "工作表1" }); #endregion } memoryStream.Seek(0, SeekOrigin.Begin); return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "TEST.xlsx")); }
/// <summary> /// Método para exportar a formato Excel una colección de objetos, a partir de un XML OpenXML. /// </summary> /// <typeparam name="T">Clase que define a la colección de objetos</typeparam> /// <param name="_data">Colección de objetos tipo T a exportar</param> /// <param name="_columnsToPrint">Arreglo de cadenas con los nombres de las propiedades a imprimir</param> /// <param name="_dateTimeFormat">Representa el formato de impresión para propiedades del objeto tipo DateTime</param> /// <param name="_printHeader">Indica si se imprime los títulos en la cabecera</param> /// <param name="_sheetName">Nombre de la hoja activa del libro</param> /// <returns>Regresa un Tuple con el archivo generado en el primer item /// y un comentario en el segundo item</returns> /// <remarks>El nombre de la columna se toma del atributo DisplatAttribute</remarks> public Tuple <byte[], string> ExportToExcelOpenXml <T>(IEnumerable <T> _data, IEnumerable <string> _columnsToPrint = null, string _dateTimeFormat = "dd/MM/yyyy", bool _printHeader = true, string _sheetName = null) where T : class { try { if (_data == null || !_data.Any()) { return(new Tuple <byte[], string>(null, String.Empty)); } System.IO.MemoryStream stream = new System.IO.MemoryStream(); SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); WorkbookPart workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Sheet sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = _sheetName ?? Resources.DefaultSheetName }; sheets.AppendChild(sheet); AddStyleSheet(document); PropertyInfo[] _properties = typeof(T).GetProperties(); UInt32 rowIdex = 0; var row = new Row { RowIndex = ++rowIdex }; sheetData.AppendChild(row); var cellIdex = 0; bool _print; if (_printHeader) { for (int _counter = 0; _counter < _properties.Length; _counter++) { _print = true; if (_columnsToPrint != null && _columnsToPrint.Any()) { _print = _columnsToPrint.Select(x => x.ToLowerInvariant()) .Contains(_properties[_counter].Name.ToLowerInvariant()); } if (_print) { var _displayAttribute = _properties[_counter].GetCustomAttributes(false) .FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute; row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _displayAttribute != null ? GetDisplayAttributeFromResourceType(_displayAttribute) : _properties[_counter].Name, 1)); } } } foreach (var x in _data) { cellIdex = 0; row = new Row { RowIndex = ++rowIdex }; sheetData.AppendChild(row); for (int _counter = 0; _counter < _properties.Length; _counter++) { _print = true; if (_columnsToPrint != null && _columnsToPrint.Any()) { _print = _columnsToPrint.Select(y => y.ToLowerInvariant()) .Contains(_properties[_counter].Name.ToLowerInvariant()); } if (_print) { Cell cell; var _value = _properties[_counter].GetValue(x); switch (_value != null ? _value.GetType().ToString() : string.Empty) { case "": case "System.String": cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty); break; case "System.DateTime": cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? ((DateTime)_value).ToString(_dateTimeFormat) : string.Empty); break; case "System.Boolean": cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? ((bool)_value ? ResourcesXMLExcel.TrueValue : ResourcesXMLExcel.FalseValue) : string.Empty); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": case "System.Decimal": case "System.Double": cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty); break; case "System.DBNull": cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.NullObject); break; default: cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.DefaultTypeText); break; } row.AppendChild(cell); } } } workbookpart.Workbook.Save(); document.Close(); return(new Tuple <byte[], string>(stream.ToArray(), null)); } catch (Exception ex) { return(new Tuple <byte[], string>(null, Resources.ExceptionText + ex.Message)); } }
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(); }
/// <summary> /// 生成多个sheet的Excel文件 /// </summary> /// <param name="dataItems"></param> /// <param name="shNames">sheet name</param> /// <param name="filename">生成的excel文件名(包含路径)</param> /// <param name="doMerge">true: 存在合并单元格/false: 不存在合并单元格</param> /// <param name="customFormat">true: 有自定义样式/false: 没有自定义样式</param> public void GenerateXlsxFile(List <BaseSheetData> exlsSheetData, string[] shNames, string filename, bool doMerge = false, bool customFormat = false) { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheetDoc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild(new Sheets()); SharedStringTablePart tbpart = null; var stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); List <SheetCellItem> allCells = new List <SheetCellItem>(); for (uint i = 0; i < exlsSheetData.Count; i++) { ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData; if (xlsxSheetData != null) { allCells.AddRange(xlsxSheetData.AllCells); } } if (allCells.Any()) { if (customFormat) { stylePart.Stylesheet = GenerateStylesheet(allCells); } else { stylePart.Stylesheet = DefaultStylesheet(); //GenerateDefaultStylesheet(allCells); } stylePart.Stylesheet.Save(); } if (allCells.Any(x => x.DataType == DataTypes.SharedString)) { tbpart = workbookPart.AddNewPart <SharedStringTablePart>(); } for (uint i = 0; i < exlsSheetData.Count; i++) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData; if (xlsxSheetData != null) { worksheetPart.Worksheet = new Worksheet(); if (customFormat) { var cols = GenerateColumns(xlsxSheetData.AllCells); if (cols != null) { worksheetPart.Worksheet.Append(cols); } } else { foreach (var c in xlsxSheetData.AllCells) { c.FormatIndex = c.RowIndex.Equals(1) ? 1u : 2u; } } worksheetPart.Worksheet.Append(CreateSheetData(xlsxSheetData.SheetRows, tbpart)); if (doMerge) { DoMerge(xlsxSheetData.AllCells, worksheetPart.Worksheet); } } UInt32Value id = UInt32Value.FromUInt32(i + 1); Sheet sheet = new Sheet { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = id, Name = shNames[i] }; sheets.AppendChild(sheet); } workbookPart.Workbook.Save(); } }
public async Task AddSheet <T>(IEnumerable <T> model, string sheetName) where T : class { if (model == default) { throw new ArgumentNullException(nameof(model)); } if (string.IsNullOrWhiteSpace(sheetName)) { throw new ArgumentException(nameof(sheetName)); } await Task.Run(() => { Sheet sheet = new Sheet() { Id = WorkbookPart.GetIdOfPart(WorksheetPart), SheetId = _sheetId++, Name = sheetName }; Sheets.AppendChild(sheet); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance); Row headerRow = new Row(); foreach (PropertyInfo property in properties) { headerRow.AppendChild(new Cell() { DataType = CellValues.String, CellValue = new CellValue(Localization.Get(property.Name)) }); } SheetData.AppendChild(headerRow); WorkbookPart.Workbook.Save(); Type stringType = typeof(string); foreach (T value in model) { Row row = new Row(); foreach (PropertyInfo property in properties) { object propertyValue = property.GetValue(value); if (propertyValue is IEnumerable enumerable && propertyValue.GetType() != stringType) { string tempResult = string.Empty; foreach (object childResult in enumerable) { if (childResult != null) { tempResult += $"{childResult}, "; } } propertyValue = tempResult.Length > 0 ? tempResult.Substring(0, tempResult.Length - 2) : tempResult; } Cell cell = new Cell() { CellValue = new CellValue(propertyValue?.ToString() ?? string.Empty) }; if (propertyValue is long || propertyValue is ulong || propertyValue is double || propertyValue is decimal) { cell.DataType = CellValues.Number; } else if (propertyValue is bool) { cell.DataType = CellValues.Boolean; } else { cell.DataType = CellValues.String; } row.AppendChild(cell); } SheetData.AppendChild(row); WorkbookPart.Workbook.Save(); } }); }
private static Sheet CreateProjectSheet(SpreadsheetDocument document, WorkbookPart workbookpart, Sheets sheets, int ProjectID, int month, int year, RATV3Entities db, uint sheetID = 1) { #region GetData //getting the department Project project = db.Projects.Where(x => x.ProjectID == ProjectID).FirstOrDefault(); Department department = db.Departments.Where(x => x.DepartmentID == project.DepartmentID).FirstOrDefault(); //setting the date interval for our Sprints DateTime firstDayOfMonth = new DateTime(year, month, 1); DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddTicks(-1); while (firstDayOfMonth.DayOfWeek != DayOfWeek.Monday) { firstDayOfMonth = firstDayOfMonth.AddDays(-1); } while (lastDayOfMonth.DayOfWeek != DayOfWeek.Sunday) { lastDayOfMonth = lastDayOfMonth.AddDays(1); } //GETTING SPRINTS + SPRINT IDS List <Sprint> sprints = db.Sprints.Where(x => x.ProjectID == ProjectID && (DbFunctions.TruncateTime(x.StartDate) >= firstDayOfMonth.Date && DbFunctions.TruncateTime(x.EndDate) <= lastDayOfMonth.Date)) .OrderBy(x => x.StartDate).ToList(); int[] sprintIds = new int[] { 0 }; if (sprints != null && sprints.Count > 0) { sprintIds = sprints.Select(x => x.SprintID).ToArray(); } //Getting Employees and Tasks List <Employee> employees = db.Employees.Where(x => x.DepartmentID == department.DepartmentID).ToList(); List <Task> tasks = db.Tasks.Where(x => x.SprintID.HasValue && sprintIds.Contains(x.SprintID.Value)).ToList(); #endregion WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart); var sheet = new Sheet() { Id = relationshipId, SheetId = sheetID, Name = project.Title }; sheets.AppendChild(sheet); var sheetData = new SheetData(); newWorksheetPart.Worksheet = new Worksheet(sheetData); //create header rows DateTime monthDate = new DateTime(year, month, 1); List <string> MergeableRows = new List <string>(); Row projectRow = CreateProjectRow(firstDayOfMonth, lastDayOfMonth, project, MergeableRows, monthDate, 1); Row sprintRow = CreateSprintRow(firstDayOfMonth, lastDayOfMonth, sprints, MergeableRows, 2); Row headerRow = CreateDatesRow(firstDayOfMonth, lastDayOfMonth, 3); sheetData.AppendChild(projectRow); sheetData.AppendChild(sprintRow); sheetData.AppendChild(headerRow); //Merging cells MergeCells mergeCells = new MergeCells(); for (int i = 0; i < MergeableRows.Count; i += 2) { mergeCells.Append(new MergeCell() { Reference = new StringValue(MergeableRows[i] + ":" + MergeableRows[i + 1]) }); } //creating content rows int colIndex = 4; foreach (Employee emp in employees) { //creating row for this employee List <Task> empTasks = tasks.Where(x => x.EmployeeID == emp.EmployeeID).ToList(); Row row = CreateEmployeeRow(firstDayOfMonth, lastDayOfMonth, colIndex, emp, empTasks); sheetData.AppendChild(row); colIndex++; } //set col width Columns columns = new Columns(); columns.Append(new Column() { Min = 1, Max = 1, Width = 20, CustomWidth = true }); columns.Append(new Column() { Min = 2, Max = 100, Width = 12, CustomWidth = true }); newWorksheetPart.Worksheet.Append(columns); //merging cells newWorksheetPart.Worksheet.InsertAfter(mergeCells, newWorksheetPart.Worksheet.Elements <SheetData>().First()); newWorksheetPart.Worksheet.Save(); return(sheet); }
public void WriteExcelModeOfPaymentFile(DataTable table, string path, string firstLine) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = GenerateStyleSheet(); stylesPart.Stylesheet.Save(); SheetData sheetData = new SheetData(); //add a row Row firstRow = new Row(); //firstRow.RowIndex = (UInt32)1; //create a cell in C1 (the upper left most cell of the merged cells) Cell dataCell = new Cell(); dataCell.CellReference = "A1"; dataCell.DataType = CellValues.String; CellValue cellValue = new CellValue(); cellValue.Text = firstLine; dataCell.Append(cellValue); firstRow.AppendChild(dataCell); sheetData.AppendChild(firstRow); // Add a WorkbookPart to the document. worksheetPart.Worksheet = new Worksheet(sheetData); //create a MergeCells class to hold each MergeCell MergeCells mergeCells = new MergeCells(); //append a MergeCell to the mergeCells for each set of merged cells mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:J1") }); worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <SheetData>().First()); //this is the part that was missing from your code Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); sheets.AppendChild(new Sheet() { Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "Sheet1" }); Row headerRow = new Row(); List <String> columns = new List <string>(); //skip// foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { Row newRow = new Row(); foreach (String col in columns) { Cell cell = new Cell(); if (col == " SALES" || col == "CASH" || col == "M-PESA" || col == "CREDIT CARD" || col == "DEBIT CARD" || col == "CHQEUE") { cell.DataType = CellValues.Number; cell.StyleIndex = 3; } else { cell.DataType = CellValues.String; } cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbookpart.Workbook.Save(); } }
public void Export(DataTable dt, string excelFilePath, string header) { if (dt == null || excelFilePath == string.Empty) { throw new ArgumentNullException(); } var fi = new FileInfo(excelFilePath); #region 文件已存在 if (File.Exists(excelFilePath) && fi.Length != 0) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, true)) { Sheet sheet = document.WorkbookPart.Workbook.Descendants <Sheet>() .FirstOrDefault(s => s.Name == EscapeSheetNameInvalidChar(dt.TableName)); if (sheet != null) { var worksheetPart = (WorksheetPart)(document.WorkbookPart.GetPartById(sheet.Id)); sheet.Remove(); document.WorkbookPart.DeletePart(worksheetPart); document.WorkbookPart.Workbook.Save(); } // document.AddWorkbookPart(); // document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) // document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView())); // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! // WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); // Stylesheet stylesheet = new Stylesheet(); // workbookStylesPart.Stylesheet = stylesheet; uint sheetId = 1; var newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); // save worksheet WriteDataTableToExcelWorksheet(dt, newWorksheetPart); newWorksheetPart.Worksheet.Save(); var sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { throw new OpenXmlPackageException("已有Excel文件损坏,请删除"); } // create the worksheet to workbook relation //if (worksheetNumber == 1) // document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } sheets.AppendChild(new Sheet { Id = document.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = EscapeSheetNameInvalidChar(dt.TableName) }); document.WorkbookPart.Workbook.Save(); Trace.WriteLine("成功创建: " + excelFilePath); } } #endregion #region 新建文件 else { using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook)) { document.AddWorkbookPart(); document.WorkbookPart.Workbook = new Workbook(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) // document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView())); // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! // WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); // Stylesheet stylesheet = new Stylesheet(); // workbookStylesPart.Stylesheet = stylesheet; uint sheetId = 1; var newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); HeaderFooter headerFooter = new HeaderFooter(); headerFooter.FirstHeader = new FirstHeader(); headerFooter.FirstHeader.Text = header; newWorksheetPart.Worksheet.AppendChild <HeaderFooter>(headerFooter);; // save worksheet WriteDataTableToExcelWorksheet(dt, newWorksheetPart); newWorksheetPart.Worksheet.Save(); var sheets = new Sheets(); document.WorkbookPart.Workbook.AppendChild(sheets); if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } sheets.AppendChild(new Sheet { Id = document.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = EscapeSheetNameInvalidChar(dt.TableName) }); document.WorkbookPart.Workbook.Save(); } Trace.WriteLine("成功创建: " + excelFilePath); } #endregion }
public async Task <string> GetXlsxAsync(IReadOnlyCollection <Department> departments) { if (departments == null) { throw new InvalidOperationException("尚未進行選股,無法匯出。"); } if (departments.Count == 0) { throw new InvalidOperationException("股別數為零,無法匯出。"); } if (departments.Select(x => x.Students).Sum(x => x.Count) == 0) { throw new InvalidOperationException("股內人數加總為零,無法匯出。"); } var date = DateTime.Now; var ms = new MemoryStream(); using (var spreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheet.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); // Insert stylesheet to make the top row bold var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); workbookStylesPart.Stylesheet = CreateStylesheet(); // Define sheet var sheetName = $"{date:yyyy}-股員名單"; WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName }; sheets.AppendChild(sheet); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); // Insert data var row = new Row { RowIndex = 1 }; row.Append( new Cell { CellValue = new CellValue("學號"), DataType = CellValues.String }, new Cell { CellValue = new CellValue("姓名"), DataType = CellValues.String }, new Cell { CellValue = new CellValue("股別"), DataType = CellValues.String }); sheetData.AppendChild(row); uint rowIndex = 2; foreach (var department in departments) { foreach (var student in department.Students) { var entryRow = new Row { RowIndex = rowIndex }; entryRow.Append( new Cell { CellValue = new CellValue(student.Id), DataType = CellValues.String, StyleIndex = 1 }, new Cell { CellValue = new CellValue(student.Name), DataType = CellValues.String, StyleIndex = 1 }, new Cell { CellValue = new CellValue(student.Department.Name), DataType = CellValues.String, StyleIndex = 1 }); sheetData.AppendChild(entryRow); rowIndex++; } } // Add title spreadsheet.PackageProperties.Title = $"{date:yyyy} 選股結果"; spreadsheet.PackageProperties.Creator = "RaffleBlazor 選股程式"; } return(await ReadStreamToBase64(ms)); }
private static void BuildWorkbook(string filename, int count = 3) { try { using (SpreadsheetDocument doc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = doc.AddWorkbookPart(); WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); WorkbookStylesPart workbookStylesPart = workbookpart.AddNewPart <WorkbookStylesPart>(); workbookpart.Workbook = new Workbook(); worksheetPart.Worksheet = new Worksheet(); workbookStylesPart.Stylesheet = CreateStylesheet(); Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets()); Columns columns = worksheetPart.Worksheet.AppendChild(new Columns()); //顺序在columns之后 SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); #region sheets var sheet = sheets.AppendChild(new Sheet { Name = "产品", SheetId = 1, Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), }); #endregion #region cols columns.Append(new Column() { Min = 1, Max = 1, Width = 38.22d, CustomWidth = true }); columns.Append(CreateColumnData(2, 4, 20)); columns.Append(CreateColumnData(6, 6, 6.5703125)); #endregion #region autofilter worksheetPart.Worksheet.AppendChild(new AutoFilter { Reference = "B1" }); #endregion autofilter #region header Row r; Cell c; // header r = new Row(); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "A1"; c.CellValue = new CellValue("产品ID"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "B1"; c.CellValue = new CellValue("产品说明"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "C1"; c.CellValue = new CellValue("订单说明"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "D1"; c.CellValue = new CellValue("折扣说明"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "E1"; c.CellValue = new CellValue("货币"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; c.CellReference = "F1"; c.CellValue = new CellValue("费用"); r.Append(c); sheetData.Append(r); #endregion #region data for (int i = 0; i < count; i++) { r = new Row(); c = new Cell(); c.DataType = CellValues.String; // c.CellReference = "A2"; c.CellValue = new CellValue("15D05473-742F-4691-8BF4-5124F2D66176"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; // c.CellReference = "B2"; c.CellValue = new CellValue("Iced Lemon Tea"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; // c.CellReference = "C2"; c.CellValue = new CellValue("Special Iced Lemon Tea"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; // c.CellReference = "D2"; c.CellValue = new CellValue("Iced Lemon Tea (50% off)"); r.Append(c); c = new Cell(); c.DataType = CellValues.String; // c.CellReference = "E2"; c.CellValue = new CellValue("USD"); r.Append(c); c = new Cell(); c.StyleIndex = 3; c.DataType = CellValues.Number; // c.CellReference = "F2"; c.CellValue = new CellValue(i.ToString()); r.Append(c); c = new Cell(); c.DataType = CellValues.Boolean; // c.CellReference = "G2"; c.CellValue = new CellValue("0"); r.Append(c); sheetData.Append(r); } #endregion doc.Save(); } } catch (Exception e) { Console.WriteLine(e.ToString()); Console.ReadLine(); } }
public JsonResult ExportExcel(string SSN = "", string DEPSEQ = "", string claim = "", string Dependent = "", string SortingColumn = "", string Orderby = "", DateTime? Fromdate = null, DateTime? Todate = null) { DataTable ExcelDatatable = new DataTable(); var fileName = "ClaimDetails" + ".xlsx"; string fullPath = Path.Combine(Server.MapPath("~/ExcelFile"), fileName); try { DataTable dt = new DataTable(); if (DEPSEQ == null || DEPSEQ == "0") { dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTMemberClaims(SSN, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text); } else { dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTDependentClaims(SSN, DEPSEQ, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text); } ExcelDatatable = GetClaimDetailExportDatatable(dt); using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = myDoc.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); Worksheet ws = new Worksheet(); SheetData sheetData = new SheetData(); WorkbookStylesPart wbsp = workbookpart.AddNewPart<WorkbookStylesPart>(); wbsp.Stylesheet = GenerateStylesheet(); wbsp.Stylesheet.Save(); Columns columns = new Columns(); columns.Append(CreateColumnData(1, 1, 11)); columns.Append(CreateColumnData(2, 3, 16)); columns.Append(CreateColumnData(4, 4, 20)); columns.Append(CreateColumnData(5, 5, 11)); columns.Append(CreateColumnData(6, 6, 28)); columns.Append(CreateColumnData(7, 9, 11)); ws.Append(columns); //add a row Row firstRow = new Row(); firstRow.RowIndex = (UInt32)1; Cell dataCell = new Cell(); CellValue cellValue = new CellValue(); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A1"; dataCell.StyleIndex = 2; dataCell.CellValue = new CellValue("Filtered By"); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A2"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("From Date : " + ((Fromdate == null) ? "ALL" : Fromdate?.ToString("MM/dd/yyyy"))); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A3"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("To Date : " + ((Todate == null) ? "ALL" : Todate?.ToString("MM/dd/yyyy"))); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A4"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("DEPN# : " + ((DEPSEQ == "") ? "ALL" : DEPSEQ)); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A5"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("CLAIM# : " + ((claim == "") ? "ALL" : claim)); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); List<String> columns1 = new List<string>(); foreach (System.Data.DataColumn column in ExcelDatatable.Columns) { columns1.Add(column.ColumnName); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.StyleIndex = 2; dataCell.CellValue = new CellValue(column.ColumnName); firstRow.Append(dataCell); } sheetData.Append(firstRow); foreach (DataRow dsrow in ExcelDatatable.Rows) { firstRow = new Row(); foreach (String col in columns1) { dataCell = new Cell(); dataCell.DataType = CellValues.String; //c.CellReference = "A2"; dataCell.StyleIndex = 1; dataCell.CellValue = new CellValue(dsrow[col].ToString()); firstRow.Append(dataCell); } sheetData.Append(firstRow); } ws.Append(sheetData); worksheetPart.Worksheet = ws; MergeCells mergeCells = new MergeCells(); //append a MergeCell to the mergeCells for each set of merged cells mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:I1") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A2:I2") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A3:I3") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A4:I4") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A5:I5") }); worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First()); //this is the part that was missing from your code Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets()); sheets.AppendChild(new Sheet() { Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "Sheet1" }); //myDoc.WorkbookPart.Workbook = workbookpart.Workbook; myDoc.WorkbookPart.Workbook.Save(); myDoc.Close(); } } catch (Exception) { throw; } return Json(new { fileName = fileName }); }
public void WriteExcelProductWiseSaleFile(DataTable table, string path, string firstLine) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = GenerateStyleSheet(); stylesPart.Stylesheet.Save(); SheetData sheetData = new SheetData(); //add a row Row firstRow = new Row(); //firstRow.RowIndex = (UInt32)1; //create a cell in C1 (the upper left most cell of the merged cells) //Cell dataCell = new Cell(); //dataCell.CellReference = "A1"; //dataCell.DataType = CellValues.String; //CellValue cellValue = new CellValue(); //cellValue.Text = firstLine; //dataCell.Append(cellValue); //firstRow.AppendChild(dataCell); //sheetData.AppendChild(firstRow); // Add a WorkbookPart to the document. worksheetPart.Worksheet = new Worksheet(sheetData); //create a MergeCells class to hold each MergeCell //MergeCells mergeCells = new MergeCells(); //append a MergeCell to the mergeCells for each set of merged cells //mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:J1") }); //worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First()); //this is the part that was missing from your code Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); sheets.AppendChild(new Sheet() { Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "Productwise Sales Report" }); Row headerRow = new Row(); List <String> columns = new List <string>(); //Hide Coulumn name// foreach (System.Data.DataColumn column in table.Columns) { if (column.ColumnName == "Id" || column.ColumnName == "RowNumber" | column.ColumnName == "FoodMenuName") { } else { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(null);// (column.ColumnName); headerRow.AppendChild(cell); } } //sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { if (dsrow[4].ToString().Contains("Total")) { Row newRow = new Row(); foreach (String col in columns) { Cell cell = new Cell(); if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice") { cell.DataType = CellValues.Number; cell.StyleIndex = 3; } else { cell.DataType = CellValues.String; } cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } else if (dsrow[0].ToString().Contains("==")) { } else { Row newRow = new Row(); foreach (String col in columns) { Cell cell = new Cell(); if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice") { cell.DataType = CellValues.Number; cell.StyleIndex = 3; } else { cell.DataType = CellValues.String; } cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } workbookpart.Workbook.Save(); } }