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();
}