public MemoryStream GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet() {
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1, Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers) {
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0) {
// Add the column configuration if available
if (data.ColumnConfigurations != null) {
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows) {
cellIdex = 0;
row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
foreach (var callData in rowData) {
var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return stream;
}
public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
{
MemoryStream stream = new MemoryStream();
UInt32 rowcount = 0;
// Create the Excel document
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
var worksheet = new Worksheet();
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var sheets = new Sheets();
var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
// Add header to the sheet
var row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
}
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
// Add data to the sheet
foreach (DataRow dataRow in data.Rows)
{
row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
}
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return stream;
}
public static void LoadSheetData(SheetData sheetData, DataTable data, int rowIndex, int columnindex)
{
//Populate data
Repository.Utility.WriteLog("LoadSheetData started", System.Diagnostics.EventLogEntryType.Information);
int startRow = rowIndex + 1;
for (int i = 0; i < data.Rows.Count; i++)
{
Row row = GetRow(sheetData, i + startRow);
if (row == null)
{
row = CreateContentRow(data.Rows[i], i + startRow, columnindex);
sheetData.AppendChild(row);
}
else
{
PopulateRow(data.Rows[i], i + 2, row);
}
}
// // Position the chart on the worksheet using a TwoCellAnchor object.
// drawingsPart.WorksheetDrawing = new WorksheetDrawing();
// TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<TwoCellAnchor>(new TwoCellAnchor());
// twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("1"),
// new RowId("2")
// ));
// twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("5"),
// new RowId(data.Rows.Count.ToString())
// ));
// twoCellAnchor.Append(new ClientData());
// // Save the WorksheetDrawing object.
// drawingsPart.WorksheetDrawing.Save();
Repository.Utility.WriteLog("LoadSheetData completed successfully", System.Diagnostics.EventLogEntryType.Information);
}
public static void GenerateReport(List<DataObject> objects)
{
SheetData data = new SheetData();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataObject obj in objects)
{
Cell headerCell = createTextCell(objects.IndexOf(obj) + 1, 1, obj.Name);
header.AppendChild(headerCell);
}
data.AppendChild(header);
SpreadsheetDocument doc = CreateDoc(data);
/*using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
///loop through each data row DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
} */
}
public static void LoadSheetData(SheetData sheetData, DataTable data, int rowIndex, int columnindex)
{
//Populate data
int startRow = rowIndex + 1;
for (int i = 0; i < data.Rows.Count; i++)
{
Row row = GetRow(sheetData, i + startRow);
if (row == null)
{
row = CreateContentRow(data.Rows[i], i + startRow, columnindex);
sheetData.AppendChild(row);
}
else
PopulateRow(data.Rows[i], columnindex, row);
}
}
public override void addDataRows(SheetData sheetData, DataTable dt)
{
// бежим по строкам
foreach (DataRow dataRow in dt.Rows)
{
var row = new Row();
for (int i = 0; i < dataRow.ItemArray.Length; i++)
{
var cell = new Cell();
var cellValue = new CellValue();
ApplyStyle(dataRow[i], RestMetadata.GetType(dt.Columns[i].ColumnName), ref cell, ref cellValue);
cell.Append(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
}
private void BtnExportarExcel(object sender, RoutedEventArgs e)
{
string Fecha = DateTime.Now.ToString("dd-MM-yyyy");
string FilePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "./Excel/" + Fecha + ".xlsx");
//string filetoOpen;
try
{
using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(FilePath,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbPart = spreedDoc.WorkbookPart;
if (wbPart == null)
{
wbPart = spreedDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
}
//Pagina 1
string sheetName = "Ventas al Crédito Pendientes";
WorksheetPart worksheetPart = null;
worksheetPart = wbPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
// Create custom widths for columns
Columns lstColumns = new Columns();
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 5, Width = 15, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 7, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 8, Max = 8, Width = 25, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 9, Max = 9, Width = 25, CustomWidth = true
});
worksheetPart.Worksheet = new Worksheet(lstColumns, sheetData);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
List <Venta> VentasPendientes = ViewModel.VentasList.Where(t => t.VentaCompletada == "No").ToList();
Row row1 = new Row();
row1.RowIndex = (UInt32)1;
row1.AppendChild(AddCellWithText("Nombre Cliente"));
row1.AppendChild(AddCellWithText("Cedula Cliente"));
row1.AppendChild(AddCellWithText("Empresa"));
row1.AppendChild(AddCellWithText("N# Pagos"));
row1.AppendChild(AddCellWithText("Fecha Ultimo Pago"));
row1.AppendChild(AddCellWithText("Monto Pagado a la fecha"));
row1.AppendChild(AddCellWithText("Saldo Pendiente"));
row1.AppendChild(AddCellWithText("Monto Total"));
row1.AppendChild(AddCellWithText("Productos Comprado"));
sheetData.AppendChild(row1);
int rowindex = 3;
foreach (var emp in VentasPendientes)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex;
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
double montoPago = 0;
if (emp.Pagos.Count() == 0)
{
row.AppendChild(AddCellWithText("0"));
row.AppendChild(AddCellWithText("No tiene"));
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
}
else
{
row.AppendChild(AddCellWithText(emp.Pagos.Count().ToString()));
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
foreach (var i in emp.Pagos)
{
montoPago = i.Monto;
}
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText((emp.MontoVenta - montoPago).ToString()));
}
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
}
sheetData.AppendChild(row);
rowindex++;
}
wbPart.Workbook.Sheets.AppendChild(sheet);
//Hoja 2
WorksheetPart worksheetPart2 = null;
worksheetPart2 = wbPart.AddNewPart <WorksheetPart>();
var sheetData2 = new SheetData();
// Create custom widths for columns
Columns lstColumns2 = new Columns();
lstColumns2.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 5, Max = 5, Width = 15, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 7, Max = 7, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 8, Max = 8, Width = 25, CustomWidth = true
});
lstColumns2.Append(new Column()
{
Min = 9, Max = 9, Width = 25, CustomWidth = true
});
worksheetPart2.Worksheet = new Worksheet(lstColumns2, sheetData2);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet2 = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Ventas al crédito completadas"
};
List <Venta> VentasCreditoCompletadas = ViewModel.VentasList.Where(t => t.VentaCompletada == "Si" && t.Tipo_Venta == "Crédito").ToList();
var workingSheet2 = ((WorksheetPart)wbPart.GetPartById(sheet2.Id)).Worksheet;
Row row2 = new Row();
row2.RowIndex = (UInt32)1;
row2.AppendChild(AddCellWithText("Nombre Cliente"));
row2.AppendChild(AddCellWithText("Cedula Cliente"));
row2.AppendChild(AddCellWithText("Empresa"));
row2.AppendChild(AddCellWithText("N# Pagos"));
row2.AppendChild(AddCellWithText("Fecha Ultimo Pago"));
row2.AppendChild(AddCellWithText("Monto Pagado a la fecha"));
row2.AppendChild(AddCellWithText("Saldo Pendiente"));
row2.AppendChild(AddCellWithText("Monto Pagado"));
row2.AppendChild(AddCellWithText("Productos Comprados"));
sheetData2.AppendChild(row2);
int rowindex2 = 3;
foreach (var emp in VentasCreditoCompletadas)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex2;
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
row.AppendChild(AddCellWithText(emp.Pagos.Count().ToString()));
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
double montoPago = 0;
foreach (var i in emp.Pagos)
{
montoPago = i.Monto;
}
row.AppendChild(AddCellWithText(montoPago.ToString()));
row.AppendChild(AddCellWithText((emp.MontoVenta - montoPago).ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
sheetData2.AppendChild(row);
rowindex2++;
}
wbPart.Workbook.Sheets.AppendChild(sheet2);
}
//Hoja 3
WorksheetPart worksheetPart3 = null;
worksheetPart3 = wbPart.AddNewPart <WorksheetPart>();
var sheetData3 = new SheetData();
// Create custom widths for columns
Columns lstColumns3 = new Columns();
lstColumns3.Append(new Column()
{
Min = 1, Max = 1, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 2, Max = 2, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 5, Max = 5, Width = 25, CustomWidth = true
});
lstColumns3.Append(new Column()
{
Min = 6, Max = 6, Width = 25, CustomWidth = true
});
worksheetPart3.Worksheet = new Worksheet(lstColumns3, sheetData3);
if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild <Sheets>(new Sheets());
}
var sheet3 = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart3),
SheetId = 3,
Name = "Ventas al Contado completadas"
};
List <Venta> VentasContadoCompletadas = ViewModel.VentasList.Where(t => t.VentaCompletada == "Si" && t.Tipo_Venta == "Contado").ToList();
var workingSheet3 = ((WorksheetPart)wbPart.GetPartById(sheet3.Id)).Worksheet;
Row row3 = new Row();
row3.RowIndex = (UInt32)1;
row3.AppendChild(AddCellWithText("Nombre Cliente"));
row3.AppendChild(AddCellWithText("Cedula Cliente"));
row3.AppendChild(AddCellWithText("Empresa"));
row3.AppendChild(AddCellWithText("Fecha Venta"));
row3.AppendChild(AddCellWithText("Monto Total"));
row3.AppendChild(AddCellWithText("Productos Comprados"));
sheetData3.AppendChild(row3);
int rowindex3 = 3;
foreach (var emp in VentasContadoCompletadas)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex3;
//Validando si esta venta fue minima y no tuvo cliente
if (emp.Cliente == null)
{
row.AppendChild(AddCellWithText("No fue Ingresado"));
row.AppendChild(AddCellWithText("No fue Ingresado"));
row.AppendChild(AddCellWithText("No fue Ingresado"));
}
else
{
row.AppendChild(AddCellWithText(emp.Cliente.Nombre));
row.AppendChild(AddCellWithText(emp.Cliente.Cedula));
row.AppendChild(AddCellWithText(emp.Cliente.Compania));
}
row.AppendChild(AddCellWithText(emp.Pagos.Last().Fecha_Pago.ToString()));
row.AppendChild(AddCellWithText(emp.MontoVenta.ToString()));
String productos = "";
if (emp.Especificaciones_producto.Count() == 0)
{
row.AppendChild(AddCellWithText("El producto no está registrado"));
}
else
{
var cantidadProductos = emp.Especificaciones_producto.Count();
//Mostramos los productos comprados
foreach (var i in emp.Especificaciones_producto)
{
if (cantidadProductos > 1)
{
productos = productos + i.Nombre + ", ";
}
else
{
productos = i.Nombre;
}
}
row.AppendChild(AddCellWithText(productos));
sheetData3.AppendChild(row);
rowindex3++;
}
}
wbPart.Workbook.Sheets.AppendChild(sheet3);
}
MessageBoxResult result = MessageBox.Show("Se ha generado correctamente la hoja de excel, con la fecha actual, en la la carpeta bin/release",
"Confirmation",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(FilePath);
}
catch (Exception err)
{
Console.WriteLine(err);
MessageBoxResult result = MessageBox.Show("Hubo un error al generar la hoja de Excel, revise la documentación por favor.",
"Confirmation",
MessageBoxButton.OK,
MessageBoxImage.Exclamation);
}
}
// exporting to excel MS Excel
public async void ExportToExcelAsync(ObservableCollection <User> list, string fileName)
{
await Task.Run(() =>
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Vk"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
OneNewMessage?.Invoke($"[{DateTime.Now.Hour}:{DateTime.Now.Minute}:{DateTime.Now.Second}] Експорт розпочато");
row.Append(
ConstructCell("Id", CellValues.String),
ConstructCell("Ім'я", CellValues.String),
ConstructCell("Прізвище", CellValues.String),
ConstructCell("Стать", CellValues.String),
ConstructCell("День народження", CellValues.String),
ConstructCell("Країна", CellValues.String),
ConstructCell("Місто", CellValues.String),
ConstructCell("Мобільний телефон", CellValues.String),
ConstructCell("Skype", CellValues.String),
ConstructCell("Instagram", CellValues.String));
sheetData.AppendChild(row);
int i = 0;
foreach (var user in list)
{
try
{
row = new Row();
row.Append(
ConstructCell(user.Id, CellValues.String),
ConstructCell(user.FirstName, CellValues.String),
ConstructCell(user.LastName, CellValues.String),
ConstructCell(user.Sex, CellValues.String),
ConstructCell(user.BDate, CellValues.String),
ConstructCell(user.Country, CellValues.String),
ConstructCell(user.City, CellValues.String),
ConstructCell(user.MobilePhone, CellValues.String),
ConstructCell(user.Skype, CellValues.String),
ConstructCell(user.Instagram, CellValues.String));
sheetData.AppendChild(row);
i++;
}
catch (Exception)
{
// ignored
}
finally
{
ProgressChaneged?.Invoke(i, list.Count);
}
}
worksheetPart.Worksheet.Save();
OneNewMessage?.Invoke($"[{DateTime.Now.Hour}:{DateTime.Now.Minute}:{DateTime.Now.Second}] Експорт завершено");
}
});
}
public bool ImportExcel(string strSiteid, string strSupplierID, string strSupplyType, string strNotificationId, string strNotification, string CategoryID, string Category, string strReadingInterval)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","SupplyTypeId","Supply Type","ReadFrequencyId","Read Frequency","MeterCategoryId","Meter Category","Reading Interval",
"Start Date","End Date","Meter Serial","Device ID","Meter Start Date","Meter End Date","Opening Read","Offset Value",
"Warrenty Date"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strSiteid, strSupplierID, strSupplyType, strNotificationId, strNotification, CategoryID, Category, strReadingInterval);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["SupplyTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["Supply Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["ReadFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("J", Convert.ToString(dt.Rows[i]["Read Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("K", Convert.ToString(dt.Rows[i]["MeterCategoryId"]), i + 2));
Subrow.AppendChild(CreateTextCell("L", Convert.ToString(dt.Rows[i]["Meter Category"]), i + 2));
Subrow.AppendChild(CreateTextCell("M", Convert.ToString(dt.Rows[i]["Reading Interval"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "MeterTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1, Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var cellIdex = 0;
Row row = null;
if (data.Headers.Count > 0)
{
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
public void WriteExcelProductWiseSaleFile(DataTable table, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
SheetData sheetData = new SheetData();
//add a row
Row firstRow = new Row();
//firstRow.RowIndex = (UInt32)1;
//create a cell in C1 (the upper left most cell of the merged cells)
//Cell dataCell = new Cell();
//dataCell.CellReference = "A1";
//dataCell.DataType = CellValues.String;
//CellValue cellValue = new CellValue();
//cellValue.Text = firstLine;
//dataCell.Append(cellValue);
//firstRow.AppendChild(dataCell);
//sheetData.AppendChild(firstRow);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//create a MergeCells class to hold each MergeCell
//MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
//mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:J1") });
//worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Productwise Sales Report"
});
Row headerRow = new Row();
List <String> columns = new List <string>();
//Hide Coulumn name//
foreach (System.Data.DataColumn column in table.Columns)
{
if (column.ColumnName == "Id" || column.ColumnName == "RowNumber" | column.ColumnName == "FoodMenuName")
{
}
else
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(null);// (column.ColumnName);
headerRow.AppendChild(cell);
}
}
//sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
if (dsrow[4].ToString().Contains("Total"))
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
else if (dsrow[0].ToString().Contains("=="))
{
}
else
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbookpart.Workbook.Save();
}
}
/*
* public static void DocxByCrud(ReadModel crud, JObject findJson, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
* {
* DocxByRows(new CrudRead(dbStr).GetAllRows(crud, findJson), docx, sheetName, headers, cols);
* }
*
* public static void DocxBySql(string sql, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
* {
* var rows = _Db.GetJsons(sql, null, dbStr);
* DocxByRows(rows, docx, sheetName, headers, cols);
* }
*/
/// <summary>
/// json rows to openXml object
/// see https://blog.johnwu.cc/article/asp-net-core-export-to-excel.html
/// </summary>
/// <param name="rows">json array</param>
/// <param name="docx"></param>
/// <param name="srcRowNo">資料開始列數, base 1, 如果為0表示沒有使用template file, 則此參數自動設為1</param>
/// <param name="sheetName">default 'Sheet1', excel save sheet name</param>
/// //<param name="template">excel template, 如果空白, 則欄位名稱會使用row field id</param>
/// //<param name="headers">excel欄位header, 如果空白, 則使用欄位id</param>
/// //<param name="cols">要輸出的excel欄位, 如果空白, 則全部輸出</param>
//public static void DocxByRows(JArray rows, SpreadsheetDocument docx, string template, string sheetName, List<string> headers = null, List<string> cols = null)
public static void DocxByRows(JArray rows, SpreadsheetDocument docx, int srcRowNo = 1)
{
//check
if (docx == null)
{
return;
}
//set cols
var rowCount = (rows == null) ? 0 : rows.Count;
var cols = new List <string>();
if (rowCount > 0)
{
foreach (var item in (JObject)rows[0])
{
cols.Add(item.Key);
}
}
//if (headers == null)
// headers = cols;
SheetData sheetData = null;
var hasTemplate = (srcRowNo > 0);
var colCount = cols.Count;
//Row srcRow = null;
//Row nowRow = null;
//Row newRow = null;
if (hasTemplate)
{
//使用範本
#region prepre excel-sheetData
var sheet = docx.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
var wsPart = (WorksheetPart)docx.WorkbookPart.GetPartById(sheet.Id);
//var ws = wsPart.Worksheet;
sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
#endregion
//var count = sheetData.Elements<Row>().Count();
//TODO: copy row style
//srcRow = sheetData.Elements<Row>().ElementAt(srcRowNo);
//寫入excel row, 使用範本
for (var rowNo = 0; rowNo < rowCount; rowNo++)
{
//第一筆時不必新增一列
var row = (JObject)rows[rowNo];
/*
* if (rowNo == 0)
* {
* nowRow = sheetData.Elements<Row>().ElementAt(srcRowNo);
* Cell cell;
* for (var colNo = 0; colNo < colCount; colNo++)
* {
* cell = nowRow.Elements<Cell>().ElementAt(colNo);
* cell.CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString());
* cell.DataType = CellValues.String;
* }
* }
* else
* {
*/
//新增一列 & 填入欄位
//TODO: copy row style
var newRow = new Row();
for (var colNo = 0; colNo < colCount; colNo++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
DataType = CellValues.String,
});
}
sheetData.InsertAt(newRow, rowNo + srcRowNo);
//}
}
}
else
{
//沒有範本
#region prepre excel-sheetData
var bookPart = docx.AddWorkbookPart();
bookPart.Workbook = new Workbook();
var sheetPart = bookPart.AddNewPart <WorksheetPart>();
sheetPart.Worksheet = new Worksheet(new SheetData());
var sheets = bookPart.Workbook.AppendChild(new Sheets());
sheets.Append(new Sheet()
{
Id = bookPart.GetIdOfPart(sheetPart),
SheetId = 1,
Name = "Sheet1",
});
sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
#endregion
//add header row
//srcRowNo = 1; //base 1
var newRow = new Row();
for (var i = 0; i < colCount; i++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(cols[i]),
DataType = CellValues.String,
});
}
sheetData.AppendChild(newRow);
//寫入excel row, 沒有範本
for (var rowNo = 0; rowNo < rowCount; rowNo++)
{
//var excelRow = NewRow(row, colCount, cols);
var row = (JObject)rows[rowNo];
newRow = new Row();
for (var colNo = 0; colNo < colCount; colNo++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
DataType = CellValues.String,
});
}
sheetData.AppendChild(newRow);
}
}
}
public void GenerateExcelFile(string listDefUrlOrID, string listDefItemId, string listOrgUrlOrID, string listOrgItemId, ResponseModel model)
{
try
{
string position1 = string.Empty;
string position2 = string.Empty;
string position3 = string.Empty;
string name1 = "_____________________";
string name2 = "_____________________";
string name3 = string.Empty;
string date1 = @"_____ _______________2020 г.";
string date2 = @"_____ _______________2020 г.";
string defId = string.Empty;
string defTitle = string.Empty;
string lookUpValue = string.Empty;
bool isLookUpNotNull = false;
var siteId = SPContext.Current.Site.ID;
var webId = SPContext.Current.Web.ID;
model.SiteId = siteId;
model.WebId = webId;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(siteId))
using (SPWeb web = site.OpenWeb(webId))
{
model.SiteUrl = site.Url;
model.WebUrl = web.Url;
Divisions division = new Divisions();
SPList defList = GetListByRelariveUrlOrId(web, listDefUrlOrID);
SPServiceContext serviceContext = SPServiceContext.GetContext(site);
UserProfileManager manager = new UserProfileManager(serviceContext);
if (defList != null)
{
model.DefListUrl = defList.DefaultViewUrl;
SPListItem defListItem = defList.GetItemById(Convert.ToInt32(listDefItemId));
if (defListItem != null)
{
SPFieldUserValue userEditor = new SPFieldUserValue(web, defListItem["Editor"].ToString());
SPUser spUser3 = userEditor.User;
if (spUser3 != null)
{
string[] temp = spUser3.Name.Split(' ');
name3 += temp[0] + " " + temp[1].Substring(0, 1) + " " + temp[2].Substring(0, 1);
UserProfile profile3 = manager.GetUserProfile(spUser3.LoginName);
position3 = TryGetProperty(profile3, "title");
}
defId = defListItem.ID.ToString();
defTitle = defListItem["Title"].ToString();
model.ItemUrl = defListItem.Url;
string jsonValue = defListItem["ListDataJSON"].ToString();
model.JsonValue = jsonValue;
division = JsonConvert.DeserializeObject <Divisions>(jsonValue);
var templookUp = defListItem["Условие стеснённости"];
if (templookUp != null)
{
SPFieldLookupValue lookupField = new SPFieldLookupValue(templookUp.ToString());
if (lookupField != null)
{
lookUpValue = lookupField.LookupValue;
isLookUpNotNull = true;
}
}
}
}
SPList orgList = GetListByRelariveUrlOrId(web, listOrgUrlOrID);
if (orgList != null)
{
SPListItem orgListItem = orgList.GetItemById(Convert.ToInt32(listOrgItemId));
SPFieldUserValue userValue1 = new SPFieldUserValue(web, orgListItem["fldApprove"].ToString());
SPUser spUser1 = userValue1.User;
if (spUser1 != null)
{
string[] temp = spUser1.Name.Split(' ');
name1 += temp[0] + " " + temp[1].Substring(0, 1) + " " + temp[2].Substring(0, 1);
UserProfile profile1 = manager.GetUserProfile(spUser1.LoginName);
position1 = TryGetProperty(profile1, "title");
}
SPFieldUserValue userValue2 = new SPFieldUserValue(web, orgListItem["fldConfirm"].ToString());
SPUser spUser2 = userValue2.User;
if (spUser2 != null)
{
string[] temp = spUser2.Name.Split(' ');
name2 += temp[0] + " " + temp[1].Substring(0, 1) + " " + temp[2].Substring(0, 1);
UserProfile profile2 = manager.GetUserProfile(spUser2.LoginName);
position2 = TryGetProperty(profile2, "title");
}
}
string tempFileUrl = SPUtility.GetCurrentGenericSetupPath(@"TEMPLATE\LAYOUTS\Sibur.SharePoint.DefList\Template\DefectiveListTemplate.xlsx");
model.FileExist = File.Exists(tempFileUrl);
// SPFile templateFile = web.GetFile(tempFileUrl);
// byte[] byteArray = templateFile.OpenBinary();
byte[] byteArray = File.ReadAllBytes(tempFileUrl);
model.ArrayLength = byteArray.Length;
using (MemoryStream memoryStream = new MemoryStream())
{
memoryStream.Write(byteArray, 0, byteArray.Length);
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(memoryStream, true))
{
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
Sheet sheet = spreadDocument.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
Worksheet worksheet = (spreadDocument.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
if (workBookPart.WorkbookStylesPart.Stylesheet.Descendants <StylesheetExtensionList>().Any())
{
workBookPart.WorkbookStylesPart.Stylesheet.RemoveAllChildren <StylesheetExtensionList>();
workBookPart.WorkbookStylesPart.Stylesheet.RemoveAllChildren <Stylesheet>();
}
WorkbookStylesPart workbookStylesPart = workBookPart.WorkbookStylesPart;
Stylesheet stylesheet1 = GenerateStylesheet();
workbookStylesPart.Stylesheet = stylesheet1;
Row row0 = new Row();
row0.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell("СОГЛАСОВАНО", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("УТВЕРЖДАЮ", CellValues.String, 5),
ConstructCell("", CellValues.String, 0));
Row row1 = new Row();
row1.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell(position1, CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell(position2, CellValues.String, 5),
ConstructCell("", CellValues.String, 0));
Row row2 = new Row();
row2.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell(name1, CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell(name2, CellValues.String, 5),
ConstructCell("", CellValues.String, 0));
Row row3 = new Row();
row3.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell(date1, CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell(date2, CellValues.String, 5),
ConstructCell("", CellValues.String, 0));
Row row35 = new Row();
row35.Append(
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1));
Row row4 = new Row();
row4.Append(
ConstructCell("", CellValues.String, 5),
ConstructCell("Ведомость объемов работ № " + defTitle, CellValues.String, 6),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1));
Row row5 = new Row();
row5.Append(
ConstructCell("", CellValues.String, 5),
ConstructCell("", CellValues.String, 6),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1),
ConstructCell("", CellValues.String, 1));
Row row6 = new Row();
row6.Append(
ConstructCell("", CellValues.String, 2),
ConstructCell("п/п", CellValues.String, 2),
ConstructCell("Наименование выполняемой работы", CellValues.String, 2),
ConstructCell("Ед.изм.", CellValues.String, 2),
ConstructCell("Кол-во", CellValues.String, 2),
ConstructCell("МТР", CellValues.String, 2),
ConstructCell("Примечание", CellValues.String, 2));
Row row7 = new Row();
row7.Append(
ConstructCell("", CellValues.String, 2),
ConstructCell("1", CellValues.Number, 2),
ConstructCell("2", CellValues.Number, 2),
ConstructCell("3", CellValues.Number, 2),
ConstructCell("4", CellValues.Number, 2),
ConstructCell("5", CellValues.Number, 2),
ConstructCell("6", CellValues.Number, 2));
sheetData.AppendChild(row0);
sheetData.AppendChild(row1);
sheetData.AppendChild(row2);
sheetData.AppendChild(row3);
sheetData.AppendChild(row35);
sheetData.AppendChild(row4);
sheetData.AppendChild(row5);
sheetData.AppendChild(row6);
sheetData.AppendChild(row7);
////////////////////////MERGE HEAD////////////////////////
MergeCells mergeCells;
if (worksheet.Elements <MergeCells>().Count() > 0)
{
mergeCells = worksheet.Elements <MergeCells>().First();
}
else
{
mergeCells = new MergeCells();
if (worksheet.Elements <CustomSheetView>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <CustomSheetView>().First());
}
else
{
var sss = worksheet.Elements <SheetData>().Count();
worksheet.InsertAfter(mergeCells, worksheet.Elements <SheetData>().First());
}
}
MergeCell mergeCell1 = new MergeCell()
{
Reference =
new StringValue("B2:C2")
};
MergeCell mergeCell2 = new MergeCell()
{
Reference =
new StringValue("B3:C3")
};
MergeCell mergeCell3 = new MergeCell()
{
Reference =
new StringValue("F2:G2")
};
MergeCell mergeCell4 = new MergeCell()
{
Reference =
new StringValue("F3:G3")
};
//
MergeCell mergeCell5 = new MergeCell()
{
Reference =
new StringValue("B4:C4")
};
MergeCell mergeCell6 = new MergeCell()
{
Reference =
new StringValue("B5:C5")
};
MergeCell mergeCell7 = new MergeCell()
{
Reference =
new StringValue("F4:G4")
};
MergeCell mergeCell8 = new MergeCell()
{
Reference =
new StringValue("F5:G5")
};
//
MergeCell mergeCell9 = new MergeCell()
{
Reference =
new StringValue("B7:G7")
};
mergeCells.Append(mergeCell1);
mergeCells.Append(mergeCell2);
mergeCells.Append(mergeCell3);
mergeCells.Append(mergeCell4);
mergeCells.Append(mergeCell5);
mergeCells.Append(mergeCell6);
mergeCells.Append(mergeCell7);
mergeCells.Append(mergeCell8);
mergeCells.Append(mergeCell9);
///////////////// TABLE BODY FROM 11 ROW INDEX /////////////////////////
uint rowIndex = 11;
int workId = 1;
foreach (Division div in division.divisions)
{
Row rowDiv = new Row();
rowDiv.Append(
ConstructCell("", CellValues.String, 2),
ConstructCell($"Раздел: {workId.ToString()} , {div.title}", CellValues.String, 2), // 19/03/2019
ConstructCell("", CellValues.String, 2),
ConstructCell("", CellValues.String, 2),
ConstructCell("", CellValues.String, 2),
ConstructCell("", CellValues.String, 2),
ConstructCell("", CellValues.String, 2));
sheetData.AppendChild(rowDiv);
MergeCell mergeCellDiv = new MergeCell()
{
Reference =
new StringValue("B" + rowIndex.ToString() + ":" + "G" + rowIndex.ToString())
};
mergeCells.Append(mergeCellDiv);
rowIndex++;
foreach (Work work in div.works)
{
Row rowWork = new Row();
rowWork.Append(
ConstructCell("", CellValues.String, 3),
ConstructCell(workId.ToString(), CellValues.Number, 3),
ConstructCell(work.title, CellValues.String, 3),
ConstructCell(work.units, CellValues.String, 3),
ConstructCell(work.count, CellValues.String, 3),
ConstructCell(work.mtp, CellValues.String, 3),
ConstructCell(work.comment, CellValues.String, 3));
sheetData.AppendChild(rowWork);
rowIndex++;
workId++;
}
}
Row rowEnd1 = new Row();
rowEnd1.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("Условия стеснённости:", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0));
sheetData.AppendChild(rowEnd1);
rowIndex++;
if (isLookUpNotNull)
{
Row rowEnd2 = new Row();
rowEnd2.Height = 80.0;
rowEnd2.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell(lookUpValue, CellValues.String, 7),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0));
sheetData.AppendChild(rowEnd2);
MergeCell mergeCellLookUp = new MergeCell()
{
Reference =
new StringValue("B" + rowIndex.ToString() + ":" + "G" + (rowIndex + 1).ToString())
};
mergeCells.Append(mergeCellLookUp);
}
Row rowEnd3 = new Row();
rowEnd3.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0));
sheetData.AppendChild(rowEnd3);
Row rowEnd4 = new Row();
rowEnd4.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0));
sheetData.AppendChild(rowEnd4);
Row rowEnd5 = new Row();
rowEnd5.Append(
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell(position3, CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell("", CellValues.String, 0),
ConstructCell(name3, CellValues.String, 0),
ConstructCell("", CellValues.String, 0));
sheetData.AppendChild(rowEnd5);
spreadDocument.WorkbookPart.Workbook.Save();
spreadDocument.Close();
defTitle = defTitle.Replace(".", "");
defTitle = defTitle.Replace("-", "");
defTitle = defTitle.Replace(":", "");
defTitle = defTitle.Replace("_", "");
defTitle = defTitle.Replace(" ", "");
string fileName = defTitle;
Encoding encoding = Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("Content-disposition", "attachment; filename*=UTF-8''" + HttpUtility.UrlEncode(fileName, encoding) + ".xlsx");
memoryStream.Position = 0;
byte[] arr = memoryStream.ToArray();
HttpContext.Current.Response.BinaryWrite(arr);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
memoryStream.Close();
}
}
});
}
catch (Exception ex)
{
model.Message = ex.Message;
Utilities.Log("Дефектная ведомость ", ex.ToString());
}
}
// private var userid = HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
// private System.Security.Claims.ClaimsPrincipal currentUser = this.User;
// private var wat = ClaimTypes.GivenName;
// private var watwat = User.FindFirst(ClaimTypes.GivenName).Value;
// //CreateSpreadsheetWorkbook("C:\\Users\\naylo\\Downloads\\New folder\\new.xlsx");
// private Excel1();
// return private View();
//}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
// worksheetPart.Worksheet = new Worksheet(sheetData);
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
// Open the document for editing.
//using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
//{
// // Insert other code here.
//}
sheets.Append(sheet);
string cs = "URI=file:CustomerDB.db";
string data = String.Empty;
int i = 0;
int j = 0;
using (SQLiteConnection con = new SQLiteConnection(cs))
{
con.Open();
string stm = "SELECT * FROM CustomerTB";
using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read()) // Reading Rows
{
Row newRow = new Row();
for (j = 0; j <= rdr.FieldCount - 1; j++) // Looping throw colums
{
data = rdr.GetValue(j).ToString();
//xlWorkSheet.Cells[i + 1, j + 1] = data;
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(data);
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
i++;
}
}
}
con.Close();
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
/// <summary>
/// Método para exportar a formato Excel una colección de objetos, a partir de un XML OpenXML.
/// </summary>
/// <typeparam name="T">Clase que define a la colección de objetos</typeparam>
/// <param name="_data">Colección de objetos tipo T a exportar</param>
/// <param name="_columnsToPrint">Arreglo de cadenas con los nombres de las propiedades a imprimir</param>
/// <param name="_dateTimeFormat">Representa el formato de impresión para propiedades del objeto tipo DateTime</param>
/// <param name="_printHeader">Indica si se imprime los títulos en la cabecera</param>
/// <param name="_sheetName">Nombre de la hoja activa del libro</param>
/// <returns>Regresa un Tuple con el archivo generado en el primer item
/// y un comentario en el segundo item</returns>
/// <remarks>El nombre de la columna se toma del atributo DisplatAttribute</remarks>
public Tuple <byte[], string> ExportToExcelOpenXml <T>(IEnumerable <T> _data, IEnumerable <string> _columnsToPrint = null, string _dateTimeFormat = "dd/MM/yyyy", bool _printHeader = true, string _sheetName = null) where T : class
{
try
{
if (_data == null || !_data.Any())
{
return(new Tuple <byte[], string>(null, String.Empty));
}
System.IO.MemoryStream stream = new System.IO.MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = _sheetName ?? Resources.DefaultSheetName
};
sheets.AppendChild(sheet);
AddStyleSheet(document);
PropertyInfo[] _properties = typeof(T).GetProperties();
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
bool _print;
if (_printHeader)
{
for (int _counter = 0; _counter < _properties.Length; _counter++)
{
_print = true;
if (_columnsToPrint != null && _columnsToPrint.Any())
{
_print = _columnsToPrint.Select(x => x.ToLowerInvariant())
.Contains(_properties[_counter].Name.ToLowerInvariant());
}
if (_print)
{
var _displayAttribute =
_properties[_counter].GetCustomAttributes(false)
.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _displayAttribute != null
? GetDisplayAttributeFromResourceType(_displayAttribute)
: _properties[_counter].Name, 1));
}
}
}
foreach (var x in _data)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
for (int _counter = 0; _counter < _properties.Length; _counter++)
{
_print = true;
if (_columnsToPrint != null && _columnsToPrint.Any())
{
_print = _columnsToPrint.Select(y => y.ToLowerInvariant())
.Contains(_properties[_counter].Name.ToLowerInvariant());
}
if (_print)
{
Cell cell;
var _value = _properties[_counter].GetValue(x);
switch (_value != null ? _value.GetType().ToString() : string.Empty)
{
case "":
case "System.String":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty);
break;
case "System.DateTime":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null
? ((DateTime)_value).ToString(_dateTimeFormat)
: string.Empty);
break;
case "System.Boolean":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? ((bool)_value ? ResourcesXMLExcel.TrueValue : ResourcesXMLExcel.FalseValue) : string.Empty);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
case "System.Decimal":
case "System.Double":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty);
break;
case "System.DBNull":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.NullObject);
break;
default:
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.DefaultTypeText);
break;
}
row.AppendChild(cell);
}
}
}
workbookpart.Workbook.Save();
document.Close();
return(new Tuple <byte[], string>(stream.ToArray(), null));
}
catch (Exception ex)
{
return(new Tuple <byte[], string>(null, Resources.ExceptionText + ex.Message));
}
}
private void CreateSettingsWorksheet(WorkbookPart workbookPart, int lookupCounter,
Dictionary <string, int> sharedStringIndices,
Dictionary <string, object[]> fieldDictionary)
{
string rId = "rId" + lookupCounter;
workbookPart.Workbook.Sheets.AppendChild(new Sheet
{
Name = "Settings",
SheetId = (UInt32Value)(lookupCounter + (0U)),
Id = rId,
State = SheetStateValues.Hidden
});
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(rId);
var worksheet = new Worksheet
{
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", R_SCHEMA);
worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var sheetProperties = new SheetProperties {
CodeName = "Sheet" + lookupCounter
};
var sheetDimension = new SheetDimension {
Reference = "A1"
};
var sheetData = new SheetData();
var row = new Row {
RowIndex = 1U, Spans = new ListValue <StringValue> {
InnerText = "1:1"
}, DyDescent = 0.25D
};
var cell = new Cell {
CellReference = "A1", DataType = CellValues.SharedString
};
var cellValue = new CellValue
{
Text =
sharedStringIndices[new Act(_spWeb).IsOnline.ToString()].ToString(
CultureInfo.InvariantCulture)
};
cell.AppendChild(cellValue);
row.AppendChild(cell);
sheetData.AppendChild(row);
int lRowIndex = 1;
int uRowIndex = 1;
foreach (var pair in fieldDictionary)
{
if (!((bool)pair.Value[3]))
{
continue;
}
int index = 0;
string col = "A";
switch ((SPFieldType)pair.Value[1])
{
case SPFieldType.User:
index = uRowIndex++;
col = "B";
break;
case SPFieldType.Lookup:
case SPFieldType.MultiChoice:
index = lRowIndex++;
col = "C";
break;
}
var r = new Row
{
RowIndex = (UInt32Value)(index + 0U),
Spans = new ListValue <StringValue> {
InnerText = "1:1"
},
DyDescent = 0.25D
};
var c = new Cell {
CellReference = col + index, DataType = CellValues.String
};
var cv = new CellValue
{
Text = pair.Key
};
c.AppendChild(cv);
r.AppendChild(c);
sheetData.AppendChild(r);
}
worksheet.Append(sheetProperties, sheetDimension, sheetData);
worksheetPart.Worksheet = worksheet;
}
private bool ExportToExcel(DataTable table, string sFilePath, string sSheetName = "")
{
try
{
SpreadsheetDocument workbook;
if (sSheetName == "")
{
sSheetName = table.TableName;
}
if (File.Exists(sFilePath))
{
workbook = SpreadsheetDocument.Open(sFilePath, true);
}
else
{
workbook = SpreadsheetDocument.Create(sFilePath, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
}
uint sheetId = 1;
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
Sheet oSheet = sheets.Elements <Sheet>().Where(s => s.Name == sSheetName).FirstOrDefault();
if (oSheet != null)
{
oSheet.Remove();
}
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sSheetName
};
sheets.Append(sheet);
Row headerRow = new Row();
List <string> columns = new List <string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbook.Close();
return(true);
}
catch (Exception ex)
{
if (ShowMessage)
{
Reporter.ToUser(eUserMsgKeys.ExportExcelFileFailed, ex.Message.ToString());
}
}
return(false);
}
private void CreateResourcesWorksheet(Dictionary <string, object[]> fieldDictionary, DataTable dataTable,
WorkbookPart workbookPart, Dictionary <string, int> sharedStringIndices)
{
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
var worksheet = new Worksheet(new SheetProperties {
CodeName = "Sheet1"
},
new SheetDimension
{
Reference =
string.Format("A1:{0}{1}",
GetColId(fieldDictionary.Count - 1),
dataTable.Rows.Count + 2)
}, new SheetViews(
new SheetView(
new Pane
{
VerticalSplit = 1D,
TopLeftCell = "A3",
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
},
new Selection
{
ActiveCell = "A2",
SequenceOfReferences =
new ListValue <StringValue> {
InnerText = "A2"
}
},
new Selection {
Pane = PaneValues.BottomLeft
})
{
TabSelected = true,
TopLeftCell = "A2",
WorkbookViewId = (UInt32Value)0U
}))
{
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", R_SCHEMA);
worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var columns = new Columns();
UInt32Value colIndex = 1U;
foreach (var pair in fieldDictionary)
{
var column = new Column
{
Min = colIndex,
Max = colIndex,
Width = 9.140625D,
Style = 1U,
BestFit = true,
CustomWidth = true
};
if (pair.Key.Equals("ID") || pair.Key.Equals("Title"))
{
column.Style = 5U;
}
if (pair.Key.Equals("SharePointAccount") && new Act(_spWeb).IsOnline)
{
column.Hidden = true;
}
columns.AppendChild(column);
colIndex++;
}
columns.AppendChild(new Column {
Min = colIndex, Max = 16384U, Width = 9.140625D, Style = 1U
});
var sheetData = new SheetData();
for (UInt32Value i = 1; i <= dataTable.Rows.Count + 2; i++)
{
var row = new Row {
RowIndex = i
};
if (i == 1)
{
row.Hidden = true;
}
else if (i == 2)
{
row.StyleIndex = 4U;
row.CustomFormat = true;
}
var rowId = (int)(i - 1);
for (int j = 0; j < fieldDictionary.Count; j++)
{
var cellValue = new CellValue();
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(j);
object oValue = null;
string value = string.Empty;
if (rowId > 1)
{
oValue = dataTable.Rows[rowId - 2][pair.Key];
if (pair.Key.Equals("ResourceLevel"))
{
foreach (var l in ((IList <string>)pair.Value[2])
.Select(level => level.Split('|'))
.Where(l => l[0].Equals(oValue.ToString())))
{
oValue = l[1];
break;
}
}
value = oValue.ToString();
}
switch (rowId)
{
case 0:
cellValue.Text = sharedStringIndices[pair.Key].ToString(CultureInfo.InvariantCulture);
break;
case 1:
cellValue.Text =
sharedStringIndices[pair.Value[0].ToString()].ToString(CultureInfo.InvariantCulture);
break;
default:
cellValue.Text = sharedStringIndices[value].ToString(CultureInfo.InvariantCulture);
break;
}
var cell = new Cell {
CellReference = GetColId(j) + i, StyleIndex = 1U
};
if (rowId > 1)
{
switch ((SPFieldType)pair.Value[1])
{
case SPFieldType.Counter:
case SPFieldType.Integer:
case SPFieldType.Number:
cellValue.Text = value;
break;
case SPFieldType.DateTime:
cell.StyleIndex = 3U;
cellValue.Text = oValue != null && oValue != DBNull.Value
? ((DateTime)oValue).ToOADate()
.ToString(CultureInfo.InvariantCulture)
: null;
break;
case SPFieldType.Currency:
cell.StyleIndex = 2U;
cellValue.Text = value;
break;
case SPFieldType.Boolean:
cell.DataType = CellValues.Boolean;
cellValue.Text = value;
break;
default:
cell.DataType = CellValues.SharedString;
break;
}
switch (pair.Key)
{
case "ID":
case "Title":
case "Generic":
case "SharePointAccount":
case "Email":
cell.StyleIndex = 5U;
break;
}
}
else
{
cell.DataType = CellValues.SharedString;
}
if (i == 2)
{
cell.StyleIndex = 4U;
}
cell.AppendChild(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
var worksheetExtensionList = new WorksheetExtensionList();
var worksheetExtension = new WorksheetExtension {
Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"
};
worksheetExtension.AddNamespaceDeclaration("x14", X14_SCHEMA);
var dataValidations = new X14.DataValidations {
Count = 0U
};
dataValidations.AddNamespaceDeclaration("xm", XM_SCHEMA);
for (int i = 0; i < fieldDictionary.Count; i++)
{
string colId = GetColId(i);
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(i);
var spFieldType = (SPFieldType)pair.Value[1];
if (spFieldType != SPFieldType.Boolean && spFieldType != SPFieldType.Lookup &&
spFieldType != SPFieldType.User && spFieldType != SPFieldType.Choice)
{
continue;
}
if (pair.Key.Equals("SharePointAccount"))
{
continue;
}
if ((bool)pair.Value[3])
{
continue;
}
dataValidations.Count++;
var dataValidation = new X14.DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
ShowInputMessage = true,
ShowErrorMessage = true
};
string formulaValue = string.Empty;
switch (spFieldType)
{
case SPFieldType.Boolean:
formulaValue = "BValues!$A$1:$A$2";
break;
case SPFieldType.Lookup:
case SPFieldType.Choice:
int count = ((List <string>)pair.Value[2]).Count();
formulaValue = pair.Key.Replace("_x0020_", "__") + "Values!$A$1" +
(count > 1 ? ":$A$" + count : string.Empty);
break;
case SPFieldType.User:
formulaValue = "DNValues!$D:$D";
break;
}
var dataValidationForumla = new X14.DataValidationForumla1();
var formula = new Formula {
Text = formulaValue
};
dataValidationForumla.AppendChild(formula);
var refSeq = new ReferenceSequence {
Text = string.Format("{0}3:{0}1048576", colId)
};
dataValidation.Append(dataValidationForumla, refSeq);
dataValidations.AppendChild(dataValidation);
}
worksheetExtension.AppendChild(dataValidations);
worksheetExtensionList.AppendChild(worksheetExtension);
worksheet.Append(columns, sheetData, worksheetExtensionList);
worksheetPart.Worksheet = worksheet;
}
private void CreateLookupValueSheets(Dictionary <string, object[]> fieldDictionary, WorkbookPart workbookPart,
Dictionary <string, int> sharedStringIndices)
{
int lookupCounter = 4;
foreach (var pair in fieldDictionary)
{
string key = pair.Key;
var spFieldType = (SPFieldType)pair.Value[1];
if (spFieldType != SPFieldType.Lookup &&
spFieldType != SPFieldType.Choice &&
spFieldType != SPFieldType.MultiChoice && !key.Equals("ResourceLevel"))
{
continue;
}
key = key.Replace("_x0020_", "__");
string id = "rId" + lookupCounter;
workbookPart.Workbook.Sheets.AppendChild(new Sheet
{
Name = key + "Values",
SheetId = (UInt32Value)(lookupCounter + (0U)),
Id = id,
State = SheetStateValues.Hidden
});
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(id);
var worksheet = new Worksheet
{
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", R_SCHEMA);
worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var lookups = (List <string>)pair.Value[2];
int lookupCount = lookups.Count();
var sheetProperties = new SheetProperties {
CodeName = "Sheet" + lookupCounter
};
var sheetDimension = new SheetDimension {
Reference = lookupCount > 0 ? "A1:A" + lookupCount : "A1"
};
var sheetData = new SheetData();
int counter = 0;
foreach (string lookupValue in from lookup in lookups select lookup.Split('|')[1])
{
counter++;
sheetData.AppendChild(new Row(new Cell(
new CellValue(
sharedStringIndices[lookupValue]
.ToString(CultureInfo.InvariantCulture)))
{
CellReference = "A" + counter,
DataType = CellValues.SharedString
})
{
RowIndex = (UInt32Value)(counter + (0U))
});
}
worksheet.Append(sheetProperties, sheetDimension, sheetData);
worksheetPart.Worksheet = worksheet;
lookupCounter++;
}
CreateDisplayNameWorksheet(workbookPart, lookupCounter);
CreateSettingsWorksheet(workbookPart, lookupCounter + 1, sharedStringIndices, fieldDictionary);
}
public void WriteExcelModeOfPaymentFile(DataTable table, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
SheetData sheetData = new SheetData();
//add a row
Row firstRow = new Row();
//firstRow.RowIndex = (UInt32)1;
//create a cell in C1 (the upper left most cell of the merged cells)
Cell dataCell = new Cell();
dataCell.CellReference = "A1";
dataCell.DataType = CellValues.String;
CellValue cellValue = new CellValue();
cellValue.Text = firstLine;
dataCell.Append(cellValue);
firstRow.AppendChild(dataCell);
sheetData.AppendChild(firstRow);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell()
{
Reference = new StringValue("A1:J1")
});
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
Row headerRow = new Row();
List <String> columns = new List <string>();
//skip//
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == " SALES" || col == "CASH" || col == "M-PESA" || col == "CREDIT CARD" || col == "DEBIT CARD" || col == "CHQEUE")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
}
}
public static void CreateServiceReport(this DataTable dataTable, string filepath, List <DateTime> dates)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var sheetData = new SheetData();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
Row firstRow = new Row();
Cell headerCell = new Cell();
headerCell.DataType = CellValues.String;
headerCell.CellValue = new CellValue("Период: " + dates.Min().ToShortDateString() + "-" + dates.Max().ToShortDateString());
firstRow.AppendChild(headerCell);
sheetData.AppendChild(firstRow);
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Гос.номер" || col == "Модель")
{
cell.DataType = CellValues.String;
}
else
{
cell.DataType = CellValues.Number;
}
cell.CellValue = new CellValue(dsrow[col].ToString().Replace(",", "."));
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
private byte[] Export <T>(IEnumerable <T> datas, Dictionary <string, string> headerDict)
where T : class, new()
{
byte[] result = null;
var ms = new MemoryStream();
var fileUrl = Path.Combine(Environment.CurrentDirectory, DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx");
using (var workbook = SpreadsheetDocument.Create(fileUrl, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)
)
{
var type = typeof(T);
var props = type.GetProperties();
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = "sheet1"
};
sheets.Append(sheet);
Row headerRow = new Row();
List <string> columns = new List <string>();
foreach (var kvp in headerDict)
{
columns.Add(kvp.Value);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(kvp.Value);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (var item in datas)
{
Row newRow = new Row();
foreach (string col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
var propName = headerDict.FirstOrDefault(x => x.Value == col).Key;
var prop = props.FirstOrDefault(x => x.Name == propName);
cell.CellValue = new CellValue(
prop.GetValue(item).ToString()
);
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
//var options = new ParallelOptions()
//{
// MaxDegreeOfParallelism = 8,
//};
//ConcurrentBag<Row> rows = new ConcurrentBag<Row>();
//Parallel.ForEach(datas, options,data => {
// Row newRow = new Row();
// foreach (string col in columns)
// {
// Cell cell = new Cell();
// cell.DataType = CellValues.String;
// var propName = headerDict.FirstOrDefault(x => x.Value == col).Key;
// var prop = props.FirstOrDefault(x => x.Name == propName);
// cell.CellValue = new CellValue(
// col
// );
// newRow.AppendChild(cell);
// }
// rows.Add(newRow);
//});
//sheetData.Append(rows);
}
result = File.ReadAllBytes(fileUrl);
try
{
var tf = new TaskFactory();
tf.StartNew(() =>
{
File.Delete(fileUrl);
});
}
catch
{
}
return(result);
}
/// <summary>
/// This method exports datatable to a excel file
/// </summary>
/// <param name="table">DataTable</param>
/// <param name="exportFile">Excel file name</param>
private static void ExportDataTableToExcel(DataTable table, string exportFile)
{
try
{
// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(exportFile, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
SheetData data = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void SetDataRows(SheetData sheetData, IEnumerable<Wrestler> wrestlers)
{
foreach (var wrestler in wrestlers) {
var row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue(wrestler.Name)
});
row.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue(wrestler.Finisher)
});
row.AppendChild(new Cell {
DataType = CellValues.Number,
CellValue = new CellValue(DateTime.Now.ToOADate().ToString(CultureInfo.InvariantCulture)),
StyleIndex = 7
});
row.AppendChild(new Cell {
DataType = CellValues.Number,
CellValue = new CellValue(wrestler.Age.ToString(CultureInfo.InvariantCulture))
});
row.AppendChild(new Cell {
DataType = CellValues.Boolean,
CellValue = new CellValue(wrestler.Alive.ToString().ToLower())
});
}
}
public static SpreadsheetDocument createSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document (A SpreadsheetDocument must have at least a WorkbookPart and a WorkSheetPart.)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart (A SpreadsheetDocument must have at least a WorkbookPart and a WorkSheetPart.)
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// The Worksheet will contain SheetData and Columns. It is the Sheet data which the actual values
// goes in rows and cells. By initializing the Worksheet we can append a SheetData as its child
// by passing it as argument.
// Append a “Sheets” to the Workbook. The Sheets will contain one or many Sheet which each associated
// with a WorksheetPart.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Hello, World!"
};
sheets.Append(sheet);
// Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet:
WorkbookStylesPart stylePart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
// Adding custom width to specific columns is very easy. First we need to create a Columns
// object and then add one or more Column as its children which each will define the custom
// width of a range of columns in the spreadsheet. You can explore the properties of the
// column to specify more customization on columns. Here we are only interested in specifying
// the width of the columns.
Columns columns = new Columns(
new Column // Id column
{
Min = 1,
Max = 1,
Width = 4,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 2,
Max = 3,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
// At the end save the Workbook.
workbookpart.Workbook.Save();
// Append a SheetData class to the worksheet. The SheetData acts as the container where all the rows and columns will be going.
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header row
Row headerRow = new Row();
// Add cells to the header row
headerRow.Append(
ConstructCell("Id", CellValues.String, 2),
ConstructCell("Name", CellValues.String, 2),
ConstructCell("Birth Date", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(headerRow);
// Constructing data row
Row dataRow = new Row();
// Add cells to the data row
dataRow.Append(
ConstructCell("1", CellValues.String, 1),
ConstructCell("Oliver Ofenloch", CellValues.String, 1),
ConstructCell("2020-08-24", CellValues.String, 1));
// Insert the data row to the Sheet Data
sheetData.AppendChild(dataRow);
return(spreadsheetDocument);
} // public static SpreadsheetDocument createSpreadsheetWorkbook(string filepath)
public void ToExcelSheet(SheetData sheetData)
{
if (sheetData == null) throw new ArgumentNullException("sheetData");
this.ToExcelRows().ForEach(dataRow => sheetData.AppendChild(dataRow));
}
public bool ImportExcel(string strSiteid, string strNotificationId, string DepartmentId)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "BillingFrequencyId", "Billing Frequency"
, "ConsumerDepartmentId","Consumer Department","Network Name","Billing Upto","Billing Date","Fixed Bill Notice","Bank Name","Bank Sort Code",
"Bank Account Number","OIN / SUN Number","Manual Review of Bill","Contact Name","Telephone Number","Mobile Number","Email Address","Number of Domestic Properties",
"Number of Commercial Properties","Number of Bulk Supplies","End Date","Client Reference Number"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldType[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldType[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldType[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldType[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldType[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldType[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldType[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldType[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldType[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldType[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldType[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldType[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldType[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldType[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldType[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldType[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldType[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldType[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldType[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldType[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldType[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldType[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldType[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldType[23], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
//-------------------------Field Name----------------------------------
row.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 2));
row.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 2));
row.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 2));
row.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 2));
row.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 2));
row.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 2));
row.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 2));
row.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 2));
row.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 2));
row.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 2));
row.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 2));
row.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 2));
row.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 2));
row.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 2));
row.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 2));
row.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 2));
row.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 2));
row.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 2));
row.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 2));
row.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 2));
row.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 2));
row.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 2));
row.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 2));
row.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 2));
// Append Row to SheetData
sheetData.AppendChild(row);
DataTable dt = GetRecordFromSiteMaster(strSiteid, strNotificationId, DepartmentId);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 3));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 3));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["BillingFrequencyId"]), i + 3));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Billing Frequency"]), i + 3));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["ConsumerDepartmentId"]), i + 3));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Consumer Department"]), i + 3));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldType)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "SiteTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
//OverRide
public void saveXLS(String testName, DataSet ds)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException)
{
MessageBox.Show("Another Copy of the Document is open, Please close the document and retry export");
throw;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[35];
int k = 0;
//create and add header row.
Row headerRow = new Row();
//add headers for germline mutation export
string[] germlineHeaders = { "Chromosome", "Position", "Gene Name", "Ref", "Var", "Strand", "Ref Codon", "Var Codon", "Ref AA", "Var AA", "AA Name", "CDS Name", "Cosmic Details", "Shows", "History", "RefSNP", "Clinical Significance", "MAF", "Chromosome Sample Count", "Alleles", "Allepe pop %" };
foreach (string s in germlineHeaders)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
List<Mutation> mutationList = new List<Mutation>();//remove this
//create and add rows for each mutation.
//Here we go through ds
int tbllength = ds.Tables[0].Rows.Count;
String[] infoString = new String[21];
for (int rowNum = 0; rowNum < tbllength; rowNum++)//for each mutation m
{
Row newRow = new Row();
for (int j = 0; j < 21; j++)
{
infoString[j] = (string)ds.Tables[0].Rows[rowNum].ItemArray[j];
}
//string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!infoString[12].Equals("-----"))//index 12 is the Cosmic Name postion so
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
//if (longestWordPerColumn[i].Length < infoString[i].Length)
// longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 21; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
static void Main(string[] args)
{
string filePath = @"C:\Source\CSharp\Office\OpenXML\WriteSample.xlsx";
IList <UserDetails> persons = new List <UserDetails>()
{
new UserDetails()
{
ID = "1001", Name = "ABCD", City = "City1", Country = "USA"
},
new UserDetails()
{
ID = "1002", Name = "PQRS", City = "City2", Country = "INDIA"
},
new UserDetails()
{
ID = "1003", Name = "XYZZ", City = "City3", Country = "CHINA"
},
new UserDetails()
{
ID = "1004", Name = "LMNO", City = "City4", Country = "UK"
},
};
// Lets converts our object data to Datatable for a simplified logic.
// Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
Console.WriteLine($"Creating spreasheet {filePath}");
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
Console.WriteLine("Closing spreadsheet");
workbookPart.Workbook.Save();
}
Console.WriteLine("Press <enter> to end....");
Console.ReadLine();
}
public void WriteCessCategoryExcelFile(DataTable cessSummary, DataTable cessDetail, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Begin: Code block for Excel sheet 1 // Cess Summary
WorksheetPart worksheetPart1 = workbookpart.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
WorkbookStylesPart stylesPart1 = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart1.Stylesheet = GenerateStyleSheet();
stylesPart1.Stylesheet.Save();
// the data for sheet 1 // Cess Summary
Row firstRow1 = new Row();
Cell dataCell1 = new Cell();
dataCell1.CellReference = "A1";
dataCell1.DataType = CellValues.String;
CellValue cellValue1 = new CellValue();
cellValue1.Text = firstLine;
dataCell1.Append(cellValue1);
firstRow1.AppendChild(dataCell1);
sheetData1.AppendChild(firstRow1);
workSheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = workSheet1;
MergeCells mergeCells1 = new MergeCells();
mergeCells1.Append(new MergeCell()
{
Reference = new StringValue("A1:H1")
});
worksheetPart1.Worksheet.InsertAfter(mergeCells1, worksheetPart1.Worksheet.Elements <SheetData>().First());
Row headerRow1 = new Row();
List <String> columns1 = new List <string>();
foreach (System.Data.DataColumn column in cessSummary.Columns)
{
columns1.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow1.AppendChild(cell);
}
sheetData1.AppendChild(headerRow1);
foreach (DataRow dsrow1 in cessSummary.Rows)
{
Row newRow1 = new Row();
foreach (String col in columns1)
{
Cell cell = new Cell();
if (col == "NetSales" || col == "Vatable" || col == "NonVatable" || col == "TotalTax" || col == "GrandTotal" || col == "CateringLevy")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow1[col].ToString());
newRow1.AppendChild(cell);
}
sheetData1.AppendChild(newRow1);
}
Sheet sheets1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = "Summary"
};
sheets.Append(sheets1);
// End: Code block for Excel sheet 1 // Cess Summary
// Begin: Code block for Excel sheet 2 // Cess Details
WorksheetPart worksheetPart2 = workbookpart.AddNewPart <WorksheetPart>();
Worksheet workSheet2 = new Worksheet();
SheetData sheetData2 = new SheetData();
// the data for sheet 1 // Cess Details
Row firstRow2 = new Row();
Cell dataCell2 = new Cell();
dataCell2.CellReference = "A1";
dataCell2.DataType = CellValues.String;
CellValue cellValue2 = new CellValue();
cellValue2.Text = firstLine;
dataCell2.Append(cellValue2);
firstRow2.AppendChild(dataCell2);
sheetData2.AppendChild(firstRow2);
workSheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = workSheet2;
MergeCells mergeCells2 = new MergeCells();
mergeCells2.Append(new MergeCell()
{
Reference = new StringValue("A1:G1")
});
worksheetPart2.Worksheet.InsertAfter(mergeCells2, worksheetPart2.Worksheet.Elements <SheetData>().First());
Row headerRow2 = new Row();
List <String> columns2 = new List <string>();
foreach (System.Data.DataColumn column in cessDetail.Columns)
{
columns2.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow2.AppendChild(cell);
}
sheetData2.AppendChild(headerRow2);
foreach (DataRow dsrow2 in cessDetail.Rows)
{
Row newRow2 = new Row();
foreach (String col in columns2)
{
Cell cell = new Cell();
if (col == "NetSales" || col == "Vatable" || col == "NonVatable" || col == "TotalTax" || col == "GrandTotal" || col == "CateringLevy")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow2[col].ToString());
newRow2.AppendChild(cell);
}
sheetData2.AppendChild(newRow2);
}
Sheet sheets2 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Detail"
};
sheets.Append(sheets2);
// End: Code block for Excel sheet 1 // Cess Details
workbookpart.Workbook.Save();
}
}
private void SetHeader(SheetData sheetData)
{
var header = sheetData.AppendChild(new Row());
header.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue("Name"),
StyleIndex = 1
});
header.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue("Finisher"),
StyleIndex = 1
});
header.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue("Date of Birth"),
StyleIndex = 1
});
header.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue("Age"),
StyleIndex = 1
});
header.AppendChild(new Cell {
DataType = CellValues.String,
CellValue = new CellValue("Alive"),
StyleIndex = 1
});
}
static void Main(string[] args)
{
if (args.Length == 0)
{
ExitProgram("Please drag this program onto a .chart file to open it.");
}
string chartPath = args[0];
string chartFolder = chartPath.Substring(0, chartPath.LastIndexOf('\\'));
if (!chartPath.EndsWith(".chart"))
{
ExitProgram("You must open a chart file.");
}
if (!File.Exists(chartPath))
{
ExitProgram("File could not be found.");
}
Song song = new Song(chartPath);
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(chartFolder + "\\" + song.name + ".xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
if (workbookPart.Workbook.Sheets == null)
{
workbookPart.Workbook.AppendChild(new Sheets());
}
Sheet sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = song.name
};
var workingSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
uint rowIndex = 1;
foreach (Section section in song.sections)
{
Row row = new Row
{
RowIndex = rowIndex
};
if (rowIndex == 1) // Header
{
row.AppendChild(AddCellWithText("Section"));
row.AppendChild(AddCellWithText("FC"));
}
else // Data
{
row.AppendChild(AddCellWithText(section.title));
row.AppendChild(AddCellWithText("No"));
}
sheetData.AppendChild(row);
rowIndex++;
}
workbookPart.Workbook.Sheets.AppendChild(sheet);
workbookPart.Workbook.Save();
Console.WriteLine("Successfully created Spreadsheet file");
}
ExitProgram();
}
public static void CreateSpreadsheetWorkbook(this DataTable dataTable, string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var sheetData = new SheetData();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
protected virtual void GenWorksheetPart(WorksheetPart worksheetPart, DataTable dt)
{
if (worksheetPart == null) throw new ArgumentNullException("worksheetPart", "Параметр worksheetPart имеет значение null.");
if (dt == null) throw new ArgumentNullException("Таблица", "Параметр таблица имеет значение null.");
Worksheet workSheet = new Worksheet();
SheetData sheetData = new SheetData();
//записать в Excel названия полей
Row headerRow = new Row();
addHeaderRow(headerRow, dt);
sheetData.AppendChild(headerRow);
//записать в Excel строки с данными
addDataRows(sheetData, dt);
workSheet.Append(sheetData);
worksheetPart.Worksheet = workSheet;
}
//Main class function, export the mutationList to XLSX file, sets file name to testName.
public static void saveXLS(String testName, List<Mutation> mutationList)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException )
{
throw ;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[12];
int k = 0;
//create and add header row.
Row headerRow = new Row();
foreach (string s in Mutation.getHeaderForExport())
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
//create and add rows for each mutation.
foreach (Mutation m in mutationList)
{
Row newRow = new Row();
string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!m.CosmicName.Equals("-----"))
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
if (longestWordPerColumn[i].Length < infoString[i].Length)
longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 12; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
private string ExportToOxml(bool firstTime, string fileName, string RutaArchivos)
{
try
{
//Check if the file exists.
//if (firstTime)
//{
// Random rnd = new Random();
// fileName = "Reporte_" + DateTime.Now.ToString("yyyyMMddHHmmss") + rnd.Next(1000).ToString().PadLeft(4, Convert.ToChar("0")) + ".xlsx";
// while (System.IO.File.Exists(fileName))
// fileName = "Reporte_" + DateTime.Now.ToString("yyyyMMddHHmmss") + rnd.Next(1000).ToString().PadLeft(4, Convert.ToChar("0")) + ".xlsx";
//}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(System.IO.Path.Combine(RutaArchivos, fileName), SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Hoja" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(System.IO.Path.Combine(RutaArchivos, fileName), true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
{
workbookpart.Workbook = new Workbook();
}
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements <Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements <Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Hoja" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
return(fileName);
}
catch (Exception ex)
{
return("Error: Export=>" + ex.Message + ex.StackTrace);
}
}
public bool ImportExcel(string strNotificationFrequencyId, string strNotificationFrequency, string NotificationTime, string BillingCurrencyId, string strBillingCurrency, string ContractTypeId, string ContractType, string DepartmentId, string Department)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "NotificationFrequencyId", "Notification Frequency", "Notification Time", "BillingCurrencyId"
, "Billing Currency","ContractTypeId","Contract Type","ClientContactDepartmentId","Client Contact Department","Client Name","1st Line of the Address",
"2nd Line of the Address","City","County","Postcode","Company Number", "VAT Number","Client Bank","Sort Code","Client Bank A/c No","End Date",
"Client Contact Start Date","Client Contact First Name","Client Contact Last Name","Client Contact 1st Line of the Address","Client Contact 2nd Line of the Address",
"Client Contact City","Client Contact County","Client Contact Postcode","Client Contact Telephone Number","Client Contact Mobile Number","Client Contact Email Address"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
titleRow.AppendChild(CreateTextCell("Y", ListOfExportFieldName[24], 1));
titleRow.AppendChild(CreateTextCell("Z", ListOfExportFieldName[25], 1));
titleRow.AppendChild(CreateTextCell("AA",ListOfExportFieldName[26], 1));
titleRow.AppendChild(CreateTextCell("AB",ListOfExportFieldName[27], 1));
titleRow.AppendChild(CreateTextCell("AC",ListOfExportFieldName[28], 1));
titleRow.AppendChild(CreateTextCell("AD",ListOfExportFieldName[29], 1));
titleRow.AppendChild(CreateTextCell("AE",ListOfExportFieldName[30], 1));
titleRow.AppendChild(CreateTextCell("AF",ListOfExportFieldName[31], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strNotificationFrequencyId, strNotificationFrequency, NotificationTime, BillingCurrencyId, strBillingCurrency, ContractTypeId, ContractType, DepartmentId, Department);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["NotificationFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Notification Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["Notification Time"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["BillingCurrencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["Billing Currency"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["ContractTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["Contract Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["ClientContactDepartmentId"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["Client Contact Department"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "ClientTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
internal static void LoadGraphSheetData(SheetData sheetData, List<GraphDataGroup> data, int rowIndex, int columnIndex)
{
//Populate data
Repository.Utility.WriteLog("LoadSheetData started", System.Diagnostics.EventLogEntryType.Information);
int startRow = rowIndex + 1;
for (int i = 0; i < data.Count; i++)
{
for (int j = 0; j < data[i].Data.Count; j++)
{
Row row = GetRow(sheetData, j + startRow);
if (row == null)
{
row = CreateContentRow(data[i], j + startRow, columnIndex);
sheetData.AppendChild(row);
}
else
PopulateRow(data[i].Data[j], row, data[i].Type);
}
}
Repository.Utility.WriteLog("LoadSheetData completed successfully", System.Diagnostics.EventLogEntryType.Information);
}
public static void CreateSpreadsheetWorkbook(string filename, List <Statistics.Stats> stats)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
var spreadsheetDocument = SpreadsheetDocument.
Create(filename + ".xlsx", SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
var sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = filename
};
sheets.Append(sheet);
var row = new Row();
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(stats[0].Parameters["Algorithm"]),
});
sheetData.AppendChild(row);
row = new Row();
var row2 = new Row();
// Write header row 2,3
foreach (var key in stats[0].Parameters.Keys)
{
if (key != "Algorithm")
{
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(key)
});
row2.AppendChild(new Cell());
}
}
foreach (var key in stats[0].Output.Keys)
{
for (var i = 0; i < stats[0].Output[key].Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? key : "")
});
row2.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? "mean" : "std")
});
}
}
for (var i = 0; i < stats[0].Edges.Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? "Edges" : "")
});
row2.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? "mean" : "std")
});
}
foreach (var key in stats[0].Times.Keys)
{
for (var i = 0; i < stats[0].Times[key].Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? key : "")
});
row2.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? "mean" : "std")
});
}
}
var properties = stats[0].CliqueTrees[0].GetType().GetProperties();
foreach (var prop in properties)
{
for (var i = 0; i < stats[0].CliqueTrees.Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? prop.Name : "")
});
row2.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(i == 0 ? "mean" : "std")
});
}
}
sheetData.AppendChild(row);
sheetData.AppendChild(row2);
// now write data
var index = 0;
foreach (var s in stats)
{
row = new Row();
foreach (var item in s.Parameters)
{
if (item.Key != "Algorithm")
{
row.AppendChild(new Cell()
{
DataType = CellValues.Number,
CellValue = new CellValue(Convert.ToString(item.Value, System.Globalization.CultureInfo.InvariantCulture))
});
}
}
foreach (var item in s.Output)
{
for (var i = 0; i < item.Value.Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.Number,
CellValue = new CellValue(Convert.ToString(item.Value[i], System.Globalization.CultureInfo.InvariantCulture))
});
}
}
for (var i = 0; i < s.Edges.Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.Number,
CellValue = new CellValue(Convert.ToString(s.Edges[i], System.Globalization.CultureInfo.InvariantCulture))
});
}
foreach (var item in s.Times)
{
for (var i = 0; i < item.Value.Count; i++)
{
row.AppendChild(new Cell()
{
DataType = CellValues.Number,
CellValue = new CellValue(Convert.ToString(item.Value[i], System.Globalization.CultureInfo.InvariantCulture))
});
}
}
foreach (var prop in properties)
{
for (var i = 0; i < s.CliqueTrees.Count; i++)
{
var v = prop.GetValue(s.CliqueTrees[i]);
var isNumber = v is int || v is double;
row.AppendChild(new Cell()
{
DataType = isNumber ? CellValues.Number : CellValues.String,
CellValue = new CellValue(Convert.ToString(v, System.Globalization.CultureInfo.InvariantCulture))
});
}
}
sheetData.AppendChild(row);
index++;
}
workbookpart.Workbook.Save();
OpenXmlValidator validator = new OpenXmlValidator();
var errors = validator.Validate(spreadsheetDocument);
foreach (ValidationErrorInfo error in errors)
{
Console.Write(error.Description);
}
// Close the document.
spreadsheetDocument.Close();
}
private static void createExcelFile(string filePath, DataTable dt, string tableName = "")
{
SpreadsheetDocument doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
WorkbookPart wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
WorksheetPart wsPart = wbPart.AddNewPart <WorksheetPart>();
wsPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(wsPart),
SheetId = 1,
Name = tableName == "" ? dt.TableName : tableName
};
sheets.Append(sheet);
SheetData sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
Row headRow = new Row();
headRow.RowIndex = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell c = new Cell();
c.DataType = new EnumValue <CellValues>(CellValues.String);
c.CellReference = getColumnName(i + 1) + "1";
c.CellValue = new CellValue(dt.Columns[i].ColumnName);
headRow.AppendChild(c);
}
sheetData.AppendChild(headRow);
for (int i = 0; i < dt.Rows.Count; i++)
{
Row r = new Row();
r.RowIndex = (UInt32)i + 2;
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell c = new Cell();
c.DataType = new EnumValue <CellValues>(getCellType(dt.Columns[j].DataType));
c.CellReference = getColumnName(j + 1) + r.RowIndex.ToString();
if (c.DataType.Value == CellValues.Boolean)
{
string value = bool.Parse(dt.Rows[i][j].ToString()) ? "1" : "0";
c.CellValue = new CellValue(value);
}
else
{
c.CellValue = new CellValue(dt.Rows[i][j].ToString());
}
r.Append(c);
}
sheetData.Append(r);
}
wbPart.Workbook.Save();
doc.Close();
}
public static void ExportToOxml(bool firstTime, string filePath, DataTable resultsData)
{
//Delete the file if it exists.
if (firstTime && File.Exists(filePath))
{
File.Delete(filePath);
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filePath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements<Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
public byte[] CreaExcelScadenze(PrioritaDS ds, PrioritaDS dsAnagrafica)
{
byte[] content;
MemoryStream ms = new MemoryStream();
//string filename = @"c:\temp\mancanti.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 6;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 20;
c.CustomWidth = true;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Scadenze"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Barcode", CellValues.String, 2));
row.Append(ConstructCell("ODL", CellValues.String, 2));
row.Append(ConstructCell("Reparto", CellValues.String, 2));
row.Append(ConstructCell("Articolo", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Scadenza", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
List <string> IDPRDMOVFASE = ds.RW_SCADENZE.Select(x => x.IDPRDMOVFASE).Distinct().ToList();
foreach (string idMovFase in IDPRDMOVFASE)
{
PrioritaDS.USR_PRD_MOVFASIRow odl = ds.USR_PRD_MOVFASI.Where(x => x.IDPRDMOVFASE == idMovFase).FirstOrDefault();
PrioritaDS.MAGAZZRow articolo = dsAnagrafica.MAGAZZ.Where(x => x.IDMAGAZZ == odl.IDMAGAZZ).FirstOrDefault();
foreach (PrioritaDS.RW_SCADENZERow scadenza in ds.RW_SCADENZE.Where(x => x.IDPRDMOVFASE == idMovFase).OrderBy(x => x.DATA))
{
row = new Row();
row.Append(ConstructCell(odl.BARCODE, CellValues.String, 1));
row.Append(ConstructCell(odl.NUMMOVFASE, CellValues.String, 1));
row.Append(ConstructCell(odl.CODICECLIFO, CellValues.String, 1));
row.Append(ConstructCell(articolo.MODELLO, CellValues.String, 1));
row.Append(ConstructCell(scadenza.QTA.ToString(), CellValues.String, 1));
row.Append(ConstructCell(scadenza.DATA.ToShortDateString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public bool ImportExcel(string strPropbandid)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","First Name","Last Name","Start Date","Bank Name","Bank Sort Code","Correspondence Address",
"City","County","Post Code","E-mail Address","Mail Service","Telephone Number","Mobile Number","Managing Agent",
"Managing Telephone Number","Managing Email","Notes","Client Reference Number"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strPropbandid);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "PropertyTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
static void Main(string[] args)
{
//Get Template and create new file to edit
System.IO.File.Copy("Template.xlsx", "CustomersReport.xlsx", true);
//Generate Data
IEnumerable <Customer> reportData = Report.GetCustomers();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open("CustomersReport.xlsx", true))
{
WorkbookPart wBookPart = null;
wBookPart = spreadsheetDoc.WorkbookPart;
spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//Get worksheetpart
WorksheetPart wSheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First();
//Get existing workSheetPart
WorksheetPart newWorksheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First();
//add Styles
WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.WorkbookStylesPart;
//stylesPart.Stylesheet = Styles.GenerateStyleSheet(); //ya tiene una hoja de estilos
stylesPart.Stylesheet.Save();
string relationshipId = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Give the new worksheet a name.
Sheet sheet = new Sheet {
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Customer_Report"
};
sheets.Append(sheet);
//get existing sheetData
SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (Customer data in reportData)
{
Row contentRow = new Row();
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Name
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.RegisterDate
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.LastBuy
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Item
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.Quantity.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.ItemCost.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = string.Format("{0}", data.Quantity * data.ItemCost)
}
});
sheetData.AppendChild(contentRow);
}
}
}