protected override DataTable ReadFile() { string outputPath = CopyFileToAppData(); DataTable dataTable = new DataTable(); using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(outputPath, false)) { DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, _sheetName); DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { dataTable.Columns.Add(GetCellValue(spreadsheetDocument, cell)); } for (int i = 1; i < rows.Count() - 1; i++) { DataRow tempRow = dataTable.NewRow(); for (int j = 0; j < rows.ElementAt(i).Descendants <Cell>().Count(); j++) { tempRow[j] = GetCellValue(spreadsheetDocument, rows.ElementAt(i).Descendants <Cell>().ElementAt(j)); } dataTable.Rows.Add(tempRow); } } return(dataTable); }
protected internal Sheet(ref DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, ref DocumentFormat.OpenXml.Spreadsheet.Sheet sheet) { this.worksheetPart = worksheetPart; this.worksheet = this.worksheetPart.Worksheet; this.sheetData = this.worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); this.sheet = sheet; }
protected void PopulateExcelTable(DataTable dataTable, excel.SheetData sheetData) { excel.Row excelRow = new excel.Row(); foreach (DataColumn dataColumn in dataTable.Columns) { excelRow.Append(new excel.Cell() { CellValue = new excel.CellValue(GetColumnName(dataColumn)) }); } sheetData.AppendChild(excelRow); foreach (DataRow row in dataTable.Rows) { excelRow = new excel.Row(); foreach (object cellItem in row.ItemArray) { excelRow.Append(new excel.Cell() { CellValue = new excel.CellValue(cellItem.ToString()) }); } sheetData.AppendChild(excelRow); } }
private void ExportDatatableToExcel(DataTable table, string destination) { using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } }
private void Summarize(MSOpenXML.SheetData ws, int row, List <int[]> totais) { for (int i = 0; i < _columns.Count; i++) { string lCommand = ""; switch (_columns[i].SummaryOperation) { case ESummaryOperation.None: break; case ESummaryOperation.Sum: lCommand = "SUBTOTAL(9,"; break; case ESummaryOperation.Average: lCommand = "AVERAGE("; break; case ESummaryOperation.Count: lCommand = ""; break; case ESummaryOperation.Multiply: lCommand = "PRODUCT("; break; case ESummaryOperation.Acumulate: throw new InvalidOperationException("Acumulate is not supported by MS Excel."); case ESummaryOperation.Mode: lCommand = "MODE("; break; default: break; } MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i + _groups.Count)); if (lCommand != "") { c.CellFormula = new MSOpenXML.CellFormula("{0}{1})".Formata(lCommand, totais.Aggregate <int[], string>("", (r, n) => r = r + "," + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1]).Right(-1))); } if (_columns[i].SummaryOperation == ESummaryOperation.Count) { c.CellFormula = new MSOpenXML.CellFormula(totais.Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(i + _groups.Count) + n[0] + ":" + GetColumnName(i + _groups.Count) + n[1] + ")").Right(-1)); } } }
private SheetData WriteDataTableToExcelWorksheet() { SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = this.exportConfig.Count; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } uint rowIndex = 1; // // Create the Header row in our Excel Worksheet // rowIndex = WriteHeaderToExcelWorksheet(rowIndex, sheetData, excelColumnNames); PropertyInfo[] properties = WriteDataFieldsHeaderToExcelWorksheet(rowIndex, sheetData, excelColumnNames); // // Now, step through each row of data in our DataTable... rowIndex = WriteDataFieldsDataRowsToExcelWorksheet(rowIndex, sheetData, properties, excelColumnNames); if (typeof(T).Name.Equals("ItemUsageReportItemModel")) { AddTotalRowExcel(rowIndex, excelColumnNames, sheetData); } return(sheetData); }
//new public void ExportDSToExcel(DataSet ds, string destination) { using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; foreach (DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> columns = new List<string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } } }
public void CreateExcel(Stream stream) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" }; sheets.Append(sheet); workbookPart.Workbook.Save(); //List<Employee> employees = Employees.EmployeesList; DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // Constructing header Row row = new Row(); row.Append( ConstructCell("Id", CellValues.String), ConstructCell("Name", CellValues.String), ConstructCell("Birth Date", CellValues.String), ConstructCell("Salary", CellValues.String)); // Insert the header row to the Sheet Data sheetData.AppendChild(row); // Inserting each employee for (int i = 0; i < 5; i++) { row = new Row(); row.Append( ConstructCell(14.ToString(), CellValues.Number), ConstructCell("Employee", CellValues.String), ConstructCell(DateTime.Now.ToShortDateString(), CellValues.String), ConstructCell((20000).ToString(), CellValues.Number)); sheetData.AppendChild(row); } worksheetPart.Worksheet.Save(); } }
// Create Excel Sheet public void UpdateCells(string docName, IList <ExcelSheetInput> excelSheetInputs) { using (SpreadsheetDocument createdDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart createdWorkbookPart = createdDocument.AddWorkbookPart(); createdWorkbookPart.Workbook = new spd.Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart createdWorksheetPart = createdWorkbookPart.AddNewPart <WorksheetPart>(); createdWorksheetPart.Worksheet = new spd.Worksheet(); spd.Sheets createdSheets = createdWorkbookPart.Workbook.AppendChild(new spd.Sheets()); spd.Sheet createdSheet = new spd.Sheet() { Id = createdWorkbookPart.GetIdOfPart(createdWorksheetPart), SheetId = 1, Name = "Sheet 1" }; createdSheets.Append(createdSheet); createdWorkbookPart.Workbook.Save(); spd.SheetData sheetData = createdWorksheetPart.Worksheet.AppendChild(new spd.SheetData()); var rowsCount = excelSheetInputs.Last().RowNumber; for (var rowsCounter = 1; rowsCounter <= rowsCount; rowsCounter++) { var rowItems = excelSheetInputs.Where(e => e.RowNumber == rowsCounter).ToList(); // Constructing header spd.Row createdRow = new spd.Row(); foreach (ColumnName c in Enum.GetValues(typeof(ColumnName))) { try { var cellValue = rowItems.First(e => e.ColumnName.Equals(c.ToString())); createdRow.Append(ConstructCell(cellValue.Text, spd.CellValues.String)); } catch (Exception) { createdRow.Append(ConstructCell("", spd.CellValues.String)); } } sheetData.AppendChild(createdRow); } createdWorkbookPart.Workbook.Save(); } }
private int PrintGroupFooter(MSOpenXML.SheetData ws, int row, object[] lLastKey, List <int[]>[] lGroupIndexes, int i) { MergeRange(ws.Parent as MSOpenXML.Worksheet, CellName(row, i), CellName(row, _groups.Count - 1)); //ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Font.Bold = true; //ws.Range[ws.Cells[row, i + 1], ws.Cells[row, _columns.Count + _groups.Count]].Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; MSOpenXML.Cell c = CreateCellIfNotExist(ws.Parent as MSOpenXML.Worksheet, CellName(row, i)); c.CellFormula = new MSOpenXML.CellFormula("CONCATENATE(\"Total \'" + lLastKey[i] + "\': \"," + lGroupIndexes[i].Aggregate <int[], string>("", (r, n) => r = r + "+ROWS(" + GetColumnName(_groups.Count) + n[0] + ":" + GetColumnName(_groups.Count + _columns.Count - 1) + n[1] + ")").Right(-1) + ")"); Summarize(ws, row, lGroupIndexes[i]); (c.Parent as MSOpenXML.Row).Descendants <MSOpenXML.Cell>().ForEach(x => x.StyleIndex = 6); lGroupIndexes[i].Clear(); return(++row); }
/// <summary> /// Initializes a new instance of the <see cref="OpenXmlExcelWriteManager" /> class. /// </summary> public OpenXmlExcelWriteManager(OpenXmlSpreadsheet.Worksheet worksheet) { if (worksheet == null) { throw new ArgumentNullException("worksheet"); } this.worksheet = worksheet; this.sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); this.columnLetterStore = new Dictionary <uint, string>(); this.excelColumns = new Dictionary <uint, OpenXmlSpreadsheet.Column>(); this.excelRows = new Dictionary <uint, OpenXmlSpreadsheet.Row>(); }
/// <summary> /// Processes the paragraph part. /// </summary> /// <param name="wkb">The WKB.</param> /// <param name="part">The part.</param> /// <param name="placeholder">The placeholder.</param> /// <param name="mapping">The mapping.</param> /// <param name="worksheetPart">The worksheet part.</param> /// <param name="reportWSPart">The report ws part.</param> /// <returns></returns> private static DrawingSpreadsheet.Shape ProcessParagraphPart(OpenXmlSpreadsheet.Workbook wkb, ExportPart part, MappingPlaceholder placeholder, ParagraphMapping mapping, WorksheetPart worksheetPart, WorksheetPart reportWSPart) { string SheetName = ""; uint RowStart = 0; uint RowEnd = 0; uint ColStart = 0; uint ColEnd = 0; // if no placeholder is specified then return now if (placeholder == null) { return(null); } DrawingSpreadsheet.Shape matchShape = worksheetPart.GetShapeByName(placeholder.Id); if (matchShape == null) { return(null); } // save the location of this shape, // this information will be used to position the incoming chart Extents extents = matchShape.ShapeProperties.Transform2D.Extents; Offset offset = matchShape.ShapeProperties.Transform2D.Offset; OpenXmlSpreadsheet.DefinedName rtfXmlDefinedName = wkb.GetDefinedNameByName(string.Format("{0}_{1}", part.DataSheetName, mapping.SourceFieldName)); wkb.BreakDownDefinedName(rtfXmlDefinedName, ref SheetName, ref RowStart, ref RowEnd, ref ColStart, ref ColEnd); WorksheetPart wksp = wkb.GetWorksheetPartByName(SheetName); OpenXmlSpreadsheet.SheetData sheetData = wksp.Worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); OpenXmlSpreadsheet.Cell rtfXmlCell = sheetData.GetCell(ColStart, RowStart + 1); // Use the cell on the hidden data sheet as source for the XAML reader Section RTFSection = XamlSectionDocumentReader(rtfXmlCell.CellValue.InnerText); // The paragraph in the cell.inlinestring have a very different class structure to the paragraphs in the shape.textbody // So, the paragraph will need to go through a converter to do this. DrawingSpreadsheet.Shape targetShape = ConvertParagraph(worksheetPart, RTFSection, matchShape); // positon the new graphic frame after the shape its going to replace matchShape.Parent.InsertAfter <OpenXmlElement>(targetShape, matchShape); matchShape.Remove(); return(targetShape); }
}//end method public static MemoryStream ExportDataSet(DataSet ds) { MemoryStream ms = new MemoryStream(); using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); foreach (System.Data.DataTable table in ds.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 1) { sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> columns = new List<String>(); foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); }//end loop sheetData.AppendChild(headerRow); foreach (System.Data.DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); }//end loop sheetData.AppendChild(newRow); }//end loop }//end loop workbookPart.Workbook.Save(); workbook.Close(); }//end using ms.Position = 0; return ms; }//end method
/// <summary> /// Inserts the text. /// </summary> /// <param name="spreadSheetData">The spread sheet data.</param> /// <param name="sharedStringPart">The share string part.</param> /// <param name="rowIndex">Index of the row.</param> /// <param name="column">The column.</param> /// <param name="text">The text.</param> /// <param name="cellValues">The cell values.</param> protected static void InsertText(SpreadSheet.SheetData spreadSheetData, SharedStringTablePart sharedStringPart, string column, int rowIndex, string text, SpreadSheet.CellValues cellValues) { int sharedStringIndex = 0; rowIndex += 1; if (cellValues == SpreadSheet.CellValues.SharedString) { sharedStringIndex = InsertSharedStringItem(sharedStringPart, text); } SpreadSheet.Cell cell = InsertCellInWorksheet(spreadSheetData, column, (uint)rowIndex); cell.CellValue = new SpreadSheet.CellValue(cellValues == SpreadSheet.CellValues.SharedString ? sharedStringIndex.ToString() : text); cell.DataType = new EnumValue <SpreadSheet.CellValues>(cellValues); }
/// <summary> /// Inserts the cell in worksheet. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="columnName">Name of the column.</param> /// <param name="rowIndex">Index of the row.</param> /// <returns></returns> protected static SpreadSheet.Cell InsertCellInWorksheet(SpreadSheet.SheetData sheetData, string columnName, uint rowIndex) { string cellReference = columnName + rowIndex; SpreadSheet.Row row; if (sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).First(); } else { row = new SpreadSheet.Row() { RowIndex = rowIndex }; sheetData.Append(row); } // If column doesn't exist, insert one. if (row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return(row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == cellReference).First()); } else { SpreadSheet.Cell refCell = null; foreach (SpreadSheet.Cell cell in row.Elements <SpreadSheet.Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } SpreadSheet.Cell newCell = new SpreadSheet.Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); return(newCell); } }
static void SaveRow(Excel.SheetData exportedSheetData, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Row row) { Excel.Row exportedRow = new Excel.Row() { RowIndex = RowIndex(row), Hidden = row._hidden }; if (row._hidden) { exportedRow.Hidden = true; } exportedSheetData.Append(exportedRow); foreach (var cell in row._cells.OrderBy(r => r.Column._index)) { SaveCell(exportedRow, styleSheet, cellFormatList, cell); } }
/// <summary> /// Updates the embedded object. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="sharedStringTablePart">The shared string table part.</param> protected override void UpdateEmbeddedObject(SpreadSheet.SheetData sheetData, SharedStringTablePart sharedStringTablePart) { int rowIndex = 0; // Add first row for column names(A, B) InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(0), rowIndex, chartData.xColumnName, SpreadSheet.CellValues.SharedString); InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(1), rowIndex, chartData.yColumnName, SpreadSheet.CellValues.SharedString); rowIndex += 1; // Add subsequent rows for column names(A, B) foreach (var v in chartData.xValToYValMap) { InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(0), rowIndex, v.Key.ToString(), SpreadSheet.CellValues.Number); InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(1), rowIndex, v.Value.ToString(), SpreadSheet.CellValues.Number); rowIndex += 1; } }
private MSOpenXML.Row CreateContentRow(MSOpenXML.SheetData ws, int row, object[] values, Type[] types) { //Create cells that contain data MSOpenXML.Row r = ws.AppendChild(new MSOpenXML.Row()); r.RowIndex = (uint)row; for (int i = 0; i < values.Length; i++) { MSOpenXML.Cell c = r.AppendChild(new MSOpenXML.Cell()); c.CellReference = CellName(row, i); //if (values[i] != null) Type t = types[i]; if (t == null) { c.DataType = MSOpenXML.CellValues.String; c.CellValue = new MSOpenXML.CellValue(); } else if (t.In(typeof(int), typeof(int?), typeof(long), typeof(long?))) { c.StyleIndex = 2; //DataType = dos.CellValues.Number; c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((long)Convert.ChangeType(values[i], typeof(long))).ToString()); } else if (t.In(typeof(decimal), typeof(decimal?), typeof(float), typeof(float?), typeof(double), typeof(double?))) { c.StyleIndex = 3; //DataType = dos.CellValues.Number; c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((decimal)Convert.ChangeType(values[i], typeof(decimal))).ToString(_en_us_ci.NumberFormat)); } else if (t.In(typeof(DateTime), typeof(DateTime?))) { c.StyleIndex = 1; //.DataType = dos.CellValues.Date; c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((DateTime)values[i]).ToOADate().ToString(_en_us_ci.NumberFormat)); } else if (t.In(typeof(bool), typeof(bool?))) { c.DataType = MSOpenXML.CellValues.Boolean; c.CellValue = new MSOpenXML.CellValue(values[i].ToString()); } else { c.DataType = MSOpenXML.CellValues.String; c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : values[i].ToString()); } } return(r); }
/// <summary> /// Updates the embedded object. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="sharedStringTablePart">The shared string table part.</param> protected override void UpdateEmbeddedObject(SpreadSheet.SheetData sheetData, SharedStringTablePart sharedStringTablePart) { int seriesIndex = 0; foreach (string seriesKey in chartData.columnNameToSeries.Keys) { // Add first row for column names(A, B ...) InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(seriesIndex), 0, seriesKey, SpreadSheet.CellValues.SharedString); for (int seriesPointIndex = 0; seriesPointIndex < chartData.columnNameToSeries[seriesKey].Count; seriesPointIndex++) { // Add subsequent rows for column names(A, B ...) // Index 0 is category axis data InsertText(sheetData, sharedStringTablePart, GetExcelColumnName(seriesIndex), seriesPointIndex + 1, chartData.columnNameToSeries[seriesKey][seriesPointIndex].ToString(), seriesIndex == 0 ? SpreadSheet.CellValues.SharedString : SpreadSheet.CellValues.Number); } seriesIndex += 1; } }
/// <summary> /// Merges all cells that have been marked to be merged in the cellinfos dictionary. /// </summary> /// <param name="worksheet">The worksheet.</param> /// <param name="totalRowCount">The total number of rows in the worksheet.</param> /// <param name="totalColCount">The total number of columns in the worksheet.</param> /// <param name="cellInfos">A dictionary of cell information, keyed by row and column index.</param> private static void MergeCells(OpenXmlSpreadsheet.Worksheet worksheet, uint totalRowCount, uint totalColCount, LayeredCellsDictionary cellInfos) { OpenXmlSpreadsheet.SheetData sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); // Process the collection of merged cells. for (uint worksheetRow = 1; worksheetRow <= totalRowCount; worksheetRow++) { for (uint worksheetCol = 1; worksheetCol <= totalColCount; worksheetCol++) { // Get cellInfo, if it has a MergeTo then merge it. var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); ExcelCellInfo cellInfo = cellInfos[currentCoOrdinate].CellInfo; if (cellInfo.MergeTo != null) { sheetData.MergeCells(cellInfo.Cell, cellInfo.MergeTo.Cell); } } } }
/// <summary> /// Updates the embedded object. /// </summary> /// <returns></returns> protected string UpdateEmbeddedObject() { EmbeddedPackagePart embeddedPackagePart = chartPart.EmbeddedPackagePart; string sheetName = string.Empty; if (embeddedPackagePart != null) { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(embeddedPackagePart.GetStream(), true)) { SharedStringTablePart sharedStringTablePart; SpreadSheet.Sheet sheet = spreadsheetDoc.WorkbookPart.Workbook.Sheets.FirstOrDefault() as SpreadSheet.Sheet; string sheetId = sheet.Id; sheetName = sheet.Name; WorksheetPart wsp = (WorksheetPart)spreadsheetDoc.WorkbookPart.Parts.Where(pt => pt.RelationshipId == sheetId).FirstOrDefault().OpenXmlPart; SpreadSheet.SheetData sheetData = wsp.Worksheet.Elements <SpreadSheet.SheetData>().FirstOrDefault(); if (spreadsheetDoc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { sharedStringTablePart = spreadsheetDoc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); if (sharedStringTablePart.SharedStringTable != null) { sharedStringTablePart.SharedStringTable.RemoveAllChildren <SpreadSheet.SharedStringItem>(); } } else { sharedStringTablePart = spreadsheetDoc.WorkbookPart.AddNewPart <SharedStringTablePart>(); } sheetData.RemoveAllChildren <SpreadSheet.Row>(); UpdateEmbeddedObject(sheetData, sharedStringTablePart); wsp.Worksheet.Save(); } } return(sheetName); }
/* * static Excel.SharedStringTable SaveSharedStringTable() * { * var exportedSharedStringTable = new Excel.SharedStringTable(); * * return exportedSharedStringTable; * } */ static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId) { var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>(); string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart); var exportedWorksheet = new Excel.Worksheet(); exportedWorksheetPart.Worksheet = exportedWorksheet; var exportedColumns = new Excel.Columns(); exportedWorksheet.Append(exportedColumns); var exportedSheetData = new Excel.SheetData(); exportedWorksheet.Append(exportedSheetData); var exportedSheet = new Excel.Sheet() { Name = sheet.Name, Id = relId, SheetId = sheetId }; if (sheet.Hidden) { exportedSheet.State = Excel.SheetStateValues.Hidden; } exportedSheets.Append(exportedSheet); foreach (var column in sheet.Columns.OrderBy(r => r.Index)) { SaveColumn(exportedColumns, column); } foreach (var row in sheet.Rows.OrderBy(r => r.Index)) { SaveRow(exportedSheetData, styleSheet, cellFormatList, row); } exportedWorksheetPart.Worksheet.Save(); }
public List <Excel.Cell> FindParsedCells(StringKeyList stringTable, Excel.SheetData sd) { List <Excel.Cell> results = new List <Excel.Cell>(); foreach (OpenXmlElement element in sd) { if (element is Excel.Row row) { foreach (OpenXmlElement celement in element) { if (celement is Excel.Cell cell) { string val = ReadCell(cell, stringTable); if (val.IndexOf('#') >= 0) { results.Add(cell); } } } } } return(results); }
/// <summary> /// Updates the embedded object. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="sharedStringTablePart">The shared string table part.</param> protected abstract void UpdateEmbeddedObject(SpreadSheet.SheetData sheetData, SharedStringTablePart sharedStringTablePart);
public void generarReporteGenerico(DataSet dsDatosReporte, string nombreReporte) { string directorio = @"C:\Reportes\"; string pathArchivo = directorio + nombreReporte + ".xlsx"; if (!Directory.Exists(directorio)) { Directory.CreateDirectory(directorio); } try { var workbook = SpreadsheetDocument.Create((pathArchivo), DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook); { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); foreach (System.Data.DataTable table in dsDatosReporte.Tables) { var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (System.Data.DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } } workbook.WorkbookPart.Workbook.Save(); workbook.Close(); } } catch (Exception) { throw; } System.Diagnostics.Process.Start(pathArchivo); }
public static String ExportHportIDXToOpenXMLCSV(GridView gv, string fileName, string sheetName = "") { string filePath = HttpContext.Current.Server.MapPath(fileName + sheetName.ToString() + ".xlsx"); StringBuilder sbCSVString = new StringBuilder(); try { using (var workbook = SpreadsheetDocument.Create(filePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet(); sheet.Id = relationshipId; sheet.SheetId = sheetId; if (sheetName != "") { sheet.Name = sheetName; } else { sheet.Name = fileName; } WorksheetPart newWorksheetPart; newWorksheetPart = sheetPart.Worksheet.WorksheetPart; SheetProperties sp = new SheetProperties(new PageSetupProperties()); sheetPart.Worksheet.SheetProperties = sp; sheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true); DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup(); pgOr.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait; pgOr.FitToHeight = 3; pgOr.FitToWidth = 1; sheetPart.Worksheet.AppendChild(pgOr); sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); int rowCounter = 0; int columnCounter = 0; DocumentFormat.OpenXml.Spreadsheet.Row columnRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (DataColumn col in ((DataTable)gv.DataSource).Columns) { String cellValue = col.ColumnName; DocumentFormat.OpenXml.Spreadsheet.Cell cellCol = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cellCol.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cellCol.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); columnRow.AppendChild(cellCol); sbCSVString.Append(cellValue); columnCounter++; if (columnCounter < ((DataTable)gv.DataSource).Columns.Count) { sbCSVString.Append(","); } } sheetData.AppendChild(columnRow); sbCSVString.Append(Environment.NewLine); foreach (GridViewRow dataSourceRow in gv.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newSheetRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); //DataRow dataSourceRow = rowView.Row; string cellValue = dataSourceRow.Cells[0].Text.ToString(); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sbCSVString.Append(","); cellValue = dataSourceRow.Cells[1].Text.ToString(); cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sbCSVString.Append(","); cellValue = dataSourceRow.Cells[2].Text.ToString(); cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sbCSVString.Append(","); cellValue = dataSourceRow.Cells[3].Text.ToString(); cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sbCSVString.Append(","); cellValue = dataSourceRow.Cells[4].Text.ToString(); cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sbCSVString.Append(","); cellValue = dataSourceRow.Cells[5].Text.ToString(); cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); newSheetRow.AppendChild(cell); sbCSVString.Append(cellValue); sheetData.AppendChild(newSheetRow); sbCSVString.Append(Environment.NewLine); rowCounter = rowCounter + 1; } } File.WriteAllText(filePath.Replace("xlsx", "csv"), sbCSVString.ToString()); } catch (System.Exception ex) { string sError; sError = ex.ToString(); throw ex; } return(filePath); }
private void CreateExcelFile(DataTable table, string destination) { // hfFileName.Value = destination; // lblFileName.Text = string.Empty; using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); //foreach (System.Data.DataTable table in ds.Tables) //{ var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List <String> columns = new List <string>(); foreach (System.Data.DataColumn column in table.Columns) { columns.Add(column.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (System.Data.DataRow dsrow in table.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } //} } // btnDownloadExcel.Visible = true; // lblFileName.Text = "Servicing file created successfully"; }
public ActionResult DownloadStudentSubmittedProposalsSpreadsheet() { int courseId = 1; uint i = 2; string cellReference = "A2"; string fileName = "StudentProposalsSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx"; List <string> headerRow = new List <string>(); MemoryStream memoryStream = new MemoryStream(); DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); List <DocumentFormat.OpenXml.Spreadsheet.Font> fonts = new List <DocumentFormat.OpenXml.Spreadsheet.Font> { new DocumentFormat.OpenXml.Spreadsheet.Font(new Bold()), new DocumentFormat.OpenXml.Spreadsheet.Font() }; List <ForegroundColor> foregroundColors = new List <ForegroundColor>(); foregroundColors.Add(new ForegroundColor { Rgb = HexBinaryValue.FromString("FFD633") }); foregroundColors.Add(new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFFFF") }); List <BackgroundColor> backgroundColors = new List <BackgroundColor>(); backgroundColors.Add(new BackgroundColor { Indexed = 64 }); backgroundColors.Add(new BackgroundColor { Indexed = 64 }); List <DocumentFormat.OpenXml.Spreadsheet.Alignment> alignments = new List <DocumentFormat.OpenXml.Spreadsheet.Alignment>(); alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment()); alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment { WrapText = true, Vertical = VerticalAlignmentValues.Top }); SpreadsheetDocument spreadsheetDocument; Stylesheet stylesheet; stylesheet = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments); spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream); ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet); List <List <string> > spreadsheetData = _projectctx.GetSelfInitiatedProjectsSpreadsheetData(courseId); headerRow.AddRange(new string[] { "S/N", "Project Background", "Project Title", "Project Brief", "Technology Specification" }); sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1)); foreach (List <string> l in spreadsheetData) { sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2)); ++i; cellReference = "A" + i; } ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData); memoryStream.Seek(0, SeekOrigin.Begin); Response.Headers.Add("Content-Disposition", "attachement; filename=" + fileName); Response.Cookies.Append("completedDownloadToken", "downloaded", new CookieOptions() { Expires = DateTime.Now.AddSeconds(8) }); return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return(true); }
public async Task <bool> Export(IEnumerable <T> _list) { bool exported = false; string className = typeof(T).Name; this.fileName = string.Concat(System.IO.Path.GetFileNameWithoutExtension(this.fileName), "_", className.ToUpper(), System.IO.Path.GetExtension(this.fileName)); bool IsFileAvailable = await CheckIfFileCanBeUsed(this.fileName); if (IsFileAvailable) { PropertyInfo[] properties = typeof(T).GetProperties(); string sheetName = "SHEET " + className.ToUpper(); string randomPath = this.RandomPath; this.oldPath = randomPath; using (var workbook = SpreadsheetDocument.Create(randomPath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); WorkbookStylesPart workStylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); workStylePart.Stylesheet = GenerateStyleSheet(); var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); headerRow.Height = new DoubleValue() { Value = 32 }; foreach (PropertyInfo propertyInfo in properties) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(propertyInfo.Name.ToUpper()); cell.StyleIndex = 1; headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (T _obj in _list) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (PropertyInfo propertyInfo in properties) { DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; object propertyValue = propertyInfo.GetValue(_obj, null); string cellValue = (propertyValue != null ? propertyValue.ToString() : ""); cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue); cell.StyleIndex = 0; newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbook.Save(); exported = true; } } return(exported); }
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet, string dateFormat) { // Create the Excel file contents. This function is used when creating an Excel file either writing // to a file, or writing to a MemoryStream. spreadsheet.AddWorkbookPart(); spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) spreadsheet.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 = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); //Stylesheet stylesheet = new Stylesheet(); //workbookStylesPart.Stylesheet = stylesheet; //:format the worksheet CreateTableStyles(spreadsheet); // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint worksheetNumber = 1; foreach (DataTable dt in ds.Tables) { // For each worksheet you want to create string workSheetID = "rId" + worksheetNumber.ToString(); string worksheetName = dt.TableName; WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(); // create sheet data var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); // save worksheet WriteDataTableToExcelWorksheet(dt, sheetData, dateFormat); //autosize the column widths //get your columns (where your width is set) Columns columns = AutoSize(sheetData); //add to a WorksheetPart.WorkSheet newWorksheetPart.Worksheet = new Worksheet(); newWorksheetPart.Worksheet.Append(columns); newWorksheetPart.Worksheet.Append(sheetData); newWorksheetPart.Worksheet.Save(); // create the worksheet to workbook relation if (worksheetNumber == 1) { spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); } spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = (uint)worksheetNumber, Name = dt.TableName }); worksheetNumber++; } spreadsheet.WorkbookPart.Workbook.Save(); }