// Generates content of workbookPart1.
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
private WorksheetPart wsSheet = null; // WorkSheetPart
#endregion Fields
#region Constructors
public LogGenerator(string fn)
{
//// get spreadsheet path from constructor
//path = folder;
//// File name is based on date and time
//DateTime now = DateTime.Now;
//// Construct the spreadsheet filename
//string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
// path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
if (File.Exists(fn))
{
doc = SpreadsheetDocument.Open(fn, true);
}
else
{
//
// Create the Spreadsheet document
//
doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
//
// Add WoorkBookPart to the document
//
wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add WorkSheetPart to the WorkBookPart
//
wsSheet = wbPart.AddNewPart<WorksheetPart>();
wsSheet.Worksheet = new Worksheet(new SheetData());
wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add sheets to the WorkBook
//
sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//
// Append new sheet and associate it with the workbook
//
sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheets.Append(sheet);
wbPart.Workbook.Save();
//CreatePackage(fn);
//if (File.Exists(fn))
//{
// TestXls();
//}
}
}
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 void Create(string fileName)
{
FileName = fileName;
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = doc.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// Create Styles
var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Style = new CustomStylesheet();
LoadCustomFonts();
LoadCustomBorders();
LoadCustomStyles();
Style.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
// create workbook and sheet
var workbook = new Workbook();
var worksheet = new Worksheet();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// create columns
var columns = new Columns();
CreateColumns(columns);
worksheet.Append(columns);
// create Sheet
var sheets = new Sheets();
var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
var sheetData = new SheetData();
LoadData(sheetData);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
doc.WorkbookPart.Workbook = workbook;
doc.WorkbookPart.Workbook.Save();
//doc.Close();
}
}
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 4U,
Name = _resources.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
/// <summary>
/// Creates the sheet
/// </summary>
/// <param name="sheets">The sheet for this item</param>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="workbookPart">The workbookpart associated with the spreadsheet</param>
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 3U, // <-- Change for each sheet that is created
Name = _groupOrder.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
public void CreateExcelReport <T>(string fileName, bool isHorizontalHead, T[] data)
{
using (var spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart =
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
?
spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
:
spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист11"
};
sheets.Append(sheet);
var fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance | BindingFlags.DeclaredOnly);
var fieldNumHead = 1;
foreach (var field in fields)
{
var column = this.GetExcelColumnName(isHorizontalHead ? fieldNumHead : 1);
var rowIndex = isHorizontalHead ? (uint)1 : (uint)fieldNumHead;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = column,
RowIndex = rowIndex,
Text = field.Name,
StyleIndex = 0U
});
fieldNumHead++;
}
var dataRowNum = 2;
foreach (var dataRow in data)
{
var fieldNum = 1;
foreach (var field in fields)
{
var column = string.Empty;
var rowIndex = 0U;
column = this.GetExcelColumnName(isHorizontalHead ? fieldNum : dataRowNum);
rowIndex = isHorizontalHead ? (uint)dataRowNum : (uint)fieldNum;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = column,
RowIndex = rowIndex,
Text = field.GetValue(dataRow).ToString(),
StyleIndex = 0U
});
fieldNum++;
}
dataRowNum++;
}
workbookpart.Workbook.Save();
}
}
/// <summary>
/// Generate service order report
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
{
if (itemsource.IsSelectedServiceOrder)
{
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// The SheetData object will contain all the data.
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
Form serviceOrder = itemsource.ServiceOrderData;
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[9];
for (int n = 0; n < 9; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 9; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:E2";
mergeCells.Append(mergeCell);
currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:E4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));
worksheet.Append(columns);
int rowIndex = 8;
Row sectionRow;
foreach (var section in serviceOrder.Sections)
{
sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
mergeCell = new MergeCell();
mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
mergeCells.Append(mergeCell);
AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
sheetData.Append(sectionRow);
foreach (var element in section.FormElements)
{
rowIndex++;
//The current row is obtained for updating the value of the cell
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
switch (element.Field.FieldType)
{
case FieldType.Catalogue:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
if (!string.IsNullOrEmpty(element.Field.FieldValue))
{
string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
}
else
{
AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
}
break;
case FieldType.RegularExpressionText:
case FieldType.Time:
case FieldType.SingleTextLine:
case FieldType.MultipleTextLine:
case FieldType.Datepicker:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
case FieldType.Boolean:
string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
break;
case FieldType.Integer:
case FieldType.Decimal:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
default:
break;
}
sheetData.Append(rowData);
}
rowIndex+=2;
}
worksheet.Append(sheetData);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" };
pivotCaches1.Append(pivotCache1);
workbook1.Append(sheets1);
workbook1.Append(pivotCaches1);
workbookPart1.Workbook = workbook1;
}
protected void ExcelDl_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Chart1.RenderControl(hw);
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";
WorkbookPart wbp = spreadsheetDocument.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();
// add contents
List<string> values = new List<string>();
//values.Add(lblSectionFunctionSelected.Text);
uint i = 1;
foreach (var value in values)
{
UInt32Value rowIndex = UInt32Value.FromUInt32(i);
var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sd.Append(row);
var cell = new Cell
{
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(CellValues.String)
};
row.InsertAt(cell, 0);
i++;
}
// add image
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
imgp.FeedData(fs);
}
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "Chart";
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill();
DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
blip.Embed = dp.GetIdOfPart(imgp);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
blipFill.Blip = blip;
blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
blipFill.Append(stretch);
DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
Bitmap bm = new Bitmap(fileName);
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
t2d.Extents = extents;
DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();
pos.X = 0;
pos.Y = 10;
Extent ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
AbsoluteAnchor anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
WorksheetDrawing wsd = new WorksheetDrawing();
wsd.Append(anchor);
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
ws.Append(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "history chart";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = wb;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
File.WriteAllBytes(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx", stream.ToArray());
}
Response.WriteFile(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx");
Response.Flush();
Response.Close();
Response.End();
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 240, YWindow = 45, WindowWidth = (UInt32Value)28320U, WindowHeight = (UInt32Value)12855U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "NOMBRE DE SOLAPA", SheetId = (UInt32Value)1U, Id = "rId1" };
switch (_Estado.ToUpper())
{
case "ORDENADO":
{
sheet1.Name = "O" + _oCabecera.AnoMes + " " + _oCabecera.IdentifEspacio;
break;
}
case "ESTIMADO":
{
sheet1.Name = "E" + _eCabecera.AnoMes + " V" + _eCabecera.Version + " " + _eCabecera.IdentifEspacio;
break;
}
case "CERTIFICADO":
{
sheet1.Name = "C" + _cCabecera.AnoMes + " " + _cCabecera.IdentifEspacio + _cCabecera.IdentifOrigen;
break;
}
}
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
public bool WriteDataCollectors(IEnumerable <DataCollector> dataCollectors, Stream stream)
{
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Create a sheet in the document
var data = new SheetData();
WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>();
worksheet.Worksheet = new Worksheet(data);
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Case Reports"
};
sheets.Append(sheet);
uint rowIndex = 0;
// Add some headers
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var headers = new SortedDictionary <string, string>
{
{ "A", "Full Name" },
{ "B", "Display Name" },
{ "C", "Year of birth" },
{ "D", "Sex" },
{ "E", "PreferredLanguage" },
{ "F", "Lat. / Long." },
{ "G", "Region" },
{ "H", "District" },
{ "I", "Village" },
{ "J", "Phonenumbers" },
{ "K", "Registered at" },
{ "L", "Last Report Recieved At" }
};
foreach (var header in headers)
{
var cell = new Cell {
CellReference = header.Key + rowIndex
};
row.Append(cell);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(header.Value);
}
}
// Insert data
foreach (var dataCollector in dataCollectors.OrderBy(e => e.RegisteredAt))
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var fullName = new Cell {
CellReference = "A" + rowIndex
};
row.Append(fullName);
fullName.DataType = new EnumValue <CellValues>(CellValues.String);
fullName.CellValue = new CellValue(dataCollector.FullName);
var displayName = new Cell {
CellReference = "B" + rowIndex
};
row.Append(displayName);
displayName.DataType = new EnumValue <CellValues>(CellValues.String);
displayName.CellValue = new CellValue(dataCollector.DisplayName);
var yearOfBirth = new Cell {
CellReference = "C" + rowIndex
};
row.Append(yearOfBirth);
yearOfBirth.DataType = new EnumValue <CellValues>(CellValues.Number);
yearOfBirth.CellValue = new CellValue(dataCollector.YearOfBirth.ToString());
var sex = new Cell {
CellReference = "D" + rowIndex
};
row.Append(sex);
sex.DataType = new EnumValue <CellValues>(CellValues.String);
sex.CellValue = new CellValue(Enum.GetName(typeof(Sex), dataCollector.Sex));
var preferredLanguage = new Cell {
CellReference = "E" + rowIndex
};
row.Append(preferredLanguage);
preferredLanguage.DataType = new EnumValue <CellValues>(CellValues.String);
preferredLanguage.CellValue = new CellValue(Enum.GetName(typeof(Language), dataCollector.PreferredLanguage));
var location = new Cell {
CellReference = "F" + rowIndex
};
row.Append(location);
location.DataType = new EnumValue <CellValues>(CellValues.String);
location.CellValue = new CellValue(dataCollector.Location.Latitude.ToString() + ", " + dataCollector.Location.Longitude.ToString());
var region = new Cell {
CellReference = "G" + rowIndex
};
row.Append(region);
region.DataType = new EnumValue <CellValues>(CellValues.String);
region.CellValue = new CellValue(dataCollector.Region ?? "Unknown");
var district = new Cell {
CellReference = "H" + rowIndex
};
row.Append(district);
district.DataType = new EnumValue <CellValues>(CellValues.String);
district.CellValue = new CellValue(dataCollector.District ?? "Unknown");
var village = new Cell {
CellReference = "I" + rowIndex
};
row.Append(village);
village.DataType = new EnumValue <CellValues>(CellValues.String);
village.CellValue = new CellValue(dataCollector.Village ?? "Unknown");
var phoneNumbers = new Cell {
CellReference = "J" + rowIndex
};
row.Append(phoneNumbers);
phoneNumbers.DataType = new EnumValue <CellValues>(CellValues.String);
phoneNumbers.CellValue = new CellValue(string.Join(", ", dataCollector.PhoneNumbers.Select(pn => pn.Value)));
var registeredAt = new Cell {
CellReference = "K" + rowIndex
};
row.Append(registeredAt);
registeredAt.DataType = new EnumValue <CellValues>(CellValues.Date);
registeredAt.CellValue = new CellValue(dataCollector.RegisteredAt);
var lastReportRecievedAt = new Cell {
CellReference = "L" + rowIndex
};
row.Append(lastReportRecievedAt);
lastReportRecievedAt.DataType = new EnumValue <CellValues>(CellValues.Date);
lastReportRecievedAt.CellValue = new CellValue(dataCollector.LastReportRecievedAt ?? dataCollector.RegisteredAt);
}
// Save the document in memory, and serve to client
workbook.Workbook.Save();
document.Close();
return(true);
}
public static void WriteExcelFile(string fileName, DataTable table, List <string> pallets)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
for (int i = 0; i < pallets.Count; i++)
{
UInt32Value c = 0;
c++;
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheet sheet = new Sheet()
{
Name = pallets[i]
};
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)
{
if (dsrow["PalletID"].ToString() == pallets[i])
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Weight")
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbookPart.Workbook.Save();
}
// workbookPart.Workbook.Save();
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)152511U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Slicer\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Slicer_Column1" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "Slicer_Column2" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "Slicer_Column3" };
definedName3.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{46BE6895-7355-4a93-B00E-2C351335B9C9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.SlicerCaches slicerCaches1 = new X15.SlicerCaches();
slicerCaches1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerCache slicerCache1 = new X14.SlicerCache(){ Id = "rId2" };
X14.SlicerCache slicerCache2 = new X14.SlicerCache(){ Id = "rId3" };
X14.SlicerCache slicerCache3 = new X14.SlicerCache(){ Id = "rId4" };
slicerCaches1.Append(slicerCache1);
slicerCaches1.Append(slicerCache2);
slicerCaches1.Append(slicerCache3);
workbookExtension2.Append(slicerCaches1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
sheets1.Append(sheet4);
sheets1.Append(sheet5);
sheets1.Append(sheet6);
sheets1.Append(sheet7);
sheets1.Append(sheet8);
sheets1.Append(sheet9);
sheets1.Append(sheet10);
sheets1.Append(sheet11);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
definedName3.Text = "#N/A";
DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
definedName4.Text = "#N/A";
DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
definedName5.Text = "#N/A";
DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
definedName6.Text = "#N/A";
DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
definedName7.Text = "#N/A";
DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
definedName8.Text = "#N/A";
DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
definedName9.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
definedNames1.Append(definedName4);
definedNames1.Append(definedName5);
definedNames1.Append(definedName6);
definedNames1.Append(definedName7);
definedNames1.Append(definedName8);
definedNames1.Append(definedName9);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };
pivotCaches1.Append(pivotCache1);
pivotCaches1.Append(pivotCache2);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences();
X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" };
X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" };
X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" };
X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" };
X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" };
X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" };
X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" };
X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" };
X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" };
timelineCacheReferences1.Append(timelineCacheReference1);
timelineCacheReferences1.Append(timelineCacheReference2);
timelineCacheReferences1.Append(timelineCacheReference3);
timelineCacheReferences1.Append(timelineCacheReference4);
timelineCacheReferences1.Append(timelineCacheReference5);
timelineCacheReferences1.Append(timelineCacheReference6);
timelineCacheReferences1.Append(timelineCacheReference7);
timelineCacheReferences1.Append(timelineCacheReference8);
timelineCacheReferences1.Append(timelineCacheReference9);
workbookExtension2.Append(timelineCacheReferences1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Generate an excel report dinamically
/// </summary>
/// <param name="model">Data source</param>
public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
{
MemoryStream report = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = model.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", model.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", model.FormName, currentRowTitle,5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < model.Captions.Count; i++)
{
var caption = model.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var item in model.DataRows)
{
int numberOfColumnsData = item.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the data information
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = item.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return report;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName1.Text = "Sheet1!$B$2:$I$13";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };
pivotCaches1.Append(pivotCache1);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.DataModel dataModel1 = new X15.DataModel();
X15.ModelTables modelTables1 = new X15.ModelTables();
X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };
modelTables1.Append(modelTable1);
dataModel1.Append(modelTables1);
workbookExtension1.Append(dataModel1);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };
OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
workbookExtension2.Append(openXmlUnknownElement1);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
public string ExportDetails(DataSet dataset, ExportFormat formatType, string filename)
{
if (dataset.Tables.Count < 2 && formatType == ExportFormat.CSV)
{
using (var writer = File.CreateText(filename))
{
WriteCSV(dataset.Tables[0], writer);
writer.Flush();
}
return(filename);
}
if (formatType == ExportFormat.CSV)
{
//need to write each query result to a separate file and zip up
if (System.IO.Path.GetExtension(filename) != "zip")
{
filename = Path.ChangeExtension(filename, "zip");
}
using (var filestream = new FileStream(filename, FileMode.Create))
using (var zipstream = new ICSharpCode.SharpZipLib.Zip.ZipOutputStream(filestream))
{
for (int i = 0; i < dataset.Tables.Count; i++)
{
var table = dataset.Tables[i];
string zipEntryFilename = Path.ChangeExtension(table.TableName, "csv");
var zipEntry = new ICSharpCode.SharpZipLib.Zip.ZipEntry(zipEntryFilename);
zipEntry.DateTime = DateTime.Now;
zipstream.PutNextEntry(zipEntry);
using (var writer = new StreamWriter(zipstream, Encoding.Default, 1024, true))
{
WriteCSV(table, writer);
writer.Flush();
}
}
}
return(filename);
}
else if (formatType == ExportFormat.Excel)
{
//need to write each query result to a separate tab, with the tab name the queryname
using (var filestream = File.Create(filename, 1024))
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(filestream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = doc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
doc.WorkbookPart.Workbook.Sheets = new Sheets();
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
var excelValidator = new Lpp.Utilities.Excel.ExcelEx();
for (uint sheetID = 1; sheetID <= dataset.Tables.Count; sheetID++)
{
var table = dataset.Tables[((int)sheetID) - 1];
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet {
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetID, Name = excelValidator.ValidateTabName(table.TableName)
};
sheets.Append(sheet);
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Row headerRow = new Row();
for (int i = 0; i < table.Columns.Count; i++)
{
headerRow.AppendChild(new Cell {
DataType = CellValues.String, CellValue = new CellValue(table.Columns[i].ColumnName)
});
}
sheetData.AppendChild(headerRow);
Row dataRow;
for (int j = 0; j < table.Rows.Count; j++)
{
dataRow = new Row();
var row = table.Rows[j];
for (int k = 0; k < row.ItemArray.Length; k++)
{
dataRow.AppendChild(new Cell {
DataType = CellValues.String, CellValue = new CellValue(row.ItemArray[k].ToStringEx())
});
}
sheetData.AppendChild(dataRow);
}
worksheetPart.Worksheet.Save();
}
}
return(filename);
}
else
{
throw new ArgumentOutOfRangeException(nameof(formatType), "formatType", "Invalid export format type value: " + formatType.ToString());
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\WorkBookPr\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties2 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension1.Append(workbookProperties2);
workbookExtensionList1.Append(workbookExtension1);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "F1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "№",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 2,
Text = "Тип мебели",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 2,
Text = "Название модели",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = 2,
Text = "Габариты",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = 2,
Text = "Кол-во",
StyleIndex = 0U
});
uint i = 1;
int sum = 0;
foreach (var model in info.Models)
{
sum += model.Key * model.Value.Price;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = i + 2,
Text = i.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = i + 2,
Text = model.Value.TypeName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = model.Value.ModelName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = i + 2,
Text = model.Value.Dimensions,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = i + 2,
Text = model.Value.Price.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "F",
RowIndex = i + 2,
Text = model.Key.ToString(),
StyleIndex = 0U
});
i++;
}
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = i + 2,
Text = "Итого:",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "E",
RowIndex = i + 2,
Text = sum.ToString(),
StyleIndex = 0U
});
workbookpart.Workbook.Save();
}
}
/// <summary>
/// Generate the report for catalogue categories
/// </summary>
/// <param name="templatePath">Path of the template</param>
/// <param name="itemSource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[2];
for (int n = 0; n < 2; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 2; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", LanguageResource.CatalogueValuesReport, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:B4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 9;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 11;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 12;
foreach (var item in itemSource.SearchResult.Collection)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public static void CreateSpreadsheetWorkbook(string filepath, DataTable dt, List <SQLBuilder.Clauses.Column> SelectedColumns)
{
FileInfo f = new FileInfo(filepath);
if (f.Exists)
{
f.Delete();
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
var stylesPart =
spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
string cl = "";
uint row = 2;
int index;
Cell cell;
foreach (DataRow dr in dt.Rows)
{
for (int idx = 0; idx < dt.Columns.Count; idx++)
{
if (idx >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((idx / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + idx % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + idx));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row - 1, worksheetPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(dr[idx]));
if (dr[idx] != null)
{
if (SelectedColumns.Count != 0)
{
string colFormat = SelectedColumns[idx].Format;
if (colFormat != null)
{
cell.StyleIndex = (UInt32)SQLBuilder.Common.ColumnFormat.Instance.getExcelColumnFormat(colFormat);
if (dr[idx].GetType() == typeof(string))
{
cell.DataType = CellValues.String;
}
else if (dr[idx].GetType() == typeof(bool))
{
cell.DataType = CellValues.Boolean;
}
else if (dr[idx].GetType() == typeof(DateTime))
{
cell.DataType = CellValues.Date;
}
else if (dr[idx].GetType() == typeof(decimal) ||
dr[idx].GetType() == typeof(double))
{
cell.DataType = CellValues.Number;
}
else if (dr[idx].GetType() == typeof(Int16) || dr[idx].GetType() == typeof(Int32) || dr[idx].GetType() == typeof(Int64))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
}
else
{
cell.StyleIndex = 8;
cell.DataType = CellValues.String;
}
}
}
}
row++;
}
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
//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();
}
public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) {
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up 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
},
new Column // Salary column
{
Min = 4,
Max = 4,
Width = 8,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
List <Employee> employees = Employees.EmployeesList;
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String, 2),
ConstructCell("Name", CellValues.String, 2),
ConstructCell("Birth Date", CellValues.String, 2),
ConstructCell("Salary", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var employee in employees)
{
row = new Row();
row.Append(
ConstructCell(employee.Id.ToString(), CellValues.Number, 2),
ConstructCell(employee.Name, CellValues.String, 2),
ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 3),
ConstructCell(employee.Salary.ToString(), CellValues.Number, 3));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
}
//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();
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
Sheets sheets1 = new Sheets();
sheets = sheets1;
Sheet sheet1 = new Sheet() { Name = "oi_th", SheetId = (UInt32Value)1U, Id = "R3c8458136e1d4b6c" };
sheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheet = sheet1;
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public byte[] CreaExcelFattureRitardate(FattureRitardateModel FattureRitardate)
{
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();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 40,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 20,
CustomWidth = true
},
new Column
{
Min = 4,
Max = 4,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 5,
Max = 5,
Width = 20,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Fatture Ritardate"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("ODL", CellValues.String, 2),
ConstructCell("Data Creazione", CellValues.String, 2),
ConstructCell("Utente Inserimento", CellValues.String, 2),
ConstructCell("Lavorante", CellValues.String, 2),
ConstructCell("Data Scadenza", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
foreach (FatturaRitardataModel FatturaRitardata in FattureRitardate.FattureRitardate)
{
row = new Row();
row.Append(
ConstructCell(FatturaRitardata.ODL, CellValues.String, 1),
ConstructCell(FatturaRitardata.DATA_CREAZIONE.ToShortDateString(), CellValues.String, 1),
ConstructCell(FatturaRitardata.UIDUSER_INSERIMENTO, CellValues.String, 1),
ConstructCell(FatturaRitardata.LAVORANTE, CellValues.String, 1),
ConstructCell(FatturaRitardata.DATA_SCADENZA.ToShortDateString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
/// <summary>
/// Generate an excel file with the list of users
/// </summary>
/// <param name="dataSource">The list of users</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public byte[] CreaExcelMagazziniEsterni(List <MagazzinoLavorantiEsterniModel> magazzini, string lavorante, string DataInizio, string DataFine)
{
byte[] content;
MemoryStream ms = new MemoryStream();
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();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 15,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column
{
Min = 4,
Max = 4,
Width = 15,
CustomWidth = false
},
new Column
{
Min = 5,
Max = 5,
Width = 30,
CustomWidth = true
},
new Column
{
Min = 6,
Max = 6,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 7,
Max = 7,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 8,
Max = 8,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 9,
Max = 9,
Width = 30,
CustomWidth = true
},
new Column
{
Min = 10,
Max = 10,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 11,
Max = 11,
Width = 10,
CustomWidth = true
},
new Column
{
Min = 12,
Max = 12,
Width = 10,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = lavorante
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row staticRow = new Row();
staticRow.Append(
ConstructCell("Dal", CellValues.String, 2),
ConstructCell(DataInizio, CellValues.String, 1),
ConstructCell("Al", CellValues.String, 2),
ConstructCell(DataFine, CellValues.String, 1));
sheetData.AppendChild(staticRow);
sheetData.AppendChild(new Row());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Azienda", CellValues.String, 2),
ConstructCell("ODL", CellValues.String, 2),
ConstructCell("Data inizio", CellValues.String, 2),
ConstructCell("Data fine", CellValues.String, 2),
ConstructCell("Modello", CellValues.String, 2),
ConstructCell("Descrizione", CellValues.String, 2),
ConstructCell("Quantità", CellValues.String, 2),
ConstructCell("Peso", CellValues.String, 2),
ConstructCell("Componente", CellValues.String, 2),
ConstructCell("Descrizione", CellValues.String, 2),
ConstructCell("Quantità", CellValues.String, 2),
ConstructCell("Peso", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (MagazzinoLavorantiEsterniModel elemento in magazzini)
{
row = new Row();
row.Append(
ConstructCell(elemento.Azienda, CellValues.String, 1),
ConstructCell(elemento.ODL, CellValues.String, 1),
ConstructCell(elemento.DataInizio, CellValues.String, 1),
ConstructCell(elemento.DataFine, CellValues.String, 1),
ConstructCell(elemento.Modello, CellValues.String, 1),
ConstructCell(elemento.ModelloDescrizione, CellValues.String, 1),
ConstructCell(elemento.Quanita.ToString(), CellValues.String, 1),
ConstructCell(elemento.Peso.ToString(), CellValues.String, 1),
ConstructCell(elemento.Componente, CellValues.String, 1),
ConstructCell(elemento.ComponenteDescrizione, CellValues.String, 1),
ConstructCell(elemento.QuanitaComponente.ToString(), CellValues.String, 1),
ConstructCell(elemento.PesoComponente.ToString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 600, YWindow = 105, WindowWidth = (UInt32Value)13995U, WindowHeight = (UInt32Value)8190U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Hoja1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
definedName1.Text = "Hoja1!$A$1:$AK$31";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
public byte[] CreaExcelGiacenzeMagazzino(List <ModelloGiacenzaModel> giacenze)
{
byte[] content;
MemoryStream ms = new MemoryStream();
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();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 35,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 60,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Giacenze"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Modello", CellValues.String, 2),
ConstructCell("Dettaglio", CellValues.String, 2),
ConstructCell("Giacenze", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (ModelloGiacenzaModel elemento in giacenze)
{
row = new Row();
row.Append(
ConstructCell(elemento.Modello, CellValues.String, 1),
ConstructCell(elemento.Descrizione, CellValues.String, 1),
ConstructCell(elemento.Giacenza, CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public static void WriteXlsx(string file, string[][] contents)
{
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(file, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var ssp = workbookpart.AddNewPart <SharedStringTablePart>();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
uint rowCount = 1;
//加入所有行
foreach (var contentRow in contents)
{
Row row = new Row {
RowIndex = rowCount, Spans = new ListValue <StringValue>()
};
row.Spans.Items.Add(new StringValue("1:" + contentRow.Length.ToString()));
for (int i = 0; i < contentRow.Length; i++)
{
if (contentRow[i] == null)
{
continue;
}
Cell cell = new Cell {
CellReference = XlsxFileReader.GetExcelColumnName(i + 1) + rowCount
};
if (contentRow[i].All(obj => Char.IsDigit(obj) || obj == '.'))
{
cell.CellValue = new CellValue(contentRow[i]);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
else
{
cell.CellValue = new CellValue(InsertSharedStringItem(contentRow[i], ssp).ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
row.AppendChild(cell);
}
sheetData.AppendChild(row);
rowCount++;
}
spreadsheetDocument.Close();
}
public byte[] CreaExcelPosizioneCampionario(List <PosizioneCampionarioModel> giacenze)
{
byte[] content;
MemoryStream ms = new MemoryStream();
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();
// Setting up columns
Columns columns = new Columns(
new Column
{
Min = 1,
Max = 1,
Width = 20,
CustomWidth = true
},
new Column
{
Min = 2,
Max = 2,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 3,
Max = 3,
Width = 20,
CustomWidth = false
},
new Column
{
Min = 4,
Max = 4,
Width = 70,
CustomWidth = true
},
new Column
{
Min = 5,
Max = 5,
Width = 15,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Campionario"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Campione", CellValues.String, 2),
ConstructCell("Posizione", CellValues.String, 2),
ConstructCell("Progressivo", CellValues.String, 2),
ConstructCell("Seriale", CellValues.String, 2),
ConstructCell("Cliente", CellValues.String, 2));
sheetData.AppendChild(row);
foreach (PosizioneCampionarioModel elemento in giacenze)
{
row = new Row();
row.Append(
ConstructCell(elemento.Campione, CellValues.String, 1),
ConstructCell(elemento.Posizione, CellValues.String, 1),
ConstructCell(elemento.Progressivo.ToString(), CellValues.String, 1),
ConstructCell(elemento.Seriale, CellValues.String, 1),
ConstructCell(elemento.Cliente, CellValues.String, 1));
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
public void CreateExcelFile(SpreadsheetDocument document)
{
// Add a WorkbookPart to the document
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
#region All columns
// Sheet 1 : All columns
WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart1.Worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheet sheet1 = new Sheet()
{
Name = "All columns",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1)
};
sheets.Append(sheet1);
// header
Row headerRow1 = new Row();
Cell nameHeader1 = new Cell()
{
CellValue = new CellValue("Name"), DataType = CellValues.String
};
headerRow1.AppendChild(nameHeader1);
Cell LXHeader1 = new Cell()
{
CellValue = new CellValue("LX (mm)"), DataType = CellValues.String
};
headerRow1.AppendChild(LXHeader1);
Cell LYHeader1 = new Cell()
{
CellValue = new CellValue("LY (mm)"), DataType = CellValues.String
};
headerRow1.AppendChild(LYHeader1);
Cell concreteHeader1 = new Cell()
{
CellValue = new CellValue("Concrete"), DataType = CellValues.String
};
headerRow1.AppendChild(concreteHeader1);
Cell NRebarXHeader1 = new Cell()
{
CellValue = new CellValue("Rebar X"), DataType = CellValues.String
};
headerRow1.AppendChild(NRebarXHeader1);
Cell NRebarYHeader1 = new Cell()
{
CellValue = new CellValue("Rebar Y"), DataType = CellValues.String
};
headerRow1.AppendChild(NRebarYHeader1);
Cell NRebarDiameter1 = new Cell()
{
CellValue = new CellValue("Diameter"), DataType = CellValues.String
};
headerRow1.AppendChild(NRebarDiameter1);
Cell SteelHeader1 = new Cell()
{
CellValue = new CellValue("Steel"), DataType = CellValues.String
};
headerRow1.AppendChild(SteelHeader1);
Cell coverHeader1 = new Cell()
{
CellValue = new CellValue("Cover (mm)"), DataType = CellValues.String
};
headerRow1.AppendChild(coverHeader1);
sheetData1.Append(headerRow1);
// data for sheet 1
List <Column> notClusterCol = columns.Where(c => !c.IsCluster).ToList();
for (int n = 0; n < notClusterCol.Count; n++)
{
Column col = notClusterCol[n];
Row rowCol = new Row();
Cell nameCell = new Cell();
nameCell.DataType = CellValues.String;
nameCell.CellValue = new CellValue(col.Name);
rowCol.AppendChild(nameCell);
Cell LXCell = new Cell();
LXCell.DataType = CellValues.Number;
LXCell.CellValue = new CellValue(col.LX.ToString());
rowCol.AppendChild(LXCell);
Cell LYCell = new Cell();
LYCell.DataType = CellValues.Number;
LYCell.CellValue = new CellValue(col.LY.ToString());
rowCol.AppendChild(LYCell);
Cell concreteCell = new Cell();
concreteCell.DataType = CellValues.String;
concreteCell.CellValue = new CellValue(col.ConcreteGrade.Name);
rowCol.AppendChild(concreteCell);
Cell NRebarXCell = new Cell();
NRebarXCell.DataType = CellValues.Number;
NRebarXCell.CellValue = new CellValue(col.NRebarX.ToString());
rowCol.AppendChild(NRebarXCell);
Cell NRebarYCell = new Cell();
NRebarYCell.DataType = CellValues.Number;
NRebarYCell.CellValue = new CellValue(col.NRebarY.ToString());
rowCol.AppendChild(NRebarYCell);
Cell NRebarDiameter = new Cell();
NRebarDiameter.DataType = CellValues.Number;
NRebarDiameter.CellValue = new CellValue(col.BarDiameter.ToString());
rowCol.AppendChild(NRebarDiameter);
Cell steelCell = new Cell();
steelCell.DataType = CellValues.String;
steelCell.CellValue = new CellValue(col.SteelGrade.Name);
rowCol.AppendChild(steelCell);
Cell coverCell = new Cell();
coverCell.DataType = CellValues.Number;
coverCell.CellValue = new CellValue(col.CoverToLinks.ToString());
rowCol.AppendChild(coverCell);
//Cell refCell = new Cell();
//refCell.DataType = CellValues.String;
//string refs = col.Name;
//if (col.IsCluster)
// refs = col.ColsInCluster.Aggregate((i, j) => i + ", " + j);
//refCell.CellValue = new CellValue(refs);
//rowCol.AppendChild(refCell);
sheetData1.AppendChild(rowCol);
}
worksheetPart1.Worksheet.AppendChild(sheetData1);
#endregion
#region Cluster columns
// Sheet 1 : All columns
WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart2.Worksheet = new Worksheet();
SheetData sheetData2 = new SheetData();
Sheet sheet2 = new Sheet()
{
Name = "Clusters",
SheetId = 2,
Id = document.WorkbookPart.GetIdOfPart(worksheetPart2)
};
sheets.Append(sheet2);
// header
Row headerRow2 = new Row();
Cell nameHeader2 = new Cell()
{
CellValue = new CellValue("Name"), DataType = CellValues.String
};
headerRow2.AppendChild(nameHeader2);
Cell LXHeader2 = new Cell()
{
CellValue = new CellValue("LX (mm)"), DataType = CellValues.String
};
headerRow2.AppendChild(LXHeader2);
Cell LYHeader2 = new Cell()
{
CellValue = new CellValue("LY (mm)"), DataType = CellValues.String
};
headerRow2.AppendChild(LYHeader2);
Cell concreteHeader2 = new Cell()
{
CellValue = new CellValue("Concrete"), DataType = CellValues.String
};
headerRow2.AppendChild(concreteHeader2);
Cell NRebarXHeader2 = new Cell()
{
CellValue = new CellValue("Rebar X"), DataType = CellValues.String
};
headerRow2.AppendChild(NRebarXHeader2);
Cell NRebarYHeader2 = new Cell()
{
CellValue = new CellValue("Rebar Y"), DataType = CellValues.String
};
headerRow2.AppendChild(NRebarYHeader2);
Cell NRebarDiameter2 = new Cell()
{
CellValue = new CellValue("Diameter"), DataType = CellValues.String
};
headerRow2.AppendChild(NRebarDiameter2);
Cell SteelHeader2 = new Cell()
{
CellValue = new CellValue("Steel"), DataType = CellValues.String
};
headerRow2.AppendChild(SteelHeader2);
Cell coverHeader2 = new Cell()
{
CellValue = new CellValue("Cover (mm)"), DataType = CellValues.String
};
headerRow2.AppendChild(coverHeader2);
sheetData2.Append(headerRow2);
// data for sheet 1
List <Column> clusterCol = columns.Where(c => c.IsCluster).ToList();
for (int n = 0; n < clusterCol.Count; n++)
{
Column col = clusterCol[n];
Row rowCol = new Row();
Cell nameCell = new Cell();
nameCell.DataType = CellValues.String;
nameCell.CellValue = new CellValue(col.Name);
rowCol.AppendChild(nameCell);
Cell LXCell = new Cell();
LXCell.DataType = CellValues.Number;
LXCell.CellValue = new CellValue(col.LX.ToString());
rowCol.AppendChild(LXCell);
Cell LYCell = new Cell();
LYCell.DataType = CellValues.Number;
LYCell.CellValue = new CellValue(col.LY.ToString());
rowCol.AppendChild(LYCell);
Cell concreteCell = new Cell();
concreteCell.DataType = CellValues.String;
concreteCell.CellValue = new CellValue(col.ConcreteGrade.Name);
rowCol.AppendChild(concreteCell);
Cell NRebarXCell = new Cell();
NRebarXCell.DataType = CellValues.Number;
NRebarXCell.CellValue = new CellValue(col.NRebarX.ToString());
rowCol.AppendChild(NRebarXCell);
Cell NRebarYCell = new Cell();
NRebarYCell.DataType = CellValues.Number;
NRebarYCell.CellValue = new CellValue(col.NRebarY.ToString());
rowCol.AppendChild(NRebarYCell);
Cell NRebarDiameter = new Cell();
NRebarDiameter.DataType = CellValues.Number;
NRebarDiameter.CellValue = new CellValue(col.BarDiameter.ToString());
rowCol.AppendChild(NRebarDiameter);
Cell steelCell = new Cell();
steelCell.DataType = CellValues.String;
steelCell.CellValue = new CellValue(col.SteelGrade.Name);
rowCol.AppendChild(steelCell);
Cell coverCell = new Cell();
coverCell.DataType = CellValues.Number;
coverCell.CellValue = new CellValue(col.CoverToLinks.ToString());
rowCol.AppendChild(coverCell);
Cell refCell = new Cell();
refCell.DataType = CellValues.String;
string refs = col.Name;
if (col.IsCluster)
{
refs = col.ColsInCluster.Aggregate((i, j) => i + ", " + j);
}
refCell.CellValue = new CellValue(refs);
rowCol.AppendChild(refCell);
sheetData2.AppendChild(rowCol);
}
worksheetPart2.Worksheet.AppendChild(sheetData2);
#endregion
#region Column positions
// Sheet 1 : All columns
WorksheetPart worksheetPart3 = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart3.Worksheet = new Worksheet();
SheetData sheetData3 = new SheetData();
Sheet sheet3 = new Sheet()
{
Name = "Positions",
SheetId = 3,
Id = document.WorkbookPart.GetIdOfPart(worksheetPart3)
};
sheets.Append(sheet3);
// header
Row headerRow3 = new Row();
Cell nameHeader = new Cell()
{
CellValue = new CellValue("Name"), DataType = CellValues.String
};
headerRow3.AppendChild(nameHeader);
Cell X0Header = new Cell()
{
CellValue = new CellValue("X0 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(X0Header);
Cell Y0Header = new Cell()
{
CellValue = new CellValue("Y0 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(Y0Header);
Cell Z0Header = new Cell()
{
CellValue = new CellValue("Z0 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(Z0Header);
Cell X1Header = new Cell()
{
CellValue = new CellValue("X1 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(X1Header);
Cell Y1Header = new Cell()
{
CellValue = new CellValue("Y1 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(Y1Header);
Cell Z1Header = new Cell()
{
CellValue = new CellValue("Z1 (mm)"), DataType = CellValues.String
};
headerRow3.AppendChild(Z1Header);
sheetData3.Append(headerRow3);
// data for sheet 3
for (int n = 0; n < notClusterCol.Count; n++)
{
Column col = notClusterCol[n];
Row rowCol = new Row();
Cell nameCell = new Cell();
nameCell.DataType = CellValues.String;
nameCell.CellValue = new CellValue(col.Name);
rowCol.AppendChild(nameCell);
Cell X0Cell = new Cell();
X0Cell.DataType = CellValues.Number;
X0Cell.CellValue = new CellValue(col.Point1.X.ToString());
rowCol.AppendChild(X0Cell);
Cell Y0Cell = new Cell();
Y0Cell.DataType = CellValues.Number;
Y0Cell.CellValue = new CellValue(col.Point1.Y.ToString());
rowCol.AppendChild(Y0Cell);
Cell Z0Cell = new Cell();
Z0Cell.DataType = CellValues.Number;
Z0Cell.CellValue = new CellValue(col.Point1.Z.ToString());
rowCol.AppendChild(Z0Cell);
Cell X1Cell = new Cell();
X1Cell.DataType = CellValues.Number;
X1Cell.CellValue = new CellValue(col.Point2.X.ToString());
rowCol.AppendChild(X1Cell);
Cell Y1Cell = new Cell();
Y1Cell.DataType = CellValues.Number;
Y1Cell.CellValue = new CellValue(col.Point2.Y.ToString());
rowCol.AppendChild(Y1Cell);
Cell Z1Cell = new Cell();
Z1Cell.DataType = CellValues.Number;
Z1Cell.CellValue = new CellValue(col.Point2.Z.ToString());
rowCol.AppendChild(Z1Cell);
sheetData3.AppendChild(rowCol);
}
worksheetPart3.Worksheet.AppendChild(sheetData3);
#endregion
workbookPart.Workbook.Save();
document.Close();
}
public async Task <string> GenerateExcel1(string fileName)
{
try
{
#region 择保存文件位置、类型
var savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.DocumentsLibrary;
// Dropdown of file types the user can save the file as
savePicker.FileTypeChoices.Add("Excel workbook(*.xlsx,*.xls)", new List <string>()
{
".xlsx", ".xls"
});
// Default file name if the user does not type one in or select a file to replace
savePicker.SuggestedFileName = fileName;
StorageFile file = await savePicker.PickSaveFileAsync();
if (file != null)
{
// Prevent updates to the remote version of the file until we finish making changes and call CompleteUpdatesAsync.
Windows.Storage.CachedFileManager.DeferUpdates(file);
var folder = await file.GetParentAsync();
// if (folder == null)
Windows.Storage.AccessCache.StorageApplicationPermissions.FutureAccessList.AddOrReplace("PickSaveFolderToken", folder);
#region 对应文件夹下新建一个excel文件
return(await Task <String> .Run(() =>
{
Task.Yield();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(file.Path, SpreadsheetDocumentType.Workbook, true);
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
return file.Path;
}));
#endregion
}
else
{
return("");
}
#endregion
}
catch (Exception ex)
{
await MainPage.ShowErrorMessage(ex.Message);
return(null);
}
}
public IExportOutputModel Export <T>(IExportInputModel <T> inputModel)
{
if (inputModel == null || !inputModel.IsValid())
{
_errorCollector.Errors.Add(BusinessErrors.InvalidInput);
return(null);
}
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(inputModel.SourceData), (typeof(DataTable)));
MemoryStream memoryStream = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
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 = inputModel.EntityName
};
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);
}
memoryStream.Seek(0, SeekOrigin.Begin);
}
var file = memoryStream.ToArray();
memoryStream.Dispose();
//"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
return(new ExportOutputModel
{
File = file
});
}
public void Test()
{
using (var workbook = SpreadsheetDocument.Create(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Test.xlsx"), DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
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 = "UnitTest"
};
sheets.Append(sheet);
Row headerRow = new Row();
var dataHelper = new DataHelper();
var data = dataHelper.GetData();
List <String> columns = new List <string>()
{
"Id", "albumId", "title", "Url", "thumbnailUrl"
};
foreach (var column in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
for (int i = 0; i < 1; i++)
{
int k = 0;
foreach (RandomData item in data)
{
if (++k == 5)
{
break;
}
Row newRow = new Row();
AddCell(newRow, item.Id);
AddCell(newRow, item.Field1);
AddCell(newRow, item.Field2);
AddCell(newRow, item.Field3);
AddCell(newRow, item.Field4);
AddCell(newRow, item.Field5);
AddCell(newRow, item.Field6);
AddCell(newRow, item.Field7);
AddCell(newRow, item.Field8);
AddCell(newRow, item.Field9);
AddCell(newRow, item.Field10);
AddCell(newRow, item.Field11);
AddCell(newRow, item.Field12);
AddCell(newRow, item.Field13);
AddCell(newRow, item.Field14);
AddCell(newRow, item.Field15);
AddCell(newRow, item.Field16);
AddCell(newRow, item.Field17);
AddCell(newRow, item.Field18);
AddCell(newRow, item.Field19);
sheetData.AppendChild(newRow);
}
}
sheetPart.Worksheet.Save();
}
}
public static void WriteExcelFile(string fileName, DataTable table)
{
// DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
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();
if (col == "Weight")
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
}
}
public FileResult ExportStonesReport(int companyId, string sCurrDate)
{
byte[] b;
sCurrDate = sCurrDate.Replace("'", "");
if (!DateTime.TryParse(sCurrDate, out DateTime curr))
{
curr = DateTime.Now.ToLocalTime();
}
string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}";
DCTSOpenXML oxl = new DCTSOpenXML();
StoneSorter stoneSorter = new StoneSorter();
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
// Build Excel File
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// declare locals
Row row;
Cell cell;
int rr;
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Stones"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sd = new SheetData();
// Build sheet
// Title
row = new Row();
cell = oxl.SetCellVal("A1", $"Export - Stones {currDate}");
row.Append(cell);
sd.Append(row);
row = new Row();
cell = oxl.SetCellVal("A2", "");
row.Append(cell);
sd.Append(row);
// Headers
row = new Row();
UInt32 cn = 1;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Id"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Name"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Title"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Stone"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Shape"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Vendor"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Carat"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Size"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Price"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Setting Cost"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Qty"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Parent Handle"); row.Append(cell); cn++;
oxl.columns.Append(new Column()
{
Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true
}); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Tags"); row.Append(cell); cn++;
worksheet.Append(oxl.columns);
sd.Append(row);
var stones = db.Stones.Where(v => v.CompanyId == companyId).Include("Vendor").Include("Shape");
List <Stone> Stones = stones.AsEnumerable()
.OrderBy(s => s.Name).ThenBy(s => s.Shape.Name).ThenBy(s => s.StoneSize, stoneSorter).ToList();
// Content
for (int i = 0; i < Stones.Count(); i++)
{
cn = 1;
row = new Row();
rr = 4 + i;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Id); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Label); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Title); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Name); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Shape.Name); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Vendor?.Name); row.Append(cell); cn++;
if (Stones[i].CtWt == null)
{
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), "");
}
else
{
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].CtWt.Value);
}
row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].StoneSize); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Price); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].SettingCost); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Qty); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].ParentHandle); row.Append(cell); cn++;
cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Tags); row.Append(cell); cn++;
sd.Append(row);
}
worksheet.Append(sd);
// Autofit columns - ss:AutoFitWidth="1"
worksheetPart.Worksheet = worksheet;
workbookPart.Workbook.Save();
document.Close();
b = memStream.ToArray();
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Stones as of " + $"{currDate}" + ".xlsx"));
}
}
}
public static void ExportAdvancedMode(Stream stream, List <ExcelSheet> excelSheets)
{
// La clase SpreadsheetDocument representa un paquete de documentos de Excel.
// Para crear un documento de Excel, cree una instancia de la clase SpreadsheetDocument y rellénela con partes.
// Como mínimo, el documento debe tener una parte de libro que sirva como contenedor para el documento y una parte de hoja de cálculo.
// Por defecto AutoSave = true, Editable = true, y Type = xlsx.
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
// Añadimos un elemenro de libro al documento (es obligatorio)
var wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
// Añadimos hojas al libro
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
foreach (var excelSheet in excelSheets)
{
var sheetName = excelSheet.SheetName.ToLower();
// START
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
// END START
#region DATA
#region Style configuration
// Intentamos obtener el estilo
WorkbookStylesPart styles = wbp.WorkbookStylesPart;
#region Estilos por defecto
if (styles == null)
{
// Creamos un nuevo estilo
styles = wbp.AddNewPart <WorkbookStylesPart>();
styles.Stylesheet = new Stylesheet();
// Build the formatted header style
styles.Stylesheet.Fonts = new Fonts {
Count = 1
};
styles.Stylesheet.Fonts.AppendChild(new Font()); // required, reserved by Excel
styles.Stylesheet.Fills = new Fills {
Count = 2
};
styles.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
styles.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
styles.Stylesheet.Borders = new Borders {
Count = 1
};
styles.Stylesheet.Borders.AppendChild(new Border()); // required, reserved by Excel
styles.Stylesheet.CellStyleFormats = new CellStyleFormats {
Count = 1
};
styles.Stylesheet.CellStyleFormats.AppendChild(new CellFormat()); // required, reserved by Excel
styles.Stylesheet.NumberingFormats = new NumberingFormats {
Count = 0
};
//styles.Stylesheet.NumberingFormats.AppendChild(new NumberingFormat {NumberFormatId = 0, FormatCode = "."}); // required, reserved by Excel
styles.Stylesheet.CellFormats = new CellFormats {
Count = 1
};
styles.Stylesheet.CellFormats.AppendChild(new CellFormat()); // required, reserved by Excel
}
#endregion
#region Estilo de cabecera
List <ExcelStyleRelation> styleHeaderRelation = new List <ExcelStyleRelation>();
if (excelSheet.HeaderStyles.Any())
{
foreach (ExcelStyleConfig styleConfig in excelSheet.HeaderStyles.Where(w => w.SheetNameStyle.Equals(sheetName, StringComparison.InvariantCultureIgnoreCase)))
{
// Convertimos todas las lineas a minusculas
styleConfig.SheetNameStyle = styleConfig.SheetNameStyle.ToLower();
styleConfig.HeaderName = styleConfig.HeaderName.ConvertAll(d => d.ToLower());
UInt32Value headerFontIndex = 0;
UInt32Value headerFillIndex = 0;
UInt32Value headerBorderIndex = 0;
UInt32Value headerStyleFormatIndex = 0;
UInt32Value headerStyleIndex = 0;
if (styleConfig.GetFont() != null)
{
headerFontIndex = CreateFont(styles.Stylesheet, styleConfig.GetFont());
}
if (styleConfig.GetFill() != null)
{
headerFillIndex = CreateFill(styles.Stylesheet, styleConfig.GetFill());
}
if (styleConfig.GetBorder() != null)
{
headerBorderIndex = CreateBorder(styles.Stylesheet, styleConfig.GetBorder());
}
headerStyleIndex = CreateCellFormat(styles.Stylesheet, headerStyleFormatIndex, headerFontIndex, headerBorderIndex, headerFillIndex, null, styleConfig.GetAlignment());
styleHeaderRelation.Add(new ExcelStyleRelation(styleConfig.SheetNameStyle, styleConfig.HeaderName, headerStyleIndex));
}
}
#endregion
#region Estilo de filas
var styleRowsRelation = new List <ExcelStyleRelation>();
if (excelSheet.RowsStyles.Any())
{
foreach (var styleConfig in excelSheet.RowsStyles.Where(w => w.SheetNameStyle.Equals(sheetName, StringComparison.InvariantCultureIgnoreCase)))
{
// Convertimos todas las lineas a minusculas
styleConfig.SheetNameStyle = styleConfig.SheetNameStyle.ToLower();
styleConfig.HeaderName = styleConfig.HeaderName.ConvertAll(d => d.ToLower());
UInt32Value rowFontIndex = 0;
UInt32Value rowFillIndex = 0;
UInt32Value rowBorderIndex = 0;
UInt32Value rowNumberFormatIndex = 0;
UInt32Value rowStyleFormatIndex = 0;
UInt32Value rowStyleIndex = 0;
if (styleConfig.GetFont() != null)
{
rowFontIndex = CreateFont(styles.Stylesheet, styleConfig.GetFont());
}
if (styleConfig.GetFill() != null)
{
rowFillIndex = CreateFill(styles.Stylesheet, styleConfig.GetFill());
}
if (styleConfig.GetBorder() != null)
{
rowBorderIndex = CreateBorder(styles.Stylesheet, styleConfig.GetBorder());
}
if (styleConfig.GetNumberingFormat() != null)
{
rowNumberFormatIndex = CreateNumberFormat(styles.Stylesheet, styleConfig.GetNumberingFormat());
}
rowStyleIndex = CreateCellFormat(styles.Stylesheet, rowStyleFormatIndex, rowFontIndex, rowBorderIndex, rowFillIndex, rowNumberFormatIndex, styleConfig.GetAlignment());
styleRowsRelation.Add(new ExcelStyleRelation(styleConfig.SheetNameStyle, styleConfig.HeaderName, rowStyleIndex, styleConfig.ApplyOnlyWhenValue));
}
}
#endregion
// Save styles
styles.Stylesheet.Save();
#endregion
#region Header data
// Generamos la línea de cabecera
var headerRow = new Row
{
RowIndex = (uint)1
};
int columnIndex = 1;
foreach (DataColumn column in excelSheet.Table.Columns)
{
// Comprobamos si vienen datos en la configuración de las columnas
var dataColumn = excelSheet.ColumnsConfiguration.FirstOrDefault(f => column.ColumnName.Equals(f.ColumnName, StringComparison.InvariantCultureIgnoreCase));
if (dataColumn == null)
{
dataColumn = new ExcelColumnConfig(column.ColumnName);
excelSheet.ColumnsConfiguration.Add(dataColumn);
}
// Si es visible, añadimos, si no, nos la saltamos, tanto aquí, como en las filas
if (dataColumn.ColumnVisible)
{
var cellHeader = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(dataColumn.ColumnDisplayName),
CellReference = GetColumnName(columnIndex) + 1,
};
#region Aplicación de estilos de cabecera
var headerStyleFound = styleHeaderRelation
.Where(s => s.ApplyStyleOnSheetName.Equals(sheetName) && (s.ApplyStyleOnColumns.Contains(column.ColumnName.ToLower()) || s.ApplyStyleOnColumns.Contains("*")))
.ToList();
if (headerStyleFound.Any())
{
if (headerStyleFound.Count == 1)
{
cellHeader.StyleIndex = headerStyleFound.Single().StyleIndex;
}
else
{
var relation = headerStyleFound.First(w => !w.ApplyStyleOnColumns.Contains("*"));
cellHeader.StyleIndex = relation.StyleIndex;
}
}
#endregion
headerRow.AppendChild(cellHeader);
columnIndex++;
}
}
// Añadimos la línea de cabecera a la hoja
sd.AppendChild(headerRow);
#endregion
#region Rows data
// Generamos cada una de las filas
foreach (DataRow row in excelSheet.Table.Rows)
{
// Creamos una nueva fila
var newRow = new Row();
// Para cada columna, vamos rellenando los datos
foreach (DataColumn col in excelSheet.Table.Columns)
{
// Comprobamos si vienen datos en la configuración de las columnas
var dataColumn = excelSheet.ColumnsConfiguration.First(f => col.ColumnName.ToLower().Equals(f.ColumnName.ToLower()));
// Si es visible, añadimos, si no, nos la saltamos, tanto aquí, como en las filas
if (!dataColumn.ColumnVisible)
{
continue;
}
var cell = GetCellValues(row, col, dataColumn.ColumnDateFormat);
var haveValue = !string.IsNullOrEmpty(cell.CellValue.Text);
// Buscamos todos los estilos que apliquen a esta hoja de excel, y ademas apliquen a esta columna o a todas
var dataStyleFound = styleRowsRelation
.Where(s => s.ApplyStyleOnSheetName.Equals(sheetName) && (s.ApplyStyleOnColumns.Contains(col.ColumnName.ToLower()) || s.ApplyStyleOnColumns.Contains("*")))
.ToList();
if (!dataStyleFound.Any())
{
newRow.AppendChild(cell);
continue;
}
#region Aplicación de estilos de filas
if (dataStyleFound.Count == 1)
{
var relation = dataStyleFound.Single();
if (haveValue && relation.ApplyOnlyWhenValue)
{
// Si este estilo aplica solamente cuando tiene valor y la celda tiene valor...
cell.StyleIndex = relation.StyleIndex;
}
else if (!relation.ApplyOnlyWhenValue)
{
// Si este estilo no aplica solamente cuando tiene valor, se lo asignamos
cell.StyleIndex = relation.StyleIndex;
}
}
else
{
// Tiene varios estilos aplicando a esta columna
// La prioridad va en este orden:
// 1 - Si aplica a la columna y solo cuando tenga valor
// 2 - Si aplica a la columna
// 3 - Si aplica a todas las columnas
var relation = dataStyleFound.FirstOrDefault(f => f.ApplyOnlyWhenValue && !f.ApplyStyleOnColumns.Contains("*"));
if (relation != null && haveValue)
{
cell.StyleIndex = relation.StyleIndex;
}
else
{
relation = dataStyleFound.FirstOrDefault(f => !f.ApplyStyleOnColumns.Contains("*"));
if (relation != null)
{
cell.StyleIndex = relation.StyleIndex;
}
else
{
relation = dataStyleFound.First();
cell.StyleIndex = relation.StyleIndex;
}
}
}
#endregion
newRow.AppendChild(cell);
}
// Añadimos la fila a la hoja
sd.AppendChild(newRow);
}
#endregion
#endregion
// END
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheet sheet = new Sheet
{
Name = excelSheet.SheetName,
SheetId = (UInt32Value)excelSheet.SheetId,
Id = wbp.GetIdOfPart(wsp)
};
sheets.Append(sheet);
// END END
}
// Guardamos el libro
wbp.Workbook.Save();
// Cerramos el documento
document.Close();
}
stream.Seek(0, SeekOrigin.Begin);
}
static void Main(string[] args)
{
//Создаем новый документ
using (SpreadsheetDocument document = SpreadsheetDocument.Create("document.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
Boolean needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 10, Width = 20, CustomWidth = true
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Отчет по входящим"
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
InsertCell(row, 1, "Стиль 1", CellValues.String, 5);
InsertCell(row, 2, "Стиль 2", CellValues.String, 5);
InsertCell(row, 3, "Стиль 3", CellValues.String, 5);
InsertCell(row, 4, "Стиль 4", CellValues.String, 5);
InsertCell(row, 5, "Стиль 5", CellValues.String, 5);
InsertCell(row, 6, "Стиль 6", CellValues.String, 5);
InsertCell(row, 7, "Стиль 7", CellValues.String, 5);
// Добавляем в строку все стили подряд.
row = new Row()
{
RowIndex = 2
};
sheetData.Append(row);
InsertCell(row, 1, "1", CellValues.Number, 1);
InsertCell(row, 2, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 2);
InsertCell(row, 3, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 3);
InsertCell(row, 4, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 4);
InsertCell(row, 5, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 5);
InsertCell(row, 6, ReplaceHexadecimalSymbols("01.01.2017"), CellValues.String, 6);
InsertCell(row, 7, ReplaceHexadecimalSymbols("123"), CellValues.String, 7);
workbookPart.Workbook.Save();
document.Close();
}
}
public void CreateFile(string nameFile, string nameOfSheet)
{
// using (SpreadsheetDocument document =
// SpreadsheetDocument.Create("TestReport.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion();
//fv.ApplicationName = "Microsoft Office Excel";
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
Boolean needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 10, Width = 20, CustomWidth = true
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = nameOfSheet
};
sheets.Append(sheet);
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
InsertCell(row, 1, "Time", CellValues.String, 5);
InsertCell(row, 2, "TestCaseName", CellValues.String, 5);
InsertCell(row, 3, "TestName", CellValues.String, 5);
InsertCell(row, 4, "TestCommand", CellValues.String, 5);
InsertCell(row, 5, "ResultsOfTest", CellValues.String, 5);
InsertCell(row, 6, "DataFromFile", CellValues.String, 5);
InsertCell(row, 7, "DataFromBoard", CellValues.String, 5);
InsertCell(row, 8, "nameOfCAN", CellValues.String, 5);
InsertCell(row, 9, "TX_msg_cnt", CellValues.String, 5);
InsertCell(row, 10, "RX_msg_cnt", CellValues.String, 5);
InsertCell(row, 11, "Error_Count", CellValues.String, 5);
InsertCell(row, 12, "Lost_RX_q", CellValues.String, 5);
InsertCell(row, 13, "Lost_RX_o", CellValues.String, 5);
InsertCell(row, 14, "Lost_TX_q", CellValues.String, 5);
InsertCell(row, 15, "RX_queue", CellValues.String, 5);
InsertCell(row, 16, "TX_queue", CellValues.String, 5);
InsertCell(row, 17, "B_O", CellValues.String, 5);
InsertCell(row, 18, "UsedRX", CellValues.String, 5);
InsertCell(row, 19, "UsedTX", CellValues.String, 5);
//InsertCell(row, 5, "Стиль 5", CellValues.String, 5);
//InsertCell(row, 6, "Стиль 6", CellValues.String, 5);
//InsertCell(row, 7, "Стиль 7", CellValues.String, 5);
// Добавляем в строку все стили подряд.
//row = new Row() { RowIndex = 2 };
//sheetData.Append(row);
//InsertCell(row, 1, "1", CellValues.Number, 1);
//InsertCell(row, 2, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 2);
//InsertCell(row, 3, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 3);
//InsertCell(row, 4, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 4);
//InsertCell(row, 5, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 5);
//InsertCell(row, 6, ReplaceHexadecimalSymbols("01.01.2017"), CellValues.String, 6);
//InsertCell(row, 7, ReplaceHexadecimalSymbols("123"), CellValues.String, 7);
workbookPart.Workbook.Save();
//document.Close();
}
}
/* Export the list to excel file at the location provide by DependencyService */
public async System.Threading.Tasks.Task ExportDataToExcelAsync()
{
// Granted storage permission
var storageStatus = await CrossPermissions.Current.CheckPermissionStatusAsync(Permission.Storage);
if (storageStatus != PermissionStatus.Granted)
{
var results = await CrossPermissions.Current.RequestPermissionsAsync(new[] { Permission.Storage });
storageStatus = results[Permission.Storage];
}
if (Developers.Count() > 0)
{
try
{
string date = DateTime.Now.ToShortDateString();
date = date.Replace("/", "_");
var path = DependencyService.Get <IExportFilesToLocation>().GetFolderLocation() + "XFDevelopers" + date + ".xlsx";
FilePath = path;
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, 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 = "Xamarin Forms developers list"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("No", CellValues.String),
ConstructCell("FullName", CellValues.String),
ConstructCell("Phone", CellValues.String)
);
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Add each product
foreach (var d in Developers)
{
row = new Row();
row.Append(
ConstructCell(d.ID.ToString(), CellValues.String),
ConstructCell(d.FullName, CellValues.String),
ConstructCell(d.Phone, CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
MessagingCenter.Send(this, "DataExportedSuccessfully");
}
}
catch (Exception e)
{
Debug.WriteLine("ERROR: " + e.Message);
}
}
else
{
MessagingCenter.Send(this, "NoDataToExport");
}
}
public void CreateExcel(Stream output, DataSet report)
{
if (output == null)
{
throw new ArgumentNullException("output", string.Format("Параметр {0} имеет значение null.", "output"));
}
if (report == null)
{
throw new ArgumentNullException("report", string.Format("Параметр {0} имеет значение null.", "report"));
}
using (var workbook = SpreadsheetDocument.Create(output, SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
foreach (DataTable table in report.Tables)
{
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 = table.TableName
};
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);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("test.xlsx", 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);
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open("test.xlsx", true))
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(text, shareStringPart);
// Insert a new worksheet.
worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
// Save the new worksheet.
worksheetPart.Worksheet.Save();
}
// Save the new worksheet.
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
//создает нову книгу с листами Excel
protected virtual void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
{
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet() { Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId1" };
sheets.Append(sheet);
workbook.Append(sheets);
workbookPart.Workbook = workbook;
}
private static void ExportList(List <ReportData> dueDates, string destination)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(destination, 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 = "Due Dates"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Create Header Row
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
headerRow.Append(
new Cell()
{
CellValue = new CellValue("FormMasterId"),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue("Country"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Region"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("TaxFormCode"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Description"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Domains"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("DueDate"),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(headerRow);
// Add Data Rows to SheetData
foreach (ReportData rd in dueDates)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
newRow.Append(
new Cell()
{
CellValue = new CellValue(rd.FormMasterId.ToString()),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue(rd.Country),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Region),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.TaxFormCode),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Description),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Domains),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.DueDate),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(newRow);
}
worksheetPart.Worksheet.Save();
}
}
private void generateWorkbookPartContent(WorkbookPart workbookPart)
{
var workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var fileVersion = new FileVersion { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
var workbookProperties = new WorkbookProperties { DefaultThemeVersion = 124226 };
var bookViews = new BookViews();
var workbookView = new WorkbookView { XWindow = 630, YWindow = 600, WindowWidth = 27495, WindowHeight = 11955 };
bookViews.Append(workbookView);
var sheets = new Sheets();
var sheet = new Sheet { Name = "Документ", SheetId = 1, Id = "rId1" };
sheets.Append(sheet);
var calculationProperties = new CalculationProperties { CalculationId = 0 };
workbook.Append(fileVersion);
workbook.Append(workbookProperties);
workbook.Append(bookViews);
workbook.Append(sheets);
workbook.Append(calculationProperties);
workbookPart.Workbook = workbook;
}
public void SystemTest()
{
using (var driver = new ChromeDriver())
{
/* Opening Configuration File and Loading Init Data */
if (File.Exists("configurationfile.xml") == false)
{
throw new Exception("Configuration file do not exists in the program's directory.");
}
XmlDocument configurationFile = new XmlDocument();
configurationFile.Load("configurationfile.xml");
string projectName = configurationFile.SelectSingleNode("//project").InnerText;
string settingInProgess = configurationFile.SelectSingleNode("//setting_inprogress").InnerText;
string settingCompleted = configurationFile.SelectSingleNode("//setting_completed").InnerText;
if (projectName == String.Empty || settingInProgess == String.Empty || settingCompleted == String.Empty)
{
throw new Exception(String.Format("At least one of the configuration arguments is empty. ProjectName: {0}, SettingInProgressName: {1}, SettingCompletedName: {2}", projectName, settingInProgess, settingCompleted));
}
/* Initializing the Driver and Navigating to TMS Home Page */
var wait = new WebDriverWait(driver, TimeSpan.FromSeconds(60));
driver.Manage().Window.Maximize();
driver.Navigate().GoToUrl("https://tms.lionbridge.com/");
wait.Until(ExpectedConditions.UrlMatches("https://tms.lionbridge.com/"));
/**/
TMSProjectsPage tmsProjectsPage = new TMSProjectsPage(driver);
tmsProjectsPage.ClickChosenProject(projectName);
TMSProjectHomePage tmsProjectHomePage = new TMSProjectHomePage(driver);
tmsProjectHomePage.ChangeItemsPerPageToMinimum(driver);
tmsProjectHomePage.StatusClick();
TMSStatusPage tmsStatusPage = new TMSStatusPage(driver);
tmsStatusPage.AssingeeClick();
TMSAssigneesSubpage tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
tmsAssigneesSubpage.InitializeFiltersPanel(driver);
if (tmsAssigneesSubpage.AssigneeCount == 1)
{
throw new Exception("Activities drop down list is empty. Program now will shut down. ");
}
tmsAssigneesSubpage.ChoseActivityOption(driver, settingInProgess);
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
/*PageNavBar pageNavBar = new PageNavBar(driver);
*
* if (pageNavBar.ItemsPerPage != null)
* {
* pageNavBar.ItemsPerPage.ChoseDropDownOption(driver, "1000");
* }*/
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
AssigneeList assigneeList = new AssigneeList(driver);
List <AssigneeData> listAssigneeData = new List <AssigneeData>();
foreach (AssigneeItem assigneeItem in assigneeList.AssigneeItemsList)
{
listAssigneeData.Add(new AssigneeData(assigneeItem));
}
assigneeList.AssigneeItemsList[0].AssigneeItemElements[0].TagSingleJob(driver);
tmsAssigneesSubpage = new TMSAssigneesSubpage(driver);
tmsAssigneesSubpage.LeftMenu.JobsView.ButtonClick();
wait.Until(ExpectedConditions.ElementIsVisible(By.ClassName("r_L")));
JobList jobList = new JobList(driver);
jobList.JobShowHistory(driver, 0);
HistoryPopUp historyPopUp = new HistoryPopUp(driver);
historyPopUp.InitializeFiltersPanel(driver);
historyPopUp.ChoseSourceLanguageOption(driver, listAssigneeData[0].assigneeDataElements[0].sourceLanguage);
historyPopUp.ChoseTargetLanguageOption(driver, listAssigneeData[0].assigneeDataElements[0].targetLanguage);
historyPopUp.ChoseActivityOption(driver, settingCompleted);
HistoryList historyList = new HistoryList(driver);
//historyList.HistoryItemsList[0].HistoryItemElements[0].StepCompletedByClick(driver);
listAssigneeData[0].assigneeDataElements[0].translatorName = historyList.HistoryItemsList[0].HistoryItemElements[0].StepCompletedBy;
string path = Path.Combine(Directory.GetCurrentDirectory(), "TestFile.xlsx");
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, 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 sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
UInt32 rowIndex = 0;
foreach (var info in listAssigneeData)
{
var row = new Row()
{
RowIndex = rowIndex
};
var firstNameCell = new Cell()
{
CellReference = "A" + (rowIndex + 1)
};
firstNameCell.CellValue = new CellValue(info.assigneeDataElements[0].jobName);
firstNameCell.DataType = CellValues.String;
row.AppendChild(firstNameCell);
Cell secondNameCell = new Cell()
{
CellReference = "B" + (rowIndex + 1)
};
secondNameCell.CellValue = new CellValue(info.assigneeDataElements[0].sourceLanguage);
secondNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(secondNameCell);
Cell thirdNameCell = new Cell()
{
CellReference = "C" + (rowIndex + 1)
};
thirdNameCell.CellValue = new CellValue(info.assigneeDataElements[0].targetLanguage);
thirdNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(thirdNameCell);
Cell fourthNameCell = new Cell()
{
CellReference = "D" + (rowIndex + 1)
};
fourthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].reviewerName);
fourthNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(fourthNameCell);
Cell fifthNameCell = new Cell()
{
CellReference = "E" + (rowIndex + 1)
};
fifthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].translatorName);
fifthNameCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.AppendChild(fifthNameCell);
Cell sixthNameCell = new Cell()
{
CellReference = "F" + (rowIndex + 1)
};
sixthNameCell.CellValue = new CellValue(info.assigneeDataElements[0].effort);
sixthNameCell.DataType = CellValues.String;
row.AppendChild(sixthNameCell);
Cell seventhNameCell = new Cell()
{
CellReference = "G" + (rowIndex + 1)
};
seventhNameCell.CellValue = new CellValue(info.assigneeDataElements[0].wordcount);
seventhNameCell.DataType = CellValues.String;
row.AppendChild(seventhNameCell);
sheetData.AppendChild(row);
rowIndex++;
}
workbookpart.Workbook.Save();
}
}
}
// Generates content of workbook.
private void GenerateWorkbookContent(WorkbookPart workbook)
{
workbook.Workbook = new Workbook { MCAttributes = new MarkupCompatibilityAttributes { Ignorable = "x15" } };
workbook.Workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook.Workbook.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook.Workbook.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion = new FileVersion { ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
Sheets sheets = new Sheets();
Sheet sheet = new Sheet { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets.Append(sheet);
workbook.Workbook.Append(fileVersion);
workbook.Workbook.Append(sheets);
}
public static void CreateDoc(ExcelInfo info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "F1"
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 2,
Text = "№",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = 2,
Text = "Название",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = 2,
Text = "Цена",
StyleIndex = 0U
});
uint i = 1;
foreach (var service in info.Services)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = i + 2,
Text = i.ToString(),
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = i + 2,
Text = service.ServiceName,
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = i + 2,
Text = service.Price.ToString(),
StyleIndex = 0U
});
i++;
}
workbookpart.Workbook.Save();
}
}
private void WriteWorkbook()
{
int i = 0;
if (!IsNewSpreadsheet)
{
wbp.Workbook.FileVersion = new FileVersion() { ApplicationName = SLConstants.ApplicationName };
if (slwb.WorkbookProperties.HasWorkbookProperties)
{
wbp.Workbook.WorkbookProperties = slwb.WorkbookProperties.ToWorkbookProperties();
}
else
{
wbp.Workbook.WorkbookProperties = null;
}
if (slwb.WorkbookViews.Count > 0)
{
wbp.Workbook.BookViews = new BookViews();
for (i = 0; i < slwb.WorkbookViews.Count; ++i)
{
wbp.Workbook.BookViews.Append(slwb.WorkbookViews[i].ToWorkbookView());
}
}
else
{
wbp.Workbook.BookViews = null;
}
Sheets sheets = new Sheets();
for (i = 0; i < slwb.Sheets.Count; ++i)
{
sheets.Append(slwb.Sheets[i].ToSheet());
}
wbp.Workbook.Sheets = sheets;
if (slwb.DefinedNames.Count > 0)
{
DefinedNames dns = new DefinedNames();
for (i = 0; i < slwb.DefinedNames.Count; ++i)
{
dns.Append(slwb.DefinedNames[i].ToDefinedName());
}
wbp.Workbook.DefinedNames = dns;
}
else
{
// just assign a new DefinedNames() to clear out the existing one
if (wbp.Workbook.DefinedNames != null) wbp.Workbook.DefinedNames = new DefinedNames();
}
if (slwb.CalculationCells.Count > 0)
{
if (wbp.CalculationChainPart == null) wbp.AddNewPart<CalculationChainPart>();
wbp.CalculationChainPart.CalculationChain = new CalculationChain();
CalculationCell cc;
int iCurrentSheetId = 0;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
cc = slwb.CalculationCells[i].ToCalculationCell();
if (cc.SheetId.Value == iCurrentSheetId) cc.SheetId = null;
else iCurrentSheetId = cc.SheetId.Value;
wbp.CalculationChainPart.CalculationChain.Append(cc);
}
wbp.CalculationChainPart.CalculationChain.Save();
if (wbp.Workbook.CalculationProperties == null)
{
wbp.Workbook.CalculationProperties = new CalculationProperties()
{
CalculationId = SLConstants.CalculationId
};
}
}
else
{
if (wbp.CalculationChainPart != null)
{
wbp.DeletePart(wbp.GetIdOfPart(wbp.CalculationChainPart));
}
}
wbp.Workbook.Save();
}
else
{
using (OpenXmlWriter oxw = OpenXmlWriter.Create(wbp))
{
List<OpenXmlAttribute> oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("xmlns:r", null, SLConstants.NamespaceRelationships));
oxw.WriteStartElement(new Workbook(), oxa);
oxw.WriteElement(new FileVersion() { ApplicationName = SLConstants.ApplicationName });
if (slwb.WorkbookProperties.HasWorkbookProperties)
{
oxw.WriteElement(slwb.WorkbookProperties.ToWorkbookProperties());
}
if (slwb.WorkbookViews.Count > 0)
{
oxw.WriteStartElement(new BookViews());
for (i = 0; i < slwb.WorkbookViews.Count; ++i)
{
oxw.WriteElement(slwb.WorkbookViews[i].ToWorkbookView());
}
oxw.WriteEndElement();
}
SLSheet sheet;
oxw.WriteStartElement(new Sheets());
for (i = 0; i < slwb.Sheets.Count; ++i)
{
sheet = slwb.Sheets[i];
if (sheet.State == SheetStateValues.Visible)
{
oxw.WriteElement(new Sheet()
{
Name = sheet.Name,
SheetId = sheet.SheetId,
Id = sheet.Id
});
}
else
{
oxw.WriteElement(new Sheet()
{
Name = sheet.Name,
SheetId = sheet.SheetId,
State = sheet.State,
Id = sheet.Id
});
}
}
oxw.WriteEndElement();
if (slwb.DefinedNames.Count > 0)
{
oxw.WriteStartElement(new DefinedNames());
for (i = 0; i < slwb.DefinedNames.Count; ++i)
{
oxw.WriteElement(slwb.DefinedNames[i].ToDefinedName());
}
oxw.WriteEndElement();
}
if (slwb.CalculationCells.Count > 0)
{
oxw.WriteElement(new CalculationProperties() { CalculationId = SLConstants.CalculationId });
}
// workbook
oxw.WriteEndElement();
}
if (slwb.CalculationCells.Count > 0)
{
wbp.AddNewPart<CalculationChainPart>();
using (OpenXmlWriter oxw = OpenXmlWriter.Create(wbp.CalculationChainPart))
{
oxw.WriteStartElement(new CalculationChain());
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
oxw.WriteElement(slwb.CalculationCells[i].ToCalculationCell());
}
oxw.WriteEndElement();
}
}
}
}
// Generates content of a Workbook Part.
private void GenerateWorkbookPartContent(WorkbookPart workbookPart, List<string> sheetNames)
{
Workbook workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets = new Sheets();
Sheet sheet;
for(int ii = 0; ii < sheetNames.Count; ii++)
{
sheet = new Sheet() { Name = sheetNames[ii], SheetId = (UInt32Value)Convert.ToUInt32(ii + 1), Id = "rId" + (ii + 1) };
sheets.Append(sheet);
}
//CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)145621U };
//workbook.Append(fileVersion);
//workbook.Append(workbookProperties);
//workbook.Append(bookViews1);
workbook.Append(sheets);
//workbook.Append(calculationProperties1);
workbookPart.Workbook = workbook;
}
/// <summary>
/// Generate an excel file with the information of certificates
/// </summary>
/// <param name="dataSource">The list of certificates</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
List<CertificateDocument> dataSource = model.Certificates.Collection;
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the title
UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);
//set min date and max date in header
Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
string minDate, maxDate;
//get dates
if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
{
minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
}
else
{
minDate = model.IssuanceDateFrom;
maxDate = model.IssuanceDateTo;
}
//write both dates
UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public MemoryStream CreateExcelDoc(List <TransactionExcelModel> transactions, MemoryStream mem)
{
SpreadsheetDocument document = SpreadsheetDocument.Create(mem, 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();
// Setting up 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 = 2,
Width = 15,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 3,
Max = 3,
Width = 10,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 4,
Max = 4,
Width = 15,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 5,
Max = 5,
Width = 35,
CustomWidth = true
}
,
new Column // Name and Birthday columns
{
Min = 6,
Max = 7,
Width = 10,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 8,
Max = 8,
Width = 20,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Remittance"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", 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),
ConstructCell("Контрагент", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var transaction in transactions)
{
row = new Row();
row.Append(
ConstructCell(transaction.Id.ToString(), CellValues.Number, 1),
ConstructCell(transaction.ActionDate.ToString("yyyy/MM/dd"), CellValues.String, 1),
ConstructCell(transaction.Sum.ToString(), CellValues.String, 1),
ConstructCell(transaction.ProjectName, CellValues.String, 1),
ConstructCell(transaction.OperationName, CellValues.String, 1),
ConstructCell(transaction.TransactionType, CellValues.String, 1),
ConstructCell(transaction.Score, CellValues.String, 1),
ConstructCell(transaction.CounterPartyName, CellValues.String, 1)
);
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return(mem);
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 360, YWindow = 150, WindowWidth = (UInt32Value)14355U, WindowHeight = (UInt32Value)4680U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
private void AddSheet(SheetContext sheetContext)
{
WorkbookPart workbookPart = sheetContext.DocumentContext.WorkbookPart;
WorksheetPart worksheetPart = sheetContext.WorksheetPart;
uint sheetCount = workbookPart.GetSheetCount();
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac xr xr2 xr3"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
worksheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
worksheet.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
worksheet.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
worksheetPart.Worksheet = worksheet;
SheetViews sheetViews = worksheet.GetOrCreateSheetViews();
SheetView sheetView = new SheetView {
WorkbookViewId = (UInt32Value)0U
};
if (sheetCount == 0)
{
sheetView.TabSelected = true;
}
sheetViews.AppendChild(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties
{
DefaultRowHeight = 15D,
DyDescent = 0.25D,
};
ColumnContext CreateColumnContext(IPropertyRenderer renderer)
{
// FreezeMetadata for optimization
renderer.FreezeMetadata();
return(new ColumnContext(
sheetContext,
renderer.GetMetadata <ExcelColumnMetadata>() ?? _defaultColumnMetadata,
renderer));
}
sheetContext.Columns = sheetContext
.ReportRenderer
.Renderers
.Select(CreateColumnContext)
.ToList();
Columns columns = sheetContext.IsNotTransposed ? CreateColumns(sheetContext.Columns) : CreateColumnsTransposed();
SheetData sheetData = new SheetData();
//workSheet.Append(sheetDimension);
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
worksheet.Append(columns);
worksheet.Append(sheetData);
//workSheet.Append(pageMargins);
// Append a new worksheet and associate it with the workbook.
Sheets sheets = workbookPart.Workbook.Sheets;
Sheet sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetCount + 1,
Name = sheetContext.ReportRenderer.ReportName,
};
sheets.Append(sheet);
bool freezeTopRow = ExcelMetadata.FreezeTopRow.GetFirstDefinedValue(
sheetContext.SheetMetadata,
sheetContext.DocumentMetadata);
if (freezeTopRow)
{
worksheet.FreezeTopRow(rowNum: 1);
}
sheetContext.SheetData = sheetData;
sheetContext.Sheet = sheet;
}
public void SaveFile(string path)
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbp = spreadsheet.AddWorkbookPart();
wbp.Workbook = new Workbook();
Sheets sheets = wbp.Workbook.AppendChild <Sheets>(new Sheets());
Parallel.For(0, dict_sheet_name_map.Count, sheet_num =>
{
WorksheetPart wsp;
float tmp_fltvalue;
string tmp_strvalue = "";
Cell tmp_cell;
lock ("addition")
{
wsp = wbp.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = (UInt32)(sheet_num + 1), Name = sheet_names[sheet_num]
};
sheets.Append(sheet);
}
Parallel.For(0, lst_DepositCells[sheet_num].Count, i =>
{
lst_DepositCells[sheet_num][i].Sort((x, y) => x.Length == y.Length ? string.Compare(x, y) : x.Length > y.Length ? 1 : -1);
});
OpenXmlWriter writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < lst_DepositCells[sheet_num].Count; row++)
{
writer.WriteStartElement(new Row());
for (int col = 0; col < lst_DepositCells[sheet_num][row].Count; col++)
{
string tmp_address = lst_DepositCells[sheet_num][row][col];
bool is_string = false;
if (dict_FloatResults[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue))
{
is_string = false;
}
else if (dict_StringResults[sheet_num].TryGetValue(tmp_address, out tmp_strvalue))
{
is_string = true;
}
else if (dict_FloatConstants[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue))
{
is_string = false;
}
else if (dict_StringConstants[sheet_num].TryGetValue(tmp_address, out tmp_strvalue))
{
is_string = true;
}
if (is_string)
{
if (insert_formula)
{
tmp_cell = new Cell()
{
CellReference = tmp_address, DataType = CellValues.String, CellFormula = new CellFormula(dict_Formulas[lst_DepositCells[sheet_num][row][col]])
}
}
}
;
else
{
tmp_cell = new Cell()
{
CellReference = tmp_address, DataType = CellValues.String
}
};
/// <summary>
/// Generate inspection reports
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
foreach (var item in itemsource.InspectionReports)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = item.Value.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < item.Value.Captions.Count; i++)
{
var caption = item.Value.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var row in item.Value.DataRows)
{
int numberOfColumnsData = row.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the row
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = row.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
